Best practices: Upgrading server H/W? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Best practices: Upgrading server H/W?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-02-27 : 13:51:25

Ok, I've got a production database that's been on a dual Pentium Pro machine with 1GB of RAM and 12 disk spindles for the past four years. I've also got a shiny new dual P4/2.8 with 4GB and 20 spindles of 15K drives. I'm stoked.

However, I'm also wary, since I've never moved a DB like this before. I'm thinking of just doing a backup/restore approach, but I've got some questions:

Is backup/restore probably the easiest way? Would there be any benefit to just doing detach/attach instead? The logical locations for the data and log files will definitely change. The machines are connected on a fast network.

What do I do about all of my scheduled tasks, which I believe live in MSDB. Should I transfer MSDB over, or recreate those tasks?

Am I correct in thinking that I *do not* want to try to move the master DB over?
Is there any easy way to script the users and make sure that the DB users stay in sync with the SQL server users? I seem to remember hearing that there was some trick inovolved with this

Does anyone have a pointer to any article that covers this kind of move step-by-step? I can afford some downtime in the middle of the night when I make the move, but I really really really want to just do it right the first time.

Thanks!
-b

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-02-27 : 14:00:26

Definitely do a detach/attach method. It is much faster. You can change the logical locations very easily by just moving the files into the directories where you want them to exist. I would recreate the jobs by generating the scripts. Yes, you do not want to move the master databse. To transfer the users, just run this (uses a crappy cursor; you will need to change Database1 and Linkedserver1 to what is appropriate in your environment):

SET NOCOUNT ON

DECLARE @login sysname
DECLARE @pwd sysname
DECLARE @new_pwd varchar(255)

DECLARE cur_Users CURSOR FOR
SELECT l.name, l.password
FROM master.dbo.syslogins l
INNER JOIN Database1.dbo.sysusers u ON l.sid = u.sid
WHERE (l.isntname = 0) AND (u.islogin = 1 AND u.isaliased = 0 AND u.hasdbaccess = 1)
ORDER BY u.name

OPEN cur_Users

FETCH cur_Users INTO @login, @pwd

WHILE @@FETCH_STATUS = 0
BEGIN
-- If the login does not exist on the destination server, then add it.
IF ((SELECT count(*) FROM Linkedserver1.master.dbo.syslogins WHERE name = @login) = 0)
BEGIN
EXEC Linkedserver1.master.dbo.sp_addlogin @loginame = @login, @passwd = @pwd, @encryptopt = skip_encryption, @defdb = 'Database1'
END

-- If the login does exist on the destination server, then synchronize the password.
ELSE
BEGIN
EXEC Linkedserver1.master.dbo.sp_droplogin @login
EXEC Linkedserver1.master.dbo.sp_addlogin @loginame = @login, @passwd = @pwd, @encryptopt = skip_encryption, @defdb = 'Database1'
END

FETCH cur_Users INTO @login, @pwd
END

CLOSE cur_Users
DEALLOCATE cur_Users

Edited by - tduggan on 02/27/2003 14:01:33

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources