Creating a custom SQL Server Docker Image on top of the Official Image
Tags: docker, sqlserver, programming, container
Published: 2021-06-09 06:48:48

Hello people, so today let's discuss how we can create our own custom SQL Server image on top of the official image. 

This can be really useful in multiple scenarios like when we have a new team member joining our team. Instead of giving them a fresh instance of SQL Server we can have an image of the initial setup and they can just pull it and be ready to work on it

## Pre-requisite

* Docker Desktop up and running on the machine. Can be download from here https://docs.docker.com/v17.09/docker-for-windows/install/
* Account on Docker Hub so that we can publish and then pull our custom Sql Server docker image
* Running instance of a fresh SQL Server container from the official image available on docker hub mcr.microsoft.com/mssql/server:2017-latest.

```
docker run -e "ACCEPT_EULA=Y" "SA_PASSWORD=MYPASSWORD123" -p 1433:1433 --name MyContainerName -d mcr.microsoft.com/mssql/server:2017-latest
```

## Setting up the SQL Server 🔥

* Once we have the server up and running, login to the SQL Server using SQL Server Management studio with the IP address of the host
* Username would be **SU** and password is **MYPASSWORD123** as used in the command above to run the container
* Next we can setup our server like the database, tables manually or using any backup file etc
* Now we have our database in place and we want to create an image of this setup so as to when next time someone pulls the image they don't have to manually import the database anymore

## Create Custom Docker Image

* Firstly, stop the running container using the command

```
docker stop MyContainerName
```

* Next we push our changes on to the container so that we can build an image of it

```
docker commit MyContainerName
```

* Then copy the image of our specific container from the list using the command 

```
Docker images
```

* The newly created image does not have a repository and tag. Execute the following command to tag the image

```
docker tag <imageID> <docker-hub-username>/<docker-hub-repository-name>:<tag-name>
```

For example: docker tag a82e969d1395 rajatsrivas/ myownsql:sqlCustomImage

* Now our image is built and we can create a container using the image 

```
docker run -p 1433:1433 --name sqlCustomImage -d rajatsrivas/ myownsql
```

* If you are logged into docker hub on local Docker Desktop this step will be skipped else login using the command prompt

``` docker login -username=rajatsrivas ```

* Enter the password into the next line and finally push the image to the docker hub repository

``` docker push rajatsrivas/myownsql ```

* Go to https://hub.docker.com/ 
* We should have the image which we pushed to the docker hub

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3t76806r6iqjwyg0ggi2.png)

### Pull and Run our Custom Image 🏃‍♂️

* Pull the image on to any machine using the command 

``` docker pull rajatsrivas/myownsql:latest ```


* Run the container and access the server on the SSMS. The server should have the database which was imported and setup in the earlier steps

```
docker run -p 1433:1433 --name <container-name> -d rajatsrivas/myownsql:latest

```

## Conclusion

So there it is we have implemented our custom image on top of an official docker image available. 


This is quite a small step in onboarding but one can leverage similar implementations for setting up sandbox environments very quickly and efficiently.

Hope this was useful. Keep learning keep building