May 15, 2014 at 8:33 am
Hi,
We are upgrading the sql server 2008 database server to SQL Server 2012.
For this just restoring DB backups from 2008 server onto 2012 is good enough for the upgrade or do we need to run any upgrade tool like upgrade advisor?
This upgrade is build a brand new sql server and move 2008 DBs to the new 2012 server , not upgrading the existing server.
what are the other things to consider as part of the upgrade?
Thanks.
May 15, 2014 at 8:51 am
You will need to create the logins for the database yourself. I think I saw an article about that on the front page here, today.
Once the database has been restored, you will want to change the compatibility level to 110, and update the statistics on all of the tables.
So long as you don't have replication, CDC, or database mirroring/log shipping going on in this database, you should be all set.
May 15, 2014 at 9:01 am
I have replication and log shipping setup. So I am planning to create them back on new server as in the existing server.
Thanks.
May 15, 2014 at 9:02 am
Here is a script that you can use to sync your users back. You will have to add them manually to the new instance under Security. Once the DB is restored use this script for your users and the permissions will sync back up as they were. This will save you TONS of time and keep your permissions straight. Good Luck
sp_change_users_login update_one, username, username
MCSE SQL Server 2012\2014\2016
May 15, 2014 at 11:24 am
Thanks guys for your help.
May 15, 2014 at 11:31 am
I have one more question. say we restore the DB on new server at 1 PM based on 1 PM full backup from Old Server on Monday and if the old server is still getting the data after the last full back and if I needed to shutdown the old server at 1 PM on Tuesday and make the new server live, how can I transfer the data from 1 PM Monday to 1 PM Tuesday from old server to new server?
Does the transaction log backups from that time period will help for the cause?
THanks,
Hari.
May 15, 2014 at 11:35 am
I would not recommend switching over while production is going on. This is an at night when people are sleeping change. I would backup the DB, detach it to assure no more transactions, restore, sync users, test application. Or you could do it during the day if you want, but no users can be access the data unless you want to move it asynchronously. 🙂
MCSE SQL Server 2012\2014\2016
May 16, 2014 at 3:30 am
I would agree with the post above.
But i had to do that.
We did a full backup on the old server, restored it to the new server and left it in recovery mode. every 6 hours, we copied the log backups over to the new server and applied them. When the old server was finished, final log backup, copy/apply and recover.
If you are not in full mode with log backups; sql 2008r2 can replicate to sql server 2012.
May 16, 2014 at 6:45 am
That's another way to do it Joseph. If the DB is in Full Recov Mode then you can do log shipping until they are done with the old server. Replication is always another option if you have the DB in Simple Mode.
sql_novice_2007,
Just remember that if you start switching the mode from full to simple and then back you ruin all backups previously done and have to start the sequence all over again. Good Luck with your move!
MCSE SQL Server 2012\2014\2016
May 16, 2014 at 12:45 pm
lkennedy76 (5/15/2014)
Here is a script that you can use to sync your users back. You will have to add them manually to the new instance under Security. Once the DB is restored use this script for your users and the permissions will sync back up as they were. This will save you TONS of time and keep your permissions straight. Good Lucksp_change_users_login update_one, username, username
You should not be using sp_change_users_login as "This feature will be removed in a future version of Microsoft SQL Server." ALTER user userName WITH LOGIN = loginName
Also, this is not needed of you use sp_help_revlogin to generate the logins, passwords, and sids.
Jared
CE - Microsoft
May 16, 2014 at 12:52 pm
sql_novice_2007 (5/15/2014)
Hi,We are upgrading the sql server 2008 database server to SQL Server 2012.
For this just restoring DB backups from 2008 server onto 2012 is good enough for the upgrade or do we need to run any upgrade tool like upgrade advisor?
This upgrade is build a brand new sql server and move 2008 DBs to the new 2012 server , not upgrading the existing server.
what are the other things to consider as part of the upgrade?
Thanks.
I would still run the upgrade advisor. We also run an extended event for getting deprecated features. I am working on a blog post about this now:
USE myMonitoringDatabase;
GO
--Create the event
DECLARE @file1 VARCHAR(128)
DECLARE @file2 VARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)
SET @file1 = (SELECT SUBSTRING(physical_name, 1, PATINDEX('%\data\myMonitoringDatabase.mdf', physical_name)) + 'LOG\Monitor_Deprecated_Discontinued_features.xel'
FROM master.sys.master_files
WHERE db_name(database_id) = 'myMonitoringDatabase' and file_id = 1)
SET @file2 = (SELECT SUBSTRING(physical_name, 1, PATINDEX('%\data\myMonitoringDatabase.mdf', physical_name)) + 'LOG\Monitor_Deprecated_Discontinued_features.xem'
FROM master.sys.master_files
WHERE db_name(database_id) = 'myMonitoringDatabase' and file_id = 1)
SET @SQL =
'IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=''Monitor_Deprecated_Discontinued_features'')
BEGIN
ALTER EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER
STATE = STOP;
DROP EVENT session Monitor_Deprecated_Discontinued_features ON SERVER;
END;
EXEC xp_cmdshell N''del ' + REPLACE(@file1,'.xel', '*') + '''
CREATE EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER
--We are looking at deprecated features
ADD EVENT sqlserver.deprecation_announcement
(
--Add additional columns to track
ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id,
sqlserver.tsql_stack, sqlserver.username, sqlserver.client_hostname)
),
--We are looking at discontinued features
ADD EVENT sqlserver.deprecation_final_support
(
--Add additional columns to track
ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id, sqlserver.tsql_stack))
--As a best practice use asynchronous file target, reduces overhead.
ADD TARGET package0.asynchronous_file_target(
SET filename=''' + @file1 + '''
, metadatafile=''' + @file2 + '''
, max_file_size = 5
,max_rollover_files = 5)
WITH(MAX_DISPATCH_LATENCY = 1 SECONDS, STARTUP_STATE = ON
)
--Now start the session
ALTER EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER
STATE = START;'
--PRINT @SQL
EXEC sp_executesql @SQL
GO
CREATE PROCEDURE [dbo].[usp_logDeprecatedFeatures]
AS
BEGIN
BEGIN TRY
DECLARE @filename1 VARCHAR(255);
DECLARE @filename2 VARCHAR(255);
SELECT @filename1 = REPLACE(CAST(esf.value AS VARCHAR(255)), '.xel', '*xel')
FROM sys.server_event_sessions es
INNER JOIN sys.server_event_session_fields esf
ON es.event_session_id = esf.event_session_id
WHERE es.name = 'Monitor_Deprecated_Discontinued_features'
AND esf.name = 'filename';
SELECT @filename2 = REPLACE(CAST(esf.value AS VARCHAR(255)), '.xem', '*xem')
FROM sys.server_event_sessions es
INNER JOIN sys.server_event_session_fields esf
ON es.event_session_id = esf.event_session_id
WHERE es.name = 'Monitor_Deprecated_Discontinued_features'
AND esf.name = 'metadatafile';
CREATE TABLE #temp (EventName NVARCHAR(50), Feature NVARCHAR(500), MessageTxt NVARCHAR(500),
DatabaseId NVARCHAR(50), SQLText NVARCHAR(500));
INSERT INTO #temp
SELECT DISTINCT FinalData.R.value ('@name', 'nvarchar(50)') AS EventName,
FinalData.R.value ('data(data/value)[1]', 'nvarchar(500)') AS Feature,
FinalData.R.value ('data(data/value)[2]', 'nvarchar(500)') AS MESSAGE,
FinalData.R.value ('(action/.)[1]', 'nvarchar(50)') AS DatabaseID,
FinalData.R.value ('(action/.)[2]', 'nvarchar(50)') AS SQLText
FROM ( SELECT CONVERT(XML, event_data) AS xmldata
FROM sys.fn_xe_file_target_read_file(@filename1, @filename2, null, null)) AsyncFileData
CROSS APPLY xmldata.nodes ('//event') AS FinalData (R)
WHERE FinalData.R.value ('data(data/value)[1]', 'nvarchar(500)') <> 'Deprecated encryption algorithm'
INSERT INTO log_deprecatedFeatures
SELECT t.EventName , t.Feature, t.MessageTxt,
t.DatabaseId , t.SQLText
FROM #temp t
LEFT JOIN log_deprecatedFeatures ldf
ON t.EventName = ldf.EventName
AND t.Feature = ldf.Feature
AND t.MessageTxt = ldf.MessageTxt
AND t.DatabaseId = ldf.DatabaseId
AND t.SQLText = ldf.SQLText
WHERE ldf.EventName IS NULL
END TRY
BEGIN CATCH
IF(SELECT ERROR_NUMBER()) = 9420
BEGIN
SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END
ELSE
BEGIN
DECLARE @errormessage NVARCHAR(255)
DECLARE @errorseverity INT
DECLARE @errorstate INT
SELECT @errormessage = ERROR_MESSAGE(),
@errorseverity = ERROR_SEVERITY(),
@errorstate = ERROR_STATE()
RAISERROR(@errormessage, @errorseverity, @errorstate)
END
END CATCH
END
You'll also need a table to hold the data above. When you do this "side-by-side" there are several other things you will want to do, migrate jobs, ssis packages, etc. If you need a full checklist for the night of migration, search the internet or ask and I can send you something generic.
Jared
CE - Microsoft
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply