High Availablility and DR

I just implemented a Hospital Management Information System (HMIS) in my organization which runs on Microsoft SQL server. I have provided 2 servers for redundancy and availability but its not yet configured.

How best can i setup my infrastructure so that both servers will be used for HA. I don't have a SAN or any storage system in place. I currently run on Microsoft SQL server 2012 standard.

I will want my primary server to replicate to my secondary server, so that if primary server goes down, secondary server can continue operation with minimal down time. What is the best availability and DR options in MS SQL Server for me? considering I want to minimize RTO and RPO. Cost is also of essence for me because we are a small organization.


more ▼

asked May 09, 2017 at 03:37 PM in Default

avatar image

21 1

Which edition of SQL Server are you running?

May 09, 2017 at 04:52 PM JohnM

Dang it. Ooops. I see it now. Standard.

May 09, 2017 at 04:53 PM JohnM
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

If cost is an issue, then Enterprise edition just for HA/DR might be out of the question. Log shipping is a very valid solution and low cost. Log shipping has the ability to have a load delay that many other HA/DR solutions such as mirroring, clustering, and AG's don't have. Depending on the number of cores you have licensed, would determine how extreme of a cost Enterprise would run you. Something I always bring up when helping to setup HA/DR solutions, is "what are you trying to protect against". If hardware failure, just about every solution covers that. OS failures, some solutions like virtualization don't have a warm standby and leave you somewhat vulnerable. Storage protection, WSFC with shared storage is out. An oops situation, an update statement without a where clause, well, only a load delay can protect you there. If it takes 8 hours to restore a VLDB, then replicating a drop statement, update/delete statement is near real time. With log shipping and a load delay of 24 hours, you are most likely protected. Rolling changes forward (24 hours of logs, typically takes minutes), so your RTO and RPO are still rather small, for a huge amount of extra protection.

So it really comes down to what are you trying to protect against, and how much $$ can you spend and what are the true SLA's for your data center is a smoking hole in the ground. You may find you need HA for where you are at, and also a DR site.

more ▼

answered May 11, 2017 at 02:59 AM

avatar image

40.9k 39 95 168

(comments are locked)
10|1200 characters needed characters left

If you go with Enterprise, then Always On is best option for HA and easy maintenance compared to Log shipping/Mirroring.

more ▼

answered May 09, 2017 at 07:53 PM

avatar image

240 12 18 19

(comments are locked)
10|1200 characters needed characters left

SQL Server Disaster Recovery high availability options improves the availability of databases. And prevents the effects of hardware or software failures. SQL Server provides many options for creating high availability, main high availability options are going to be discussed here as a solution for disaster recovery: You may go through this to choose correct option: http://www.sqlmvp.org/disaster-recovery-with-high-availability-options/

more ▼

answered May 18, 2017 at 04:29 AM

avatar image

421 3 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 09, 2017 at 03:37 PM

Seen: 75 times

Last Updated: May 18, 2017 at 04:29 AM

Copyright 2018 Redgate Software. Privacy Policy