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