October 27, 2008 at 10:59 am
We are running SQL Server 2005 Standard Edition and we have our main user databases on a SAN that is replicated on a periodic basis to our disaster center SAN. We have tested bringing up the user databases at the disaster center using a different server and the SAN disk from the DR site. We are able to get the user databases to work come up with little difficulty.
However, it would be a big help to have the system databases, including master with all of the SQL logins (our 3rd-party software designed it to use SQL logins) and user database locations, replicated as well so that we can point our DR server to the SAN for starting SQL and it will recognize all of our user databases and msdb with our SQL jobs.
I'm trying to find out what if any hang-ups there would be with this approach or if anyone has additional suggestions. We have not tried this yet so I'm in the planning phase of this project.
I know the master database can be a bit finicky especially when dealing with logins. Any advice would be appreciated.
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
October 27, 2008 at 11:06 am
Other than the logins, what is the reason for restoring master? If it is just logins, you can script them out in an automated fashion at whatever interval you want and apply them to your DR site. Should work well and would seem to be less painful. Just my thoughts. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 27, 2008 at 11:37 am
Well, from a DR standpoint I would think it would help us get up and running faster. The SQL logins have to be "remapped" back to the database users if the database is restored.
I just wanted to see if anyone else has tried this or if it is more work than it is worth.
Thanks
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
November 5, 2008 at 7:41 am
The master database holds information that is specific to the local machine, and so trying to do a full master restore might not be a good idea.
As for the logins, if you have to do a 'remap' then you need to rebuild on your DR master the logins with the original SID that was created on the production server. When a db is restored, the master db login is initially compared to the db user based on the SID, not the name. The simple approach is to drop and restore the logins with the sid as part of the create login command.
The more you are prepared, the less you need it.
November 7, 2008 at 3:25 pm
Hi-
First I want to make sure I'm understanding... Your DR Server will be the same computername and Service Account??
We've done exactly that process a number of times on SQL 2000, so unless there's something tricky in sql2005- it should be fine.
sidetrack [FYI] below
I asked about the computername because I just tried to do similar [but with different computername and Service Account:
We'll be upgrading one of ours from x86 to x64 and plan to just use snaps (or redirect the actual volumes?)
I'm not a SAN Guy so I don't know what's possible there, but I just tried similar in our QA environment.
we brought the x86 production snaphot to QA and I changed the config of SQL to use those system files.
SQL Came up just fine, but I had issues showing in the SQL logs [encryption errors]
•I found errors in the SQL Logs discussing encryption errors:
oError: 15466, Severity: 16, State: 1.
An error occurred during decryption.
oI further see this error when attempting to configure replication and Linked servers.
I managed to get through it by doing the following:
[Use Master]
1. ALTER SERVICE MASTER KEY FORCE REGENERATE
2. ALTER SERVICE MASTER KEY
3. WITH NEW_ACCOUNT = 'Stericorp\QAsqlservice' , NEW_PASSWORD = 'blahblahblah'
4. RESTORE MASTER KEY FROM FILE = 'E:\MASTER_MasterKey.bak'
DECRYPTION BY PASSWORD = 'blahblahblah'
ENCRYPTION BY PASSWORD = 'blahblahblah' (before doing the restore I needed to back it up on production)
5. ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'blahblahblah'
I was then able to do everything I needed 🙂
Ben
November 14, 2008 at 6:24 am
Andrew Peterson (11/5/2008)
The simple approach is to drop and restore the logins with the sid as part of the create login command.
why not just script off the logins from the source and create the logins on the target using the generated script. This will keep all SID's and passwords 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply