mssql on macos setup notes

mssql.notes.txt

§ install

1. install Homebrew https://docs.brew.sh/Installation.html
2. install Docker
	$ brew cask install docker
	this places the Docker.app in the Applications folder, and can be launched directly to start the service
3. install mssql per the instructions https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker
	$ sudo docker pull microsoft/mssql-server-linux:2017-latest
4. run the image, check the status
	$ sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_PID=Developer' -e 'MSSQL_SA_PASSWORD=<password>' -p 1433:1433 --name mssql0 -d microsoft/mssql-server-linux:2017-latest
	$ docker ps -a
	$ netstat -an | grep LISTEN
	$ sudo lsof -nPi -sTCP:LISTEN | grep 1433
5. install sql-cli and connect https://www.npmjs.com/package/sql-cli
	$ npm install -g sql-cli
	$ mssql -u sa -p <password>
	or possibly something like
	$ mssql -s <host> -u <username@host> -p <password> -d <database> -e
	Connecting...Enter ".help" for usage hints.
	mssql> .help
	
§ copy database

1. create backup on remote host
	$ sqlcmd -H localhost -U SA -P <password> -Q "BACKUP DATABASE [<database>] TO DISK = N'<filename>' WITH NOFORMAT, NOINIT, NAME = '<name>', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
	$ sudo mv /var/opt/mssql/data/<filename> ~/
2. copy backup to local container directory 'backup'
	$ scp <user@host>:</path/to/filename> ~/
	$ sudo docker exec -it mssql0 mkdir /var/opt/mssql/backup
	$ sudo docker cp <filename> mssql0:/var/opt/mssql/backup
3. restore from backup
	$ sudo docker exec -it mssql0 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<password>' -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/<filename>"' | tr -s ' ' | cut -d ' ' -f 1-2
	LogicalName PhysicalName
	------------------------
	<database> /var/opt/mssql/data/<database.mdf>
	...multiple logical and physical names: for each add a MOVE logicalName TO physicalName entry like:
	$ sudo docker exec -it mssql0 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<password>' -Q 'RESTORE DATABASE <database> FROM DISK = "/var/opt/mssql/backup/<filename>" WITH MOVE "<logicalName1>" TO "<physicalName1>", MOVE "<logicalName2 /var/opt/...mdf>" TO "<physicalName2 /var/opt/...ldf>"'
	Processed...
	RESTORE DATABASE successfully processed...
4. make queries
	$ sudo docker exec -it mssql0 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<password>' -Q 'SELECT Name FROM sys.Databases'

§ tasks
0. change master password
	> ALTER LOGIN SA WITH PASSWORD='<password>'
1. setup readonly user with sql; in my case I either did something wrong or had a confused preexisting/restored user and to fix I removed the user (on both sys and db: drop user <username>) and recreated.
	> create login <username> with password='<password>'
	> create user <username> from login <username>
	> grant connect, select, execute to <username>
	> use <database>
	> create user <username> from login <username>
	> grant connect, select, execute to <username>


§ more info, links

* more Docker
	https://docs.docker.com/get-started/
	https://docs.docker.com/docker-for-mac/
* helpful
	http://database.guide/how-to-install-sql-server-on-a-mac/
	https://medium.com/@reverentgeek/sql-server-running-on-a-mac-3efafda48861
	https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-backup-and-restore-database
	https://docs.microsoft.com/en-us/sql/relational-databases/databases/copy-databases-to-other-servers
	https://docs.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container
	https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility