July 17, 2014 at 3:46 am
Hi Team,
Currently we have a database in SQL Server 2005.
Decided to move that DB into a new VM where we need to install sql server 2008 R2.
We will backup the DB from the source server and restore in the destination server.
By any chance has anyone prepared a properly formated checklinst for the steps we need to carry out ?
Since its a prod server, I dont want to miss even small things.
Thanks in advance.
Smith.
July 17, 2014 at 3:52 am
Joy Smith San (7/17/2014)
Hi Team,Currently we have a database in SQL Server 2005.
Decided to move that DB into a new VM where we need to install sql server 2008 R2.
We will backup the DB from the source server and restore in the destination server.
By any chance has anyone prepared a properly formated checklinst for the steps we need to carry out ?
Since its a prod server, I dont want to miss even small things.
Thanks in advance.
Smith.
A good place to start would be the Microsoft SQL Server 2008 Upgrade Advisor found in the Microsoft® SQL Server® 2008 R2 Feature Pack
😎
July 17, 2014 at 4:27 am
ensure you transfer any logins that may be required for the db
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2014 at 5:35 am
Basically, run through the Object Explorer in SSMS. You have the database backup, so that's done. So, Security, logins & roles next. Then Server Objects, usually just linked servers, but check everything. Management, policies and resource governor and all the rest. Finally, SQL Agent with it's jobs and users and schedules. The layout there provides you with your checklist. Also, each of those objects can usually be scripted, so you shouldn't have to many issues or miss too many things.
"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
July 17, 2014 at 7:51 am
Don't forget any deprecated features or code. (And any DTS packages!)
There is a Technical Upgrade to SQL 2008 R2 ref guide here.
Edit: since you going P2V, you may want to work closely with your storage/vm guy, and take a baseline of existing 2005 db/instance before you move.
qh
July 17, 2014 at 8:17 am
Thanks a lot all, all point noted. Your suggestions matches with the checklist I have prepared, so am on right track 🙂 . Thanks once again.
July 18, 2014 at 12:36 am
Just one more thing. Any sample WBS template for this acitvity ?
Thanks.
July 18, 2014 at 2:12 am
Joy Smith San (7/18/2014)
Just one more thing. Any sample WBS template for this acitvity ?Thanks.
Hi Joy
A quick google search brings up plenty of samples (Office/Excel templates) that you can tweak for your own migration/upgrade.
qh
July 18, 2014 at 6:19 am
Also, add a step to update your documnetation as part of the move. 🙂
September 2, 2014 at 1:16 am
This is how I do it, if anyone sees any mistakes I am making, please point them out. Of course, start with making a backup.
1. On the production server, run this script to aid you in exporting the users from the database:
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
2. Backup your databases from your production server and restore them on your new server.
3. On the original production server, start a new query and run the command "EXEC sp_help_revlogin" (without the "")
4. Save the output from that query (will contain all users and passwords, encrypted), paste it into a new query on the new server and run it.
5. Run this query against your newly restored databases to check that the accounts are ok. If the query returns any lines, they are not ok.
sp_change_users_login 'Report'
6. For all the accounts that are displayed in the previous query, run this query: sp_change_users_login 'update_one', 'the_username', 'the_username'
Substitute "the_username" for your actual username.
7. Test access to the database by logging into it with the various accounts you have. Create backupjobs, etc.
8. Done!
As I stated earlier, if anyone has any tips on how to make this method better, please tell.
September 2, 2014 at 3:10 am
mr_belpitt (9/2/2014)
sp_change_users_login 'update_one', 'the_username', 'the_username'Substitute "the_username" for your actual username.
Don't take this for granted, the server login and database user won't necessarily have matching names.
It's possible to have a login Fred map to a database user Bob 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 2, 2014 at 3:17 am
Perry Whittle (9/2/2014)
mr_belpitt (9/2/2014)
sp_change_users_login 'update_one', 'the_username', 'the_username'Substitute "the_username" for your actual username.
Don't take this for granted, the server login and database user won't necessarily have matching names.
It's possible to have a login Fred map to a database user Bob 😉
Excellent point, to fix that is a bit out of my league at the moment 🙂
September 2, 2014 at 4:30 am
mr_belpitt (9/2/2014)
Perry Whittle (9/2/2014)
mr_belpitt (9/2/2014)
sp_change_users_login 'update_one', 'the_username', 'the_username'Substitute "the_username" for your actual username.
Don't take this for granted, the server login and database user won't necessarily have matching names.
It's possible to have a login Fred map to a database user Bob 😉
Excellent point, to fix that is a bit out of my league at the moment 🙂
Move away from sp_change_users_login, the best way is to use
Use [yourdb]
GO
ALTER USER [bob] with login = [Fred]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply