====== MS SQL Server unter Linux ======

 
====== Status, start, and stop MS SQL Server ======
 

<sxh bash>
systemctl status|start|stop mssql-server
</sxh>

====== Verbindung verschlüsseln ======

<sxh sh>
openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=hetzner_mssql' -keyout mssql.key -out mssql.pem -days 3650
</sxh>

<file sh mssql.conf>
[network]
tlscert = /run/secrets/hetzner_mssql.pem
tlskey = /run/secrets/hetzner_mssql.key
tlsprotocols = 1.2
forceencryption = 1
</file>

====== Konfiguration ======

Nach der Installation muss das Konfigurationsskript ''mssql-conf'' einmal aufgerufen werden

<code bash>
sudo /opt/mssql/bin/mssql-conf setup
</code>

<code bash>
Wählen Sie eine SQL Server-Edition:
  1) Evaluation (Kostenlos, keine Nutzungsrechte für die Produktion, auf 180 Tage begrenzt)
  2) Developer (Kostenlos, keine Nutzungsrechte für die Produktion)
  3) Express (kostenlos)
  4) Web (KOSTENPFLICHTIG)
  5) Standard (KOSTENPFLICHTIG)
  6) Enterprise (KOSTENPFLICHTIG)
  7) Enterprise Core (KOSTENPFLICHTIG)
  8) Ich habe eine Lizenz im Einzelhandel erworben und besitze einen Production Key.

Details zu den Editionen finden Sie hier:
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x407

Die Nutzung KOSTENPFLICHTIGER Editionen dieser Software erfordert eine separate Lizenzierung über ein
Microsoft-Volumenlizenzierungsprogramm.
Durch die Auswahl einer KOSTENPFLICHTIGEN Edition bestätigen Sie, dass Sie die erforderliche
Anzahl von Lizenzen zur Installation und Ausführung dieser Software besitzen.

Edition eingeben(1-8):
</code>

Danach kommt die Abfrage der Sprache für den Server und des Passworts für den Nutzer ''SA''


Wenn das Skript erfolgreich war, wird der MS SQL-Server gestartet und das Skript meldet:
<code bash>
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /lib/systemd/system/mssql-server.service.
Das Setup wurde erfolgreich abgeschlossen. SQL Server wird jetzt gestartet.
</code>


Bei Problemen bekommt man die Ausgabe des Servers mit:

<code bash>
systemctl status mssql-server
</code>

====== Nutzung ======

====== SQL Server Management Studio ======

Der SQL-Server kann unter Windows mit dem ''SQL Server Managment Studio'' und mit dem ''SQL-Server Configuration Manager'' erreicht und genutzt werden, genauso wie die SQL-Server auf Windows auch. Nach der Installation ist Port 1433 und der Nutzer SA mit dem angegeben Passwort voreingestellt, Änderungen können mit dem ''SQL-Server Configuration Manager'' vorgenommen werden.

{{ ::sql_server_linux_studio_01.png?300 |}}

Außerdem ist der Server erreichbar über:
  * pyodbc und [[mssql:freetds|FreeTDS]]
  * DBeaver https://dbeaver.jkiss.org/
  * Lokal auf dem Server über einen sqlcmd client der extra installiert werden muss: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu
  * isql client (Linux command line, sehr rudimentär und buggy)

====== Verbindung mit ''sqlcmd'' ======

<sxh sh>
/opt/mssql-tools/bin/sqlcmd -S localhost,5432 -U SA -P 'passwort'
</sxh>

======Ändern das Passworts für SA ======

<sxh sh>
/opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P "Old one" \
   -Q 'ALTER LOGIN SA WITH PASSWORD="New one"'
</sxh>


===== Attaching a database =====

 :!: Attention :!:  If databases were previously created on another server, the master key must be regenerated:

''ALTER SERVICE MASTER KEY FORCE REGENERATE''


The database files have to be transferred to the Linux machine as well and stored there under the directory ''/var/opt/mssql'' (in Docker: ''/var/opt/mssql/data'').

After that, the databases have to be transferred to the user mssql and made available:

<sxh sh>
sudo chown -R mssql /var/opt/mssql/data
sudo chmod -R u+rw /var/opt/mssql/data
</sxh>

**SQL Server Management Studio: ** (When adding new databases there is a small bug that you have to work around):

After selecting a new database via the Add button, the path to it is wrong because it contains a backslash. This can be quickly changed by selecting the same file again under ''Location for MDF file'' and the ''...'' button. Maybe this bug is fixed in the meantime in the newest version.

{{ ::sql_server_linux_studio_02.png?800 |}}

**Command line: **

<sxh sql>
USE [master]
GO
CREATE DATABASE [DiversityAgents_SMNS] ON ( FILENAME = N'/var/opt/mssql/data/DiversityAgents_SMNS.mdf' ), ( FILENAME = N'/var/opt/mssql/data/DiversityAgents_SMNS_log.ldf' ) FOR ATTACH
GO
</sxh>


===== Database is not accessible (Recovery pending) =====

cf. https://www.stellarinfo.com/blog/fix-sql-database-recovery-pending-state-issue/

<sxh sql>
ALTER DATABASE [DBName] SET EMERGENCY;
GO
ALTER DATABASE [DBName] set single_user
GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE [DBName] set multi_user
GO
</sxh>
===== Performance =====

No matter how much memory you put in a system, SQL Server will use all it can get until it’s caching entire databases in memory and then some.

[[https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/|SQL Server Perfmon tutorial]]

Checking is SQL Server: Buffer Manager – Page Life Expectancy. This counter records how long SQL Server is able to cache data in memory, measured in seconds. Higher numbers are better. As a general starting point, this number shouldn’t dip below 300 seconds (5 minutes) for too long.

**More memory:**

As you add more memory, the Page Life Expectancy counter should go up, and the Physical Disk: Average Reads/sec counter should go down. A nice side effect is that the Physical Disk: Average Sec/Read counter (aka latency) should also go down, because the less work we make our storage do, the faster it’s able to respond.

**Need for more memory:**

  * The total size of the MDF files on the SQL Server’s hard drives is more than 2x memory
  * Page Life Expectancy is averaging under 300 during end user load times (typically weekday business hours)
  * SQL Statistics – Batch Requests/sec and Compilations/sec.  Batch Requests is the number of incoming queries, and Compilations is the number of new plans we had to build.


===== Connect to MS SQL Server using Python =====

===== Ubuntu < 22.04, Debian =====

<sxh bash>
sudo apt install odbcinst unixodbc unixodbc-dev

pip3 install pyodbc
</sxh>

In Python:
<sxh python>
import pyodbc
connection_string = "DRIVER=/usr/lib/libtdsodbc.so;SERVER=smns.diversityworkbench.de;Port=5432;UID=<username>;PWD=<password>;Database=<database name>"
connection = pyodbc.connect(connection_string)
</sxh>

===== Ubuntu >= 22.04 =====

<sxh bash>
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 mssql-tools18  unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc

pip3 install pyodbc
</sxh>

In Python:
<sxh python>
import pyodbc
connection_string = "DRIVER=/usr/lib/libmsodbcsql-18.so;SERVER=tcp:smns.diversityworkbench.de,5432;UID=<username>;PWD=<passord>;TrustServerCertificate=yes"
connection = pyodbc.connect(connection_string)
</sxh>

For using using tsql or isql on the command line, add the following files to /etc:

<file ini /etc/odbc.ini>
[DC]
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
Server = tcp:smns.diversityworkbench.de,5432
Desription = ODBC Datenverbindung zur Datenbank MyDatabaseName
TrustServerCertificate = yes
</file>

<file ini /etc/odbcinst.ini>
[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/x86_64-linux-gnu/libodbc.so.2
Setup=

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
UsageCount=1
</file>