管理 C# 项目中的数据库更改
我有一个 C# 应用程序(在 VisualStudio 2010 中),它使用通过 C# 中的 TableAdapter 访问的 SqlServer 2005。
我还没有发现管理数据库更改的好方法。例如,在我的下一个版本中,我进行了一系列数据库架构更改。我在 Sql Server Management Studio 中进行了所有数据库更改。但现在,在部署新的应用程序代码后,我必须依次在生产服务器上手动进行这些更改(缓慢且有错误)。
此外,如果我决定将版本回滚到以前的版本,我必须手动检查并撤消所有数据库更改,然后才能部署旧代码(现在我受到时间限制,因为应用程序已关闭)。同样,这也很容易出错。
哦,希望我的错误之一不会对生产数据库造成大规模破坏,否则我现在必须从存储中取出最新的备份并重试(非常耗时)。
我听说过诸如从 Rails 迁移(以及像 SubSonic 这样的 ORM)之类的东西。我认为新的 ORM 样式(在 C# 代码中定义架构)有助于缓解很多问题,但不幸的是,由于我使用 TableAdapters,我不知道如何实现迁移之类的东西。
人们如何处理这个问题?
I have a C# app (in VisualStudio 2010) that used SqlServer 2005 accessed through TableAdapters in C#.
I haven't discovered a good way to manage DB changes. For example, in my next release I have a bunch of db schema changes. I made all of my DB changes in Sql Server Management Studio. But now I have to manually make these changes on the production servers in turn after I deploy the new application code (slow and buggy).
Furthermore, if I decide to roll back my release to a previous version, I have to manually go through and undo all my db changes before I can deploy the old code (and now I am under time constraints because the app is down). Again, this is also very error prone.
Oh, and lets hope that one of my errors doesn't cause massive destruction to the production DB, otherwise I now have to pull the most recent backup out of storage and try again (very time consuming).
I have heard of things like Migrations from Rails (and ORMs like SubSonic). I think that the new ORM style (define your schema in c# code) helps alleviate a lot of this, but unfortunately, as I am using TableAdapters, I don't see how I could implement something like migrations.
How do people deal with this?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
DB 的发布管理通常涉及静态数据的迁移和运行脚本以更新/创建可编程性元素(存储过程、UDF、触发器等)并修改现有模式定义。在我看来你好像缺少脚本。如果您手动对开发数据库进行更改,而不是创建反映这些更改的脚本,则您将需要针对测试/生产环境重复相同的手动步骤,正如您所说,这容易出错且危险。
SQL Server Management Studio 可以轻松保存反映任何数据库对象更改的脚本。工具栏中应该有一个名为“生成更改脚本”的图标,它使您可以选择将 SQL 文件保存到磁盘。然后,您可以使用它对另一台服务器执行相同的更改。您还可以手动编写任何或所有存储过程、UDF、触发器等的脚本,并针对服务器运行它们(只需右键单击它们)。
至于回滚,通常是通过恢复部署过程开始之前创建的数据库备份来实现的。
每个公司的整个过程往往有所不同,但通常都是这样完成的。
自动生成模式的 ORM 对我来说总是很邪恶,更不用说几乎不可能在生产环境中使用,但我想还有一个选择。
Release management for DBs usually involves migrations of static data and running of scripts to update/create programmability elements (sprocs, UDFs, triggers, etc) and modify existing schema definitions. Looks to me like you're missing the scripts. If you're making changes manually to your development DB and not creating scripts that mirror those changes, you will need to repeat the same manual steps against your test/production environments, which as you say is error prone and dangerous.
SQL Server Management Studio makes it easy to save scripts that reflect changes to any database objects. In the toolbar there should be an icon called "Generate change script", which gives you the option to save the SQL file to disk. You can then use this to perform the same change against another server. You can also manually script any or all stored procs, UDFs, triggers and so on, and run those against a server as well (just right-click on them).
As to rollback, that's normally achieved by restoring a backup of the database made just before the deployment process begins.
This whole process tends to be different for each company, but that's generally how it's done.
ORMs that auto-generate schemas have always seemed evil to me, not to mention pretty much impossible to use against a production box, but I guess there's also an option.
处理此问题的最简单方法是购买可以通过比较两个数据库更改来检测数据库架构的软件,并生成可以更新目标数据库的更改脚本。为此,我使用 Visual Studio Ultimate 2010,但也有更便宜的软件可以执行相同的操作。这对我来说 99% 的时间都有效(唯一不能正常工作的情况是当我重命名表列时)。
如果您没有这样的软件,那么手动生成 SQL 更改脚本至关重要。每当您对数据库模式进行更改时,请跟踪用于更改的 SQL,并将其添加到下一版本软件的数据库模式更改的一个大文件中。一开始有点乏味,但你很快就会习惯的。
然后,当您准备好部署软件时,请执行以下操作:
所有这些步骤都可以使用批处理文件和 SQL Server 代理或 SQLCMD 轻松实现自动化。
通常,您应该首先部署到临时服务器,然后非常彻底地测试您的网站,然后才转移到生产服务器。这样您就可以避免生产服务器出现更长的停机时间,并最大限度地降低丢失重要数据的风险。
The easiest way to deal with this problem is to buy software that can detect db schema by comparing two databases changes and generate a change script that can update your target database. I am using Visual Studio Ultimate 2010 for that, but there's also cheaper software that can do the same. This works for me 99% of the time (the only instance where this did not work properly for me is when I renamed a table column).
If you don't have such a piece of software, it is crucial to generate your SQL change scripts by hand. Whenever you do a change to the database schema, keep track of the SQL you used for that changed and add it to one big file of db schema changes for the next version of your software. It's a bit tedious at the beginning, but you'll get used to it pretty quickly.
Then when you are ready to deploy the software, proceed as follows:
All of these steps can be easily automated using batch files and the SQL server agent or SQLCMD.
Generally you should deploy to a staging server first, then test your website very thoroughly and only then move on to the production server. This way you avoid longer downtimes on your production server and minimize the risk of losing any vital data.
Red Gate Software 目前正在解决这个问题。请查看我们的 SSMS 插件 SQL 源代码控制,与SQL Compare Pro结合使用。我们还在开发“迁移”功能,该功能将于今年晚些时候推出,允许为特定版本转换定义自定义迁移脚本。由于我们仍处于项目的早期阶段,因此仍有时间向我们提供反馈并帮助我们设计一个出色的解决方案。我们很乐意与您进一步讨论您的要求!
Here at Red Gate Software we're currently tackling this exact issue. Please take a look at our SSMS add-in, SQL Source Control, in combination with SQL Compare Pro. We're also working on a 'Migrations' feature, due out later this year, allowing custom migrations scripts to be defined for specific version transitions. As we're still in the early stages of the project, there's still time to give us feedback and help us design a great solution. We'd love to speak to your further about your requirements!