• SQL Prompt
• SQL Code Analysis

Managing Cross-Database Object References (EI016, EI026, EI027)

Three-part names in SQL Server are required to make references to another database on the same server, or four-part names to make queries to databases on another server. The 4-part ‘dotted’ references consist of server or server-alias, database name, schema name, and object name. You need to create the linked server on every SQL Server instance that will make a remote call and having done so the 4-part references allow us to run SQL queries, create views or call procedures or functions on the remote server.

There is nothing ‘wrong’ in using a three- or four-part reference to execute procedures or functions, if you need to run queries that reference table-sources in other databases. (see Top 3 Performance Killers For Linked Server Queries for a note of caution about using linked server queries). However, depending on the complexity of the application’s setup and the deployment, it is sometimes better to avoid hard coding these references, and to use synonyms instead. For ad-hoc SQL code from an application, it is always wrong to have hardcoded cross-database references because they can’t be located easily.

If references to remote objects are hardcoded, then all such refences must be located and altered, if the server or database name is changed, or the referenced object changes location. Therefore, SQL Prompt has a few SQL Code Analysis rules, in the Execution rule category, which will warn you of the existence of hard-coded references to procedures (EI016), functions (EI026) or tables or views (EI027) in other databases.

You can abstract these types of references to remote objects into synonyms, which exist only on the database where they are defined, and from which the remote reference is made. A synonym’s references can also include three-part references to another database on the same instance, or even two-part references in the same database. Now, if changes are made to the name or location of the base object, then we just need to make one change, to the synonym created in the database making remote calls.

Four-part references via a linked server aren’t the only way of accessing remote servers. You can access linked servers via OPENQUERY, though this offers no real advantage. For occasional use, OPENROWSET and OPENDATASOURCE are alternatives but they are more useful for accessing data sources other than SQL Server on an ad-hoc basis rather than as an integral part of the application.

Using synonyms for cross-database, and cross-server, object references

Cross-server and cross-database references can cause difficulties in the delivery process if you find that you need them to refer to a different server or database, at different stages in the process. Whereas the server name in a link can be whatever alias you choose, you can’t alias the database name. If you keep the Server alias name the same between delivery environments even if the physical servers are different, then you can refer to different physical servers without any change in the database code (although setting up alias server links using ODBC can be a fraught process).

If you are faced with having to use different database names at different stages, all the references must be changed. There could be hundreds of these. The fewer changes you must make, the better. If these references aren’t properly encapsulated in an interface of some sort you are likely to have a long, tiresome, and error-prone task that will slow down your delivery process. It is even worse if these three- or four-part references appear in ‘ad hoc’ code from an application or from dynamic SQL, because they are very difficult to track and almost impossible to alter. Instead of using the reference, it is better for the code to call the appropriate synonym as if it were the target table source (view, table, or function). We must encapsulate references within a synonym whenever it is important to be able to make changes to them.

What sort of changes are these? They mostly happen during the delivery process but aren’t unheard-of in a production setting. Often, these external references to other servers don’t exist in development and so often need to be ‘mocked’ (usually a simple data source via an ODBC link). They may only be assigned to their destination during test or staging. If you are doing a rapid delivery process, every change will need to be scripted. A lot of scripts might need to be changed, if you have references that will be different in every delivery environment (e.g. Integration Test, UAT, Performance Testing, staging and production)

If the alias name for any linked servers is kept constant, regardless of which physical server is used, and you have managed to avoid name changes in the database, then all deployments can be done without a code change. Otherwise all external references must be held in synonyms, to make it easy to manage in code, but changing the synonyms always represents a code change. You generally manage this by using variants of each script for each delivery environment, purely to delete and recreate the synonyms with the correct reference for that delivery environment, executed in a post-deployment script.

As well as solving the problem of name-changes in these references, across the delivery process, synonyms are useful for circular cross-databases dependences that must be ‘spoofed’ or ‘mocked’ in order to do a successful build. I’ve written about this in detail here ‘Database Build Blockers: Cross-Server Database Dependencies’.

Corralling up those pesky three and four-part object references.

If you are faced with having to convert a database from using three-part cross database references or four-part cross server references to using synonyms, you can find them easily just so long as they in the code of a module (view, function, procedure and so on). If so, the sys.sql_expression_dependencies system view will find them and let you know what needs to be fixed. Be warned though: XML documents are considered by SQL Server to be external databases and produce false positives when attempting to identify cross-database dependencies.

There are restrictions on how you use a synonym. Basically, it is functions, procedures, tables, and views. You can’t use a synonym for a schema, for example (you can create it, but you can’t use it) and you can’t use four-part names for functions.

Although Synonyms are database objects that have a schema, synonyms can’t be schema-bound, (Schema binding ties any new object to the base object that it depends upon), so you can drop them without having to delete or alter dependent objects that reference it. It is necessary to prevent schema binding to allow the synonym’s reference to be changed. This can be a curse as well as a blessing because the only way you can check what objects have code that use a synonym is to examine their expression dependencies. You would never know if it is being used by a client application executing ad-hoc queries. Also, there is no constraint on the number of synonyms that an object can have. Like a bandit, a database object could wander around under several synonyms. You can only use synonyms with SELECT, INSERT, DELETE, UPDATE and EXECUTE statements, and synonyms cannot reference synonyms.

So, here’s an example of a 4-part reference that we want to abstract away.

We can refactor this by creating synonyms

If you subsequently drop the synonyms, you’ll get an ‘Invalid object’ error when you execute this.

At what point is it easy to see if a synonyms is being referenced? When it is referenced within the code of a view, procedure, function, or other module. We’ll demonstrate by turning our query into a view

And we can check it out.

We can now see if there are any dependencies on the synonym.

We can easily see all the synonyms that reference other servers …

… and all the synonyms that reference other databases

So, to pull all this together, it is easy to locate all cross-database and cross-server references in a database, if the code is contained in SQL Server programmable objects. It is easier to change code for different development or delivery environments if synonyms are used for these references.

Because synonyms cannot be schema-bound, a synonym can be deleted even when some functionality within the application depends on it being there, and it is using ad-hoc code. It isn’t possible to detect the use of a synonym unless it is in a module. This can be prevented. If access control is competently implemented, then it will be impossible for ad-hoc code from applications to do cross-server or cross-database queries directly from application code.

Deployment without code changes.

A synonym is schema-based, so you can have identically named synonyms in different schemas. Here we create identically named synonyms in another schema

Let’s check what we’ve got.

…and our query will work when they reference the new versions (if you’ve set up the appropriate linked servers)

Synonyms are seldom referenced by schema prefix. This could be used to advantage. Users can be set to use a different schema to dbo as their default schema This default schema setting specifies the first schema that will be searched by the server when it resolves the names of objects for this user. This means that, by changing the default schema for the users, it is possible to change the version of the synonym being used, and thereby change the external reference being used. You just need to ensure that synonyms are referenced without an explicit schema. Changes to existing users aren’t considered to be database changes so this method should allow a smooth deployment. If users are assigned to each stage in the delivery process, so that test has its own users, and staging its own users, then it can be done without any code changes at all. I haven’t tried it on a real deployment yet, but it might just fix your deployment problems!

Pulling it all together

If you are able to create linked servers on all your host SQL Servers with appropriate logical aliases, and you resist the temptation to rename your databases in different delivery environments, then you need never fear cross-server or cross-database object references. Otherwise, you will become very familiar with SQL Server synonyms, so that all these cross-references can be encapsulated in them. These will occasionally need caution because abstractions can always lead to misunderstandings, and you’ll need to find slick ways of changing them when necessary for a delivery environment such as test or staging, or when the names of SQL Server instances change.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more