Log Shipping as a Disaster Recovery Solution

A DBA must be able to recover his/her SQL servers whenever a disaster occurs. There are many ways of doing it, but at the end the choice you make depends on your company requirements. In my case I decide to use Log Shipping as my disaster recovery solution. In this post I will not explain how Log Shipping works; there are many articles on the internet about it, what I will explain its how I recover from a Disaster using Log Shipping.

When Disaster strikes

Put yourself in this situation, all your SQL servers are gone. What are you going to do now? That question I can’t answer for you, but I can tell you what I will do in that situation. As I already mentioned, I chose Log Shipping as my disaster recovery solution. My company has a Disaster Recovery location where I have one standby SQL server. That server is receiving all the logs from my production databases through Log Shipping. So, first thing I got to do is to go to my disaster recovery location and make sure I restore the last transaction log backups. After doing that my databases will be ready. My next problem is how to let the applications talk to my standby SQL server?

I have two ways of doing it. The first and easiest is using the DNS to forward the connections to my production servers to my standby server. Then the question is how do you do it when your production SQL servers are using named instances instead of default? Well the answer to that question is to make sure your standby SQL server has the same named instances as your production servers, and then it will work (I can assure that because I tested it). Take note that there is also a chance that your applications will not work only with the DNS forwarding, then you will have to do the changes in the applications, which can be a lot of work (I hope you don’t have to do it). In case you have to, make sure you have the proper documentation for that, you can ask that to your software providers.

What about your logins?

Ok, so my databases are ready, so what about the logins, log shipping does not send the login information to the standby server. The way to solve the SQL server logins problem is described by Microsoft see the link http://support.microsoft.com/kb/246133 using the stored procedures SP_hexadecimal and SP_help_revlogin you will be able to retrieve the logins and its passwords. The next step is to make sure you build a package that sends the output of  SP_help_revlogin to the standby server so that you can just recreate the logins there.

Port numbers

The best thing will be if you set the port numbers of the standby SQL server to be the same as in the SQL server production servers. Some applications use the port number of the SQL server in their configuration files. You can check the port numbers in the SQL configuration manager, if you have any doubts you can also check the error log of the SQL server instance, there you will see something like “Server is listening on [ ‘any’ <ipv4> 1433]”.

I already told you how I use Log Shipping to recover from a disaster. It is now up to you to decide how you are going to recover from a Disaster. If there is something I can help you with don’t hesitate to contact me.