Sql-server – Syncing Database for Server Transition

migrationsql serversql-server-2005sql-server-2008-r2

I'm transitioning from an old web/sql box combined to a much more robust solution on a completely different host (800hosting to RackSpace).

Our application has very high uptime requirements. It runs 24x7x365 and impacts literally thousands of sites, and I want to ensure the minimum possible downtime during the transition. (Don't ask how we're pulling this off with a single box right now.)

My plan for the web server is a little bit of DNS work to point a new subdomain to the new server setup, and forward requests from the existing server. That'll take care of flipping everyone to the new web box fast enough. The problem is the database – how do I keep it in sync until I'm ready to flip the switch on the website (or is it worth the effort vs. just accepting the downtime to stop the app, backup, compress, transfer, and restore?).

A few details. We're running Sql2k5 on the old system, 2k8r2 on the new. The database itself is ~30 gigs for the primary, ~60 gigs for the warehouse. I can live with downtime on the warehouse, but really want to minimize the impact on the main database.

Any suggestions for the best way to migrate the database across from the old setup to the new?

Best Answer

S1 = old server, S2 = new server

Full Backup S1
Restore to S2 and with NOrecovery
Block ALL data changing connections to S1 (e.g. change web connection password)
Diff backup on S1
Restore diff to S2 with recovery - S2 will now be ready
Enable connections to S2 ( don't forget to reset any changed passwords back to the originals)

Related Solutions

Sql-server – Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box

For logins, this is better IMHO:

http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

And for jobs, you don't need SSIS. Just highlight the Jobs node in Management Studio, click on Object Explorer Details (F7), in the right pane select all, right-click and script.

enter image description here

I'd do the logins first, in case any of the jobs involve any of the logins.

Sql-server – Migrate database from SQL Server 2005 to SQL Server 2008 R2

In order.

Some easy options: You could look at the start->run services.msc and see which services are there; you could run powershell and look at run gsv *SQL*; or you could look at the Start menu->Microsoft SQL Server 2008 R2 and see what's there.
If you're a database newbie, I would take a look at the excellent database maintenance scripts from Ola Hallengren. These cover backups, index maintenance and database integrity checks.
I would migrate the database by taking a backup of the old database and restoring that to the new server - this will bring all objects, tables, data, stored procedures etc. However, you'll then have to migrate any appropriate scheduled tasks and user IDs. There are some SQL Server Integration Services tasks that'll help with jobs and logins (indeed, they'll also help with the data migration...).

Lastly, welcome to the job. I would recommend that you have a trawl through Red Gate's bookstore - there are a lot of free PDF versions of their books. Have a skim of SQL Server Backup and Restore, Troubleshooting SQL Server for the Accidental DBA.

Good luck!

edit. A pair of thoughts, relating to your migration from 2005 to 2008 R2 specifically.

You should be OK doing this by using database backup and restore - good news.
However, be aware that it is entirely possible that some features your app relies on may be deprecated / discontinued. Once you get up & running on SQL Server 2008 R2, you can keep an eye out for these features using the sys.dm_os_performance_counters DMV. There are various blog posts out there, but the gist is:
```
SELECT * FROM sys.dm_os_performance_counters
WHERE Object_name like '%:Deprecated Features'
AND cntr_value > 0
```

(unfortunately, tracking Deprecated Features wasn't part of the SQL 2005 release...)

Related Question