How can I build in Azure Devops with cross database queries?

Nick_Foster

I have an application with two databases, one for live production data and one for archived data.  I am trying to get the live database under Change Automation but there are a number of views and stored procedures that reference or union with records in the archive database.

The project builds fine locally, but the Azure Devops build fails as it's in a hosted build agent and the archive database doesn't exist.

Is there a way to put both databases in the same source control solution and have them built and deployed together that would solve this problem?  Or is there a way to prevent these verification failures?

Tianjiao_Li

I'm afraid you will need to have the other database available in some manner (whether it's creating a fake stub that has objects named correctly to allow the verification to succeed, or you'll need to use the build option that just creates the resource without verification (which isn't recommended) in which case you will need to verify it is all valid on your own.

Two databases can't be in the same repo unfortunately unless you wanted to smash them together and use different schema names rather than keep them in separate actual databases.

Nick_Foster

Ok, we definitely don't want to merge the databases back together. The whole reason to move the archive tables out of the primary db was to keep the backup sizes sensible in case we need to restore them.

Do you have any samples or information around creating a fake for validation purposes? I'm really new to this and would love some help.

Cheers.

Tianjiao_Li

Can you confirm if you are using SQL Change Automation project or SQL Source Control project?

Nick_Foster

SQL Change Automation

SamTouray

A "fake stub" is just a version of the real database but without any data.

When using SQL Change Automation, you store a representation of your database in source control as a set of .sql files that contain DDL statements e.g. CREATE TABLE ... etc.

When you want make make a change, you update source control and use SQL Change Automation to create a database artifact which can then be used to update your target environments e.g. UA, Staging, Production etc.

You need a temporary server for SQL Change Automation to create this database artifact.

If you have views or stored procedures that reference another database on the same server, then you can create a "fake stub" database on the temporary server.

e.g.

Database in source control:       OrderTracker

Database *not* in source control: StaffTracker (fake)

In OrderTracker:

CREATE VIEW dbo.AllOrders

AS

SELECT OrderNumber,

       ...

  FROM dbo.Order

       INNER JOIN StaffTracker.dbo.Staff

Tianjiao_Li

I've escalated this to the development team for the best possible solution and will update here once I hear back from them.

Tianjiao_Li

We believe @SamTouray 's solution would work for @Nick_Foster !

Nick_Foster

Sam told me what a stub was and I already understand that.  What I was asking for was practical advice on how to achieve this in an Azure Devops hosted build.  Would I just have a script that ran in the build to create the stub database?  I can't put it in the pre/post deployment scripts in the project because this only needs to happen in the hosted build process, not any other time.

Has anyone done this before? Do you have any examples you can share?

SamTouray

The creation of the stub is a manual one off task. There is no need to re-create the stub for each build as it won't change.

Nick_Foster

SamTouray said:

The creation of the stub is a manual one off task. There is no need to re-create the stub for each build as it won't change.

Except this is an Azure Devops hosted build. As I understand it the VM is freshly created for each job and then discarded.

SamTouray

You have a SQL Server instance that hosts your "build database": the database that SQL Change Automation creates from source control each time you want to build your database. For example, the build database that you point SQL Change Automation at could be called: "OrderTracker_Build", this entire database gets cleaned and re-created from source control by SQL Change Automation on every build. As long as the stub database is in the same SQL Server instance, then objects that use cross database queries will compile.

SamTouray

I haven't watched these videos myself, but they sound like they cover your scenario: Getting Consistent Builds: Fixing Cross-Database Dependencies: https://www.youtube.com/watch?v=xm6Arpbz078 and

Managing Cross Database Dependencies in Builds - Two Popular Options: https://www.youtube.com/watch?v=20xJTUokUxM.

SamTouray

Sorry, just saw your post about the build being on a hosted VM. If you're not able to run the build on an on-premise build agent then your'e correct in that you will have to find some way to script it (I've never scripted the creation of a non-source controlled database before). Are your test and production databases in Azure, because at some point you'll want to deploy your database changes and if your test/production databases are on premise then you probably need an on premise build agent anyway?

Nick_Foster

Test and production DBs are currently on premise, but deployment will only require the same deployment agent as we run for our web/windows service deployments.
We are using the hosted build service precisely so we don't have to maintain local build agents and keep them up to date.  No-one has time for that, it's the reason we moved from TFS to Azure Devops.
I guess I'll have to find a way to script the stub database into whatever instance the Redgate tasks are using for the build.

TonyD

@Nick_Foster - if you need a scripted solution, we just published a 'how-to' article on Product Learning that might help. It uses SQL Change Automation pre-deployment scripts to create 'stubs' of any cross-database references where the objects don't yet exist. It doesn't require any changes to the source scripts themselves. See: Database Build Blockers: Mutually Dependent Databases

Garth_Martin

@Nick_Foster Did you ever find a reasonable solution for this when using Hosted Agent builds?  I am in the same boat, and all I can find is for scripted solutions.  Thanks.

Nick_Foster

@Garth_Martin We used the pre-deployment script to create a stub with just enough of the related db to validate the build. Something like:

/*To get around the cross database queries we need to scaffold out the archive database*/

IF (DB_ID('Archive') IS NULL)

    BEGIN

        -- create the database

        CREATE DATABASE [Archive]

    END

GO

-- create the objects that we need for validation

IF (OBJECT_ID('Archive.dbo.Alerts') IS NULL)

    BEGIN

        CREATE TABLE [Archive].[dbo].[Alerts]

        (

            [AlertID] [INT] NOT NULL,

            [ObjectID] [INT] NOT NULL,

...

Garth_Martin

@Nick_Foster Thanks, that's basically what we have wound up doing. A bit hacky in my opinion, but it gets the job done.