Upgrade from SQL 2000 to SQL 2008 on a new server

  • Hi,

    Gearing up for the big move from our 32 bit sql 2000 servers to new 64 bit sql 2008 servers. I have sorted out (I think) moving the user databases, server logins, database users and permissions. The process so far is

    User databases;

    Copy current backup files to external harddrive on SQL 2000 server

    Move external harddrive to new SQl 2008 server

    Restore databases (With recovery, Move NOUNLOAD, STATS = 10)

    ALTER DATABASE [Mydb] MODIFY FILE ( NAME = [filename], NEWNAME =[new file name]), this process corrects some historic inconsistencies with naming conventions.

    DBCC checkdb ([mydb]) WITH ALL_ERRORMSGS , NO_INFOMSGS

    USE [mydb]DBCC CHECKTABLE ([mytable], indexid) with ALL_ERRORMSGS , DATA_PURITY , NO_INFOMSGS

    DBCC UPDATEUSAGE ([mydb],[mytable], indexid) with COUNT_ROWS , NO_INFOMSGS

    sp_dbcmptlevel @dbname=N'[mydb]', @new_cmptlevel=100

    Transfer logins, database membership, database permissions, server and database role membership from the SQL 2000 to SQL 2008 server

    Resolve any orphaned users

    create maintenance tasks and backup devices

    Have I missed anything??

    System Databases:

    I am a bit stuck on what to do with the system databases, how do I move the system databases, do I need to move them??

    The SQL jobs currently on the SQL 2000 server, do I simply script these and run the script(s) on the SQL 2008 server

    Extended stored procs, can I simply copy the DLL files to the new server and then register the extended stored procs, is there an issue with the logical and physical names of the files?

  • Been there, done that, have the t-shirt 😎

    Our upgrades done in the fashion you describe were all very successful. Make sure to run the upgrade wizard and resolve any T-SQL issues as we *prefer* to NOT run in compatibility mode and provide that crutch to our dev team.

    You may want to install DTS RunTime as not all of our packages were able to be re-written in SSIS in time.

    We simply scripted all of the logins from one server to another using Idera's Toolkit, same with the Jobs (excluding RS stuff of course). A query I used to see what logins were absolutely necessary to transfer is as follows (run this on the 2000 server):

    USE [master]

    GO

    SET NOCOUNT ON

    CREATE TABLE ##Logins(

    DatabaseName VARCHAR(255),

    LoginName VARCHAR(255)

    )

    DECLARE

    @dbName VARCHAR(255),

    @strSQL NVARCHAR(4000)

    DECLARE cDatabases CURSOR FAST_FORWARD

    FOR SELECT S.[name]

    FROM sysdatabases S

    WHERE s.[dbid] > 4

    --AND S.[name] IN('DATABASE_NAME_LIST')

    ORDER BY S.[name]

    OPEN cDatabases

    FETCH cDatabases INTO @dbName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @dbName = '['+RTRIM(@dbName)+']'

    --In the event of db collation problems, may need to set join property on sysusers to <snip> ON SL.[name] = S.[name] COLLATE Latin1_General_BIN </snip>

    SET @strSQL = 'INSERT INTO ##Logins SELECT '''+@dbName+''' AS DatabaseName, S.[name] AS UserName

    FROM '+@dbName+'..sysusers S

    INNER JOIN master..syslogins SL ON SL.[name] = S.[name] COLLATE Latin1_General_BIN

    WHERE SUSER_SNAME(S.SID) IS NOT NULL AND S.issqlrole = 0 AND S.hasdbaccess = 1 AND S.[name] NOT IN(''dbo'') AND S.islogin = 1'

    EXEC sp_executesql @strSQL

    FETCH cDatabases INTO @dbName

    END

    CLOSE cDatabases

    DEALLOCATE cDatabases

    --Return only distinct logins associated with databases to be retained

    SELECT DISTINCT 'LOGIN_VALID' AS LoginStatus, L.LoginName

    FROM ##Logins L

    UNION ALL

    --Return distinct logins that are not being used

    SELECT DISTINCT 'NOT_USED', SL.[name]

    FROM ##Logins L

    FULL OUTER JOIN [master]..syslogins SL ON SL.[name] = L.LoginName

    WHERE L.LoginName IS NULL

    ORDER BY 1, 2

    DROP TABLE ##Logins

    GO

    This was just a cleanup attempt so we didn't transfer any logins that did not have a database user associated with it somewhere.

    Also, in addition to running scripts to update logical/physical file names (man that felt good to do :)), we did the following in the RESTORE script:

    /***********************Run the following commands against the restored database to standardize***********************/

    --Bring database online

    SET @strSQL = N''ALTER DATABASE ['' + @db + ''] SET ONLINE''

    EXEC sp_executesql @strSQL

    --Set multi-user

    SET @strSQL = N''ALTER DATABASE ['' + @db + ''] SET MULTI_USER''

    EXEC sp_executesql @strSQL

    --Change DB owner to ''sa''

    SET @strSQL = ''EXEC '' + @db + ''..sp_changedbowner ''''sa''''''

    EXEC sp_executesql @strSQL

    --Update usage to prevent DBCC CHECKDB errors from converted 8.0 databases

    SET @strSQL = ''DBCC UPDATEUSAGE('' + @db + '')''

    EXEC sp_executesql @strSQL

    Looks like you caught the UPDATEUSAGE on your side also so you're in good shape operationally. I'll let you know if I think of anything else.

    Good luck!

    MJM

    P.S. We did not restore the system databases as we felt this was (a) a good opportunity for cleanup, and (b) did not want to monkey with any compatibility issues going from 32-bit to 64-bit, how the system resource db interacts with other system db's from 2000, etc.

    We did not have any xp's to move as you do so I can't speak to that.

    We did our final backups to the backup volume on the 2000 server, then I used xcopy to copy the *.bak files over to the new server. Depending on your db sizes etc maybe moving the HDD is a better idea. Just my $0.02 (and worth every penny)

  • You don't move system dbs. The logins/jobs/etc scripts will move the data as do the restores.

    Jobs - I believe you script these and run the script. If they don't work, you'll have to rebuild them from scratch.

    XPs - No idea. I believe you re-register these are new procs, copy DLLs first.

  • Mark

    Thanks for the super fast reply, I'll add the bits you have suggested to my "just do it " script. I'm still scratching my head regarding what if anything to do with the system databases on the old SQL 2000 server, is it worth physically moving these to the new 64 bit SQL 2008 server, or shoud I just copy over the extended stored procs and register them??

    Regards fishbarn

  • Steve,

    Another super fast reply, thanks.

    I reckon my question regarding the system databases was due to my brain being somewhat full up at the moment, could do with a holiday!

    I've got a little doohickey that should copy the dlls and then do the re-registering.

    Long time since I did an upgrade.

    Regards

    Fishbarn

  • BACKUP LOG MyBrain WITH TRUNCATE_ONLY;

    UPDATE dbo.Self SET Status = 'BACK_AT_WORK' WHERE Status = 'ON_HOLIDAY'

    GO

    Should get ya fixed right up 😛

  • You are welcome. I'm in the opposite mode. Leaving for a couple days holiday in hours!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply