• Flyway
• Database Dependencies
• Database Deployments

Dealing with Cross-Server Database Dependencies in Flyway Projects

Many relational database management systems (RDBMS) such as Microsoft SQL Server, Oracle Database, and MySQL allow cross-server references via linked servers. Unless their use is sensibly planned, however, cross-server dependencies can cause unnecessary grief in both development work and in continuous integration.

Whereas it is almost risk-free to make a connection to existing databases on other servers with an unchanging interface, it becomes tricky when some of the participating databases are changing in an uncontrolled way. The worst problem, and the one we deal with here, comes about when two or more databases come to depend on each other because of references to objects in the other database. This can happen easily if the development of the database happens ‘organically’, to put it politely.

When you will want to build or migrate one of the databases without all the remote databases being present, how do you do it? In the following examples, I’ll be using Flyway with SQL Server to illustrate how to avoid problems when developing and deploying database systems that are distributed across several servers. The technique I demonstrate, using synonyms alongside temporary local ‘stub’ objects, allows you to work independently on a database with Flyway, even if it has cross-server dependencies. In PostgreSQL, it is possible to use user-defined types to achieve the same thing, but it will ‘add a manual dimension’. Be aware that with these techniques potentially breaking changes in the remote reference might not be discovered till you run integration tests.

If you’re dealing with mutual (‘circular’) dependencies between databases on the same server, then the same technique will work, but I describe a simpler one here that does not need synonyms: Dealing with Mutually Dependent Databases in Flyway.

Linked Servers and remote object references

The Linked Server system lets users create and use database links that connect two or more servers, allowing them to access, query and update data in objects, such as tables, that are in another database server. This can be useful for consolidating data from different servers, or for creating complex data models that span several servers.

The RDBMS handles these cross-server database calls entirely differently from local cross-database calls. 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 the RDBMS.

On inspecting any SQL that makes cross-server calls, the most obvious difference is that we use four-part references (server.database.schema.object) to the objects on the linked servers, rather than the three-part references used for cross-database dependencies on the same server.

For example, let’s say there is a view in the Hyrcanus database that has a 4-part reference to a table in another database (Antigonus), hosted on a remote server (Phasael):

When we run this script, the RDBMS will immediately check that the referenced object (ThirdTable) exists. If it doesn’t, because the remote server isn’t currently accessible for example, then the script will fail with an ‘invalid object’ error. Even if it is, development can become tricky when some of the participating databases are changing in an uncontrolled way.

Even if the remote server is constantly accessible throughout the database lifecycle, or you have local test versions of the other databases in the group, and there are no changes to the objects you reference, you will face other problems. For example, it is possible to hit the problem of doing a rebuild for a group of databases that make cross-server references to each other such that none can be built without the existence of all the others.

In SQL Server, SQL Synonyms will help solve these issues, with a little help from the use of temporary ‘stub’ objects.

Handling remote object references using synonyms

To cope with cross-server dependencies in SQL Server, we generally use SQL Server Synonyms in our code in place of the actual four-part address of the object. So, continuing our previous example, instead of a view making a direct cross-server reference, we create a synonym that references the remote object and then the view uses the local synonym:

This has several advantages. Firstly, synonyms provide 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.

Secondly, 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 doesn’t help us here, at least not directly, because of course the script immediately uses the synonym in the view, at which point SQL Server will verify the remote object, and if it doesn’t exist, or has a ‘breaking change’, then the script will still fail.

To get around this, we need to create a temporary local ‘stub’ object to represent the remote object. We can give the temporary stub any name, such as ‘dummy’, but SQL Server will check that it returns the correct column names or parameter names (it does not check the datatypes).

We create the synonym on the  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:

The lack of create-time checking of the synonym allows us to swap out the referenced object merely by deleting the synonym and creating a new one with the same name but pointing to the new object. SQL Server won’t detect this sleight-of-hand until we run code that uses the synonym (such as by querying the view). This means that we can now build or alter this database without the remote references having to be in place.

This lack of checking is usually considered a drawback because your migrations would not fully check all remote dependencies, as it does the local ones. This means your migration 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 would then try to access the underlying base objects. Therefore, when using synonyms, you’ll need to make sure your subsequent post-migration tests access them all, so that the remote references are checked. Otherwise, you might get errors when you first execute a query with a synonym in staging or production.

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. This means that this same technique, using synonyms and local stubs, will also work when our databases are on the same server. However, it confers no particular advantages to do so. You might consider a simpler technique that uses temporary stubs. For a very simple database you might simply restore copies of all the dependent databases, before starting the build.

Building databases with cross-server dependencies using Flyway

In this demo, we’ll have two databases with mutual cross-server dependencies, via synonyms, as follows:

A database development team needs to be able to build or change these databases, using Flyway, in any order we like, whether the other remote database(s) are there or not.

Create the linked servers

The first step is to establish each of these as a linked server for the other (you can do this in SSMS or T-SQL).

Here is the SQL Server version that I’d run on the server Phasael to create Aristobulus as a linked server, in the form close to what SSMS generates:

The build scripts

We need two Flyway migration scripts, one to build the database Antigonus and one to build Hyrcanus. Each build script uses the techniques discussed earlier. For each object that makes a remote reference, via a synonym, we build it initially with a reference to a local ‘dummy synonym’ (based on a temporary local stub) and then sneakily swap in the synonym with the real remoted reference.

You can find both migration scripts in my Cross-Server project on GitHub, but here is the build script for the database called Antigonus on the server Phasael:

There is a similar script to build the database Hyrcanus on the server Aristobulus.

Running Flyway migrations on databases with cross server dependencies

We’ll run the build of the two cross-referencing databases in Flyway. I’m using my Flyway Teamwork framework, but only to take care of IDs and passwords. You can deal with that in the Flyway command line or preferably in environment variables (see, for an example, Getting Started with Flyway and any RDBMS).

To keep things uncomplicated, we use a directory for each server, as many as you have:

We then run this code to build the databases on their separate servers. The flyway clean is only necessary if you repeat the operation.

We run get the following report (I’ve removed the irrelevant undergrowth of text)…

Successfully dropped pre-schema database level objects (execution time 00:00.091s)
Successfully cleaned schema [dbo] (execution time 00:02.130s)
Successfully cleaned schema [dbo] (execution time 00:01.840s)
Successfully dropped post-schema database level objects (execution time 00:00.251s)
Successfully validated 1 migration (execution time 00:00.353s)
Creating Schema History table [Hyrcanus].[dbo].[flyway_schema_history] ...
Current version of schema [dbo]: << Empty Schema >>
Migrating schema [dbo] to version "1.1.1 - Cross-DatabaseExample"
Successfully applied 1 migration to schema [dbo], now at version v1.1.1 (execution time 00:00.933s)
Successfully dropped pre-schema database level objects (execution time 00:00.011s)
Successfully cleaned schema [dbo] (execution time 00:00.802s)
Successfully cleaned schema [dbo] (execution time 00:00.176s)
Successfully dropped post-schema database level objects (execution time 00:00.044s)
Successfully validated 1 migration (execution time 00:00.122s)
Creating Schema History table [Antigonus].[dbo].[flyway_schema_history] ...
Current version of schema [dbo]: << Empty Schema >>
Migrating schema [dbo] to version "1.1.1 - Cross Server Example"
Successfully applied 1 migration to schema [dbo], now at version v1.1.1 (execution time 00:00.228s)

Flyway works fine when there are more than one server and database in the migration, just so long as we keep information such as access methods, UserIDs and the like in the Flyway.conf config file

These two databases should now have been successfully. To test it, we’ll execute this from a query window in SSMS connected to Phasael.Antigonus…

then in the query window connected to Astrobulus.Hyrcanus, we execute

Now we can check that both servers can access data from each other. Whilst we are in the query window connected to the database Astrobulus.Hyrcanus we execute:

…then back to a query window in SSMS connected to the database Phasael.Antigonus:

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 development method. Having said that, Flyway deals very smoothly with multi-database and multi-server systems. 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 different types of relational database systems to coexist in the one system.

Tools in this post

Flyway

DevOps for the Database

Find out more