HowTo Install MSSQL Preview (2019) on NethServer 7.5


(fpausp) #1
# Download the Microsoft SQL Server Red Hat repository configuration file:
curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-preview.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)
1> exit


# 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 201x 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.