Editor’s note: This Docker and SQL Server tutorial was last updated on 28 May 2021 to include information about the most recent release of SQL Server 2019. It may still contain some information that is out of date.
It wasn’t long ago that the idea of running, let alone developing, a .NET application or service on any platform outside of Windows was ludicrous. But with Microsoft’s current focus on open source, those days feel like a distant memory.
With the ability to develop using dotnet core, the world of cross-platform .NET has opened up. As of the release of SQL Server 2017 (the current version is SQL Server 2019), you can develop applications and services that can run on Windows, Mac, and even Linux. But what about database technologies?
In this guide, we’ll demonstrate how to launch a SQL Server database inside a Docker container.
We’ll cover the following:
Developed by Microsoft, SQL Server is one of the most popular relational database technologies in the world. Its primary function is to store and retrieve data quested by other applications. SQL Server is commonly used in applications that support transactional and analytical workloads.
Docker enables you to create, manage, and run applications using portable, self-sufficient, and lightweight containers. Containers enable you to package your application along with the dependencies it needs to run from one computing environment to another with minimal breaking changes.
Many .NET-focused developers tend to lean toward using Microsoft SQL Server for their relational database needs. But SQL Server, for as long as many people can remember, can only run on a Windows platform. Our cross-platform .NET development just came to a screeching halt.
To solve that problem, you can use Microsoft SQL Server across multiple platforms by leveraging Docker.
For a visual example of how to run an SQL server in a Docker container, check out our video tutorial below:
To follow along with this guide, make sure you’re using SQL Server 2017 or above. Otherwise, you won’t be able to use cross-platform SQL Server. The most recent version is SQL Server 2019 (15.x), released on 4 November 2019. Use this complete SQL Server version history to ensure your stack is up to date.
You must have docker installed with docker-compose included. This is usually all handled for you if you use Docker Desktop for Mac.
Finally, we’ll use the npm library mssql to connect, update, and query the database container. You can install that locally or globally with an npm install mssql command from any terminal.
Got those all squared away? Cool, let’s learn how to launch an SQL Server database for cross-platform development.
To launch an SQL Server container, first create a docker-compose.yml file in the root of your project. Inside that file, define a sql-server-db resource that uses the SQL Server image that Microsoft provides.
Here is what our docker-compose.yml file looks like:
version: "3.2"
services:
sql-server-db:
container_name: sql-server-db
image: microsoft/mssql-server-linux:2017-latest
ports:
- "1433:1433"
environment:
SA_PASSWORD: "change_this_password"
ACCEPT_EULA: "Y"
To launch our database, we can run an up command from our command line:
$ docker-compose up -d Pulling sql-server-db (microsoft/mssql-server-linux:2017-latest)... 2017-latest: Pulling from microsoft/mssql-server-linux 59ab41dd721a: Pull complete 57da90bec92c: Pull complete 06fe57530625: Pull complete 5a6315cba1ff: Pull complete 739f58768b3f: Pull complete 0b751601bca3: Pull complete bcf04a22644a: Pull complete 6b5009e4f470: Pull complete a9dca2f6722a: Pull complete Creating sql-server-db ... done
We can see in our terminal that the sql-server-db has been successfully created. Now we can explore how we can connect to it to run some queries. Let’s start off by just connecting to our database container:
$ mssql -u sa -p change_this_password
We should now see that we are connected to our database, and mssql is waiting for a command. Let’s go ahead and run the .databases command to see what databases are inside our SQL Server container:
mssql> .databases name ------ master model msdb tempdb 4 row(s) returned Executed in 1 ms
We see that the standard SQL Server databases are present — master, model, msdb, and tempdb. Let’s go ahead and create our own database and a table inside of it. We can do that by creating a SQL script file called my_db_setup.sql that we can run inside our container.
USE master; GO CREATE DATABASE SampleDB; GO CREATE TABLE dbo.MyTable ( id bigint IDENTITY(1,1) PRIMARY KEY, name varchar(500) null ) GO
Now that we have our setup script, we can run it against our database container using mssql:
$ mssql -u sa -p change_this_password mssql> .run my_db_setup.sql USE master; OK Executed in 0 ms CREATE DATABASE SampleDB; OK Executed in 0 ms CREATE TABLE dbo.MyTable ( id bigint IDENTITY(1,1) PRIMARY KEY, name varchar(500) null ) OK Executed in 0 ms
Now that we have run our script, we can list our databases and tables to see everything that just got created:
mssql> .databases name -------- master model msdb SampleDB tempdb 5 row(s) returned Executed in 1 ms mssql> .tables database schema name type -------- ------ --------------------- ---------- master dbo MSreplication_options BASE TABLE master dbo MyTable BASE TABLE master dbo spt_fallback_db BASE TABLE master dbo spt_fallback_dev BASE TABLE master dbo spt_fallback_usg BASE TABLE master dbo spt_monitor BASE TABLE master dbo spt_values VIEW 7 row(s) returned Executed in 1 ms
Just like that, we have our own database and a table configured inside of it. All of this is running as a Docker container that we can share with others and run across a variety of different platforms.
Microsoft has been upgrading more and more of its frameworks, tools, and languages to support cross-platform development. dotnet core is a huge leap forward in terms of .NET/C# development because it can run on Windows, Mac, and Linux.
But .NET is merely a framework; we also need the tools that we often use in that ecosystem to support multiple platforms. That is what we demonstrated here: SQL Server 2017 and above can be run on any platform by leveraging container technology.
With a few lines in a Docker compose file and a simple npm library like mssql, we can launch a SQL Server Database on any platform. This is incredibly handy not only for production deployments but for development environments as well.
Other developers within a team can now use the same database by running docker-compose up and running whatever seed database script we have on hand. We could even create our own Docker image that has the entire database configured and then use that image in our Docker compose file.
With things like Docker and dotnet core, the world of cross-platform development using the .NET ecosystem is more possible than ever before.
If you have any questions about this blog post, AWS, serverless, or coding in general, feel free to ping me via twitter @kylegalbraith. Also check out my weekly Learn by Doing newsletter or my Learn AWS By Using It course to learn even more about the cloud, coding, and DevOps.
Install LogRocket via npm or script tag. LogRocket.init() must be called client-side, not
server-side
Learn more →
$ npm i --save logrocket
// Code:
import LogRocket from 'logrocket';
LogRocket.init('app/id');
// Add to your HTML:
<script src="https://cdn.lr-ingest.com/LogRocket.min.js"></script>
<script>window.LogRocket && window.LogRocket.init('app/id');</script>
React Shepherd stands out as a site tour library due to its elegant UI and out-of-the-box, easy-to-use React Context implementation.
Cookies are crucial to web development. This article will explore how to handle cookies in your Next.js applications.
Use the Tempo library to format dates and times in JavaScript while accounting for time zones, daylight saying time, and date internationalization.
This guide explores how to use the cron package in Deno, `Deno.cron`, to handle scheduling tasks with specific commands.
8 Replies to "How to run SQL Server in a Docker container"
SA_PASSWORD: “change_this_password” as shown on the docker-compose.yml file did not work for me and container logs had the following message:
2019-07-17 14:19:20.63 spid31s ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols..
2019-07-17 14:19:20.63 spid31s An error occurred during server setup. See previous errors for more information.
It seems that you need to change your password with something complex that fulfills the given criteria. “AEdCC.b9” should work I guess.
You will have to do as the password says “Change it” to something a bit more secure.
How can I the mentioned .sql file via docker-compose?
Can you tell me which npm package you are using and what version? I tried `npm i -g mssql` but that will not take the arguments you describe, it says no such file -u.
where does the Data and log files stored? If it is inside the container by default, how can we map to the host MacOs volume?
How do we backup the database to outside of the container?
good question, did you find it out?
Just add a volume:
“`
volumes:
– ~/apps/mssql/data:/var/lib/mssqlql/data
“`