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

• 
  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.

  Tianjiao_Li July 08, 2019 11:44

  - 0 +
• 
  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.

  Nick_Foster July 09, 2019 10:14

  - 0 +
• 
  Can you confirm if you are using SQL Change Automation project or SQL Source Control project?

  Tianjiao_Li July 11, 2019 10:47

  - 0 +
• 
  SQL Change Automation

  Nick_Foster July 11, 2019 11:01

  - 0 +
• 
  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

  SamTouray July 11, 2019 15:54

  - 0 +
• 
  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 July 11, 2019 16:04

  - 0 +
• 
  We believe @SamTouray 's solution would work for @Nick_Foster !
  
  

  Tianjiao_Li July 12, 2019 11:00

  - 0 +
• 
  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?

  Nick_Foster July 12, 2019 11:21

  - 0 +
• 
  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.

  SamTouray July 15, 2019 08:46

  - 0 +
• 

  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.

  Nick_Foster July 15, 2019 08:55

  - 0 +
• 
  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 July 15, 2019 08:59

  - 0 +
• 
  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 July 15, 2019 09:11

  - 0 +
• 
  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?

  SamTouray July 15, 2019 13:49

  - 0 +
• 
  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.

  Nick_Foster July 15, 2019 14:33

  - 0 +
• 
  @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

  TonyD July 24, 2019 17:29

  - 0 +
• 
  @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.

  Garth_Martin September 17, 2021 17:18

  - 0 +
• 
  @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,

  ...
  
  
  

  Nick_Foster September 20, 2021 08:08

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

  Garth_Martin September 20, 2021 11:57

  - 0 +

Add comment

Please sign in to leave a comment.