Options for HA Configurations? Not Replication!
If you’re considering high availability options, Microsoft SQL Server Replication is not an option. Microsoft SQL Server has other H/A options such as Clustering, Mirroring, and AlwaysOn that deal with your RDBMS or database as a whole. Log Shipping is typically used as a low-cost H/A implementation, however, its best used as a secondary option to replication. The reasons for not looking at these methods as true HA are related to administration and fixing synchronization errors. Replication and Log Shipping are great options for improving performance on your main RDBMS by redirecting certain types of user transactions.
Your High Availability considerations should start from the ground up. What I mean by this is starting from the hardware layer working up to the software layer. Consider the following graph incorporating the different SQL Server technologies, infrastructure cost and DBA administration.
As you can see, I do not include replication. The reason why is because replication should be considered as a data distribution technology rather than an H/A technology.
With replication, you can replicate as much or as little data to meet your business and/or application needs. Examples include:
- using merge replication to distribute application users over topographical regions
- using transactional replication to offload near real-time application or reporting needs from your main system to secondary systems
- using snapshot replication to create a day-old reporting environment
Replication offers performance improvements that many database infrastructure environments overlook.