February 13, 2017 at 6:53 am
Hi all,
I made a post a while ago in regards to an update to our DR process we currently have. In that, Perry Whittle posted the following, but I didn't receive any further feedback:
its been said before but i'll say it again. Do not restore system databases across server. For a DR machine sync the objects in the 2 instances
Our DR location is off site, and is isn't an online location. The site has details of the servers we have, and have the same spec machines that we have. Should the worst happen we gain access to those machines and working space, however, at this moment they are offline (they don't even have Windows installed, we have to effectively start fresh using all our backups). It's therefore impossible for me to sync the objects between the two locations.
In an ideal world, yes syncing would be great, but I don't work for a big company. The likely cost of having an always online DR server would far outweigh our needs/budget.
What I want to understand is the reasoning behind Perry's post and why we should not be restoring the system databases. What are the problems behind it, and what kind of impact can that have on the server. Also, as syncing objects is off the table, what would you you suggest is a different plausible solution?
If your concerns are in regards to that a different version might be installed at the offsite location, I feel we do have this covered. We have an agent task sends an email to our DBA team with the current SQL version, which includes the KB. Our emails are all also storde in an encrypted state by a 3rd party, so even if we were to lose our Exchange server (which is backed up as well), our emails will be available for the next 99 years (pretty sure that's our service agreement). The version is also saved in a text file to the same location our backups go to, which goes onto tape and are stored off site on a rotation basis. The agent task happens both weekly and after the service starts (so that should we force a critical update through we know about it).
Any answer will be greatly received. Any questions, I'll be happy to give as much detail as I can.
Thanks all.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 13, 2017 at 7:12 am
Thom
If you're rebuilding the same server on similar hardware but in a different place, then I can't see much harm in that. But even then you'd want to be sure that you've installed Windows and SQL Server with exactly the same settings. If you have an automated build process, that shouldn't be a problem.
However, I wouldn't want to restore msdb or master on to a different server (with a different name) - there are too many things that may depend on the name of the server that could go wrong. Just script out everything in master and msdb (settings, logins, jobs, Database Mail to name but a very few) and keep the scripts up to date whenever anything changes on your principal server.
John
February 13, 2017 at 7:38 am
Thanks John.
The server will be rebuilt exactly as it is now. Same SQL version (down the the KB), same Windows version, should even be the same hardware. Instance & server will be named the same as well,
I agree that if it were a different server (instance and/or name) it would far from ideal. But then if that were the case then anything that communicate with the SQL server would fall over anyway, as it wouldn't be able to find the expected host.The DR scenario is more for if the building burns down, is off limits for an indeterminate period of time, etc, so a full rebuild will be needed, rather than a semi functional server replacement.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 13, 2017 at 8:22 am
Thom A - Monday, February 13, 2017 6:53 AMHi all,I made a post a while ago in regards to an update to our DR process we currently have. In that, Perry Whittle posted the following, but I didn't receive any further feedback:
its been said before but i'll say it again. Do not restore system databases across server. For a DR machine sync the objects in the 2 instances
Our DR location is off site, and is isn't an online location. The site has details of the servers we have, and have the same spec machines that we have. Should the worst happen we gain access to those machines and working space, however, at this moment they are offline (they don't even have Windows installed, we have to effectively start fresh using all our backups). It's therefore impossible for me to sync the objects between the two locations.
In an ideal world, yes syncing would be great, but I don't work for a big company. The likely cost of having an always online DR server would far outweigh our needs/budget.
What I want to understand is the reasoning behind Perry's post and why we should not be restoring the system databases. What are the problems behind it, and what kind of impact can that have on the server. Also, as syncing objects is off the table, what would you you suggest is a different plausible solution?
If your concerns are in regards to that a different version might be installed at the offsite location, I feel we do have this covered. We have an agent task sends an email to our DBA team with the current SQL version, which includes the KB. Our emails are all also storde in an encrypted state by a 3rd party, so even if we were to lose our Exchange server (which is backed up as well), our emails will be available for the next 99 years (pretty sure that's our service agreement). The version is also saved in a text file to the same location our backups go to, which goes onto tape and are stored off site on a rotation basis. The agent task happens both weekly and after the service starts (so that should we force a critical update through we know about it).
Any answer will be greatly received. Any questions, I'll be happy to give as much detail as I can.
Thanks all.
If this is what you wish to do then by all means do it. Just bear in mind it's not supported by Microsoft for starters.
There's so much information embedded in master and msdb that you can have real problems, you may be ok short term but the golden rule is, do not restore across servers. Encrypted logins in the instance can be an issue due to the way the service master key is created. Also as pointed out you need to ensure the exact same install settings are used so that file paths are available. Given all the stuff you need to account for it's a damn site easier to sync a server to a DR machine.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2017 at 8:56 am
Perry Whittle - Monday, February 13, 2017 8:22 AMIf this is what you wish to do then by all means do it. Just bear in mind it's not supported by Microsoft for starters.
There's so much information embedded in master and msdb that you can have real problems, you may be ok short term but the golden rule is, do not restore across servers. Encrypted logins in the instance can be an issue due to the way the service master key is created. Also as pointed out you need to ensure the exact same install settings are used so that file paths are available. Given all the stuff you need to account for it's a damn site easier to sync a server to a DR machine.
Thanks for replying Perry.
In response to the part I have emphasised in your post, syncing isn't something I can do (as stated in my initial post). Therefore, firstly I need to understand the problems of our current solution. Secondly, then to see what alternative solution there are, and if they can be implemented and how easily. Thus, as I can't sync, what would your suggestion be to do instead?
In regards to creating a script, unless my understanding is incorrect, users for specific logins are linked. Thus creating the logins again will not create the same link between the two (as the login ID will be different). This isn't the same for Windows Authentication, however, we use both, Although I very much imagine I can easily find something to restore the links, or amend the IDs, by using a if anyone has a script that feel is particularly helpful, I'd appreciate it if they could point me in it's direction.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 13, 2017 at 9:03 am
Thom
I think you just use the "SID=" option in your CREATE LOGIN statement. You get the SID from sys.sql_logins.
John
February 13, 2017 at 9:29 am
Thom A - Monday, February 13, 2017 8:56 AMPerry Whittle - Monday, February 13, 2017 8:22 AMIf this is what you wish to do then by all means do it. Just bear in mind it's not supported by Microsoft for starters.
There's so much information embedded in master and msdb that you can have real problems, you may be ok short term but the golden rule is, do not restore across servers. Encrypted logins in the instance can be an issue due to the way the service master key is created. Also as pointed out you need to ensure the exact same install settings are used so that file paths are available. Given all the stuff you need to account for it's a damn site easier to sync a server to a DR machine.Thanks for replying Perry.
In response to the part I have emphasised in your post, syncing isn't something I can do (as stated in my initial post). Therefore, firstly I need to understand the problems of our current solution. Secondly, then to see what alternative solution there are, and if they can be implemented and how easily. Thus, as I can't sync, what would your suggestion be to do instead?
In regards to creating a script, unless my understanding is incorrect, users for specific logins are linked. Thus creating the logins again will not create the same link between the two (as the login ID will be different). This isn't the same for Windows Authentication, however, we use both, Although I very much imagine I can easily find something to restore the links, or amend the IDs, by using a if anyone has a script that feel is particularly helpful, I'd appreciate it if they could point me in it's direction.
It is extremely easy to dump the logins from one system to another as well as sql server agent jobs and a host of other stuff too.
Pretty much anything in the system can be scripted and moved across.
Typically you won't create lots of users or agent jobs every single day, if you ensure you sync logins and jobs once a day you are pretty much covered.
DR is not meant to be a replacement for your prod, it's meant to be an emergency backup that keeps the core system alive until the prod server is back online.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2017 at 9:58 am
Thanks Perry, but I think you've missed the point of post. The remote server is not on site, nor online. I cannot sync to something, if that something isn't accessible.
This DR is for literal worst case, like the entire building burning down. This it won't be a case of getting the prod server back up because 3 of the drives all decided to simultaneous fall over, but that the server no longer exists. It's gone, as is everything else.
@john-2 Thanks. Seems simple enough, there's even an example (F), on the MSDN CREATE LOGIN (Transact-SQL) page (doh!). I should just be able to combine this with a declaration that the password is already HASHED. I'm not keen on trying to implement this when our next DR test is due in the next month, but definitely worth testing in the mean time and seeing how far I get.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply