ironpot devLog

# mssql on docker

Tags: mssqldocker

# docker data volume 생성

$ docker volume create sql_volume
$ docker volume ls
$ docker volume inspect sql_volume
1
2
3

# network

추후 .net core app 의 connection string 을 단순하게 하기 위해 docker network 를 생성하여 묶어준다.

$ docker network create todo-api
$ docker network ls
$ docker network inspect todo-api
1
2
3

# run

$ docker run -d -p 1433:1433 -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=y0urStrong!Password" --network=todo-api --name sql1 -v sql_volume:/var/opt/mssql mcr.microsoft.com/mssql/server:2019-latest

# example
$ cd ~
$ mkdir sql-data
# uid=10001(mssql) gid=0(root) groups=0(root)
$ sudo chown 10001 sql-data
$ docker run -d -p 1433:1433 -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=y0urStrong\!Password" --network=mssql-outer --name mssql -v /home/user/sql-data:/var/opt/mssql mcr.microsoft.com/mssql/server:2019-latest
1
2
3
4
5
6
7
8

# DB restore test

테스트를 위한 AdventureWorks (opens new window) 다운로드
SSMS 또는 Azure Data Studio (opens new window) (과거 SQL operation studio) 로 테스트

$ docker exec -d sql1 mkdir /var/opt/mssql/backup
$ docker cp AdventureWorks2019.bak sql1:/var/opt/mssql/backup/AdventureWorks2019.bak

# 삭제 시
$ docker exec -it sql1 rm /var/opt/mssql/backup/AdventureWorks2019.bak
$ docker exec -it -u root sql1 rm /var/opt/mssql/backup/AdventureWorks2019.bak
1
2
3
4
5
6
-- 백업파일 확인
RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/backup/AdventureWorks2019.bak'
GO

RESTORE DATABASE AdventureWorks2019 FROM DISK = N'/var/opt/mssql/backup/AdventureWorks2019.bak'
WITH
MOVE 'AdventureWorks2017' TO '/var/opt/mssql/data/AdventureWorks2019.mdf',
MOVE 'AdventureWorks2017_log' TO '/var/opt/mssql/data/AdventureWorks2019_log.ldf'
GO

SELECT Name FROM sys.Databases

-- /var/opt/mssql/backup 에 백업생성
BACKUP DATABASE [AdventureWorks2019] TO DISK = N'/var/opt/mssql/backup/AdventureWorks2019_2.bak'
WITH NOFORMAT, NOINIT, NAME = 'AdventureWorks2019-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

sqlcmd 이용

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost \
   -U SA -P 'y0urStrong!Password' \
   -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/AdventureWorks2019.bak"' \
   | tr -s ' ' | cut -d ' ' -f 1-2

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P 'y0urStrong!Password' \
   -Q 'RESTORE DATABASE AdventureWorks2019 FROM DISK = "/var/opt/mssql/backup/AdventureWorks2019.bak" WITH MOVE "AdventureWorks2017" TO "/var/opt/mssql/data/AdventureWorks2019.mdf", MOVE "AdventureWorks2017_log" TO "/var/opt/mssql/data/AdventureWorks2019_log.ldf"'

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P 'y0urStrong!Password' \
   -Q 'SELECT Name FROM sys.Databases'

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P 'y0urStrong!Password' \
   -Q "BACKUP DATABASE [AdventureWorks2019] TO DISK = N'/var/opt/mssql/backup/AdventureWorks2019_2.bak' WITH NOFORMAT, NOINIT, NAME = 'AdventureWorks2019-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# mssql sa 암호 변경

$ docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "y0urStrong!Password" -Q 'ALTER LOGIN SA WITH PASSWORD="y0urNewStrong!Password"'
1

# mssql 을 container 로 docker run 할 경우, volume permission 문제

$ docker logs mssql
SQL Server 2019 will run as non-root by default.
This container is running as user mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
/opt/mssql/bin/sqlservr: Error: The system directory [/.system] could not be created.  Errno [13]

# volume 연결 시 권한 문제로 인해 container 가 올라가지 않을 경우
$ sudo chown 10001 ./sql-data
1
2
3
4
5
6
7
8

# Reference