December 28, 2012 at 3:21 pm
We currently have two production servers at my company, let's call them DB1 and DB2, each with a handful of databases that are used fairly extensively throughout the day. A lot of our queries (mainly reports) access data from both servers via linked server.
However, now we are in the process of merging these two servers into one new server called DB3. The plan is to create aliases on the new server which point back to the new server. So whenever a query contains table DB1.Database.dbo.Table, it's actually pulling from the local (DB3) server.
My question is, what are the potential impacts on performance for this? Based on my own testing, it looks like on my test box that queries that reference these linked servers that are aliased back to local are occasionally showing wait types of OLEDB.
We do plan to, over time, go through our code and remove the linked server references; if for no other reason than to clean things up. I am just wondering if anyone else has had experience with this, and if so, are there any red flags to even doing this?
December 29, 2012 at 4:11 am
cphite (12/28/2012)
We currently have two production servers at my company, let's call them DB1 and DB2, each with a handful of databases that are used fairly extensively throughout the day. A lot of our queries (mainly reports) access data from both servers via linked server.However, now we are in the process of merging these two servers into one new server called DB3. The plan is to create aliases on the new server which point back to the new server. So whenever a query contains table DB1.Database.dbo.Table, it's actually pulling from the local (DB3) server.
My question is, what are the potential impacts on performance for this? Based on my own testing, it looks like on my test box that queries that reference these linked servers that are aliased back to local are occasionally showing wait types of OLEDB.
We do plan to, over time, go through our code and remove the linked server references; if for no other reason than to clean things up. I am just wondering if anyone else has had experience with this, and if so, are there any red flags to even doing this?
As you are seeing with the OLEDB waits even though the previously remote call to DB1 is now essentially a call to a local database instance on DB3 it still has to be run through all the same pipes a remote call has to be run through. The only difference is the lack of an external network trip. Linked Server calls are expensive for this reason. In theory you can expect performance similar or slightly better to what you had when the call to DB1.Database.dbo.Table was a call to a physically different server on the network depending on how much data you were transferring over the wire so in terms of starting operations on DB3 you may be able to get away with no code changes. If I were in your shoes I test that theory and if it did not hold up in all cases, i.e. if some queries took longer using the loopback alias than they were when the instances were separate, then I would push pretty hard to get the code changes prioritized to go in with the DB3 golive as opposed to a secondary phase.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 31, 2012 at 12:13 pm
Thanks for your reply... that is pretty much along the lines of what I was thinking would be the case. I just wanted to make sure there wasn't some issue we'd be creating that I was missing 🙂
I do have some more expensive queries that I plan to update as part of the migration; and am planning to update the rest in phase two, with preference given to any that show performance issues.
January 1, 2013 at 4:47 am
You could create a DNS alias (e.g. CNAME record) for each database which point to the correct server. E.g. for the Finance database on server DB1 create the DNS record "financedb CNAME db1.yourdomain.local".
Next use this name in your connection strings. E.g. "Data Source=financedb;Initital Catalog=Finance;Integrated Security=True;Application Name=FinanceApp".
When the Finance database moves to server DB3. You will only have to update the DNS record to "financedb CNAME db3.yourdomain.local". Flush the DNS cache on all the client computers using "ipconfig.exe /flushdns" and restart the client application.
If you don't administrate your local DNS zone, you could ask your DNS administrator to delegate a sub zone (e.g. databases.yourdomain.local) to you so you can make changes in this sub zone but not in the parent zone.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply