Handling two interconnected databases with SQL Source Control

Guenter

I have an ETL process completely coded in SQL (no change of techology wanted) that uses a StagingDB where the incoming data is stored in the source format and a QueryDB where data is stored in native format.
The ETL process is controlled by a bunch of stored procedures in the QueryDB which call a bunch of procedures in the StaginDB (to preprocess data before it is imported into the query store), Within the preprocessing the StagingDB uses the QueryDB e.g. to match Identifiers.

Both databases are SQl Source Control-linked to the same repository (in folders "QueryDB" and "StagingDB").

I find it annoying that I cannot produce an automated deployment script because the inter-database dependencies are not honored by the SQL Source Control script generating process.
Every once in a while I create a new Table in the QueryDB and one in the StagingDB and views and procedures in both DBs to handle the ETL. This breaks the autognerated scripts from SQL Source Control as I would have to order script parts from both DBs correctly to make them work. So for now I have to use trial and error and extract parts of the generated script to other script files to get all in the correct order.
(and no, there are no circular references, this is assured by the development process).

Does anyone know how to organize such a project so I can use the generated scripts from SQl Source Control without manual post-processing?

Peter_Laws

Hello Guenter,

If your experimentation, did you observe that the incorrect order was that elements of a single scripts were authored in the incorrect order?
Or, were there individual scripts (I.E db objections) being executed in the wrong order please?

This may seem like a semantic difference, they are handled by different parts of the code base.
The simple answer to your question is no, there's no manual override, we'd need to identify and fix the issue.

While what you've described is straight forward enough, in order to identify the issue we'd need to replicate the fault. I appreciate you likely cannot share you ETL process even privately, would you be able to create a small sample db that demonstrates the issue that we could use as a frame of reference?

Guenter

Hi Peter,

there is no "error" in the product, it's just a missing feature. 
Imagine the following:

• a table "Customers" in database A with a CustomerID from an autoincrement column that contains the customers of interest in your working database
• a table "ImportCustomers" in database B that contains a superset of customer data imported from some 3rd party
• a procedure prepareCustomers in database B that retrieves the CustomerID from table "Customers" in database A and joins it with the "ImportCustomers" in database B on some condition to find existing customers and writes new attributes to a table "ETL.Customers" in database B
• a procedure updateCustomers in database A that calls prepareCustomers in database B and then merges the content of ETL.Customers from database B into Customers in database A

Now some day the third party offers an additional column "Preferred Database Tool Vendor".

To import that, I have to 

• add a column to Customers in database A
• add a column to ImportCustomers in database B
• add a column to ETL.Customers in database B
• modify the prepareCustomers function in database B so it can use the newly created column in database A as a join criteria
• modify the updateCustimers function in database A to use the newly created column in ETL.Customers

With the current version of SQL Compare, I can only generate one script per database.
But I cannot run the script for database A before the script for database B because the column in ETL.Customers in database B is missing.
And I cannot run the script for database A after the script for database B because the column in Customers in database A is missing.

I would like to have ONE script for both databases that 

• creates/modifies all tables in A
• creates/modifies all tables in B
• creates/modifies all Views in A and B, honoring dependencies between views in both databases
• creates/modifies all stored procedures in A and B, honoring dependencies between procedures in both databases

In the above case, the correct order would be

1. modify Customer in A
2. modify ImportCustomer in B
3. modify ETL.Customer in B
4. modify prepareCustomer in B
5. modify updateCustomer in A

Hope this clarifies my issue.

Peter_Laws

It greatly does, thank you.

While we do have support for cross database dependencies, unless these elements were constraints, I don't believe they would be detected, as you've said.

I'm not certain if this falls in the intended scope of the product, but I will raise the feature request and let you know the outcome.

Peter_Laws

I've spoken to the developers and their initial assessment is that it would necessitate a sizable architectural change to accommodate.

Presently there hasn't been much demand for this functionality, however that doesn't mean it isn't wanted, people might just not have asked. What I'd recommend is head to the feature request forum (below) as that will provide a much better platform for your idea and more easily enable others to show their support if they too would appreciate this functionality.

https://redgate.uservoice.com/forums/sql-compare

Thank you for sharing your ideas Guenter