Azure SQL Database framework objects in database source control

Applies to

ApexSQL Source Control

Summary

This article will explain the starting point in database source control for Azure SQL Database using ApexSQL Source Control. This article will provide a general overview of all additional source control objects created during the linking a database to source control repository process, depending on the chosen development model.

Description

Even though the basic working options are the same when it comes to SQL Database and Azure SQL Database, there are some differences regarding the login/user settings. In the Azure SQL Database environment, there can be only one server admin, and it cannot be changed. Also, any other user will not have the same level of the Server permissions, and they cannot be granted as well.

Based on this, database source control for Azure SQL Database will be a little bit different than for SQL Database when it comes to creating additional source control objects and location of them based on the linked database. More about necessary database source control permissions for Azure SQL Database can be found in the Minimal permissions for Azure SQL Database in database source control article.

Since the creation of those additional source control objects, called the framework objects, depends on the chosen development model during the linking a database to the source control repository process, to explain them, in this article will be linked two databases (Test_01 and Test_02), one in the dedicated development model and the other in the shared development model.

Dedicated development model

The first step in the database source control for Azure SQL Database is linking it to the source control repository. Right-click on a database in the Object Explorer panel, in our case on the Test_01 database, and from the context menu, choose the Link database to source control command:

Database source control command in the Object Explorer panel

In the Source control setup window, the default tab that will be shown is the Connection type tab. No matter which option is chosen under this tab, the native solution (linking a database to any of the supported source control systems) or working folder (linking a database to a local folder without the need for a source control system), it will not have any effect on the framework objects creation, it will affect only on the type of information needed under the System login tab in order to successfully link a database to source control repository.

Based on that, in this example, the native solution will be selected, and from the Source control system drop-down list, the Git source control system will be chosen.

Click the Next button to proceed:

Native solution for linking a database to source control repository

Under the Development model tab, the development model for database source control must be chosen. Based on the chosen development model the specific framework objects will be created. Since in this example the database should be linked in the dedicated development model, that option will be chosen:

The dedicated development model chosen for database source control

All information from this point on related to linking a database to source control repository, the first steps for database source control, can be found in the How to link a SQL Server database to source control system article.

After the linking part is done, and the database is linked to the source control repository, the database and all linked object from that database icons in the Object Explorer panel will be changed:

Object status for the linked database in the Object Explorer

This applies that for the chosen dedicated development model, the following framework objects are created in the linked database:

  • ApexSQL_SourceControl_DatabaseLog – created under the Tables node as part of the dbo schema, where will be stored all the changes made against linked database objects caught with ApexSQL_SourceControl_DDL Trigger
  • ApexSQL_SourceControl_DDL Trigger – database trigger created under the Programmability/Database Triggers node. It is used to catch any change made against any linked database objects and store that change in the ApexSQL_SourceControl_DatabaseLog table
  • ApexSQL_SourceControl_FrameworkVersion – scalar-valued function created under the Programmability/Functions/Scalar-valued Functions node as part of the dbo schema:

Framework objects for linked database in the dedicated development model

Shared development model

The shared development model applies to multiple database developers that are working at the same time on the same shared database. For this development model, ApexSQL Source Control supports more options that are very helpful when it comes to database development in this type of environment. Based on that, more information will be tracked for database source control, which applies that more framework objects will be created.

The first step is to link the chosen database to the source control repository in the shared development model, in our case, the Test_02 database. The only difference between linking it to the dedicated or shared development model, besides the needed permissions, is in the Development model tab in the Source control setup window, so only that tab will be explained.

To link a database in the shared development model, the Shared option must be chosen under the Development model tab:

The shared development model chosen for database source control

By default, when the Shared option is chosen, under the Database field will be written the name of the database that is in the linking process. This means the framework objects will be stored in the same database that is chosen for linking to the source control repository, in our case it is the Test_02 database.

After the linking process is done, and the database icon is changed, the following framework objects will be created in the linked database under the ApexSQL_SourceControl schema:

  • Tables (created under the Tables node):

    • ChangeLog – all information about the made changes against the database objects after the Log changes option is enabled will be stored in this table. More information about this can be found in the How to use the Change log feature in ApexSQL Source Control article
    • ExcludedObjects – database objects that are excluded from database source control (the changes made against those objects will not be tracked), will be stored in this table. Information about how to exclude database object from database source control can be found in the Object filtering article
    • Objects – in this table will be stored linked database objects and their status (checked, checked out and locked, edited, dropped, and database user who changed object status). Based on that information, the object status in the Object Explorer panel will be updated (the icon will be changed based on the object status). More about this topic can be found in the Object status in the database source control article
    • Polices – database settings defined under the Administration tab of the Options window will be stored in this table. More information about these options can be found in the Database development policies, How to use the Change log feature in ApexSQL Source Control , and How to commit changes from other users in the database source control articles
    • UserMappings – the name of the database user who first linked the database as well as the database ID will be stored in this table

  • Procedures (created under the Programmability/Stored Procedures node):

    • UpdateExcludedObjects – this procedure is used to write information about the excluded database objects in the ApexSQL_SourceControl.ExcludedObjects table
    • UpdateObjects – this procedure is used to write information about the database objects’ status change in the ApexSQL_SourceControl.Objects table

  • Function (created under the Programmability/Functions/Scalar-valued Functions node)

    • FrameworkVersion – this function will be used for the upgrade of the framework objects if there is a need for that

  • Database level trigger (created under the Programmability/Database Triggers node)

    • ApexSQL_SourceControl_DDL Trigger – every change made against a database object (object status and DDL change) will be caught by this database trigger and written in the ApexSQL_SourceControl.Object and ApexSQL_SourceControl.ChangeLog tables

  • Note: In the ApexSQL_SourceControl.ChangeLog table the changes will be only written if the Log changes option is enabled under the Administration tab of the Options window

Framework objects for linked database in the shared development model

Even though the framework objects will be created for every linked Azure SQL Database in the database itself, based on the chosen development model, the different set of the framework objects will be created, the framework objects that will suit the chosen environment.

Affected versions

The SQL inventory system explained in this article is implemented in ApexSQL Source Control 2021 version and higher. In the previous versions of ApexSQL Source Control, the scalar-valued function FrameworkVersion will not be created and corresponding permission is not needed.

From the ApexSQL Source Control 2021 version, the Alter any database DDL trigger and Alter any schema permissions are replaced with the Alter permission for databases linked in the shared development model for Azure SQL Database.

 

Related posts:

  1. SQL inventory system upgrade in ApexSQL Source Control
  2. How to manually unlink a database from SQL source control
  3. How to link a SQL Server database to a Team Foundation Server (Azure DevOps Server/Services) repository
  4. Database synchronization warnings and messages in ApexSQL Diff and ApexSQL Diff for MySQL
  5. Minimal permissions for Azure SQL Database in database source control