Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Monday, January 29, 2018

Install SQL Server 2017 on Linux [RHEL]


SQL Server 2017 now runs on Linux. It’s the same SQL Server database engine, with many similar features and services regardless of your operating system.

I'm providing below the straight away installation for it.


Install SQL Server 2017
****************************
1- Download the Microsoft SQL Server Red Hat repository configuration file. This is the Cumulative Update (CU) repository.

[root@te1-hdp-rp-dn04 ~]# sudo curl -o /etc/yum.repos.d/mssql-server.repo 

https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   232  100   232    0     0    241      0 --:--:-- --:--:-- --:--:--   241

2- Run the following commands to install SQL Server:

[root@te1-hdp-rp-dn04 ~]# yum install -y mssql-server
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
HDF-3.0                                                                                                                   | 2.9 kB  00:00:00
HDP-2.6                                                                                                                   | 2.9 kB  00:00:00
HDP-UTILS-1.1.0.21                                                                                                        | 2.9 kB  00:00:00
ambari-2.5.0.3                                                                                                            | 2.9 kB  00:00:00
cassandra/signature                                                                                                       |  819 B  00:00:00
cassandra/signature                                                                                                       | 2.9 kB  00:00:00 !!!
mysql-connectors-community                                                                                                | 2.5 kB  00:00:00
mysql-tools-community                                                                                                     | 2.5 kB  00:00:00
mysql57-community                                                                                                         | 2.5 kB  00:00:00
packages-microsoft-com-mssql-server-2017                                                                                  | 2.9 kB  00:00:00
rhel-7-server-optional-rpms                                                                                               | 3.5 kB  00:00:00
rhel-7-server-rpms                                                                                                        | 3.5 kB  00:00:00
treasuredata                                                                                                              | 2.9 kB  00:00:00
(1/6): mysql57-community/x86_64/primary_db                                                                                | 134 kB  00:00:00
(2/6): packages-microsoft-com-mssql-server-2017/primary_db                                                                | 8.0 kB  00:00:00
(3/6): rhel-7-server-optional-rpms/7Server/x86_64/updateinfo                                                              | 1.8 MB  00:00:04
(4/6): rhel-7-server-rpms/7Server/x86_64/updateinfo                                                                       | 2.5 MB  00:00:04
(5/6): rhel-7-server-optional-rpms/7Server/x86_64/primary_db                                                              | 6.2 MB  00:00:13
(6/6): rhel-7-server-rpms/7Server/x86_64/primary_db                                                                       |  47 MB  00:01:24
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.3015.40-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================
 Package                     Arch                  Version                         Repository                                               Size
=================================================================================================================================================
Installing:
 mssql-server                x86_64                14.0.3015.40-1                  packages-microsoft-com-mssql-server-2017                166 M

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

Total download size: 166 M
Installed size: 166 M
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/packages-microsoft-com-mssql-server-2017/packages/mssql-server-14.0.3015.40-1.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY
Public key for mssql-server-14.0.3015.40-1.x86_64.rpm is not installed
mssql-server-14.0.3015.40-1.x86_64.rpm                                                                                    | 166 MB  00:05:11
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.3015.40-1.x86_64                                                                                            1/1

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

  Verifying  : mssql-server-14.0.3015.40-1.x86_64                                                                                            1/1

Installed:
  mssql-server.x86_64 0:14.0.3015.40-1

Complete!

3- After the package installation finishes, run mssql-conf setup and follow the prompts to set the SA password and choose your edition. the following editions are freely licensed: 

Evaluation, Developer, and Express.


Make sure to specify a strong password for the SA account (Minimum length 8 characters, including uppercase and lowercase letters, base 10 digits and/or non-alphanumeric symbols).

[root@te1-hdp-rp-dn04 ~]# /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): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409

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

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...

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.


4- Once the configuration is done, verify that the service is running:

[root@te1-hdp-rp-dn04 ~]# 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 Mon 2018-01-29 10:50:26 AST; 32s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 18923 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           ├─18923 /opt/mssql/bin/sqlservr
           └─18943 /opt/mssql/bin/sqlservr

Jan 29 10:50:31 te1-hdp-rp-dn04 sqlservr[18923]: 2018-01-29 10:50:31.19 spid7s      8 transactions rolled forward in database 'msdb' (4...quired.
Jan 29 10:50:31 te1-hdp-rp-dn04 sqlservr[18923]: 2018-01-29 10:50:31.22 spid13s     Polybase feature disabled.
Jan 29 10:50:31 te1-hdp-rp-dn04 sqlservr[18923]: 2018-01-29 10:50:31.22 spid13s     Clearing tempdb database.
Jan 29 10:50:31 te1-hdp-rp-dn04 sqlservr[18923]: 2018-01-29 10:50:31.22 spid7s      0 transactions rolled back in database 'msdb' (4:0)...quired.
Jan 29 10:50:32 te1-hdp-rp-dn04 sqlservr[18923]: 2018-01-29 10:50:32.29 spid13s     Starting up database 'tempdb'.
Jan 29 10:50:32 te1-hdp-rp-dn04 sqlservr[18923]: 2018-01-29 10:50:32.66 spid13s     The tempdb database has 1 data file(s).
Jan 29 10:50:32 te1-hdp-rp-dn04 sqlservr[18923]: 2018-01-29 10:50:32.68 spid24s     The Service Broker endpoint is in disabled or stopped state.
Jan 29 10:50:32 te1-hdp-rp-dn04 sqlservr[18923]: 2018-01-29 10:50:32.68 spid24s     The Database Mirroring endpoint is in disabled or s... state.
Jan 29 10:50:32 te1-hdp-rp-dn04 sqlservr[18923]: 2018-01-29 10:50:32.70 spid24s     Service Broker manager has started.
Jan 29 10:50:32 te1-hdp-rp-dn04 sqlservr[18923]: 2018-01-29 10:50:32.74 spid7s      Recovery is complete. This is an informational mess...quired.
Hint: Some lines were ellipsized, use -l to show in full.


5- To allow remote connections, open the SQL Server port on the firewall on RHEL. The default SQL Server port is TCP 1433.

[root@te1-hdp-rp-dn04 ~]# firewall-cmd --zone=public --add-port=1433/tcp --permanent
FirewallD is not running



At this point, SQL Server is running on your RHEL machine and is ready to use!

Install the SQL Server command-line tools
***************************************************
1- Download the Microsoft Red Hat repository configuration file.

[root@te1-hdp-rp-dn04 ~]# sudo curl -o /etc/yum.repos.d/msprod.repo 

https://packages.microsoft.com/config/rhel/7/prod.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   193  100   193    0     0    244      0 --:--:-- --:--:-- --:--:--   243


2- If you had a previous version of mssql-tools installed, remove any older unixODBC packages.

[root@te1-hdp-rp-dn04 ~]# yum remove unixODBC-utf16 unixODBC-utf16-devel

Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
No Match for argument: unixODBC-utf16
No Match for argument: unixODBC-utf16-devel
No Packages marked for removal


3- Run the following commands to install mssql-tools with the unixODBC developer package.

[root@te1-hdp-rp-dn04 ~]# yum install -y mssql-tools unixODBC-devel
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
packages-microsoft-com-prod                                                                                               | 2.9 kB  00:00:00
packages-microsoft-com-prod/primary_db                                                                                    |  43 kB  00:00:00
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:14.0.6.0-1 will be installed
--> Processing Dependency: msodbcsql < 13.2.0.0 for package: mssql-tools-14.0.6.0-1.x86_64
--> Processing Dependency: msodbcsql >= 13.1.0.0 for package: mssql-tools-14.0.6.0-1.x86_64
--> Running transaction check
---> Package msodbcsql.x86_64 0:13.1.9.2-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================
 Package                         Arch                       Version                        Repository                                       Size
=================================================================================================================================================
Installing:
 mssql-tools                     x86_64                     14.0.6.0-1                     packages-microsoft-com-prod                     249 k
Installing for dependencies:
 msodbcsql                       x86_64                     13.1.9.2-1                     packages-microsoft-com-prod                     4.0 M

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

Total download size: 4.2 M
Installed size: 4.2 M
Downloading packages:
(1/2): mssql-tools-14.0.6.0-1.x86_64.rpm                                                                                  | 249 kB  00:00:01
(2/2): msodbcsql-13.1.9.2-1.x86_64.rpm                                                                                    | 4.0 MB  00:00:08
-------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                            536 kB/s | 4.2 MB  00:00:08
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
The license terms for this product can be downloaded from
https://aka.ms/odbc131eula and found in
/usr/share/doc/msodbcsql/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 : msodbcsql-13.1.9.2-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-14.0.6.0-1.x86_64                                                                                                 2/2
  Verifying  : mssql-tools-14.0.6.0-1.x86_64                                                                                                 1/2
  Verifying  : msodbcsql-13.1.9.2-1.x86_64                                                                                                   2/2

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

Dependency Installed:
  msodbcsql.x86_64 0:13.1.9.2-1

Complete!



4- For convenience, add /opt/mssql-tools/bin/ to your PATH environment variable. This enables you to run the tools without specifying the full path. Run the following commands to modify the PATH for both login sessions and interactive/non-login sessions:

[root@te1-hdp-rp-dn04 ~]# echo 'export PATH=$PATH:/opt/mssql-tools/bin' >> ~/.bash_profile
[root@te1-hdp-rp-dn04 ~]# .source ~/.bash_profile



Install SQL Server Agent 
*******************************
The following steps install SQL Server Agent (mssql-server-agent) on Linux. The SQL Server Agent runs scheduled SQL Server jobs.

[root@te1-hdp-rp-dn04 ~]# yum install mssql-server-agent
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
Resolving Dependencies
--> Running transaction check
---> Package mssql-server-agent.x86_64 0:14.0.3015.40-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================
 Package                          Arch                 Version                      Repository                                              Size
=================================================================================================================================================
Installing:
 mssql-server-agent               x86_64               14.0.3015.40-1               packages-microsoft-com-mssql-server-2017               1.5 M

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

Total download size: 1.5 M
Installed size: 1.5 M
Is this ok [y/d/N]: y
Downloading packages:
mssql-server-agent-14.0.3015.40-1.x86_64.rpm                                                                              | 1.5 MB  00:00:05
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mssql-server-agent-14.0.3015.40-1.x86_64                                                                                      1/1

+--------------------------------------------------------------------------------+
Please restart mssql-server to enable Microsoft SQL Server Agent.
+--------------------------------------------------------------------------------+

  Verifying  : mssql-server-agent-14.0.3015.40-1.x86_64                                                                                      1/1

Installed:
  mssql-server-agent.x86_64 0:14.0.3015.40-1

Complete!


[root@te1-hdp-rp-dn04 ~]# systemctl restart mssql-server


If you already have mssql-server-agent installed, you can update to the latest version with the following commands:

yum check-update
yum update mssql-server-agent
systemctl restart mssql-server



Connect to Server from Command Line
************************************************
Connect , Run sqlcmd with parameters for your SQL Server name (-S), the user name (-U), and the password (-P). You can omit the password on the command line to be prompted to enter it.

If you later decide to connect remotely, specify the machine name or IP address for the -S parameter, and make sure port 1433 is open on your firewall.
Upon success you should get to a sqlcmd command prompt: 1>.

[root@te1-hdp-rp-dn04 ~]# sqlcmd -S localhost -U SA -P 'P@ssw0rd'
1>

Create and query data
***************************
-- Create a new database

1> CREATE DATABASE MyDB

-- Write a query to return the name of all of the databases on your server

2> SELECT Name from sys.Databases

--The previous two commands were not executed immediately. You must type GO on a new line to execute the previous commands:

3> GO
Name
-----------------------------------------------
master
tempdb
model
msdb
MyDB

(5 rows affected)
1>

Insert data
*************
--From the sqlcmd command prompt, switch context to the new MyDB database use SQL Statements:

1> USE MyDB
2> CREATE TABLE Emp(id INT, ename NVARCHAR(50))
3> INSERT INTO Emp VALUES (1, 'Ali');INSERT INTO Emp VALUES (2, 'Abuzar');
4> GO
Changed database context to 'MyDB'.

(1 rows affected)

(1 rows affected)

Select data
*************
1> SELECT * FROM Emp WHERE id=2
2> go
id          ename
----------- --------------------------------------------------
          2 Abuzar

(1 rows affected)


1> quit

Connect from Windows
******************************


SQL Server tools on Windows connect to SQL Server instances on Linux in the same way they would connect to any remote SQL Server instance.


If you have a Windows machine that can connect to your Linux machine, try the same steps in this topic from a Windows command-prompt running sqlcmd. Just verify that you use the target Linux machine name or IP address rather than localhost, and make sure that TCP port 1433 is open.

1> select @@servername + '\' + @@servicename
2> go
                                                                                                                                                 
--------------------------------------------

te1-hdp-rp-dn04\MSSQLSERVER  








Manage the SQL Server
*****************************

To find the IP address 
***************************
[root@te1-hdp-rp-dn04 ~]# ifconfig
[root@te1-hdp-rp-dn04 ~]# ifconfig enp4s0f1 | grep 'inet'    
[root@te1-hdp-rp-dn04 ~]# ip addr show enp4s0f1 | grep "inet"
  

Start/Stop Service
**********************

You can stop, start, or restart the SQL Server service as needed using the following commands:

systemctl stop mssql-server
systemctl start mssql-server
systemctl restart mssql-server

Log files
***********
The SQL Server engine logs to the /var/opt/mssql/log/errorlog file in both the Linux and Docker installations. You need to be in ‘superuser’ mode to browse this directory.

[root@te1-hdp-rp-dn04 ~]# cat /var/opt/mssql/log/errorlog
.....
.....
018-01-29 10:50:32.29 spid13s     Starting up database 'tempdb'.
2018-01-29 10:50:32.66 spid13s     The tempdb database has 1 data file(s).
2018-01-29 10:50:32.68 spid24s     The Service Broker endpoint is in disabled or stopped state.
2018-01-29 10:50:32.68 spid24s     The Database Mirroring endpoint is in disabled or stopped state.
2018-01-29 10:50:32.70 spid24s     Service Broker manager has started.
2018-01-29 10:50:32.74 spid7s      Recovery is complete. This is an informational message only. No user action is required.
2018-01-29 11:08:17.80 spid54      Starting up database 'MyDB'.
....
.....


If you prefer, you can also convert the files to UTF-8 to read them with ‘more’ or ‘less’ with the following command:

[root@te1-hdp-rp-dn04 ~]# iconv -f UTF-16LE -t UTF-8 /var/opt/mssql/log/errorlog -o /var/opt/mssql/log/errorlogUTF


Extended Events
*********************
Extended events can be queried via a SQL command. Extended Events is a light weight performance monitoring system that uses very few performance resources. It has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.

Example:

1> SELECT  name , description FROM    sys.dm_xe_packages ORDER BY   name;
2> go


Crash dumps
****************

Look for dumps in the log directory in Linux. Check under the /var/opt/mssql/log directory for Linux Core dumps (.tar.gz2 extension) or SQL minidumps (.mdmp extension)

-- For Core dumps
[root@te1-hdp-rp-dn04 ~]# ls /var/opt/mssql/log | grep .tar.gz2

--For SQL dumps
[root@te1-hdp-rp-dn04 ~]# ls /var/opt/mssql/log | grep .mdmp


Start SQL Server in Minimal Configuration Mode
***********************************************************

This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting.

I tested as below. Stopped the SQL Server then started with minimal configuration and 
connected with sqlcmd. Afte testing used CTRL+C to shut it down again.

[root@te1-hdp-rp-dn04 ~]# systemctl stop mssql-server

Start SQL Server on Linux with the "mssql" user to prevent future startup issues.

[root@te1-hdp-rp-dn04 ~]# sudo -u mssql /opt/mssql/bin/sqlservr -f
.....
2018-01-29 11:47:11.69 spid9s      Starting up database 'model'.
2018-01-29 11:47:11.83 spid16s     A self-generated certificate was successfully loaded for encryption.
2018-01-29 11:47:11.84 spid16s     Server is listening on [ 'any' <ipv6> 1433].
2018-01-29 11:47:11.84 spid16s     Server is listening on [ 'any' <ipv4> 1433].
2018-01-29 11:47:11.85 Server      Server is listening on [ ::1 <ipv6> 1434].
2018-01-29 11:47:11.85 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2018-01-29 11:47:11.85 Server      Dedicated admin connection support was established for listening locally on port 1434.
2018-01-29 11:47:11.86 spid16s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2018-01-29 11:47:12.06 spid9s      Polybase feature disabled.
2018-01-29 11:47:12.06 spid9s      Clearing tempdb database.
2018-01-29 11:47:13.06 spid9s      Starting up database 'tempdb'.
2018-01-29 11:47:13.46 spid6s      Recovery is complete. This is an informational message only. No user action is required.

^C2018-01-29 11:48:54.47 spid6s      SQL Server shutdown due to Ctrl-C or Ctrl-Break signal. This is an informational message only. No user action is required.



Start SQL Server in Single User Mode
**********************************************


Under certain circumstances, you may have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. For example, you may want to change server configuration options or recover a damaged master database or other system database

After above Ctrl-C, I started Server with singel user mode.

[root@te1-hdp-rp-dn04 ~]# sudo -u mssql /opt/mssql/bin/sqlservr -m

You can open sqlcmd and do the required maintenance.

After testing , I started the SQL Server as normal

[root@te1-hdp-rp-dn04 ~]# systemctl start mssql-server


Note: 


If you have accidentally started SQL Server with another user, you must change ownership of SQL Server database files back to the 'mssql' user prior to starting SQL Server with systemd. For example, to change ownership of all database files under /var/opt/mssql to the 'mssql' user, run the following command

chown -R mssql:mssql /var/opt/mssql/


Resetting the system administration (SA) password.
****************************************************************
If you have forgotten the system administrator (SA) password or need to reset it for some other reason, follow these steps.

Log into the host terminal, run the following commands and follow the prompts to reset the SA password:

[root@te1-hdp-rp-dn04 ~]# systemctl stop mssql-server
[root@te1-hdp-rp-dn04 ~]# /opt/mssql/bin/mssql-conf setup

[root@te1-hdp-rp-dn04 ~]# sqlcmd -S te1-hdp-rp-dn04 -U SA -P 'P@ssw0rd1'



References:
https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide#connection

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/selects-and-joins-from-system-views-for-extended-events-in-sql-server

No comments: