8 August 2019

2 Comments

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

8 August 2019

2 Comments

Database Build Blockers: Cross-Server Database Dependencies

Phil Factor demonstrates how to tackle builds when databases make cross-server references. The technique uses synonyms to represent the remote objects, and local 'stub' objects to overcome the problems caused by 'missing references' when building the individual objects.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Cross-server dependencies, in one or more databases, can cause unnecessary grief in both development work and in continuous integration. Linked Servers are a useful and popular feature and shouldn’t cause too many problems in development, with careful planning.

By using four-part references in SQL, to objects in linked servers, SQL Server lets you fetch and update data in databases on other servers. If the remote server is constantly accessible throughout the database lifecycle, it is all very easy. Even so, if a group of databases make cross-server references to each other such that none can be built without the existence of all the others, then it can all get rather more complicated. This article shows you how a mixture of stubs and synonyms can make it all work, first using hand-rolled scripts, then as an automated build process using SQL Change Automation.

One of the advantages of using synonyms for remote objects, which is that they do not require a check on the existence of the base object at the time that the object is created, is also a possible issue in that your builds do not fully exercise all dependencies. In other words, your build might succeed even if, for example, the definition a remote object has changed in a way that would normally make it fail. You will not find this out till you run your subsequent tests, which access the underlying base objects.

Although you can also use the synonym technique for cross database dependencies on the same server, it confers no advantages. I’d recommend instead you consider a simpler technique that uses temporary stubs and does a complete build. On other occasions you might consider simply restoring copies of all the dependent databases, before starting the build.

Handling cross-server dependencies using synonyms

On inspecting any SQL that makes cross-server calls, the most obvious difference is that we use four-part references to the objects on the linked servers, rather than the three-part references used for cross-database dependencies on the same server. Once the linked servers are defined on the SQL Server instance, the 4-part references allow us to run SQL queries, create views or call procedures on them.

However, SQL Server handles these cross-server database calls entirely differently. They are simply remote procedure calls, and their usefulness depends on the capabilities of the underlying OLE-DB provider. When using them, transactions across servers should be avoided. Linked servers are usually the only realistic option to access data whose source is outside of SQL Server.

To demonstrate the problems that cross-server dependencies can cause for a standard build process, we’re going to use the same, simple two-database project with mutual dependencies as we used for the cross-database article. However, this time the databases might live on different servers.

Figure 1: Simple cross-server circular (mutual) dependencies

To cope with cross-server dependencies, we generally use SQL Server Synonyms in our code in place of the actual four-part address of the object. This provides a layer of abstraction to avoid actual server instances being specified in code. It means that when the server or database name is changed, it requires just one change in the database making remote calls. A synonym’s references can include three-part references to another database on the same instance, or even two-part references in the same database. If you want to change the location of the actual object, you merely delete the synonym and create a new one with the same name but pointing to the new object.

So instead of using this view definition in our build scripts:

Listing 1: Object source script for TheFirstView

We can do this:

Listing 2: Creating TheFirstView on a synonym

With synonyms, unlike views, SQL Server doesn’t check the existence of the base object at the point it creates the synonym; it checks this only at run time, when you attempt to use it. This allows you to build a database without needing that external reference to exist at the time.

Of course, in the above example we still have a problem, because we’re immediately using the synonym within the view, at which point SQL Server checks that the base object assigned to the synonym exists, which it might not. To get around this, we still need a local stub to represent the remote object. In other words, we create the synonym on a local “dummy” version of the remote object, create the view using the “dummy synonym”, then immediately drop the dummy synonym and replace it with the one with the real remote reference.

When building using synonyms, you’ll need to make sure your subsequent post-build tests access all them all, so that the remote references are checked. Otherwise, you might get errors when you first execute a query with a synonym.

This same technique, using synonyms and local stubs, will also work when our databases are on the same server, and I’ve provided a CrossDBRefsUsingSynonyms script that you can try out. However, using synonyms is over-the-top for cross-database dependencies, as there are simpler ways of achieving the same ends, as I demonstrated in my previous article, Database Build Blockers: Mutually Dependent Databases. Also, for the reasons already described, I generally avoid using synonyms unless I have no choice.

Hand-scripted builds with cross-server dependencies

The real joy of the technique of creating dummy ‘stubs’ of remote objects, which may not exist or be in network range, and then substituting a different synonym to point to a remote server, is that you can build the database without the remote instance and its database being available. Just don’t try running any of the code that contains the dependency.

We need to be able to build these databases, on their separate servers, in any order we like, whether the other remote database(s) are there or not. This means we need two hand-cut build scripts.

In this simple example (see Figure 1), TheFirstDatabase is on the server Phasael, and TheSecondDatabase is on Aristobulus. You’ll need to establish each of these as a linked server for the other (you can do this in SSMS or T-SQL). Here is the script for the first database to be built, on the server Phasael.

Listing 3: Build script for TheFirstDatabase, with cross-server dependencies

At the start, we couldn’t be sure that TheSecondDatabase was there, so we create a local stub, called ‘dummy’, on TheFirstDatabase that returns the columns referenced by its two views, which would otherwise require the remote database. The name of the local stub object doesn’t have to match anything, so we use the name ‘dummy’ to emphasize why we’re doing it. Using the dummy ‘stub’ as the base object each time, we create synonyms for each of the remote objects referenced by the views in this database (TheFirstView and TheFourthView).

We then build each of these views, using its synonyms for the remote reference. SQL Server checks the existence of the column(s) using the underlaying ‘dummy’ data source. If it finds the correct parameter name or column name (it does not check the datatype), it is satisfied, and the views build successfully. Once each object is successfully built, we can delete its dummy synonym and create a new one with the same name but the full, 4-part, cross-server reference. This, fortunately, isn’t checked at this stage, so this sleight-of-hand isn’t detected until we’ve ensured that the linked server references are all set up and checked.

Here is the second script that builds TheSecondDatabase, on the server Aristobulus, which works in the same fashion.

Listing 4: Build script for TheSecondDatabase, with cross-server dependencies

These two databases should now have been successfully built. You now need to verify them by first checking that there is a linked server on the instance for every remote database reference, and that it has the referenced database(s) with the referenced object(s). You can do this in SSMS.

Automated builds with cross-server dependencies using SQL Change Automation

Now we’ll stop cheating and try to incorporate these techniques for coping with cross-server dependencies into an automated build system for doing CI. This must allow developers to update individual objects and add new functionality whilst preserving the existing data. When this essential upgrade process is practiced and tested throughout the deployment process, it can be used in staging and release to production with more confidence

We still have two databases on two servers, and we want to be able to develop them independently, and build them independently, whether to build a new version from scratch, or update an existing version of the database. We need to be able to develop and build each database from object-level scripts. Unfortunately, we can’t use the technique I’ve illustrated above because these batches are designed to be simply executed to build the database. A standard build server will just execute every object-level script in the directory, in the sequence specified by an ordered list.

SQL Change Automation is not a straightforward build server; it uses the SQL Compare engine to do the builds, and SQL Compare doesn’t use the source code that you have in source control directly. It uses the collection of scripts to create a ‘model’ of the database, and then compares this model to and the target database and devises a script that migrates the target database to the version represented by the ‘model’. So, a build (from scratch) is simply a comparison to an empty target database. You get an entire build script, but it isn’t just the individual object files concatenated. The problem for us is that our entire narrative of clever changes to synonyms and temporary stubs is stripped out because it is irrelevant to a synchronization script. You may have all sorts of migration devices in the source file, in addition to the database objects, just as I’ve demonstrated in the previous listings, but they won’t be used.

So how do we get it all to work?

The object source scripts

In our example, each database has its conventional object-level SQL script files:

Figure 2: The build script for a table

The build scripts for the views that reference remote objects make conventional use of synonyms, just as demonstrated in Listing 2. As this is a simple demo, I’ve not done separate files for each Synonym, but included them with the build scripts:

Figure 3: The build script for synonym and a view

The pre-build “stubchecks”

Each of our synonyms must, at build time, be able to reference a valid object of the correct name. For example, in order to build TheFourthView, in TheFirstDatabase, we’ll need at least a ‘stub’ in place for TheSecondView, in the TheSecondDatabase, which will return TheFirstColumn. This stub is enough to convince SQL Server that the correct reference exists even though the stub doesn’t do anything.

In short, any object that is the target of a remote reference (from Figure 1, this is all the tables plus TheSecondView) will need to have at least stubs in place, before we do any build operation with SQL Change Automation (or SQL Compare). Therefore, each database includes a script (I’ve called it StubCheck) that is run before any genuine building is done and checks for the existence of each of its objects that are referenced externally or is likely to be so. If a referenced object does not exist on the database, it creates a stub for it. When the SQL Change Automation build process starts, the SQL Compare or SCA changes the skeletal form of the database into the real database.

We need a StubCheck script for each participating database, and each one is hand-cut and modified whenever the interface changes. Listing 5 shows the StubCheck for TheFirstDatabase.

Listing 5: StubCheck script for TheFirstDatabase

Figure 4 shows the StubCheck for TheSecondDatabase. I’ve saved it in the PreScripts folder, and there is no harm to them being re-run, but it is not an official pre-script (which must be called PreScript.sql). SQL Change Automation will only ever run one of these per build operation, and we need run each stubcheck script manually, for every database, before we start the build

Figure 4: StubCheck script for TheSecondDatabase

If no server instance is available to do provide references, you can have a local database with stubs on it, and alter the synonyms accordingly, but you will need to change the synonyms when you deploy the database to its intended location. As long as you can compile the routines (procedures and views) without error, and you can have the synonyms in place, that’s OK. You just change the synonym when it comes to provisioning the database. If you need to run tests before provisioning, then you would need to change the synonym to point to a ‘mock’ database that can perform what functionality is required for the test.

The PowerShell Build Script

So, we have everything in place, and can build all the contributing databases. This PowerShell code can be modified to work on just one database, but it must still do a pre-build check that the stubs exist on all the databases.

Listing 6: SCA PowerShell script for building databases with cross-server dependencies

This arrangement can be defeated if you are updating existing databases, and two different databases change their interfaces in one revision when there are mutual dependencies. Updating the stubs won’t help. The answer, of course is to not do it, but if you do, then change the Stubcheck for that build to alter the new referenced object if it already exists. Or to start with an empty database for both participants in the mutual dependency.

Conclusion

If your database makes references, and therefore has dependencies, outside the database then any complications in building or updating databases can be overcome by means of stubs, mocks and synonyms. They don’t, and shouldn’t, box you into any architecture or build method. The only requirements are a cool head and an understanding of the significance of build errors when they happen. The use of linked servers can be tiresome at the setting-up stage, especially if you are using ODBC providers that are flawed in any way. However, they proved an excellent way of allowing SQL Server to coexist with databases of different types and brands in the one system.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter
  • John Arick

    Isn’t it preferable to use openquery() over the 4 part naming for linked server queries?

    I’ve always seen performance hits with 4 part naming because the optimizer does not utilize the remote server’s indexes or statistics.

  • Mike Vessey

    lots of great info here. I’ve inherited multiple servers with dozens of cyclic linked servers after the old DBA didn’t index correctly and just split the server up into 3 servers.
    we’ve been using DNS and 4 part naming to get round this, but as soon as I tried to make CI builds I got into real difficulties…. I’d never considered synonyms to overcome my build failures

You may also like