DATABASE MIGRATION

Lower version of Microsoft SQL Server to Higher version of SQL Server.

                                      A complete DBA Guide with Automated Scripts and Steps

Introduction:

I have seen lot of DBA’s  spending much of their time in making migration plans and even while implementing such changes in their customer environment. As Microsoft is releasing newer versions of SQL Servers frequently, based on business and customer requirements, in the same phase, application services has to be migrated  to meet-up customer requirement and evade End of Life risks. Still many clients are using SQL Server 2000 (n 2005) versions in their environment, which have to be upgraded in coming days. Here I will be providing the easiest solution for migrating a Lower version of SQL instance databases to a higher version of SQL Instance in faster way with minimum downtime.

There are many ways and methods to migrate these legacy databases, few consider backup and Restore strategy for moving  databases from one SQL instance to another, but it’s a time consuming process, and it works well when we have few databases in the migration scope. What if we have to migrate 50+ or 100+ user databases from one SQL instance to another SQL instance, well, the best possible method and solutions are outlined over here to ease the administrator activity.

To minimize the application downtime, we should plan well in advance with all the pre-requisites and complete implementation and rollback scripts ready for executing during this activity. Try to avoid using GUI to make any configuration changes for an instance or to a database. Well, without any further enlightenment, let's get into the steps - I will be providing more notes and comments in each section as we go....

Assume that we are migrating databases from SQL Server 2005 to 2012\ 2014; the steps will remain same though you migrate databases from SQL 2000 to 2008 or R2.

Please read carefully and follow each step -

Pre-Migration checklist:

1.    Make sure destination server has enough disk space for copying all database files.

2.    Make sure required ports are opened for the application server.

3.    Make sure that SQL Server collation is either same or as per the application or vendor requirement.

4.    If any of the database is using Fulltext Seach feature, refer - additional references at end of this article.

5.    DTS Package migrations  are not covered here, refer - additional references at the end of this article.

6.    Only single LDF file is considered for attaching  script on destination server, in-case you have multiple log files, either remove it  or manually include it in the output script. Multiple NDF's are considered without any issues in all the scripts.

7.    You should read all the notes from each section (and scripts for updating few parameters).

8.    Update the parameters ( destination server name,  share folder, compatibility level etc) as applicable for your environment and requirement in the migration activity.

Important Note: Scripts 1 to 8 (and STEP2) should be executed well in advance and saved, and If requires make necessary changes in the scripts and query output as per your environment and requirement.

Table 1:

  *Scripts used for rollback as well.              **Well in advance.              ***During actual change.

STEP 1:

1.  Take SQL instance and database configuration details from the source server and save the results in an excel file for reference during migration activity.

Sheet 1 -  

                 sp_helpdb

Sheet 2 -  

                 select  * from master.sys.master_files

Sheet 3 -  

                 select * from master.sys.configurations -- To run on SQL 2005 and above.

                 sp_configure -- To run on SQL 2000 (enable show advanced options and rerun).

Sheet 4 -

                 SELECT * FROM master.sys.sysfulltextcatalogs

STEP 2:

Script out all logins, jobs, linked servers, operators, any user procedures on system databases, SQLMail or Database mail etc.

For scripting out logins - follow the below links.  

As per the links, create "sp_help_revlogin" procedures on the source server well in advance, and copy the scripted logins.  

Note: Remove system accounts and copy only the required logins to the destination server.

http://support.microsoft.com/kb/918992

http://support.microsoft.com/kb/246133

For scripting out jobs and other objects -

Identify what jobs and other objects should be migrated to the destination SQL Server. To do this, launch SQL Server Management Studio - Go to SQL Server Agent, select Jobs folder -> Click on Object Explorer Details from View Menu. holding ctrl key, select required jobs from the right pane -> After the selection Right click ->Script Job as -> Create To -> New Query Editor Window  or save to a File. Similarly most other objects (such as operators, linked servers, Alerts, etc) can be scripted out to a New Query Window or to a SQL File. Finally the scripted objects should be executed on the destination server using SSMS. 

STEP 3:

The given t-sql scripts [# 1 to # 8] must be executed on source server to generate the implementation scripts well in advance(few days or weeks before), and the same query output (t-sql commands) will be executed on the destination server (and on source server) at the time of actual implementation period.

/*============================================================
Script 1 - Generate script for Detaching databases.
Important Notes:
 
Run the below Query on Source server, to generate the detach commands in the query result window.
Saved query output (t-sql) commands must  be executed on the Source server during your actual change window.
The same sql commands can be used for detaching the database on destination server incase rollback is required.
 
Add the parameter @keepfulltextindexfile='true' incase any of user db is having fulltext seach index catalog. This parameter will be removed next releases.  
 
Detach command wont work in the below scenarios:
 
If the database is used in replication, it must be unpublish.
Before you can detach the database, you must drop all of its snapshots.  
If the database is used in mirroring session, it must be dropped.
A suspect database cannot be detached; before you can detach it, you must put it into emergency mode.
 
 Author : Dathuraj Pasarge
Last Edited : 2nd June 2014
===============================================================*/
declare @dbname varchar(150)
declare @cmd varchar(250)
 
IF CURSOR_STATUS('global','dbcursor')>=-1
BEGIN
CLOSE  dbcursor
 DEALLOCATE dbcursor
 
END
 
declare dbcursor cursor for
select name from master..sysdatabases where dbid>4 and name not in('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks')  -- Include any additional databases in NOT IN clause to exclude the databases from detach.
 
Open dbcursor
Fetch next from dbcursor into @dbname
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
        Begin
        set @cmd='sp_detach_db '+''''+@dbname+'' +''',''true'''
        
        print @cmd
        print 'go'
        End
 
        Fetch next from dbcursor into @dbname
END
 
close dbcursor
deallocate dbcursor
 

Output

/*===============================================================
Script 2 : Generate script - Re-Attaching the databases on source server,
 incase of rollback.
 
Important Notes
----------------------
Run it on the source server, and save the query output for reattaching the databases,  
incase of rollback is required for any unseen issues occured during  
the migration activity.
 
Note: Scripts for Rolling back the change.
 
Author : Dathuraj Pasarge.
Last Edited : 2nd June 2014
================================================================*/declare @dbname nvarchar(450)
declare @cmd nvarchar(1200)
declare @mdf nvarchar(800)
declare @ldf nvarchar(800)
 
DECLARE @serverVersion varchar(50)
select @serverVersion = CONVERT(varchar(50),SERVERPROPERTY('ProductVersion'))
SET @serverVersion = LEFT(@serverVersion, CHARINDEX('.', @serverVersion) - 1)
 
 
 
IF CURSOR_STATUS('global','dbcursor')>=-1
BEGIN
CLOSE  dbcursor
 DEALLOCATE dbcursor
 
END
 
If  convert(int,@serverVersion)<9
    
BEGIN
declare dbcursor cursor for
 
select name from master..sysdatabases where dbid>4 and name not in('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks')
-- Include the db's in NOT IN clause, which are suspect or inaccessible.
 
Open dbcursor
 
Fetch next from dbcursor into @dbname
 
WHILE @@FETCH_STATUS = 0
BEGIN
    Begin
set @mdf = RTRIM((select [filename] from master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=1))
set @ldf= RTRIM((select [filename] from master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=2))
 
set @cmd='sp_attach_db '+''''+@dbname+''','+ ''''+@mdf+''','''+@ldf+''''
 
    print @cmd
    print 'go'
    End
 
Fetch next from dbcursor into @dbname
END
 
close dbcursor
deallocate dbcursor
        END
    ELSE  
If  convert(int,@serverVersion)>=9    
BEGIN
    declare dbcursor cursor for
 
select name from master..sysdatabases where dbid>4 and name not in('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks')
-- Include the db's in NOT IN clause, which are suspect or inaccessible.
 
Open dbcursor
 
Fetch next from dbcursor into @dbname
 
WHILE @@FETCH_STATUS = 0
BEGIN
    Begin
 
set @mdf = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=1
and type_desc='ROWS'
)
set @ldf = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=2
and type_desc='LOG')
 
set @cmd='sp_attach_db '+''''+@dbname+''','+ ''''+@mdf+''','''+@ldf+''''
 
print @cmd
    print 'go'
    End
 
        Fetch next from dbcursor into @dbname
    END
close dbcursor
deallocate dbcursor
END

Output

/*==========================================================
Script 3 : Ge