SQL Server Database real-time replication

Question

I have a database on an SQL Server instance hosted on Azure Windows VM. There are two things I need to achieve.

1. Create a real-time duplicate of the database on another server. i.e. I need my database to make a copy of itself and then copy all of it's data to the duplicate at regular intervals. Let's say, 2 hours.

2. If my original database fails due to some reason, I need it to redirect all read/write requests to the duplicate database.

Any elaborate answer or links to any articles you deem helpful are welcome. Thank you!

Answer 1

You can have a high availability solution for your SQL Server databases in Azure using AlwaysOn Availability Groups or database mirroring.

Basically, you need 3 nodes for true HA. The third one can be a simple file server that will work as the witness to complete the quorum for your failover cluster. Primary and Secondary will be synchronized and in case of a failure, secondary will take over. You can also configure read requests to be split among instances.

If HA is not really that important for your use case, disaster recovery will be a cheaper solution. Check the article below for more info.

High Availability and Disaster Recovery for SQL Server in Azure Virtual Machines

https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-sql-server-high-availability-and-disaster-recovery-solutions/