Now that SQLServer 2016 is available on Linux as a public preview, one of the easiest ways to officially deploy it is through Docker engine. With the Docker image of MS SQLServer on Linux, you will have a running instance within few seconds. Being on a Docker image doesn’t change how you connect to SQLServer via SSMS or standards-based connectivity like Progress DataDirect SQLServer ODBC and JDBC drivers which are based on wire protocol communication.
In this tutorial, we will go through on how you can run SQLServer Linux using Docker engine, load some sample data to it and connect to the instance using the Progress DataDirect SQLServer ODBC driver.
1. To load the sample data to instance, let's bash in to container using the following command
docker exec -i -t <container name> /bin/bash
2. You should now be seeing the bash console of the docker container.
3. On your host machine, Download Adventure Works back up file from here .
4. Copy the backup file from the host machine to docker container using the following command
docker cp .\AdventureWorks2014.bak <container_name>:/AdventureWorks2014.bak
5.Back on the docker container bash console, create a directory for the backup file and move it in to that directory using the following commands
mkdir -p /var/opt/mssql/backup
mv AdventureWorks2014.bak /var/opt/mssql/backup/
6. Next step is to install sqlcmd to restore the database, but to do that we would have to install couple of packages and make some minor configuration changes. Run the following commands to install mssql-tools and unixodbc-dev
apt-get install curl
apt-get install apt-transport-https
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | tee /etc/apt/sources.list.d/msprod.list
apt-get install mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
7.You need to reconfigure locales before you use sqlcmd, to do that run the command “dpkg-reconfigure locales”. Choose “en_US UTF-8 UTF-8” and set “en_US UTF-8 UTF-8” as default locale.
8.Connect to SQL Server using sqlcmd by running the following command.
9.Restore the database Adventureworks by running the following command to have sample data in SQL Server Linux.
RESTORE DATABASE AdventureWorks
FROM DISK = '/var/opt/mssql/backup/AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_Data' TO '/var/opt/mssql/data/AdventureWorks2014_Data.mdf',
MOVE 'AdventureWorks2014_Log' TO '/var/opt/mssql/data/AdventureWorks2014_Log.ldf'
- Download the SQL Server ODBC driver for Windows from the Progress website.
- Extract the zip file PROGRESS_DATADIRECT_ODBC_SQLSERVER_WIN_xx.zip and run the setup.exe to install the SQL Server ODBC driver. It’s a simple setup and you should be done in few seconds.
- Open the ODBC Administrator (XX bit) depending on whether you installed 32 or 64-bit driver. Note that they have separate ODBC administrator tools.
- On ODBC administrator, choose Add -> Select DataDirect 7.X SQL Server Wire Protocol as your driver and click on Finish. You should now see a setup window as below.
5. Fill in host as ‘localhost’ and database as ‘AdventureWorks’ and Click on the button ‘Test Connect’. Provide the username as ‘SA’ and password that you set when you ran the Docker image for the first time. If you have created another login for your instance, you can also use that. You should be successfully connected to the SQL Server database on Linux running on a Docker engine.
- Install the package pyodbc using pip by running the following command.
2. Copy the following Python script which creates a connection to SQL Server using the ODBC driver that you have just configured in to a file or python console and run it.
##Importing pyodbc module
##connecting to your database through ODBC
cnxn = pyodbc.connect('DSN=SQLDocker;UID=sa;PWD=pass, autocommit=True)
cursor = cnxn.cursor()
3. Once you have successful connected to SQL Server, you can append the following script to the above file to print the tables in your database.
for row in cursor.tables():
if row.table_type == "TABLE":
4. You can also try executing scripts by appending following script.
##Executing a simple query and printing the records
cursor.execute("SELECT BusinessEntityID , NationalIDNumber, LoginID, OrganizationNode, OrganizationLevel, JobTitle, BirthDate, MaritalStatus FROM AdventureWorks.HumanResources.Employee")
for row in cursor.fetchall():
We hope this tutorial helped you to connect to your SQL Server database running on Docker engine. Try the SQLServer ODBC driver for 15 days and contact us if you have any questions or need an extension for the trial.