Sitecore — Changing Databases Names Deployed by MSSQL-Init Image

It’s not always clear how Sitecore prepare their scripts that are inside the containers for installations like 10.1 and 10.2. Sometimes we have to figure out things by ourselves, and this one was a detail that I couldn’t find anywhere.

In this post I will show you how to change the default Sitecore databases names to use any name you want.

Why is this useful?

We recently got a request from a client that wanted to have one Azure SQL server for 2 different environments, and as you might know, it’s not possible to have databases with the same name in an Azure SQL server, you have to use different names. This was my use case, maybe you have your own reasons if you are reading this post.

Our steps will be the following:

• Download docker-examples folder to build the new mssql-init image;
• Pull and Access mssql-init XP1 image to retrieve the files that need to be modified;
• Modify the Files;
• Add them to mssql-init folder to be copied to the image during the build;
• Run the build;

Downloading the Folder to Build the Image

Here I’m using the default Sitecore docker examples hosted on Github:

docker-examples/custom-images at develop · Sitecore/docker-examples

Retrieving the Files from the Sitecore Image

The first thing you have to do is to extract all the necessary files for this task. You have to pull, create and connect to the container that has the mssql-init image. To do so, just run the following:

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

Worth noting that I’m using Sitecore 10.2 here. The command above will modify the image entrypoint and allows you to navigate through the files in this container.

Now that you are there, you have to download the following files:

And also, within the “resources” folder download databases.json:

You can use the following command to copy the file from your docker container to your local machine:

docker cp <ContainerID>:/File/Path/To/Download Local/Path/
docker cp 5bcddb5ba2a:/C:/installShards.ps1 C:/tmp/resources

If you have VS code, you can also use the docker extension to help you on this task, I prefer it personally, because you can simply select the file and download it:

After downloading all of them, make sure you put the files in the following location:

docker-examples-develop\custom-images\docker\build\mssql-init

Modifying the Files

databases.json

Starting with the databases.json file, all you have to do is to look for the databases names and modify their names:

Don’t change anything else, keep the dacpacs the same name, you are just modifying the database name.

DeployDatabases.ps1

This is the most complicated script to change:

• On line 123, change it to:

$databaseName = "<prefix>.$($dacpac.BaseName)"

• This will add a prefix in front of the database name and expand the attribute BaseName, that is, if the dacpac name is Sitecore.Core, it will become <prefix>.Sitecore.Core.
• Then you have to add this code right after line 123:

$ConnectionString = "Data Source=$SqlServer;database=$databaseName;User ID=$SqlAdminUser;Password=$SqlAdminPassword;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString;
try{$sqlConnection.Open();}catch{}
$state = $sqlConnection.State
$sqlConnection.Close();

• I’ve added this verification because I noticed that the script tried to deploy the databases twice, and we don’t want that, so basically what it does is to connect to the database if exists, check if the state is open and store it in a variable called $state, and then close the connection. The next part will use the $state variable;
• Finally, to use the $state variable, we will add it on line 132:

-or $state -eq "Open"

This verification will make sure that the database will be skipped deploying if it exists.

InstallShards.ps1

In this script, all you have to change is on line 12 and 15, to add the prefix in the hardcoded database name:

SetSitecoreAdminPassword.ps1

Same thing as the previous script, two harcoded databases names to change on line 21 and 23:

StartInit.ps1

Lastly, on line 34 we have to do the same thing as we have done for DeployDatabases.ps1 script, which is adding the <prefix>. and expand the variable:

"DEV.$($_.BaseName)"

Dockerfile

You need to copy the edited files to the image and also modify its entrypoint to change the databases names in the entrypoint as you can see in the snippet below:

Building the Image

It’s now time to build the image. Make sure that the files are located in the correct folder:

docker-examples-develop\custom-images\docker\build\mssql-init

Navigate to the folder that has the docker-compose files in it. Note that I’m using docker-compose and compose.override that comes from the XP1 installation, so I’ve just renamed them to the following:

Now you can build the image by doing the following:

cd docker-examples-develop\custom-images
docker-compose build --no-cache mssql-init

This will build the mssql-init image only, not the entire solution. After that, you’ll have the image ready to deploy the renamed databases:

Testing the Image

After having the image ready, I tested it in an AKS cluster that deployed the databases to an Azure SQL server, and here is the result:

As you can see, I’ve created two sets of databases at the same SQL server and I had no issues at all. The websites are running as expected:

Bonus

If you don’t want to go through step by step to prepare the files, I’ve got you covered! Here are the files with the proper changes, you just have to build the image now:

medium/sitecore-mssql-init-changes at main · RafaelM1994/medium

Well, that’s it for today’s post, if you have another way to do the same thing, let us know in the comments.