Jodie Beay was already apprehensive when v1 was released. She was concerned about the two-database design, which was in place to separate database functionality. ‘OK, OK, the designers should have considered using Schemas for this.’ She thought, ‘But this design is what we’re dealing with. Originally, the Foreign-exchange information was going to be an external oData service via SSIS, but there were performance problems and the data feed proved to be tricky.’ This was how a Foreign exchange database, on a separate server, came into existence. Jodie had dealt with cross-server dependencies before and it had always been tricky.

The Development team were using SQL Release, which is part of the DLM Automation, and Octopus Deploy, to do the automated deployment to the QA environment. It was the first deployment since the initial release.

Jodie took a deep breath and hit the deploy button. The deployment succeeded, so she ran some manual tests to make sure everything was working. When she ran the UpdateRatesFromForex procedure, red letters immediately filled the screen.

A quick review of the procedure showed there was a reference to a database that didn’t exist on the current server. It was a hard-coded reference to a function in the Forex database, and it failed because the Forex.dbo.GetLatestExchangeRate function couldn’t be found.

Jodie scratched her head. Of course it couldn’t be found. The developer was referencing a three-part name when the Forex database actually existed on an entirely separate server in Quality Assurance (QA), requiring a four-part name: <server>.<database>.<schema>.<object>.

Even then, it wouldn’t work because you can’t call functions via four-part names. Sighing, she wandered down the corridor to the developers and told them their changes wouldn’t work on production. It was Derek’s code. ‘I can’t believe it, it worked fine on my machine!’ he spluttered. The two of them checked the laptop. On the laptop, and all the other development machines, the two databases were on the same server. So the code was able to reference SQL objects such as tables, views, functions and procedures in development machines using three-part names. Fortunately, in QA, they had to be on separate servers as they would be in production, and so executing the procedure had failed. The reference would need a four-part name and a linked server to the Forex server. A function would have to be called via OPENQUERY.

Jodie sighed. How do we deploy code that specifies, in SQL, the location of the databases when that location is different across environments? The problem was that a linked server is configured at server-level, not database, so it can’t really be scripted as part of the database deployment. Of course, when you link the servers and databases, you would give them a consistent ‘logical’ name. It would be possible to check that the links were consistently configured, with the same name, across environments. The trouble is you still wouldn’t get over the fact that the developers had the Forex database on the same server.

Derek and Jodie scratched their heads for a while. Jodie said ‘We can do a fix for this. The trick is to use ODBC as an interface. By doing this, you can link a server to itself and treat it as a separate server, exactly as it would be in production. You can then use a consistent four-part reference in every environment. A developer would set up a system DSN on the development machine, using ODBC, which links to their local SQL Server. This could be called anything, of course, but let’s call it ‘ForexDSN’. Then you’d add in a linked server using this ‘ForexDSN’ DSN by executing this code on the server…

‘Once you’ve done that,’ she continued, ‘the four-part reference would work for tables, views and procedures as long as the Forex database was installed on the local server. Now, in each environment, the linked server is given the same logical name, ForexServer, but links with whatever external live server is appropriate in that particular environment. You see, effectively, you’ve abstracted out this dependency from the code into a server setting. Then, as far as I can see, the simple deployment will work without any scripts or complications. You can also configure the security as appropriate.’

But Derek had a problem with this, ‘You’d still have the difficulty that user-defined functions can’t be called with this type of four-part reference. You’d either have to use stored procedures or call the function via OPENQUERY. When the function was on the same server and you used the three-part reference it worked fine, but it wouldn’t work with four-part references!’

They sat down together and tackled this problem. It finally started working but, even when wrapped up in a local function, it was looking as if using OPENQUERY wasn’t going to be a particularly elegant option.

However, Jodie seemed satisfied. ‘This is going to be in just one place. You can call it locally and all the nasty stuff happens inside the function. It’s cool.’

Derek pursed his lips. ‘Let’s just change it to a stored procedure, and use Synonyms’.

Jodie shrugged ‘It’s your call. But this way, you can do all your access of the Forex database via the one local function. And it’ll work in development just as long as the DSN is set up and the linked server is defined on the server to use it.’

Derek frowned. ‘We shouldn’t be having four-part names in the database at all. We should have a synonym in place that gives us a level of abstraction so we only need to change one part of the code if we have to change the reference. Having one local function is fine, as long as we never need any more. I don’t like the idea of having to maintain a bunch of OPENQUERY functions each time we want to use another function from Forex. I’d feel happier if we abstract this interface by using a synonym. Actually, I’d rather access the two databases only from the application. It would be a much cleaner solution.’

‘You can’t limit the access to the application. We have an end-of-day reconciliation process on SQL Agent that requires the Forex information. Also, whichever option you choose you have just one object to change if you changed the name of the server, because you only have one function’.

Derek called a couple of other developers, Riza and Will, over to discuss it. Eventually the consensus was for using synonyms and procedures. Jodie shrugged and went back to her admin chores.

Derek and Riza went back to the Forex development database and changed the GetLatestExchangeRate function into a stored procedure with an output variable. This also required changes to the application, which relied on the same function. To keep track of these changes, they pushed it through source control and deployed the result to QA.

After changing the GetLatestExchangeRate function into a stored procedure, they added the synonym, dbo.GetLatestExchangeRate, in LightningWorks on Derek’s development server. This synonym pointed at the new stored procedure GetLatestExchangeRate in the Forex database. They then used this synonym in the UpdateRatesFromForex procedure instead of the hardcoded reference to the original function.

The modified UpdateRatesFromForex procedure now looked like this:

After they deployed this change to QA, running the UpdateRatesFromForex procedure succeeded.

‘Jodie?’ Derek called over the phone, ‘It’s working now. It passes all the tests. Can you sign-off the release please?’

‘That’s great. Just let me run some tests on QA.’

Bar the sound of rapidly tapping keys, the phone line went quiet for a moment. Then Jo’s voice came through again. ‘Oh yes, all my tests passed.’

There was another slight pause. ‘Oh. Hang on a minute’, continued Jo, ‘You do realize you were accessing the server via the sysadmin role? That’s a major security problem. It goes against the principle of least privilege. Sorry guys, but you’ve got to reduce those permissions used by connections from the LightningWorks server to the Forex server to only what you need.’

Derek and Riza conferred yet again. They decided to create a new user with the role GetRates in the Forex database, with an associated SQL login, GetLatestRates. The GetRates role only had permission to run the GetLatestExchangeRate procedure. They then set up the linked server to use the GetLatestRates login instead of sysadmin.

The first deployment to QA succeeded, but tests showed that calling GetLatestExchangeRate was now failing. This was because SQL Release ignores user role membership by default, so the new user wasn’t part of the GetRates role in QA. This meant they couldn’t run the function. To fix this, they had to remove the “IgnoreUsersRolesAndPermissions” option from the call to SQL Release.

With this change made, they deployed again to QA. The password for the GetLatestRates login was set to the default of ‘p@ssw0rd’, so they manually updated it to something more secure.

So far so good. They had it working manually. But they needed to automate it to avoid the chances of a deployment failing due to human error.

To automate the changes, there would need to be SQL DDL scripts to:

• Set a secure password for the GetLatestRates login
• Create a linked server with the correct server name and database name on the server containing the LightningWorks database
• Set the RPC Out option to ‘True’ for the linked server
• Edit the linked server to use the GetLatestRates login when connecting to Forex
• Update the synonym to use the correct server and database name in each environment

The only complication was that these scripts needed slight changes specific to each environment, so had to be parameterized. They used Octopus Deploy variables to set different values depending on the environment. These variables were referenced in the scripts using “#{VariableName}“.

They then wrote simple post-deployment scripts locally that would make these changes. They tested the scripts and made them part of their deployment process in Octopus Deploy.

On Forex they ran the script:

On LightningWorks they ran the script:

They deleted the manual changes that had been made to QA and ran a deployment to QA with the new scripts. This was successful.

Later that day, Jodie was talking to Phil, the grizzled senior DBA in the team. She told him about the problem and the solution they’d decided on.

‘Given the cross-server dependency, what would you have done, Phil? Derek thinks that they’d have done better to pull the dependency out of the database into the application. That would have avoided making the changes to the Forex database, which would have been difficult if other applications relied on it. Derek’s also worried about having post-deployment scripts and having to update them every time we change the synonyms or linked server users.’

Phil thought about this. ‘Actually, I would avoid multiple servers altogether. Unless you’ve got special performance reasons for needing two separate servers, you’re much better off partitioning your functionality by using schemas. If the databases were owned by different teams and were being developed and updated independently, then I’d use separate databases on the same server. Both are easy to reference. Using separate servers complicates transactions mightily and causes these deployment problems too. You should only do it as a last resort.’

‘Well, what about the idea of pulling the dependency out of the database and into the application?’

‘Hmm. Given you have an existing system with two databases that need to communicate, your choices depend on whether you have scheduled batch processes or ETL systems that require the link. You’d also have a worse problem if a second application started to use the database. I don’t think that the complication of the special login to read the Forex rate is an issue, because you’d have to do it whichever way you decided. Configuring a linked server isn’t really part of the database script because it’s dependent on the environment.’

‘So what about using synonyms or four-part references?’

Phil scratched his beard in thought. ‘Yes, well synonyms or four-part identifiers are equally valid solutions. But you’d only want to use a synonym if you’re accessing several routines in the linked server from several parts of the code, and if you weren’t having to access user defined functions. Personally, I’d have simply kept the function as it was, and made sure that all environments referenced the databases with the same logical names. After all, a synonym change and a function change represent the same amount of DDL changes. And the access is only in one place, the body of that messy-looking function.’