Regarding Microsoft SQL server Disaster recovery solutions

Hi All,

We are planning to implement MS SQL 2012 DR for our PROD server. This PROD server is one of the critical server in our environment and DR location is remote location (approx. 50 KM from primary site).

As I said, this is one of the critical server and we have defined RPO=0 minutes and RTO = 15 minutes for this server.

The available DR solutions in MS SQL are DB Mirroring, Log Shipping, Clustering and Replication. Please suggest me which DR solution we can choose as per our requirement (above mentioned RPO and RTO time) and how much N/W bandwidth (approx.) is required for suggested DR solution.

Waiting for valuable suggestions from MS SQL experts.

Thanks in advance.
Who is Participating?
ZberteocConnect With a Mentor Commented:
Always On only works on the 2012 Enterprise version. So you will have to have at least 2 servers both 2012 enterprise in order to be able to implement it. If that is not possible then you will have to consider alternatives like mirroring from 2008 to 2012.

Mirroring in the actual simple form only allows on mirror and is database by database and not by server instance, Which means if you have 2 databases that are mirrored and one fails it will be failed over but the other one stays on the primary server. It works differently and is not as flexible.  

Another option is to use log shipping which is simpler and allows multiple replcias.

Replication also works and makes both servers available to use. However is is more complicated to setup and to maintain and the replicated databases are actualy not 100% replicas of the publications.

Just watch those videos and you will see explained all teh advantages and disadvantages of any of these HR-DR solutions.

Pint is that Always On is the best solutions for HA and DR. The only problem is the licensing. issue, which can be a big one.
Barry CunneyCommented:
Hi sg
There is a Note in the following Technical Article about Database Mirroring being removed in the future

"Database mirroring
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use AlwaysOn Availability Groups instead."
sg05121983Author Commented:
Thanks BCUNNEY for the information.

Please suggest me which other DR solutions we can choose other than AlwaysOn as per our RPO/RTO.

Please note that, our PROD server version is SQL 2008 Ent edition and DR server version is 2012 Ent edition.

@MS SQL experts: Pls share your thoughts on this.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Firs we need to know what version your production server is and then we can delve into possibilities.

If you have 2012 Enterprise version then you best choice is always on, which practically gives you RTO and RPO = 0!
sg05121983Author Commented:
Hi Zberteoc,

My source server (PRDO) version is SQL 2008 (Ent edition) and destination server (DR) version is SQL 2012 (Ent edition), can we implement Always On feature for DR purpose?
Yes. It is by far the best solution MS SQL serve has come up with so far. It combines mirroring but an improved one with up to 4 mirrors at the same time, that are available for read only access, and the automatic clustering failover. It is relatively simple to setup, to monitor and works perfectly. It fails over in seconds if your primary server doesn't respond and the secondary becomes primary. The failover is smooth and your applications won't even noticed that happened. With AO the applications don't point to the database servers directly but to a listener that is setup for this purpose(basically a virtual ip that acts as a pointer) which always will route the request towards the active primary server. When you bring back your original primary server it can stay as secondary so you don't need to fail back if you don't want to.

Your secondary server(s) can be used for reporting or for other purposes where you need to access data for read so that the load is balanced.

Here is an overview article:

And here some detailed videos:

There are 3 videos by the same guy and they are not short but if you are patient you will understand how AO is better compared any other HA -DR solution and how to set it up.
sg05121983Author Commented:
Thanks for the detailed information.

As I said, my prod server version is SQL 2008 (primary site). As per below URL we have to enable AlwaysOn feature on both the SQL instances (primary and DR). But my primary site (PROD) version is SQL 2008. Please let me know how to configure/enable/implement in SQL 2008.

Sorry, I am asking lot of questions.
sg05121983Author Commented:
No comments
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.