Install SQL Server on Windows 10 using Docker

Starting with SQL Server 2017, Microsoft announced you can run SQL in Docker containers. This is great news for DBA’s because we tend to run multiple installations of SQL Server locally and with Docker we can spin up (or remove) a SQL instance fairly quickly.

I’m not going to go into what Docker is because there’s an entire website that goes into much more detail than I ever could. This post is going to focus on a simple SQL installation on Windows 10 using Docker. Let ‘s gooooooo!

First, let’s download and install the latest version of Docker. To find the latest version, open an internet browser and navigate to https://docs.docker.com/docker-for-windows/install/. From here, click “Download from Docker Hub”

You will need to create a docker ID and password if you don’t have one. Once you have this info let’s login.

Next, let’s click “Get started with Docker Desktop” from the home page and “Download Docker Desktop for Windows”

After the download is complete, go ahead and start the install. On the Configuration screen, I’m going to Use Windows containers instead of Linux. Click OK.

Docker Desktop is installing:

 After installation succeeds, you must log out so make sure to save everything!

Upon logging back in, you will be greeted with this message, Hit OK and reboot.

After reboot, let’s open Powershell (as administrator) and issue the following statement. This will show you that Docker is installed properly and it will give you the version we are running.

Docker Version

Next, let’s do a search for a SQL Server image. Back in Docker Hub, in the search box, let’s type SQL Server and click on Microsoft SQL Server:

This page will give you all sorts of valuable information.

You can also use the following PowerShell statement to pull SQL Server images:

Docker search Microsoft

Again, from Powershell, let’s download the Microsoft SQL Server Developer Edition images. To do this use the following:

Docker pull microsoft/mssql-server-windows-developer

This will take a few minutes so grab some coffee…..or a beer.

Once the download is complete you can run the following statement to see the images downloaded:

Docker images

OK, we have Docker installed and our SQL Server image downloaded. Now the fun part….install SQL Server! In PowerShell, let’s run the following:

Docker run –name DockerSQL -d -p 14331:1433 -e sa_password= St0ngP@SSw0rd! -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

  • –name – this is the name of the container you are creating
  • -d – Runs the container in the background otherwise the container will take over your cmd when it starts
  • – p port:port – Maps a TCP port on the host environment (first value) with a TCP port in the container (second value). In this example, SQL Server is listening on TCP 14331 in the container and this is exposed to the port, 1433, on the host. I’m using 14331 because I’ll create multiple containers and each container needs a different port number.
  • -e – Creates environment variables under the container execution runtime, for example, sa password and End User License Agreement (EULA)
  • sa_password – Create sa password for SQL instance (Hint, don’t use a $)
  • ACCEPT_EULA – Yes

It will take a few minutes to finish the install, but it’s much shorter than a regular SQL install. Once finished, run the following to get the IP address of the container (make sure to use your container name after docker inspect):

$Container_1 = docker inspect DockerSQL |
ConvertFrom-Json |
select -ExpandProperty SyncRoot |
select -expand netWorkSettings |
select -ExpandProperty Networks |
Select -ExpandProperty nat |
Select IPAddress
$Container_1

As you can see, the IP for my container is 172.31.29.174. If I open SQL Server Management Studio and connect to this IP using the sa account and password I created I can connect successfully:

If you have any issues connecting try using the following docker statement to view the logs:

Docker container logs –details DockerSQL

To remove the container, simply stop the container

Docker stop DockerSQL

And remove.

Docker rm DockerSQL

Community driven Enhancements in SQL Server 2017

While SQL Server 2016 runs faster, SQL Server 2017 promises to run faster and empower customers to run smarter with intelligent database features like the ability to run advanced analytics using Python in a parallelized and highly scalable way, the ability to store and analyze graph data, adaptive query processing and resumable online indexing allowing customers to deploy it on platform of their choice (Windows or Linux). SQL Server is one of the most popular DBMS among SQL Community and is a preferred choice of RDBMS among customers and ISVs owing to its strong community support. In SQL Server 2017 CTP 2.0, we have released several customer delighters and community driven enhancements based on the learnings and feedback from customers and community from in-market releases of SQL Server.

Smart Differential Backup – A new column modified_extent_page_count is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database. The new column modified_extent_page_count will allow DBAs, SQL Community and backup ISVs to build smart backup solution which performs differential backup if percentage changed pages in the database is below a threshold (say 70-80%) else perform full database backup. With large number of changes in the database, cost and time to complete differential backup is similar to that of full database backup so there is no real benefit of taking differential backup in this case but it can rather increase the restore time of database. By adding this intelligence to the backup solutions, customers can now save on restore and recovery time while using differential backups.

Consider a scenario where you previously had a backup plan to take full database backup on weekends and differential backup daily. In this case, if the database is down on Friday, you will need to restore full db backup from Sunday, differential backups from Thursday and then T-log backups from Friday. By leveraging modified_extent_page_count in your backup solution, you can now take full database backup on Sunday and lets say by Wednesday, if 90% of pages have changed, the backup solution should take full database backup rather than differential backup. Now, if the database goes down on Friday, you can restore the full db backup from Wednesday, small differential backup from Thursday and T-log backups from Friday to restore and recover the database quickly compared to the previous scenario. This feature was requested by customers and community in connect item 511305.

Click here to view the rest of this post.