This article isn’t going to be instructional, but it’s going to be really helpful if you are trying to understand what is the best solution for you in this dark forest of MSSQL HA solution.
|Method||Log Shipping||Mirroring||Replication||Always On|
|Implementation Method||Take the transaction log backup from one server and restores on the secondary using a managed scheduled job.||Send the VLF (Virtual Log File – the content of the transaction log files) directly to the secondary server.|
The restore is done automatically.
|The VLF’s are being read by the Log Reader Agent which passes them on to the distributor which manages the distribution to the other servers.|
You can replicate a whole DB or even a table.
|Send the VLF (Virtual Log File – the content of the transaction log files) directly to the secondary server.|
The restore is done automatically (Same as mirroring).
|Access to Secondary||Always on “Restore Mode” and therefore not accessible.|
You can put it on “Standby” mode after the restore and access it but it will become in “Restore Mode” each time there’s will be a transaction log restore.
|Always on “Restore Mode” and therefore not accessible.|
You can create a snapshot of the DB once in a while and work with it.
|Readable and Writable, although writing can jeopardize the Replication process.||You can choose, but all in all it’s readable and accessible at all times.|
|Why not?||Delay of 5-15 minutes (depends on your log shipping settings).|
Can’t really work with the secondary DB.
No automatic failover.
|Can’t work with the secondary DB.|
If you work with snapshots you have delay and the data is not always the same.
If you wish to have automatic failover you need to configure it on the client side.
2 Servers only.
|Changes the schema.|
Each table has to have primary key.
No automatic failover.
Worse performance because the synchronization is at the row level.
|Hard to configure.|
Requires AD domain.
You have to manage a lot of components (domain, DNS, MFCS).
|Why yes?||Suitable for clients who wish to have cold / asynchronous backup with fairly quickly restore.||If you wish to have hot backup for failover purposes and no need for more than 2 servers.||Good solution for reporting DB that doesn’t necessary requires all the data.|
Could be used at some cases as a Load Balancer for DB’s.
|For environments which requires automatic failover and a readable secondary for load balancing purposes.|