Best Practices for Multiple Teams Deploying to the Same Instance (SQL Server)

This document discusses use-cases when there are multiple teams that deploy to the same database instance. We will discuss the following two use-cases in this scenario:

  1. Each team has one or more dedicated database(s) in the same database instance
  2. Multiple teams write to one or more shared databases in the same database instance

Use-case 1: Each team has one or more dedicated database in the same instance

This is a common use-case for applications that integrate multiple subsystems. Multiple teams, each with their own Git repository, deploy database changes into their own dedicated databases in the same database instance.

Team Service account Database
Team 1 svc_team1_dbA DatabaseA
Team 2 svc_team2_dbB DatabaseB
Team 3 svc_team3_dbC DatabaseC
Team 4 svc_team4_dbD DatabaseD

Structuring Git repository

Each team will dedicate a directory in their repository for database changes. In this directory, they can manage all their scripts along with the changelog.xml file. The team can also create a directory for their database.

In the case of a team deploying to multiple databases, they could organize their repository where they have a subdirectory for each database.

The team needs a main changelog file which can control the flow of all changes to all databases.

See the section "Deploying with Liquibase" later in this document for sample scripts.

Use-case 2: Multiple teams write to one or more shared databases in the same instance

In this use-case, multiple teams, each with their own Git repository, deploy database changes to one or more shared databases. This use-case is common for legacy applications that have added subsystems over time or other complex application architectures.

Team Database
Team 1 DatabaseA, DatabaseB, DatabaseC
Team 2
Team 3
Team 4

There are three important considerations here:

  1. You will use a single URL and credentials to connect to all databases. This requires that you use a single service account which has permissions to multiple databases.
  2. Within SQL scripts, each object would need to be fully qualified with database name, schema name and object name. This is a requirements because of the single service account used to deploy to multiple databases.
  3. Liquibase tracking tables (DATABASECHANGELOG and DATABASECHANGELOGLOCK) will be created only in one database that is specified in the URL. Deployments to multiple databases will be tracked by a single DATABASECHANGELOG tracking table in the database specified in the URL.

Note: It is important to understand that teams must communicate with each other about their database changes going into shared databases. Database changes often have dependencies, and these changes need to be coordinated so that they are deployed in the correct order.

Structuring Git repository

When multiple teams share common databases, it is not possible to use each team's own application repository. This use case requires setting up a dedicated SQL repository for shared databases.

The team needs a main changelog file that can control the flow of all changes to all databases.

See the section "Deploying with Liquibase" later in this document for sample scripts.

Sample mainChangelog.xml

Here is a sample mainChangelog.xml file which can be used in a multi-database repository:


<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.6.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.6.xsd ">

    <include file="databaseA/changelog.xml" />
    <include file="databaseB/changelog.xml" />
    <include file="databaseC/changelog.xml" />

</databaseChangeLog>

Deploying with Liquibase

The basic script for deploying with Liquibase using a single service account to deploy to multiple shared databases looks like this:

# add liquibase path to the environment variable
export PATH=<Path to Liquibase>:${PATH}

# git clone or retrieve a versioned artifact
git clone <GIT URL to the repo> 

cd <Repo>/sql_code

# run the "status" command
liquibase status --changelog-file=masterChangelog.xml \
          --url=<Database URL> \
          --username=<username> \
          --password=<password> \
          -–verbose

# run the "update" command
liquibase update --changelog-file=masterChangelog.xml \
          --url=<Database URL> \
          --username=<username> \
          --password=<password>

# run the "history" command
liquibase history --changelog-file=masterChangelog.xml \
          --url=<Database URL> \
          --username=<username> \
          --password=<password>

The script for deploying with Liquibase using proxy users to deploy to one database at a time looks like this. This is for the SQL Server use case (not the use of --default-catalog-name argument):

# add liquibase path to the environment variable
export PATH=<Path to Liquibase>:${PATH}

# git clone or retrieve a versioned artifact
git clone <GIT URL to the repo>
cd <Repo>/sql_code
LBDATABASENAMES="${1:-"HR OC OE SH"}"

for LBDATABASE in $LBDATABASENAMES
do

# run the "status" command
liquibase status --changelog-file=masterChangelog.xml \
          --url=<Database URL> \
          --username=<username> \
          --password=<password> \
          --default-catalog-name=${LBDATABASE} \
          –-verbose

# run the "update" command
liquibase update –-changelog-file=masterChangelog.xml \
          --url=<Database URL> \
          --username=<username> \
          --password=<password> \
          --default-catalog-name=${LBDATABASE}

# run the "history" command
liquibase history --changelog-file=masterChangelog.xml \
          --url=<Database URL> \
          --username=<username> \
          --password=<password> \
          --default-catalog-name=${LBDATABASE}
done

Liquibase automatically creates DATABASECHANGELOG and DATABASECHANGELOGLOCK tables (also called DBCL tables) for tracking deployments. These tables are created in the default database for the user connecting to the instance.

If you want your DBCL tables to be created in another database, e.g., a dedicated database for Liquibase tracking only, then you can use --liquibase-catalog-name=<database> to indicate your specific database. You need to make sure that the service account allows creating DBCL tables in this database, and allows querying these tables.

  • --liquibase-catalog-name=<catalog>
    • The parameter specifies the catalog to use for the creation of Liquibase objects, like the DATABASECHANGELOG and DATABASECHANGELOGLOCK tracking tables.
  • --default-catalog-name=<catalog>
    • The parameter specifies the default catalog name to use for the database connection.