Author Photo

Ahmer's SysAdmin Recipes is a blog for Linux System Administrators. This blog provides recipes for Installation & Configuration of Linux, Ubuntu, Oracle Database, MySQL, Apache, Nginx, Oracle Cloud Control, FreeIPA, Kerberos & OpenLDAP, Subversion, PXE, DevOps, etc. over Red Hat Enterprise Linux (RHEL), CentOS, Ubuntu and Windows.

Please give me your feedback and help me improve this blog. Please let me know If you want me to write on a specific topic.

Thursday, 11 April 2019

Install MS SQL Server 2019 on CentOS 7

Install MS SQL Server 2019 on CentOS 7SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. Since it’s inception in 1989 from Sybase SQL Server, MS SQL Server was a platform dependent RDBMS and it only support different versions of Microsoft Windows. However, Microsoft haved added support for Linux platform in MS SQL Server 2017. Therefore, we can now install MS SQL Server 2017 (or later) on Windows, Linux and Docker platforms.

MS SQL Server 2017 is the stable version that is currently available to download. However, a MS SQL Server 2019 version is also available at their preview yum repository.

In this article, we will install MS SQL Server 2019 on CentOS 7 machine and then we will create a test database and a table using Transact-SQL (T-SQL).

This article only addresses the installation of MS SQL Server 2019 on CentOS 7 and some basic commands to give you an introduction of the environment. Therefore, if you are willing to know more about MS SQL Server and Transact-SQL (T-SQL) then you should read Learn Microsoft SQL Server Intuitively: Transact-SQL: The Solid Basics by Peter Lalovsky.

 

System Specification:

We have provisioned a CentOS 7 virtual machine with following specifications:

Hostname: sqlserver-01.example.com
IP Address: 192.168.116.157/24
Operating System: CentOS 7.6
CPU: 3.4 Ghz (2 cores)
Memory: 2 GB
Storage: 60 GB

 

Installing MS SQL Server 2019 on CentOS 7:

Connect to sqlserver-01.example.com using ssh as root user.

Download and add MS SQL Server 2019 (Preview) yum repository.

[root@sqlserver-01 ~]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-preview.repo % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 240 100 240 0 0 62 0 0:00:03 0:00:03 --:--:-- 62

Build yum cache as follows:

[root@sqlserver-01 ~]# yum makecache fast Loaded plugins: fastestmirror Determining fastest mirrors * base: mirrors.ges.net.pk * extras: mirrors.ges.net.pk * updates: mirrors.ges.net.pk base | 3.6 kB 00:00 extras | 3.4 kB 00:00 packages-microsoft-com-mssql-server-preview | 2.9 kB 00:00 updates | 3.4 kB 00:00 (1/3): extras/7/x86_64/primary_db | 187 kB 00:03 (2/3): packages-microsoft-com-mssql-server-preview/primary | 68 kB 00:04 (3/3): updates/7/x86_64/primary_db | 3.4 MB 00:11 Metadata Cache Created

Install MS SQL Server 2019 on CentOS 7 using yum command.

[root@sqlserver-01 ~]# yum install -y mssql-server Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.ges.net.pk * extras: mirrors.ges.net.pk * updates: mirrors.ges.net.pk Resolving Dependencies --> Running transaction check ---> Package mssql-server.x86_64 0:15.0.1400.75-4 will be installed --> Processing Dependency: cyrus-sasl for package: mssql-server-15.0.1400.75-4.x86_64 --> Processing Dependency: libsss_nss_idmap for package: mssql-server-15.0.1400.75-4.x86_64 --> Processing Dependency: cyrus-sasl-gssapi for package: mssql-server-15.0.1400.75-4.x86_64 --> Processing Dependency: gdb for package: mssql-server-15.0.1400.75-4.x86_64 --> Processing Dependency: bzip2 for package: mssql-server-15.0.1400.75-4.x86_64 --> Running transaction check ---> Package bzip2.x86_64 0:1.0.6-13.el7 will be installed ---> Package cyrus-sasl.x86_64 0:2.1.26-23.el7 will be installed ---> Package cyrus-sasl-gssapi.x86_64 0:2.1.26-23.el7 will be installed ---> Package gdb.x86_64 0:7.6.1-114.el7 will be installed ---> Package libsss_nss_idmap.x86_64 0:1.16.2-13.el7_6.5 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: mssql-server x86_64 15.0.1400.75-4 packages-microsoft-com-mssql-server-preview 208 M Installing for dependencies: bzip2 x86_64 1.0.6-13.el7 base 52 k cyrus-sasl x86_64 2.1.26-23.el7 base 88 k cyrus-sasl-gssapi x86_64 2.1.26-23.el7 base 41 k gdb x86_64 7.6.1-114.el7 base 2.4 M libsss_nss_idmap x86_64 1.16.2-13.el7_6.5 updates 154 k Transaction Summary ================================================================================ Install 1 Package (+5 Dependent packages) Total download size: 211 M Installed size: 216 M Downloading packages: (1/6): bzip2-1.0.6-13.el7.x86_64.rpm | 52 kB 00:03 (2/6): cyrus-sasl-2.1.26-23.el7.x86_64.rpm | 88 kB 00:03 (3/6): cyrus-sasl-gssapi-2.1.26-23.el7.x86_64.rpm | 41 kB 00:03 (4/6): libsss_nss_idmap-1.16.2-13.el7_6.5.x86_64.rpm | 154 kB 00:04 (5/6): gdb-7.6.1-114.el7.x86_64.rpm | 2.4 MB 00:20 warning: /var/cache/yum/x86_64/7/packages-microsoft-com-mssql-server-preview/packages/mssql-server-15.0.1400.75-4.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY Public key for mssql-server-15.0.1400.75-4.x86_64.rpm is not installed (6/6): mssql-server-15.0.1400.75-4.x86_64.rpm | 208 MB 11:20 -------------------------------------------------------------------------------- Total 316 kB/s | 211 MB 11:24 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 : cyrus-sasl-gssapi-2.1.26-23.el7.x86_64 1/6 Installing : gdb-7.6.1-114.el7.x86_64 2/6 Installing : cyrus-sasl-2.1.26-23.el7.x86_64 3/6 Installing : libsss_nss_idmap-1.16.2-13.el7_6.5.x86_64 4/6 Installing : bzip2-1.0.6-13.el7.x86_64 5/6 Installing : mssql-server-15.0.1400.75-4.x86_64 6/6 +--------------------------------------------------------------+ Please run 'sudo /opt/mssql/bin/mssql-conf setup' to complete the setup of Microsoft SQL Server +--------------------------------------------------------------+ Verifying : mssql-server-15.0.1400.75-4.x86_64 1/6 Verifying : bzip2-1.0.6-13.el7.x86_64 2/6 Verifying : libsss_nss_idmap-1.16.2-13.el7_6.5.x86_64 3/6 Verifying : cyrus-sasl-2.1.26-23.el7.x86_64 4/6 Verifying : gdb-7.6.1-114.el7.x86_64 5/6 Verifying : cyrus-sasl-gssapi-2.1.26-23.el7.x86_64 6/6 Installed: mssql-server.x86_64 0:15.0.1400.75-4 Dependency Installed: bzip2.x86_64 0:1.0.6-13.el7 cyrus-sasl.x86_64 0:2.1.26-23.el7 cyrus-sasl-gssapi.x86_64 0:2.1.26-23.el7 gdb.x86_64 0:7.6.1-114.el7 libsss_nss_idmap.x86_64 0:1.16.2-13.el7_6.5 Complete!

After installation of MS SQL Server 2019, run setup as suggested by yum installer above.

[root@sqlserver-01 ~]# /opt/mssql/bin/mssql-conf setup usermod: no changes 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) - CPU Core utilization restricted to 20 physical/40 hyperthreaded 7) Enterprise Core (PAID) - CPU Core utilization up to Operating System Maximum 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):

Select your required MS SQL Server edition. We are installing Developer Edition of MS SQL Server.

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]:

You can view the License and Privacy statments in a browser. Read and Agree with License terms to continue.

Do you accept the license terms? [Yes/No]:Yes Enter the SQL Server system administrator password: Set a Strong System Administrator (SA) Password for MS SQL Server Instance. Enter the SQL Server system administrator password: Confirm the SQL Server system administrator password: Configuring SQL Server... This is an evaluation version. There are [167] days left in the evaluation period. ForceFlush is enabled for this instance. ForceFlush feature is enabled for log durability. 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.

Check status of MS SQL Server Service.

[root@sqlserver-01 ~]# 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 Wed 2019-04-10 20:51:55 PKT; 13h ago Docs: https://docs.microsoft.com/en-us/sql/linux Main PID: 7763 (sqlservr) CGroup: /system.slice/mssql-server.service ââ7763 /opt/mssql/bin/sqlservr ââ7785 /opt/mssql/bin/sqlservr Apr 10 20:52:24 sqlserver-01.example.com sqlservr[7763]: 2019-04-10 20:52:24.... Apr 10 20:52:24 sqlserver-01.example.com sqlservr[7763]: 2019-04-10 20:52:24.... Apr 10 20:52:24 sqlserver-01.example.com sqlservr[7763]: 2019-04-10 20:52:24.... Apr 10 20:52:25 sqlserver-01.example.com sqlservr[7763]: 2019-04-10 20:52:25.... Apr 10 20:52:25 sqlserver-01.example.com sqlservr[7763]: 2019-04-10 20:52:25.... Apr 10 20:52:25 sqlserver-01.example.com sqlservr[7763]: 2019-04-10 20:52:25.... Apr 10 20:52:30 sqlserver-01.example.com sqlservr[7763]: 2019-04-10 20:52:30.... Apr 10 20:52:30 sqlserver-01.example.com sqlservr[7763]: 2019-04-10 20:52:30.... Apr 10 20:52:30 sqlserver-01.example.com sqlservr[7763]: 2019-04-10 20:52:30.... Apr 11 09:14:38 sqlserver-01.example.com sqlservr[7763]: 2019-04-11 09:14:38.... Hint: Some lines were ellipsized, use -l to show in full.

To allow remote connections, we have to allow the MS SQL Server default port 1433/tcp in Linux Firewall.

[root@sqlserver-01 ~]# firewall-cmd --permanent --add-port=1433/tcp success [root@sqlserver-01 ~]# firewall-cmd --reload success

 

Installing SQL Server Tools on CentOS 7:

We have configured a MS SQL Server 2019 and we can use SQL Server Management Studio (SSMS) on a windows based client to connect and use our MS SQL Server 2019.

However, we are also installing MS SQL Server command line utilities, so we can perform database administration tasks directly on our database server.

Download and install Microsoft yum repository to install SQL Server tools.

[root@sqlserver-01 ~]# 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 84 0 0:00:02 0:00:02 --:--:-- 84

Build yum cache for the newly added yum repository.

[root@sqlserver-01 ~]# yum makecache fast Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.ges.net.pk * extras: mirrors.ges.net.pk * updates: mirrors.ges.net.pk base | 3.6 kB 00:00 extras | 3.4 kB 00:00 packages-microsoft-com-mssql-server-preview | 2.9 kB 00:00 packages-microsoft-com-prod | 2.9 kB 00:00 updates | 3.4 kB 00:00 packages-microsoft-com-prod/primary_db | 164 kB 00:01 Metadata Cache Created

Install SQL Server Tools using yum command.

[root@sqlserver-01 ~]# yum install -y mssql-tools Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.ges.net.pk * extras: mirrors.ges.net.pk * updates: mirrors.ges.net.pk Resolving Dependencies --> Running transaction check ---> Package mssql-tools.x86_64 0:17.3.0.1-1 will be installed --> Processing Dependency: msodbcsql17 < 17.4.0.0 for package: mssql-tools-17.3.0.1-1.x86_64 --> Processing Dependency: msodbcsql17 >= 17.3.0.0 for package: mssql-tools-17.3.0.1-1.x86_64 --> Running transaction check ---> Package msodbcsql17.x86_64 0:17.3.1.1-1 will be installed --> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql17-17.3.1.1-1.x86_64 --> Processing Dependency: libodbcinst.so.2()(64bit) for package: msodbcsql17-17.3.1.1-1.x86_64 --> Running transaction check ---> Package unixODBC.x86_64 0:2.3.7-1.rh will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: mssql-tools x86_64 17.3.0.1-1 packages-microsoft-com-prod 254 k Installing for dependencies: msodbcsql17 x86_64 17.3.1.1-1 packages-microsoft-com-prod 769 k unixODBC x86_64 2.3.7-1.rh packages-microsoft-com-prod 213 k Transaction Summary ================================================================================ Install 1 Package (+2 Dependent packages) Total download size: 1.2 M Installed size: 1.2 M Downloading packages: (1/3): mssql-tools-17.3.0.1-1.x86_64.rpm | 254 kB 00:02 (2/3): unixODBC-2.3.7-1.rh.x86_64.rpm | 213 kB 00:01 (3/3): msodbcsql17-17.3.1.1-1.x86_64.rpm | 769 kB 00:03 -------------------------------------------------------------------------------- Total 337 kB/s | 1.2 MB 00:03 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : unixODBC-2.3.7-1.rh.x86_64 1/3 The license terms for this product can be downloaded from https://aka.ms/odbc17eula 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.3.1.1-1.x86_64 2/3 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.3.0.1-1.x86_64 3/3 Verifying : unixODBC-2.3.7-1.rh.x86_64 1/3 Verifying : mssql-tools-17.3.0.1-1.x86_64 2/3 Verifying : msodbcsql17-17.3.1.1-1.x86_64 3/3 Installed: mssql-tools.x86_64 0:17.3.0.1-1 Dependency Installed: msodbcsql17.x86_64 0:17.3.1.1-1 unixODBC.x86_64 0:2.3.7-1.rh Complete!

SQL Server tools has been installed in /opt/mssql/bin/ directory.

[root@sqlserver-01 ~]# ls /opt/mssql-tools/bin/ bcp sqlcmd

Add this directory to PATH environment variable for accessibility and convenience.

[root@sqlserver-01 ~]# cat > /etc/profile.d/mssql-tools.sh << EOF > #!/bin/bash > export PATH=$PATH:/opt/mssql-tools/bin > EOF

SQL Server tools has been installed on CentOS 7.

 

Using sqlcmd to execute Transact-SQL on MS SQL Server:

Connect to MS SQL Server instance using SA user and password.

[root@sqlserver-01 ~]# sqlcmd -S localhost -U SA -P 'Ahmer@1234'

You are now at the Transact-SQL (T-SQL) command prompt. Execute a query as follows:

1> select name from sys.databases; 2> go name ---------------------- master tempdb model msdb (4 rows affected)

Now, create a custom database on our MS SQL Server 2019 instance.

1> create database contacts; 2> go

Create a table in contacts database.

1> use contacts; 2> create table contacts_list 3> (contact_id char(6) primary key, 4> contact_name char(30)); 5> go Changed database context to 'contacts'.

Check our newly created table.

1> select name from sys.tables; 2> go name ----------------------- contacts_list (1 rows affected)

Insert a row in contacts_list table.

1> use contacts; 2> insert into contacts_list 3> values 4> ('101','Ahmer Mansoor'); 5> go Changed database context to 'contacts'. (1 rows affected)

You can execute any Transact-SQL (T-SQL) commands using sqlcmd. But we are not showing every command here in this article to keep it brief.

Exit from Transact-SQL (T-SQL) command prompt.

1> quit

We have successfully configured MS SQL Server 2019 on CentOS 7.

Install MS SQL Server 2019 on CentOS 7


YOU MIGHT ALSO LIKE:

No comments:

Post a Comment