moving a production db to a new SQL server 2

[ITsmyfault]

Hello:

I checked the FAQ but did not see this one..

I have an aging Win2k cluster running SQL2k Ent Ed. I have built a shiny new SQl2k cluster on our SAN but I am not sure how to "properly" move the database from old server to new server.

Can I just create duplicate users on the new server, take a db backup and import it into the new server? Forget for the moment that the servers have different names/IP's and that the front end app will need re-direction..

thanks for any pointers you may care to share.

- Joe

 

[AccordingToDale]

How many db's do you have? If there is only a few, that's certainly one way to move them.

You could also detach them, move them and attach them to the new server.

You will have to do one other step after restoring the backups or mvoing the db's to the new server. When you create the new login's on the server they will not have the same SID as the login's in the db. Run the following script for each db to fix that...

exec sp_change_users_login 'Update_One', 'loginName', 'loginName'

Dale

 

[mrdenny]

You are on a SAN so it should be fairly easy.

Setup the new cluster and new Temp SAN drives. Install SQL exactally as it is on the old cluster. Patch to the same level as the old cluster.

Stop Both SQL Servers. Have the same remove the LUNs from the new cluster. Move the LUNs from the old cluster to the new cluster. Start SQL on the new cluster. Point the clients to the new cluster.

The only thing you will need to do it inform the database of the new virtual server name using the sp_drop_server and sp_add_server procedures.

Restart SQL.

Done. Everything will come across, username, etc.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[JBaileys]

Joe,

Several years ago I set-up a cluster on a shiny new W2K OS. Things change.

To begin with, make sure you have a good backup. Hopefully, you are not a 24 hour shop and you have a couple hours to migrate the data.

One option is to use DTS. With DTS, you can setup a transfer database routine which will allow for you to transfer all objects (including users and logins). I would insist with your supervisor to have both test and production servers and begin the process with the test server. I like to say: "If you are serious about data integrity and our environment, I would plan on multiple clustered servers (get yourself a straw hat - because you gonna' be a farmer).

 

[ITsmyfault]

Hi & thanks to both of you. Unfortunately I do not have the old cluster on the new SAN.. it's a 2-node SCSI job (I know.. I know!) I wish I could just swap out the LUNs. I had not thought of that but it would be sweet!

I only have the 1 db to move, so whichever way I do it it will not take a long time. Thanks for the tip on the SID's.. I missed that entirely. (ouch). Is there a tech note on the 'detach' & move proceedure? It sounds pretty straight forward but then again.. If it's on the MS site, I have not found it (yet).. still looking.

 

[mrdenny]

The detach and copy is fairly straight forward.

Move the logins across using DTS is possible. If not then script them out and create them using the hashes stored in the old database. Then reattach the database.

I use this code to find and fix all detached user accounts when I reattach or restore a database.

exec sp_change_users_login @Action='report'

create table #T1
(UserName varchar(100),
UserSID varbinary(32))

insert into #T1
select UserName = name, UserSID = sid from sysusers
            where issqluser = 1 and (sid is not null and sid <> 0x0)
                    and suser_sname(sid) is null
            order by name
--exec sp_change_users_login @Action='report'

declare @UserNAme varchar(100)
declare cur CURSOR for select UserNAme from #T1
open cur
fetch next from cur into @UserName
while @@FETCH_STATUS = 0
BEGIN
	exec sp_change_Users_login @action='update_one', @UsernamePattern=@UserName, @loginName=@UserName
	fetch next from cur into @UserName
END
close cur
deallocate cur

drop table #T1

exec sp_change_users_login @Action='report'

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[ITsmyfault]

finally found some MSFT notes on detach/attach.. fyi

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

Will find and read up on DTS stuff tonight.. thanks for the help!
JBaileys - I wish I had the luxury of a test server/network.. but no. we're expected to work w/o a net here bc IT is all point and click and if it doesn't work it's my fault..
At least I know our backup solution is 100% so if we have to go to tape I won't even break a sweat.

thanks!

 

[ITsmyfault]

just to close the loop.. I moved that db tonight and it went smooth as can be. THANKS!! I used the detach/attach thing, created a new user on the other side and 'fixed' the logins on the front side to use the new user and it all looks good (can decrypt and report on all the data.. )

Why the term "detach" is on a menu but not in the help file will remain a mystery.

 

[mrdenny]

It's in the help. You have to know the name of the procedure to find it though.

I'm glad everything went well.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)