Upgrading DB Server
-
Hey everyone, I am a self taught DBA at a small company and we are replacing our DB server hardware and software from WIN NT4, SQL7 to WIN2K SQL2K. What is the best way to move the databases? I heard that the export/import method works well but I also need to ensure that all of the login names move as well.
What is the proper method of doing this? I have read about using DTS and setup a test box to move a database and user information, but the DTS fails because I cannot get the login name information to transfer. I am not sure if it is an issue with the SQL7 server configuration or if it is something to do with SQL2K or both.
Any help or guidence is GREATLY appreciated.
BTW: the databases are for JD Edwards. I don't know if that is important or not.
-
A SQL 7 backup can be restored in SQL 2000 so this is an easy method of copying the databases from one server to another.
To transfer logins and passwords from a SQL Server 7.0 server to an instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS Package Transfer Logins Task in SQL Server 2000. To use this task:
- Connect to the SQL Server 2000 destination server, move to to the Data Transformation Services in SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.
- After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information about the Source, Destination and Logins tabs as appropriate.
If you don't move the login first the database users will be orphaned, that is the user will not match and login. If this happens you will need to resyn users and logins. Here is a sample script to do this.
USE database_name --Change to active database name
go
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur 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 orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go
Francis
-
Thanks for the update and advise. I have read several posts about restoring from backup and will go that route.
As for the DTS Package Transfer Logins Task in SQL Server 2000, this continually fails for me (is there a way to get the log files to share?).
Also, how about the master database. Do I need to retore all of these db's, in what order? Sorry for being such a bub, but I have very limited resources out here in the sticks and have to do this myself and need to be sure.
Thanks again for your help.
-
Do not attempt to transfer any of the system databases (master, msdb, temp or model.) These will all be created for you when you install SQL 2000. You may need to transfer or recreate any jobs you have (backup jobs, reindexing jobs, etc) but only transfer these objects not the system databases.
If the DTS transfer logins doesn't work for you here's a script for you. You will need to set up a linked server on you2 sql 2000 box to the old SQL 7 server. Execute the first script on your SQL 2000 box to copy logins. The 2nd script is executed on the SQL 7 server and the output cut and pasted to the SQL 2000 sever. This script will add the SQL Server roles that may have been defined. Once you're done the linked server can be dropped (best to wait to do this when you are ready to completed remove the SQL 7 server)
-- 1st Script:Setup a linked server called impserver from which the
-- standard logins needs to be transferred. You can call it
-- whatever you want & modify the linked server name also.
declare @login sysname , @password sysname
declare implogins cursor for
select name , password
from [impserver].master.dbo.syslogins
where isntname = 0 and charindex( 'repl_' , name ) = 0 and
charindex( 'distributor' , name ) = 0 and name != 'sa'
open implogins
while ( 'FETCH IS OK' = 'FETCH IS OK' )
begin
fetch implogins into @login , @password
if @@fetch_status < 0 break
exec sp_addlogin @login , @password , @encryptopt = 'skip_encryption'
end
deallocate implogins
go
-- 2nd script: execute this in the database on the source server and
-- copy output to target server
declare @ServerRole nvarchar(35)
declare @MemberName sysname
declare srvrolemember cursor for
select 'ServerRole' = spv.name, 'MemberName' = lgn.name
from
master.dbo.spt_values spv,
master.dbo.sysxlogins lgn,
sysusers u
where
spv.low = 0 and
spv.type = 'SRV' and
lgn.srvid IS NULL and
spv.number & lgn.xstatus = spv.number and
lgn.sid = u.sid and
lgn.name <> 'sa'
order by 'MemberName'
for read only
open srvrolemember
fetch next from srvrolemember into @ServerRole, @MemberName
while @@fetch_status = 0
begin
Print 'exec sp_addsrvrolemember N''' + @MemberName + ''', ' + @ServerRole
fetch next from srvrolemember into @ServerRole, @MemberName
end
deallocate srvrolemember
Francis
-
Wow!! That's more than I ever expected!I am testing this on a temporary server I setup and will let you know how things come out.
Thanks for all your help! I know now that I have come to the right forum (finally!).
-
I am trying something similar but my servers are in different domains so I cannot use the DTS Transfer logins routine.
BOL says that I can do the following
E. Create a login ID and do not encrypt the password
This example creates an SQL Server login for the user Margaret with a password of Rose on Server1,
extracts the encrypted password, and then adds the login for the user Margaret to Server2 using
the previously encrypted password but does not further encrypt the password.
User Margaret can then log on to Server2 using the password Rose.
-- Server1 EXEC sp_addlogin Margaret, Rose--ResultsNew login created.-- Extract encrypted password for MargaretSELECT CONVERT(VARBINARY(32), password)FROM syslogins WHERE name = 'Margaret'--Results------------------------------------------------------------------ 0x2131214A212B57304F5A552A3D513453(1 row(s) affected)-- Server2 EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453, @encryptopt = 'skip_encryption'I've tried this but none of my logins work on my target server. What am I doing wrong? -
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply