Sql CI Builds for databases with external references

ktighe

Hi,

I am currently setting up a Team City build using the "Red Gate SQL CI Build" template. I have configured the build step to use a LocalDB instance for the build.

I'm running into a problem however because the database I'm building contains some references to other databases. There are a few stored procedures that directly reference another database, so the schema deploy to LocalDB fails because the other database does not exist there.

I realize this is not ideal, but there isn't time to refactor these procedures to remove the dependencies at the moment. Is there a way to configure the SQL CI build to tell it about certain dependencies (or even to ignore them?). With the SSDT approach it was possible to build a database schema that referenced exported .dacpac files to handle this problem, but I'm not sure how to accomplish the same with the Redgate tools. Is this possible?

Thanks,
Kevin

Rob C

Hi Kevin,

Dealing with cross-database dependencies is a difficult problem to solve. One option is to make sure the temporary database is able to access the other database, so you might want to think about using the same SQL Server instance as the referenced database to build the temporary database.

There is a blog post from some members of the team responsible for SQL CI on this subject. I suggest that you take a look at it as they've spent some time approaching this subject in depth - https://www.red-gate.com/blog/database- ... pendencies

I hope this helps.

crimdon

I've just started playing with this product since it became part of the Developers tool-belt and I see this as a serious flaw

MikeN

We've just started using this and I've managed to get round the cross database/linked server issue for us.

Basically, based on what Robert suggested, I setup the CI projects to sync a copy of each database (structure only) onto the build server as well as the target, linked servers are simply setup on the build server too.

Simple example...

On Dev box:
Database1 has Reference table
Database2 has Main table and Joined view (which uses Main and Reference)

On Build box:
Database1 (empty)
Database2 (empty)

On Target box:
Database1 (empty)
Database2 (empty)

DLM Automation in VS:
Create Reference.sqlciproj which has Build step, Sync step to build box and Sync step to target box.
Create Main.sqlciproj which has Build step and Sync step to target box.

When the Reference build runs (successfully) it builds a temp Reference database first (then blows it away), then syncs to the build box and finally to the target box; this copy of the Reference database is now up-to-date with the dev box and stays around.

When the Main build runs (successfully) it builds a temp Main database which uses the build box Reference database - all is happily resolved - it then syncs to the target box; this temp copy of the Main database doesn't hang around, but can easily be made to if you have the need, using another sync step to the build box.

Result, on Dev box:
Database1 has Reference table
Database2 has Main table and Joined view

On Build box (structure only, no data):
Database1 has Reference table
Database2 (empty, or populated if you desire and have further dependencies)

On Target box:
Database1 has Reference table
Database2 has valid Main table and Joined view

I've just build this scenario as a proof of concept, also using link servers to other sql instances, and all works fine. There are a couple of minor gotchas that might catch you but it does work.

Hope that helps.