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

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

DBAs… please be gentle with me, I'm a 'greenhorn' SQL Server DBA, pressed into service because my company's DBA had resigned and we haven't found his replacement, yet.

First, my situation:

I'm supposed to 'transfer' a web-app from a server in Singapore to a (pair of) newly-built server(s) in Indonesia. Unfortunately, the Singapore DB server is running SQL Server 2005 Express Edition, while the new server is running SQL Server 2008 R2. To make things worse, the developers of the web-app is no longer supporting us (we're practically coasting with the last version of the web-app)*.

I plan on creating a new Instance on the SQL2008R2.

I have several questions that have been bugging my mind:

  1. How do I find out what 'features' (e.g., Reporting Service, Replication, etc.) has been installed on the Singapore server?

  2. How to ensure a complete backup (Databases and Stored Procedures and other relevant tidbits)?

  3. I noted that there are several Stored Procedures (not many, just 4 that I can see under the "Databases\AppDB\Programmability\Stored Procedures" tree), how to ensure successful migration of these Stored Procedures? Any gotchas I need to be aware of?

Thank you for any inputs.

* The plan actually is to just migrate the web-app as a "read-only" app for archival/audit
purposes; management has plans to deploy a better web-app in its place.

Best Answer

In order.

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

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

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

  1. You should be OK doing this by using database backup and restore - good news.

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