SQL Server is one of the leading Database Management systems in the market. It has established itself as an easy-to-use, reliable and efficient system. It provides a wide range of functionality as well. It works on SQL and like any other DBMS, under a wide range of scenarios, the system doesn’t provide the level of efficiency you require.
Docker is a comprehensive suite of Platform as a Service (PaaS) products that leverage OS-level virtualization to provide software in packages also known as Containers.
This blog talks in detail about the steps you can follow to establish and deploy Docker MSSQL Containers for your business requirements seamlessly. It also talks about various salient aspects like ways to check the Docker SQL Version, running and executing multiple SQL Server Docker Containers, and the process you can use to upgrade a SQL Server Docker Container.
Table of Contents
What is Microsoft SQL Server?
Image Source
SQL Server is a relational database system provided by Microsoft. It can hold petabytes of data and have 100s of parallel running cores. It has a comprehensive querying layer that aids in business analysis and tightly integrates with reporting tools like Power BI.
Recent versions have spark support built-in; which means analyzing large amounts of data using Spark SQL without much additional setup needed.
It supports ANSI SQL, the standard SQL (structured query language) language. SQL Server comes with its implementation of the proprietary language called T-SQL (transact SQL). SQL server management studio (SSMS) is the main interface tool, consisting of a client and server.
A client is an application for sending requests to the SQL Server installed on a given machine, whereas a server is capable of processing input data as requested.
SQL Server provides enterprise-grade security and support which makes it a favorite among organizations with strict compliance requirements. This post is about loading data from Google Analytics to SQL Server.
Relational databases store and manage data in a traditional table format. Businesses use relational databases to handle data from their applications and ensure they always have access to critical information.
Key Features of MySQL Server
Here are a few key features of SQL Server:
- The Database Engine: This segment handles data storage, fast exchange processing, and data security.
- The SQL Server: This assistance begins, pauses, stops, and continues the dataset process.
- The Server Agent: It plays out the job of the task scheduler. It tends to be set off by any occasion or according to request.
- The SQL Server Browser: This receives approaching queries and connects with the ideal SQL server instance.
- The SQL Server Full-Text Search: This permits the client to run a full-text search against Character information in SQL Tables.
What is Docker?
Image Source
Docker aims to make software development predictable and efficient. Docker achieves this by taking away mundane, tedious configuration tasks throughout the development lifecycle for easy, fast, and portable application development – Cloud and Desktop.
Docker’s extensive end-to-end platform consists of CLIs, UIs, and APIs along with robust security measures engineered to work together across the entire application delivery lifecycle.
Docker Containers are a standardized unit of software that lets developers isolate their app from the environment, therefore, solving the localized operation problem. Docker has grown to become the go-to standard for sharing and building containerized apps for virtually millions of developers- from the Cloud to Desktops.
With Docker, you can install from a single package to get up and running in no time flat. Therefore, you can test and code locally with Docker while ensuring consistency between production and development.
Key Features of Docker
Here are a few salient features of Docker that allow it to stand out of the crowd:
- Ease of Packaging: Docker allows you to package applications as portable container images to function in any conducive environment consistently from AWS ECS to on-premise Kubernetes, Google GKE, Azure ACI, and so much more.
- Large Pantheon of Images to Choose From: You can use Docker Trusted Content that includes pictures from the Docker Verified Publishers from the Docker Hub Repository along with Docker Official images for your specific business use case.
- Seamless Integrations: With Docker, you can seamlessly integrate with your favorite tools across your development pipeline. Docker works well with all development tools that you leverage including CircleCI, VS Code, and GitHub among others.
- Personalized Developer Access: Docker lets you personalize your developer access to images via roles-based access control. Thus, Docker allows you to extract insights into activity history with the help of Docker Hub Audit Logs.
- Ease of Deployment: Docker lets you deploy your applications in different containers independently and in a vast array of languages as well. Thus, by leveraging Docker you can minimize the risk of conflict between libraries, languages, and frameworks.
What is a Docker Image?
A Docker Image is described as a file that you can leverage to carry out the code within a Docker Container. This read-only template offers a convenient way to package up applications and preconfigured server environments.
You can use them for your private use or share them publicly with other Docker users. Beginners can leverage Docker images to better understand the Docker platform.
A Docker image consists of a collection of files that package together with all the essentials like dependencies, installations, and application code. You can generate a Docker image by using one of the following methods:
- Dockerfile: By developing a plain-text file, also known as a Dockerfile, which provides the specifications for generating a Docker image.
- Interactive: In this method, you can run a container from an existing Docker image, manually changing the container environment through a series of live steps, and saving the resultant state as a new image.
A fully managed No-code Data Pipeline platform like Hevo helps you integrate data from 100+ data sources (including 40+ Free Data Sources) to a destination of your choice like SQL Server in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources provides users with the flexibility to bring in data of different kinds, in a smooth fashion without having to code a single line.
GET STARTED WITH HEVO FOR FREE
Check Out Some of the Cool Features of Hevo:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
- Connectors: Hevo supports 100+ Integrations to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Amazon Redshift, Firebolt, Snowflake Data Warehouses; Databricks, Amazon S3 Data Lakes; MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL databases to name a few.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources, that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Simplify your Data Analysis with Hevo today!
SIGN UP HERE FOR A 14-DAY FREE TRIAL!
Is it Possible to Run SQL Server on Docker?
To make sure that you can run SQL Server on Docker, you need to use SQL Server 2017 or a newer version. Otherwise, you won’t be able to leverage a cross-platform SQL Server. Next, you need to have docker installed with docker-compose included. This is generally handled for you if you leverage Docker Desktop for Mac.
You’ll also need the npm library to update, connect, and query the SQL Server Docker container. You can either install that globally or locally with an npm install mssql command from any terminal.
How to Set up & Deploy SQL Server on Docker?
Here are the steps you can follow to set up and deploy a SQL Server Docker Container seamlessly:
SQL Server Docker Setup: Install Docker on your System
- Step 1: You can follow this link to download Docker to your machine.
Image Source
- Step 2: Next, download the DMG file on your Mac and install the package. It might take Docker some time to get up and running. Once it is installed, you can view the Docker icon being available on the status bar as given below. Click on the icon and select Preferences.
Image Source
- Step 3: In the Preferences section choose Resources. Here, you need to increase the default allocated memory of the Docker Engine to 6 GB for the SQL Server to run smoothly. Allocating 4 GB would work just fine, however, if you are aiming for optimal performance, 6 GB is the way to go.
Image Source
- Step 4: Once you’ve allocated the memory as needed, you can go ahead and click on the Apply & Restart button. This restarts the Docker Engine with the new memory configurations.
SQL Server Docker Setup: Execute and Run Docker
- Step 1: Now that you’ve installed Docker, you can open up the terminal and supply the following command to check if Docker has been successfully installed on your device:
docker -v
Image Source
SQL Server Docker Setup: Pull & Run the Docker Image for SQL Server
- Step 1: As you can see from the figure above, Docker has been installed successfully and you can see the installed version as well. With Docker up and running, the next step is to pull the official SQL Server Docker image from Docker Hub and get down to brass tacks.
- Step 2: Next, you will be creating a directory for this exercise and opening the terminal in that directory. Once in the directory, run the command given below to pull the docker image from the repository to your local device:
docker pull mcr.microsoft.com/mssql/server
Image Source
- Step 3: Once the SQL Server Docker Image has been pulled and extracted, you need to start the SQL Server Docker Container for this image. A Container in Docker is a running instance of the Docker image that can be started by performing the command as follows:
docker run -e ‘ACCEPT_EULA=Y’ -e ‘SA_PASSWORD=Str0ngPa$$w0rd’ -p 1433:1433 -d mcr.microsoft.com/mssql/server
Image Source
- Step 4: As visible from the figure above, on running the command as mentioned in the previous step, an ID is returned. This is the unique ID of the SQL Server Docker Container that is currently running the SQL Server Instance. You can also verify the containers that currently running by leveraging the command as follows:
docker container ls
Image Source
SQL Server Docker Setup: Connect to your SQL Server Docker Instance
- Step 1: Now that your SQL Server Docker Container is up and running, you can connect to the SQL Server instance through Azure Data Studio. Open up Azure Data Studio and use the credentials to access it:
Image Source
- Step 2: As soon as it gets connected, you can look at the version information of the SQL Server instance as follows:
Image Source
- Step 3: Now that you’ve connected to Azure Data Studio, you can run SQL commands with it. But first, you need to check if all the databases are present there by default. You can run the SQL command given below to check all the installed databases within a SQL Server instance:
SELECT * FROM sys.databases;
Image Source
- Step 4: As evident from the figure above, you have all the four default databases installed and ready to use. On top of this, since the SQL Server instance is now running inside a SQL Server Docker Container, it might be pivotal to check the location of the data and the log files for administrative purposes. You can use the following command to list the path of the log files and the data for the databases that are installed in the instance:
SELECT [name], [physical_name] FROM sys.masterfiles;
Image Source
- Step 5: As you can see from the above image, you have the entire path of the data and the log files which can serve useful for later administration. You can even create new databases here and use this as an equivalent SQL Server instance on Windows.
How to Check the SQL Server Version for your Docker Container?
If you wish to know the version of SQL Server within a running SQL Server Docker container, you can run the following command to showcase it.
For the following command, you can simply replace the <Container name or ID> with the name of the target container name or ID. You also need to replace <YourStrong!Password> with the SQL Server password for the SA login.
docker exec -it <Container ID or name> /opt/mssql-tools/bin/sqlcmd `
-S localhost -U SA -P "<YourStrong!Passw0rd>" `
-Q "SELECT @@VERSION"
You can even identify the SQL Server version and Build Number for a target SQL Server Docker Container image. The command mentioned below will show the SQL Server version and build information for the mcr.microsoft.com/mssql/server:2017-latest image.
It can execute this by running a new container with an environment variable PAL_PROGRAM_INFO=1. As a result, the container instantaneously exits and gets removed by the docker rm command.
docker run -e PAL_PROGRAM_INFO=1 --name sqlver `
-ti mcr.microsoft.com/mssql/server:2019-latest; `
docker rm sqlver
The aforementioned commands depict the version information similar to the following output:
sqlservr
Version 15.0.4063.15
Build ID 8a3bb4cca325e1d0b3071b3a193f6a1d74b440fbd95d2fb18881651a5b9ec8e8
Build Type release
Git Version 0335c462
Built at Fri Aug 28 04:50:27 GMT 2020
PAL
Build ID cc5ceea1b3d294f7d0166f99932f98c7eacfaaa81bcd7cf23c6a89f785829b63
Build Type release
Git Version ae9d66dff
Built at Fri Aug 28 04:46:48 GMT 2020
Packages
system.security 6.2.9200.10,unset,
system.certificates 6.2.9200.10,unset,
secforwarderxplat 15.0.4063.15
sqlservr 15.0.4063.15
system.common 10.0.17134.1246.202005133
system.netfx 4.7.2.461814
system 6.2.9200.10,unset,
sqlagent 15.0.4063.15
How to Run & Execute Multiple SQL Server Containers?
With Docker, you can also run multiple SQL Server Containers on the same host machine. You can use this approach for situations that need multiple instances of SQL Server on the same host. Every container needs to expose itself to a different port.
In the example below, you will be creating two SQL Server 2019 Containers and mapping them to ports 1402 and 1401 on the host machine:
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -p 1401:1433 -d mcr.microsoft.com/mssql/server:2019-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -p 1402:1433 -d mcr.microsoft.com/mssql/server:2019-latest
Due to the aforementioned code snippet, there are two instances of SQL Server running in different containers. Clients can easily connect to each instance by leveraging the port number and the IP address of the Docker host container:
sqlcmd -S 10.3.2.4,1401 -U SA -P "<YourPassword>"
sqlcmd -S 10.3.2.4,1402 -U SA -P "<YourPassword>"
Ways to Run a Particular Container Image for SQL Server
Here are a couple of ways that you can use to run an MSSQL Docker Image:
SQL Server Docker Container Image: Running Production Container Images
Here are a few considerations you need to keep in mind before you go about running Production Container Images:
- You can only leverage SQL Server in a production environment if you have a valid license. You can access SQL Server Standard and Enterprise Edition licenses through Microsoft Volume Licensing.
- You can configure the Developer Container image to run the production editions. You can use the following steps to run the production editions:
- Step 1: Review the requirements and run procedures within the quickstart. You also need to specify your production edition with the MSSQL_PID environment variable. Here’s the example that depicts how you can run the latest SQL Server 2017 Container image for the Enterprise Edition:
docker run --name sqlenterprise `
-e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong!Passw0rd>" `
-e "MSSQL_PID=Enterprise" -p 1433:1433 `
-d "mcr.microsoft.com/mssql/server:2019-latest"
SQL Server Docker Container Image: Running RHEL-based Container Images
The documentation for SQL Server Linux Container images points to Ubuntu-based Containers. Starting with SQL Server 2019, you can leverage the Containers based on Red Hat Enterprise Linux (RHEL). An example for the RHEL image will look something like mcr.microsoft.com/mssql/rhel/server:2019-CU1-rhel-8. For instance, the command mentioned below pulls the Cumulative Update 1 for SQL Server 2019 Container that leverages RHEL 8:
docker pull mcr.microsoft.com/mssql/rhel/server:2019-CU1-rhel-8
Bonus: How to Upgrade SQL Server in your Docker Container?
If you want to upgrade SQL Server within your Docker Container, you first need to identify the tag for the release for your upgrade. You can then pull this version from the registry with the docker pull command:
docker pull mcr.microsoft.com/mssql/server:<image_tag>
This updates the SQL Server image for any containers that you create subsequently, however, it does not update SQL Server in any running containers. To carry this out, you need to create a new container with the latest SQL Server Container image and move your data to that new container:
- Step 1: You need to make sure that you are using one of the Data Persistence techniques for your existing SQL Server Container. This allows you to start a new container with the same data.
- Step 2: Next, you need to stop the SQL Server Container with the docker stop command.
- Step 3: After that, you need to create a new SQL Server Container with docker run and mention either a data volume container or a mapped host directory. You also need to make sure that you use the specific tag for your SQL Server upgrade. The new container can now use a new version of SQL Server alongside your existing SQL Server data.
- Step 4: Next, you need to verify your data and databases within the new container.
- Step 5: For the final step, you can remove the old container by using docker rm. However, this step is optional.
Conclusion
This blog talks about the different steps you can follow to set up a SQL Server Docker Container seamlessly. It also gives a brief introduction to SQL Server and Docker before diving into the setup process.
Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications into your Data Warehouse or a tool like SQL Server to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.
VISIT OUR WEBSITE TO EXPLORE HEVO
Want to take Hevo for a spin?
SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.