How to handle dependencies on procedurally managed objects?

zfransen

We have some tables whose schemas are being managed based on another process using a configuration table. Thus, we do not want to source control those particular tables. However, we have built some views on top of said tables that we DO want to source control. How can we source control the latter without the former? Currently failing to validate with an ignore filter.

way0utwest

I'm not sure you can do this. Perhaps the best way to try this (not sure if this works and can't test right now), is to create a synonym and use that? It might resolve the reference, but I'm sure you don't necessarily want that to be the object deployed to production.

Unfortunately, part of the issue is that we want to ensure that the dependencies are evaluated, which means that if you filter out the table, we still see a view dependency. Since we use SQL Compare, this limits some of the choices. 

There is an /EXCLUDE switch  that might work, but is there a reason you don't want to source control the tables? If they aren't changed by you, there's no issue as there won't be changes deployed.

way0utwest

Good to hear. Hope that continues to work for you.

zfransen

Indeed, not too keen on the synonym approach even though it is quite creative. 

We will take a look at the /EXCLUDE switch.

The main reason we do not want to source control is if procedural changes are made to those tables, then when we work off of a sanitized production restore and attempt to generate migration scripts, changes to that externally controlled schema will be picked up.

Ultimately, it would be beneficial if we could exclude it from the script generation, but include it in the Schema-Model.

Edit:
Just to provide clarity, the procedurally controlled schema is an Operational Data Store that will automatically make additive changes to the tables representing the transactional system. The source controlled logic is the ELT into star schemas. We definitely understand that this particular use case is a corner case-- we will likely not use the script generation function, but take advantage of the rest of the features that come with Change Automation for this particular DB.

way0utwest

That makes sense, as I wasn't sure how often the schema in those tables changes. Synonyms are a hack, and I understand here this being an issue.

Exclude might work, but in talking with the team, they said this sometimes still pulls in dependencies, so you might need to see if this works for you. If it doesn't, maybe the really hacky way to do this is import the migration script and then comment it out completely. This would allow the migration to get deployed with zero effect. You could even include a comment inside the comment that this was an external change from a third party upgrade/patch.

It's not the best, but for now, we don't have a good way to ensuring we can walk a dependency tree while also supporting just partial project changes. There is some work being done on dependencies, but no idea what this will go through the Compare engine, which is what is used here in the SCA process.

way0utwest

I just found this in the docs as well. I haven't tried this, but will attempt to get to it. This might help you: https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/advanced-project-manipulation/partial-projects

zfransen

We ended up writing a script as a predeployment gated by the IsShadowDeployment environment variable. The script generates the dependencies off a conifg table on our shadow deployment server. So far, so good!