将SQL Server 2000升级到2005或2008 - DTS到SSIS

Our office uses SQL Server 2000, and by and large it has served our needs well. I'm looking into the possibility of upgrading to SQL 2005 or 2008 because of the new features not found in 2000 (when I saw Index usage statistics which told me which Indexes were the most used and which were never used, that blew me away).

我们的办公室使用SQL Server 2000,总的来说它很好地满足了我们的需求。我正在研究升级到SQL 2005或2008的可能性,因为2000年没有找到新功能(当我看到索引使用统计数据告诉我哪些索引是最常用的,哪些从未使用过,这让我感到震惊) 。

I'm not too concerned with how to move the databases from 2000 to 2005 or 2008 - I can just take a full backup and then restore the backup to the new sql installation. What concerns me are the existing DTS packages. We have dozens of them that we rely on every day. How difficult/easy is it to convert the existing DTS packages to SSIS? What are some of the 'gotchas' that I have to look out for?

我不太关心如何将数据库从2000移动到2005或2008 - 我可以进行完整备份,然后将备份恢复到新的sql安装。我关心的是现有的DTS包。我们每天都依赖它们数十种。将现有DTS包转换为SSIS有多困难/容易?我需要注意哪些“陷阱”?

4 个解决方案

#1

Doing a backup & restore to do a database server upgrade has many gotcha's. It's best to do an in-place upgrade (you might want to take a full backup of the server first) & let the MS installer do the work. 2005 supports 2000 DTS packages as Legacy so there's no need to learn SSIS & convert all your packages unless you have the time & there isn't too many.

进行备份和还原以进行数据库服务器升级有很多问题。最好进行就地升级(您可能希望首先对服务器进行完整备份)并让MS安装程序完成工作。 2005支持2000 DTS包作为Legacy,因此除非你有时间且没有太多,否则不需要学习SSIS并转换所有包。

As for the backup & restore upgrade, don't forget about:

至于备份和恢复升级,请不要忘记:

logins, you'll need transfer these, see KB246133 for more details

Any user and role permissions given to system databases will need to be re-created or transferred

需要重新创建或传输给系统数据库的任何用户和角色权限

linked server or remote servers will need to be re-setup

需要重新设置链接服务器或远程服务器

SQL Server Agent Jobs need to be exported-imported

需要导出导入SQL Server代理作业

Any Extended stored procedures need to be re-created

需要重新创建任何扩展存储过程

Replication will need to be re-setup

需要重新设置复制

Log shipping will need to be re-setup & the standby server also upgraded

需要重新设置日志传送并且备用服务器也已升级

#2

There is a migration tool for DTS packages, but I wouldn't use it. SSIS is so much better than DTS that you should:

有一个DTS包的迁移工具,但我不会使用它。 SSIS比DTS好得多,你应该:

Learn about SSIS. Get comfortable with it. Rejoice at having actual control flow.

了解SSIS。适应它。对实际控制流程感到高兴。

Take the time to understand and document your existing DTS packges while you've still got the environment they're written for.

花时间了解并记录您现有的DTS包装,同时您仍然可以获得所需的环境。

Use that understanding to write equivalent SSIS packages.

使用该理解来编写等效的SSIS包。

This idea will make no sense to you if you don't know SSIS. For instance, I was just thinking about how I used to have to do loops in DTS - that whole business with finding and modifying the precedence constraint you wanted to loop back to.

如果您不了解SSIS,这个想法对您没有任何意义。例如,我只是想着我以前如何在DTS中进行循环 - 整个业务包括查找和修改您想要循环回的优先约束。

SSIS actually has a For Loop container that can loop over a set of numbers, over the rows in a rowset, over files in a folder, etc. No hacks with precedence constraints and little hidden scripts.

SSIS实际上有一个For循环容器,它可以循环遍历一组数字,行集中的行,文件夹中的文件等。没有具有优先约束和少量隐藏脚本的黑客。

#3

DTS packages can still be run as usual in 2008 as long as Backwards Compatibility is installed. Here's the link to upgrading which uses a wizard.

只要安装了向后兼容性,DTS包仍然可以像往常一样在2008年运行。这是使用向导的升级链接。

#4

I'd recommend a tool from Pragmatic Works called DTS xChange. It handles most of the migration and helps you get on with your job. You'll have other opportunities to learn SSIS on new stuff but you need to probably wrap up your project. It can be downloaded at http://www.pragmaticworks.com

我推荐一种名为DTS xChange的Pragmatic Works工具。它处理大部分迁移并帮助您继续工作。您将有其他机会学习新东西的SSIS,但您可能需要完成项目。它可以从http://www.pragmaticworks.com下载

#1