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:
Post a Comment