Run SQL Server in Docker

Pull the SQL Server image:

docker pull mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

Run the container:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Cockroach!1' \
 -p 1433:1433 --name sql1 \
 -d mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

Change password:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
 -S localhost -U SA -P 'Cockroach!1' \
 -Q 'ALTER LOGIN SA WITH PASSWORD="CockroachDB1!"'

Restore a Backup File of WideWorldImporters Database in the Container

Create a backup directory:

docker exec -it sql1 mkdir /var/opt/mssql/backup

Download WideWorldImporters database:

curl -L -o wwi.bak 'https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak'

Copy the backup file into the container:

docker cp wwi.bak sql1:/var/opt/mssql/backup

List the logical names and paths for the backup:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost \
   -U SA -P 'CockroachDB1!' \
   -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/wwi.bak"' \
   | tr -s ' ' | cut -d ' ' -f 1-2

Execute the restore command:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P 'CockroachDB1!' \
   -Q 'RESTORE DATABASE WideWorldImporters FROM DISK = "/var/opt/mssql/backup/wwi.bak" WITH MOVE "WWI_Primary" TO "/var/opt/mssql/data/WideWorldImporters.mdf", MOVE "WWI_UserData" TO "/var/opt/mssql/data/WideWorldImporters_userdata.ndf", MOVE "WWI_Log" TO "/var/opt/mssql/data/WideWorldImporters.ldf", MOVE "WWI_InMemory_Data_1" TO "/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1"'

Verify the restored database:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P 'CockroachDB1!' \
   -Q 'SELECT Name FROM sys.Databases'

Access the SQL Server Docker Container From Your Host Using mssql-cli

mssql-cli is a CLI utility used to connect to SQL Server. It is currently under heavy development and offers a more pleasant experience over sqlcmd, in my opinion. In the typical new Microsoft fashion, the GitHub page for the project can be found here. It is also easier to install than sqlcmd on my Mac.

Install the mssql-cli tool on your host:

sudo pip install mssql-cli

In my case, the install fails due to the package six being available on my system, so the workaround is:

sudo pip install mssql-cli --ignore-installed six

Connect to your SQL Server instance directly:

mssql-cli -U SA -P 'CockroachDB1!' -S localhost,1433 -d WideWorldImporters

Run a sample query:

SELECT StockItemID, StockItemName FROM WideWorldImporters.Warehouse.StockItems WHERE StockItemID>=1

Redirect output of a query to a file we're going to use for import into CockroachDB:

mssql-cli -U SA -P 'CockroachDB1!' -S localhost,1433 -d WideWorldImporters -Q "SELECT * FROM WideWorldImporters.Warehouse.StockItemTransactions" -o StockItemTransactions.csv

Use BCP Utility For Bulk Exporting

bcp is a bulk copy utility that is better served for exporting data out of SQL Server You can try installing sqlcmd and mssqlodbc locally or connect to the container and use bcp that comes bundled with SQL Server. In my experience, setting up bcp with brew on OSX was a challenge, and I opted for executing it inside a container instead.

docker exec -it sql1 bash
/opt/mssql-tools/bin/bcp WideWorldImporters.Warehouse.StockItemTransactions out /var/opt/mssql/backup/StockItemTransactionsBCP.csv -S localhost,1433 -U sa -P 'CockroachDB1!' -c -t',' -r'\n'

Copy the file down to your host:

docker cp sql1:/var/opt/mssql/backup/StockItemTransactionsBCP.csv .

Describe StockItemTransactions table an equivalent of describe tablename in SQL Server is a stored procedure below:

exec sp_columns StockItemTransactions

The output is lengthy so it's best to output it to a file

mssql-cli -U SA -P 'CockroachDB1!' -S localhost,1433 -d WideWorldImporters -Q "exec sp_columns StockItemTransactions" -o schema.sql

+--------------------+---------------+-----------------------+-------------------------+-------------+-------------+-------------+----------+---------+---------+------------+-----------+----------------------------------------------+-----------------+--------------------+---------------------+--------------------+---------------+----------------+
| TABLE_QUALIFIER    | TABLE_OWNER   | TABLE_NAME            | COLUMN_NAME             | DATA_TYPE   | TYPE_NAME   | PRECISION   | LENGTH   | SCALE   | RADIX   | NULLABLE   | REMARKS   | COLUMN_DEF                                   | SQL_DATA_TYPE   | SQL_DATETIME_SUB   | CHAR_OCTET_LENGTH   | ORDINAL_POSITION   | IS_NULLABLE   | SS_DATA_TYPE   |
|--------------------+---------------+-----------------------+-------------------------+-------------+-------------+-------------+----------+---------+---------+------------+-----------+----------------------------------------------+-----------------+--------------------+---------------------+--------------------+---------------+----------------|
| WideWorldImporters | Warehouse     | StockItemTransactions | StockItemTransactionID  | 4           | int         | 10          | 4        | 0       | 10      | 0          | NULL      | (NEXT VALUE FOR [Sequences].[TransactionID]) | 4               | NULL               | NULL                | 1                  | NO            | 56             |
| WideWorldImporters | Warehouse     | StockItemTransactions | StockItemID             | 4           | int         | 10          | 4        | 0       | 10      | 0          | NULL      | NULL                                         | 4               | NULL               | NULL                | 2                  | NO            | 56             |
| WideWorldImporters | Warehouse     | StockItemTransactions | TransactionTypeID       | 4           | int         | 10          | 4        | 0       | 10      | 0          | NULL      | NULL                                         | 4               | NULL               | NULL                | 3                  | NO            | 56             |
| WideWorldImporters | Warehouse     | StockItemTransactions | CustomerID              | 4           | int         | 10          | 4        | 0       | 10      | 1          | NULL      | NULL                                         | 4               | NULL               | NULL                | 4                  | YES           | 38             |
| WideWorldImporters | Warehouse     | StockItemTransactions | InvoiceID               | 4           | int         | 10          | 4        | 0       | 10      | 1          | NULL      | NULL                                         | 4               | NULL               | NULL                | 5                  | YES           | 38             |
| WideWorldImporters | Warehouse     | StockItemTransactions | SupplierID              | 4           | int         | 10          | 4        | 0       | 10      | 1          | NULL      | NULL                                         | 4               | NULL               | NULL                | 6                  | YES           | 38             |
| WideWorldImporters | Warehouse     | StockItemTransactions | PurchaseOrderID         | 4           | int         | 10          | 4        | 0       | 10      | 1          | NULL      | NULL                                         | 4               | NULL               | NULL                | 7                  | YES           | 38             |
| WideWorldImporters | Warehouse     | StockItemTransactions | TransactionOccurredWhen | -9          | datetime2   | 27          | 54       | NULL    | NULL    | 0          | NULL      | NULL                                         | -9              | NULL               | NULL                | 8                  | NO            | 0              |
| WideWorldImporters | Warehouse     | StockItemTransactions | Quantity                | 3           | decimal     | 18          | 20       | 3       | 10      | 0          | NULL      | NULL                                         | 3               | NULL               | NULL                | 9                  | NO            | 55             |
| WideWorldImporters | Warehouse     | StockItemTransactions | LastEditedBy            | 4           | int         | 10          | 4        | 0       | 10      | 0          | NULL      | NULL                                         | 4               | NULL               | NULL                | 10                 | NO            | 56             |
| WideWorldImporters | Warehouse     | StockItemTransactions | LastEditedWhen          | -9          | datetime2   | 27          | 54       | NULL    | NULL    | 0          | NULL      | (sysdatetime())                              | -9              | NULL               | NULL                | 11                 | NO            | 0              |
+--------------------+---------------+-----------------------+-------------------------+-------------+-------------+-------------+----------+---------+---------+------------+-----------+----------------------------------------------+-----------------+--------------------+---------------------+--------------------+---------------+----------------+
(11 rows affected)

Import Data From SQL Server into CockroachDB

Start a single node instance of Cockroach or use your own environment:

cockroach start-single-node --insecure --host=localhost --port=26257 --background --external-io-dir $PWD

Note: Cockroach doesn't support schemas currently so we're going to place the table in default schema.

This article covers an old version of CockroachDB and in the recent releases, we added support for custom schemas.

select * from stockitemtransactions where supplierid is not null;

Import the Output of bcp From SQL Server into CockroachDB

Connect to your instance of CockroachDB SQL Shell:

cockroach sql --insecure

Import the dataset:

root@:26257/defaultdb> CREATE DATABASE IF NOT EXISTS WideWorldImporters;
USE WideWorldImporters;
DROP TABLE IF EXISTS StockItemTransactions;
IMPORT TABLE StockItemTransactions (
        StockItemTransactionID INT8 NOT NULL,
        StockItemID INT8 NOT NULL,
        TransactionTypeID INT8 NOT NULL,
        CustomerID STRING NULL, --INT8
        InvoiceID STRING NULL, --INT8
        SupplierID STRING NULL, --INT8
        PurchaseOrderID STRING NULL, --INT8
        TransactionOccurredWhen TIMESTAMP NOT NULL,
        Quantity DECIMAL NOT NULL,
        LastEditedBy INT8 NOT NULL,
        LastEditedWhen TIMESTAMP NOT NULL
)
CSV DATA ('nodelocal:///StockItemTransactionsBCP.csv');


CREATE DATABASE

Time: 572µs

SET

Time: 196µs

DROP TABLE

Time: 76.272ms

        job_id       |  status   | fraction_completed |  rows  | index_entries | system_records |  bytes
+--------------------+-----------+--------------------+--------+---------------+----------------+----------+
  509222924675055617 | succeeded |                  1 | 236667 |             0 |              0 | 15048500
(1 row)

Time: 443.022ms

One thing you should know is that some features are still maturing in Cockroach and as I highlighted in my tutorial, schemas are not available data type conversions need more work. I opted using STRING data type for CustomerID, InvoiceID, SupplierID and PurchaseOrderID to complete the tutorial but there should be a better way.