April 9, 2010 at 10:27 am
SQL Server 2005 SP3 x64 Enterprise - Clustered
We are planning for a migration to a new data center. New servers and new storage. Aside from the migration being successful, our main goal is to minimize downtime. So, my question is, which is better from that perspective - mirroring or log shipping?
We've successfully used log shipping in the past for migrations, so we're familiar with it. However, the word from management is to minimize downtime, so we thinik mirroring might give us an advantage. BTW - clustering is our HA method. If we use mirroring for the migration, we won't be keeping it turned on.
Thoughts anyone?
***************************************************************************
Steps to take -
Database Migration using Mirroring
1)Establish endpoints on Principal and Mirrored instances.
2)Restore databases on instance that is to serve as mirror WITH NORECOVERY
3)Establish High Performance database mirroring between principal and mirrored instances.
a.No witness
b.SAFETY OFF
c. You cannot mirror the master, msdb, temp, or model databases.
4)When outage window begins eliminate potential database changes on principal by -
a.Disabling application logins that are used to connect to the principal instance
b.Shutting down SQL Server Agent service
5)Change mirroring to High Protection so that principal and mirror get synced up
a.ALTER DATABASE [<dbname>] SET SAFETY FULL;
6)Verify instances are in sync by querying sys.database_mirroring and retrieving database name and the mirroring_failover_lsn
7)Initiate manual failover on principal instance
a.ALTER DATABASE [<dbname>] SET PARTNER FAILOVER;
8)Verify success of failover
9)Turn off database mirroring on principal instance (the “new” principal, that is)
a.ALTER DATABASE [<dbname>] SET PARTNER OFF
b.Optionally, delete endpoints
10)Rename virtual network name of old cluster
11)Rename virtual network name of new cluster to what the old cluster was originally named
12)Test connecting to the instance and querying the databases
13) Sync up the SIDS of the database users and the SQL Server logins
14)Re-enable the application logins and SQL Server Agent
April 9, 2010 at 12:09 pm
Your database seems to be big. Both database mirroring and log shipping should work. If you are more familiar with log shipping, it will be better to use this method. You can simply run a differential backup after application cutoff, and apply it to the standby database and then make it writable. It is very straight forward, easy, and fast to do for the database migration itself. Don't really on verbile promises, just run tests to fully verify the new server is actually ready to server.
Finally, when the database in the new server is fully ready, run a full backup before opening to applications.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply