Understanding AlwaysOn & Mirroring with SQL Server 2012

SQL-Database-The-Next-Byte

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 otherI’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?

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.

Who Cares?

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
Clustering Y No Seconds N/A
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

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:

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:

  1. Mirrored databases cannot be read.
  2. Only one database can be in a mirror.
  3. 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:

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.

 









VN:F [1.9.22_1171]
Rating: 9.3/10 (16 votes cast)

About Fpweb.net Crew

Our business is centered on bringing enterprise-class strategy, support, and security to your hosted or managed platforms no matter where you choose to deploy your environment. We specialize in providing managed services, cyber security, and expert, USA-based, 24/7 Absolute Support® on-premises, or in any cloud.
This entry was posted in SQL Server and tagged , , , , , , , , , . Bookmark the permalink.

7 Responses to Understanding AlwaysOn & Mirroring with SQL Server 2012

  1. Jonathan Hall says:

    Good article thanks Don. Helped me understand the difference between Always On and Mirroring. I’m looking for the 2nd article you mentioned would be coming but I can’t find it when I search. Can you point me to it please.

    Thanks
    Jonathan

  2. Daniel Reed says:

    Great article, thanks. Only one point to add. The obvious choice is AlwaysOn, except that you require SQL Enterprise.

  3. Niren says:

    Great article!

    i am just wondering …I am from a Product company…

    so does it make sense to ask our customers to do an AlwaysOn Architecture …Also testing the application on both these setups could render differnet results…?

  4. Saibala says:

    Good article. Practically useful.

  5. Saibala says:

    Would be nice if second blog is also available.

  6. ankita says:

    I need to submit my kt to my manager. This post is exactly what I wanted. Please send the link of next blog. I need it urgently.

  7. ankita says:

    @Saibala
    Hey Do you have the link to next blog?

Leave a Reply

Your email address will not be published. Required fields are marked *

Let's make sure you're human first: *