Use SQL Server Synonyms to Decouple Multi-Server or Multi-Database Queries

As with so many areas in software development, loose coupling is desired between apps, servers and databases. Loose coupling allows for server or database names to be changed easily. It is also critical to making it easy to setup non-production environments.

Normally when we query SQL Server, we connect to a specific server and database (often specified by a connection string). Lets assume that we are connecting to ServerA and DatabaseA. We have four options for how to reference a given table.

1. Select ColA From [TableA]. This is one part naming, and while it has the benefit of being terse, you may want to avoid it unless you are intending to use SQL Server’s system for determining the proper schema based on the current user and connection. Generally, this will get you the same data as the two part name.
2. Select ColA From [dbo].[TableA] Two part naming should be your default.
3. Select ColA From [DatabaseA].[dbo].[TableA] Three part naming adds the database
4. Select ColA From [ServerA].[DatabaseA].[dbo].[TableA] And four part naming adds the server name.

Three and Four part naming are where tight coupling come from, because we reference specific server and databases. If they change names, or you just want to query another environment, then you are in trouble. Of course, if you are just querying the database you are connected to, you never need more than two part naming. So lets use this query as our example:

-- Assume we are connected to [ServerA].[DatabaseA]

Select A.ColA 
     , B.ColB
     , C.ColC
From [dbo].[TableA] A

-- We need a three part name to get to another database
-- on the same server.
Join [DatabaseB].[dbo].[TableB] B on B.A_ID = A.ID

-- We need a four part name query across a linked server.
Join [ServerC].[DatabaseC].[dbo].[TableC] C on C.B_ID = B.ID

As you can see, its the requirement to query across databases or servers which necessitates the longer naming. If we only want to query a single other database, we can just make a new connection.

The Easy Path

If you need to do this kind of multi-server or multi-database, but only from your TSQL (say in views or stored procedures), then I have good news. You don’t need Synonyms loosely couple your database to the other servers or databases.

You can use a SQL Project and define one or more database references to these other databases and servers. You can then use sql command variables in your queries like this $(variableName). Note: if the SQL command variable syntax looks messy to you, read the section on synonyms as they can help here as well.

-- Assume we are connected to [ServerA].[DatabaseA]

Select A.ColA 
     , B.ColB
     , C.ColC
From [dbo].[TableA] A

-- Square brackets around variables are mandatory around sql command
-- variables acting as server or database names.
Join [$(DatabaseB)].[dbo].[TableB] B on B.A_ID = A.ID
Join [$(ServerC)].[$(DatabaseC)].[dbo].[TableC] C on C.B_ID = B.ID

Now the actual referenced server and database names are specified at publication time. Meaning that all you need to do to update one of these values, is to rerun publish. This approach works just as well for a new project as an existing one (Visual Studio has tooling to help you import an existing database into a sqlproj).

Unfortunately, this option breaks down when app code needs to do queries across databases.

The Complex Case

So, what if we have to do a multi server or multi database query from anywhere other than the database? This applies to any client, regardless of language. You will have a single connection string.

// Hopefully your connection string is originating in a config file, not in code.
string connectionString =
    "Data Source=ServerA;Database=DatabaseA;"
    + "Integrated Security=true";

string queryString =
    @"Select A.ColA 
     , B.ColB
     , C.ColC
From [dbo].[TableA] A
Join [DatabaseB].[dbo].[TableB] B on B.A_ID = A.ID
Join [ServerC].[DatabaseC].[dbo].[TableC] C on C.B_ID = B.ID";

// Actual code to open a connection using the connection string.
// Then code to run this query on the open connction.

Now, we have a few options that might come to mind:

• Leave it alone: The big reason not to do this is you can’t have multiple environments, which is almost always the wrong choice.
• Perform some modification to queryString based on environment config: This could be OK in some cases, but:
  • If your database code talks to multiple servers or databases (ideally controlled through SQL command as discussed above) this becomes a huge risk. Now your database and app have separate, and possibly different configurations. This is just asking to create some subtle and confusing bug down the road.
  • Any ad-hoc string replacement scheme will be less robust than the option discussed below.
  • You might light up some static analysis security tools with warnings about dynamic SQL using un-sanitized data.
• Make the query a view or stored procedure: This is totally valid. Once you move the query to the database, you can fall back on SQL command variables.
• Synonyms: Can avoid all of these issues, and I have found useful when cleaning up large numbers of these multi server or multi database queries in legacy applications.

Synonyms to the Rescue

Synonyms in SQL Server establish a new name for any table or view. For our purposes, what we care about the is the ability to map a three or four part database name, to a new synonym which is local to your database, like this: Create Synonym [ServerC].[DatabaseC].[dbo].[TableC] as [DatabaseC].[TableC].

When setting up the synonym we can use any schema, including dbo. I suggest either making a new schema that specifies the other database, or picking a naming convention for tables like this [dbo].[DatabaseC_TableC] to help developers understand that the table they are querying isn’t part of the database. I personally find the first option to quite intuitive, and will use that below.

Now our code can look like this:

-- Create the schemas and synonymns, using sql command
-- to setup the three and four part names.
Create Schema [DatabaseB];

Create Synonym [$(DatabaseB)].[dbo].[TableB] as  
    [DatabaseB].[TableB]

Create Schema [DatabaseC];

Create Synonym [$(ServerC)].[$(DatabaseC)].[dbo].[TableC] as  
    [DatabaseC].[TableC]

string connectionString =
    "Data Source=ServerA;Database=DatabaseA;"
    + "Integrated Security=true";

// Bonus: Notice how readable this is, compared to the prior version.
string queryString =
    @"Select A.ColA 
     , B.ColB
     , C.ColC
From [dbo].[TableA] A
Join [DatabaseB].[TableB] B on B.A_ID = A.ID
Join [DatabaseC].[TableC] C on C.B_ID = B.ID";

Our application no longer needs to know anything about other databases and servers in order to access these remote assets. All of our environment configuration happens at the database level and can be easily managed. This has the extra advantage of being applicable to All apps that use the database.