使用软件更新更新SQL Server数据库架构

How do you update your SQL sever database when installing your product's update? Are there any tools that will integrate with windows installer? My typical schema changes are:

安装产品更新时如何更新SQL服务器数据库?是否有任何工具可以与Windows安装程序集成?我的典型架构更改是:

Adding/removing columns
Adding/removing tables.
Adding views.
Adding/alter indexs.

6 个解决方案

#1

Not sure about integration with the windows installer, but you might look into Red Gate's SQL Packager

不确定与Windows安装程序的集成,但您可能会查看Red Gate的SQL Packager

#2

In my experience it is better to do db schema updates when your software connects to the database, rather than at install time. You want to do the following things:

根据我的经验,当您的软件连接到数据库时,而不是在安装时,最好进行数据库模式更新。你想做以下事情:

Identify each schema change with a unique identifier, such as a guid

使用唯一标识符(例如guid)标识每个架构更改

Include a list of all the changes you can apply with your product, for example compiled into a resource during your build

包括可以对产品应用的所有更改的列表,例如在构建期间编译到资源中

Have a table in the database to hold a list of schema changes that have been applied

在数据库中有一个表,用于保存已应用的架构更改列表

when you connect to your database, scan that table to see if any changes are needed

连接到数据库时,扫描该表以查看是否需要进行任何更改

This is all straightforward enough to do from within your running code, but not so easy to do in your installer.

这在您运行的代码中非常简单,但在安装程序中却不那么容易。

#3

Adam Cogan recommends creating a patch table that is used to record each and every update beyond your initial release. Instead of changing your schema through SSMS or Enterprise Manager make sure you script each change...both applications allow you to script your changes and then not apply them. Save the scripts to files (probably add them as resources) and then simply check the patches table each time you application runs.

Adam Cogan建议创建一个补丁表,用于记录初始版本之外的每个更新。不要通过SSMS或企业管理器更改架构,而是确保编写每个更改的脚本...两个应用程序都允许您编写更改脚本,然后不应用它们。将脚本保存到文件(可能将它们添加为资源),然后在每次应用程序运行时检查补丁表。

Adam has some rules to better SQL databases here

Adam在这里有一些更好的SQL数据库的规则

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLServerDatabases.aspx

#4

InstallShield lets you execute SQL scripts as part of an installation. Not tried it though, just remember it was on the GUI last time I looked!

InstallShield允许您在安装过程中执行SQL脚本。虽然没试过,但是记得上次我看过它时是在GUI上!

#5

You might want to look into SubSonic's migrations. First, it's a great way to version your DB. Second, it shouldn't be too hard to figure out how to run the exact same scripts from an installer.

您可能想要了解SubSonic的迁移。首先,这是对数据库进行版本化的好方法。其次,要弄清楚如何从安装程序运行完全相同的脚本应该不会太难。

#6

I think you have for each version of your software a bunch of database updates. Why don't you write these updates as a T-SQL instruction, to be tested-executed when the new version of your software is first launched? Just open the connexion to your database from your software and send the DDL instructions as you would send any SELECT or UPDATE instruction. I would also do something similar to what proposes Jack Paulsen: maintain a list of these T-SQL instructions with a double identification system: one linked to the database/software version it applies to (can be uniqueIdentifier), another one (number) to keep the instructions in a serial order (see my example: instruction 2 cannot be executed before instruction 1)

我认为你的每个版本的软件都有一堆数据库更新。为什么不将这些更新作为T-SQL指令编写,以便在首次启动新版软件时进行测试执行?只需从软件中打开与数据库的连接,然后发送DDL指令,就像发送任何SELECT或UPDATE指令一样。我还会做类似于Jack Paulsen的建议:用双识别系统维护这些T-SQL指令的列表:一个链接到它适用的数据库/软件版本(可以是uniqueIdentifier),另一个(数字)到按顺序保存指令(参见我的示例:指令2在指令1之前不能执行)

Example:

//instruction 1, batch instructions for version#2.162
USE myDatabase
GO
ALTER TABLE myTable
    ADD myColumn uniqueIdentifier Null
GO
//instruction 2, batch instructions for version#2.162
USE myDatabase
ALTER TABLE myTable
    ADD CONSTRAINT myTable_myColumn FOREIGN KEY (myColumn) ...
GO

For a complete description of ALTER, DROP and CREATE instructions, see your T-SQL help. Just be carefull enough to (for example) delete Indexes and Constraints linked to a field before deleting that field.

有关ALTER,DROP和CREATE指令的完整说明,请参阅T-SQL帮助。在删除该字段之前,请小心(例如)删除链接到字段的索引和约束。

You can of course add some extra UPDATE instructions to calculate values for added columns, etc.

您当然可以添加一些额外的UPDATE指令来计算添加列的值等。

You can even think of something more complicated, checking if previous upgrading steps (that led to database version #2.161) were correctly executed.

您甚至可以考虑更复杂的事情,检查以前的升级步骤(导致数据库版本#2.161)是否正确执行。

My advice: as you write these T-SQL instructions, keep also trace of their "counterparts", so that you can at any time (debugging time for example) downgrade your database structure to previous version.

我的建议:在编写这些T-SQL指令时,还要跟踪它们的“对应”,以便您可以随时(例如调试时间)将数据库结构降级到以前的版本。

#1