HowTo install MSSQL 2017 on NethServer 7.4


(fpausp) #1

HowTo Install MSSQL 2017 (14.0.3008.27-1) on NethServer 7.4

Info:

Download the Microsoft SQL Server Red Hat repository configuration file (all in one line):

curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo

Run the following command to install SQL Server:

yum install -y mssql-server

After the package installation finishes, run mssql-conf setup and follow the prompts. 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):

/opt/mssql/bin/mssql-conf setup

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

systemctl status mssql-server

To allow remote connections (LAN), open the SQL Server port on the firewall. The default SQL Server port is TCP 1433:

config set myservice service status enabled TCPPort 1433 access green
signal-event firewall-adjust

To upgrade the mssql-server package, if needed, execute the following command:

yum update mssql-server

To remove the mssql-server package, if needed, follow these steps:

yum remove mssql-server

Removing the package will not delete the generated database files. If you want to delete the database files use the following command:

rm -rf /var/opt/mssql/

To be able to connect to SQL Server on Linux from command line

Download the Microsoft Red Hat repository configuration file:

curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

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

yum remove unixODBC-utf16 unixODBC-utf16-devel

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

yum install mssql-tools unixODBC-devel -y

To update to the latest version of mssql-tools run the following commands:

yum check-update
yum update mssql-tools

Add /opt/mssql-tools/bin/ to your PATH environment variable:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Connect to SQL Server with sqlcmd

On your Linux box, open a command terminal. Run sqlcmd with parameters for your SQL Server name (-S), the user name (-U), and the password (-P).

The following command connects to the local SQL Server instance (localhost) on Linux:

sqlcmd -S localhost -U SA -P '<My-PassWord-From-Setup>'

To connect to a remote instance, specify the machine name or IP address for the -S parameter:

sqlcmd -S 192.168.xxx.xxx -U SA -P '<My-PassWord-From-Setup>'

List your Databases with:

1> SELECT Name from sys.Databases
2> GO
Name

master
tempdb
model
msdb

(4 rows affected)

Backup and restore SQL Server databases

In the following example sqlcmd connects to the local SQL Server instance and takes a full backup of a user database called demodb.

Backup a database (all in one line):

sqlcmd -S localhost -U SA -Q "BACKUP DATABASE [master] TO DISK = N'/var/opt/mssql/data/master.bak' WITH NOFORMAT, NOINIT, NAME = 'master-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

Backup the transaction log (all in one line):

sqlcmd -S localhost -U SA -Q "BACKUP LOG [demodb] TO DISK = N'/var/opt/mssql/data/demodb_LogBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'demodb_LogBackup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5"

Restore a database (all in one line):

sqlcmd -S localhost -U SA -Q "RESTORE DATABASE [demodb] FROM DISK = N'/var/opt/mssql/data/demodb.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"

Restore the transaction log (all in one line):

sqlcmd -S localhost -U SA -Q "RESTORE LOG demodb FROM DISK = N'/var/opt/mssql/data/demodb_LogBackup.bak'"

Backup with SQL Server Management Studio (SSMS)

The following steps walk through taking a backup with SSMS:
Start SSMS and connect to your server in SQL Server 2017 on Linux.

In Object Explorer, right-click on your database, Click Tasks, and then click Back Up...

In the Backup Up Database dialog, verify the parameters and options, and click OK.

SQL Server completes the database backup.

Restore with SQL Server Management Studio (SSMS)

The following steps walk you through restoring a database with SSMS:
In SSMS right-click Databases and click Restore Databases....

Under Source click Device: and then click the ellipses (...).

Locate your database backup file and click OK.

Under Restore plan, verify the backup file and settings. Click OK.

SQL Server restores the database.

If you use SQL Server Management Studio over VPN, don’t forget to setup Trusted Networks !


Trouble with Yum
SQL server CMD not found