My team is evaluating tools and processes for managing database migrations/database refactoring as described by Martin Fowler, Pramod Sadalage, et. al. We're interested in automated, repeatable, testable processes, so we're not interested in techniques like manually running SQL Compare every time we deploy. We're currently using CruiseControl.NET for continuous integration.
我的团队正在评估管理数据库迁移/数据库重构的工具和流程,如Martin Fowler,Pramod Sadalage等人所述。人。我们对自动化,可重复,可测试的流程感兴趣,因此我们对每次部署时手动运行SQL Compare等技术不感兴趣。我们目前正在使用CruiseControl.NET进行持续集成。
Our production environment has multiple SQL Server 2000 database servers with replication between them. Our migrations will thus making changes to the schema on both the source and target database server.
我们的生产环境有多个SQL Server 2000数据库服务器,它们之间有复制。因此,我们的迁移将对源数据库服务器和目标数据库服务器上的模式进行更改。
To perform such a migration with a tool such as dbdeploy, it seems we would need to run the migration against one of the servers, and we would have to add the other servers as linked servers. A single script running against the main server could thus execute DDL against any of the linked servers.
要使用dbdeploy等工具执行此类迁移,我们似乎需要针对其中一个服务器运行迁移,我们必须将其他服务器添加为链接服务器。因此,针对主服务器运行的单个脚本可以针对任何链接的服务器执行DDL。
My question is this: would this approach be considered a best practice, or is there a better technique for applying migrations that touch multiple database servers?
我的问题是:这种方法是否被视为最佳实践,或者是否有更好的技术来应用触及多个数据库服务器的迁移?
4 个解决方案
#1
I don't see anything inherently wrong with this approach, however when setting it up be sure to test what happens when one of the linked servers is down. You don't want to be rolling back all the other servers if one happens to be down and you do want to know the changes were not applied to that server.
我没有看到这种方法存在任何内在错误,但是在设置时一定要测试当其中一个链接服务器关闭时会发生什么。如果一个服务器发生故障并且您确实想知道更改未应用于该服务器,则您不希望回滚所有其他服务器。
Of course the first, most important best practice for any migration is making sure you have a solid backup process in place before you start migrating changes.
当然,任何迁移的第一个也是最重要的最佳实践是确保在开始迁移更改之前有一个可靠的备份过程。
#2
Visual Studio 2008 (Team Edition , specifically GDR) can handle automated deployment of schema against defined schema/metadata file which you can deploy to servers. This could be included in your build /deploy process. However, I think there is still an issue over replication and schema changes - there is no package that understands/is aware of your replication setup.
Visual Studio 2008(Team Edition,特别是GDR)可以根据您可以部署到服务器的已定义架构/元数据文件来处理架构的自动部署。这可以包含在您的构建/部署过程中。但是,我认为复制和架构更改仍然存在问题 - 没有任何程序包可以理解/了解您的复制设置。
For instance, we use custom replication procedures on the subscriber and although schema changes propagate from the publisher we sometimes have to manually script changes depending on any custom replication we have in place. If you do not use custom replication procs, I'd say this was the way to go.
例如,我们在订阅服务器上使用自定义复制过程,虽然架构更改从发布者传播,但我们有时必须手动编写更改,具体取决于我们现有的任何自定义复制。如果你不使用自定义复制过程,我会说这是要走的路。
#3
You can try out a combination of Chinchillin and Wizardby: install Chinchillin agents on DB servers and have it execute Wizardby migration scripts during your deployment process.
您可以尝试使用Chinchillin和Wizardby的组合:在数据库服务器上安装Chinchillin代理,并让它在部署过程中执行Wizardby迁移脚本。
This integration is in the works, though :)
这种集成正在进行中,但:)
#4
Here at Red Gate we've now got a repeatable migrations solution that uses SQL Compare and SQL Source Control. The SQL Compare command line can therefore be used to support a continuous integration process.
在Red Gate,我们现在有了一个可重复的迁移解决方案,它使用SQL Compare和SQL Source Control。因此,SQL Compare命令行可用于支持持续集成过程。
http://www.red-gate.com/MessageBoard/viewtopic.php?t=14107
It's currently an early access build, so we're keen to get as much feedback as possible.
它目前是早期访问版本,因此我们希望获得尽可能多的反馈。
#1
I don't see anything inherently wrong with this approach, however when setting it up be sure to test what happens when one of the linked servers is down. You don't want to be rolling back all the other servers if one happens to be down and you do want to know the changes were not applied to that server.
我没有看到这种方法存在任何内在错误,但是在设置时一定要测试当其中一个链接服务器关闭时会发生什么。如果一个服务器发生故障并且您确实想知道更改未应用于该服务器,则您不希望回滚所有其他服务器。
Of course the first, most important best practice for any migration is making sure you have a solid backup process in place before you start migrating changes.
当然,任何迁移的第一个也是最重要的最佳实践是确保在开始迁移更改之前有一个可靠的备份过程。
#2
Visual Studio 2008 (Team Edition , specifically GDR) can handle automated deployment of schema against defined schema/metadata file which you can deploy to servers. This could be included in your build /deploy process. However, I think there is still an issue over replication and schema changes - there is no package that understands/is aware of your replication setup.
Visual Studio 2008(Team Edition,特别是GDR)可以根据您可以部署到服务器的已定义架构/元数据文件来处理架构的自动部署。这可以包含在您的构建/部署过程中。但是,我认为复制和架构更改仍然存在问题 - 没有任何程序包可以理解/了解您的复制设置。
For instance, we use custom replication procedures on the subscriber and although schema changes propagate from the publisher we sometimes have to manually script changes depending on any custom replication we have in place. If you do not use custom replication procs, I'd say this was the way to go.
例如,我们在订阅服务器上使用自定义复制过程,虽然架构更改从发布者传播,但我们有时必须手动编写更改,具体取决于我们现有的任何自定义复制。如果你不使用自定义复制过程,我会说这是要走的路。
#3
You can try out a combination of Chinchillin and Wizardby: install Chinchillin agents on DB servers and have it execute Wizardby migration scripts during your deployment process.
您可以尝试使用Chinchillin和Wizardby的组合:在数据库服务器上安装Chinchillin代理,并让它在部署过程中执行Wizardby迁移脚本。
This integration is in the works, though :)
这种集成正在进行中,但:)
#4
Here at Red Gate we've now got a repeatable migrations solution that uses SQL Compare and SQL Source Control. The SQL Compare command line can therefore be used to support a continuous integration process.
在Red Gate,我们现在有了一个可重复的迁移解决方案,它使用SQL Compare和SQL Source Control。因此,SQL Compare命令行可用于支持持续集成过程。
http://www.red-gate.com/MessageBoard/viewtopic.php?t=14107
It's currently an early access build, so we're keen to get as much feedback as possible.
它目前是早期访问版本,因此我们希望获得尽可能多的反馈。