How to run SQL Server in a Docker container
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 new open source focus, those days feel like
a distant memory.
With the ability to develop using dotnet core, the world of
cross-platform .NET has opened up. You can now develop applications or services
that can run on Windows, Mac, and even Linux.
But what about database technologies?
Many .NET-focused developers tend to lean towards 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.
Screeching Halt
Actually, that problem has been solved as well. We can use
Microsoft SQL Server across multiple platforms by leveraging Docker. Let’s take
a look at how we can launch a SQL Server database inside of a Docker container
for our cross-platform database needs.
LogRocket Free Trial Banner
Prerequisites
To truly test this out on your own, there are some
prerequisites you should be set up with.
This is a big one: cross-platform SQL Server is only
available for SQL Server 2017 and above, so make sure that version is
compatible with whatever your building.
You must have docker installed with docker-compose included.
This is usually all handled for you if you use Docker Desktop for Mac.
In this post, I am going to 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
a SQL Server database for cross-platform development.
Launching our SQL Server container
To launch our SQL Server container, we are going to first
create a docker-compose.yml file in the root of our project. Inside of that
file, we are going to define a sql-server-db resource that uses the SQL Server
2017 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.
Conclusion
Microsoft has been moving more and more of their 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 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.[Source]-https://blog.logrocket.com/how-to-run-sql-server-in-a-docker-container/
Beginners
& Advanced level Docker Container Training in Mumbai. Asterix Solution's 25 Hour Docker Training gives
broad hands-on practicals.
Comments
Post a Comment