What is the best way to migrate multiple SQL Server databases?

Question

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)?

• Tushar Maru: Affecting clients mean zero down time or minimize down time

Answer 1

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".

• SqlNovice: If it is a dev or test server we can put the server in single user mode and then migrate all the databases

Answer 2

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.

Note: Powershell based dbatools will help in the automation.