Using replication as a migration tool
-
Hi,
I need to migrate a live system of many SQL server databases from one server to another, both running MS SQL 2005. Someone has suggested that we could use replication, so the new server would receive updates from the old server across a network (after an initial sync I'm guessing done via some kind of removable storage). These databases are quite large, about 20 gigabytes on average.
Once the date of the switchover of the websites these databases are used for arrives, we would stop the replication and use the subscriber databases instead as the main and only copy of the databases for these websites. If possible.
Looking about on the Internet through search engines, SQL websites like this one etc, I can't find anyone who has actually done this. In the Linux world I've seen it done before, so perhaps I'm just looking in the wrong place.
What are your thoughts on this sort of thing? I'm just wondering really if it's a good or bad idea, not necessarily how to do it just yet.
Thanks for your time and help.
-
It is certainly a good idea to do some sort of replication so your target database is almost up to date as this will minimise the outage when you failover. However of the various high availability technologies replication is the least suited to your purpose. This is because you would need transactional replication and that requires all tables to have primary keys. Also it only replicates user data so any changes to system tables in the database would not be carried across.
You should look at either logshipping or mirroring. Logshipping is the easiest to set up (IMHO), mirroring will be closer to real time and will failover quicker. If you do mirroring in high protection mode (synchronous) failover will be almost instantaneous with no chance of data loss. You could run in high performance mode and switch to high protection just before the failover.
with both these methods you still need a definitive cut off point where there are no connections to the live database. You will also need to copy other server level configurations such as logins as a sperate exercise up front.
As you say you have many databases you may not be able to mirror all of them due to capacity limitations, you might end up doing a mixture of mirroring and logshipping
---------------------------------------------------------------------
-
Thanks very much for this, it's appreciated.
The mirroring option sounds best really, so I'm going to spend some time looking into that one.
I just have one last question before I leave you alone! I was wondering what the best way to get the initial copy of the database across.
So it would go like this:
1. Copy the existing live database and move it physically on a USB drive (or similar)
2. Import it using SQL Server Management Studio.
3. Set up the mirroring, which should constantly "top-up" the database with the latest information, keeping it up to date.
4. Test the database on the new server is working and up to date.
5. Stop connections on the original server and start connecting to the new server.
I'm not sure how to do stage 1 though. Detaching the databases, copying the files, then re-attaching at the other end seems best, but I'm not meant to have any downtime except for when I do the actual move. I want to do a test run first though and the databases are massive. Is there a good way to do the original copy/sync of the data?
For step 4, I'm also not sure how to make sure that the database is the same on both sides. Does the mirroring setup give you a place to check the status of both the copies?
Sorry again for this... but it turns out that SQL Server is much more complex than I had expected. I've spent literally 7 hours reading up on this so far!
-
best way to initialise the database on the failover server will be backup\restore. You will need to do this anyway to prepare the database for either log shipping or mirroring. Restore the database in norecovery mode. when you read up on logshipping and mirroring reasons will become clear.
with mirroring the failover database will not be in a state you can connect to it. If you want a pre-test failover you would need to do a failover then failback. If you use synchronous mode (high protection) this is acheivable.
If you use log shipping you can have the failover database in read-only mode so would be able to connect to it for test purposes.
---------------------------------------------------------------------
-
Thanks again
I'll check all that out. -
Also note that if you have clients using the SNAC client or a recent ADO.NET driver, you can add both servers in the connection string. Then when you stop the first server, the clients will then redirect automatically to the new server.
note that you want mirroring in high safety mode to ensure all transactions move.
-
Steve Jones - Editor (9/15/2009)
Also note that if you have clients using the SNAC client or a recent ADO.NET driver, you can add both servers in the connection string. Then when you stop the first server, the clients will then redirect automatically to the new server.note that you want mirroring in high safety mode to ensure all transactions move.
just to clarify stephan, high safety and high protection mode are the same thing, terminology in mirroring can vary and be confusing. What I mean by high protection is synchronous transfer but without a witness. High safety means synchronous transfer, sometimes with a witness, sometimes without.
Excuse me if I have your meaning wrong steve.
---------------------------------------------------------------------
-
George, no problem and thanks for the clarification. They renamed the modes once, so you'll find some white papers and documentation referring to things differently.
You want synchronous mode, to be sure everything moves across.
-
Cool. There seems to be quite a bit to this.
So far my plan is probably going to be:
* Setup the new server in the same way as the old server (there are multiple instances running)
* Use this KB article to migrate usernames: http://support.microsoft.com/kb/918992/
* Login to the old server and use the "backup" function of SQL Server Management Studio to backup the databases in each instance to USB drive.
* Move the USB drive to the new server and use SQL Server Management Studio to restore the databases.
That's more-or-less it. Then I'll test the databases work on the new server.
Then when the actual migration time comes, I'm planning to use Log Shipping to "top up" the databases with the latest information.
I'll let you know how it goes.
Edit...
Changed my mind... I'm going to do a full backup, restore it to test it, delete, then restore again with "WITH NORECOVERY". Then on the day of migration, I'll do a differential backup and add that to the full backup I already had. Then, happyness...
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply