Migrating MS-SQL 2008 to 2012

Date [ 07 Nov 14 00:00 UTC ] Categories [ Tech ] Tags [ migration , sql ]

Pre-migration

Update the new servers to have the latest updates for both OS and MS-SQL
Setup Windows Failsafe cluster
Setup Always-On availability group, replicas and listener
Setup shared folder for storing backups from the old database servers
Setup shared folder for storing backup/synchronization data for the new database servers
Create a copy of the stored procedures used by jobs, and update them to work with availability group
Script the SQL server logins
If you want to change the databases to read-only mode, remove existing mirroring functionality for the databases

Migration

Put up App_Offline.htm file for the web servers

Take full backup of the database

1
2
3
4
5
6
7
BACKUP DATABASE [{DatabaseName}]
        TO DISK = N'{BackupSharedFolder}\{DatabaseName}.bak'
        WITH NOFORMAT, 
        INIT, 
        NAME=N'{DatabaseName}-Full Database Backup', 
        SKIP, 
        NO_COMPRESSION;

Take Transactional backup of the database

1
2
3
4
5
6
BACKUP LOG [{DatabaseName}]
        TO DISK = N'{BackupSharedFolder}\{DatabaseName}.log'
        WITH NOFORMAT, 
        INIT, 
        NAME = N'{DatabaseName}-Transactional Backup', 
        NO_COMPRESSION;

Set database to read-only mode

1
ALTER DATABASE [{DatabaseName}] SET  READ_ONLY WITH NO_WAIT;

Connect to the new server

Restore the database

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
DECLARE @v_DefaultDataPath NVARCHAR(250) = CONVERT(NVARCHAR,SERVERPROPERTY('InstanceDefaultDataPath'));
            DECLARE @v_DefaultLogPath NVARCHAR(250) = CONVERT(NVARCHAR,SERVERPROPERTY('InstanceDefaultLogPath'));

            DECLARE @v_FinalDataFilePath NVARCHAR(250) = CONCAT(@v_DefaultDataPath,'\{DatabaseName}.mdf');
            DECLARE @v_FinalLogFilePath NVARCHAR(250) = CONCAT(@v_DefaultLogPath,'\{DatabaseName}_Log.ldf');

            DECLARE @v_LogicalDataFileName NVARCHAR(128), @v_LogicalLogFileName NVARCHAR(128);

            DECLARE @tbl_FileList TABLE
                (
                  LogicalName NVARCHAR(128) ,
                  PhysicalName NVARCHAR(260) ,
                  [Type] CHAR(1) ,
                  FileGroupName NVARCHAR(128) ,
                  Size NUMERIC(20, 0) ,
                  MaxSize NUMERIC(20, 0) ,
                  FileID BIGINT ,
                  CreateLSN NUMERIC(25, 0) ,
                  DropLSN NUMERIC(25, 0) ,
                  UniqueID UNIQUEIDENTIFIER ,
                  ReadOnlyLSN NUMERIC(25, 0) ,
                  ReadWriteLSN NUMERIC(25, 0) ,
                  BackupSizeInBytes BIGINT ,
                  SourceBlockSize INT ,
                  FileGroupID INT ,
                  LogGroupGUID UNIQUEIDENTIFIER ,
                  DifferentialBaseLSN NUMERIC(25, 0) ,
                  DifferentialBaseGUID UNIQUEIDENTIFIER ,
                  IsReadOnl BIT ,
                  IsPresent BIT ,
                  TDEThumbprint VARBINARY(32)
                );

            INSERT  INTO @tbl_FileList
                    EXEC
                        ( 'RESTORE FILELISTONLY FROM DISK = N''{BackupSharedFolder}\{DatabaseName}.bak'''
                        );

            -- Extract the logical data file name from the backup file
            SELECT  @v_LogicalDataFileName = LogicalName
            FROM    @tbl_FileList AS tfl
            WHERE   [tfl].[Type] = 'D';

            -- Extract the logical log file name from the backup file
            SELECT  @v_LogicalLogFileName = LogicalName
            FROM    @tbl_FileList AS tfl
            WHERE   [tfl].[Type] = 'L';

            RESTORE DATABASE [EyeTeaERP] 
                FROM  DISK = N'{BackupSharedFolder}\{DatabaseName}.bak' 
                WITH  FILE = 1,  
                MOVE @v_LogicalDataFileName TO @v_FinalDataFilePath,  
                MOVE @v_LogicalLogFileName TO @v_FinalLogFilePath,  
                NORECOVERY,  
                NOUNLOAD;

            RESTORE LOG [{DatabaseName}] 
                FROM  DISK = N'{BackupSharedFolder}\{DatabaseName}.log' 
                WITH  FILE = 1,  
                NOUNLOAD;

Set the compatibility level matching the new version

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
DECLARE @v_CompLevel INT = 0;
      SELECT  @v_CompLevel = [d].[compatibility_level]
      FROM    [sys].[databases] AS d
      WHERE   [d].[name] = '{DatabaseName}';

      IF @v_CompLevel &lt; 110
          BEGIN
          -- 100 = 2008, 110 = 2012
              ALTER DATABASE [{DatabaseName}] SET COMPATIBILITY_LEVEL = 110;
          END

Add the database to the availability group

1
ALTER AVAILABILITY GROUP [{AvailabilityGroupName}] ADD DATABASE [{DatabaseName}];

Take a full backup and transactional backup of the database to the synch folder share

Connect to each of the secondary replicas

Restore the database from the synch folder share in recovering mode

1
2
RESTORE ... --AS ABOVE EXAMPLE +
            WITH NORECOVERY

Add the database to the availability group

1
ALTER DATABASE [{DatabaseName}] SET HADR AVAILABILITY GROUP = [{AvailabilityGroupName}];

Restore server logins as needed on all replicas
Connect to the availabiliy group and update the stored procedures with the modified copy created for working with Always-On availability group
Setup the database mail, and all operators as needed in all of the replicas
Restore the jobs to all of the replicas

Post-Migration

Update the configuration files connections string to point to the correct data source
Remove the App_Offline.htm file from the web servers
Confirm the failover is working as intended
Confirm the jobs are working as intended

Packaging

Package the above steps through an application created in language of your choice with care to set Connection Timeout in the connection string, and also appropriate CommandTimeout you are all set to go!