MS SQL Setup

Set up

Get the image

sudo docker pull mcr.microsoft.com/mssql/server:2019-latest

# Run the container
sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong@Passw0rd>" \
   -p 1433:1433 --name sql1 -h sql1 \
   -d mcr.microsoft.com/mssql/server:2019-latest

# How to change password
sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P "<YourStrong@Passw0rd>" \
   -Q 'ALTER LOGIN SA WITH PASSWORD="<YourNewStrong@Passw0rd>"'

# Connect to container
sudo docker exec -it sql1 "bash"

# Log in container
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourNewStrong@Passw0rd>"


# Connect locally
sqlcmd -S <ip_address>,1433 -U SA -P "<YourNewStrong@Passw0rd>"

Using

Getting Started

Find the container with

 
docker ps -a
docket start <container id>
# Log in remotely
sudo docker exec -it sql1 "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourNewStrong@Passw0rd>"
# Log in localally
sqlcmd -S 192.168.1.70,1433 -U SA -P "<YourNewStrong@Passw0rd>"

Commands

Some commands to get you going

List the databases

 
SELECT name FROM master.sys.databases
GO

Create a databases

 
CREATE DATABASE testdb
GO

Use the databases

 
USE testdb;
GO

Create a table

 
CREATE TABLE TEST_TABLE (col1 INT PRIMARY KEY,col2 VARCHAR(200));
GO

Show tables in a database

 
SELECT * FROM testdb.INFORMATION_SCHEMA.TABLES;
GO

Describe tables in a database

 
exec sp_columns TEST_TABLE;
GO

Formatting

Have not managed to fix this yet but you can do

 
:setvar SQLCMDMAXVARTYPEWIDTH 30
:setvar SQLCMDMAXFIXEDTYPEWIDTH 30