THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Linked servers: set them up in QA

How do you set up linked servers in your QA environment? You set them up the same as you do in the production environment, right? Why even ask?

Correct. There is no difference in the actual steps of setting up a linked server between your QA and prod. However, consider the following typical code fragment involving a linked server:

 SELECT abc FROM NYCSQL01.inventory.dbo.my_db;

where NYCSQL01 is a linked server pointing to a production SQL Server instance, most likely also named NYCSQL01. And let's further assume that you name all your production SQL instances with the NYC prefix and all your QA instances with a QA prefix. In this case, when you test the code that includes the above fragment, you have a choice, when setting up a linked server on your QA database server, between (1) naming the linked server QASQL01, and (2) naming the linked server NYCSQL01. In the 2nd choice, you need to make sure NYCSQL01 actually points to QASQL01(assuming that QASQL01 is the QA server for NYCSQL01). For obvious reasons, the 2nd choice is much more preferable. The most important reason is that you don't need to alter your code when testing it in QA.

But it doesn't really stop there. If you want to simplify your life, you should make sure that any reference to NYCSQL01 in your QA environment actually points to QASQL01 by default instead of NYCSQL01 for at least two reasons. First, there is no business for any code/app in QA to reference a production instance such as NYCSQL01. Well, that's not 100% true. In the rare cases where you do need to reference NYCSQL01 as NYCSQL01, special arrangement should be made for that to happen (not by default). Second, linked servers are not the only things that may reference NYCSQL01. The app being tested in QA may have other components such as an SSIS package, a batch file, a connection from a piece of CLR code, and so on that may access NYCSQL01. And when the app is being tested in QA, you want those references all access QASQL01 instead without changing any of your code. One way to make this happen is to modify the hosts file in C:\Windows\system32\drivers\etc to map NYCSQL01 to the IP address of QASQL01.

Is this how you set up your QA environment?

Published Tuesday, April 05, 2011 3:34 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Thomas LaRock said:

Linchi,

What we did was to use the name of the application as the name of the linked server. So the four part name would be [appname].[dbname].[dbo].[object], which allowed for code to be migrated between environments easily.

We did not want to have the links cross environments (prod to test, etc.), but sometimes we needed to do exactly that. I would say that 98% of the time using a logical name for the linked server was something that helped reduce confusion.

April 5, 2011 4:20 PM
 

Linchi Shea said:

Hi Tom;

No argument there. You should absolutely dedicate a separate network alias (pointing to the SQL instance) for each app. But I'm not sure from your comment whether you use the same linked sever name (same as the app name in you case) in both prod and QA, or use a different linked server name for each envrionment.

April 5, 2011 5:17 PM
 

Thomas LaRock said:

Linchi,

We used the same names.

So, for PRODSQL1 we would create a link to PRODSQL2 and name the link APPNAME.

Then, for QASQL1 we would create a link to QASQL2 and name the link APPNAME.

So, two environments, but same linked server names. Of course we had the budget for the hardware necessary for such architecture. Not everyone else is as lucky. But the idea is the same: keep the names consistent between environments.

The alternative is to have a different linked server name in each environment, which results in different sets of code, which is more overhead than anyone wants.

Of course the ideal is to never use a linked server at all, but that is just a pipe dream.

April 5, 2011 9:31 PM
 

Linchi Shea said:

Thanks Tom for the clarification! If PRODSQL02 is a host name or a server cluster virtual server name, it's better to create an alias APPNAME and use that for both the app to access the database and to name all the linked servers used by the app from any other servers. This way, the target server name and the app name will aleays agree.

April 5, 2011 11:08 PM
 

Vincent Salard said:

Hi Linchi, Thomas,

But it only makes sense while your application accesses only 1 linkedserver. When needing 2 linkedservers you loose the knowledge of which linkedserver alias to use for what...

April 6, 2011 9:21 AM
 

Linchi Shea said:

Vincent;

Correct if you strictly require each app has its own linked server setup and with the linked server strictly named after the app. In practice, though, you would never do that literally. You typically would have a naming convention that has other prefix or postfix elements to help tell which is which.

April 6, 2011 9:58 AM
 

simon said:

We define SQL Client Aliases (not DNS Aliases) using SQL Server Configuration Manager on each SQL Server that needs uses Linked Servers. The Aliases use the convention DATABASENAME@SQL (although I prefer APPNAME@SQL for flexibility), and Linked Servers are named identically - so SQL code uses things like "SELECT col from MYDB@SQL.MYDB.dbo.TABLE". The "@SQL" is for support reasons, so that everyone can see you are referencing a SQL Alias, and not a Hostname or DNS CNAME Alias. This way the Code and Linked Server definitions remain static, and only the SQL Aliases are amended to point to different Hostnames in Dev, Test, QA and Prod. The reason we define using SQL Aliases instead of directly in a Linked Server is that it is easier to amend a SQL Alias to point to a new Hostname than it is to Drop \ Create a Linked Server (e.g. in case your Database moves to another Server in Prod).

April 6, 2011 11:57 PM
 

Leif Peterson said:

For those using a BigIP or other similar device, we addressed this by defining virtual addresses for every db in every environment where each environment has one or more dedicated SQL Server instances and a copy of each db.  For any database used in a cross database query, a linked server is created with the name of the database and points to a virtual address defined for that db in that environment.  Any cross db query uses a format of db.db.schema.table and only accesses other databases in the same environment leaving all code completely portable and enabling db moves between servers while only requiring an update to the virtual address.

April 12, 2011 2:20 PM
 

Eric Russell said:

I have a script for deploying the linked servers, and the linked server names are the same across Dev, QA, and Production. I'll also reference the variable @@servername to conditionally branch code or set parameters depending on the environment.

May 6, 2011 11:06 AM
 

Dick@CS said:

why has nobody mentioned synonyms [introduced with SQL2005] ?

http://msdn.microsoft.com/en-us/library/ms191230(v=SQL.90).aspx

this is another level of indirection [performance drain?] but means the actual definitions for QA & PROD can be different and you can test sprocs then deploy happily without bending the code you've just tested.

July 26, 2011 4:24 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement