Moving to SQL 2014 Server

  • Like2SQL

    SSCommitted

    Points: 1662

    Our team needs to move databases (including VLDB's), jobs etc. off some SQL 2008 servers to servers running 2014. What is a good resource to help me chart out our plans and how to do this? As far as BOL or the Microsoft resources go, they currently have an "Upgrade to SQL 2014" section, but most of it seems to refer to upgrading to 2014 on the same server/instance. (Or did I get that wrong?). Can somebody point me in the right direction (or provide some guidance)?

    Thanks!

  • Grant Fritchey

    SSC Guru

    Points: 396558

    I'd start any upgrade with the Microsoft SQL Server Upgrade Advisor. You want to know if you've run into issues with that before you do anything else. TechNet has a detailed upgrade document with a large number of links and variations on the different upgrade paths. I'd start there and see if you run into issues with anything after you've read through that.

    Basically, if you're talking about a side-by-side upgrade rather than in place, the plan is the same. Run Upgrade Advisor and deal with any issues raised. Run a backup on the 2008 system. Run a restore on the 2014 system. Run a consistency check. Update the stats. Update the compatibility level. No other major tricks really.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • EdVassie

    SSC Guru

    Points: 60272

    Grant has covered the main things.

    Definitely do this as a side by side upgrade. An In-Place upgrade is mainly for folks who like playing Russian Roulette.

    You say you are coming from SQL2008, so you are likely running on older hardware that does not support SLAT instructions. This should therefore be an opportunity to upgrade your hardware - the new stuff will cost maybe 25% of what you paid for servers pre 2010, have much more CPU power, and cost less to run. The SLAT instructions give a small performance boost in their own right, but are increasingly important for some of the newer features to run at top speed, and also for efficient virtualisation.

    If you are worried about downtime when you make the switch, then consider linking the new and old servers using SQL P2P replication. We did this when we moved our production infrastructure to AWS. Having your data in sync on multiple servers helps reduce risk. We coupled this with a weighted load balancer (AWS Route53 in our case) where we could control how much traffic went to each set of servers. We were able to ramp up our traffic to the new servers (and ramp it back for a few hours when a problem appeared), and the final 100% go-live on the new servers happened during prime hours without our customers realising what had happened.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Summer90

    SSC-Dedicated

    Points: 32830

    To go from SQL2008 to 2014 you cannot restore master and msdb from one version to another. So, if you are going from one server to another for different releases you will need to script out all of the logins and jobs. Also, if you have any SSIS packages they will have to be dealt with a different way as well.

    Basically step one after you have SQL2014 installed is to create empty database names for all of the databases you plan to move. After that run the below code in your SQL2008. The output of that run in SQL2014. Then backup/restore your dbs from 2008 to 2014. Then, rebuild all indexes for all of your dbs you restored so the SQL2014 optimizer can make use of the indexes efficiently. Run DBCC CHECKDB on all databases. Then you will have to recreate jobs and any SSIS packages if you have any.

    And TEST, TEST, TEST... make sure you have a test environment and test the upgrade a few times. Also, you will probably want to go into the properties for each database and change the compatibility level to SQL2014.

    Here is the login script:

    -- use this to script out logins w/passwords and the server roles associated with logins.

    -- http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5486ec6b-ee6f-47fb-b1e0-61ba731ad970/migration-of-sql-server-roles-from-one-server-to-another-server-for-all-users-sqlserver-2008-?forum=transactsql

    /* ********************************************************************

    ** CHANGE HISTORY

    ***********************************************************************

    ** Date: Author: Descriptiion:

    ** ---------- ----------- --------------

    ** 11-07-2011 Srinivas Sankasani INTIAL Migration of Logins/Server Roles from 2005/2008 to 2005/2008

    ** __________ ____________ ________________________________________

    ** *******************************************************************/

    --

    --###############################[SQL Login]############################

    USE master

    SET NOCOUNT ON

    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,

    @include_db BIT = 0,

    @include_role BIT = 0 AS

    DECLARE @xstatus INT

    --DECLARE @binpwd VARBINARY(256)

    --DECLARE @dfltdb VARCHAR(256)

    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 @DatabaseUserName sysname

    DECLARE @cmd varchar(max)

    DECLARE @SERVERROLE VARCHAR(100)

    DECLARE @MEMBERNAME VARCHAR(100)

    DECLARE @defaultdb sysname

    CREATE TABLE ##SRV_Roles

    (

    SERVERROLE VARCHAR(100),

    MEMBERNAME VARCHAR(100),

    MEMBERSID VARBINARY (85)

    )

    IF (@login_name IS NULL)

    DECLARE login_curs CURSOR STATIC 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

    IF @include_db = 1

    BEGIN

    PRINT ''

    PRINT ''

    PRINT ''

    PRINT '/***** SET DEFAULT DATABASES *****/'

    FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @defaultdb + ']'

    --SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'

    PRINT @tmpstr

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    END

    END

    IF @include_role = 1

    BEGIN

    PRINT ''

    PRINT ''

    PRINT ''

    PRINT '/***** SET SERVER ROLES *****/'

    Print '--BEGIN ************************************'

    /*GET SERVER ROLES INTO TEMPORARY TABLE*/

    SET @CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'

    INSERT INTO ##SRV_Roles EXEC (@CMD)

    DECLARE SERVER_ROLES CURSOR FOR

    Select SERVERROLE ,

    MEMBERNAME

    FROM ##SRV_Roles

    OPEN SERVER_ROLES

    FETCH NEXT FROM SERVER_ROLES into @SERVERROLE,@MEMBERNAME

    WHILE (@@fetch_status =0)

    BEGIN

    Set @CMD = ''

    Select @CMD = @CMD + 'EXEC MASTER.DBO.sp_addsrvrolemember @loginame = ' + char(39) + @MEMBERNAME + char(39) + ', @rolename = ' + char(39) + @SERVERROLE + char(39) + char(10) + 'GO' + char(10)

    --from ##SRV_Roles --where MemberName = @DatabaseUserName

    Print '--Login:' + @MEMBERNAME

    Print @CMD

    FETCH NEXT FROM SERVER_ROLES into @SERVERROLE,@MEMBERNAME

    END

    CLOSE SERVER_ROLES

    DEALLOCATE SERVER_ROLES

    Drop table ##SRV_Roles

    END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

    GO

    exec sp_help_revlogin @login_name=NULL, @include_db=1, @include_role=1

    GO

  • Like2SQL

    SSCommitted

    Points: 1662

    I appreciate all the replies guys. Markus, thanks a lot of the script also. Please keep adding whatever you seniors feel will help. Thanks!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    EdVassie (6/18/2014)


    Grant has covered the main things.

    Definitely do this as a side by side upgrade. An In-Place upgrade is mainly for folks who like playing Russian Roulette.

    Prefer rubber bullets:-P

    Got bruised but not burned in the exercise;-)

    😎

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply