February 3, 2009 at 3:09 pm
Assuming I have only 1 box to practice on, what's the best way to simulate a complete server crash. I want to practice doing a restore on a "fresh" installation of SQL 2005. It's a small database, so full backups will be done on a nightly basis.
Just not sure of the best way to simulate this on a single box.
Also, how do applications handle this? Is it best to name the server the same as the old dead one? Anything else I should be aware of?
Thanks!
February 4, 2009 at 11:07 am
What do you want to simulate? Hardware failure? Database corruption?
You also mentioned you have only 1 box. Did you mean you have just 1 database server which is your production server? If the only SQL Server box you have is production, then please do not try and simulate any kind of failure.
February 4, 2009 at 12:09 pm
I would like to simulate hardware failure.
Yes, I have only 1 database server and it's dev. Funds to purchase a production server should free up hopefully in the next couple of months.
It's an access database that has been migrated out to the text box, so all the data is still safe since the sql box is not used for any other purposes right now.
Hope that helps clear things up a tad.
I'm just assuming that we will only be able to purchase 1 production sql box. So if it's hardware completely gives out, then I will need to restore on a new/another server.
February 4, 2009 at 12:22 pm
I don't think you need to simulate a hardware failure. You just need to simulate your restore process. That could be just restoring the database from backup or rebuilding the entire server and restoring from backup. Depends on what you're really trying to test.
Greg
February 4, 2009 at 12:30 pm
Ok. I did do a restore of just the db itself and that worked.
I haven't tried it restoring the other system databases though.
I suppose I could try to acquistion another test server and reformat/reload it and then try doing a restore on that one.
If that all proves successful, I should be a little more comfortable about DR shouldn't I?
Thanks all!
February 4, 2009 at 12:39 pm
Assuming that you are testing the database after restore, then yes, you should feel pretty comfortable. At the last company that I was at, we did full disaster recovery tests twice a year. That meant installing SQL, applying the correct service packs, restoring user and systems databases and testing applications. It actually took a couple of DR tests to get the process down pat. Where I'm at now, we do DR tests and we also restore a random database once a week to verify backups.
Greg
February 4, 2009 at 2:08 pm
Greg's spot on. Its not just restoring the database. A full test of the applications, reports & processes is required for DR.
As the database is not in production use then its the ideal time to build up your DR processes in preperation for when it goes live. Also, Gregs company employs a good policy. The weekly random restore will keep your sharp on the whole restore process which you'll need should you have a DR scenario in the real world.
Just make sure you have good DR documentation so that you can rebuild the server and not miss a step.
Oh and nice avatar, Greg 🙂
February 6, 2009 at 7:36 am
So, I did a practice restore on another server and now I have a question.
In the real world, if I did lose the server, would it be best to name the new one the same as the old?
Is why I ask is because once I restored to the other server, I had to go and change all of the connection info in our test apps to connect up to the new server successfully.
I also wanted to find out what would happen if I backed up from RTM to another server that has SQL SP2 on it. :hehe:
The user database restored but the master and msdb said no way. What did I miss out on those not restoring.
Also, which db is all the login info for sql stored into?
Thanks.
February 6, 2009 at 8:11 am
jbloes (2/6/2009)
So, I did a practice restore on another server and now I have a question.In the real world, if I did lose the server, would it be best to name the new one the same as the old?
Is why I ask is because once I restored to the other server, I had to go and change all of the connection info in our test apps to connect up to the new server successfully.
I also wanted to find out what would happen if I backed up from RTM to another server that has SQL SP2 on it. :hehe:
The user database restored but the master and msdb said no way. What did I miss out on those not restoring.
Also, which db is all the login info for sql stored into?
Thanks.
I guess it would depend. If the old server is a smoldering pile of rubble, and will not ever be reactivated, then re-using the name is a possibility. If there's a chance that it just needs to be fixed and eventually brought back online - hard to say. The bigger question might be to use your DR exercise to make sure you have all of those locations which might need the new name documented somewhere. that way - you got both angles covered.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 6, 2009 at 8:49 am
In a typical disaster (Is there such a thing as a typical disaster?) you would rename the new server the same as the old server, so that you wouldn't have to change all the connection info. Depending on the size of your environment, finding all of the connection info and changing it can be a daunting task.
All of the login information is stored in the master database. Did you receive an error message when you tried to restore msdb and master? Msdb is usually pretty straight forward to restore. Master, on the other hand, can be a little tricky. One common issue that I have run into is that all of the user databases have to be in place and in the same file locations before you can restore master. I discovered a workaround though. As part of my disaster recovery plan, I had a script that created all of the user databases first. Then, I could restore master and bring the server online, even though none of the data was restored yet. Finally, I would restore each user database over each empty placeholder. This allowed applications with small database to be brought back online very quickly. Obviously, applications with large databases still would take a while, but we didn't have to wait for everything to be restored before the server came back online!
Greg
February 8, 2009 at 3:09 am
grab yourself a copy of VMWare server or MS Virtual Server Ent 2005, they're both free.
set up a sql server VM and practice DR on that 😎
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 9, 2009 at 6:30 am
I believe you cannot have two servers with the same name connected to a network. I don't think DNS allows that.
For testing you would need both servers up, in addition to the 2nd server acting as a warm/cold standby when you're ready to "go live".
How do you prevent having to change the application connections during a disaster recovery?
If the applications are using a DNS ALIAS, then all you need to do for recovery is have the DNS name point to the IP of the standby server, then the APPS will never need to be touched. Of course, the logins and pw's would have to be the same on both servers, and you would have to touch the applications at least once to have them use the ALIAS name.
February 9, 2009 at 12:20 pm
I couldn't find a free MS Virtual Server...do you have the link?
As far as DNS Aliasing goes, is that something that is setup within the application or is the alising setup at the dns server and then just point the applications connection to the alias name?
Good stuff to know.
February 9, 2009 at 2:47 pm
jbloes (2/9/2009)
I couldn't find a free MS Virtual Server...do you have the link?
http://www.microsoft.com/technet/virtualserver/software/default.mspx
you will need to register, thats the only requirement.
Steve (2/9/2009)
I believe you cannot have two servers with the same name connected to a network. I don't think DNS allows that.
this is easily achievable by the use of virtual machines. Create a whole virtual network of machines that are totally segregated from your live network
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 9, 2009 at 2:52 pm
Perry has a great idea. Get a network number (like 192.168.25.0) that isn't in use on your real network, and assign all the VMs network addresses on this range. They'll communicate and not affect other clients.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply