Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


quarta-feira, 9 de maio de 2018

Abordando a instalação e configuração do SQL Server no Linux

Por Eduardo Legatti

Olá,



A notícia que mais chamou a atenção no final de 2016 foi o anúncio da Microsoft sobre o lançamento do SQL Server para Linux. De lá pra cá, algumas coisas já mudaram. Por exemplo, a instalação só era permitida apenas em máquinas com no mínimo de 4 GB de RAM livre, o que era ridículo, pois hoje existem vários notebooks com 4 GB de RAM que que são utilizadas com máquinas virtuais para fins de estudos e testes de várias aplicações, inclusive bancos de dados. Atualmente a Microsoft atendeu aos vários pedidos da comunidade e reduziu a quantidade de memória RAM mínima para 2 GB. Portanto, neste artigo irei mostrar como realizar a instalação e configuração básica do SQL Server 2017 em um sistema Linux CentOS 7.4 x86_64.

Confirmando abaixo a versão e distribuição do Linux, irei realizar o download do repositório de instalação do SQL Server a partir do site da Microsoft usando o utilitário wget conforme a seguir.

[root@linux ~]# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)

[root@linux ~]# wget https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo -O /etc/yum.repos.d/mssql-server.repo
--2018-05-04 19:34:32--  https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
Resolving packages.microsoft.com (packages.microsoft.com)... 40.76.35.62
Connecting to packages.microsoft.com (packages.microsoft.com)|40.76.35.62|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 232 [application/octet-stream]
Saving to: /etc/yum.repos.d/mssql-server.repo

100%[=============================================================================================>] 232 --.-K/s   in 0s

2018-05-04 19:34:33 (2.60 MB/s) - /etc/yum.repos.d/mssql-server.repo saved [232/232]


[root@linux ~]# ls -lh /etc/yum.repos.d
total 32K
-rw-r--r--. 1 root root 1.7K Aug 30  2017 CentOS-Base.repo
-rw-r--r--. 1 root root 1.3K Aug 30  2017 CentOS-CR.repo
-rw-r--r--. 1 root root  649 Aug 30  2017 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root  314 Aug 30  2017 CentOS-fasttrack.repo
-rw-r--r--. 1 root root  630 Aug 30  2017 CentOS-Media.repo
-rw-r--r--. 1 root root 1.3K Aug 30  2017 CentOS-Sources.repo
-rw-r--r--. 1 root root 3.8K Aug 30  2017 CentOS-Vault.repo
-rw-r--r--. 1 root root  232 Sep 19  2017 mssql-server.repo

Após a realização do download, irei instalar o pacote mssql-server usando o utilitário yum conforme a seguir.

[root@linux ~]# yum install mssql-server -y
Loaded plugins: fastestmirror, langpacks
packages-microsoft-com-mssql-server-2017                                        | 2.9 kB  00:00:00
packages-microsoft-com-mssql-server-2017/primary_db                             |  11 kB  00:00:01
Loading mirror speeds from cached hostfile
 * base: centos.brisanet.com.br
 * extras: centos.brisanet.com.br
 * updates: centos.brisanet.com.br
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.3025.34-3 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================
 Package                 Arch     Version         Repository                                 Size
==================================================================================================
Installing:
 mssql-server            x86_64   14.0.3025.34-3  packages-microsoft-com-mssql-server-2017   167 M

Transaction Summary
==================================================================================================
Install  1 Package

Total download size: 167 M
Installed size: 167 M
Downloading packages:
Public key for mssql-server-14.0.3025.34-3.x86_64.rpm is not installed
mssql-server-14.0.3025.34-3.x86_64.rpm                                          | 167 MB  00:00:53
Retrieving key from https://packages.microsoft.com/keys/microsoft.asc
Importing GPG key 0xBE1229CF:
 Userid     : "Microsoft (Release signing) [gpgsecurity@microsoft.com]"
 Fingerprint: bc52 8686 b50d 79e3 39d3 721c eb3e 94ad be12 29cf
 From       : https://packages.microsoft.com/keys/microsoft.asc
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mssql-server-14.0.3025.34-3.x86_64                               1/1

+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
  Verifying  : mssql-server-14.0.3025.34-3.x86_64                               1/1

Installed:
  mssql-server.x86_64 0:14.0.3025.34-3

Complete!

Uma vez instalado o pacote, poderemos proceder com a configuração da instância do SQL Server conforme demonstrado abaixo. Após a execução do comando mssql-conf setup, irei selecionar a opção (1) e fornecer a senha do usuário sa como Aa12345678 para prosseguir com a configuração.

[root@linux ~]# /opt/mssql/bin/mssql-conf setup
Choose an edition of SQL Server:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID)
  7) Enterprise Core (PAID)
  8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 1
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855864&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

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

The licensing PID was successfully processed. The new edition is [Enterprise Evaluation Edition].
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
DBSTARTUP (msdb, 4): FCBOpenTime took 1026 ms
DBSTARTUP (msdb, 4): FCBHeaderReadTime took 705 ms
DBSTARTUP (msdb, 4): FileMgrPreRecoveryTime took 1570 ms
DBSTARTUP (msdb, 4): MasterFilesScanTime took 852 ms
DBSTARTUP (msdb, 4): PhysicalCompletionTime took 255 ms
DBSTARTUP (msdb, 4): RecoveryCompletionTime took 169 ms
DBSTARTUP (msdb, 4): UpgradeTime took 951 ms
DBSTARTUP (msdb, 4): StartupInDatabaseTime took 7426 ms
DBSTARTUP (msdb, 4): RemapSysfiles1Time took 108 ms
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

Pronto. Após a instalação e configuração do SQL Server, podemos verificar se o serviço foi iniciado.

[root@linux ~]# systemctl status mssql-server
* 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 Fri 2018-05-04 20:12:17 BST; 2min 0s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 1594 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           |-1594 /opt/mssql/bin/sqlservr
           |-1618 /opt/mssql/bin/sqlservr

May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.65 spid22s     The Service Broker endpoint is in disabled or stopped state.
May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.65 spid22s     The Database Mirroring endpoint is in disabled or stopped state.
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): FCBOpenTime took 1254 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): FCBHeaderReadTime took 632 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): FileMgrPreRecoveryTime took 1581 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): MasterFilesScanTime took 873 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): RecoveryCompletionTime took 118 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): StartupInDatabaseTime took 6707 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.73 spid22s     Service Broker manager has started.
May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.76 spid7s      Recovery is complete. This is an informational message only. No user action is required.

Caso o SQL Server precise ser acessado remotamente e o Firewall esteja habilitado no servidor, é importante liberar a porta 1433 como demonstrado abaixo. No meu caso, o serviço FirewallD não estava em execução e portanto não havia nenhuma regra de firewall ativa.

[root@linux ~]# firewall-cmd --zone=public --add-port=1433/tcp --permanent
FirewallD is not running
[root@linux ~]# firewall-cmd --reload
FirewallD is not running

[root@linux ~]# netstat -na | grep 1433
tcp        0      0 0.0.0.0:1433            0.0.0.0:*               LISTEN
tcp6       0      0 :::1433                 :::*                    LISTEN

Vale a penas salientar que a instalação do SQL Server não vem com alguns utilitários clientes "command-line tools" como o sqlcmd. Portanto, precisaremos realizar o download do pacote e realizar a instalação conforme demonstrado abaixo.

[root@linux ~]# wget https://packages.microsoft.com/config/rhel/7/prod.repo -O /etc/yum.repos.d/prod.repo
--2018-05-04 20:16:19--  https://packages.microsoft.com/config/rhel/7/prod.repo
Resolving packages.microsoft.com (packages.microsoft.com)... 40.76.35.62
Connecting to packages.microsoft.com (packages.microsoft.com)|40.76.35.62|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 193 [application/octet-stream]
Saving to: /etc/yum.repos.d/prod.repo

100%[=============================================================================================>] 193 --.-K/s   in 0s

2018-05-04 20:16:20 (2.16 MB/s) - /etc/yum.repos.d/prod.repo saved [193/193]

[root@linux ~]# ls -lh /etc/yum.repos.d
total 36K
-rw-r--r--. 1 root root 1.7K Aug 30  2017 CentOS-Base.repo
-rw-r--r--. 1 root root 1.3K Aug 30  2017 CentOS-CR.repo
-rw-r--r--. 1 root root  649 Aug 30  2017 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root  314 Aug 30  2017 CentOS-fasttrack.repo
-rw-r--r--. 1 root root  630 Aug 30  2017 CentOS-Media.repo
-rw-r--r--. 1 root root 1.3K Aug 30  2017 CentOS-Sources.repo
-rw-r--r--. 1 root root 3.8K Aug 30  2017 CentOS-Vault.repo
-rw-r--r--. 1 root root  232 Sep 19  2017 mssql-server.repo
-rw-r--r--. 1 root root  193 Nov 16  2016 prod.repo

Após a realização do download, irei instalar os pacotes mssql-tools e unixODBC-devel aceitando os termos de licença conforme a seguir.

[root@linux ~]# yum install -y mssql-tools unixODBC-devel
Loaded plugins: fastestmirror, langpacks
packages-microsoft-com-prod                                                     | 2.9 kB  00:00:00
packages-microsoft-com-prod/primary_db                                          |  89 kB  00:00:01
Loading mirror speeds from cached hostfile
 * base: centos.brisanet.com.br
 * extras: centos.brisanet.com.br
 * updates: centos.brisanet.com.br
Package unixODBC-devel-2.3.1-11.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:17.1.0.1-1 will be installed
--> Processing Dependency: msodbcsql17 < 17.2.0.0 for package: mssql-tools-17.1.0.1-1.x86_64
--> Processing Dependency: msodbcsql17 >= 17.1.0.1 for package: mssql-tools-17.1.0.1-1.x86_64
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.1.0.1-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===================================================================================================
 Package                      Arch     Version     Repository                                  Size
===================================================================================================
Installing:
 mssql-tools                  x86_64   17.1.0.1-1  packages-microsoft-com-prod                253 k
Installing for dependencies:
 msodbcsql17                  x86_64   17.1.0.1-1  packages-microsoft-com-prod                4.1 M

Transaction Summary
====================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 4.4 M
Installed size: 4.4 M
Downloading packages:
(1/2): mssql-tools-17.1.0.1-1.x86_64.rpm                                        | 253 kB  00:00:01
(2/2): msodbcsql17-17.1.0.1-1.x86_64.rpm                                        | 4.1 MB  00:00:06
----------------------------------------------------------------------------------------------------
Total                                                                    678 kB/s | 4.4 MB  00:00:06
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
The license terms for this product can be downloaded from
https://aka.ms/odbc170eula and found in
/usr/share/doc/msodbcsql17/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
  Installing : msodbcsql17-17.1.0.1-1.x86_64                                    1/2
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746949 and found in
/usr/share/doc/mssql-tools/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
  Installing : mssql-tools-17.1.0.1-1.x86_64                                    2/2
  Verifying  : msodbcsql17-17.1.0.1-1.x86_64                                    1/2
  Verifying  : mssql-tools-17.1.0.1-1.x86_64                                    2/2

Installed:
  mssql-tools.x86_64 0:17.1.0.1-1

Dependency Installed:
  msodbcsql17.x86_64 0:17.1.0.1-1

Complete!

Pronto. Para poder executar os utilitários de linha de comandos de qualquer diretório, precisaremos adicionar o caminho abaixo na variável PATH.

[root@linux ~]# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
[root@linux ~]# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
[root@linux ~]# source ~/.bashrc

[root@linux ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/opt/mssql-tools/bin

Para iniciar o SQL Server Agent, siga os procedimentos abaixo.

[root@linux ~]# /opt/mssql/bin/mssql-conf set sqlagent.enabled true
SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'.
[root@linux ~]# systemctl restart mssql-server
 
Agora poderemos testar a conexão com a instância do SQL Server.

[root@linux ~]# sqlcmd -S localhost -U SA -PAa12345678
1> select @@version;
2> GO

-------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64)
        Apr  9 2018 18:00:41
        Copyright (C) 2017 Microsoft Corporation
        Enterprise Evaluation Edition (64-bit) on Linux (CentOS Linux 7 (Core))

(1 rows affected)

1> select database_id,name,file_id,name,physical_name from sys.master_files;
2> GO
database_id name          file_id     name       physical_name
----------- ------------- ----------- ---------- ----------------------------------
          1 master                  1 master     /var/opt/mssql/data/master.mdf
          1 mastlog                 2 mastlog    /var/opt/mssql/data/mastlog.ldf
          2 tempdev                 1 tempdev    /var/opt/mssql/data/tempdb.mdf
          2 templog                 2 templog    /var/opt/mssql/data/templog.ldf
          3 modeldev                1 modeldev   /var/opt/mssql/data/model.mdf
          3 modellog                2 modellog   /var/opt/mssql/data/modellog.ldf
          4 MSDBData                1 MSDBData   /var/opt/mssql/data/MSDBData.mdf
          4 MSDBLog                 2 MSDBLog    /var/opt/mssql/data/MSDBLog.ldf

(8 rows affected)

Para realizar o shutdown da instância do SQL Server, basta apenas executar o comando stop.

[root@linux ~]# systemctl stop mssql-server

Por fim, para se certificar de que o serviço foi finalizado, poderemos executar novamente o comando status.

[root@linux ~]# systemctl status mssql-server
* mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Fri 2018-05-04 20:40:31 BST; 4s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 1594 (code=exited, status=0/SUCCESS)

May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): StartupInDatabaseTime took 6707 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.73 spid22s     Service Broker manager has started.
May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.76 spid7s      Recovery is complete. This is an informational message only. No user action is required.
May 04 20:17:49 linux.local.net sqlservr[1594]: 2018-05-04 20:17:49.69 spid51      Using 'dbghelp.dll' version '4.0.5'
May 04 20:40:29 linux.local.net systemd[1]: Stopping Microsoft SQL Server Database Engine...
May 04 20:40:29 linux.local.net sqlservr[1594]: 2018-05-04 20:40:29.60 spid7s      Always On: The availability replica manager is going offline because SQL Server is shutting down. This is an informational message only...ion is required.
May 04 20:40:29 linux.local.net sqlservr[1594]: 2018-05-04 20:40:29.61 spid7s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
May 04 20:40:29 linux.local.net sqlservr[1594]: 2018-05-04 20:40:29.74 spid22s     Service Broker manager has shut down.
May 04 20:40:29 linux.local.net sqlservr[1594]: 2018-05-04 20:40:29.94 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
May 04 20:40:31 linux.local.net systemd[1]: Stopped Microsoft SQL Server Database Engine.
Hint: Some lines were ellipsized, use -l to show in full.



2 comentários:

Eduardo AF disse...

Parabéns Eduardo! Como sempre objetivo, assertivo e esclarecedor!

Eduardo Legatti disse...

Olá Eduardo,

Obrigado pela visita!!

Abraços,

Legatti

Postagens populares