Provisioning Your Environments

In this step, you’ll provision the necessary environments by creating databases based on a schema-only backup of your own database. This process ensures that the Flyway AutoPilot project is tailored to your specific needs. 
To make this process as easy as possible, we have built in logic for Flyway AutoPilot to be able to provision environments for you, both in CI/CD pipelines and deployments for testing, as well as during script generation! 

1. Creating a Schema-Only Backup

Begin by opening the 1.CreateSchemaBackup.sql script (Found in the repositories 'Scripts' folder) in your preferred IDE (SSMS, VS Code, etc.). This script is designed to create a schema-only backup of a selected database and save it directly into your Flyway AutoPilot project.
Edit the script to make sure the highlighted parameters are correct, like below:

Key Script Parameters:

• @SourceDB (Highlighted as 1): Set this parameter to the name of the database you wish to use as the foundation for your project. For example, if you want to use the "AdventureWorks" database, this parameter should be set accordingly.

• @BackupPath (Highlighted as 2): This parameter should point to the backup file's location within your Flyway AutoPilot project. Ensure that the path is correct for your directory, but the backup file name AutoBackup.bak should remain unchanged.

Note:
If you already have a schema-only backup of your database, you can skip this script. Simply place your backup file in the appropriate directory within your Flyway project, ensuring the file is named AutoBackup.bak. The desired end state, is your Backups folder looking like this:

2. Determining Logical and Data File Paths

Next, open the 2.FindLogicalPaths.sql script (Found in the repositories 'Scripts' folder) in your preferred IDE. This script helps identify the logical data and log file names required for restoring databases from the backup.

When restoring a database, it's crucial to know the logical data and log file names of the original database that you created the backups off of. These are typically the same as the database name but can vary. This script will extract these names, which you will need in the following step.

Note:
If you already know the logical data and log file paths, you can skip running this script.
These values are being references in two locations of the Flyway Project, and both need to be updated just once:

1. Inside the next Script, 3.CreateAllFromBackup.sql, we will show you where to add these values in the next step!
2. Inside the B001__baseline.sql script found in the Migrations folder! We will also show in a separate step below how to update this script.

3. Creating the Databases and Updating the Baseline Script

Based on the values we received from the above step, we want to update two scripts. Both the CreateAllFromBackup.sql & B001__baseline.sql.

First, update and execute the 3.CreateAllFromBackup.sql script to create the necessary databases, such as AutoPilotDev, AutoPilotTest, AutoPilotQA, and AutoPilotProd, based on the schema-only backup.

Key Script Parameters to Verify and Update:

1. Backup Path: Ensure the script points to the correct location of your schema-only backup (AutoBackup_Customer.bak) created earlier or sourced manually, this should always be located in the Flyway Project folder, unless specific needs require it to be stored somewhere else.
2. Logical File Names: These should match the logical names identified in the previous step or be manually set if already known.

Once this script has been updated and runs successfully, it will create all the necessary databases, simulating a full environment based on your chosen database schema.

Now, even though we are not ever going to manually run this script, it is important we provide the same values to our "Baseline" Script. For more information about baselines, head here. 

Start by finding and opening the "B001__baseline.sql" inside your preferred IDE. It can be found within the 'Migrations' script folder of your repository. Edit the values specified below, notice it should be a very similar process to updating the script we just executed!

Key Script Parameters to Verify and Update:

1. Backup Path: Ensure the script points to the correct location of your schema-only backup (AutoBackup.bak) created earlier or sourced manually, this should always be located in the Flyway Project folder, unless specific needs require it to be stored somewhere else.
2. Logical File Names: These should match the logical names identified in the previous step or be manually set if already known.

Do Not Run This Script, just save it!

4. Configuring Connection Strings

This step is crucial, as it helps to validate that the databases created can be accessed by Flyway, with the best location for doing this inside Flyway Desktop. 

Therefore, head back into the Flyway Desktop application so that the connection string of each environment can be validated.

1. Open up Flyway Desktop, and click on the settings icon in the top right! 

2. First edit the Development Database configuration details, followed by the Shadow Database (See next step for details). 

3. It is important that the connection details are updated to match the environment the earlier created databases now reside. As mentioned, the default server location of Localhost is used. However, if the databases were created on an instance other than one locally, ensure the connection details are updated to point at the relevant server and then test the connection.

4. Once the connection details for the Development and Shadow databases are successful, you will now want to head to the migrations tab, and make sure all of the 'Target Databases' are also correct (These are important as they will be referenced by our pipeline in a later setup stage)

5. As we did before, edit each of these connection strings and test that a connection is possible. Once this has been done, You will now be ready to use the project in its full capabilities!

About the Databases

After provisioning, you will have the following databases:

• AutoPilotDev, AutoPilotTest, AutoPilotProd: These represent a linear pipeline of databases where changes flow from development to testing, staging, and finally production.

• AutoPilotBuild, AutoPilotCheck, AutoPilotShadow: These databases support the CI/CD process but are generally not interacted with directly by users.

Next step > Capturing Schema