An introduction to ApexSQL Build

Transcript

Hello and welcome to this ApexSQL Build general overview video. ApexSQL Build is a SQL Server development tool which can build databases, build scripts or executable installers from SQL scripts, scripts folders, snapshots or source control projects. ApexSQL Build also supports database builds for SQL Azure Databases and Amazon RDS for SQL Server.

ApexSQL Build can also:

  • Automate and schedule database builds using the Command line interface or CLI
  • Build databases from one environment to another
  • Consolidate multiple SQL scripts into one SQL script
  • Preview the script impact
  • Ensure error-free deployments
  • and more

ApexSQL Build also supports picking specific SQL objects, along with automatically including any dependent ones, and creating executable installation packages that users can run independently, without installing any other software.

ApexSQL Build consists of two main components:

  • A Windows application used for manual database builds, selecting specific objects and static data, and various options for the build process
  • A console application which allows users to schedule and automate database builds unattended

Let’s begin our tour of ApexSQL Build.

After the application is started, the main application window will be shown where the Build, Update or Consolidate actions can be chosen from the Home tab. Also, previously saved projects can be selected from a list and opened. For the purpose of this video, we’ll start with the Build action from the Database section:

Once the Build action is clicked, the Output type step will be shown with four different output types to choose from:

  1. Database
  2. SQL script
  3. C# solution
  4. And Executable installer

The Database option will be chosen, as an output type to deploy directly to a SQL Server.

In the next step, the Input source should be selected from which SQL objects will be loaded for a new database build. Four different input sources can be selected:

  1. SQL script
  2. Scripts folder
  3. Snapshot
  4. And Source control project

Let’s select Source control project as an input source and click the Setup button next to it to set the source control login information.

In the first step of the Source control wizard, choose the source control system. In this example, Git will be chosen from the drop-down. The other available systems are Mercurial, Perforce, Subversion and Team Foundation Server:

In the next step of the Source control wizard, System login information should be set including the:

  • Username
  • Password
  • Repository
  • Branch
  • And Project

Once all information are provided, click the Finish button in the Source control wizard and all information will be loaded in the Input source step:

After the input source is defined, click the Next button and the Connect to SQL Server step will be shown. In this step, a SQL Server where a database will be built should be provided, along with the authentication type:

In the Database options step, configuration can be customized in order to create a new database. The Database creation script can be used in three different ways:

  1. From source control, where a database creation script was saved during the commit proces
  2. From a script, which can be located locally on the machine
  3. Or By configuration, if the script doesn’t exist, it can be created from the application

All database configuration options can be loaded with a click on the Get database properties from input source button, without the need to set each one individually.

Build objects is the next step in the Build database process and here all objects or a subset can be checked for the build process. By default, all SQL objects will be pulled from the input source, source control project in this case, and all SQL objects will be checked by default, so it’s up to user to uncheck specific objects:

The Preview step contains two tabs:

  1. The Summaries tab contains the summary of all actions that will be taken during the build process such as creating a table:


  2. The Dependencies tab contains SQL objects that are included in the build process indirectly, because they are referenced by previously checked SQL objects in the Build objects step and leaving them out may result in build errors:

Once everything is reviewed, the next is the Static data step. Here we can see if there is any static data in the loaded source control project or scripts folder:

By default, the Include static data option is checked under the Script tab of the Options window and if there is static data, the Static data step will be shown, otherwise it won’t. Also, other Build options can be set here as well, along with the Option output elements that will be included in the build script:

Once the static data is included, optional Post deployment scripts can be loaded in the next step, if there is a need to execute any SQL script immediately upon creating a new database:

In the last step, the Output options step, there are two tabs for setting any final options before the build process is initiated:

  1. The Script tab, in which File location and name can be provided in order to save the build script:

  1. The Optional information tab, in which the previously mentioned Optional output elements can be set, along with the script’s Version number option and to Increment build number automatically option:

If a database needs to be built several times, for example for testing committed objects to a source control repository on a local SQL Server, it can be easily automated by creating a batch file that can be scheduled via SQL Server Job or Task Scheduler. To create and save the batch file, click the Save as batch button in the bottom-left corner in the last step of the Build process and the Save as batch window will be shown:

As displayed, the batch script will contain all CLI switches for options that have been set, as the project was constructed. Additionally, a project file can be created and saved, and it will replace all project options and encrypt any passwords that you might have for authentication to SQL Server and for the source control system. Once everything is reviewed and set, click the Save button from the bottom-right corner of the Save as batch window and provide the location where it should be saved.

After the batch script is saved, the last step of the Build process will be shown again and with a click on the Finish button, the build process will start executing the build script. Once the build process is done, the Results window will be shown along with all information messages about taken actions:

The execution results can be saved as a text file for detailed reviewing.

Once the execution results are saved and the Close button is clicked in the Results window, the initial start window will be shown, along with the same actions in the Home tab from where a new action can be started.

Thanks for watching. For more information, please visit apexsql.com