在大型团队中管理 Sql Server 数据库版本控制
在过去的几年里，我是唯一处理我们为网络项目创建的数据库的开发人员。这意味着我可以完全控制版本管理。我无法再继续完成所有数据库工作，我想让其他一些开发人员加入这个循环。
我们使用 Tortoise SVN 并将所有存储库存储在内部专用服务器上。一些客户要求我们不要将他们的真实数据放在我们的办公室服务器上，因此我们只保留可以生成其数据库结构的脚本以及创建有用的虚假数据的脚本。有时，我们的客户希望我们获得有关我们的开发机器的最新信息。
那么较大的开发团队使用什么工作流程来处理版本管理和数据库共享。大多数开发人员更喜欢将数据库部署到其开发计算机上的 Sql Server 实例。我们是否应该
- 将每个数据库的脚本保留在 SVN 中，并让开发人员在进行微小更改时导出新脚本 进行
- 更改后分离数据库并将 MDF 文件提交到 SVN
- 将所有开发副本放在内部网络上的服务器上并强制开发人员通过远程桌面连接进行修改
- 其他一些我没有想到的选项
For the last few years I was the only developer that handled the databases we created for our web projects. That meant that I got full control of version management. I can't keep up with doing all the database work anymore and I want to bring some other developers into the cycle.
We use Tortoise SVN and store all repositories on a dedicated server in-house. Some clients require us not to have their real data on our office servers so we only keep scripts that can generate the structure of their database along with scripts to create useful fake data. Other times our clients want us to have their most up to date information on our development machines.
So what workflow do larger development teams use to handle version management and sharing of databases. Most developers prefer to deploy the database to an instance of Sql Server on their development machine. Should we
- Keep the scripts for each database in SVN and make developers export new scripts if they make even minor changes
- Detach databases after changes have been made and commit MDF file to SVN
- Put all development copies on a server on the in-house network and force developers to connect via remote desktop to make modifications
- Some other option I haven't thought of
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
开发源代码树中永远不要有 MDF 文件。 MDF 是部署应用程序的结果，而不是应用程序源的一部分。从开发源的角度来思考数据库是一条通往地狱的捷径。
所有开发交付成果都应该是部署或升级数据库的脚本。任何更改，无论多么小，都会采取脚本的形式。有些人建议使用 diff 工具，但我认为它们是一个老鼠洞。我支持对数据库元数据进行版本控制，并使用脚本从版本 N 升级到版本 N+1。在部署时，应用程序可以检查当前部署的版本，然后运行所有升级脚本以将版本更新为当前版本。没有脚本可以直接部署当前版本，新部署会部署数据库的第一个 v0，然后进行所有版本升级，包括删除不再使用的对象。虽然这听起来有点极端，但这正是 SQL Server 本身跟踪版本之间数据库中发生的各种更改的方式。
作为简单的文本脚本，所有数据库升级脚本都像任何其他源一样存储在版本控制中，并跟踪更改、比较和签入审查。
有关更详细的讨论和一些示例，请参阅 版本控制和您的数据库数据库。
Never have an MDF file in the development source tree. MDFs are a result of deploying an application, not part of the application sources. Thinking at the database in terms of development source is a short-cut to hell.
All the development deliverables should be scripts that deploy or upgrade the database. Any change, no matter how small, takes the form of a script. Some recommend using diff tools, but I think they are a rat hole. I champion version the database metadata and having scripts to upgrade from version N to version N+1. At deployment the application can check the current deployed version, and it then runs all the upgrade scripts that bring the version to current. There is no script to deploy straight the current version, a new deployment deploys first v0 of the database, it then goes through all version upgrades, including dropping object that are no longer used. While this may sound a bit extreme, this is exactly how SQL Server itself keeps track of the various changes occurring in the database between releases.
As simple text scripts, all the database upgrade scripts are stored in version control just like any other sources, with tracking of changes, diff-ing and check-in reviews.
For a more detailed discussion and some examples, see Version Control and your Database.
选项（1）。每个开发人员都可以拥有自己的最新数据库本地副本。 （最新的意思是，从最新的版本控制脚本重新创建（基础+增量更改+基础数据+运行数据）。为了使这项工作顺利进行，您应该能够在本地“一键式”部署任何数据库。
Option (1). Each developer can have their own up to date local copy of the DB. (Up to date meaning, recreated from latest version controlled scripts (base + incremental changes + base data + run data). In order to make this work you should have the ability to 'one-click' deploy any database locally.
使用 Visual Studio Database Edition 这样的工具确实不会出错。这是 VS 的一个版本，用于管理数据库架构等，包括对目标服务器的部署（更新）。
VSDE 与 TFS 集成，因此您的所有数据库架构都处于 TFS 版本控制之下。这成为您的模式管理的“事实来源”。
通常，开发人员将针对本地开发数据库进行工作，并通过将其架构与 VSDE 项目中的架构同步来保持其架构最新。然后，当开发人员对他/她的更改感到满意时，它们将被签入 TFS，然后可以完成构建和部署。
VSDE 还支持重构、模式比较、数据比较、测试数据生成等。这是一个很棒的工具，我们用它来管理我们的模式。
You really cannot go wrong with a tool like Visual Studio Database Edition. This is a version of VS that manages database schemas and much more, including deployments (updates) to target server(s).
VSDE integrates with TFS so all your database schema is under TFS version control. This becomes the "source of truth" for your schema management.
Typically developers will work against a local development database, and keep its schema up to date by synchronizing it with the schema in the VSDE project. Then, when the developer is satisfied with his/her changes, they are checked into TFS, and a build and then deployment can be done.
VSDE also supports refactoring, schema compares, data compares, test data generation and more. It's a great tool, and we use it to manage our schemas.
在以前的公司（每月迭代中使用敏捷），.sql 文件被签入版本控制，完整构建过程的（可选）部分是从生产中重建数据库，然后按顺序应用每个 .sql 文件。
在迭代结束时，.sql 指令被合并到创建数据库生产版本的脚本中，并且脚本文件被移出。因此，您只应用当前迭代的更新，而不会返回到项目开始时。
In a previous company (which used Agile in monthly iterations), .sql files were checked into version control, and (an optional) part of the full build process was to rebuild the database from production then apply each .sql file in order.
At the end of the iteration, the .sql instructions were merged into the script that creates the production build of the database, and the script files moved out. So you're only applying updates from the current iteration, not going back til the beginning of the project.
您是否看过名为 DB Ghost 的产品？我个人没有使用过它，但它看起来很全面，并且可以提供替代方案作为您问题中的第 4 点。
Have you looked at a product called DB Ghost? I have not personally used it but it looks comprehensive and may offer an alternative as part point 4 in your question.