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