维护 Web 应用程序的 TSQL 数据库创建脚本的最佳实践
我们有一个 ASP.NET Web 应用程序，需要维护数据库创建和初始化脚本。
人们是否知道维护数据库创建和初始化脚本的行业最佳实践。我可以想到两种主要方法。
- 直接手工维护一个tsql创建脚本。
- 维护主数据库并创建脚本，然后将其签入源安全。
此外，脚本应该能够通过源代码控制进行跟踪，即表顺序应该是可控的。
如果可能的话还应该包括在相同或单独的脚本中跟踪初始化数据的能力。
目前，我们从 Management Studio 生成脚本，但表的顺序似乎是随机的。
解决方案自动化程度越高越好。
We have a ASP.NET web application and need to maintain the database creation and initialization script.
Are there any industry best practices that people know of for maintaining database creation and initialization scripts. I can think of two main approaches.
- Maintain a tsql creation script directly by hand.
- Maintain a master database and create the script that is then checked into source safe.
Also the script should be able to be tracked through source control, i.e. table order should be controllable.
If possible should also include the ability to track initialisation data either in the same or a seperate script.
Currently we generate the script from management studio but the order of the tables seems to be random.
And the more automated the solution the better.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
问题不在于维护脚本，也不在于维护数据库的“主”副本。真正的问题是升级现有数据库。您在开发人员环境中进行修改，然后将其传播到测试环境，最后推送到生产环境。虽然在开发人员和测试环境阶段可以从头开始，但在生产中您始终必须升级现有部署。
根据我的经验，最佳实践是使用升级脚本。即使对于单个部署站点，这种做法也很有用，但对于可能处于不同版本的多个位置来说，它变得非常宝贵。但即使只有一个操作站点，能够重复测试升级（从当前版本的备份开始）、将更改保留在源代码控制中、拥有一个良好的形式化和同行评审的更改程序（升级脚本）仍然很有用。升级脚本可以根据操作站点的特定需求进行定制，例如特别小心地处理大型表，或者处理加密数据，或者基于差异的工具忽略或忽略的无数细节之一。主要缺点是必须编写脚本，这需要真正的 T-SQL 知识（忘记您最喜欢的管理工具中的所有“设计者”）。
The problem is not maintaining the script, nor maintaining a 'master' copy of the database. The real problem is upgrading existing database(s). You do your modification in the developer environment, which are then propagated to the test environment, and finally pushed into production environment. While at developer and test environment stage is possible to start from scratch, in production you always have to upgrade the existing deployment.
In my experience the best practice is to use upgrade scripts. This practice is useful even with a single deployed site, but it becomes invaluable with multiple locations that may be at different versions. But even with one single operational site is still useful to be able to test the upgrade repeatedly (starting from backups of current version), keep the changes in source control, have a well formalized and peer reviewed change procedure (the upgrade script). And upgrade scripts can be tailored to specific needs of the operational site, like handling a large table with special care, or deal with encrypted data, or whatever one of the myriad of the details diff based tools neglect or ignore. The main disadvantage is the the scripts have to be written, which require real T-SQL knowledge (forget all the 'designers' in you favorite management tool).
您可能需要查看 RedGate SQL 源代码管理 。
You might want to check out RedGate SQL Source Control.
您是否正在寻找 Visual Studio 数据库项目？
我使用数据库项目来存储所有数据库对象（表、视图、函数、键、触发器、跨架构的索引）并在 TFS 中保留版本控制。您可以构建数据库以确保一切都有效。您可以部署到新数据库，或与现有数据库进行架构比较。
我还将所有参考和设置数据保留在部署后脚本中，这些脚本在部署后自动运行。
Are you looking for Visual Studio Database Projects?
I use database projects to store all database objects (tables, views, functions, keys, triggers, indexes across schemas) and keep versioning in TFS. You can build the database to ensure that everything is valid. You can deploy to a fresh database, or do a schema comparison with an existing database.
I also keep all reference and setup data in post deployment scripts which are automatically run after deployment.