Moving SQL Server database from old production server to new server

Question

We had single production server for website and SQL Server database. We have plan to have a new production server for website and SQL Server. We need to move the SQL Server database from the old server to the new server.

So, how can I migrate/move the DB to new production server with less down time?

Thanks, Velu.

Answer 1

If you use SQL Server 2005/2008 you can use mirroring:

• install new SQL Server instance
• create mirror database on this server from production server
• wait until databases are synchronized
• turn old server off
• remove mirror from new server
• recover mirrored database

Downtime = time to remove the mirror and recover the database

If you don't want to use mirroring, you can do the same work without mirroring:

• install new SQL Server instance
• create backup from production DB
• copy and restore the production DB WITH NORECOVERY option on new server
• turn the production database into single-user mode
• make the transaction log backup
• turn production server off
• copy and restore the log WITH RECOVERY option on new server

Downtime = time to make log backup, copy and restore this backup on new server

Answer 2

Depends - how much do you know?

One way would be setting up mirroring, then switching the mirror and removing the older server. Mirroring, otoh, is a little deeper into sq server than using the UI to set up a database.

Answer 3

I know the below isn't the most efficient or advanced way to do thing - but I do think it is a way which almost anyone can do with minimal help and little downtime...

Assuming you're talking about a database for a CMS, you could you get the temporary URL for the new location of the website on the new server (we use external hosting, and they tend to set this up - the temporary URL is normally a suffixed ip address). Then go through the database replacing values referring to the old URL to the new, temporary one.

You can then restore the site on the new server (or whatever it is) also, replacing values for the new URL. This gives you the opportunity to test the database is functioning. When you are happy, you can just change the DNS (or A records or whatever), so the original URL points to the new server, change everything back (which should only take a minute or two with good find and replace procedures) and bobs your uncle. Its not the fastest way to make the transfer, but it should provide the least downtime to your site and peace of mind that it will all work.

What CMS are you using?

Answer 4

If the database files are on a LUN by themselves (or with other files that you don't mind moving as well), you could detach the LUN from the old server and attach them to the new. Then you'd do a "create database... for attach" on the new side.

Also, I've grown to like the idea of one node clusters for future needs. That way if you need new hardware, you can add a node into the cluster, run the node setup and you're in business. This assumes SQL 2008+, though.