How to build a SQL database from source control without dependency problems

One of the caveats of having your SQL database under a source control system, is the overhead when the time comes to deploy a new database build. Even if a single copy of the database isn’t shared among the developers, but rather each developer has its own local copy of the database objects’ scripts which are synchronized with source control on a regular basis, building a deployment SQL script may prove to be a rather challenging task.

Namely, simply executing the database object’s scripts without taking into account objects interrelationships will result in the:

Msg 208, Level 16, State 1

error message and a failed deployment. To fix this issue, the contents of the deployment SQL script need to be reviewed and the individual object scripts need to be rearranged within the deployment script. Doing this manually can be a long and tedious process, not to mention error prone, even if the database that is to be rolled out contains less than 50 objects and is borderline impossible to get right the first time if dependencies of hundreds or thousands of objects need to be checked. The good news is, there is a better way to prevent dependency error on database deployment than by checking them manually.

When building a database deployment script from source control:

1. Make sure to have the latest versions of the scripts for all of the objects that are to be deployed
2. Organize the build script in the following order:
   1. Start with user defined types, if any, and follow up with
   2. User defined functions
   3. Tables
   4. Stored procedures
   5. Views
   6. Indexes, etc.

However, although the guidelines mentioned above are a good rule of the thumb, they don’t address scenarios when objects of the same type depend on one another (e.g. a view which depends on a view). In those cases, objects of the same type still need to be ordered manually.

Another solution is to use a 3^rd party tool that creates deployment scripts from source control.

ApexSQL Build is a SQL database deployment tool that builds new databases or updates existing ones by packaging SQL scripts, script folders, database snapshots and even scripts under source control into a single deployment SQL script, a C# solution or an executable installer. It allows specifying the objects to be created or updated, ensures error-free deployment and more.

Before building a SQL database with ApexSQL Build, make sure that an existing or new source control repository has the latest committed changes.

To generate a deployment script without any dependency problems:

1. Start ApexSQL Build

2. Click the Build button under the Database section in the Home tab:

   

3. Select the Database option as an output type:

   

4. Select Source control as the input source and click the Setup button:

   

5. The Source control wizard will be opened and in the first step a source control system should be chosen:

   

6. Enter the login information and source control system parameters:
   

   Learn more about setting up the Source control wizard from here.

7. Click the Finish button

8. ApexSQL Build will retrieve all files from the source control system to a local working folder:

   

9. Click the Next button

10. Specify the SQL Server where a database will be created along with the authentication type:

    

11. In the Database options step, provide a database name that will be built from the specified source control repository. To copy the database properties from the database on the source control repository, click the Get database properties from input source button:

    

    Once everything is set, click the Next button

12. The list of the objects that will be created is shown in the Build objects step. By default, all objects are checked, but if some objects are not needed, uncheck them and they won’t be included in the building process:

    

13. Once the Next button is clicked, the list of actions that will be executed is shown in the Summaries tab, under the Preview step:

    

14. Switch to the Dependencies tab and any dependent objects will be automatically added to ensure the error free build process:

    

15. In the next step, add Post deployment scripts if there is something that should be executed upon the building process is finished. In this case, two tables, a view, and a procedure will be created after the database is built:

    

16. In the last step choose whether to save a copy of a build script locally or not:

    

17. Before the Finish button is clicked, click the Save button from the Home tab, to create a project file that can be used later for automating the build process:

    

18. Click the Finish button

19. Once the build process is finished, the Results window will be shown:

    

Automating the build process from the source control repository with ApexSQL Build CLI

The previously described process can be also run unattended, by creating a project file at the end of the process and using it in the ApexSQL Build’s CLI.

The saved project file can be called from the CLI, along with the optional switches that can provide additional information of the build process:

This process can be also run with an option to show the prompt window (attended) for database/source control credentials. Learn more about it from article on this link.

The CLI switches can be saved as a batch file or to create a PowerShell script. In either way, this job can be scheduled in four ways of scheduling ApexSQL tools.

Creating a database from a specific version of DDL scripts stored in a source control system doesn’t have to be a problem. ApexSQL Build generates a script that creates all objects successfully and eliminates all problems with missing dependencies.