June 17, 2014 at 11:40 am
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!
June 17, 2014 at 12:10 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2014 at 3:51 am
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
June 18, 2014 at 11:33 am
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.
/* ********************************************************************
** 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
June 19, 2014 at 10:38 am
I appreciate all the replies guys. Markus, thanks a lot of the script also. Please keep adding whatever you seniors feel will help. Thanks!
June 21, 2014 at 4:41 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply