(888) 685-3101 ext. 2

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
  3. Run the following code to output the logins EXEC sp_help_revlogin
  4. Remove any unnecessary accounts from the output and execute the generated script on the target
Share This