Poner en marcha un Microsoft SQL Server 2017 en Docker (Linux).
Aunque no sea una opción muy usada, pondremos a correr un Microsoft SQL Server 2017 en un contenedor, que lógicamente corre en Linux.
La solución parece poco común, cada mes que pasa lo veo mas claro, por distintas razones estamos comenzando a usar SQL Server sobre Linux, sea por una simplificación del mantenimiento, del costo de licencias, de facilitar el uso y otras.
Llevo un año probando SQL Server sobre distintos Linux en modo Virtual, ahora lo probaremos en un contenedor, donde el uso de recursos será aún menor que una V.M.
Según la página oficial, descargar la imágen
# docker pull microsoft/mssql-server-linux
Using default tag: latest
latest: Pulling from microsoft/mssql-server-linux
f6fa9a861b90: Pull complete
da7318603015: Pull complete
6a8bd10c9278: Pull complete
d5a40291440f: Pull complete
bbdd8a83c0f1: Pull complete
3a52205d40a6: Pull complete
6192691706e8: Pull complete
1a658a9035fb: Pull complete
fceab3b2d743: Pull complete
8b28cf1d590c: Pull complete
Digest: sha256:fcff69a0da62ae5ad8c1b56a0c9a6f5df42fabc848a9b7550cc20f85feac2f8a
Status: Downloaded newer image for microsoft/mssql-server-linux:latest
Para las medidas de imágenes que usamos (de unos pocos megas) en este caso 1.3 GB puede parecer mucho, si lo comparamos con los 50-80 GB que hacen falta para tener funcionando un SQL Server sobre un Windows…. es realmente muy poco.
# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
microsoft/mssql-server-linux latest db87203a2ae6 8 days ago 1.31GB
Ahora creamos el contenedor en base a esa imágen, en este caso debemos poner una clave seguro o no arrancará correctamente. (Testing!)
MS considera que la clave segura debe tener 8 o mas caracteres, Mayúsculas, Minúsculas, número y/ó caracter especial.
# docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Testing!' -p 1433:1433 -d microsoft/mssql-server-linux:2017-latest
Unable to find image 'microsoft/mssql-server-linux:2017-latest' locally
2017-latest: Pulling from microsoft/mssql-server-linux
f6fa9a861b90: Already exists
da7318603015: Already exists
6a8bd10c9278: Already exists
d5a40291440f: Already exists
bbdd8a83c0f1: Already exists
3a52205d40a6: Already exists
6192691706e8: Already exists
1a658a9035fb: Already exists
fceab3b2d743: Already exists
8b28cf1d590c: Already exists
Digest: sha256:a88f67985206a63f69d3785139aa70ab9613b53d2d28fe4ce5e14e172b76c690
Status: Downloaded newer image for microsoft/mssql-server-linux:2017-latest
88d305ba35959213981be97c2a54228535b140de6d82c1bad6f21db2d24093bf
Vemos claramente el consumo de recursos notoriamente mayor a los MySQLs, mientras que MariaDB 10.2 usa 92MB, SQL Server 2017 necesita 586mb.
# docker stats
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
d3f16f7859c1 mariadb10210 0.12% 92.8MiB / 7.685GiB 1.18% 6.93kB / 1.49kB 11.3MB / 2.22MB 30
f1e00cb00366 confident_hodgkin 2.26% 586.6MiB / 7.685GiB 7.45% 3.26kB / 0B 438MB / 53.7MB 138
Asignar 587 MB de ram a un contenedor con SQL Server sigue siendo menos que los 4gb recomendados por Microsoft (asignados al Windows).
Verificamos los puertos abiertos y solo vemos el 1433 (SQL Server)
# nmap 172.17.0.8
Starting Nmap 7.60 ( https://nmap.org ) at 2017-11-02 14:55 CET
Nmap scan report for 172.17.0.8
Host is up (0.000010s latency).
Not shown: 999 closed ports
PORT STATE SERVICE
1433/tcp open ms-sql-s
MAC Address: 02:42:AC:11:00:08 (Unknown)
Nmap done: 1 IP address (1 host up) scanned in 0.39 seconds
Ahora verificamos la conexión al servidor desde un cliente “SQLCMD” en Linux.
# sqlcmd -U sa -P "Testing!" -H 172.17.0.8
1> select @@version
2> go
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64)
Oct 19 2017 02:42:29
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)
(1 rows affected)
1>
Interactuando con el motor SQL Server.
# sqlcmd -U sa -P "Testing!" -H 172.17.0.8
1> SELECT DB_NAME(db.database_id) DatabaseName, (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB, (CAST(mflog.LogSize AS FLOAT)*8)/1024 ogSizeMB, (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB, (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB FROM sys.databases db LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id;
2> go;
DatabaseName RowSizeMB ogSizeMB StreamSizeMB TextIndexSizeMB
---------------------------- ------------------------ ------------------------ ------------------------
master 4.0 2.0 NULL NULL
tempdb 8.0 8.0 NULL NULL
model 8.0 8.0 NULL NULL
msdb 13.31 0.5 NULL NULL
(4 rows affected)
Mas ejemplos
1> use master;
2> go
Changed database context to 'master'.
1> exec sp_spaceused;
2> go
database_name database_size unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
master 6.00 MB 0.57 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
3512 KB 1440 KB 1576 KB 496 KB
Aunque suele funcionar, en algunos momentos y sin explicación, no logro conectarme al Motor SQL Server.
# sqlcmd -U sa -P "Testing!" -H 172.17.0.8
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x102.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Unos segundos después logro conectarme
# sqlcmd -U sa -P "Testing!" -H 172.17.0.8
1> exit;
2> go
Renombraremos el contenedor por algo mas “pnemotécnico” y creamos una carpeta para copias de archivos.
# docker container rename f1e00cb00366 sql1
# docker exec -it f1e00cb00366 sql1 /var/opt/mssql/backup
Copiamos un fichero local a la carpeta recién creada y verificamos que exista.
# docker cp 1.txt sql1:/var/opt/mssql/backup
# docker exec -it sql1 ls /var/opt/mssql/backup
1.txt
Verificamos las bases de datos que existen
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Testing!' -Q 'SELECT Name FROM sys.Databases'
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
(4 rows affected)
También logro hacer un backup completo de la base de datos “Master”.
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -Q "BACKUP DATABASE master TO DISK = N'/var/opt/mssql/backup/demodb.bak' WITH NOFORMAT, NOINIT, NAME = 'demodb-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
Password:
10 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
50 percent processed.
61 percent processed.
71 percent processed.
80 percent processed.
90 percent processed.
Processed 456 pages for database 'master', file 'master' on file 1.
100 percent processed.
Processed 3 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 459 pages in 0.208 seconds (17.240 MB/sec).
Ahora podemos ver el fichero generado (copia de base de datos “master”)
# docker exec -it sql1 ls -ail /var/opt/mssql/backup
total 3776
1624069 drwxr-xr-x 2 root root 4096 Nov 2 14:43 .
1430344 drwxr-xr-x 7 root root 4096 Nov 2 14:33 ..
11561672 -rw-r--r-- 1 root root 2 Nov 2 14:38 1.txt
11561673 -rw-r----- 1 root root 3854336 Nov 2 14:43 demodb.bak
Bajamos un fichero de ejemplo de internet “WideWorldImporters-Full.bak” y lo copiamos al espacio del contenedor.
# docker cp Wi*.bak sql1:/var/opt/mssql/backup
Y restaurar una base de datos de ejemplos.
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "Testing!" -Q "RESTORE DATABASE WideWorldImporters FROM DISK = '/var/opt/mssql/backup/WideWorldImporters-Full.bak' WITH MOVE 'WWI_Primary' TO '/var/opt/mssql/data/WideWorldImporters.mdf', MOVE 'WWI_UserData' TO '/var/opt/mssql/data/WideWorldImporters_userdata.ndf', MOVE 'WWI_Log' TO '/var/opt/mssql/data/WideWorldImporters.ldf', MOVE 'WWI_InMemory_Data_1' TO '/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1'"
Processed 1464 pages for database 'WideWorldImporters', file 'WWI_Primary' on file 1.
Processed 53096 pages for database 'WideWorldImporters', file 'WWI_UserData' on file 1.
Processed 33 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
Processed 3862 pages for database 'WideWorldImporters', file 'WWI_InMemory_Data_1' on file 1.
Converting database 'WideWorldImporters' from version 852 to the current version 869.
Database 'WideWorldImporters' running the upgrade step from version 852 to version 853.
Database 'WideWorldImporters' running the upgrade step from version 853 to version 854.
Database 'WideWorldImporters' running the upgrade step from version 854 to version 855.
Database 'WideWorldImporters' running the upgrade step from version 855 to version 856.
Database 'WideWorldImporters' running the upgrade step from version 856 to version 857.
Database 'WideWorldImporters' running the upgrade step from version 857 to version 858.
Database 'WideWorldImporters' running the upgrade step from version 858 to version 859.
Database 'WideWorldImporters' running the upgrade step from version 859 to version 860.
Database 'WideWorldImporters' running the upgrade step from version 860 to version 861.
Database 'WideWorldImporters' running the upgrade step from version 861 to version 862.
Database 'WideWorldImporters' running the upgrade step from version 862 to version 863.
Database 'WideWorldImporters' running the upgrade step from version 863 to version 864.
Database 'WideWorldImporters' running the upgrade step from version 864 to version 865.
Database 'WideWorldImporters' running the upgrade step from version 865 to version 866.
Database 'WideWorldImporters' running the upgrade step from version 866 to version 867.
Database 'WideWorldImporters' running the upgrade step from version 867 to version 868.
Database 'WideWorldImporters' running the upgrade step from version 868 to version 869.
RESTORE DATABASE successfully processed 58455 pages in 2.280 seconds (200.296 MB/sec).
Ahora ya tenemos una nueva base de datos (demo) recuperada en el SQL Server.
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Testing!' -Q 'SELECT Name FROM sys.Databases'
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
WideWorldImporters
(5 rows affected)
Podemos borrar los archivos que no necesitaremos mas.
# docker exec -it sql1 rm /var/opt/mssql/backup/WideWorldImporters-Full.bak
# docker exec -it sql1 rm /var/opt/mssql/backup/demodb.bak
Como en los demás contenedores restart y verificamos la persistencia de las bases de datos.
# docker restart sql1
# docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Testing!' -Q 'SELECT Name FROM sys.Databases'
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
WideWorldImporters
(5 rows affected)
Los recursos en uso ahora vuelven a ser menores.
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
8abcb524a17e mysql803 0.08% 151.1MiB / 7.685GiB 1.92% 18.7kB / 10.3kB 46.8MB / 17.4MB 28
3eeae33cde01 mysql5720 0.10% 120.2MiB / 7.685GiB 1.53% 7.26kB / 1.18kB 15.5MB / 12.7MB 28
331df765d34a mysql5638 0.09% 137.1MiB / 7.685GiB 1.74% 6.05kB / 0B 10.8MB / 49.2kB 22
f1773511204b mariadb5558 0.11% 85.09MiB / 7.685GiB 1.08% 6.44kB / 619B 8.31MB / 12.3kB 20
47b2b77cf25b mariadb10128 0.10% 86.68MiB / 7.685GiB 1.10% 10.1kB / 2.86kB 8.06MB / 69.6kB 27
d3f16f7859c1 mariadb10210 0.11% 89.69MiB / 7.685GiB 1.14% 8kB / 1.49kB 11.4MB / 2.22MB 30
f1e00cb00366 sql1 2.19% 659.1MiB / 7.685GiB 8.38% 3.26kB / 0B 936MB / 344MB 150
Hemos hecho varias verificaciones y la implementación de SQL Server en Contenedores no es perfecto, suele funcionar bien, aunque a veces hay demoras inexplicables y a veces no da servicio a clientes.
Continuaremos probando esta solución, para un Datacenter con cientos de servicios y clientes, estas soluciones pueden reducir mucho los recursos necesarios para cada servicio.
Ahora verificamos la nueva base de datos que hemos recuperado, nos servirá para jugar con ella.
# sqlcmd -U sa -P "Testing!" -H 172.17.0.8
1> ase_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id;
2> go
DatabaseName RowSizeMB ogSizeMB StreamSizeMB TextIndexSizeMB
----------------------------------------------- ------------------------ ---------------------------------
master 4.0 2.0 NULL NULL
tempdb 8.0 8.0 NULL NULL
model 8.0 8.0 NULL NULL
msdb 14.6875 0.75 NULL NULL
WideWorldImporters 3072.0 100.0 0.0 NULL
(5 rows affected)
Luego de estas pruebas, podemos verificar que estamos usando 9.8 gb de disco para Docker.
# du -h /var/lib/docker/
16G /var/lib/docker/
Espacios usados: 