SQL Server Performance

Migration from SQL2k stand-alone to SQL2k cluster

Discussion in 'SQL Server Clustering' started by biged123456, Jun 15, 2004.

  1. biged123456 New Member

    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...
  2. satya Moderator

    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.
  3. Argyle New Member

    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
  4. biged123456 New Member

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

    Thanks,

    Matt
  5. biged123456 New Member

    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...
  6. aaronsandy New Member

    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?
  7. Argyle New Member

    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.
  8. meantone New Member

    quote:Originally posted by biged123456

    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...

Share This Page