SQL Server Performance Forum – Threads Archive

Migration from SQL2k stand-alone to SQL2k cluster

I am migrating from a SQL2000 stand-alone server to a SQL2000 cluster. The current SQL stand-alone has the following:
150+ Databases
10 backup maintenance plans (covering the 150 DBs)
300+ SQL logins Can anyone make suggestions on how to migrate with the least pain? I have examined MS docs on how to transfer SQL logins http://support.microsoft.com/default.aspx?scid=kb;en-us;246133), which would save me some time. I was also thinking of backing up all the DBs to one folder, transferring the backup files and restoring on the cluster. All the destination data and log directories would be the same and I was thinking I could write a script that would restore one DB at a time. Also, besides restoring MSDB from the stand-alone and adjusting server name in the tables, is there any easy way to transfer maintenance plans? I would prefer not to restore master or MSDB on the cluster…

---

On the part of maintenance plans I would recommend to re-define them on the cluster, rather than using the MSDB to do so. Applying the service pack to the default instance and then upgrading to the clustered instance does not add the proper disk dependencies. To add the proper disk dependencies, you must install the service pack after the upgrade to the clustered instance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I usually do a detach/attach instead of backup/restore. 1. create all logins on destination server
2. Set DBs on source server in read only and dbo use only
3. detach databases
4. copy database files
5. attach databases
6. sync logins on destination server (sp_fixusers)
7. reindex and/or update stats in databases
8. run checkdb in databases
9. sync default db name on logins
10. transfer scripted sql jobs
11. transfer or recreate maitnenance plans
12. transfer dts packages
13. set up any linked/remote servers again Some scripts below (from various places on the net):
–Move DB script
–Many of the scripts below generate output that is the final script to be run ———————————— –DBO Use Only (Change true/false option)
SELECT ‘sp_dboption ”’ + Name + ”’, ”dbo use only”, ”TRUE”
G’ + ‘O ‘
FROM master.dbo.sysdatabases
WHERE DBID > 4
ORDER BY Name –Result
sp_dboption ‘UserDBName1’, ‘dbo use only’, ‘TRUE’
GO sp_dboption ‘UserDBName2’, ‘dbo use only’, ‘TRUE’
GO ———————————— –Reindex All Tables in All Databases (note: runs directly, doesn’t generate script output to run)
DECLARE @SQL Varchar(8000)
SET @SQL = ” SELECT @SQL = @SQL + ‘EXEC ‘ + NAME +
‘..sp_MSforeachtable @command1=”DBCC DBREINDEX (””?””)”’ + Char(13)
FROM MASTER..Sysdatabases
WHERE DBID > 4– Skip 6 built-in DBs. Remove this line to include others in process PRINT @SQL– To see the code you’re about to execute.
EXEC (@SQL) ———————————— –Update Statistics in All Databases
SELECT ‘Use ‘ + Name + ‘
exec sp_updatestats
G’ + ‘O ‘
FROM master.dbo.sysdatabases
WHERE DBID > 4
Order By Name –Result
Use UserDBName1
exec sp_updatestats
GO Use UserDBName2
exec sp_updatestats
GO ———————————— –DBCC CHECKDB on all databases, included system databases (note: runs directly, doesn’t generate script output to run) sp_msforeachdb ‘dbcc checkdb (?)’ ———————————— –Sync logins in All Databases – custom SP
SELECT ‘Use ‘ + Name + ‘
exec sp_fixusers
G’ + ‘O ‘
FROM master.dbo.sysdatabases
WHERE DBID > 4
Order By Name –Result
Use UserDBName1
exec sp_fixusers
GO Use UserDBName2
exec sp_fixusers
GO ————————————
–sp_fixusers
/*************************************************************************************
This procedure should be created in the Master database. This procedure takes no
parameters. It will remap orphaned users in the current database to EXISTING logins
of the same name. This is usefull in the case a new database is created by restoring
a backup to a new database, or by attaching the datafiles to a new server. Changes:
Added dbo change even if no users present
*************************************************************************************/
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_fixusers
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>’
ELSE
PRINT ‘<<< DROPPED PROCEDURE dbo.sp_fixusers >>>’
END GO CREATE PROCEDURE dbo.sp_fixusers AS BEGIN DECLARE @username varchar(255) DECLARE fixusers CURSOR
FOR SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name OPEN fixusers FETCH NEXT FROM fixusers
INTO @username IF @@FETCH_STATUS <> 0 –no users to fix but set dbo just incase
BEGIN
EXEC sp_changedbowner ‘sa’
END WHILE @@FETCH_STATUS = 0
BEGIN
IF @username=’dbo’
BEGIN
EXEC sp_changedbowner ‘sa’
END
ELSE
BEGIN
EXEC sp_change_users_login ‘update_one’, @username, @username
END
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
END
go
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
PRINT ‘<<< CREATED PROCEDURE dbo.sp_fixusers >>>’
ELSE
PRINT ‘<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>’
go ———————————— –Detach Databases
SELECT ‘sp_detach_db ”’ + Name + ”’
G’ + ‘O ‘
FROM master.dbo.sysdatabases
WHERE DBID > 4
ORDER BY Name –Result
sp_detach_db ‘UserDBName1’
GO sp_detach_db ‘UserDBName2’
GO ———————————— –Attach Databases – Hardcoded path, only one mdf and ldf, lists all databases
SELECT ‘sp_attach_db @dbname = N”’ + Name + ”’,
@filename1 = N”’ + filename + ”’,
@filename2 = N”’ + ‘D:MSSQL7data’ + Name + ‘.ldf” ‘
FROM master.dbo.sysdatabases
WHERE DBID > 4
Order By Name
–Result
sp_attach_db @dbname = N’UserDBName1′,
@filename1 = N’d:MSSQL7dataUserDBName1.mdf’,
@filename2 = N’D:MSSQL7dataUserDBName1.ldf’ sp_attach_db @dbname = N’UserDBName2′,
@filename1 = N’d:MSSQL7dataUserDBName2.mdf’,
@filename2 = N’D:MSSQL7dataUserDBName2.ldf’ ———————————— –Alternative sp_Attach_db for all databases, using existing path of DBs, handles multiple filegroups SET NOCOUNT ON DECLARE @dbname SYSNAME
DECLARE @filelist varchar(8000)
DECLARE @filecmd varchar(8000)
DECLARE @filecount INT
DECLARE @filenbr INT
DECLARE @fileid SMALLINT
DECLARE @filename NVARCHAR(260) SELECT name
INTO #databases
FROM master.dbo.sysdatabases
WHERE DBID > 4
ORDER BY name CREATE TABLE ##files
(
fileid SMALLINT NOT NULL,
[filename] NVARCHAR(260) NOT NULL
) WHILE (SELECT COUNT(*) FROM #databases) <> 0
BEGIN SELECT TOP 1 @dbname = [name]
FROM #databases
ORDER BY [name] SELECT @filecmd = ‘INSERT INTO ##files SELECT fileid, rtrim([filename]) FROM ‘ + @dbname + ‘.dbo.sysfiles’
EXEC (@filecmd) SELECT @filecount = COUNT(*) FROM ##files SET @filenbr = 1 SELECT @filelist = ” WHILE @filenbr <= @filecount
BEGIN
SELECT @fileid = fileid, @filename = [filename] FROM ##files ORDER BY fileid DESC SELECT @filelist = @filelist + ‘@filename’ + CAST(@filenbr AS VARCHAR(3)) + ‘ = N”’ + @filename + ”” IF @filenbr = @filecount –last file, no comma
BEGIN
SELECT @filelist = @filelist + CHAR(13) + CHAR(10)
END
ELSE
BEGIN
SELECT @filelist = @filelist + ‘,’ + CHAR(13) + CHAR(10)
END SELECT @filenbr = @filenbr + 1 DELETE FROM ##files
WHERE fileid = @fileid END –print attach statment for current db
PRINT ‘sp_attach_db @dbName = N”’ + @dbname + ”’,’ + CHAR(13) + CHAR(10) + @filelist + ‘GO’ + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) DELETE FROM #databases
WHERE name = @dbname
END DROP TABLE ##files
DROP TABLE #databases –Result
sp_attach_db @dbName = N’Northwind_test’,
@filename1 = N’D:MSSQLdata
orthwnd_test.mdf’,
@filename2 = N’D:MSSQLdata
orthwnd_test.ldf’
@filename3 = N’E:MSSQLdata
orthwnd_test.ndf’
GO sp_attach_db @dbName = N’pubs’,
@filename1 = N’D:MSSQLdatapubs.mdf’,
@filename2 = N’D:MSSQLdatapubs_log.ldf’
GO

---

Fantastic Argyle! This is exactly what I was looking for. Thanks, Matt

---

Just a note Argyle, I had to run the sp_fixusers twice to change the database owner to SA if the following condition was met. There are users to be fixed AND the current DBO is not one of the user accounts to be fixed. The second time it ran, it changed the owner to SA since all the users were already fixed…

---

I executed sp_fixusers ..but recived the following error.
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line 22
Cannot change the owner of the master database. How to rectify this error?

---

You have to run it in each specific database. If you use the script:
–Sync logins in All Databases – custom SP
SELECT ‘Use ‘ + Name + ‘
exec sp_fixusers
G’ + ‘O ‘
FROM master.dbo.sysdatabases
WHERE DBID > 4
Order By Name
–Result
Use UserDBName1
exec sp_fixusers
GO Use UserDBName2
exec sp_fixusers
GO It will generate the needed SQL but exclude the system datbases by filtering on dbid > 4.

---

---

]]>