How To Install Microsoft SQL Server On CentOS Linux
In December 2016 Microsoft made their SQL Server database available in Linux. Here we’ll cover how to install and perform basic setup of MSSQL in the RHEL based Linux distribution CentOS.
Install MSSQL In CentOS 7
First we’ll set up the repository file, Microsoft provide a copy of this for RHEL here: https://packages.microsoft.com/config/rhel/7/mssql-server.repo
We’ll use the wget command to copy this file to the /etc/yum.repos.d/ directory so that we can use it with the yum or dnf package manager.
[root@centos7 ~]# wget https://packages.microsoft.com/config/rhel/7/mssql-server.repo -O /etc/yum.repos.d/mssql-server.repo
Now that the repository file is in place, installation is as simple as running the following command. At the time of writing the total size of the package was a 139mb download.
[root@centos7 ~]# yum install mssql-server -y ... +-------------------------------------------------------------------+ | Please run /opt/mssql/bin/sqlservr-setup to complete the setup of | | Microsoft(R) SQL Server(R). | +-------------------------------------------------------------------+
Once the installation has completed, we are advised to run the /opt/mssql/bin/sqlservr-setup bash script to complete the setup process.
During my first installation attempt, I got the following error as my virtual machine was only running with 2GB of memory, so be sure that you have enough memory before proceeding.
sqlservr: This program requires a machine with at least 3250 megabytes of memory. Microsoft(R) SQL Server(R) setup failed with error code 1.
You’ll be able to proceed once you have adequate memory available.
[root@centos7 ~]# /opt/mssql/bin/sqlservr-setup Microsoft(R) SQL Server(R) Setup You can abort setup at anytime by pressing Ctrl-C. Start this program with the --help option for information about running it in unattended mode. The license terms for this product can be downloaded from http://go.microsoft.com/fwlink/?LinkId=746388 and found in /usr/share/doc/mssql-server/LICENSE.TXT. Do you accept the license terms? If so, please type "YES": YES Please enter a password for the system administrator (SA) account: Please confirm the password for the system administrator (SA) account: Setting system administrator (SA) account password... Do you wish to start the SQL Server service now? [y/n]: y Do you wish to enable SQL Server to start on boot? [y/n]: y Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service. Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /usr/lib/systemd/system/mssql-server-telemetry.service. Setup completed successfully.
That’s it, Microsoft SQL Server is now running successfully and listening for traffic on TCP port 1434.
[root@centos7 ~]# systemctl status mssql-server
â mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2016-12-30 02:26:37 PST; 38s ago
Main PID: 2974 (sqlservr)
CGroup: /system.slice/mssql-server.service
ââ2974 /opt/mssql/bin/sqlservr
ââ2995 /opt/mssql/bin/sqlservr
[root@centos7 ~]# netstat -antp | grep 1434
tcp 0 0 127.0.0.1:1434 0.0.0.0:* LISTEN 2995/sqlservr
Connecting To MSSQL
In order to actually connect to the server from Linux we need to install the mssql-tools package, which comes from a different repository than the one that we just set up. It can be found here: https://packages.microsoft.com/config/rhel/7/prod.repo
First we’ll download a copy of the prod.repo file and place it into the /etc/yum.repos.d/ directory.
[root@centos7 ~]# wget https://packages.microsoft.com/config/rhel/7/prod.repo -O /etc/yum.repos.d/prod.repo
We can now proceed with installing the mssql-tools package, as shown below.
[root@centos7 ~]# yum install mssql-tools -y
Once this is installed we can use the sqlcmd command to interact with the database. To see how to run sqlcmd, simply run it with the -? option for help.
Unfortunately it appears that when you specify the -P option for the password, the password must be provided in the command line with no option of being prompted for it later. Keep in mind that your password will be stored in your bash history running it this way.
[root@centos7 ~]# sqlcmd -U SA -P password
1> create database test;
2> go
1> use test;
2> go
Changed database context to 'test'.
1> create table websites(domain varchar(255));
2> go
1> insert into websites (domain)
2> values ('rootusers.com');
3> go
(1 rows affected)
1> select domain
2> from websites;
3> go
domain
rootusers.com
(1 rows affected)
In this example we create a test database with a table named websites and a column for domain names. We then insert a domain name and pull it back out with select, confirming both that we are able to connect and that basic SQL queries appear to be working as expected.
---------------------------------------------------------------------------------------------------------------------------------
The file contents are wrong. It seems when you used curl to download the repo file it failed to find it (maybe a missing letter or something of that sort).
If you are using Microsoft SQL Server, you can recreate it as instructed above.
It should look like:
[packages-microsoft-com-mssql-server-2017]
name=packages-microsoft-com-mssql-server-2017
baseurl=https://packages.microsoft.com/rhel/7/mssql-server-2017/
enabled=1
gpgcheck=1
gpgkey=https://packages.microsoft.com/keys/microsoft.asc---------------------------------------------------------------https://www.server-world.info/en/note?os=CentOS_7&p=mssql2017&f=1SQL Server 2017 : Install2017/10/05
Install Microsoft SQL Server 2017.Before installing and using, Read license terms below.
If you use it on production environment, it's not free, need to buy licenses.
⇒ https://www.microsoft.com/en-us/sql-server/sql-server-2017-pricing [1] Add SQL Server 2017 Repository and Install it.
[root@dlp ~]# curl https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo -o /etc/yum.repos.d/mssql-server-2017.repo
[root@dlp ~]# curl https://packages.microsoft.com/config/rhel/7/prod.repo -o /etc/yum.repos.d/msprod.repo[root@dlp ~]# yum -y install mssql-server mssql-tools unixODBC-devel
[2] Run initial Setup.
[root@dlp ~]# /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.
# select an edition you'd like to use
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
# agree to the license
Do you accept the license terms? [Yes/No]:y
# set admin password
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.
[root@dlp ~]# 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 Thu 2017-10-05 19:24:33 JST; 50min ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 1472 (sqlservr)
CGroup: /system.slice/mssql-server.service
+--1472 /opt/mssql/bin/sqlservr
+--1493 /opt/mssql/bin/sqlservr
Oct 05 19:24:38 dlp.srv.world sqlservr[1472]: 2017-10-05 19:24:38.50 spid11s....
Hint: Some lines were ellipsized, use -l to show in full.
[root@dlp ~]# echo 'export PATH=$PATH:/opt/mssql-tools/bin' > /etc/profile.d/mssql.sh
[root@dlp ~]# source /etc/profile.d/mssql.sh
[3] If Firewalld is running and also SQL Server is used from remote Hosts, allow service port like follows.
[root@dlp ~]# firewall-cmd --add-port=1433/tcp --permanent
success
[root@dlp ~]# firewall-cmd --reload
success
[4] Connect to the SQL Server and verify working.
[root@dlp ~]# sqlcmd -S localhost -U SA
Password: # admin password you set
# show system databases
1> select name,database_id from sys.databases;
2> go
name database_id
------------- -----------
master 1
tempdb 2
model 3
msdb 4
(4 rows affected)
# show system users
1> select name from sysusers;
2> go
name
-----------------------------------------
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin
dbo
guest
INFORMATION_SCHEMA
public
sys
(16 rows affected)
# show current user
1> select current_user;
2> go
-------------------------------
dbo
(1 rows affected)
# quit
1> exit
Tidak ada komentar:
Posting Komentar