2012 always on and reporting

Question

2012 always on and reporting

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Russell.Morgan-813114 Say Hey Kid Points: 707 More actions · Answer 1

Woah! That is worrying. Part of the over all solution is to off load your backups, DBCC work, and reporting. So if I am reading you correctly, what you are saying is reading the secondary database would cause ghost cleanup activity, etc. Forgive me, that does not make sense. Can you explain how reading from a table would cause ghost cleanup activity? Remember I'm only interested in reporting. No writes a part from temp db.

Russell.Morgan-813114 Say Hey Kid Points: 707 More actions · Answer 2

Found it: Thanks for information

Primary Replica RCSI or SI Enabled, Secondary Replica Not Read Enabled

In this instance, SI and/or RCSI are enabled on the primary replica but the secondary replica is not enabled for read workload. This case is a bit simpler because the 14-byte versioning overhead is already added to the data rows on the primary replica independent of the status of secondary replica. As shown in the following picture, if the secondary replica is not enabled for read workload, there is still a 14-byte overhead on the rows on the secondary replica, but there is no row version generation on the secondary because the read workload has not been enabled.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Based on what you posted, I am not sure if you understand or not. Your post seems to apply when you have SI/RCSI on the PRIMARY.

This is such a common issue. SOOO many companies try to do Always On without REALLY knowing all the caveats, limitations, provisos, gotchas, etc. As a matter of fact, I just posted the following on a different thread on the exact same topic yesterday:

http://technet.microsoft.com/en-us/library/ff878253(v=sql.110).aspx
Search for whitepaper: Offloading Read-Only Workloads to Secondary Replicas
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-impact-of-mapping-reporting-workload-to-snapshot-isolation-on-readable-secondary.aspx

Here is the entire thread:

http://www.sqlservercentral.com/Forums/Topic1633950-2799-1.aspx

I STRONGLY recommend to everyone pursuing AGs to REALLY study and practice with them (or better yet, get professional assistance) before rolling them into production.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service