September 30, 2009 at 9:14 am
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?
September 30, 2009 at 9:30 am
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)
September 30, 2009 at 9:36 am
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.
September 30, 2009 at 9:37 am
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
September 30, 2009 at 9:41 am
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
September 30, 2009 at 9:44 am
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 😛
September 30, 2009 at 9:56 am
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