Microsoft SQL Server

Deprecation notice

Support for Portworx on the following orchestrators will be deprecated:

• Docker Swarm and Docker Standalone
• Nomad

For installation instructions on other platforms, refer to the Platform guides section.

This page provides instructions for deploying Microsoft SQL Server (MS-SQL) with Portworx on Kubernetes.

Use these Kubernetes specs to deploy MS-SQL.

To create a highly available storage volume for MS-SQL, without having to provision storage in advance, run this command:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssw0rd' \
      -p 1433:1433 --volume-driver=pxd \
      -v name=mssqlvol,size=10,repl=3:/var/opt/mssql \
      -d microsoft/mssql-server-linux

This command runs mssql-server-linux with a 10 GB volume created dynamically with three-way replication, guaranteeing that persistent data will be fully replicated on three separate nodes.

You can now connect to MS-SQL in its container on port 1433. For example, to access the MS-SQL command prompt via docker, run:

docker exec -it <Container ID> /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "P@ssw0rd" '

note

You can run multiple instances of MS-SQL on the same host, each with its own unique persistent volume mapped, and each with its own unique IP address published.

Database recoverability using Snapshots

To take a recoverable snapshot of the mssql-server instance for a point in time, use the pxctl :

pxctl volume snapshot mssqlvol --name mssqlvol_snap_0628

Volume successfully snapped: 342580301989879504

pxctl snap list

ID			NAME			SIZE	HA	SHARED	ENCRYPTED	IO_PRIORITY	SCALE	STATUS
342580301989879504	mssqlvol_snap_0628	10 GiB	3	no	no		LOW		0	up - detached

By default, a Portworx volume snapshot is read-writable. The snapshot taken is visible globally throughout the cluster, and can be used to start another instance of MS-SQL on a different node as below:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssw0rd' \
>       -p 1433:1433 --volume-driver=pxd \
>       -v mssqlvol_snap_0628:/var/opt/mssql \
>       -d microsoft/mssql-server-linux

docker ps

CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS              PORTS                    NAMES
46eff5a9cbd6        microsoft/mssql-server-linux   "/bin/sh -c /opt/mssq"   4 minutes ago       Up 4 minutes        0.0.0.0:1433->1433/tcp   compassionate_perlman
0636d98250c4        portworx/px-dev                "/docker-entry-point."   2 hours ago         Up 2 hours                                   portworx.service
jeff-coreos-2 core # docker inspect --format '{{ .Mounts }}' 46eff5a9cbd6
[{mssqlvol_snap_0628 /var/lib/osd/mounts/mssqlvol_snap_0628 /var/opt/mssql pxd  true rprivate}]

For further reading on running an MS-SQL container image with Docker, refer to this page.