Point Stored Procs At Read-only Instance of AG Group

Question

Post reply

Point Stored Procs At Read-only Instance of AG Group

swoozie

SSCrazy

Points: 2770
More actions
January 25, 2017 at 9:27 am

#318175

I am migrating from a SQL 2005 to a SQL 2014 Always on Environment
We have a set of stored procedures that take data out of our production database and place it in a secondary reporting database for a department. All the databases are on the same instance. How do we specify the Stored Procedure to utilize the Production database siting on the Read-only node instead of Running against the Primary node?
Thanks
Susan

Viewing 5 posts - 1 through 4 (of 4 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Listener:

">https://msdn.microsoft.com/en-us/library/hh213417.aspx

Do note that if you make a secondary database readable you will modify PRIMARY data structures to have a 14-byte version store pointer placed on them. This increases row size (causing massive fragmentation for those 0-fillfactor indexes that every one has because that is the default) and it is also a tlog action (requiring more replay) and dirty page causing action (causing write activity on primary).

Always On is also rife with issues, limitations, gotchas, bugs, etc.

I would consider just continuing to use your existing process and stay away from AGs.

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

swoozie SSCrazy Points: 2770 More actions · Answer 2

Our entire platform is AG groups this is just the last product to migrate. We Use the Read only node for reporting from our report server, and I want to do the same with these stored procs, but it is the same instance\AG group that they are running in. I want them to be able to Select from the Read-only like Reports and update the Read\Write Copy.

I can do this via SSIS package, but I was trying to determine a workaround for the existing process until I can devote time to package development.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

In short, you don't.

The read only routing for AGs is based on connection strings, and a connection that indicates it is read only can be connected to the read only replica by the AG listener. If it then tries to update, it will be trying to update the read only replica and that will fail.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

swoozie SSCrazy Points: 2770 More actions · Answer 4

GilaMonster - Wednesday, January 25, 2017 12:12 PM
In short, you don't.
The read only routing for AGs is based on connection strings, and a connection that indicates it is read only can be connected to the read only replica by the AG listener. If it then tries to update, it will be trying to update the read only replica and that will fail.

I was hoping there was some weird SQL magic but if you say there isn't, then I know there isn't. I will have to wait for time to develop an SSIS package.

Thanks