I've come to the conclusion that installing SQL Server (or any database for that matter) on your local machine is for chumps. SQL Server, in particular, has some wonky setup issues that can be a real headache. I've had to reformat a machine or install a second instance locally when i've inherited a machine owned by a previous person.

But with Docker, you can keep your database dependency "contained". See what I did there? Don't worry, if people actually read my blog then I would most definitely hear the groan through space and time.

Running a SQL Server database in Docker for development

$ docker run -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=P@55w0rd -p 1433:1433 -v sqlvolume:/var/opt/mssql --name sql-server -d mcr.microsoft.com/mssql/server:2017-latest

This command will create new sql server instance for you, but let's break it down a bit.

The -e parameters are setting environment variables. -e ACCEPT_EULA=Y automatically accepts the EULA that comes with sql server and MSSQL_SA_PASSWORD=P@55w0rd sets the password. Obviously you can change that password.

-p 1433:1433 forwards the SQL Server port to your local machine port

-v sqlvolume:/var/opt/mssql is creating a volume. We need this to keep our databases around. Without the volume, you would get a fresh new SQL Server every single time you restarted the container.

--name sql-server gives your container a name, making it easier to find when you run docker ps or viewing in kitematic.

-d will keep the container running in a detached instance so it's not locked to the command prompt you just typed this in.

The last part of the command, mcr.microsoft.com/mssql/server:2017-latest, is telling docker which base image to run. This is the actual SQL Server image created by Microsoft and is tagged to be the 2017 latest version.

If all went according to plan

Great! Now I should be able to access via Azure Data Studio (previously SQL Operations Studio) or SSMS.

Your SQL Server is going to be sitting on localhost. The user will be SA and your password will be what you set in the environment variable... or P@55w0rd if you just pasted my code.

Working with Azure Databases

We've got our SQL Server running in docker and we can access it. But every time I try to import a BACPAC file from Azure I get this "could not import" error.

A little google-fu will bring up a stack exchange answer to enable contained databases.

-- Enable "contained database authentication"
EXEC sp_configure 'contained', 1;
RECONFIGURE;

And now we can successfully import BACPAC files from azure into our docker instance of SQL Server.