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.
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.
- 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
- Potential for longer downtime
- Errors could result in a non-functioning instance
- No ability to test prior to upgrade
- No simple ability to rollback
- 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
- Backup all databases to a different location
- Backup the Server Level Login Accounts
- Ensure that all prerequisite software is installed on the target server
- Newer versions of SQL Server may have additional or different requirements than previous versions
- Use the installation media to run the upgrade
- Select install from left menu
- Select the upgrade menu option
- Continue to follow through the setup wizard
- Validate the upgrade was successfull
- Remove old version of SQL Server
Rollback an In Place Upgrade
- 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.
- 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
- If a repair fails to bring the instance back online, it should be uninstalled and re-installed.
- 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.
- 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
- Requires additional hardware and resources
- Project timelines tend to be much longer
Perform a Side by Side Upgrade
- Install and Configure new instance
- Take backups of source instance databases
- Backup the Server Level Login Accounts
- Restore backups to new instance
- MIgrate server level logins to the new instance
- Test applications against new instance
- Migrate applications to the new instance
- Backup source instance and restore to target instance
- Point application to new database instance
- Test application
- Set source database to offline (ensures no connections can be made)
- Repeat for each database / application
- 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.
- Set the source server database back into ONLINE mode
- Point the application back to the original SQL Server
- Test application
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.
- Restore a Full backup of the database to the target server, leaving it in RESTORING mode
- Configure mirroring on the source database to the target database, no witness will be required
- When it does come time to migrate the database break mirroring, take the target database out of RESTORING
- 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
- Use the following code to create the necessary stored procedures. This should be done on the source instance.
- Run the following code to output the logins
- Remove any unnecessary accounts from the output and execute the generated script on the target
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