SQL Server Database real-time replication

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!

Answers


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/


Need Your Help

Joomla! 2.5 - Implement custom table as plugin or module?

php html joomla

Just started developing with Joomla! (2.5), completely newb, read through the docs talking about Plugins, Components and Modules. Having a hard time figuring out which to choose and how to implemen...

WPF - Have a list source defined at runtime but still have sample data for design time

c# wpf data-binding mvvm binding

I have some ListBoxes in my WPF app. I would like to be able to view how the design looks with out having to run the app.