April 16, 2015 at 7:02 am
Hi,
I have a SQL2014 server using high availability. So there is a primary and a secondary.
There is a vendor process that pulls allot of the data from a large (5 mil) table every morning. It takes that data into Great Plains digests it and then has to write back to my to the same exact SQL2014 production server.
The problem is that when the process starts it seems to putting a lock on the table to pull all the data - which of course creates blocking for my users. This is now a big problem, my users can't work and the application that pulls this data sometimes fails with a pipe error ..not good.
This is a procedure locked down vendor supplied process so I don't have much access. We do have some control and I was thinking of having them point to the secondary to pull the data to relieve the pressure off the main DB....but if I have them point to the secondary, they also need to write back to the database which they can't do because the secondary is read only. Could I maybe create a third replica and make that read write?
We are willing to create an additional db server if need be, but how to update it back to prod.
Not sure of my options here, can anybody think of anything that could help this situation?
April 16, 2015 at 8:51 am
Just throwing it out there but why do they have to write back to the same DB they read from? I'd have them pull from the read only unless Dirty Reads are ok then I'd just change the Transaction Isolation Level. Then write back to the other copy. Seems like you have all the right tools to do it.
FYI - I'm jealous...my workplace is finally upgrading to 2012 from 2005 this summer. Can't wait to setup Always On!
April 16, 2015 at 9:06 am
Yeah..thanks.
High Avail is pretty cool, but it's kinda a pain also.
It's nice once it's setup but setup can be a little painful. I got stuck with a 'restoring' state on my secondary and spent a weekend fixing, not fun.
April 16, 2015 at 9:11 am
if they use (ApplicationIntent=ReadOnly) in their connection string when they pull the data, you don't need to do anything else. lways on take cares of the rest. but of course they need 2 different connections for this operation, one with the additional info in the connection string as i mentioned above and one without it.
using direct IP addresses of primary and secondary replicas may result a failure as you stated in your question.
i cannot see any other way around at the moment.
April 16, 2015 at 3:21 pm
There's no read/write on the secondaries. Not yet anyway. That's basically merge replication.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2015 at 4:41 am
create a nightly job if possible. How much process takes the time ?
April 21, 2015 at 2:25 pm
Hi,
I use HA quite a bit; so hopefully I can lend some advice. First off, there are some great suggestions on here already, but there is something you're going to need to keep in mind:
MS Licensing allows you to maintain passive secondary server only. If you enable reads from that replica you need to license BOTH servers, not just the primary.
Assuming you only have one server licensed, your best bet will be to change the transaction isolation level as suggest earlier.
If you do have licenses for both servers, the using ReadIntent option in the connection string would be the best option as it will deflect the query to the secondary server.
April 23, 2015 at 12:44 pm
You can only have ONE PRIMARY Server (you can READ/WRITE).
You can have multiple SECONDARY (READ only).
You have a One Way Replication A (Primary) to B (Secondary).
The only problem with your question is that you want to be able to write to the DB and there can only be one and there is where you are stuck at the moment.
As far as I know there is no easy solution without modifying the code of the Vendor Software to do READ-INTENT to the Secondary (SELECT statements, Queries etc) and when sending a Write it goes to the Primary (see attachment notes).
No easy solution unless you have your own Developers in-house to let them know.
April 23, 2015 at 1:18 pm
krypto69 (4/16/2015)
Hi,I have a SQL2014 server using high availability. So there is a primary and a secondary.
There is a vendor process that pulls allot of the data from a large (5 mil) table every morning. It takes that data into Great Plains digests it and then has to write back to my to the same exact SQL2014 production server.
The problem is that when the process starts it seems to putting a lock on the table to pull all the data - which of course creates blocking for my users. This is now a big problem, my users can't work and the application that pulls this data sometimes fails with a pipe error ..not good.
This is a procedure locked down vendor supplied process so I don't have much access. We do have some control and I was thinking of having them point to the secondary to pull the data to relieve the pressure off the main DB....but if I have them point to the secondary, they also need to write back to the database which they can't do because the secondary is read only. Could I maybe create a third replica and make that read write?
We are willing to create an additional db server if need be, but how to update it back to prod.
Not sure of my options here, can anybody think of anything that could help this situation?
A readable secondary implies youre using AlwaysOn availability groups, is that correct?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 23, 2015 at 1:33 pm
yb751 (4/16/2015)
I'd just change the Transaction Isolation Level.
Careful here, my stairway series article at this link[/url] details the following
Stairway to HA Level 6
Impact of Row VersioningWhen you enable a database as a readable Secondary in an AlwaysOn Availability group, row versioning is automatically implemented and applies a 14 byte overhead on each row that is modified. In fact all isolation levels are transparently mapped to the snapshot isolation level to avoid redo thread blocking. Without this, report workloads could possibly interfere with the redo thread process.
The addition of the row version data on the Primary depends on the snapshot isolation or read-committed snapshot isolation (RCSI) level setting on the primary database. If row versioning is also explicitly implemented on the Primary database in the group there will be an overhead on the Primary replica too.
This row overhead can be viewed by querying the "max_record_size_in_bytes" column in the DMV "sys.dm_db_index_physical_stats". The table below describes the behaviour of versioning on a readable secondary database under different settings for disk based tables.
Readable Secondary? Snapshot isolation or RCSI Level Enabled? Primary Database Secondary DatabaseNo No No row versions or 14-byte overhead No row versions or 14-byte overhead
No Yes Row versions and 14-byte overhead No row versions or 14-byte overhead
Yes No No row versions or 14-byte overhead Row versions and 14-byte overhead
Yes Yes Row versions and 14-byte overhead Row versions and 14-byte overhead
From the Books Online link: http://msdn.microsoft.com/en-GB/library/ff878253.aspx
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply