Question about server alias

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » Administering » Question about server alias

Add to briefcase

Question about server alias

Expand / Collapse

Author

Message

Posted Friday, December 28, 2012 3:21 PM

Valued Member

Group: General Forum Members
Last Login: Saturday, May 18, 2013 8:27 AM
Points: 57, Visits: 328

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?

Post #1401081

Posted Saturday, December 29, 2012 4:11 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:29 AM
Points: 6,699, Visits: 11,728

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1401156

Posted Monday, December 31, 2012 12:13 PM

Valued Member

Group: General Forum Members
Last Login: Saturday, May 18, 2013 8:27 AM
Points: 57, Visits: 328

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.

Post #1401473

Posted Tuesday, January 01, 2013 4:47 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, April 07, 2013 1:20 PM
Points: 51, Visits: 161

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.

Post #1401573

« Prev Topic | Next Topic »

Add to briefcase

Permissions

Expand / Collapse