Microsoft MVP성태의 닷넷 이야기
개발 환경 구성: 475. docker - SQL Server 2019 컨테이너 실행 [링크 복사], [링크+제목 복사]
조회: 413
글쓴 사람
홈페이지
첨부 파일
 

docker - SQL Server 2019 컨테이너 실행

SQL Server 2019 컨테이너의 경우,

Quickstart: Run SQL Server container images with Docker
; https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker

그냥 설치/실행하면 이런 메시지를 보게 되고,

c:\temp> docker run -it mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
Unable to find image 'mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04' locally
2019-GA-ubuntu-16.04: Pulling from mssql/server
59ab41dd721a: Pull complete
57da90bec92c: Pull complete
06fe57530625: Pull complete
5a6315cba1ff: Pull complete
739f58768b3f: Pull complete
fd449e8d7345: Pull complete
51d0933375e5: Pull complete
64f21ba81504: Pull complete
55b6919c0cc6: Pull complete
Digest: sha256:c8fa22553ce421b0482febcafa712b29cbb933b0d97a8671686797b31cf157a9
Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
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.
The SQL Server End-User License Agreement (EULA) must be accepted before SQL
Server can start. The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746388.

You can accept the EULA by specifying the --accept-eula command line option,
setting the ACCEPT_EULA environment variable, or using the mssql-conf tool.

Entrypoint: opt/mssql/bin/permissions_check.sh

실행이 곧바로 종료됩니다. 메시지에 따라 (그리고 편의상 SA_PASSWORD 환경 변수까지 포함해) 다음과 같이 2개의 환경 변수를 설정해 실행해야만 합니다.

// https://hub.docker.com/_/microsoft-mssql-server 문서 참고
// 

c:\temp> docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=test_1234" -p 1433:1433 --name sql1 -d mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

c:\temp> docker ps | findstr mssql
7bd3e7787b5e        mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04   "/opt/mssql/bin/pe..."   11 minutes ago      Up 11 minutes       0.0.0.0:1433->1433/tcp   sql1

컨테이너가 실행이 되면 얼마 간의 초기화 작업을 거친 후에야 SQL Server Management Studio와 같은 도구를 이용해 접속/관리를 시작할 수 있습니다.




컨테이너 내부에서 sqlcmd를 실행해 볼까요? 일단, 컨테이너 내부로 shell을 실행시켜,

c:\temp> docker exec -it 7bd3e7787b5e /bin/bash

sqlcmd를 찾은 후,

$ find / -name sqlcmd -o -path "/proc" -prune
find: '/var/cache/apt/archives/partial': Permission denied
find: '/var/cache/ldconfig': Permission denied
find: '/root': Permission denied
find: '/etc/ssl/private': Permission denied
/opt/mssql-tools/bin/sqlcmd
/proc

윈도우의 sqlcmd.exe와 유사한 옵션으로 지정해 실행하면 됩니다.

$  /opt/mssql-tools/bin/sqlcmd -U sa -P $SA_PASSWORD
1>

참고로, mcr.microsoft.com/mssql/server 환경은 기본적으로 "non-root" 권한으로 실행되기 때문에 관리자 권한이 필요한 프로그램을 수행하고 싶다면 "docker exec" 단계에서부터 "u" 옵션을 지정해 실행해야 합니다.

docker - container에서 root 권한 명령어 실행(sudo)
; https://www.sysnet.pe.kr/2/0/12172




해당 컨테이너를 stop하는 것은 상관없지만, 당연히 "rm"으로 컨테이너 인스턴스 자체를 삭제해버리면 이후 다시 "docker run"을 하게 되었을 때 초기 이미지부터 SQL 서버 구성이 시작되기 때문에 기존 데이터는 모두 삭제가 됩니다. 따라서 아래의 문서에 따라,

Configure SQL Server container images on Docker - Persist your data
; https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-docker?view=sql-server-ver15#persist

docker run 수행 시 볼륨 매핑을 추가해야만 합니다.

-v sqlvolume:/var/opt/mssql

예) docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=test_1234" -v sqlvolume:/var/opt/mssql -p 1433:1433 --name sql1 -d mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

이후부터는, "docker rm ..." 후 다시 "docker run ..."을 해도 기존 구성해 놓은 docker 호스트 측 "sqlvolume" 디렉터리의 설정을 다시 로드하므로 사용자 데이터베이스가 정상적으로 보존이 됩니다. (확인 방법: DockerDesktopVM의 파일 시스템 접근 참고)




참고로, 아래의 내용은 mcr.microsoft.com/mssql/server 인스턴스화할 때 출력되는 로그입니다.

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.
2020-03-07 12:42:15.14 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2020-03-07 12:42:15.49 Server      Did not find an existing master data file /var/opt/mssql/data/master.mdf, copying the missing default master and other system database files. If you have moved the database location, but not moved the database files, startup may fail. To repair: shutdown SQL Server, move the master database to configured location, and restart.
2020-03-07 12:42:15.50 Server      Setup step is copying system data file 'C:\templatedata\mastlog.ldf' to '/var/opt/mssql/data/mastlog.ldf'.
2020-03-07 12:42:15.55 Server      Setup step is copying system data file 'C:\templatedata\model.mdf' to '/var/opt/mssql/data/model.mdf'.
2020-03-07 12:42:15.64 Server      Setup step is copying system data file 'C:\templatedata\modellog.ldf' to '/var/opt/mssql/data/modellog.ldf'.
2020-03-07 12:42:15.72 Server      Setup step is copying system data file 'C:\templatedata\msdbdata.mdf' to '/var/opt/mssql/data/msdbdata.mdf'.
2020-03-07 12:42:15.81 Server      Setup step is copying system data file 'C:\templatedata\msdblog.ldf' to '/var/opt/mssql/data/msdblog.ldf'.
2020-03-07 12:42:15.86 Server      Setup step is copying system data file 'C:\templatedata\model_replicatedmaster.mdf' to '/var/opt/mssql/data/model_replicatedmaster.mdf'.
2020-03-07 12:42:15.95 Server      Setup step is copying system data file 'C:\templatedata\model_replicatedmaster.ldf' to '/var/opt/mssql/data/model_replicatedmaster.ldf'.
2020-03-07 12:42:16.03 Server      Setup step is copying system data file 'C:\templatedata\model_msdbdata.mdf' to '/var/opt/mssql/data/model_msdbdata.mdf'.
2020-03-07 12:42:16.16 Server      Setup step is copying system data file 'C:\templatedata\model_msdblog.ldf' to '/var/opt/mssql/data/model_msdblog.ldf'.
2020-03-07 12:42:16.61 Server      Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) 
Sep 24 2019 13:48:23 
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS) <X64>
2020-03-07 12:42:16.61 Server      UTC adjustment: 0:00
2020-03-07 12:42:16.62 Server      (c) Microsoft Corporation.
2020-03-07 12:42:16.62 Server      All rights reserved.
2020-03-07 12:42:16.62 Server      Server process ID is 28.
2020-03-07 12:42:16.62 Server      Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2020-03-07 12:42:16.63 Server      Registry startup parameters: 
 -d /var/opt/mssql/data/master.mdf
 -l /var/opt/mssql/data/mastlog.ldf
 -e /var/opt/mssql/log/errorlog
2020-03-07 12:42:16.65 Server      SQL Server detected 1 sockets with 1 cores per socket and 2 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2020-03-07 12:42:16.66 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2020-03-07 12:42:16.66 Server      Detected 1573 MB of RAM. This is an informational message; no user action is required.
2020-03-07 12:42:16.66 Server      Using conventional memory in the memory manager.
2020-03-07 12:42:16.68 Server      Page exclusion bitmap is enabled.
2020-03-07 12:42:16.91 Server      Buffer pool extension is not supported on Linux platform.
2020-03-07 12:42:16.92 Server      Buffer Pool: Allocating 262144 bytes for 177645 hashPages.
2020-03-07 12:42:18.04 Server      Buffer pool extension is already disabled. No action is necessary.
2020-03-07 12:42:18.97 Server      Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA'].
2020-03-07 12:42:19.01 Server      Query Store settings initialized with enabled = 1, 
2020-03-07 12:42:19.20 Server      The maximum number of dedicated administrator connections for this instance is '1'
2020-03-07 12:42:19.22 Server      Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2020-03-07 12:42:19.25 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2020-03-07 12:42:19.28 Server      In-Memory OLTP initialized on lowend machine.
2020-03-07 12:42:19.68 Server      CLR version v4.0.30319 loaded.
2020-03-07 12:42:19.79 Server      [INFO] Created Extended Events session 'hkenginexesession'
2020-03-07 12:42:19.79 Server      Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
ForceFlush is enabled for this instance. 
2020-03-07 12:42:19.80 Server      Total Log Writer threads: 1. This is an informational message; no user action is required.
2020-03-07 12:42:19.82 Server      clflush is selected for pmem flush operation.
2020-03-07 12:42:19.83 Server      Software Usage Metrics is disabled.
2020-03-07 12:42:19.84 spid8s      [1]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-07 12:42:19.86 spid8s      Starting up database 'master'.
ForceFlush feature is enabled for log durability.
2020-03-07 12:42:20.92 spid8s      The tail of the log for database master is being rewritten to match the new sector size of 4096 bytes.  2560 bytes at offset 427520 in file /var/opt/mssql/data/mastlog.ldf will be written.
2020-03-07 12:42:21.82 spid8s      Converting database 'master' from version 897 to the current version 904.
2020-03-07 12:42:21.83 spid8s      Database 'master' running the upgrade step from version 897 to version 898.
2020-03-07 12:42:22.11 Server      Common language runtime (CLR) functionality initialized.
2020-03-07 12:42:22.47 spid8s      Database 'master' running the upgrade step from version 898 to version 899.
2020-03-07 12:42:23.15 spid8s      Database 'master' running the upgrade step from version 899 to version 900.
2020-03-07 12:42:23.50 Server      Failed to verify the Authenticode signature of 'C:\binn\msoledbsql.dll'. Signature verification of SQL Server DLLs will be skipped. Genuine copies of SQL Server are signed. Failure to verify the Authenticode signature might indicate that this is not an authentic release of SQL Server. Install a genuine copy of SQL Server or contact customer support.
2020-03-07 12:42:23.91 spid8s      Database 'master' running the upgrade step from version 900 to version 901.
2020-03-07 12:42:24.21 spid8s      Database 'master' running the upgrade step from version 901 to version 902.
2020-03-07 12:42:24.46 spid8s      Database 'master' running the upgrade step from version 902 to version 903.
2020-03-07 12:42:25.01 spid8s      Database 'master' running the upgrade step from version 903 to version 904.
2020-03-07 12:42:27.07 spid8s      Resource governor reconfiguration succeeded.
2020-03-07 12:42:27.08 spid8s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2020-03-07 12:42:27.17 spid8s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2020-03-07 12:42:27.66 spid8s      SQL Trace ID 1 was started by login "sa".
2020-03-07 12:42:29.64 spid8s      Server name is '933f4501eb5e'. This is an informational message only. No user action is required.
2020-03-07 12:42:29.67 spid8s      [4]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-07 12:42:29.68 spid11s     [32767]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-07 12:42:29.70 spid40s     Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2020-03-07 12:42:29.70 spid40s     Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
2020-03-07 12:42:29.72 spid8s      Starting up database 'msdb'.
2020-03-07 12:42:29.72 spid11s     Starting up database 'mssqlsystemresource'.
2020-03-07 12:42:29.76 spid11s     The resource database build version is 15.00.2000. This is an informational message only. No user action is required.
2020-03-07 12:42:29.85 spid11s     [3]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-07 12:42:29.87 spid11s     Starting up database 'model'.
2020-03-07 12:42:30.29 spid37s     Password policy update was successful.
2020-03-07 12:42:30.96 spid37s     A self-generated certificate was successfully loaded for encryption.
2020-03-07 12:42:31.07 spid37s     Server is listening on [ 'any' <ipv6> 1433].
2020-03-07 12:42:31.08 spid37s     Server is listening on [ 'any' <ipv4> 1433].
2020-03-07 12:42:31.22 spid8s      The tail of the log for database msdb is being rewritten to match the new sector size of 4096 bytes.  3072 bytes at offset 50176 in file /var/opt/mssql/data/MSDBLog.ldf will be written.
2020-03-07 12:42:31.25 Server      Server is listening on [ ::1 <ipv6> 1434].
2020-03-07 12:42:31.26 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2020-03-07 12:42:31.27 Server      Dedicated admin connection support was established for listening locally on port 1434.
2020-03-07 12:42:31.28 spid37s     Error: 39002, Severity: 16, State: 1.
2020-03-07 12:42:31.28 spid37s     SQL failed to boot extensibility for error code 0x80070005.
2020-03-07 12:42:31.31 spid37s     InitializeXdbPkgLauncher failed. ErrorCode: 0x80004005.
2020-03-07 12:42:31.31 spid11s     The tail of the log for database model is being rewritten to match the new sector size of 4096 bytes.  512 bytes at offset 73216 in file /var/opt/mssql/data/modellog.ldf will be written.
2020-03-07 12:42:31.33 spid37s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2020-03-07 12:42:31.91 spid8s      Converting database 'msdb' from version 897 to the current version 904.
2020-03-07 12:42:31.91 spid8s      Database 'msdb' running the upgrade step from version 897 to version 898.
2020-03-07 12:42:32.04 spid11s     Converting database 'model' from version 897 to the current version 904.
2020-03-07 12:42:32.05 spid11s     Database 'model' running the upgrade step from version 897 to version 898.
2020-03-07 12:42:32.80 spid8s      Database 'msdb' running the upgrade step from version 898 to version 899.
2020-03-07 12:42:32.90 spid11s     Database 'model' running the upgrade step from version 898 to version 899.
2020-03-07 12:42:33.40 spid8s      Database 'msdb' running the upgrade step from version 899 to version 900.
2020-03-07 12:42:33.45 spid11s     Database 'model' running the upgrade step from version 899 to version 900.
2020-03-07 12:42:33.86 spid8s      Database 'msdb' running the upgrade step from version 900 to version 901.
2020-03-07 12:42:34.04 spid11s     Database 'model' running the upgrade step from version 900 to version 901.
2020-03-07 12:42:34.42 spid8s      Database 'msdb' running the upgrade step from version 901 to version 902.
2020-03-07 12:42:34.51 spid11s     Database 'model' running the upgrade step from version 901 to version 902.
2020-03-07 12:42:34.86 spid11s     Database 'model' running the upgrade step from version 902 to version 903.
2020-03-07 12:42:35.16 spid11s     Database 'model' running the upgrade step from version 903 to version 904.
2020-03-07 12:42:35.56 spid11s     Clearing tempdb database.
2020-03-07 12:42:37.25 spid11s     [2]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-07 12:42:37.26 spid11s     Starting up database 'tempdb'.
2020-03-07 12:42:37.70 spid11s     The tempdb database has 1 data file(s).
2020-03-07 12:42:37.84 spid40s     The Service Broker endpoint is in disabled or stopped state.
2020-03-07 12:42:37.85 spid40s     The Database Mirroring endpoint is in disabled or stopped state.
2020-03-07 12:42:37.97 spid40s     Service Broker manager has started.
2020-03-07 12:42:38.42 spid8s      Database 'msdb' running the upgrade step from version 902 to version 903.
2020-03-07 12:42:38.75 spid8s      Database 'msdb' running the upgrade step from version 903 to version 904.
2020-03-07 12:42:39.60 spid8s      Recovery is complete. This is an informational message only. No user action is required.
2020-03-07 12:42:40.61 spid17s     The default language (LCID 0) has been set for engine and full-text services.
2020-03-07 12:42:41.90 spid17s     The tempdb database has 2 data file(s).




[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]

[연관 글]





[최초 등록일: ]
[최종 수정일: 3/8/2020 ]

Creative Commons License
이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.
by SeongTae Jeong, mailto:techsharer@outlook.com

비밀번호

댓글 쓴 사람
 



2020-03-10 08시51분
Automating SQL Server 2019 Docker Deployments
; https://www.wintellect.com/automating-sql-server-2019-docker-deployments/
정성태

1  2  3  4  5  6  7  [8]  9  10  11  12  13  14  15  ...
NoWriterDateCnt.TitleFile(s)
12088정성태12/23/2019377Linux: 28. Linux - 윈도우의 "Run as different user" 기능을 shell에서 실행하는 방법
12087정성태12/21/2019533디버깅 기술: 145. windbg/sos - Dictionary의 entries 배열 내용을 모두 덤프하는 방법 (do_hashtable.py)
12086정성태12/20/2019601디버깅 기술: 144. windbg - Marshal.FreeHGlobal에서 발생한 덤프 분석 사례
12085정성태12/20/2019490오류 유형: 586. iisreset - The data is invalid. (2147942413, 8007000d) 오류 발생 - 두 번째 이야기 [1]
12084정성태12/21/2019568디버깅 기술: 143. windbg/sos - Hashtable의 buckets 배열 내용을 모두 덤프하는 방법 (do_hashtable.py)
12083정성태12/17/2019781Linux: 27. linux - lldb를 이용한 .NET Core 응용 프로그램의 메모리 덤프 분석 방법 [1]
12082정성태12/17/2019588오류 유형: 585. lsof: WARNING: can't stat() fuse.gvfsd-fuse file system
12081정성태12/16/2019676개발 환경 구성: 465. 로컬 PC에서 개발 중인 ASP.NET Core 웹 응용 프로그램을 다른 PC에서도 접근하는 방법 [1]
12080정성태12/16/2019858.NET Framework: 870. C# - 프로세스의 모든 핸들을 열람
12079정성태12/13/2019649오류 유형: 584. 원격 데스크탑(rdp) 환경에서 다중 또는 고용량 파일 복사 시 "Unspecified error" 오류 발생
12078정성태12/13/20191018Linux: 26. .NET Core 응용 프로그램을 위한 메모리 덤프 방법 [1]
12077정성태12/13/2019728Linux: 25. 자주 실행할 명령어 또는 초기 환경을 "~/.bashrc" 파일에 등록
12076정성태12/17/2019827디버깅 기술: 142. Linux - lldb 환경에서 sos 확장 명령어를 이용한 닷넷 프로세스 디버깅 - 배포 방법에 따른 차이
12075정성태12/18/2019918디버깅 기술: 141. Linux - lldb 환경에서 sos 확장 명령어를 이용한 닷넷 프로세스 디버깅
12074정성태12/11/2019590디버깅 기술: 140. windbg/Visual Studio - 값이 변경된 경우를 위한 정지점(BP) 설정(Data Breakpoint)
12073정성태12/10/20191056Linux: 24. Linux/C# - 실행 파일이 아닌 스크립트 형식의 명령어를 Process.Start로 실행하는 방법
12072정성태12/9/2019424오류 유형: 583. iisreset 수행 시 "No such interface supported" 오류
12071정성태12/9/2019670오류 유형: 582. 리눅스 디스크 공간 부족 및 safemode 부팅 방법
12070정성태12/9/20191212오류 유형: 581. resize2fs: Bad magic number in super-block while trying to open /dev/.../root
12069정성태12/19/2019835디버깅 기술: 139. windbg - x64 덤프 분석 시 메서드의 인자 또는 로컬 변수의 값을 확인하는 방법
12068정성태11/28/20191353디버깅 기술: 138. windbg와 Win32 API로 알아보는 Windows Heap 정보 분석 [2]파일 다운로드2
12067정성태11/27/2019835디버깅 기술: 137. 실제 사례를 통해 Debug Diagnostics 도구가 생성한 닷넷 웹 응용 프로그램의 성능 장애 보고서 설명 [1]파일 다운로드1
12066정성태11/27/2019591디버깅 기술: 136. windbg - C# PInvoke 호출 시 마샬링을 담당하는 함수 분석 - OracleCommand.ExecuteReader에서 OpsSql.Prepare2 PInvoke 호출 분석
12065정성태11/25/2019742디버깅 기술: 135. windbg - C# PInvoke 호출 시 마샬링을 담당하는 함수 분석파일 다운로드1
12064정성태11/25/20191058오류 유형: 580. HTTP Error 500.0/500.33 - ANCM In-Process Handler Load Failure
12063정성태11/21/2019776디버깅 기술: 134. windbg - RtlReportCriticalFailure로부터 parameters 정보 찾는 방법
1  2  3  4  5  6  7  [8]  9  10  11  12  13  14  15  ...