In this post, I will be explaining how to customize the default ‘mssql-init’ image to change the default sitecore database names.

For this, in my Sitecore Helix example forked version, I have upgraded the sitecore version from XM 10.1 to XM 10.2. You can follow the instructions to up the docker containers locally from the readme file. Once it is up, if you login to SQL and notice, the Sitecore Database names ( master, web and core) are by default Sitecore.Core, Sitecore.Master and Sitecore.Web regardless of our solution name. It looks bit not okay for me, as I wanted to have the database names according to the name of my project in certain formats like {ProjectName}.Sitecore.{DatabaseName}. Also if there is a need to use the same SQL Server instance for two different environments, then there will be some blockers as the database names are expected to be unique.

So in this blog we will be seeing how to make the database name customizable in certain format as per our need. For this, we have to play around the mssql-init image.

What is this mssql-Init container?

The main responsibility of this container is to simply create and attach the required sitecore databases to the SQL Server docker container. It simply attaches the databases from the resources folder and execute some script to set the database users and set the sitecore admin passwords etc., and it stops. You can think of it like a starter.

Let us decompile this mssql-init image to see what is inside it.

Decompiling the mssql-init Image

I was searching whether there could be a direct way to achieve what I wanted to have, like some sort of setting/configuration etc., But I did not find anything. So we have to override this mssql-init image. As a first step, let us extract the image and see what is inside to understand it better.

As mentioned, I have used Sitecore XM 10.2 and we need to analyse this image ‘scr.sitecore.com/sxp/sitecore-xm1-mssql-init:10.2-ltsc2019‘ in detail.

To analyse this image, we need to start a powershell session and the default entrypoint has to be overridden. So that the container will be running and we can analyse the files inside it. To do so, run the following command in VS Code.

docker run -it --entrypoint powershell scr.sitecore.com/sxp/sitecore-xm1-mssql-init:10.2-ltsc2019

It will start the container. You can either use VSCode to analyse or Visual Studio’s Docker container of your choice. I personally choose, Visual Studio Docker Container to see the files inside an image.

So from Visual Studio, open the docker container and check the files inside the container. Note that this container is not part of our basic-company-nextjs application, so you will be seeing this in Other Containers tab like below.

It has the above highlighted .ps1 scripts and some .bacpac files in the resources folder. We are interested to see what they are doing in the below list of files,

• StartInit.ps1
• DeployDatabases.ps1
• SetDatabaseUsers.ps1
• SetSitecoreAdminPassword.ps1
• resources/databases.json file

Reason why we have to apply customizations in these resource files to achieve our use case ?

The entry point script of this image will be StartInit.ps1. From here, the other .ps1 files will be called to deploy the databases, to create data base users, to set the Sitecore Admin Password etc.,

If we take a look at the DeployDatabases.ps1 script, it simply reads all the .bacpac files from the /resources location and it attaches them to the SQL Server.

In the resources folder, we are having our Sitecore.Master, Sitecore.Core and Sitecore.Web bacpac files. So the above .ps1 script will simply read them and attach it to the SQL server.

If we check the database.json file again the names are hard-coded. This file will be used by the SetDatabaseUsers.ps1 to create the database users and to set the Sitecore Admin passwords.

So there are no options where we can pass our database name like we have it in case of solr-init. So we need to override these files to change the database name as per our need.

Let us see in detail how we will be doing it.

Applying Customizations

The steps will be as below,

• Downloading the required .ps1 and resource files from the original image
• Do the modifications in the downloaded files
• Modify the corresponding DockerFile to push these updated files into the container and docker-compose files for the mssql-init, cm, cd and id containers ( as we will be changing the database names)
• Build and run the containers

As first step, download these files into docker/build/mssql-init folder like below.

Changes in StartInit.ps1 file

We have to define a variable that holds the content of the databases.json file with the below highlighted changes in the name property. We are using format,

{DatabasePrefix}.Sitecore.{Databasename}

Also notice that we will be reading the DatabasePrefix from the Env variables of mssql-init container. And finally we are copying this value into the resources/databases.json file as highlighted below.

And while checking the existing databases, we need to supply our prefix too like below.

The $prefix will contain the value from the ENV variable and needs to be passed to rest of our DeployDatabase.ps1, SetSitecoreAdminPassword.ps1 as a parameter. Please check the line number 85 and 94 to see we are sending it as a parameter in the below given script.

The full modified script will look below,

[CmdletBinding()]
param (
    [Parameter(Mandatory)]
    [ValidateScript({ Test-Path $_ -PathType Container })]
    [string]$ResourcesDirectory,
    [Parameter(Mandatory)]
    [string]$SqlServer,
    [Parameter(Mandatory)]
    [string]$SqlAdminUser,
    [Parameter(Mandatory)]
    [string]$SqlAdminPassword,
    [Parameter(Mandatory)]
    [string]$SitecoreAdminPassword,
    [string]$SqlElasticPoolName,
    [object[]]$DatabaseUsers,
    [string]$DatabasesToDeploy,
    [int]$PostDeploymentWaitPeriod
)
$prefix = [System.Environment]::GetEnvironmentVariable("SQL_DATABASE_PREFIX")
$databaseConst = '[
    {
        "name": "' + $prefix + '.Sitecore.Core",
        "scripts": [
            "CreateUser.Core.sql",
            "SetSitecoreAdminPassword.sql"
        ],
        "dacpacs": [
            "Sitecore.Core.dacpac"
        ]
    },
    {
        "name": "' + $prefix + '.Sitecore.Master",
        "scripts": [
            "CreateUser.Master.sql"
        ],
        "dacpacs": [
            "Sitecore.Master.dacpac"
        ]
    },
    {
        "name": "' + $prefix + '.Sitecore.Web",
        "scripts": [
            "CreateUser.Web.sql"
        ],
        "dacpacs": [
            "Sitecore.Web.dacpac"
        ]
    },
    {
        "name": "' + $prefix + '.Sitecore.Experienceforms",
        "scripts": [
            "CreateUser.ExperienceForms.sql"
        ],
        "dacpacs": [
            "Sitecore.Experienceforms.dacpac"
        ]
    }
]';
$deployDatabases = $true
# Push the above content into the databases.json resource file
Set-Content -Path $ResourcesDirectory\databases.json -Value $databaseConst
if (-not $DatabasesToDeploy) {
    $serverDatabasesQuery = "SET NOCOUNT ON; SELECT name FROM sys.databases"
    $serverDatabases = Invoke-Expression "sqlcmd -S $SqlServer -U $SqlAdminUser -P $SqlAdminPassword -Q '$serverDatabasesQuery' -h -1 -W"
    $existingDatabases = Get-ChildItem $ResourcesDirectory -Filter *.dacpac -Recurse -Depth 1 | `
                            Where-Object { $serverDatabases.Contains($prefix + "." + $_.BaseName)}
    if ($existingDatabases.Count -gt 0) {
        Write-Information -MessageData "Sitecore databases are detected. Skipping deployment." -InformationAction Continue
        $deployDatabases = $false
    }
}
if ($deployDatabases) {
    Write-Information -MessageData "ResourcesDirectory is $ResourcesDirectory" -InformationAction Continue
    .\DeployDatabases.ps1 -ResourcesDirectory $ResourcesDirectory -SqlServer:$SqlServer -SqlAdminUser:$SqlAdminUser -SqlAdminPassword:$SqlAdminPassword -EnableContainedDatabaseAuth -SkipStartingServer -SqlElasticPoolName $SqlElasticPoolName -DatabasesToDeploy $DatabasesToDeploy -DatabasePrefixName $prefix
    if(-not $DatabasesToDeploy) {
        if(Test-Path -Path (Join-Path $ResourcesDirectory "smm_azure.sql")) {
            .\InstallShards.ps1 -ResourcesDirectory $ResourcesDirectory -SqlElasticPoolName $SqlElasticPoolName -SqlServer $SqlServer -SqlAdminUser $SqlAdminUser -SqlAdminPassword $SqlAdminPassword
        }
    
        .\SetDatabaseUsers.ps1 -ResourcesDirectory $ResourcesDirectory -SqlServer:$SqlServer -SqlAdminUser:$SqlAdminUser -SqlAdminPassword:$SqlAdminPassword `
            -DatabaseUsers $DatabaseUsers   
        .\SetSitecoreAdminPassword.ps1 -ResourcesDirectory $ResourcesDirectory -SitecoreAdminPassword $SitecoreAdminPassword -SqlServer $SqlServer -SqlAdminUser $SqlAdminUser -SqlAdminPassword $SqlAdminPassword -DatabasePrefixName $prefix
    }
}
[System.Environment]::SetEnvironmentVariable("DatabasesDeploymentStatus", "Complete", "Machine")
Start-Sleep -Seconds $PostDeploymentWaitPeriod

Changes in DeployDatabases.ps1

The changes in this file here will be these. We are adding one more parameter,

And also copy and replace the below in the line number 125.

$databaseName = $DatabasePrefixName + "." + $dacpac.BaseName

Changes in SetSitecoreAdminPassword.ps1

Similer to the above script, the change will be to introduce one more parameter to hold the prefix name and apply it to the database names like below.

Now we have done the required changes in the .ps1 files. Let us move on to modify our mssql-init Docker File and docker-compose files.

Changes in mssql-init Docker File

We have to copy these updated .ps1 files to the image. Check the line number 9-11. The full dockerfile will be like this,

# escape=`
ARG PARENT_IMAGE
ARG HEADLESS_SERVICES_IMAGE
FROM ${HEADLESS_SERVICES_IMAGE} AS headless_services
FROM ${PARENT_IMAGE}
COPY .\StartInit.ps1 c:\StartInit.ps1
COPY .\DeployDatabases.ps1 c:\DeployDatabases.ps1
COPY .\SetSitecoreAdminPassword.ps1 c:\SetSitecoreAdminPassword.ps1
# Copy and init the JSS / Headless Services Module
COPY --from=headless_services C:\module\db C:\jss_data

Changes in docker-compose file

mssql-init:-

We need to set our prefix as a env value as this will be used from our .ps1 files.

SQL_DATABASE_PREFIX: ${SQL_DATABASE_PREFIX_NAME}

Changes in the service definition of Id, CM and CD:

We need to apply our database name changes in the ConnectionStrings as below by simply prefixing it.

And finally to add the prefix in the main .env file

Build and Running the Container:-

Now simply run \.up.ps1 script. It will build and run the container. Now let us check by logging into the SQL server.

For first time, If we take a look at the mssql-init container running logs, we can notice that the databases are attached to SQL Server as expected,

You can also notice that the new databases are created with expected name and the site is also up and running using this new databases.

Also the sites are working as expected.

You can take the full look at the Docker files from my github.

Thanks for reading…..!!!! Stay tuned for more….!!!!