I am tasked with building a highly available SQL Server 2014 Enterprise environment for our new policy admin system. This database will eventually grow to 1TB +.
Background
Our normal method of disaster recovery is that we replicate our storage to a second array at our DR facility. During a disaster we use a product called Site Recovery Manager by VMWare that mounts the storage LUNS from the replicated SAN, reconfigures the VM’s with local IP addresses and then starts them on the DR network.
I have several products that use SQL Server and work very well with this solution. I am able to bring up the databases and the application servers on the new network without issue.
Problem
I have several requirements for this new environment that I am not sure how to handle.
- We have a requirement for a read only copy of the database for our data warehouse reporting.
- They would like very up to date data, so nightly backup is not an option.
Based on some reading it sounds like log shipping is not going to be an option as users will get kicked out when restoring logs.
I have been looking at Always On Availability Groups, but I don’t believe they will work with our current disaster recovery mechanism as all the inter server communication is bases off IP addresses and not DNS names, so when we re-IP the servers and bring them up on the DR network nothing is going to work.
I have also considered an Availability Group with a replica located at our DR facility but not sure how everything would work during a DR test and once we want to return to normal working mode. We do have domain controllers in DR and we simply shutdown the real one and restore one with our backup and use it during the DR test. Once we are done with the test we shut down the restored DC and bring the real one back up.
Question
Does SQL Server 2014 support read only copies without Availability Groups and if so will users loose connectivity while replication is occurring? If it does then I can hopefully use this method and then I believe our DR method will work. That is if I can use names when configuring read only and not IP addresses.
Any direction would be much appreciated.
Best Answer
Typically people just have an AG node in their DR environment and do not transfer VMs there (for some of the reasons you noted + more). You should read through the documentation for how AGs work, especially planned manual failovers for when you need to switch node roles.
You could also look at SQL Server replication to replicate your data (even close to real time). It work with DNS and even though the DB is not read only per se, you can set user privileges so they don't modify anything. The only thing to check is it requires the tables to have a primary key. This could be a good option for your reporting requirements.
Can you consider an upgrade? SQL Server 2016+ has Distributed Availability Groups, which could really solve most of your problems. With Windows Server 2016 you can have cluster nodes from different domains. Your AG would not require it to be in the same domain or network and you can even mix SQL Servers. Read more in the documentation for Distributed availability groups.