Linux Docker SQL Server ArcSDE Geodatabases
_________________________________________________________________
Running a Linux Docker container with SQL Server for the ArcSDE Geodatabase is an effective way to manage spatial data environments. This setup allows for enhanced functionality and optimized performance in geographic information systems (GIS) applications.
_________________________________________________________________
--1. Install Docker on Linux
Linux Docker Installation - Esri Community
_________________________________________________________________
--2. SQL Server on Docker
https://www.sqlservercentral.com/articles/docker-desktop-on-windows-10-for-sql-server-step-by-step
https://www.avonture.be/blog/docker-mssql-server/
_________________________________________________________________
--2.1 docker sqlserver image
https://mcr.microsoft.com/artifact/mar/mssql/server/about
docker image ls
Ubuntu Linux: https://mcr.microsoft.com/artifact/mar/mssql/server/about
curl https://mcr.microsoft.com/v2/mssql/server/tags/list
curl https://mcr.microsoft.com/v2/mssql/server/tags/list | jq '.tags[]'
docker pull mcr.microsoft.com/mssql/server:2022-latest
docker pull mcr.microsoft.com/mssql/server:2022-CU19-ubuntu-22.04
docker pull mcr.microsoft.com/mssql/server:2022-CU18-ubuntu-22.04
RedHat Linux: https://mcr.microsoft.com/en-us/artifact/mar/mssql/rhel/server/about
curl https://mcr.microsoft.com/v2/mssql/rhel/server/tags/list
curl https://mcr.microsoft.com/v2/mssql/rhel/server/tags/list | jq '.tags[]'
docker pull mcr.microsoft.com/mssql/rhel/server:2022-latest
docker pull mcr.microsoft.com/mssql/rhel/server:2022-CU19-rhel-9.1
docker pull mcr.microsoft.com/mssql/rhel/server:2022-CU18-rhel-9.1
_________________________________________________________________
--2.2 docker container sqlserver
--create volume
docker volume ls
docker volume rm sql-db-22-data
docker volume create sql-db-22-data
docker volume rm sql-db-22-tbs
docker volume create sql-db-22-tbs
--NOTES:
--1. must follow the exactly order for the parameters or it fails to create the container!!!
--2. invalid value for parameter "port": "1401:1433"
--3. the -p option must come before the image name
Examples:
/*
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" \
-p 1433:1433 --name sql1 --hostname sql1 \
-d mcr.microsoft.com/mssql/server:2022-latest
*/
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" \
-p 1433:1433 --name sql-db-22 --hostname sql-db-22 \
-d mcr.microsoft.com/mssql/rhel/server:2022-CU18-rhel-9.1
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" \
-p 1401:1433 --name sql-db-22 --hostname sql-db-22 \
-d mcr.microsoft.com/mssql/rhel/server:2022-CU18-rhel-9.1
-The system databases like master, model, msdb, and tempdb are created in the container's file system under "/var/opt/mssql/data/".
--Persisting Data
docker volume rm sql-db-22-data
docker volume create sql-db-22-data
docker volume rm sql-db-22-tbs
docker volume create sql-db-22-tbs
docker volume ls
/*
-v sql-db-22-data:/var/opt/mssql mounts the volume to the SQL Server data directory.
This includes /var/opt/mssql/data, /log, and /secrets.
-v sql-db-22-tbs:/var/opt/mssql/tbs mounts the volume to store SQL Server user database filegroups.
*/
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" \
-p 1401:1433 --name sql-db-22 --hostname sql-db-22 --restart=always \
-v sql-db-22-data:/var/opt/mssql \
-v sql-db-22-tbs:/var/opt/mssql/tbs \
-d mcr.microsoft.com/mssql/rhel/server:2022-CU18-rhel-9.1
--(Optional): mount a host directory into the container for sqlserver backups
--server:2022-CU18-rhel-9.1
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" \
-p 1401:1433 --name sql-db-22 --hostname sql-db-22 --restart=always \
-v sql-db-22-data:/var/opt/mssql \
-v sql-db-22-tbs:/var/opt/mssql/tbs \
-v /dockerhome/sqlserver/backups/sql-db-22:/var/opt/mssql/backups \
-d mcr.microsoft.com/mssql/rhel/server:2022-CU18-rhel-9.1
--server:2022-CU19-rhel-9.1
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" \
-p 1401:1433 --name sql-db-22 --hostname sql-db-22 --restart=always \
-v sql-db-22-data:/var/opt/mssql \
-v sql-db-22-tbs:/var/opt/mssql/tbs \
-v /dockerhome/sqlserver/backups/sql-db-22:/var/opt/mssql/backups \
-d mcr.microsoft.com/mssql/rhel/server:2022-CU19-rhel-9.1
--set the permissions for the folders in the docker container
docker exec -it sql-db-22 bash
chown -R mssql:mssql /var/opt/mssql/backups
--chown: changing ownership of '/var/opt/mssql/backups': Operation not permitted
chown -R mssql:mssql /var/opt/mssql/tbs
--chown: changing ownership of '/var/opt/mssql/tbs': Operation not permitted
ls -la /var/opt/mssql/
exit
/*
NOTE: sudo: command not found error because you're inside the container,
and most minimal Docker images (like the SQL Server RHEL image) do not include sudo
and you're already logged in as the mssql user, which doesn't have root privileges.
*/
Solution A: Connect to the container as root
docker exec -it -u root sql-db-22 bash
Solution B: Change Ownership from the Host
docker exec -it sql-db-22 bash
whoami
--mssql
id -a mssql
--uid=10001(mssql) gid=10001(mssql) groups=10001(mssql)
exit
docker volume inspect sql-db-22-tbs
"Mountpoint": "/dockerhome/dockerdata/volumes/sql-db-22-tbs/_data"
docker volume inspect sql-db-22-data
"Mountpoint": "/dockerhome/dockerdata/volumes/sql-db-22-data/_data"
Change ownership on the host
sudo chown -R 10001:10001 /dockerhome/dockerdata/volumes/sql-db-22-tbs/_data
sudo chown -R 10001:10001 /dockerhome/dockerdata/volumes/sql-db-22-data/_data
sudo chown -R 10001:10001 /dockerhome/sqlserver/backups/sql-db-22
docker exec -it sql-db-22 bash
ls -la /var/opt/mssql/
exit
--check the docker container logs for errors.
docker logs --tail 50 sql-db-22
--list the docker containers
docker ps -a
docker inspect 5b72d963060b | grep IPAddress
IPAddress": "172.17.0.2"
docker port sql-db-22
docker logs sql-db-22
docker logs --tail sql-db-22
docker logs -f sql-db-22
docker exec -t sql-db-22 cat /var/opt/mssql/log/errorlog | grep connection
docker stop sql-db-22
docker start sql-db-22
docker restart sql-db-22
_________________________________________________________________
--2.3 Change the system administrator password
/*
docker exec -it sql1 /opt/mssql-tools18/bin/sqlcmd \
-S localhost -U sa \
-P "$(read -sp "Enter current SA password: "; echo "${REPLY}")" \
-Q "ALTER LOGIN sa WITH PASSWORD=\"$(read -sp "Enter new SA password: "; echo "${REPLY}")\""
*/
docker exec -it sql-db-22 /opt/mssql-tools18/bin/sqlcmd \
-S localhost -U sa \
-P "$(read -sp "Enter current SA password: "; echo "${REPLY}")" \
-Q "ALTER LOGIN sa WITH PASSWORD=\"$(read -sp "Enter new SA password: "; echo "${REPLY}")\""
_________________________________________________________________
--2.4 docker container remove
docker stop sql-db-22
docker rm sql-db-22
_________________________________________________________________
--2.5 docker container terminal
docker exec -it sql-db-22 sh
docker exec -it sql-db-22 bash
docker exec -it -u root sql-db-22 bash
docker exec -it -u mssql sql-db-22 bash
_________________________________________________________________
--2.6 docker container sqlcmd
docker exec -it sql-db-22 /opt/mssql-tools18/bin/sqlcmd -S localhost \
-U sa -P "WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg"
/*
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server :
SSL Provider: [error:0A000086:SSL routines::certificate verify
failed:self-signed certificate].
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server :
Client unable to establish connection. For solutions related to encryption errors,
see https://go.microsoft.com/fwlink/?linkid=2226722.
*/
--If the connection doesn't succeed, you can add the -No option to sqlcmd to specify that encryption is optional, not mandatory.
docker exec -it sql-db-22 /opt/mssql-tools18/bin/sqlcmd -S localhost \
-U sa -P "WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" -No
SELECT @@SERVERNAME;
GO
SELECT @@SERVERNAME,SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
SERVERPROPERTY('MachineName'), SERVERPROPERTY('ServerName');
GO
quit
exit
_________________________________________________________________
--2.7 connect from remote machine using sqlcmd
sqlcmd -?
Linux: sqlcmd -S ps026300,1401 -U sa -P "WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" -No
Windows: sqlcmd -S ps026300,1401 -U sa -P "WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" (works!!!)
_________________________________________________________________
--2.8 sqlserver parameters
docker exec -it sql-db-22 /opt/mssql-tools18/bin/sqlcmd -S localhost \
-U sa -P "WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" -No
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'user connections', 500; -- Set to desired number
RECONFIGURE;
quit
exit
docker restart sql-db-22
_________________________________________________________________
--2.9 docker container upgrade from mssql-2022-CU18 to mssql-2022-CU19
--1. stop the container
docker stop sql-db-22
docker ps -a
--2. keep the docker volumes that were used to create the container
docker volume ls
--the volume sql-db-22-data has the mssql master, model, msdb, temp databases
--the volume sql-db-22-tbs has the mssql databases filegroups
--3. drop the old docker container running pg_16.8
docker rm sql-db-22
docker ps -a
--4. recreate the docker container using the mssql-2022-CU19 image
--server:2022-CU19-rhel-9.1
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" \
-p 1401:1433 --name sql-db-22 --hostname sql-db-22 --restart=always \
-v sql-db-22-data:/var/opt/mssql \
-v sql-db-22-tbs:/var/opt/mssql/tbs \
-v /dockerhome/sqlserver/backups/sql-db-22:/var/opt/mssql/backups \
-d mcr.microsoft.com/mssql/rhel/server:2022-CU19-rhel-9.1
docker ps -a
docker logs --tail 50 sql-db-22
docker exec -t sql-db-22 cat /var/opt/mssql/log/errorlog | grep connection
--NOTES: using the same volumes, all the data will be preserved
--5. check the sqlserver version
docker exec -it sql-db-22 /opt/mssql-tools18/bin/sqlcmd -S localhost \
-U sa -P "WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" -No
or
Windows: sqlcmd -S ps026300,1401 -U sa -P "WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg"
select @@version;
go
--6. set the permissions for the folders in the docker container
docker exec -it -u root sql-db-22 bash
chown -R mssql:mssql /var/opt/mssql/backups
--chown: changing ownership of '/var/opt/mssql/backups': Operation not permitted
chown -R mssql:mssql /var/opt/mssql/tbs
--chown: changing ownership of '/var/opt/mssql/tbs': Operation not permitted
ls -la /var/opt/mssql/
exit
/*
NOTE: sudo: command not found error because you're inside the container,
and most minimal Docker images (like the SQL Server RHEL image) do not include sudo
and you're already logged in as the mssql user, which doesn't have root privileges.
*/
Solution A: Connect to the container as root
docker exec -it -u root sql-db-22 bash
Solution B: Change Ownership from the Host
docker exec -it sql-db-22 bash
whoami
--mssql
id -a mssql
--uid=10001(mssql) gid=10001(mssql) groups=10001(mssql)
exit
docker volume inspect sql-db-22-tbs
"Mountpoint": "/dockerhome/dockerdata/volumes/sql-db-22-tbs/_data"
Change ownership on the host
sudo chown -R 10001:10001 /dockerhome/dockerdata/volumes/sql-db-22-tbs/_data
sudo chown -R 10001:10001 /dockerhome/sqlserver/backups/sql-db-22
docker exec -it sql-db-22 bash
ls -la /var/opt/mssql/
exit
_________________________________________________________________
--3. docker container Enable SQL Server Agent
--1. Connect to the container as root
docker exec -it -u root sql-db-22 bash
--2. Enable SQL Server Agent
--Inside the container, run:
/opt/mssql/bin/mssql-conf set sqlagent.enabled true
--You may see a message like:
--"SQL Server needs to be restarted in order to apply this setting."
--Ignore any mention of systemctl — it's not available in containers.
--3. Exit the container
exit
--4. Restart the container
docker restart sql-db-22
docker logs --tail 50 sql-db-22
--5. Verify Agent is Running
--After restart, connect to SQL Server and run:
docker exec -it sql-db-22 /opt/mssql-tools18/bin/sqlcmd -S localhost \
-U sa -P "WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg" -No
Windows: sqlcmd -S ps026300,1401 -U sa -P "WCf5zhyueRLw72468mEvUSYDrxpF9kMGBX3tg"
SELECT servicename, status_desc
FROM sys.dm_server_services;
exit
_________________________________________________________________
--4. SQLServer ArcSDE Geodatabases Best Practices
Use the database guide books and database templates scripts best practices to create the SQL Server ArcSDE Geodatabase.
Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices
- FAQ - Frequent Asked Questions
- Guide Books ArcGIS Pro
- Database Connections Best Practices
- Database Template Scripts
*For Professional Database Administrators, requires advanced RDBMS and advanced Geodatabase experience.
_________________________________________________________________
--5. SQL Server Alerts, Database Maintenance, Database Backups and Database Restore
You can download the database template scripts for SQL Server from the link below, there you will find the scripts for SQL Server on Linux to configure the alerts, maintenance jobs, database backups via the SQL Server Agent.
- Database Template Scripts
SQL Server 2022 / 2019 / 2017 / 2016 (Windows/Linux)
ArcGIS Pro 3.x & ArcMap 10.8.x June 21, 2022...\db_sqlserver_sde_admin_mcs\db_admin\3_db_backup\
1_alerts_all_platforms
2_windows
3_linux
1_db_store_procedures.sql
2_db_jobs.sql
This white paper is intended for database administrators as recommendations to move the SQL Server Enterprise Geodatabase with a Database Backup.
How to Move the SQL Server Enterprise Geodatabase with a database backup--Database Backup Example:
BACKUP DATABASE [pm] TO DISK = N'/var/opt/mssql/backups/pm/pm_full_bkp.Fbkp'
WITH FORMAT, INIT, NAME = N'pm-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
COMPRESSION, ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [db_cert]),
STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'pm'
and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=N'pm' )
if @backupSetId is null begin raiserror(N'Verify failed.
Backup information for database ''pm'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'/var/opt/mssql/backups/pm/pm_full_bkp.Fbkp'
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
