March 26, 2019 at 1:41 pm
I'm trying to determine if improvements have been made to utilizing a Linked Server. I know from the past the top 3 performance killers were: 1) Insufficient permission(i.e. needed to be sysadmin, db_owner) I believe in sql server 2012 this has been rectified where the permissions only has to be db_datareader now in order to take advantage of statistics on remote server, 2) Query Join Syntax, 3) using OpenQuery to force the processing to be done on the remote server and only passing back through the network the result set. We have reports on our primary server that really need to get moved to our reporting server, however, management is looking for a quick fix to this problem without having to involve the development team. They want me to create stored procedure stubs on the primary server and move the stored procedures that actually do the work to the remote server. Now the real underlying reason why we are doing this is to reduce the cpu/memory on our primary server. I'm really for migrating this stored procedure to the reporting server rather than utilize a linked server, cause, unless the majority of the processing happens on the reporting server, we aren't buying anything.
Thanks, in advance
March 26, 2019 at 2:59 pm
hate to ask is this for just reporting from databases from server b thru server a? If so wouldnt a AG read only node be better? Also is this all sql or is there any Oracle or DB2?
March 27, 2019 at 1:30 am
This is all SQL Server no Oracle or DB2. AG Read Only Node. You are referring to a replica correct? Unfortunately we are still using Transactional Replication.To me the correct way would be to move all sprocs that a reporting to our reporting database. Issue is, they are looking to get there without moviing all of these reporting sprocs over to our reporting database.I'm sure your first question is if they went to all that trouble to create a reporting database why didn't they follow through initially and have all reporting sprocs moved to the reporting database. The key here is follow through which they didn't do. By the way, the whole reason why they are wanting to do all of this is to change our primary server to Standard Edition from Enterprise to save licensing costs. Part of their problem is they have Champagne taste with beer pocket money.
March 27, 2019 at 5:48 am
AG would be Always On which would require Enterprise, if you can swing it (want to be on at 2014 2016 if you can). When setting up you restore the primary to the read only node and turn on AG an you are done. All transactions are immediate, you can do multiple databases kind of like log shipping on steroids
March 27, 2019 at 7:13 am
An old-school, and effectively free, solution to this is setting up log shipping. However, that has received almost no love from Microsoft for years. The preferred method as was already outlined is Availability Groups. I'd pursue AGs if it were me.
Like log shipping, there hasn't been any substantive changes to Linked Servers. In fact, Microsoft has again introduced a different technology solution in that space, Polybase. This has seen enhancements in the upcoming release, SQL Server 2019. It might be the way to go if you have to stick to linked server methods.
Also, just for my piece of mind, the suggestion to upgrade to at least 2014 was made. Don't upgrade to 2014. Go to 2016 or better. 2014 introduced a new cardinality estimation engine, but didn't introduce an easy way to mitigate issues with it. Those came in 2016 with Query Store and DATABASE SCOPED CONFIGURATION. Friends don't let friends upgrade to SQL Server 2014.
"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
March 28, 2019 at 2:42 am
thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply