What is best practise if we have application using a SQL Server database and we would like to have backup if SQL Server does not work?
Our scenario:
- We would like to use our main SQL Server database
- If main SQL Server does not work, we would like to use a different SQL Server database with the same data.
- If the main SQL Server works again, we need to sync data back to the main database
- There is not possible generate same data, because every record use UID.
- We can use SQL Server 2014 or SQL Server 2012.
Failover Cluster is option, but are they any other options?
May there will be help from some web pages where are those situations described and recommend what is best in each situation.
Best Answer
It sounds like what you are looking for is an Always On Availability Group in Synchronous Commit mode with automatic failover. The transactions sync from the primary to the secondary and in the event of a system failure, will pass ownership of the database to the secondary. Your application would need to be aware of both the primary and secondary and which one is active. This feature does make use of the cluster service but you don't have to have an actual cluster with shared storage (see this post from Brent Ozar for more details about hot to work around that). This is the more expensive option, since it requires Windows Enterprise.
You can implement mirroring in high safety mode which does essentially the same thing on any edition of windows, even a desktop if you want, since it doesn't require clustering features. You won't get all the extra benefits, but if you just want to have a synchronous hot standby of your database then it will do that. The caveat here is that mirroring is deprecated, so if you implement this now, you will eventually have to migrate to availability groups in the future.