Bitbucket Database with SQL Server

Before starting with the actual Bitbucket server, we need to configure and setup the DB. This generally consists of the following high level building blocks:

  • Database Engine
  • Initialization Side Car
  • SQL scripts for DB creation and optionally schema definitions if required
  • Database Engine Docker Volume initialization

It is worth noting that all the init will do is execute a single sql file exec command, something that when done manually is extremely simple.

sqlclient -h [host] -u [user] -p [password] -f some.sql

Database Script Runner

One easy way to accomplish this is using a generic Database engine specific script runner that we then use to allow execution of any arbitrary script.

labimagesdb-script-sqlserver
version: '3.3'

services:
  db-script-sqlserver:
    image: infra/db-script-sqlserver:1.0.0
    build:
      context: .
      network: host
      args:
        ARG_ART_URL: http://d1i-doc-ngbuild:3001
      extra_hosts:
        - "d1i-doc-ngbuild:172.22.90.2"

FROM infra/ubuntu/focal:1.0.0

ARG ARG_ART_URL

RUN sed -e "s|APT_URL|${ARG_ART_URL}|" /etc/apt/sources.list.base > /etc/apt/sources.list \
    && apt-get update \
    && apt-get install -y curl netcat dos2unix gnupg \
    && apt-get clean \
    && rm /etc/apt/sources.list

COPY msprod.list.base /etc/apt/sources.list.d/msprod.list.base

# first MS key and sources, then ubuntu, then the actual install
# https://packages.microsoft.com/config/ubuntu/20.04/prod.list
RUN curl $ARG_ART_URL/repository/apt-keys/microsoft/microsoft.asc | apt-key add - \
    && sed -e "s|APT_URL|${ARG_ART_URL}|" /etc/apt/sources.list.d/msprod.list.base > /etc/apt/sources.list.d/msprod.list \
    && sed -e "s|APT_URL|${ARG_ART_URL}|" /etc/apt/sources.list.base > /etc/apt/sources.list \
    && apt-get update \
    && ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev \
    && apt-get clean \
    && rm /etc/apt/sources.list \
    && rm /etc/apt/sources.list.d/msprod.list

ENV PATH="$PATH:/opt/mssql-tools/bin"

# actual code itself

COPY sqlserver-init.sh /init/sqlserver-init.sh
RUN chmod +x /init/sqlserver-init.sh \
    && dos2unix /init/sqlserver-init.sh

COPY init.sh /init/init.sh
RUN chmod +x /init/init.sh \
    && dos2unix /init/init.sh

RUN curl $ARG_ART_URL/repository/dml/docker/tools/wait-for -o /init/wait-for \
    && dos2unix /init/wait-for \
    && chmod +x /init/wait-for

ENTRYPOINT ["/init/init.sh"]
#!/bin/bash

./init/wait-for $SQL_SERVER_HOST:1433 -t 300 -- /init/sqlserver-init.sh
deb [arch=amd64,armhf,arm64] APT_URL/repository/apt-proxy-ubuntu-focal-microsoft/ focal main

#!/bin/bash


# sleeping due to wait for having available port but sql server not yet available
echo "Giving SQL Server time to init after port availability"

sleep 30

echo "Assuming SQL Server is now started to init"

sqlcmd -S $SQL_SERVER_HOST -U sa -P "$SA_PASSWORD" -i /init/script.sql

printf "\nScript Execution Completed\n\n"

As we are using SQL Server for our db script runner, we need to install the Microsoft linux based client connectivity tools via our Ubuntu package manager. However, those are located on https://packages.microsoft.com/config/ubuntu/20.04/ and not the default package manager locations. To ensure we follow our core guidelines for availability, we will leverage our created APT proxy that was setup during the initial Seed Installation (see aptRepositories.groovy). As with the other APT proxy we follow the same command wrapping approach and a customized msprod.list.base file - this time though a double wrapping approach.

# first MS key and sources, then ubuntu, then the actual install
# https://packages.microsoft.com/config/ubuntu/20.04/prod.list
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
  && sed -e "s|APT_URL|${ARG_ART_URL}|" /etc/apt/sources.list.d/msprod.list.base > /etc/apt/sources.list.d/msprod.list \
  && sed -e "s|APT_URL|${ARG_ART_URL}|" /etc/apt/sources.list.base > /etc/apt/sources.list \
  && apt-get update \
  && ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev \
  && apt-get clean \
  && rm /etc/apt/sources.list \
  && rm /etc/apt/sources.list.d/msprod.list

Another element that is worth noting is the usage again of wait-for for side car setups. While we are waiting for the SQL Server process itself on its defined port of 1433, we have to introduce another wait inside the actual sqlserver-init.sh file. This is due to the SQL Server port being made available BUT the underlying database engine still starting up.

Microsoft SQL Server Image

The next challenge is taking control of the SQL Server Dockerfile. Unfortunately that is not quite as easy as the other public Dockerfiles located at https://github.com/Microsoft/mssql-docker.

# mssql-server-linux
# Maintainers: Microsoft Corporation (LuisBosquez and twright-msft on GitHub)
# GitRepo: https://github.com/Microsoft/mssql-docker

# Base OS layer: Latest Ubuntu LTS.
FROM ubuntu:16.04

# Default SQL Server TCP/Port.
EXPOSE 1433

# Copy all SQL Server runtime files from build drop into image.
COPY ./install /

# Run SQL Server process.
CMD [ "/opt/mssql/bin/sqlservr" ]

The above file is the one that microsoft recommends for docker usage via mcr.microsoft.com/mssql/server:2019-latest. However, Looking at available packages for MS SQL Server for Linux Ubuntu available here https://packages.microsoft.com/ubuntu/20.04/, we identified the ability to install SQL by hand via package manager to their latest supported version of Ubuntu focal 20.04. As such we were able to properly take control of this image from scratch as well.

labimagessqlserver
version: '3.3'

services:
  sqlserver:
    image: infra/mssql/2019:1.0.0
    build:
      context: .
      network: host
      args:
        ARG_ART_URL: http://d1i-doc-ngbuild:3001
      extra_hosts:
        - "d1i-doc-ngbuild:172.22.90.2"

FROM infra/ubuntu/focal:1.0.0

ARG ARG_ART_URL

RUN sed -e "s|APT_URL|${ARG_ART_URL}|" /etc/apt/sources.list.base > /etc/apt/sources.list \
    && apt-get update \
    && apt-get install -y curl gnupg gnupg1 gnupg2 \
    && apt install -y software-properties-common systemd vim -y \
    && apt-get clean \
    && rm /etc/apt/sources.list

COPY mssql.list.base /etc/apt/sources.list.d/mssql.list.base

RUN sed -e "s|APT_URL|${ARG_ART_URL}|" /etc/apt/sources.list.base > /etc/apt/sources.list \
    && curl $ARG_ART_URL/repository/apt-keys/microsoft/microsoft.asc | apt-key add - \
    && sed -e "s|APT_URL|${ARG_ART_URL}|" /etc/apt/sources.list.d/mssql.list.base > /etc/apt/sources.list.d/mssql.list \
    && apt-get update \
    && ACCEPT_EULA=Y apt-get install -y mssql-server \
    && apt-get clean \
    && rm /etc/apt/sources.list.d/mssql.list \
    && rm /etc/apt/sources.list

ENTRYPOINT /opt/mssql/bin/sqlservr
deb [arch=amd64,armhf,arm64] APT_URL/repository/apt-proxy-ubuntu-focal-microsoft-mssql/ focal main

Building and Running

First step is building the DB script runner that we will then use as a side car to the database. And of course our main SQL Server image.

docker-compose -f images/db-script-sqlserver/docker-compose.yml build
docker-compose -f images/sqlserver/docker-compose.yml build

#docker login docker-private.acme.com
images/docker-push.sh infra/db-script-sqlserver:1.0.0 docker-private.acme.com
images/docker-push.sh infra/mssql/2019:1.0.0 docker-private.acme.com

To start the DB we will be using the following compose.

labcomposed
version: '3.3'

services:
  sqlserver:
    image: infra/mssql/2019:1.0.0
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=yourStrong(!)Password
    ports:
      - 1433:1433
    hostname: d1i-doc-dbss01
    volumes:
      - sqlserver-data:/var/opt/mssql
      - sqlserver-backup:/custom/bak
    networks:
      - ops-network
networks:
  ops-network:
    name: ops-network
volumes:
  sqlserver-data:
    name: sqlserver-data
  sqlserver-backup:
    name: sqlserver-backup

Simply start a SQL Server instance so we can get ready for config and connect.

# start DB
docker-compose -f composed/docker-compose-sqlserver.yml up --no-start
docker-compose -f composed/docker-compose-sqlserver.yml start

Next is the compose using our generic MS SQL Server script runner. Through the compose we are mounting bitbucket-sqlserver-init.sql into the runner as script.sql to be able to create the initial Database for bitbucket.

labcomposed
version: '3.3'

services:
  db-script-sqlserver:
    image: infra/db-script-sqlserver:1.0.0
    environment:
      - SQL_SERVER_HOST=d1i-doc-dbss01
      - SA_PASSWORD=yourStrong(!)Password
    volumes:
      - ./db/bitbucket-sqlserver-init.sql:/init/script.sql
    networks:
      - ops-network
networks:
  ops-network:
    name: ops-network


CREATE DATABASE bitbucket
GO
USE bitbucket
GO
ALTER DATABASE bitbucket SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE bitbucket SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE bitbucket COLLATE SQL_Latin1_General_CP1_CS_AS
GO
SET NOCOUNT OFF
GO
USE master
GO
CREATE LOGIN bitbucketuser WITH PASSWORD=N'ZeCodeIzGood', DEFAULT_DATABASE=bitbucket, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER AUTHORIZATION ON DATABASE::bitbucket TO bitbucketuser
GO

Run the compose.

docker-compose -f composed/docker-compose-bitbucket-sqlserver-init.yml up

The end result is our new pre-initialized database volume that contains the database and user information required for the Bitbucket instance.

> docker volume ls
DRIVER    VOLUME NAME
local     sqlserver-backup
local     sqlserver-data
local     nexus-data

Previous
Source Control Bitbucket - Intro
Next
Source Control Bitbucket - Server Install
2020-2023 © All Rights Reserved. | Privacy Policy