888-685-3101, ext. 2

SQL Server Migration and Upgrade Strategies

by | Feb 17, 2016 | Blog, Databases

Abstract

Upgrading or migrating your SQL Server can seem like a very daunting task. And it certainly can be especially when we start to look at hardware requirements, size and space needs, time constraints, and the numerous impacts to the the users and applications that utilize the data. The end goal is always the same, get the data to the new server with as little downtime as possible. Here we will explore some of the more common migration and / or upgrade situations.

Methods

In Place Upgrade

The In Place Upgrade is often considered when the only need is to upgrade your current instance of SQL Server to a newer version. It is strongly recommended that this method not be used as the negative points of its implementation far outweigh the positive gains.

Pros

  • Easy to perform
  • No additional hardware required
  • No need to move large files around
  • No need to reconfigure applications to point to new server name

Cons

  • Potential for longer downtime
  • Errors could result in a non-functioning instance
  • No ability to test prior to upgrade
  • No simple ability to rollback

Considerations

  • The SQL Server Edition can not be downgraded at this time
  • The version you are upgrading from must be able to be upgraded to the desired version being upgraded to.
    • SQL Server 2000 would need to be upgraded to SQL Server 2005 before being upgraded to SQL Server 2012

Perform an In Place Upgrade

  1. Backup all databases to a different location
  2. Backup the Server Level Login Accounts
    1. https://support.microsoft.com/en-us/kb/918992
  3. Ensure that all prerequisite software is installed on the target server
    1. Newer versions of SQL Server may have additional or different requirements than previous versions
  4. Use the installation media to run the upgrade
    1. Select install from left menu
    2. Select the upgrade menu option
    3. Continue to follow through the setup wizard
  5. Validate the upgrade was successfull
  6. Remove old version of SQL Server

Rollback an In Place Upgrade

  1. If the migration failed and all changes were rolled back successfully and the instance of the older version start then verify the applications supported by the database are still functioning.
  2. In the event that the old instance does not come back online it may be required to repair the instance again using the installation media
  3. If a repair fails to bring the instance back online, it should be uninstalled and re-installed.
  4. Once the instance is running you may or may not need to restore the databases and server logins, use the backups / scripts from steps 1 and 2 of Perform an In Place Upgrade process.

Side by Side Migration / Upgrade

The Side by Side migration / upgrade method is the preferred method to migrate to a new server and/or upgrade your version of SQL Server. This method involves configuring a new database server which will run in unison with the current running SQL Server source.

Pros

  • Very easy to rollback
  • Detailed control of migration
  • No modifications to original instance
  • Migration can be phased and staged
  • Shorter down times for applications
  • Can easily test prior to migration

Cons

  • Requires additional hardware and resources
  • Project timelines tend to be much longer

Perform a Side by Side Upgrade

  1. Install and Configure new instance
  2. Take backups of source instance databases
  3. Backup the Server Level Login Accounts
    1. https://support.microsoft.com/en-us/kb/918992
  4. Restore backups to new instance
  5. MIgrate server level logins to the new instance
  6. Test applications against new instance
  7. Migrate applications to the new instance
    1. Backup source instance and restore to target instance
    2. Point application to new database instance
    3. Test application
    4. Set source database to offline (ensures no connections can be made)
    5. Repeat for each database / application
  8. Once all databases / applications that will be migrated are completed set the original instance SQL Server service to Disabled and stop the service

Rollback Side by Side Migration

Rolling back a side by side transaction is easy to do as we have taken a phased approach to migrating the databases.

  1. Set the source server database back into ONLINE mode
  2. Point the application back to the original SQL Server
  3. Test application

Variations

Dealing with Large Databases

Typically simple backup and restores work great for most smaller databases, when a larger database is encountered it may not be as easy, fast, or even be possible to simply backup and restore the database to its new home. In cases where there are large databases and the downtime during migration needs to be minimal we use SQL Server replication technologies to help us perform the migration. Typically a simple mirroring configuration will be acceptable to get this job done.

  1. Restore a Full backup of the database to the target server, leaving it in RESTORING mode
  2. Configure mirroring on the source database to the target database, no witness will be required
  3. When it does come time to migrate the database break mirroring, take the target database out of RESTORING
  4. Finally configure the application to connect to the new target database

Using the same name

Many people are opposed to changing the name of their instance which is why many will choose to perform an in place upgrade where this is not an issue. It is possible however to perform the side by side migration and ultimately use the original instance name. The downside to this is that it will likely require a much longer amount of downtime. This is because all the databases will need to be up to date and the old instance must be taken offline.

One option is a simple DNS change to point the old name to the new IP address. While this isn’t the preferred method it will get the job done quickly. Flushing the DNS of clients may be required for the DNS change to be immediate.

The other option involves changing the name of the target server to the name of the source server. This first requires that the old instance name be deleted from Active Directory and once the name is changed the SQL Server instance must be updated by using the following:

sp_dropserver <old_name>;
GO
sp_addserver <new_name>,local;
GO

Migrate SQL Server Instance Logins

Microsoft has provided a script which in turn generates a script to re-create the instance level logins on a new server. The scripts to perform this action are not included with SQL Server and can be retrieved from https://support.microsoft.com/en-us/kb/918992

This procedure should only be performed by a trained professional

  1. Use the following code to create the necessary stored procedures. This should be done on the source instance.
    2. USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue

GO 

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name,l.hasaccess, l.denylogin FROM sys. server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN  ('S', 'G', 'U') AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ('S', 'G', 'U') AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@fetch_status = - 1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '

PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb< + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name 
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME(@name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' +  QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME(@name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
  2. Run the following code to output the logins EXEC sp_help_revlogin
  3. Remove any unnecessary accounts from the output and execute the generated script on the target