Running Microsoft SQL Server using Docker and Connecting to it using C#
Docker lets you spin up any container regardless of which OS you are running. It also provides you with a isolated environment where you can run the desired environment with ease. It lets you deploy the environment that you used during development.
In this tutorial, I’m going to show how to run the Microsoft SQL Server using Docker and connect to the MS SQL Server using C#.
Let’s start the SQL Server detached container by running the following command:
$ docker run -e 'ACCEPT_EULA=Y' \
-e 'SA_PASSWORD=C#1234SQLServer' \
-p 1433:1433 \
-d mcr.microsoft.com/mssql/server:2019-latestThe above command starts the Microsoft SQL Server 2019 and binds to port 1433.
The above command also sets some environment variables as follows:
- ACCEPT_EULA: The value of Y indicates the acceptance of End-User Licensing Agreement.
- SA_PASSWORD: Defines the database system administrator password with userid as sa.
In order to confirm the successful installation of the SQL Server run the following command by replacing the CONTAINER_ID with the container id got from the previous command (you can also find the container id by running the docker ps command):
$ docker exec -it $CONTAINER_ID \
/opt/mssql-tools/bin/sqlcmd -S localhost \
-U sa \
-P C#1234SQLServer \
-Q \
"
:setvar SQLCMDMAXVARTYPEWIDTH 30
:setvar SQLCMDMAXFIXEDTYPEWIDTH 30SELECT
SERVERPROPERTY('MachineName') AS ComputerName,
SERVERPROPERTY('ServerName') AS InstanceName,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel;
"
The above command logs into the SQL Server docker container and connects to the server using the sqlcmd with user sa and respective password and executes the command with -Q option. The command should display something like this:
As the server is running on docker the MachineName/ComputerName and ServerName/InstanceName are same as the CONTAINER_ID of the docker container. By default if the MSSQL_PID environment variable is not set then it runs with the Developer Edition.
Now that we have the SQL Server successfully running as docker container lets connect to this using the C# and get the results. The following C# code connects to the docker and prints the same results that we did with the sqlcmd command.
// Output
ComputerName: CONTAINER_ID
InstanceName: CONTAINER_ID
Edition: Developer Edition (64-bit)
ProductVersion: 15.0.4123.1
ProductLevel: RTMConclusion
In this tutorial, we have looked at how easy it is to spin up MS SQL Server docker container and connect to this container using the C# code. If you have any questions please write them in the comment below. You can find the source code on my github.
