Problem
Since I have being developing I have always used a local sql server instance to create and restore databases for development. Having a local instance is great as you have full control over the sql server.
This leads to some issues when it comes to development however these being:
We have a full sql server on our development machines, if your not familiar with sql server and configuration you might give it too much power and it will use up all the resources on your machine.
Restoring large databases can take some time. On a project I work on regularly I tend to need to setup and restore a database if switching branches, etc. This process each time I restore takes anywhere from 5-10 mins, do this 4-6 times a day and you are losing 20-60 mins a day.
The issue I was having is that the scripts I was running required me to have specific database names and every time this ran it would delete and re-create the database.
Solution
What I would like is the ability to easily setup a new instance depending upon the branch I am working upon and easily discard when done.
This lead me to investigate using docker to create an image of the server and then depending on how I wished to use it, I could spin up a new server almost instantly.
The steps I needed to perform in order to achieve this are as follows:
Create a base container of the sql server.
Connect to the server and restore the database.
Create an image from this container.
Creating a base container
There are lost out tutorials out there that will help with getting the base container setup. To create a pretty standard sql server instance you can run the following (Please note to change the password):
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Password123" --name "mssql-server" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
This docker command will create a base container using the sql server image from docker hub and configure the sa password. It will also set the sql server port to 1433.
The command can take some time to run the first time as it needs to download the image before running the container.
Connecting to the sever and restoring the database
Once it completes you should have a sql server container up and running in docker. You can try connecting to the server on 'localhost' with the sa password details.
NOTE: If you already have a sql server instance running on your development machine you will first need to stop the instance to use 'localhost'. Open 'services' and stop the 'sql server'.
Once connected you can restore a bacpac to the server.
Create and image from the container.
Having followed the above you should now have a sql server instance running in a docker container with your databases configured.
As you can see if we where to even do this each time we wanted a new instance it would take some time.
To solve this we can capture an image of the container and tag it so that we can reuse the image as many times as we wish.
To create an image of the container run
docker commit new-container new-image:latest
To run a new instance we can now run
docker run --name "new-server" -p 1433:1433 -d new-image:latest
Hopefully we now have 2 instances running of the same sql instance.
You will need to stop 1 of the containers to connect using localhost. You can introduce a network and assign a specific ip address to the container if you wish to run multiple container at once. As I am working on a branch at a time usually this was not a requirement.
This has helped me with improving my development process. However I also see this being useful for creating demo environments.
Optimizely comes with the built in command you can run which is 'Initialize-EPiDatabase' you can use this to setup a fresh Optimizely database and then run 'Update-EPiDatabase' to ensure it is up-to-date.
Next steps could also be setup up a private container registry using azure container registry and then sharing you images with your team so they too can benefit from your images, or maybe even they could implement a 1 click deploy to setup a demo / training site to use with customers.
Hopefully this has shown you how easy it is to get started using docker containers in development.
If you have any recommendations for improvement please drop a comment below, I am just dipping my toes in the water so any info people can provide it much appreciated.
Top comments (0)