Linked servers: set them up in QA

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.

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.

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...

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.

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).

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.

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.

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.