Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

I want to migrate my database server to a new server. Right now I have a database server with Windows Server 2008 and now I am migrating to a new, separate server with Windows Server 2012.

There are around 50 to 100 databases.

What is the best way to migrate the database server without affecting clients (meaning no downtime)?

share|improve this question
what does "affecting clients' mean? you could put the databases into read_only mode and so clients can still read but not make any writes while you're backing up and restoring dbs to the new instance. additionally, are there going to be new app servers? how are you going to accommodate connection string changes with zero downtime? – swasheck Jun 5 at 21:32
Affecting clients mean zero down time or minimize down time – Tushar Maru Jun 6 at 7:05

migrated from stackoverflow.com Jun 5 at 18:25

2 Answers

up vote 3 down vote

Copy 50 to 100 databases to a completely new server with zero downtime? I don't think that's going to happen. When you're talking about a migration, planned downtime is an absolute must.

In this case, depending on the size of the databases, I'd take the scripting route with this. If you are proficient with PowerShell, you can loop through all the databases and take backups to store in accessible storage for the new server. Then on the new server, you can script out the restore of all the databases.

Don't forget about the server-level items (logins, jobs, etc.). You can script these as well, but with this particular requirement SSIS actually makes it very easy. There are tasks to do all of these server-level items.

If you're expecting no downtime, how are the clients going to redirect their requests to the new database server? There are a few external solutions for this, but something to consider when you promise "zero downtime".

share|improve this answer
up vote 0 down vote

There are couple of options which you can use to Minimize your downtime with your current available hardware :

Foremost step is to run Upgrade advisor (if the SQL Server versions will be different from Old to New server).

Also, take FULL backups first for all the databases - just in-case if something goes wrong.

Method 1 : Log Shippping

  1. Set up logshipping between old server and new server (possibly every 1 mins).

  2. When you decide to failover, take a tail log backups first and then restore that tail log backups for all the databases and then restore the databases with recovery on New server.

  3. Obviously, you can do some prep-work beforehand to create logins, sql agent jobs, etc.

Method 2 : Database Mirroring

  1. Create mirroring for all the databases in High performance (asynchronous) to avoid performance overhead if waiting for a transaction to commit on mirror server and then acknowledge by the principal.

  2. When you want to failover, change to SYNCHRONOUS mode ( High Availability Mode) and then initiate a failover. Only transactions that are in-flight will be interrupted.

These methods are just overview of what each technology can achieve.

There will always be downtime when you have to re-point your applications/users to new server.

share|improve this answer
Log Shipping and Mirroring for a handful, yes. But that would be a nightmare on 50 - 100 databases, I think. – Thomas Stringer Jun 5 at 20:07
@ThomasStringer We have implemented logshipping for more than 70+ databases and it just works fine. We have created a portal client , server and region wise for failing over to secondary servers, so just clicking a button, we can failover one or many client, one or many servers (e.g. Florida to London, HongKong to New York) or entire region (e.g. All servers in New York failover to UK due to Hurricane Sandy). In the background the .Net application executes tail log backups and restores to secondary server and then restore the database with recovery. – Kin Jun 5 at 20:16
My point exactly. You had to basically create another management tool to make that feasible. Sure, this can be done with a long and complicated script, or another programmatic solution (like yours), but I'm not sure if that'd be an initial recommendation to the OP. Just my opinion, your answer and thoughts are more than valid. – Thomas Stringer Jun 5 at 20:23
@ThomasStringer Agreed that when you have to maintain such large environment, you need your own custom tools that does the manual work for you on the top of whats provided out of box by Microsoft. But for one time move of databases, you dont need such .. just basic powershell or TSQL will do the job. As always, the OP needs to test, test and test the solution that he chooses to go with. – Kin Jun 5 at 20:31
@ThomasStringer though, the question doesn't say all 50-100 have to be migrated at the same time; it's possible that mirroring could be used for handfuls of databases at a time. And you don't necessarily have to limit the number you mirror at a time to Microsoft's SWAG (10 last I checked), depending on volume etc. – Aaron Bertrand♦ Jun 6 at 3:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.