November 3, 2011 at 2:46 pm
I inherited a truly nasty system which is piecemealed together. Fortunately, we are working on getting it off SQL 2000 but the logical design is extremely poor. Some of the things we are going to re-engineering and others we are simply going to duplicate for now on a new system with SQL2008 R2 664 bit. One of the things I need to just move is a bunch of databases which are loaded each day for reporting purposes. I don’t have the luxury of changing the load as far as table structures or taking just the daily changes each day. We truncate the tables and reload them from a database on a server I will call A2. Currently all this resides on the same server. However, we will be moving the reporting databases to another server called MBSQL08.
In a test environment, I tested a sample load, inserting from a SourceDatabaseA on A2 to ReportDatabase on A2. It took 4 seconds. I created a linked server from A2 to MBSQL08. I then loaded data from SourceDatabaseA on A2 to ReportDatabase on MBSQL08. It took 3 minutes, 36 seconds. This is a small fraction of the data I have to transfer daily.
The current process takes a half hour moving the data from one database to another on the same server for all the data I have to transfer. I need to be able to transfer the data in roughly a half hour daily in order to be able to meet production requirements. What is the best way to move the data across the servers?
Scott
November 3, 2011 at 2:59 pm
Are you going to do a complete cutover to the new servers? I'd suggest restoring backups on the new servers, although I'm not sure you can restore a backup from 2000 directly on 2008R2. If you can't I'd use the import/export wizard on the the new server to pull data across.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 3, 2011 at 3:20 pm
You can restore from SQL 2000 to SQL 2008 R2, just not master, model, or msdb
http://msdn.microsoft.com/en-us/library/ms191253%28v=SQL.105%29.aspx
November 3, 2011 at 4:01 pm
But is that faster than bulk copy or SSIS?
November 3, 2011 at 4:06 pm
SSIS is your best bet.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 3, 2011 at 4:49 pm
For a very similar requirement we backed up and restored databases from one server to another on a nightly basis. It was much faster (due to size) to backup and restore.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 3, 2011 at 9:40 pm
Backup & restore will be much better option and that to at the time when there is less load on the server.It will be faster during night time.
November 3, 2011 at 11:34 pm
I am also agreed with SQLRNNR and Mahesh:-)
Ali
MCTS SQL Server2k8
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply