– “Progress comes at a price.” Edwin Sarmiento-

I’ve responded to customers’ inquiries about the benefits of implementing SQL Server 2012 AlwaysOn Availability Groups since Microsoft released a public CTP of “Denali.” I’ve delivered presentations, written articles, prepared proof-of-concept designs and even recorded a video on this topic. While it may be a new feature in SQL Server 2012, the concepts are the same as in the older versions of SQL Server. In a few words, I describe AlwaysOn Availability Groups as a “database mirroring configuration sitting on top of a Windows Failover Cluster infrastructure.” Why do I say this? It’s because I want SQL Server DBAs to leverage what they already know on features like database mirroring and failover clustering and apply them when dealing with AlwaysOn Availability Groups.

With the drive to maximize IT investments in organizations, I also get asked a lot about the concept of the readable secondaries. Now, I’d be honest. I try to stay away from anything that pertains to licensing but I sure do answer any technical question that may arise during the discussion. One of this is the concept of redirecting read-only workloads on the secondary replica. Sure, this is a great idea because you can offload your reporting applications from the primary replica to the secondary replica.  You can, then, provision faster disk subsystems and more memory on the secondary replica to accommodate the read-only workload that you can now run against it. This concept is really nothing new. It’s the concept behind a mirrored database that you take a snapshot from so you can run reports against it. Because of the maintenance overhead of creating database snapshots against mirrored databases, not too many customers implement it. But those who do understand what they’re up against. They know that the tempdb database on the mirrored server will now be experiencing more load because of the versioning applied to the mirrored database when a snapshot is taken. Now, Readable Secondaries are basically that – a mirrored database that is constantly getting row versions shipped from the principal database. Since you cannot make any changes – whether schema or data –  to the mirrored database (or Readable Secondaries for AlwaysOn Availability Groups), neither can the version store in the database engine. Can you guess where those changes will be made?

Answer: The principal database (or the primary replica in the case of AlwaysOn Availability Groups.)

With that in mind, all records that are being modified in the primary database will automatically get an additional 14-bytes as a side effect of turning on Readable Secondaries. Now, you might be thinking, “14 bytes isn’t all that bad, is it?” Think again. A 100 byte row will grow by 14% once you make some modifications on it. Think about what that will do to your pages – index page splits that may cause index fragmentation or the allocation of a new data page if there isn’t enough space on the data page where the record is being stored. This also affects the amount of transaction log records that get sent to the secondary replicas and, therefore, affect the failover time which affects the overall recovery point objective and recovery time objective (RPO/RTO.)

To prove the point, let’s look at a record from a database that is configured in an AlwaysOn Availability Group. Using the undocumented DBCC commands – DBCC IND and DBCC PAGE, we can take a look at a particular record inside a database.

DBCC IND ('membership','tblCommittees',1)
GO
DBCC TRACEON (3604)
GO
DBCC PAGE (membership, 1, 203, 3)


Example of using DBCC IND with DBCC PAGE

I’m using a sample database called membership with a table named tblCommittees for this example. Using the DBCC IND command, I was able to retrieve the Page ID value for the record that I want to look at, in this case, page 203. Currently, the row containing the CommitteeName column value Membership has a length of 63 bytes. The membership database is joined in an AlwaysOn Availability Group but not configured with readable secondaries. Even if I modify the value of the CommitteeName column from Membership to Membershop, the size of the row remains the same.

After turning on readable secondaries on my AlwaysOn Availability Group, I updated the same column again, reverting it back to Membership. This time, the record size has increased by an additional 14 bytes, bringing it up to 77 bytes.

This is the version store kicking in, appending 14 bytes in the row to contain the row versioning information. You’ll see the output of the DBCC PAGE command displaying the version information for the updated record (the size of the column remains the same because I’ve only modified one character in the column value. )

This is just one of those things to keep in mind when implementing AlwaysOn Availability Groups readable secondaries. make sure that you properly test your environment with your existing workload, including administrative workloads such as batch processes and index maintenance.