Today, we dig back into SQL Server 2012 to look at AlwaysOn, Clustering, Log Shipping , Mirroring , Replication & Snap Shots, focusing on the what, the why and the who cares!
I’ve been asked about these different items repeatedly by various other engineers and users.
Each of these represents a technique which affects a production server. Some are a means to protect data or ensure data access. Others are a means to reduce the stress on a production server by off-loading report-writing, or data-analysis from the production server to another server. One combines both protection and stress-reduction.
Each has its benefits and its costs. Whether one, more than one or none at all fit your needs depends on just what your needs are.
So let’s discuss them. Let’s analyze them in detail to determine what they do, what they cost, and when they are of benefit. As there is so much to cover, I’ll divide this between two separate blogs.
Today, I’ll describe each one in general terms, noting how they are both similar and different from each other. I’ll finish today by going over AlwaysOn and Mirroring in detail. In the next blog, I’ll go over Clustering, Log-shipping, Replication and Snapshots. I’ll also compare the decision points to use when deciding which ones, if any, fill your needs.
Why do we have these different technologies? Because data does not exist in a vacuum! It must be used to be of benefit. To be of benefit, it must be accurate, and it must be available. It also must be timely; that is available in a short enough time period to be read or reported on in time to make good decisions.
And if the data is available in a manner that encourages users to use various tools to easily produce their own reports, there’s often that ”Oh wow!” factor.
We do. Our users rely on us. In fact, they even pay us to provide accurate, time data in an environment where they can access the data without adversely impacting other activities. Each of these techniques either safeguards data by providing redundancy, provides readily usable standby equipment, or provides copies of current data in an alternative environment so production activities are not slowed down.
|Mode||Separate Server||Provides Secondary Database||Data Availability, Latency||Secondary Data can be read for reports|
|AlwaysOn||Y||Y (multiple)||Minimal||Y (real-time)|
|Mirroring||Y||Y (one database)||Seconds||No|
|Log-Shipping||Y||Yes||Minutes/hours||Y (data frozen but updated)|
|Replication||Y||Sub set||Minutes/hours||Y (data frozen but updated)|
|Snapshot||N||Sub set||Minutes/hours||Y (data frozen)|
AlwaysOn is the most recent and sophisticated availability technique from Microsoft on SQL Server. It provides a hot-standby for the servers and duplicate data for the database. It consists of two separate servers and databases. Each server and data storage is provisioned separately from the other server and data storage and are interconnected by Ethernet/networking.
Each is a separate installation of SQL Server, complete with its own license. Each has been installed as a clustered instance. Once each clustered node is ready, the DBA creates an availability group connecting the two servers. One of the servers is designated as the primary server, and the other becomes the secondary server.
Next each database to be protected is added to the availability group. Each of these database(s) is backed up on the primary server, and restored to the secondary server. When restored, the database(s) are left in the recovering mode which enables additional transactions from the primary server to be inserted into the secondary server, while leaving the secondary server in a readable mode.
In fact, leaving the secondary database in this mode enables one of the better features of AlwaysOn—that of being able to have multiple readable copies of the primary database. We can have up to four additional copies of each database to use for report-writing. This really takes a lot of stress off of the primary server.
As transactions are sent to each primary database’s log file, they are copied to each secondary database’s log file. This ensures the secondary databases are kept in sync with the primary. When they’re posted to the respective databases will depend on whether the AlwaysOn is configured as asynchronous or synchronous. (This technical aspect is best discussed in another edition.)
As pictures often speak a thousand words, here’s a schema of a simple AlwaysOn setup:
Which brings us to Mirroring:
Mirroring resembles an incomplete AlwaysOn. In fact, AlwaysOn grew out of Mirroring. Mirroring provides two servers each with its own data storage. The main differences are:
- Mirrored databases cannot be read.
- Only one database can be in a mirror.
- Automatic application redirection via a virtual name for the database. When a database is mirrored, on failover, the database can only be accessed using the new server name. In AlwaysOn, the two servers use a virtual name similar to clustering.
Here is a schema of a Mirrored Database:
As can be seen, it is resembles the AlwaysOn.
Since both approaches cost about the same (two servers, two storages and two licenses), the choice should be fairly simple. In fact, Microsoft has already deprecated Mirroring. It’s likely to be discontinued after SQL Server 2012.
The obvious choice is AlwaysOn.