SQLTeam.com | Weblogs | Forums

What kind of technology should i use?


I have a database application, that talks to microsoft sql, i want to create some redundancy, so if Server A fails i can still work from Server B, i been using mirroring, but the fact that it requires a witness for auto switching is a pain.. my requirements are simple:

a- Data must be equal between the two servers all the time
b- in case one server goes down the other must be able to pick up right away with no data loss..


What version and edition of SQL Server are you using?

Have you thought of a failover cluster, availability groups?

Do you need the secondary system to be in a separate data centre/location?


It will be 2016, the secondary system is another vm at the same location...

And yes im looking at availability groups and clustering, but not sure where is the benefit in that when compared to mirroring.


Availability groups would give you failover without the requirement of witness server although that may depend upon the edition you are using. It also uses DNS listeners so you connect your application to a virtual server name, you can do this with mirroring but that is a manual process which you would have to change if you failed over. If there is a failover the AG Listener automatically points to the secondary server with only a minimal of time.

You have mentioned using VM's at the same location, I assume this second VM is not housed on the same host server and or using virtual disks on the same pool of physical disks. I spent quite some time working out why a customers mirrored pair of SQL Servers was running so badly only to realise that both sides of the mirror were using the same disks so you have a disk being read at the same time the data was being written to the same disks...as soon as we moved the secondary to a separate system it flew.

I think that mirroring would achieve your requirements (infrastructure dependant) if you set it up as High Availability which would get as close to no data loss as possible, any transaction would be written to the secondary and committed before it gets committed to the primary. You would need a witness to achieve auto failover but even then you would have to repoint your application stack to the new server. You could of course have a second application stack always pointing to the secondary SQL instance but you would need some further infrastructure/components that would then change the servers the users hit.

Clusters are nice and easy, you only have one set of databases to worry about so haven't got to deal with setting up all your secondaries and should a failover over occur your application should just keep running (there are some that do not cope with cluster failovers but that is an application issue not a SQL Server one). The downside is that a cluster, while having multiple hosts, tends to use shared disks so your disks can become a single point of failure. We personally get round this by having production clusters in one datacentre and DR services in a separate DC.

I have also used AG's and this, I like to describe them, is like mirroring on steroids, it uses parts from clustering, mirroring and log shipping to give you a really powerful option but to benefit from this fully you need to be using Enterprise Edition of SQL Server.

Sorry I cannot answer your question with a "you should do this" as every environment is different but I hope this gives you some information to muse over.


Have a look on AlwaysOn Availablity Group in SQL Server To know more about Availablity Group in SQL Server.
visit: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server
To add AG visit: http://www.sqlserverlogexplorer.com/adding-alwayson-availability-group-existing-failover-cluster/