What is the best way to load data from SQL 2000 to SQL 2012?
I need to load large amount of data from SQL 2000 source to a SQL 2012 server. I have tried to use a SQL 2008R2 bridge server successfully, by creating a linked server from bridge server to connect to SQL 2000 source, use SQL command (i.e. insert into dbo.dimCustomer select * from APOLLO.Neo.dbo.vCustomer) to load data from SQL 2000 source to the bridge server, then do the same to load data from bridge server to SQL 2012 destination. Now I'd like to save some time and steps by not using a bridge server, but that when I got a lots of problems: 1. I cannot create a linked server on SQL 2012 destination to connect to SQL 2000 source, 2. I tried to create SSIS package by using OLE DB connections, it seems to work for some tables but take forever for others when reading the SQL 2000 source. Have you ever experience this? Are there other ways to accomplish the same? What is the best/recommended way to do this? Thanks for your suggestions.
Since you do have the SQL Server 2008 R2 available, you can try this: 1. Stop the SQL Server 2000 instance so you can copy the database file to the location used to store the database files of your SQL Server 2008 R2 instance 2. Attach the database file to the SQL Server 2008 R2 instance so the database is now upgraded to 2008 R2. When this happens, the compat level of the database will remain at 80 still, and therefore, you should change it to 100. Otherwise, you will not be able to attach this database to SQL Server 2012 because the maximum number of compat level steps to jump is 2. SQL Server 2000 is 80, 2005 is 90, 2008/2008R2 is 100 and 2012 is 110. You can jump to 110 from 90 and above but not from 80, so changing the compat level to 100 from 80 is a must to ensure that your database is indeed 2008 R2 at this time. 3. Stop the 2008 R2 instance (or just detach the database, either will do) so you can now copy the database file to the location used to store the database files of the SQL Server 2012 instance. 4. Attached the file from the previous step to the SQL Server 2012 instance. Check the compat level and if still reads 100, change it to 110. This way you have the entire database which was originally SQL Server 2000 now upgraded to SQL Server 2012 and also located on the same instance with you other databases. If you need to move the data from one database to another, you can use the standard T-SQL without necessity to worry about any linked servers and/or openrowset/openquery. Oleg