Home Forums SQL Server 2014 Administration - SQL Server 2014 SQL with High availability, disaster recovery and online (read only) reports availability RE: SQL with High availability, disaster recovery and online (read only) reports availability

  • Your SQL environment is only part of what you need to consider, and the optimal solution for your situation will depend on other factors.

    * Do you have multiple data centers, if so what is the bandwidth you have or can move to between them.

    * How is your storage organised, is it virtualised in any way within a single data center or between data centers

    * What server virtualisation do you have in place

    * Last but also very important, what portion of your solution are you willing to put into the cloud, and what are the capabilities of your preferred cloud supplier

    I currently work at a charity, which means substantial discounts from hardware and software vendors, and the resulting costs mean that keeping our DR in house is the cheapest option. We operate out of two data centers in the UK, one in London the other 150 miles away in Wales and have excellent band width between the sites. Our storage is virtualised across both sites, with storage-layer replication that mirrors a given LUN in both locations. Our server layer is also virtualised using Hyper-V, and we can run any given server image at either site. We also replicate just about all data to a third site 250 miles away in Scotland that just has a file storage capability.

    We have chosen to use SQL 2014 with clustering to provide our HA and DR. The underlying infrastructure allows us to run one or both nodes at either site, but typically we run only one node at a given site. Failover then becomes the normal failover time for a cluster, maybe a minute or two for the busiest systems.

    We did consider using Availability Groups, but found this very difficult to set up at the infrastructure level. However, we do plan to try this again in a few months.

    SQL Server gives some very good options for HA and DR, but you cannot choose what will be best for your organisation until you know the capabilities and limitations of the underlying infrastructure, and also the capabilities of the staff who will have to build and maintain what is put in place.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara