Problem:

We work with a vendor database and they don't want the flyway_schema_history table in production.  They will periodically run scripts that delete any objects they don't manage.  

Solution - Save the Flyway Schema History (FSH) data to another location:

1. Add a MERGE INTO T-SQL script with the current data from the FSH table into either source control or some other known location. (This can be done fairly easily with sp_generate_merge.)
2. Run a pre-deploy script that verifies whether the FSH table and sp_generate_merge exist on target database. (If not, adds them, and runs the MERGE INTO script to populate the FSH table.)
3. Run a standard Flyway migrate.
4. Run a post-deploy script that uses sp_generate_merge to get latest data from FSH and updates our MERGE INTO statement as necessary, either in source control or in the known location as necessary.

This way, deployments still work even if someone has deleted the FSH table. There is a risk that some scripts may get executed twice if any get deployed outside the normal process, but the intention is that this will not be possible and that scripts will be written to be re-runnable anyway.
I can actually see this working quite nicely, with Jenkins committing updates to the FSH MERGE INTO script back into git, providing developers with a log in git of which scripts have been executed on prod. It also avoids the need to manage any additional admin databases and allows us to keep all the necessary deployment scripts and metadata together in source control.

Code is available at https://github.com/Alex-Yates/not_TPR. 

TODO: still need to get Jenkins to push the flyway_schema_history data back into source control.

If vendor only drops tables

Use Views: 

1. Create a separate Flyway database and add the flyway_schema_history table in there
2. Create a view in the original database
   CREATE VIEW dbo.flyway_schema_history AS SELECT * FROM [Flyway].[dbo].[flyway_schema_history] 

Note - SYNONYMS do NOT work:

1. 2 Production databases: "prod" and “prod_shadow”
2. Run flyway migrate on both. Both deployments ran successfully. Each DB now had a populated flyway_schema_history table and a handful of identical objects.
3. Deleted the flyway_schema_history table on “prod”, and created a synonym that pointed to the flyway_schema_history table on “prod_shadow” instead.
4. Attempted to run flyway migrate again. Was hoping that the deployment to the “prod” DB would use the flyway_schema_history table from “prod_shadow”. However, it appears that Flyway is clever enough to know that it’s not looking at the actual flyway_schema_history table. It either is unwilling or unable to accept the synonym in place of the traditional flyway_schema_history table.
   
   

If you have any feedback on this, please let the Development Team know.