Microsoft SQL Server - Revision history

Busk at 18:08, 30 August 2019

2019-08-30T18:08:05Z

Busk at 18:49, 6 August 2019

2019-08-06T18:49:14Z

Busk at 00:48, 30 July 2019

2019-07-30T00:48:13Z

Busk at 00:40, 30 July 2019

2019-07-30T00:40:44Z

Busk at 00:33, 30 July 2019

2019-07-30T00:33:27Z

Busk at 00:19, 30 July 2019

2019-07-30T00:19:59Z

Busk at 00:17, 30 July 2019

2019-07-30T00:17:59Z

Busk at 00:07, 30 July 2019

2019-07-30T00:07:46Z

Busk at 00:04, 30 July 2019

2019-07-30T00:04:11Z

Busk: Created page with "# Commands # 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.yo..."

2019-07-17T12:11:22Z

Created page with "# Commands # 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.yo..."

New page

# Commands

# 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

← Older revision		Revision as of 18:08, 30 August 2019
Line 1:		Line 1:
		+	# Hierarchical Data
		+
		+	https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-2017
		+
	# MSSQL		# MSSQL
	[[ MSSQL Replication ]]		[[ MSSQL Replication ]]

← Older revision		Revision as of 18:49, 6 August 2019
Line 1:		Line 1:
		+	# MSSQL
		+	[[ MSSQL Replication ]]
		+
	# Commands		# Commands

← Older revision		Revision as of 00:48, 30 July 2019
Line 114:		Line 114:

	https://github.com/microsoft/sql-server-samples/tree/master/samples/containers/replication		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://github.com/microsoft/sql-server-samples/tree/master/samples/development-frameworks/SqlServerOnDocker

← Older revision		Revision as of 00:40, 30 July 2019
Line 112:		Line 112:

	# Docker Compose		# Docker Compose
		+
		+	https://github.com/microsoft/sql-server-samples/tree/master/samples/containers/replication

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

← Older revision		Revision as of 00:33, 30 July 2019
Line 120:		Line 120:

	https://kimsereyblog.blogspot.com/2018/10/docker-compose-asp-net-core-application.html		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

← Older revision		Revision as of 00:19, 30 July 2019
Line 118:		Line 118:

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

← Older revision		Revision as of 00:17, 30 July 2019
Line 116:		Line 116:

	https://azure.microsoft.com/en-us/resources/videos/connect-2017-use-sql-server-2017-in-docker-containers-for-your-ci-cd-process/		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/

← Older revision		Revision as of 00:07, 30 July 2019
Line 112:		Line 112:

	# Docker Compose		# Docker Compose
		+
		+	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://azure.microsoft.com/en-us/resources/videos/connect-2017-use-sql-server-2017-in-docker-containers-for-your-ci-cd-process/

← Older revision		Revision as of 00:04, 30 July 2019
Line 110:		Line 110:

	https://docs.microsoft.com/en-us/sql/linux/tutorial-sql-server-containers-kubernetes?view=sql-server-2017		https://docs.microsoft.com/en-us/sql/linux/tutorial-sql-server-containers-kubernetes?view=sql-server-2017
		+
		+	# Docker Compose
		+
		+	https://azure.microsoft.com/en-us/resources/videos/connect-2017-use-sql-server-2017-in-docker-containers-for-your-ci-cd-process/