Managing Cross-Database Object References (EI016, EI026, EI027)
Phil Factor explains cross database and cross-server references, how to find them in your code, and when it's better to avoid hard-coding these references and use synonyms instead.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/* get the current address (abode) of our customers*/ SELECT ThePerson.Title, ThePerson.FirstName, ThePerson.LastName, ThePerson.Suffix, TheAbode.TypeOfAddress, TheAddress.AddressLine1, TheAddress.AddressLine2, TheAddress.City, TheAddress.County, TheAddress.PostCode, TheAbode.Address_id FROM PHILF01.Customers.Customer.Abode AS TheAbode INNER JOIN PHILF01.Customers.Customer.Person AS ThePerson ON ThePerson.person_ID = TheAbode.Person_id INNER JOIN PHILF01.Customers.Customer.Address AS TheAddress ON TheAbode.Address_id = TheAddress.AddressID WHERE GetDate() BETWEEN TheAbode.Start_date --and the future AND Coalesce(TheAbode.End_date, '1/1/2500'); |
We can refactor this by creating synonyms
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE SYNONYM ThePerson FOR [PHILF01].[Customers].[Customer].[Person] CREATE SYNONYM TheAddress FOR [PHILF01].[Customers].[Customer].[Address] CREATE SYNONYM TheAbode FOR [PHILF01].[Customers].[Customer].[Abode] /* get the current address (abode) of our customers*/ SELECT ThePerson.Title, ThePerson.FirstName, ThePerson.LastName, ThePerson.Suffix, TheAbode.TypeOfAddress, TheAddress.AddressLine1, TheAddress.AddressLine2, TheAddress.City, TheAddress.County, TheAddress.PostCode, TheAbode.Address_id FROM TheAbode INNER JOIN ThePerson ON ThePerson.person_ID = TheAbode.Person_id INNER JOIN TheAddress ON TheAbode.Address_id = TheAddress.AddressID WHERE (GetDate() BETWEEN TheAbode.Start_date AND Coalesce(TheAbode.End_date, '1/1/2500')); |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/* view of customers and their current domicile*/ CREATE VIEW MyCustomers AS SELECT ThePerson.Title, ThePerson.FirstName, ThePerson.LastName, ThePerson.Suffix, TheAbode.TypeOfAddress, TheAddress.AddressLine1, TheAddress.AddressLine2, TheAddress.City, TheAddress.County, TheAddress.PostCode, TheAbode.Address_id FROM TheAbode INNER JOIN ThePerson ON ThePerson.person_ID = TheAbode.Person_id INNER JOIN TheAddress ON TheAbode.Address_id = TheAddress.AddressID WHERE (GetDate() BETWEEN TheAbode.Start_date AND Coalesce(TheAbode.End_date, '1/1/2500')); |
And we can check it out.
1 |
SELECT * FROM MyCustomers |
We can now see if there are any dependencies on the synonym.
1 2 3 4 5 6 7 8 |
SELECT OBJECT_NAME(Dependencies.referencing_id) AS ReferencingObject, OBJECT_SCHEMA_NAME(ReferencedObject.OBJECT_ID) AS [Schema], Dependencies.referenced_entity_name, ReferencedObject.type_desc FROM sys.sql_expression_dependencies Dependencies INNER JOIN sys.objects ReferencedObject ON Dependencies.referenced_id = ReferencedObject.OBJECT_ID WHERE ReferencedObject.type_desc = 'SYNONYM' AND OBJECT_NAME(Dependencies.referencing_id) = 'MyCustomers'; |
We can easily see all the synonyms that reference other servers …
1 2 3 |
SELECT NAME, base_object_name, PARSENAME(base_object_name,4) AS SERVER FROM sys.synonyms WHERE PARSENAME(base_object_name,4) IS NOT null |
… and all the synonyms that reference other databases
1 2 3 4 5 |
SELECT NAME, base_object_name, COALESCE(PARSENAME(base_object_name,4)+'.','')+ PARSENAME(base_object_name,3) AS [Database] FROM sys.synonyms WHERE PARSENAME(base_object_name,3) IS NOT null |
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
1 2 3 4 5 6 |
CREATE SYNONYM Staging.ThePerson FOR [Staging].[Customers].[Customer].[Person] CREATE SYNONYM Staging.TheAddress FOR [Staging].[Customers].[Customer].[Address] CREATE SYNONYM Staging.TheAbode FOR [Staging].[Customers].[Customer].[Abode] CREATE SYNONYM Test.ThePerson FOR [Test].[Customers].[Customer].[Person] CREATE SYNONYM Test.TheAddress FOR [Test].[Customers].[Customer].[Address] CREATE SYNONYM Test.TheAbode FOR [Test].[Customers].[Customer].[Abode] |
Let’s check what we’ve got.
1 2 3 4 5 6 |
SELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + NAME AS Synonym_Name,-- WITH the Schema too base_object_name, --the string of the actual reference with square bracket delimiters COALESCE(PARSENAME(base_object_name, 4) + '.', '') + PARSENAME(base_object_name, 3) AS [Database] --database with server prefix. FROM sys.synonyms WHERE PARSENAME(base_object_name, 3) IS NOT NULL; |
…and our query will work when they reference the new versions (if you’ve set up the appropriate linked servers)
1 2 3 4 5 6 7 8 9 10 11 |
SELECT ThePerson.Title, ThePerson.FirstName, ThePerson.LastName, ThePerson.Suffix, TheAbode.TypeOfAddress, TheAddress.AddressLine1, TheAddress.AddressLine2, TheAddress.City, TheAddress.County, TheAddress.PostCode, TheAbode.Address_id FROM staging.TheAbode INNER JOIN staging.ThePerson ON ThePerson.person_ID = TheAbode.Person_id INNER JOIN staging.TheAddress ON TheAbode.Address_id = TheAddress.AddressID WHERE (GetDate() BETWEEN TheAbode.Start_date AND Coalesce(TheAbode.End_date, '1/1/2500')); |
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.