Microsoft SQL Server

Hierarchical Data

https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-2017

MSSQL

MSSQL Replication

Installing Microsoft SQL Server on Docker

In order to test scaling easily and well probably best to use Docker & Kubernetes to orchestrate this.

https://www.youtube.com/watch?v=HkWwaOG3aSw

launch the docker instance named mssql

docker run --name mssql \
      -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=Admin-123' \
      -e 'MSSQL_PID=Enterprise' -p 1433:1433 \
      -d mcr.microsoft.com/mssql/server 

Install mssql tools on Ubuntu so we can interact with the instance

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
echo "deb [arch=amd64] https://packages.microsoft.com/ubuntu/18.04/prod bionic main" | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt update
sudo apt install mssql-tools msodbcsql17 unixodbc libodbc1
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Let's connect to our instance using sqlcmd

sqlcmd -S 127.0.0.1 -U sa

CREATE DATABASE jtest;
go
USE jtest;
go
CREATE TABLE jtable (
    first_name VARCHAR (50) NOT NUL,
):
go
select * from jtest.information_schema.tables;
go

Enter password from docker creation command Admin-123

References

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-the-utility?view=sql-server-2017

https://www.sqlshack.com/working-sql-server-command-line-sqlcmd/

https://www.mssqltips.com/sqlservertip/1543/using-sqlcmd-to-execute-multiple-sql-server-scripts/

More Commands

Run SQL commands using command line ==

• Connect to SQL Server from the command line to get an interactive shell sqlcmd -U username -S server\instance
• Run an SQL script sqlcmd -U username -S server\instance -e -i script.sql
• Run from python os.system('sqlcmd ...')

ALTER syntax

• Change column type ALTER TABLE your_table ALTER COLUMN your_column varchar(20)
• Change column name sp_RENAME 'your_table.old_column', 'new_column', 'COLUMN'
• Change collation of a database ALTER DATABASE dbname COLLATE SQL_Latin1_General_CP1_CI_AS

Type conversion

• Convert datetime date to YYYYMMDD format CONVERT(CHAR(8), GETDATE(), 112)
• Convert YYYYMMDD format into datetime CONVERT(datetime, '20110518') CONVERT(datetime, '20110518 14:19')
• Get yesterday's date DATEADD(DAY, -1, GETDATE()
• Get yesterday's date in YYYYMMDD format CONVERT(CHAR(8), DATEADD(DAY, -1, GETDATE()), 112)

Misc

• Remove null characters from a column

UPDATE the_table SET the_column = REPLACE(the_column, CHAR(0), '')

Kubernetes?

https://docs.microsoft.com/en-us/sql/linux/tutorial-sql-server-containers-kubernetes?view=sql-server-2017

Docker Compose

https://github.com/microsoft/sql-server-samples/tree/master/samples/containers/replication

docker exec -it {container_name}  /bin/sh
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P MssqlPass123

https://github.com/microsoft/sql-server-samples/tree/master/samples/development-frameworks/SqlServerOnDocker

https://azure.microsoft.com/en-us/resources/videos/connect-2017-use-sql-server-2017-in-docker-containers-for-your-ci-cd-process/

https://docs.docker.com/compose/aspnet-mssql-compose/

https://kimsereyblog.blogspot.com/2018/10/docker-compose-asp-net-core-application.html

Demo

https://github.com/twright-msft/mssql-aspnet-docker-demo-app

https://dev.to/carlos487/installing-dotnet-core-in-ubuntu-1804-7lp

sudo apt-get install dotnet-sdk-2.1.105