March 12, 2009 at 5:39 am
Hello everyone 🙂
We have an 8 year old SQL server (running 2000-8.0.760) on this server is about 30 databases ranging from a few hundred mb to a few GB, nothing major.
However the server is knackered, and needs upgrading, and as all the databases are relatively simple we bit the bullet and have bought a new server with sql server 2008. Now the fun begins!
These databases contain lots of data used by our payroll dept, cash-sheets, delivery systems, and a few other web-based back-end databases. Over the last 8 years dozens of SQL server user accounts have been created with varying levels of access, used in peoples spreadsheets, DSN's, ODBC links and god only knows what else...
When I joined the company a year ago I have attempted to isolate where these accounts are being used and identify the passwords, however there are still many I have not been able to track down.
Now then, what we would like to do is install a blank fresh copy of SQL 2008, migrate all the databases across as BAK files, re-create the users, then change the IP and name of the server to replace the old one. this would be done over a weekend etc. This prompts a few questions which i was hoping you could shed some light on.
1 - When Restoring a SQL 2000 .BAK file to SQL 2008, is there a best practise regarding such things? Should the database be updated in anyway? or just put in compatibility mode?
2 - If we have a database on our 2008 server set in 8.0 compatibility mode, what’s the best way of bringing that database to version 10?
3 - Is there any way to export users of a server to another whilst retaining server roles and passwords?
4 - What are the other likely pit-falls of such an approach to this upgrade/migration?
5 – Apart from SQL jobs & maintenance plans that will need to be re-created, is there any other major components that need consideration when moving to a new blank set of master databases?
I look forward to hearing your thoughts on this matter. (Apart from telling me i'm crazy for skipping version 9 haha)
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
March 12, 2009 at 6:35 am
1 - When Restoring a SQL 2000 .BAK file to SQL 2008, is there a best practise regarding such things? Should the database be updated in anyway? or just put in compatibility mode?
Database will be upgrade as soon as you restore it; so just changing the compatibility level will be needed.
2 - If we have a database on our 2008 server set in 8.0 compatibility mode, what’s the best way of bringing that database to version 10?
Just change the compatibility level; make sure you run your 2000 Serve through a SQL Server Upgrade Advisor to make sure no hidden surprises wait for you.
3 - Is there any way to export users of a server to another whilst retaining server roles and passwords?
Database users and their permissions will come over; server users no. So all the SQL Server logins will be recreated. But you will not have to recreate the database users; you can map them to new logins you create using "sp_change_users_login".
4 - What are the other likely pit-falls of such an approach to this upgrade/migration?
Side-by-side upgrades are best; if something goes wrong at least you can revert back. But I first run SQL Server Upgrade Advisor to make sure you don't miss anything. The thing you might run into is in-line SQL coding which you can't check easily.
5 – Apart from SQL jobs & maintenance plans that will need to be re-created, is there any other major components that need consideration when moving to a new blank set of master databases?
- DTS packages.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 12, 2009 at 7:26 am
1 - When Restoring a SQL 2000 .BAK file to SQL 2008, is there a best practise regarding such things? Should the database be updated in anyway? or just put in compatibility mode?
2 - If we have a database on our 2008 server set in 8.0 compatibility mode, what’s the best way of bringing that database to version 10?
3 - Is there any way to export users of a server to another whilst retaining server roles and passwords?
4 - What are the other likely pit-falls of such an approach to this upgrade/migration?
5 – Apart from SQL jobs & maintenance plans that will need to be re-created, is there any other major components that need consideration when moving to a new blank set of master databases?
My .02 cents...
1. Depends on if migrating or upgrading SQL Server. I like detach, but depends on whether or not the app/db can be down for any amount of time.
2. Simply change the compat mode. By default, this will not go to 10
3. sp_helprevlogin to move
4. The upgrade or migration doc from Microsoft has recently been upped to a svelte 400 pages from the once measly 300 pages - I'd start there first for best-practices. I've done several migrations and upgrades, so get an upgrade checklist and start jotting down items (users, jobs, dts pkgs, link servers, etc.)
5. Nothing major, you've covered the majority I'd say.
March 12, 2009 at 8:42 am
Cheers for the input guys.
In regards to sp_help_revlogin, i noticed on the http://support.microsoft.com/kb/246133 page it lists a new sp for 2000-2005, specifically 'sp_help_revlogin_2000_to_2005 '
Is this one compatible for moving 2000-2008?
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
March 12, 2009 at 9:16 am
It should, 2008 and 2005 most of the catalogs are same; however I never used the function so I am unsure how well it will work ...
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 18, 2009 at 5:11 pm
Hi Guys,
According from the Q&A No. 2. Do we need to set compatibility Mode to 2000 first before attach the database then change compatibility mode to 2008 after attached the Databases?
For DTS Packages, msdb database will be transfer to 2008 database, is that right? What about master db? Do I need to transfer that as well.
Thanks.
Leo
May 7, 2009 at 5:04 am
i know this threads getting a little old, but we have just started setting up the 2008 (64bit) server to replace a 2005 server, and migrating users, passwords, roles, memberships etc.
I came across the following little gem when crawling the internet for information regarding user migration. Anyway, the script below can be run on any 2005 server, it loops through all users, recreates the create user statements with HEX password hashes, but then checks that users permissions and scripts all them too. It then dumps out the SQL to run on the 2008 box (assuming you have backups of the databases restored on the 2008 box ready)
Just thought it might come in handy for anyone else migrating from 2005 to 2008 without using an in-place upgrade.
USE master
GO
IF OBJECT_ID ('usp_hexadecimal') IS NOT NULL
DROP PROCEDURE usp_hexadecimal
GO
CREATE PROCEDURE usp_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 ('Transfer_login_2005_2008') IS NOT NULL
DROP PROCEDURE Transfer_login_2005_2008
GO
CREATE PROCEDURE Transfer_login_2005_2008 @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 = '/* Transfer_login_2005_2008 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 usp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC usp_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
SET NOCOUNT ON
Declare
@List varchar(max),
@DatabaseUserName sysname,
@DB_principal_id smallint,
@ServerUserName sysname,
@RoleName sysname,
@DB_Name sysname,
@cmd varchar(max),
@default_schema_name sysname,
@DB_Nam sysname,
@state_desc sysname,
@permission_name sysname ,
@schema_name sysname ,
@object_name sysname ,
@user_name sysname
/******************************************USER LIST HERE******************************/
/*E.G. 'User1, user3,domain\user1,domain\user2'*/
--set @List = 'qaauto,qatest,qauser1,qauser2,qauser3'
/*To Script all sql and windows logins uncomment below, note this may re-create undesired accounts and
should be modified in the where clause when needed*/
select @list = isnull(@list,'') + [name] + ',' from master.sys.server_principals where type in ('S','U','G','R','C','K')
if right(@List,1) ','
Begin
set @List = @List + ','
End
Create Table ##DB_USERs
(
Name sysname,
DatabaseUserID smallint null,
ServerUserName sysname null,
default_schema_name sysname null
)
Create Table ##DB_Roles
(
Name sysname
)
CREATE TABLE ##syspermissions (
[DB_Name] [sysname] NULL ,
[state_desc] [sysname] NULL ,
[permission_name] [sysname] NULL ,
[schema_name] [sysname] NULL ,
[object_name] [sysname] NULL ,
[user_name] [sysname] NULL,
[principal_id] [int] NULL
)
CREATE TABLE ##SRV_Roles
(
SERVERROLE VARCHAR(100),
MEMBERNAME VARCHAR(100),
MEMBERSID VARBINARY (85)
)
/*Loop thru file_list*/
while @List ''
Begin
set @DatabaseUserName = left( @List, charindex( ',', @List ) - 1 )
Print '--BEGIN ' + @DatabaseUserName + ' ************************************'
Print '--********Begin Script the Login ********************************************************'
/*Script login with password*/
Execute Transfer_login_2005_2008 @DatabaseUserName
Print 'GO'
/*GET SERVER ROLES INTO TEMPORARY TABLE*/
SET @CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'
INSERT INTO ##SRV_Roles EXEC (@CMD)
Set @CMD = ''
Select @CMD = @CMD + 'EXEC 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 '--Assign Server Roles'
Print @CMD
Delete ##SRV_Roles
Print '--********End Script the Login *********************************************************'
Print ''
/*Get a table with dbs where login has access*/
set @DB_Name = ''
While @DB_Name is not null
Begin
Select
@DB_Name = min(name)
from
master.sys.databases
where
/*limit by database if needed*/
name > @DB_Name
--and name in ('Accounting','CAMDW_DST','Employee','FFS_Staging','HRTraining')
IF @DB_Name IS NULL BREAK
Set @cmd =
'insert ##DB_USERs
SELECT '
+ char(39) + @DB_Name + char(39) + ',' +
'u.[principal_id],
l.[name],
u.default_schema_name
FROM '
+ '[' + @DB_Name + '].[sys].[database_principals] u
INNER JOIN [master].[sys].[server_principals] l
ON u.[sid] = l.[sid]
WHERE
u.[name] = ' + char(39) + @DatabaseUserName + char(39)
Exec (@cmd)
End
/*Add users/roles/object permissions to databases*/
set @DB_Name = ''
While @DB_Name is not null
Begin
Select
@DB_Name = min(name)
from
##DB_USERs
where
name > @DB_Name
if @DB_Name is null BREAK
Print '/************Begin Database ' + @DB_Name + ' ****************/'
select @ServerUserName = ServerUserName,@DB_principal_id = DatabaseUserID,@default_schema_name = default_schema_name from ##DB_USERs where name = @DB_Name
Set @cmd =
'USE [' + @DB_Name + '];' + char(10) +
'CREATE USER [' + @DatabaseUserName + ']' + char(10) +
CHAR(9) + 'FOR LOGIN [' + @ServerUserName + ']' + char(10) +
CHAR(9) + 'With DEFAULT_SCHEMA = [' + @default_schema_name + ']' + char(10) +
'GO'
Print '--Add user to databases'
Print @cmd
/*Populate roles for this user*/
Select @cmd =
'Insert ##DB_Roles
Select name
FROM '
+ '[' + @DB_Name + '].[sys].[database_principals]
WHERE
[principal_id] IN (SELECT [role_principal_id] FROM [' + @DB_Name + '].[sys].[database_role_members] WHERE [member_principal_id] = ' + cast(@DB_principal_id as varchar(25)) + ')'
--Print @cmd
Exec (@cmd)
/*Add user to roles*/
Set @cmd = ''
Select @cmd = isnull(@cmd,'') + 'EXEC [sp_addrolemember]' + char(10) +
CHAR(9) + '@rolename = ''' + Name + ''',' + char(10) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''+ char(10) +
'GO' + char(10)
from ##DB_Roles
if len(@cmd) > 0
Print '--Add user to role(s)'
Print @cmd
Delete ##DB_Roles
/*Object Permissions*/
Set @cmd =
'
Insert ##syspermissions
select ' + char(39) + @DB_Name + char(39) + ',a.[state_desc],a.[permission_name], d.[name],b.[name],c.[name],c.[principal_id]
from '
+ '[' + @DB_Name + '].sys.database_permissions A
JOIN ' + '[' + @DB_Name + '].[sys].[objects] b
ON A.major_id = B.object_id
JOIN ' + '[' + @DB_Name + '].[sys].[database_principals] c
ON grantee_principal_id = c.principal_id
JOIN '+ '[' + @DB_Name + '].sys.schemas d
ON b.schema_id = d.schema_id'
Exec (@cmd)
If exists (select 1 from ##syspermissions where principal_id = @DB_principal_id)
Print '--Assign specific object permissions'
DECLARE crs_Permissions CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT
[DB_Name],
[state_desc],
[permission_name] ,
[schema_name] ,
[object_name] ,
[user_name]
FROM
##syspermissions
Where
principal_id = @DB_principal_id
OPEN crs_Permissions
FETCH NEXT FROM crs_Permissions INTO @DB_Name,@state_desc,@permission_name ,@schema_name ,@object_name ,@user_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = @state_desc + ' ' + @permission_name + ' ON [' + @schema_name + '].[' + @object_name + '] TO [' + @user_name + ']'
Print @cmd
FETCH NEXT FROM crs_Permissions INTO @DB_Name,@state_desc,@permission_name ,@schema_name ,@object_name ,@user_name
END
CLOSE crs_Permissions
DEALLOCATE crs_Permissions
delete ##syspermissions
Print '/************End Database ' + @DB_Name + ' ****************/'
Print ''
/*next db*/
End
Print '--END ' + @DatabaseUserName + ' ************************************'
Print ''
/*Parse the list down*/
set @List = right( @List, datalength( @List ) - charindex( ',', @List ) )
/*Clear data for the last user*/
Delete ##DB_USERs
End
/*Clean up*/
Drop table ##DB_USERs
Drop table ##DB_Roles
drop table ##syspermissions
Drop table ##SRV_Roles
use master
Drop procedure Transfer_login_2005_2008
Drop procedure usp_hexadecimal
After the migration databases all behaved normally and password hashes seemed to work fine.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
May 8, 2009 at 3:14 am
For DTS Packages, msdb database will be transfer to 2008 database, is that right? What about master db? Do I need to transfer that as well.
Don't even think of restoring any system databases from one version of SQL Server to another. Also don't think of restoring system databases from one patch level of SQL Server to another patch level on the same version. You will most likely break SQL Server and if you don't you will be outside of Microsoft support.
If you want to get DTS packages from one SQL Server instance to another you will need to export and import them. See my post at http://qa.sqlservercentral.com/Forums/Topic669611-148-1.aspx for advice on doing this.
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
May 10, 2009 at 2:12 am
This is a link to the MS Technical Reference Guide for upgrading:
http://www.microsoft.com/downloads/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7
there is a lot of information there.
I have attached a checklist that I put together based on reading through that tech ref which has some of the things I looked at before doing the upgrade.
It has been of great use to me thus far.
One thing I will highlight is that you can trace your SQL Server activity, and save it to a flat file. Take that file (the .trc file generated) through the 2008 upgrade advisor.
One thing that cannot be stressed enough is to test all applications against a development copy of the databases running on SQL 2008. And make sure the databases are in 100 (2008) compatibility mode when testing. Once you change from 80 to 100, behavioral changes will occur that could possible break applications.
Upgrade Advisor:
http://www.microsoft.com/downloads/details.aspx?FamilyId=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852
Best of luck to you.
Steve
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply