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 CreateSchemaBackup.sql script 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 FindLogicalPaths.sql script 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, 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 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.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 inside your migrations script folder. Once found open it up, and 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 we created can be accessed by Flyway, with the best location for doing this inside Flyway Desktop. 

To do this:

1. Open Flyway Desktop: Navigate to the project settings.
2. Update Connection Strings: Modify the connection strings to point to the correct server or environment where your databases are hosted.

This flexibility allows you to run the project on different servers or even in cloud environments, such as Azure, by simply adjusting the connection settings.

If you head into Flyway Desktop, you should now see that the error goes away when being refreshed, and can connect to the Databases that we just created! 
It is important, however, that we check the connectivity before moving forward as if everything has gone well, we should be done with the setup! 

To test our connectivity, do this:

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

2. We can edit the connections of both our Development and Shadow (the database used by Flyway for calculations). 

3. It is important that we edit the connection, and update the values to match wherever you created the databases with the script. As mentioned, it is by default looking for a database on LocalHost. If you did this somewhere else, update the connection to point to the server and then test the connection!

4. Once this has been validated, we want to head to the migrations tab, and make sure all of our 'Target Databases' are also correct! 

5. As we did before, edit each of these connection strings and test that a connection is possible! Once this has been done, We should 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.