High Availability with SQL Server - What is the solution for what? Hello everyone!
SQL Server has long broken the necessary data availability. But what are the various solutions proposed, and what can we expect?
I am proposing today a summary of each solution proposed by Microsoft with SQL Server 2005, and what you can reasonably expect from each.
LOG SHIPPING AND Mirroring - TWO SOLUTIONS, AN OBJECTIVE Many have heard of Log Shipping, many people have heard of mirroring ... But to what end, what impact on the existing, what maintenance?
The Log Shipping has long been recognized as an inexpensive solution, simple to implement, but rather difficult to operate, particularly during a failover server. Indeed, failover is manual, the latest data loss likely, because the downtime varies depending on the reactivity of the DBA. The
Mirroring can be seen as a solution of Log Shipping evolved. If SAFETY OFF mode is selected, the mirroring effect behaves like log shipping, copying the transaction log over the water. The latency is generally lower than in the case of the Log Shipping, and the implementation is completely automated.
However, if one selects FULL SAFETY, Mirroring ensures the user a perfect synchronization of databases, that does not allow the log shipping.
Moreover, the use of WITNESS automates the process of switching servers in case of error. Whether
Log Shipping or Mirroring, initialization of databases is performed by Backup / Restore. Given these similarities
, I prefer therefore largely Mirroring in SQL Server 2005 which allows a latency much smaller than his buddy Log Shipping.
Furthermore, the use of WITNESS provides automatic failover of the most significant, and moreover one of the industry's fastest with a time of no service less than 5 seconds.
REPLICATION AND DELIVERY DATA The use of the data distribution as a means of high availability is a commonly used method.
There are many ways to distribute its data. The most simplistic is the copy batch data at regular intervals. This method is very simple to implement similar applications in the Log Shipping, even if its method is radically different. Moreover, it is possible to use the database obtained by reading for example.
For the basics of low volume and bearing the risk of data loss the most recent snapshot replication is also this problem. However, unlike the batch copy, snapshot replication supports the very difficult handling large volumes.
Finally, for distribution to ensure greater freshness of data, transactional replication also offers an interesting compromise. Again, the data are read. In contrast, the transactional publication should be disabled to make any change whatsoever in the data structure. Additionally, transactional replication is often very fragile when a maintenance operation on the transaction log is necessary.
The distribution of data, whether a batch or replication, as its low latency as it is, is not strictly speaking a true high availability solution. However, it can be an effective compromise to overcome a possible failure of the system while retaining the use of all machinery purchased.
CLUSTERING
clustering method remains the queen of high availability, allowing, among others not specify a single network address for multiple physical computers, thus providing seamless failover for client applications.
To be implemented, clustering requires advanced knowledge on either the SQL Server as the operating system. The black dot
clustering proposed by Microsoft remains idle backup servers, inducing an extra capital cost. Moreover, clustering does not provide solutions in case of physical destruction of storage media - eg fire - since these physical resources are shared between different nodes (I shall not mention here the possibilities of geo-cluster for reasons of simplification).
Regarding the additional capital, we must still note that under the clustering, as in the mirroring, a backup server assets less than 30 days per year requires no license.
The deadline for server failover in the case of clustering around 30 seconds, to be compared with the 5 seconds of mirroring.
Unlike its competitor Oracle, SQL Server does not have a real clustering solution Active-Active. Microsoft prefers to emphasize the increase in single-server performance. For those whose architecture would require a scale out, it is possible to implement an alternative solution of creating two clusters Assets and Liabilities crossed simulant is Active-Active clustering.
REPLICATION PEER-TO-PEER and Mirroring "ACTIVE-PASSIVE" Microsoft also offers other ways to use the various machines while minimizing potential data loss in case of system crash.
The older of the two methods I will present here is replication Peer-To-Peer. It is neither more nor less than a bi-directional transactional replication.
This type of replication is extremely useful as part of backup locations offshore. In addition, all servers are active. If we multiply replication nodes, this method is very effective and can even consider a load sharing (load balancing). Contrary to clustering, each machine is individually identified, and consequently the load sharing should be managed at a lower level, for example using a switch active.
Another technique, based on mirroring, can have read access to databases. This method requires a shared resource using, for example, a SAN array, and only one server accesses the database in writing. This solution can be very interesting in the case of DataWarehouse requiring high computing capacity at Reading. A mirror server takes over in case of failure of the master server.
Replication Peer-to-peer solution is a widespread but often used for its approximation capabilities of user data than for high availability and procured.
CONCLUSION I will conclude this post by making the following observation: the business need driver obviously the choice of technology, both by financial factors than by aspects of criticality data. It is therefore important to carefully consider all aspects before setting a server architecture of databases, this conditioning also developing applications revolving around it.
you soon!
