Background
Historically not only installing SQL servers, but most of the software had difficulties. The container technology such as Docker magically solved this by packaging software as installed VM like images called docker images. It is easy as downloading the image and start using the software.
There are a lot of ways to get it working for us if we want to get SQL instance up and running using containers.
Why SQL Server from a container?
Let us see the benefits of running from containers
Running with minimal efforts
No need to spend time installing and configuring. Below single command will get us started provided the Docker is preinstalled.
This is really useful if we just need to test something out.
If there is already a SQL Server running on the host machine there will be port conflict. We can connect to the SQL instance from SSMS using the localhost as the connection string and check the version. Use the below command for getting the version.
SELECT @@version
Outputs
Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64) Oct 28 2019 19:56:59 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS) <X64>
As shown its shows the Edition as Developer. This is the default value. Also runs on top of the Ubuntu 16.04.6 operating system. Meaning the container uses that OS image, not our host machine.
Running Enterprise version
There may be some testing we need to do using the Enterprise version. Below command runs SQL server Enterprise edition.
No reinstallation. Just a flag to the SQL Instance. Now check the version using the same command after connecting to the instance.
SELECT @@version
Outputs
Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64) Oct 28 2019 19:56:59 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS) <X64>
Bonus point - Check the machine name. It will show the container id as the machine name.
SELECT
HOST_NAME() as HostName,
SERVERPROPERTY('MachineName') as ServerMachineName
Upgrade
When we run SQL Server from containers the upgrade is very easy. Just change the image tag. We get the upgrade done. Compare that with the standalone installation. Also, it's very easy to revert in case something is not working. Just change the tag back.
Persistence
By default, the persistence is on the file system inside the docker container. When we restart the machine or remove the container and start again, we will lose all the changes including created databases. It will be like a fresh machine. This is because the containers are immutable.
The location of the data files can be checked by using the below command
SELECT
db.name AS DBName,
type_desc AS FileType,
Physical_Name AS Location
FROM
sys.master_files mf
INNER JOIN
sys.databases DB ON db.database_id = mf.database_id
The output looks like in the below screenshot
Using a new volume data path
There is a concept of Volume in the container which helps us here. We can tell the Container to have a different volume mapped to a local location in our host machine's hard disk. It can be accomplished by the below command.docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password!' -e 'MSSQL_PID=Enterprise' -p 1433:1433 -v G:\databases:/var/opt/mssql/data -d mcr.microsoft.com/mssql/server:2019-latest
The highlighted option tells the container to use local g:\databases folder from the host machine to store the contents of /var/opt/mssql/data folder in the container. When we run the container using the above command, we can see the master.mdf, master.ldf, model, and temp DB files, and all the files are showing up in the host machine. When we create a new database without specifying any storage, that will also come into the g:\databases
Docker-compose way
The same command can be saved as a docker-compose file and can be combined with other containers.
version: '3.7'
services:
SQLServer:
image: mcr.microsoft.com/mssql/server:2019-latest
environment:
- ACCEPT_EULA=Y
- SA_PASSWORD=Password!
- MSSQL_PID=Enterprise
ports:
- '1433:1433'
volumes:
- G:\databases:/var/opt/mssql/data
Restoring backup
Once we have this mapped path using volume, that is available in the host machine and container. It is easy to share the backup files. If the backup is available in the host machine, just copy to the g:\databases. Then connect to SQL instance in the container from the host machine's SSMS or any client tool. Do a normal restore wizard to point the backup file in /var/opt/mssql/data
This seems very much simple to develop. But before putting into production, we need to know more things that will be discussed in the next post.
No comments:
Post a Comment