MSSQL Setup on MAC using bacpac dump from Commerce Cloud

[KashifIbrahim]

Introduction

The need to debug and play with the real data is evident, what if you don't have enough data on your local MAC to check all the scenarios? Some developers would surely like to get the data from SAP Commerce Cloud infrastructure and use it locally. If you are one such developer, continue reading.

Pre-requisites

1. Docker must be installed
2. have a db dump .bacpac file

Docker setup

First, we need to create a docker image for the MSSQL, and then we will start the server on Docker.

If you don't have Docker, get it downloaded and setup from here.
Start Docker, and run the following commands (with your changes) on Terminal.
This pulls the latest image for the MSSQL server:

docker pull mcr.microsoft.com/mssql/server:2022-latest

Now run the following command to create and run your new docker container.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_USER=SA" -e "MSSQL_SA_PASSWORD=yourSecretPassword123" -p 1433:1433 --name mssql --hostname mssql --platform linux/amd64 -d mcr.microsoft.com/mssql/server:2022-latest

Spoiler

Note that on some modern Macs with Apple Silicon-based processors you may get a message from Docker along the lines of:

WARNING: The requested image's platform (linux/amd64) does not match the detected host platform (linux/arm64/v8) and no specific platform was requested

To circumvent this you can navigate to Settings in your Docker Desktop then click Features in Development and check "Use Rosetta for x86/amd64 emulation on Apple Silicon.

Docker will restart and your containers should be running properly.

Here you can see that we are using the following:
1. user = SA
2. password = yourSecretPassword123 (use your own)
3. port = 1433 
4. server name = mssql
5. hostname = mssql
6. platform = linux/amd64

Now, I've used "mcr.microsoft.com/mssql/server:2022-latest" to get the latest image and not used "mcr.microsoft.com/azure-sql-edge:latest" as I had some issues with azure-sql-edge version while creating the database using bacpac file.

After this command, you should have a new container running, something like this:

Installing Azure Data Studio

Instead of running a bunch of commands from the Terminal, we will mostly be using the Azure Data Studio for creating our database out of the db dump file.
Azure Data Studio is a lightweight, cross-platform data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server.
Here is the setup:

1. Install Azure Data Studio: Download here
On the page scroll down and make sure to download the correct version for your MAC (based on the chip)

2. Run Azure Data Studio: Once downloaded and installed, run the Azure Data Studio, you should see the following screen:

3. Make the connection with MSSQL server running in Docker:
Click on New -> New Connection, and specify the connection details on the dialog:

Click on "Enable trust..." if a dialog popup appears.

Now the left pane should show the connection, you should also be able to see a screen similar to this:

4. Install extensions for Azure Data Studio: 
Now, let us install a few more extensions to Azure Data Studio so that we can import the bacpac file.
Install the extensions listed in the image below, not all of these are required for importing the bacpac file, but are really helpful in many other admin functions, make sure to install DACPAC extension, this extension is required for working with bacpac files.

5. Configure to make MSSQL Contained:

Open the Query Editor on Azure Data Studio

and run the following script:

USE master
GO
sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1
 
GO
RECONFIGURE
GO

This will make sure that you do not get any errors such as :

Spoiler

The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database. You may need to use RECONFIGURE to set the value_in_use.

6. Import the dump file:
Now, before you import the dump file, make sure that the virtual disk space allocated to Docker is more than the DB Size that you are importing. For instance, if your DB size is 10GB, make sure you allocate at least 20GB to Docker, remember that there are other containers as well, in that case you might have to increase the virtual disk space accordingly.

Here is how to increase the disk space for Docker, in my case I was dealing with a 55GB database, so I increased the Visrual disk space to 120GB. This will restart your Docker, make sure that the MSSQL server is running after Docker restarts.

Restart Azure Data Studio, on the left panel under Databases, right click and select "Data-tier Application Wizard"

Select Create a database from a .bacpac file

Provide .bacpac file location and enter any database name for your liking, then follow the wizard.

This process will start the task of creating the database, when the task is finished you will be able to see your new database under the databases, similar to the image below:

Modify local.properties to connect to this DB

After the database is available in Azure Data Studio, and the import is finished, connect to the new DB by changing the database properties in the local.properties file:

db.url=jdbc:sqlserver://localhost:1433;databaseName=${db.name};responseBuffering=adaptive;loginTimeout=10;disableStatementPooling=false;statementPoolingCacheSize=1000
db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
db.username=sa
db.password=yourPassword123
db.name=mydb
db.tableprefix=

Conclusion

There are only a few resources available online that allows you to import bacpac file to your local setup on a MAC device. I hope this helps you with your development struggles, all the best.

Additional Perks: Azure Data Studio could run queries on the connected databases. Check the Azure Data Studio documentation on the link I shared above. Enjoy.