Phil Factor

10 November 2020

• SQL Change Automation
• Database Builds and Deployments

Database Build Blockers: Four-part Object References

If your code contains 4-part references to objects in databases on other servers and those objects aren’t ‘present’ during database builds, it will cause failures. Perhaps you’ve been getting around this by using live references from the development server to a linked production server, but this is often not possible if personal or sensitive data is stored. Also, if you’ve made the mistake of hard coding the network name of the remote server into the references, then you’ll also have maintenance issues.

So, what do you do? Some teams set up what, over time, becomes an increasingly complicated development environment, with servers with the same name as production. A better option, which I’ve described in Database Build Blockers: Cross-Server Database Dependencies, uses synonyms to represent the remote references, alongside local ‘stub’ objects. This also gets around circular cross-server dependency problems, although with the drawback that your builds won’t necessarily verify all dependencies fully, if the definitions of any remote objects change.

In the synonym approach, all you need to do is replace all the existing four-part references in the code with synonyms. If you raised your eyebrows, or gulped, at that word ‘all’, then that solution may not appropriate. For some applications, you’ll need to track down not all the four-part references in the database code, but also references to external servers in SQL code embedded within the application in C# or JavaScript. Synonyms may not be a suitable solution anyway. You may not be able to change all parts of the code if it is part of a bought-in application or maintained externally. You may not be allowed to make code changes to create appropriate synonyms to suit a particular database environment such as testing.

A much better alternative in these cases, assuming copies of all dependent database can be made available in the development system, is to use server aliases for the linked servers.

Problems with Linked Servers

SQL Server lets you fetch and update data in databases on other servers by using the system of linked servers. A linked server is a connection to another SQL Server. If a user has suitable permissions, it allows access to databases on that server. There are many reasons why a database system might need to do this. It can, though, present several difficulties in the development environment.

• Problems with builds: The most obvious problem is that you can’t build any of the component databases that has four-part references unless all the servers and their databases are present on the network. The build fails if SQL Server finds a four-part reference, checks for the existence of the base object, and can’t find it. This problem just gets worse when there are mutual cross-server references between databases.
• Difficulty in testing: After a successful build or migration, you then must test your work. Even if you are developing just one database within an interlinked group, you may hit problems if the data on the live system includes restricted or personal information. If do, you’ll probably find that you are not allowed to test out your development database by accessing the production version of the linked servers.
• Complications with coordination: You may be developing an application that has several linked databases on different servers. The databases on the other servers may require being developed together. This would require that all the remote servers be constantly accessible throughout the database lifecycle at the correct version.

Using Server Aliases with linked Servers

We can get around these development difficulties without needing to change all the four-part references to synonyms. We use two ‘tricks’ with the configuration of linked servers on the host SQL Server. Firstly, we use an attribute of a linked server that isn’t immediately obvious, the Alias. Then use the network name of our development server as the network name of the ‘remote’ server. We’ll explain this with an example. Here, we have two ‘remote’ servers called Aristobulus and Phasael and each is a linked server for the other.

Objects in each database access those in the other via four-part references, the first part of which is the ‘logical’ name of the remote server. This is often set to be the same as the network name, but it doesn’t have to be: you can provide an alias or ‘logical name’ for a linked server. This allows you to change the network name of the real server without affecting the link used by the four-part references. Here, we have created two links on our development server (Devserver). one link with the alias Aristobulus and one with the alias Phasael. In both cases these are the real names of the remote servers, but in each case the underlying link is back to Devserver.

Now, we can make 4-part references between objects in each database, as if one database were located on Phasael and the other on Aristobulus, even though they are, in fact, both on the same Devserver!

This means that if we are developing a multi-server application on an isolated development machine, or even on laptop while commuting, we can hoodwink the system into believing we are linking with a remote server.

These linked server configurations are done at server level, not the individual database so, unlike for synonyms, there are no code changes to the databases required. To make the databases work in any development or deployment environment, you just need the correct links on the server hosting them. If necessary, you can use a different alias for a server, or a different server for an alias. In this way you can ‘mock-up’ a whole array of separate servers and databases.

This technique will solve our immediate problem, and several others as well. If used in conjunction with synonyms, linked servers provide a common way of mocking up (‘mocking’) database interfaces. Linked servers, in general, need not always be to a database hosted on a remote SQL Server instance. If you are developing on a laptop, for example, it is possible to create simple text files that ODBC obligingly disguises as a database. SQL Server allows links to any properly-written ODBC source. Microsoft provides drivers for Access, Text (CSV) and Excel. Third-party providers allow you to connect with almost any imaginable source, and some that are beyond imagination. I demonstrate how to link with a text-based ODBC source here in The TSQL of CSV: Comma-Delimited of Errors, so you can access it and write to it as a database. To prove it is possible, I created a text-version of Adventureworks.

This sort of mock-up is more suitable when you can use a synonym to contain the actual table reference because neither CSV, Excel nor Access have schemas. The references to objects become subtly different in ODBC. We can get around this by using synonyms to mask the non-standard object references.

Installing a linked server

As described in the previous section, we are, somewhat bizarrely, going to create a linked server on our development server that links back to the same server. By doing this, we can create both remote servers, Phaesel and Aristobulus, as ‘servers’ on our development server. We can, in fact, link to as many phantom remote servers as we need.

It is simple to do, because we can guarantee that the users on the ‘remote’ servers match those on our server, because they are actually on our server! This means that we avoid the chore of setting up the security or server options for the link. We just need to use the right names for the databases and schemas.

In our first demonstration code, we’ll be checking to see if the linked servers already exist and, if so, I’ll delete them. Then we’ll create them on Devserver. Here is the code to do the linkage. Hit Ctrl+Shift+M before executing this, and key in the network name for your server that you use to log into it.

Building databases with mutual ‘cross-server’ dependencies

Now let’s try to build two remote databases that have cross-database dependencies. These two very simple databases are the same ones used in the previous two articles in this series (see Database Build Blockers: Mutually Dependent Databases, for example). The database called TheFirstDatabase is on the server Phasael, and the database called TheSecondDatabase is on Aristobulus. Each one contains four-part references to the other and the following diagram illustrates the objects and dependencies:

If we were to just execute the build script for the database Phasael, we get:

Msg 7314, Level 16, State 1, Procedure TheFirstView, Line 4 [Batch Start Line 102]
The OLE DB provider "SQLNCLI11" for linked server "Aristobulus" does not contain the table ""TheSecondDatabase"."dbo"."TheThirdTable"". The table either does not exist or the current user does not have permissions on that table.
Msg 7314, Level 16, State 1, Procedure ThefourthView, Line 4 [Batch Start Line 107]
The OLE DB provider "SQLNCLI11" for linked server "Aristobulus" does not contain the table ""TheSecondDatabase"."dbo"."TheSecondview"". The table either does not exist or the current user does not have permissions on that table.

Ah, we think, no worries, we’ll create the other database first. To save you from having to do the experiment, here’s the result:

Msg 7314, Level 16, State 1, Procedure TheSecondView, Line 4 [Batch Start Line 157]
The OLE DB provider "SQLNCLI11" for linked server "Phasael" does not contain the table ""TheFirstDatabase"."dbo"."TheSecondTable"". The table either does not exist or the current user does not have permissions on that table.
Msg 7314, Level 16, State 1, Procedure TheThirdView, Line 5 [Batch Start Line 162]
The OLE DB provider "SQLNCLI11" for linked server "Phasael" does not contain the table ""TheFirstDatabase"."dbo"."TheFirstTable"". The table either does not exist or the current user does not have permissions on that table.

In the Database Build Blockers: Cross-Server Database Dependencies article, I got around this problem by using synonyms and “dummy” objects. Here, however, because we can alias the servers, and we can access the actual databases, it is all much simpler.

The following script will build TheFirstDatabase on Phasael. It assumes that the database has been created. To prevent build errors, we can simply create ‘stubs’ in one of the databases, for each of the views that makes an external reference (as demonstrated in Database Build Blockers: Mutually Dependent Databases). Before we do the build, we need to check to see if the remote references exist, and if they don’t we put create the ‘stub’ objects with the correct name but no functionality, so the build will progress.

Well that works fine. Now for TheSecondDatabase which is currently just represented by stubs for any object that is referenced by TheFirstDatabase. We’ll just delete these and substitute the real object.

Well, that went without error. Here are the four references illustrated in the previous diagram:

We now have built both databases despite the cross-dependencies. They are not on physical servers named Phasael and Aristobulus. Instead, your development server has created two servers that are, unbeknownst, linked back sneakily to your development server. On your development server, any four-part reference to Phasael or Aristobulus will work even though TheFirstDatabase and TheSecondDatabase are on your server.

Hopefully, you will see the way that a single development build server can easily build and test a database system consisting of the most complicated nest of servers and databases. The only compromise you’ve had to make is to have all these databases represented under their real name but placed on the development server.

How does this work in SQL Compare or SQL Change Automation?

If you are using SQL Compare or SCA, we must maintain one pre-deployment script for each database that we build as part of the deployment. This script will ensure that any object that is the target of a remote reference is represented, at least by a stub, before we do any build operation. Each script is hand-cut and must be modified whenever a new four-part reference is added.

So, for example, the pre-deployment script for TheFirstDatabase will create stubs for TheSecondView and TheThirdTable, if those objects don’t already exist. It is run before any genuine building is done, and then SCA or SQL Compare does a full build of all databases, so no stubs are left in place, and therefore all dependencies are fully tested.

This is a solution that I’ve already described in Database Build Blockers: Mutually Dependent Databases. The SCA script will work as previously because we are doing the same process here; both databases are on the same server, even though referenced by four-part names. When you want to deploy to production, you merely redo the linked servers to point to the real remote servers.

Conclusion

If you use server aliases, it isn’t a big problem to develop, test or build a database system comprising several linked servers, even if the individual databases use four-part references within the code rather than synonyms. This article gives you a quick way of trying out the technique of doing so.