Redgate Flyway

EDITION: TEAMS

On the Schema model tab, setup your development database.
Click Manage Static Data and configure your static data as described above. For the baseline, only specify static data that is already deployed to your downstream environments.
Capture the static data that is already in your downstream environments in your Schema model. If your development database has additional static data in an existing deployed table, you'll need to edit the Data\Table.sql file in your project to remove these rows that have not been deployed yet. (Or, you can just remove them when you generate the the migration script.)
Generate the migration script to capture all the static data changes that have already been deployed to your downstream environments.
Make sure the script doesn't run on the downstream environments.
1. Option 1: Use the migrations tab to connect to each of your downstream environments and update them so that this script is not ran on them.
  1. Choose flyway migrate and add the following parameters:
    -cherryPick=<yourStaticDataVersionNumber>
    -skipExecutingMigrations=true
    
    In this example, you can see the V004__InitialStaticData.sql script is Pending against my Prod database, but I don't want it to run against Prod since this data already exists in Prod. So I add the parameters above. I can then either click Run migrate or View Dry Run script to see what will happen before migrating.
  2. Clicking View Dry Run script, we can see that running Migrate is only going to insert a record into the flyway_schema_history table so this versioned migration script will recorded so we don't try to execute this script against that target.
  3. After running migrate, you can see the InitialStaticData script has been marked as successfully applied and it did not take any execution time since it was actually skipped.
    →
2. Option 2: Use a Guard Clause
  1. At the top of the generated migration script, write an IF statement to check for the data first before inserting it.
```
IF (SELECT COUNT(*) FROM TABLE = <number>)
THEN
<do insertions>
```

On the Schema model tab, setup your development database.

Configure static data by adding tables to track to the toml config file as follows

  [[redgateCompare.staticDataTables]]
  schema = "dbo"
  table = "Table_1"
 
  [[redgateCompare.staticDataTables]]
  schema = "dbo"
  table = "Table_2"
  excludedColumns = [ "col2" ]

Capture the static data that is already in your downstream environments in your Schema model. If your development database has additional static data in an existing deployed table, you'll need to edit the Data\Table.sql file in your project to remove these rows that have not been deployed yet. (Or, you can just remove them when you generate the the migration script.)
Generate the migration script to capture all the static data changes that have already been deployed to your downstream environments.
Make sure the script doesn't run on the downstream environments.
1. Option 1: Connect to each of your downstream environments and update them so that this script is not ran on them.
  1. Run the following:
```
flyway migrate -cherryPick=<yourStaticDataScriptVersionNumber> -skipExecutingMigrations=true
```
  2. After running migrate, you can run the info command and see the InitialStaticData script has been marked as successfully applied and it did not take any execution time since it was actually skipped.
2. Option 2: Use a Guard Clause
  1. At the top of the generated migration script, write an IF statement to check for the data first before inserting it.
```
IF (SELECT COUNT(*) FROM TABLE = <number>)
THEN
<do insertions>
```

Things to Note

Note: You'll need permissions to your downstream environments to migrate them like this. If you only have read-access, you could provide the DBA with the dry run script to update the downstream environments manually or have them fix up the baseline of your static data through Flyway Desktop using the instructions above.

Note 2: If your downstream environments are in different states, it's easier to synchronize them first and then use the process above. You can use our data comparison tools that are included in Flyway Enterprise to check that the static data across your environments are in synch.

If not, you'll need multiple V scripts to capture the different states. This can be complicated as shown in the following example:
The MyCodes table has the following values in the different environments.

Dev	Test	QA	Staging	Production
MyCodes 1 2 3 4	MyCodes 1 2 3 4	MyCodes 1 2 3	MyCodes 1 2	MyCodes 1 2

You would need to:

Create a V script (e.g., V002__MyCodes-twoRecords.sql) that inserts 1 and 2 and cherryPick V002 and skip it's execution on Staging and Prod.
Create another V script (e.g., V003__MyCodes-thirdRecord.sql) that inserts 3. Then, cherryPick V002, V003 and skip it's execution on QA, since value 3 already exists there.
Create another V script (e.g., V004__MyCodes-fourthRecord.sql) that inserts 4. Then, cherryPick V002,V003,V004 and skip it's execution on Test, since value 4 already exists there.

The next time you go to migrate Test, no scripts will be pending.

The next time you go to migrate QA, only V004 will be pending.

The next time you go to migrate Staging and Prod, V003 and V004 will be pending.

Redgate Flyway

Tutorial - Baseline static data for projects that have already been deployed to downstream environments

Things to Note

Cookies on red-gate.com