Tag Archives: SQL2017

SQL Server 2017 in a Docker Container running on a Mac – Tips and Tricks

Posted on by

I started using a MacBook Pro at work, and the very first thing I did was configure SQL Server 2017 running on a docker container, and run it locally within the Mac. In this post I will discuss some tips and tricks which will come handy if you are interested to pursue this route.

Tip 1 : How do I install SQL Server on a Mac?

You can follow the below steps to get started.

sudo docker pull microsoft/mssql-server-linux:2017-latest
  • Docker run
sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<ReallyStrongPassword>' \
 -p 1401:1433 --name containername
 -d microsoft/mssql-server-linux:2017-latest

Tip 2 : How to connect to the SQL instance running on a container via SQL Operations Studio?

Tip 3 : How to view the running containers?

Run docker ps

docker ps

Tip 4 : How to stop the running container?

docker stop <container_id>

Tip 5 : How to view the details of containers which are in stopped state? 

docker ps -a

Tip 6 : How to connect to the SQL instance running on a container via command line?

Run the below sqlcmd command –

sqlcmd -S YourIP,Port -U SA -P '<ReallyStrongPassword>'

You can issue regular T-SQL commands after you establish the connection.

Tip 7 : How to persist data ?

If you remove the container, the entire database which you might have configured is lost. To avoid this, you can create a data volume container. Below is the command to create container with a data volume with name sqlvolume –

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=ReallyStrongPassword' -p 1433:1433 --name containername 
-v sqlvolume:/var/opt/mssql -d microsoft/mssql-server-linux:2017-latest

The databases which you create will now be persisted until you delete the volume.

Tip 8 : How to share the data volume container with multiple containers ?

If you stop an existing container which is using the data volume,then you can create another container with the same data volume name and databases which were hosted under the data volume will reflect under the new container.

Conclusion:

The ability to spin up and spin down containers and multiple containers is changing the way I test various versions of SQL Server/Databases and its super flexible when it comes to a CI/CD pipeline.

Thanks for reading, and keep watching this space for more.

SQL Server 2017 running on Windows Containers!

Posted on by

One of the questions which I normally get all the time whenever I talk about SQL Server and Docker is – Why you want to run SQL Server on containers?

Here is what Microsoft has to say:

“Windows Containers are an isolated, resource controlled, and portable operating environment. An application inside a container can run without affecting the rest of the system and vice versa. This isolation makes SQL Server in a Windows Container ideal for Rapid Test Deployment scenarios as well as Continuous Integration Processes.”

Recently I had a chance to invest sometime configuring SQL Server 2017(CTP 2) on Windows Containers, and this post will give you the step by step instructions on how to configure this.

Step 1 –

Choose Windows Server 2016 for running containers.

All my testing was done in AWS, and I picked the below AMI- Microsoft Windows Server 2016 Base with Containers

Step 2 – 

Install Docker. Run the below PS commands –

Install-Module -Name DockerMsftProvider -Repository PSGallery -Force

Install-Package -Name docker -ProviderName DockerMsftProvider
Start-Service Docker

Step 3 – 

Update Windows Patches.

Step 4 – 

Do docker pull to get the Official images for Microsoft SQL Server for Windows Containers.

docker pull microsoft/mssql-server-windows

Step 5 – 

Run the container.

docker run -d -p 1433:1433 -e sa_password=<Your strong SA Password> -e ACCEPT_EULA=Y microsoft/mssql-server-windows

Step 6 – 

Run docker ps and note down the container_ID.

Run docker inspect and note down the IP of the container.

docker inspect --format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' <Container_ID>

Step 7 – 

Connect to the SQL Server(Instance) outside the container via SQL Server Management Studio. Use the IP to connect.

*Click on the image below for better resolution.

 

Conclusion :

That’s pretty much it. Within 7 steps you are running SQL Server 2017 on Windows Containers. Its super lightweight and easy to spin up. Perfect fit for a CI pipeline.

Thanks for reading, and keep watching this space for more(container goodness!).