Moving to new server
-
Hi,
our current MS SQL 2005 server has becom to small for us so we need to move everything to a new, more powerfull server. What is the best way to do this? All our users have an SQL-login and that login is set to be dbowner for their database.
I tried to copy a database with logins, using the dettach and attach method, it worked but:
1. The users password was changed
2. The user was disabled and
3. After enebled and fixed the password for the login, the login wasn't the dbowner and couldn't connect to the db.
So it seems like copying the databases is a bad idea...
So any suggestions how to do this?
/Anders
-
I prefer to backup on old box and restore on the new box.
before doing the backup restore operation, I use the stored procedures found here
http://support.microsoft.com/kb/246133/EN-US/. This article describes the use of these sp.
In short running the stored procedures will create a script on the old box that can be run on the new box to pre-create the logins ( and passwords ) of all the logins you will need on the new box.
-
Hello
OK, the above script will work.....but things must be done in the following order
- load the login script, run and extract the existing userids
- run the add login scripts on the new machine. Note this script is great for adding passwords, but will not re-add roles like sa.
- detach your old databases
- copy old database mdf and ldf files to new machine
- attach databases to new machine
- you will need to repair you logins on the new machine. withing each database execute sp_change_users_login with the 'auto_fix' option. This will map your database access to the NEW machines logins.
Good luck
Eric
-
I recently performed this for a client on the weekend. We installed the new server, installed SQL 2K5 and Reporting Services. Then we shut everything down. The we shut down the existing production server. Perfromed a copy of all SQL database and reporting folders. Ran the rskeymgmt utility for Reporting Services on the old and new server. The we started up the new SQL server. Everything was perfect.
Some caveats though. Both server directory structures must be 100% identical. We did not use Full Text Search, SSIS or the Service Broker.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
ok, thanks, I thought MS had fixed this "problem" in SQL 2005 and made it easier to restore everything on a new server.
I will do a test tonight and see if it works.
-
The basic problem is that if you create a new SQL login without providing the SID value, a new SID will be assigned. Then when you restore or attach a database from another server, the mismatched SIDs foul up permissions. SQL identifies database users by matching the SID to the logins, not the name. This is not a bug, it's a feature, and I doubt Microsoft will ever "fix" it. (This only applies to SQL logins, the SID for windows comes from the domain and will be synchronized automatically.)
The intelligent way to deal with it is to correctly create synchronized SQL logins on the new server in the first place. You don't need their scripts to make the binary values pretty, just run your own queries. This is written to run on a SQL 2005 box to create logins (with server roles) for a new SQL 2005 box. Check the results before executing, the clauses I used to suppress system logins are only guaranteed to work on the one system I tested on.
SELECT
'CREATE LOGIN ' + quotename(name) + ' WITH PASSWORD=', password_hash, ' HASHED, SID=', sid
,', DEFAULT_DATABASE=' + quotename(default_database_name)
,', DEFAULT_LANGUAGE=''' + default_language_name + ''''
FROM sys.sql_logins
WHERE principal_id > 1
SELECT 'CREATE LOGIN ' + quotename(name) + ' FROM WINDOWS'
FROM sys.syslogins
WHERE denylogin=0 AND (isntuser=1 OR isntgroup=1)
AND name NOT IN ('NT AUTHORITY\SYSTEM','BUILTIN\Administrators')
AND NOT name LIKE '%SQLServer2005%'
SELECT 'EXEC sp_addsvrolemember ''' + logins.name + ''', ''' + roles.name + ''''
FROM sys.server_role_members xr
INNER JOIN sys.server_principals logins ON logins.principal_id = xr.member_principal_id
INNER JOIN sys.server_principals roles on roles.principal_id = xr.role_principal_id
WHERE logins.name <> 'sa'
AND logins.name NOT IN ('NT AUTHORITY\SYSTEM','BUILTIN\Administrators')
AND NOT logins.name LIKE '%SQLServer2005%'
-
Just curious, why do you run rskeymgmt on both the old and new server? shouldn't the key you kept when you installed on the old server still be the key there? or did you run it and copy the key to the new server so the key was the same on both sides?
Guess I hadn't thought this one through; but if you have reporting metadata encrypted with the old key, for it to be usable on the new server you have to have the same key.
Okay, that must be the answer; but I will complete the post here - Good call, Rudy! Thanks for making that distinction.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
David, I had to run the rskeymgmt utility on the old SQL server to extract the encryption keys since the client had never done so. Then again on the new SQLserver in order to had Reporting Services work properly (making the key the same on both servers). If you don't do this then you have to redo all of the connection and security stuff. This 'stuff' can be quite a pain based on the complexity of the security implemented and the database reporting environment, especially if there are 3rd party applications present !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Scotts litle script did the trick real fine, thanks for all your help guys!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply