Linux: Instalar SQL Server 2019

Raul Unzue Pulido 8 noviembre, 2020 Linux, RedHat, Windows 2 Comentarios 5,504 Vistas

Linux: Instalar SQL Server 2019

Hoy os vamos a explicar como instalar SQL Server 2019 en un sistema linux Centos 8 / RedHat 8.

Desde hace un tiempo, uno de los productos estrella de Microsoft, como es su gestor de base de datos SQL Server se integra con sistemas Linux. Esto está dentro del cambio de estrategia de Microsoft, que pasó de intentar matar Linux, a ser uno de los mayores aportadores de la comunidad Open. Algo que celebramos los que no nos gusta decantarnos por un solo sistema operativo.

Así que vamos a ello, os vamos a explicar como configurarlo sobre un Centos 8, en mi caso, que corre en un LXC de Proxmox.

Requerimientos SQL Server 2019 en Centos 8

Mínimo 3 GB de RAM
10 GB de disco para los ficheros de SQL
CPU mínima de 1,4GHz

Repositorios SQL Server 2019 en Centos 8

Como imaginaréis, no está disponible en los repositorios originales, así que tendremos que añadirlos al sistema operativo Centos 8:

curl https://packages.microsoft.com/config/rhel/8/mssql-server-2019.repo -o /etc/yum.repos.d/mssql-server-2019.repo
curl https://packages.microsoft.com/config/rhel/8/prod.repo -o /etc/yum.repos.d/msprod.repo

1 2	curl https://packages.microsoft.com/config/rhel/8/mssql-server-2019.repo -o /etc/yum.repos.d/mssql-server-2019.repo curl https://packages.microsoft.com/config/rhel/8/prod.repo -o /etc/yum.repos.d/msprod.repo

Instalación SQL Server 2019 en Centos 8

Para instalarlo, usaremos el comando:

dnf -y install mssql-server

Para poder manejar SQL, instalaremos también las Tools correspondientes en el sistema:

yum -y install mssql-tools unixODBC-devel

Tendremos que aceptar la licencia dos veces. Escribimos YES un par de veces + INTRO:

Configuración SQL Server en Centos 8

Ahora arrancamos la primera configuración:

/opt/mssql/bin/mssql-conf setup

Nos va a pedir que versión queremos instalar. En mi caso, la de Developer que es FREE:

Pulsamos 2 y YES:

Ahora nos pedirá las credenciales de nuestro administrador para SQL. Las introducimos para que comience el proceso de configuración inicial:

Nos devolverá, si todo ha salido bien, un resultado así:

Do you accept the license terms? [Yes/No]:Yes

Enter the SQL Server system administrator password: 
Confirm the SQL Server system administrator password: 
Configuring SQL Server...

ForceFlush is enabled for this instance. 
ForceFlush feature is enabled for log durability.
DBSTARTUP (master, 1): FileMgrPreRecoveryTime took 111 ms
DBSTARTUP (master, 1): PhysicalRecoveryTime took 133 ms
DBSTARTUP (master, 1): PhysicalCompletionTime took 215 ms
DBSTARTUP (master, 1): UpgradeTime took 11446 ms
DBSTARTUP (master, 1): StartupInDatabaseTime took 11656 ms
DBSTARTUP (master, 1): RemapSysfiles1Time took 108 ms
DBSTARTUP (msdb, 4): FCBHeaderReadTime took 110 ms
DBSTARTUP (msdb, 4): FileMgrPreRecoveryTime took 276 ms
DBSTARTUP (msdb, 4): PhysicalCompletionTime took 113 ms
DBSTARTUP (msdb, 4): RecoveryCompletionTime took 215 ms
DBSTARTUP (msdb, 4): UpgradeTime took 10845 ms
DBSTARTUP (msdb, 4): StartupInDatabaseTime took 11018 ms
DBSTARTUP (msdb, 4): RemapSysfiles1Time took 155 ms
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

Do you accept the license terms? [Yes/No]:Yes

Enter the SQL Server system administrator password:

Confirm the SQL Server system administrator password:

Configuring SQL Server...

ForceFlush is enabled for this instance.

ForceFlush feature is enabled for log durability.

DBSTARTUP (master, 1): FileMgrPreRecoveryTime took 111 ms

DBSTARTUP (master, 1): PhysicalRecoveryTime took 133 ms

DBSTARTUP (master, 1): PhysicalCompletionTime took 215 ms

DBSTARTUP (master, 1): UpgradeTime took 11446 ms

DBSTARTUP (master, 1): StartupInDatabaseTime took 11656 ms

DBSTARTUP (master, 1): RemapSysfiles1Time took 108 ms

DBSTARTUP (msdb, 4): FCBHeaderReadTime took 110 ms

DBSTARTUP (msdb, 4): FileMgrPreRecoveryTime took 276 ms

DBSTARTUP (msdb, 4): PhysicalCompletionTime took 113 ms

DBSTARTUP (msdb, 4): RecoveryCompletionTime took 215 ms

DBSTARTUP (msdb, 4): UpgradeTime took 10845 ms

DBSTARTUP (msdb, 4): StartupInDatabaseTime took 11018 ms

DBSTARTUP (msdb, 4): RemapSysfiles1Time took 155 ms

Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /usr/lib/systemd/system/mssql-server.service.

Setup has completed successfully. SQL Server is now starting.

Ahora simplemente realizamos pruebas básicas para ver que todo está en su sitio:

Comprobamos que el servicio está bien configurado:

[root@SQLSERVER01 ~]# systemctl status mssql-server.service 
● mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-11-08 11:13:07 UTC; 1min 38s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 892 (sqlservr)
    Tasks: 141
   Memory: 622.4M
   CGroup: /system.slice/mssql-server.service
           ├─892 /opt/mssql/bin/sqlservr
           └─908 /opt/mssql/bin/sqlservr

Nov 08 11:13:16 SQLSERVER01 sqlservr[892]: [155B blob data]
Nov 08 11:13:24 SQLSERVER01 sqlservr[892]: [96B blob data]
Nov 08 11:13:24 SQLSERVER01 sqlservr[892]: [66B blob data]
Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [75B blob data]
Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [96B blob data]
Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [100B blob data]
Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [71B blob data]
Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [124B blob data]
Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [73B blob data]
Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [186B blob data]

[root@SQLSERVER01 ~]# systemctl status mssql-server.service

● mssql-server.service - Microsoft SQL Server Database Engine

Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)

Active: active (running) since Sun 2020-11-08 11:13:07 UTC; 1min 38s ago

Docs: https://docs.microsoft.com/en-us/sql/linux

Main PID: 892 (sqlservr)

Tasks: 141

Memory: 622.4M

CGroup: /system.slice/mssql-server.service

├─892 /opt/mssql/bin/sqlservr

└─908 /opt/mssql/bin/sqlservr

Nov 08 11:13:16 SQLSERVER01 sqlservr[892]: [155B blob data]

Nov 08 11:13:24 SQLSERVER01 sqlservr[892]: [96B blob data]

Nov 08 11:13:24 SQLSERVER01 sqlservr[892]: [66B blob data]

Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [75B blob data]

Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [96B blob data]

Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [100B blob data]

Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [71B blob data]

Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [124B blob data]

Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [73B blob data]

Nov 08 11:13:29 SQLSERVER01 sqlservr[892]: [186B blob data]

Habilitamos que servicio arranque al inicio si no lo está:

[root@SQLSERVER01 ~]# systemctl is-enabled mssql-server.service 
enabled

[root@SQLSERVER01 ~]# systemctl enable mssql-server.service

[root@SQLSERVER01 ~]# systemctl is-enabled mssql-server.service

enabled

[root@SQLSERVER01 ~]# systemctl enable mssql-server.service

Incluímos /opt/mssql/bin/ a nuestro PATH:

[root@SQLSERVER01 ~]# echo 'export PATH=$PATH:/opt/mssql/bin:/opt/mssql-tools/bin' | tee /etc/profile.d/mssql.sh
export PATH=$PATH:/opt/mssql/bin:/opt/mssql-tools/bin

1 2	[root@SQLSERVER01 ~]# echo 'export PATH=$PATH:/opt/mssql/bin:/opt/mssql-tools/bin' \| tee /etc/profile.d/mssql.sh export PATH=$PATH:/opt/mssql/bin:/opt/mssql-tools/bin

Para poder ejecutar binarios en nuestra shell actual:

source /etc/profile.d/mssql.sh

1	source /etc/profile.d/mssql.sh

Si usáis firewall, que no es mi caso, abrimos las iptables:

firewall-cmd --add-port=1433/tcp --permanent
firewall-cmd --reload

1 2	firewall-cmd --add-port=1433/tcp --permanent firewall-cmd --reload

Y comenzamos los testeos básicos de puro SQL:
- - Comprobamos la conexión con nuestro administrador:
[root@SQLSERVER01 ~]# sqlcmd -S localhost -U SA Password: 1>

1
2
3

[root@SQLSERVER01 ~]# sqlcmd -S localhost -U SA
Password:
1>

- - Hacemos un select a los usuarios del sistema:

[root@SQLSERVER01 ~]# sqlcmd -S localhost -U SA
Password: 
1> select name from sysusers;
2> GO
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
##MS_AgentSigningCertificate##                                                                                                  
##MS_PolicyEventProcessingLogin##                                                                                               
db_accessadmin                                                                                                                  
db_backupoperator                                                                                                               
db_datareader                                                                                                                   
db_datawriter                                                                                                                   
db_ddladmin                                                                                                                     
db_denydatareader                                                                                                               
db_denydatawriter                                                                                                               
db_owner                                                                                                                        
db_securityadmin                                                                                                                
dbo                                                                                                                             
guest                                                                                                                           
INFORMATION_SCHEMA                                                                                                              
public                                                                                                                          
sys                                                                                                                             

(16 rows affected)

[root@SQLSERVER01 ~]# sqlcmd -S localhost -U SA

Password:

1> select name from sysusers;

2> GO

name

--------------------------------------------------------------------------------------------------------------------------------

##MS_AgentSigningCertificate##

##MS_PolicyEventProcessingLogin##

db_accessadmin

db_backupoperator

db_datareader

db_datawriter

db_ddladmin

db_denydatareader

db_denydatawriter

db_owner

db_securityadmin

dbo

guest

INFORMATION_SCHEMA

public

sys

(16 rows affected)

- - Generamos una base de datos de Prueba:

1> CREATE DATABASE elblogdenegu
2> SELECT Name from sys.Databases
3> GO
Name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
elblogdenegu                                                                                                                    

(5 rows affected)
1> USE elblogdenegu
2> CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
3> INSERT INTO Inventory VALUES (1, 'raul', 150); INSERT INTO Inventory VALUES (2, 'unzue', 154);
5> GO
Changed database context to 'elblogdenegu'.

(1 rows affected)

(1 rows affected)
1> SELECT * FROM Inventory LIMIT 1;

1> CREATE DATABASE elblogdenegu

2> SELECT Name from sys.Databases

3> GO

Name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

elblogdenegu

(5 rows affected)

1> USE elblogdenegu

2> CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)

3> INSERT INTO Inventory VALUES (1, 'raul', 150); INSERT INTO Inventory VALUES (2, 'unzue', 154);

5> GO

Changed database context to 'elblogdenegu'.

(1 rows affected)

1> SELECT * FROM Inventory LIMIT 1;

- - Comprobamos:

1> select name,database_id from sys.databases; 
2> GO
name                                                                                                                             database_id
-------------------------------------------------------------------------------------------------------------------------------- -----------
master                                                                                                                                     1
tempdb                                                                                                                                     2
model                                                                                                                                      3
msdb                                                                                                                                       4
elblogdenegu                                                                                                                               5

(5 rows affected)

1> select name,database_id from sys.databases;

2> GO

name database_id

-------------------------------------------------------------------------------------------------------------------------------- -----------

master 1

tempdb 2

model 3

msdb 4

elblogdenegu 5

(5 rows affected)

Espero os parezca interesante…

¿Te ha gustado la entrada SÍGUENOS EN TWITTER?

Follow @@elblogdenegu

�Te ha gustado la entrada S�GUENOS EN TWITTER O INVITANOS A UN CAFE?

Follow @@elblogdenegu

2 comentarios

Jorge
10 octubre, 2021 a 18:56

Muchas gracias por el artículo. Excelente lo instalé en minutos. Sigue así siempre entusiasta, que el éxito jamás te aburra

Responder
- Raul Unzue Pulido
  10 octubre, 2021 a 19:54
  
  Muchas gracias Jorge
  
  Responder