M1 Mac Incompatibility with MSSQL '19

[joshuashafferopti]

Describe the bug
This is a known issue with M1 Macs: microsoft/mssql-docker#668 - unrelated to any Foundation misconfiguration

To Reproduce

1. Have an M1 Mac
2. Read through Docker startup logs

Expected behavior
Successful configuration of Foundation

Screenshots
Docker logs:

Script logs:

Desktop (please complete the following information):

• OS: macOS 12.2.1

[headquarters]

I was able to get the main branch of Foundation CMS running on an M1 Mac on macOS Monterey 12.3.1. I made a lot of changes to setup.sh iteratively so I don't have all the pieces together in a neat package to make a PR. Nonetheless, I wanted to share the research I did and the changes in case others want to work around this issue.

Replace image

First, the mcr.microsoft.com/mssql/server:2019-latest does not support Arm64 (as noted in the original issue description). So, I replaced that image with mcr.microsoft.com/azure-sql-edge:latest in setup.sh.

Use go-sqlcmd

The next problem I hit was that sqlcmd does not exist in the Azure SQL Edge container (see Note under "How to use this Image" at https://hub.docker.com/_/microsoft-azure-sql-edge). The workaround for that is to use this go binary: https://github.com/microsoft/go-sqlcmd

I installed go and downloaded the Arm64 build of this binary from https://github.com/microsoft/go-sqlcmd/releases/tag/v0.5.0

I placed the go-sqlcmd binary in the root of the project, then replaced all the docker exec commands with the following lines:

# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "EXEC msdb.dbo.sp_delete_database_backuphistory N'$cms_db'"
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "if db_id('$cms_db') is not null ALTER DATABASE [$cms_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "if db_id('$cms_db') is not null DROP DATABASE [$cms_db]"
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "EXEC msdb.dbo.sp_delete_database_backuphistory N'$commerce_db'"
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "if db_id('$commerce_db') is not null ALTER DATABASE [$commerce_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "if db_id('$commerce_db') is not null DROP DATABASE [$commerce_db]"
echo "Executing database prep steps"
./sqlcmd -S $SQLSERVER -U SA -Q "EXEC msdb.dbo.sp_delete_database_backuphistory N'$cms_db'"
./sqlcmd -S $SQLSERVER -U SA -Q "if db_id('$cms_db') is not null ALTER DATABASE [$cms_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
./sqlcmd -S $SQLSERVER -U SA -Q "if db_id('$cms_db') is not null DROP DATABASE [$cms_db]"
./sqlcmd -S $SQLSERVER -U SA -Q "EXEC msdb.dbo.sp_delete_database_backuphistory N'$commerce_db'"
./sqlcmd -S $SQLSERVER -U SA -Q "if db_id('$commerce_db') is not null ALTER DATABASE [$commerce_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
./sqlcmd -S $SQLSERVER -U SA -Q "if db_id('$commerce_db') is not null DROP DATABASE [$commerce_db]"

# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -d $commerce_db -b -i "./build/SqlScripts/FoundationConfigurationSchema.sql" -v appname=$APPNAME
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -d $commerce_db -b -i "./build/SqlScripts/UniqueCouponSchema.sql"
echo "Executing SQL scripts"
./sqlcmd -S $SQLSERVER -U SA -d $commerce_db -b -i "./build/SqlScripts/FoundationConfigurationSchema.sql" -v appname=$APPNAME
./sqlcmd -S $SQLSERVER -U SA -d $commerce_db -b -i "./build/SqlScripts/UniqueCouponSchema.sql"

In the above commands I removed the -P flag because go-sqlcmd doesn't support it. I added
export SQLCMDPASSWORD=$password to the setup.sh script to pass the password into those commands.

Fix connection syntax differences

The next hurdle I hit was an issue with syntax for the go-sqlcmd connection string versus what dotnet-episerver commands expect. The go-sqlcmd expects your server and port to look like 127.0.0.1:1433 ($SQLSERVER above). The dotnet-expiserver command expects an MSSQL-like connection string of 127.0.0.1,1433.

Full, messy script

Here is the full script for reference, but note: it's messy since it took a few iterations and has a lot of commented out stuff because I got to various parts of the steps and didn't want to rerun earlier steps that were successful. It'd take some work to make this more reliable to be run from scratch.

ROOTPATH=$PWD
ROOTDIR=$PWD
SOURCEPATH=$ROOTPATH/src

add_sql_container()
{
    if [ $( docker ps -a | grep sql_server_optimizely | wc -l ) -gt 0 ]; then
        echo "sql_server_optimizely exists"
    else
        sudo docker run -d --name sql_server_optimizely -h $1 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Episerver123!' \
           -p 1433:1433 mcr.microsoft.com/azure-sql-edge:latest
        docker cp ./build sql_server_optimizely:/build
    fi
}

while [ -z "$APPNAME" ]; do
    read -p "Enter your app name: " APPNAME
done

read -p "Enter your SQL server name [localhost]: " SQLSERVER
# SQLSERVER=${SQLSERVER:-localhost}
SQLSERVER=127.0.0.1:1433

# dotnet new -i EPiServer.Net.Templates --nuget-source https://nuget.optimizely.com/feed/packages.svc/ --force
# dotnet tool update EPiServer.Net.Cli --global --add-source https://nuget.optimizely.com/feed/packages.svc/
# dotnet nuget add source https://nuget.optimizely.com/feed/packages.svc -n Optimizely
# dotnet dev-certs https --trust

# dotnet build
# add_sql_container "$SQLSERVER"

cms_db=$APPNAME.Cms
commerce_db=$APPNAME.Commerce
user=$APPNAME.User
password=Episerver123!
export SQLCMDPASSWORD=$password
errorMessage="" 

mkdir "$ROOTPATH/Build/Logs" 2>nul

# cd src/Foundation/
# npm ci
# npm run dev
# cd ../..

# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "EXEC msdb.dbo.sp_delete_database_backuphistory N'$cms_db'"
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "if db_id('$cms_db') is not null ALTER DATABASE [$cms_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "if db_id('$cms_db') is not null DROP DATABASE [$cms_db]"
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "EXEC msdb.dbo.sp_delete_database_backuphistory N'$commerce_db'"
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "if db_id('$commerce_db') is not null ALTER DATABASE [$commerce_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -Q "if db_id('$commerce_db') is not null DROP DATABASE [$commerce_db]"
echo "Executing database prep steps"
./sqlcmd -S $SQLSERVER -U SA -Q "EXEC msdb.dbo.sp_delete_database_backuphistory N'$cms_db'"
./sqlcmd -S $SQLSERVER -U SA -Q "if db_id('$cms_db') is not null ALTER DATABASE [$cms_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
./sqlcmd -S $SQLSERVER -U SA -Q "if db_id('$cms_db') is not null DROP DATABASE [$cms_db]"
./sqlcmd -S $SQLSERVER -U SA -Q "EXEC msdb.dbo.sp_delete_database_backuphistory N'$commerce_db'"
./sqlcmd -S $SQLSERVER -U SA -Q "if db_id('$commerce_db') is not null ALTER DATABASE [$commerce_db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
./sqlcmd -S $SQLSERVER -U SA -Q "if db_id('$commerce_db') is not null DROP DATABASE [$commerce_db]"


echo "Executing dotnet-episerver commands"
dotnet-episerver create-cms-database "./src/Foundation/Foundation.csproj" -S 127.0.0.1,1433 -U sa -P $password --database-name "$cms_db"  --database-user $user --database-password $password
dotnet-episerver create-commerce-database "./src/Foundation/Foundation.csproj" -S 127.0.0.1,1433 -U sa -P $password --database-name "$commerce_db" --reuse-cms-user

# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -d $commerce_db -b -i "./build/SqlScripts/FoundationConfigurationSchema.sql" -v appname=$APPNAME
# docker exec -it sql_server_optimizely /opt/mssql-tools/bin/sqlcmd -S $SQLSERVER -U SA -P $password -d $commerce_db -b -i "./build/SqlScripts/UniqueCouponSchema.sql"

echo "Executing SQL scripts"
./sqlcmd -S $SQLSERVER -U SA -d $commerce_db -b -i "./build/SqlScripts/FoundationConfigurationSchema.sql" -v appname=$APPNAME
./sqlcmd -S $SQLSERVER -U SA -d $commerce_db -b -i "./build/SqlScripts/UniqueCouponSchema.sql"


#dotnet run --project src/Foundation/Foundation.csproj

[joshuashafferopti]

@headquarters this is super helpful, thank you for documenting! I am excited to give this a try

[jafetbjarkar]

My experience so far...

• azure-sql-edge did not support all tSQL functions I needed so no luck there.
• I tried setting up mssql image on Linux. That kinda worked but was really slow and a hassle to use.

Now I am still using my old mac when needed. Still waiting for mssql image with M1 support 🙏

[clairan]

Since a while back, Docker can emulate SQL Server, at least the 2022 version. I'm running it in Docker on a M1 Pro, and it works fine :)