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:
Parabéns Eduardo! Como sempre objetivo, assertivo e esclarecedor!
Olá Eduardo,
Obrigado pela visita!!
Abraços,
Legatti
Postar um comentário