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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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!
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sg05121983Author Commented:
No comments
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.