Running a SQL Server Instance in Docker

I've found it very convenient to have a local instance of SQL Server. I use it for:

• quick tests
• performance testing
• demonstrations
• development
• testing
• work around limitations of a cloud instance

This has become really easy with containers. Even if you have the ability to install SQL Server natively on the host (Windows or Linux), it is much quicker and easier to use containers. 

This means that you can run an instance of SQL Server on Windows, Linux, and Mac. 

The first time I did this, I followed Bob Ward's Take the SQL Server Mac challenge, and had a SQL instance running on my Macbook, while in the audience listening to Bob's presentation. Yep, under 5 minutes.

So here is my pared-down How-To. I've tried to do a minimal setup that should work on Win, Linux, Mac, with very little adjustment.

Here's the overview:

1. Download, install, and run Docker
2. Get to an (elevated?) command prompt
3. Run a long-ish docker command
4. Verify via Docker
5. Connect from Host with client 
6. Shut it down

1. Docker Installation

Head over to Docker and download Docker Desktop. I recommend the Stable version for whichever platform you are on:

• Docker for Windows Stable Version
• Docker for Mac Stable Version
• Docker for various Linux flavors

Follow the installation instructions for your platform. This varies slightly for the platform you are on. If you can install a program on your machine, you can do this.

2. Command Prompt

For Mac, use the Terminal Application to get a command line. Here's how.

For Windows, type cmd in the search area, and choose "Run As Administrator". Here's how. Administrator privileges are not required for everything, but use it this first time to make sure you can get it working.

3. Run Docker Command

This is a long-ish command, but all the pieces are necessary. There are various ways to do this, but i'm using a "quick" method found in Microsoft's documentation. This method both pulls the SQL Server 2019 container image, and runs it with the correct parameters. Here's the command that I used:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=123ABCabc" -p 1401:1433 --name sqlsrvr1 -h -sqlsrvr1 -d mcr.microsoft.com/mssql/server:2019-latest

Here's the code again, broken into multiple lines. Note the Windows continuation character ^ (caret) at the end of each line:

docker run -e "ACCEPT_EULA=Y" ^
  -e "SA_PASSWORD=123ABCabc" ^
  -p 1401:1433 ^
  --name sqlsrvr1 ^
  -h sqlsrvr1 ^
  -d ^
  mcr.microsoft.com/mssql/server:2019-latest

Either of the above should work via cut-and-paste, but I find it easier to read and visually verify the one-parameter-per-line version.

The basic command (without options, which won't work) is:

docker run mcr.microsoft.com/mssql/server:2019-latest

This creates a process that runs the image mcr.microsoft.com/mssql/server:2019-latest. If that image happens to be on the local machine, great. If not, it will download the image from mcr.microsoft.com.

Note the "tag" at the end of the image url: 2019-latest. Microsoft releases many container images from which you can choose. Find them all on DockerHub. With this tag, are basically saying, get the latest stable release of SQL Server 2019. 

That basic command will not create a usable sql server container. You need some additional pieces:

• -e "ACCEPT_EULA=Y"
• passes "Y" to SQL Server, indicating acceptance of the End User License Agreement (EULA)
• -e "SA_PASSWORD=123ABCabc"
• passes the "sa" user password to SQL Server
• you will log in to this server using SQL Authentication, with "sa" as the username, and "123ABCabc" as the password
• I'm using "123ABCabc" because it satisfies the password policy requirements (9 chars, upper and lower, digits"
• "sa" is short for system administrator, and is basically the root user on the server instance that you are running
• -p 1401:1433
• maps the container's port 1433 to your machine's port 1401
• SQL Server "listens" for connections on this port by default
• So if you connect to your host machine's port 1401, you will be connected to the container's port 1433
• in my situation, I am running a SQL instance on the host that is already using the default port 1433.
• --name sqlsrvr1
• this gives the container your desired name, rather than a docker-generated name
• when issuing commands on the host, you can refer to the container by this name
• -h sqlsrvr1
• this sets the HOSTNAME environment variable inside the container, rather than the docker-generated name
• when issuing commands inside the container, you can refer the system inside the container by this name
• -d
• this runs the container "detached"
• this means that the container will run in the background rather than the foreground

4. Verify via Docker 

If you've run the command in the previous section, you should have a running container based on the mcr.microsoft.com/mssql/server:2019-latest image. Run this at the command line to see it:

docker ps

This command gives a list of the running containers. You should see results something like this:

CONTAINER ID   IMAGE                                      COMMAND                CREATED        STATUS        PORTS                     NAMES
d325650811b1   mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 43 minutes ago Up 43 minutes 0.0.0.0:1401->1433/tcp sqlsrvr1

The results indicate that the container is running, with the proper name, and listening on the proper port

Verify via Connection

Now fire up a SQL client to connect to the container, and use it. I'm using Azure Data Studio (ADS), which will run on Windows, MacOS, and Linux. Here's the "new connection" dialog in ADS

Just to be clear:

• ADS is running on the host
• it is connecting to the host IP address (localhost)
• it is connecting to the host Port 1401
• Docker has mapped host port 1401 to the container's port 1433, which is the default port for SQL Server
• So ADS will be connected to the container's port 1433
• Note the Server text box has "url, port" - there's a comma between the url and the port

You should see that your container is shown in the Server Explorer on the left-hand side of the ADS window. 

6. Shut it down

You can, of course, leave this container running in the background and use it whenever you need it. But what if you want to shut it down? Or entirely get rid of the container? Here's how to do it:

docker stop sqlsrvr1

This stops the container. It will no longer be running in the background. The container is still there, and can be restarted like this:

docker start sqlsrvr1

To entirely remove the container from the host machine (it cannot be started, and will no longer take up space on your host's drive), you would issue this command on the stopped container:

docker rm sqlsrvr1

Summary

Docker can seem overwhelming at first, especially if you are not comfortable working at the command line. But if you are looking to run a local instance of SQL Server, running a Docker container is the fastest path.

1 Petabyte in a Single Rack

https://venturebeat.com/2019/10/30/meet-edsff-1pb-of-flash-storage-in-a-single-rack/

Docker, Kubernetes, Microservices, and Domain Driven Design

I'm attending an excellent 2-day workshop ASP.NET Core, Docker on Azure and Azure Kubernetes Service - What You Need to Know Part 1 and Part 2, with
Michele Bustamente of Solliance at DevIntersection right now.

It's a deep dive and much of the hands-on content is going over my head. But I am really appreciating the context, motivation, and use cases described. If you get a chance to hear Michele Bustamente speak, take it.

I am no expert on these topics, and much of it is a real departure from common design patterns back when I was a System Architect.

Domain Driven Design (DDD) and Microservices seem to fit well when there is a need to scale out and provide capacity elasticity. So it ties with containers and orchestration. Here are some succinct statements that I wish someone had written down for me.

Glossary

• Domain Driven Design - a design approach for software
• Microservices - an implementation approach for software
• Containers - lightweight virtualized application environments
• implied is elasticity - add or remove containers as load changes
• Docker - a container framework
• Orchestration - management of containers, resources, and their relationships through elastic operations, i.e. adding or removing containers or resources
• Kubernetes - a framework for orchestration
• .NET Core - Microsoft's implementation of .NET that is containerizable - free, open-source, and cross-platform

Domain Driven Design

• DDD carves up functionalities into "domains"
• functionalities within a domain are highly interrelated
• functionalities across domains are less related
• each domain is designed and developed from end-to-end: Persistence to User Interface
• DDD teams are cross functional: organizational domain expert, developer, architect, data engineer, devops, etc.
• The goal of a domain team is to create a very clean, tight set of services within the domain
• meeting this goal is easier due to the clear, focused scope of the domain

Although there is nothing in DDD that requires use of microservices, or any particular architecture, it naturally aligns with microservices.

Microservices

• focused, lightweight applications that provide functionality over a network
• agnostic of any particular technology, language or framework
• align well with DDD
• can be (easily?) containerized
• a domain's functionality could be implemented as one or more microservices
• microservices are meant to be independent, and decoupled from each other
• however, there may be shared resources, such as a database

Containers & Docker

• containers are lightweight virtualized operating system environments for applications
• lightweight is achieved through
• limited feature set (thus .Net Core rather than .Net)
• shared image, i.e., operating system kernel, libraries and other dependencies
• lightweight is important for elasticity - cheap to allocate and reclaim
• Docker is a commonly used container framework

Orchestration & Kubernetes

• Orchestration tasks
• provisioning of new containers 
• configuration of containers
• locations of resources, such as storage
• credentials for resources
• common state across containers
• load balancing
• logging of orchestration activities, errors
• decommissioning of containers, reclamation of resources

Related Concepts & Requirements

• Agile 
• this mentality seems to be a pre-requisite
• Continuous Integration / Continuous Deployment
• all the basics, but also the containers and orchestration
• Eventual Consistency
• relates to domains sharing resources across domains
• one domain/microservice (A) might have high levels of writes, while another (B) might have high levels of reads. This could be architected as separate data stores, with A writes going to a write-optimized store, and changes being replicated (eventually) on B's read optimized store

Summary

The current use case is for high volume systems that must be elastic. But the idea of smaller, focused, agile, domain driven teams is compelling. With well-scoped smaller domains, it should easier to achieve smooth CI/CD, embrace re-factoring when necessary, stay focused on domain issues, and deliver high-quality software. As the tooling gets better, the overhead involved in containers, orchestration, and distributed microservice coordination should make this even more compelling.

The SwitchOffset Function

-- Database by Doug
-- Douglas Kline
-- 1/30/2019
-- the SwitchOffset function

-- how to use switchoffset 
-- function available beginning SQL 2008

-- see "Time Zones and DATETIMEOFFSET" video

-- an update to the previous video
-- thanks to a viewer who pointed this function out to me
-- you know who you are!

SELECT GETDATE() AS [now, somewhere]

-- my time zone is EST -05:00
-- the server is in the Azure US east data center (EST)

-- note that the GETDATE() returns the time GMT, i.e. -00:00
-- the returned time is 5 hours in the future (based on EST)

-- also note that GETDATE() does not contain the time zone,
-- it returns a datetime, which does not contain time zone information

SELECT SQL_VARIANT_PROPERTY(GETDATE(), 'BaseType')

-- you can the server datetime with time zone like this:

SELECT SYSDATETIMEOFFSET(), 
       SQL_VARIANT_PROPERTY(SYSDATETIMEOFFSET(), 'BaseType')

-- this result proves that Azure SQL returns UTC 00:00

-- my current database server happens to be
-- in the eastern time zone of the US, which is -05:00 UTC

-- so what is the actual time, in EST?

-- observe the difference between the following values

SELECT GETDATE()                                  AS [Azure datetime GMT],
       CAST (GETDATE() AS DATETIMEOFFSET)         AS [converted Azure datetime GMT],
       TODATETIMEOFFSET(GETDATE(), '-05:00')      AS [todatetimeoffset result EST], -- but note no hour change
       SYSDATETIMEOFFSET()                        AS [sysdatetimeoffset EST],   
       SWITCHOFFSET(SYSDATETIMEOFFSET(),'-05:00') AS [switchoffset] -- this is the right one

-- note that GETDATE() is not as accurate  
-- for a couple of reasons
-- fewer decimal points

-- but also 
-- datetimes' one-thousandths place is always 0, 3, or 7   
-- from doc'n "Rounded to increments of .000, .003, or .007 seconds"

-- so, before SWITCHOFFSET existed, ...

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),'-05:00')                AS [EST the easy way],
       TODATETIMEOFFSET(DATEADD(HOUR, -5, SYSDATETIMEOFFSET()), '-05:00')  AS [EST the hard way]

-- so, thinking of a DATETIMEOFFSET data type as a complex object
-- with many different parts: year, month, day, hour, time zone, etc.
-- it looks like SWITCHOFFSET changes two things: time zone and hour

-- but let's say that my source datetimeoffset 
--   is near a time part boundary, 
--   for example, the end of the year

DECLARE @NewYearsEveEST AS DATETIMEOFFSET
DECLARE @NewYearsEveGMT AS DATETIMEOFFSET

SET  @NewYearsEveEST = DATETIMEOFFSETFROMPARTS(2019,12,31,23,50,0,0,-5,0,7)
SET  @NewYearsEveGMT = SWITCHOFFSET(@NewYearsEveEST,'+00:00')

SELECT   @NewYearsEveEST AS [NYEveEST], 
         @NewYearsEveGMT AS [NYEveGMT]

-- note that the year, month, day, hour, and time zone changed

-- in summary
-- SWITCHOFFSET is really helpful to have
-- simpler code, likely more reliable
-- use SYSDATETIMEOFFSET to get max precision w/Offset

-- Database by Doug
-- Douglas Kline
-- 1/30/2019
-- the SwitchOffset function