asked
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.
The tables on which "it seems to take forever" - are those particularly large tables?
Questions for you to help us help you: 1. Is it a one-time job, or to be a regular / scheduled thing? 2. Is it many tables, or just a few?
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
Is it one time job? Maybe import/export wizard?
You can also try script that problematic tables altogether with data (i don't know exactly how huge that tables are) and then manually run script(s) on SQL 2012... Of course only if this is one time job. Or export data as csv files and than import that files into new SQL Server.
Rather than using a Linked Server, have you tried using a straight ODBC connection in a more ad-hoc way, such as using an [`OPENROWSET`][1] query? [1]:
http://msdn.microsoft.com/en-GB/library/ms190312.aspx
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
@rdeng Sorry I misunderstood about the daily basis. If you do need to migrate the data daily (hopefully with the way to get just the deltas) then @ThomasRushton suggestion to use the openrowset should be the best option. The name of the provider is probably going to be SQLOLEDB in place of the more modern SQLNCLI but other than that, openrowset works well provided that you don't attempt to insert from the joins but first get the deltas from SQL Server 2000 to some temp tables and only then merge them with you 2012 tables. From what I have done in the past, I remember that I could never import/merge more than 20,000 records per second for medium width tables regardless of how beefy was the network/hardware, but this is a pretty decent speed and it was always fast enough for me.
Thanks, Oleg, that is a very interesting idea which I never thought of or tried before. But since I need to do this on a daily basis, it won't work in my case. Right now I still have the 2 step data flow from SQL 2000 source to SQL 2008 bridge, then to SQL 2012 destination. This works but too many moving parts for my taste. Thanks for your suggestions.
18 People are following this question.
