SQL 2000 到 SQL 2008 的迁移策略
我已经仔细阅读了这里有关从 SQL 2000 迁移到 SQL 2008 的线程，但还没有真正遇到我的问题，所以这里我们讨论另一个线程。
我正在制定一项策略，将特定的 SQL 2000 数据库移动到新的 SQL 2008 R2 实例。我的问题是关于传输模式和数据的最佳方法。我知道的一种方法是执行快速“n”脏分离-复制-附加方法，只要我完成了关于兼容性和代码等的作业，该方法就应该可以工作。
但是，如果我通过脚本编写架构和登录，然后通过 SSIS 复制数据呢？我正在考虑尝试这样做，以便我可以更轻松地将一些测试用例集成到包中（错误处理等）。如果我这样做的话，我会给自己做些什么呢？
I've perused the threads here on migration from SQL 2000 to SQL 2008 but haven't really run into my question, so here we go with another one.
I'm building a strategy to move specific SQL 2000 databases to a new SQL 2008 R2 instance. My question comes with regards to the best method for transferring the schema and data. One way I know of is to do the quick 'n' dirty detach - copy - attach method, which should work so long as I've done my homework wrt compatibility and code and such.
What if, though, I wrote the schema and logins via script and then copied the data via SSIS? I'm thinking of trying that so I can more easily integrate some of my test cases into the package (error handling and whatnot). What would I be setting myself up for if I did this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于您要在服务器或实例之间移动数据，因此我建议通过数据流移动数据。如果您不希望多次运行该代码，则可以让向导为此移动生成代码。然而，当我在两年多前执行此操作时，向导代码生成了组合执行 sql 任务，将许多“创建表”命令组合到一个任务中，并创建了一些数据流任务，其中有多个源和目标，以便在其中插入数据目的地。这对于启动和运行来说很好，但是当我修改新目标表的架构后想要再次刷新表时，它就不够了。如果您希望多次运行刷新，那么您可能需要先花时间创建目标架构，然后手动创建数据流。
移动数据后，您可以在新服务器上启用全文搜索。我不相信您需要在第一次加载时启用此功能。
我建议不要使用分离-附加方法进行迁移的原因之一是，您将所有脏东西从 2000 数据库转移到 2008 R2 数据库。如果 2000 服务器上的安全性太松懈，或者有许多不应该存在的旧用户，那么从头开始清理可能会更容易。如果使用detach-attach方式，那么就得担心用户了。
Since you are moving the data between servers or instances, I would recommend moving the data via data flows. If you don't expect to run the code more than once, then you can let the wizard generate your code for this move. However, when I did this once 2+ years ago, the wizard code generated combined execute sql tasks that combined many "create table" commands into one task and created a few data flow tasks that had multiple source and destinations in them to insert data in the destination. This was good to get up and running, but it was inadequate when I wanted to refresh the tables one more time after I modified the schema of the new target tables. If you expect to run the refresh more than once, then you may want to take the time to create the target schema first and then manually create the data flows.
Once you have moved the data, then you can enable full-text search on the new server. I don't believe you will need to have this enabled on your first load.
One reason I recommend against the detach-attach method for migration is that you bring all the dirty laundry from the 2000 database to the 2008 R2 database. If you had too lax security on the 2000 server or many ancient users that shouldn't exist, it could be easier to clean this up by starting from scratch. If you use the detach-attach method, then you have to worry about users.