[SQL] I am confused about “Snapshot” “Mirror” “Replication” and “Failover Clustering”


Recently I am reviewing these solution in my environment. For my company, we already built the stable SQL server and rarely to look at what kind of solution is the best for us.
Just for my understanding and I still have lots knowledge needed to know
If your company have a individual central data center, you probably have this solution to run SQL Server.
It means the SQL server replicate data from local server to the main server (data center). And it’s a geographic replication.
Which one is standby? The answer is none of one.. Both of publisher & subscriber SQL server are active.
This solution is good for one of SQL Server Disaster recovery plans.
Failover Clustering)
Basically I don’t like this method, but Microsoft recommends this very much.
WHY? I guess because it can make more money to Microsoft. Because you need a Hyper-V, Windows Server Enterprise version , good CPU and hardware environment. And a external storage such like SAN (Storage Area Network)
You want to build a High-Availability SQL, this is what you want. But try to think about, an SAN in a company that usually not only used for SQL Server, but also used for others server such like VMWare. So the storage is shared and efficiency are shared…
This is a good solution what I like. If you have a local server. The built steps are easy and convenient to finish. Your transaction log are merged to your mirror SQL server without restoring logs manually. And the data delay is very less, just like you r looking at a mirror. What the mirror shows up is what you have. Kinda handy?? Yes, it is. Or you can say this solution is like a lovely couple, just needs a good “witness”.
Only one thing is one server failover(SvrA), the mirror server(SvrB) will active up. Then the SvrA is fixed up, but you need to failover SvrB manually and push active role back to SvrA. It’s not bad, and not like “Replication” or “Failover Clustering” solution
If you have experienced VMWare server (like ESX), Snapshot is an essential step that you need to do. You can think SQL server save a copy of original page before you affected/changed table.

My own conclusion:
Failover Clustering + Mirroring…
Newer Older