Migrating MS-SQL 2008 to 2012

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

  1. Put up App_Offline.htm file for the web servers
  2. Take full backup of the database

    BACKUP DATABASE [{DatabaseName}]
        TO DISK = N’{BackupSharedFolder}{DatabaseName}.bak’
        WITH NOFORMAT,
        INIT,
        NAME=N’{DatabaseName}-Full Database Backup’,
        SKIP,
        NO_COMPRESSION;
    

  3. Take Transactional backup of the database

    BACKUP LOG [{DatabaseName}]
        TO DISK = N’{BackupSharedFolder}{DatabaseName}.log’
        WITH NOFORMAT,
        INIT,
        NAME = N’{DatabaseName}-Transactional Backup’,
        NO_COMPRESSION;
    

  4. Set database to read-only mode

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

  5. Connect to the new server

    1. Restore the database

      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;
      </pre>
      
    2. Set the compatibility level matching the new version

    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
        </code>
    
    1. Add the database to the availability group

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

    1. Take a full backup and transactional backup of the database to the synch folder share
  6. Connect to each of the secondary replicas

    1. Restore the database from the synch folder share in recovering mode

      RESTORE … –AS ABOVE EXAMPLE +
              WITH NORECOVERY
              …
          

    2. Add the database to the availability group

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

  7. Restore server logins as needed on all replicas

  8. Connect to the availabiliy group and update the stored procedures with the modified copy created for working with Always-On availability group

  9. Setup the database mail, and all operators as needed in all of the replicas

  10. 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!

comments powered by Disqus