MSSQL 101

Microsoft SQL Server, commonly known as MSSQL, is a relational database management system developed by Microsoft. It is a robust, enterprise-grade database server that is widely used for a variety of applications, from small-scale web applications to large-scale data warehousing and business intelligence solutions.

Overview

MSSQL is known for its tight integration with other Microsoft products, such as Windows Server, Azure, and the .NET framework. It offers a comprehensive set of tools for data management, business intelligence, and data analysis. MSSQL uses Transact-SQL (T-SQL), an extension of the standard SQL language, for querying and managing data.

Installation

MSSQL can be installed on both Windows and Linux. Here are the general steps for installing SQL Server on Ubuntu.

Ubuntu

Import the public repository GPG keys:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

Register the Microsoft SQL Server Ubuntu repository:

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

Install SQL Server:

sudo apt-get update
sudo apt-get install -y mssql-server

Run the setup script:
```
sudo /opt/mssql/bin/mssql-conf setup
```
This script will prompt you to accept the license terms and set the administrator password.

Install command-line tools:

sudo apt-get install -y mssql-tools unixodbc-dev

Basic Concepts and Commands

Connecting to MSSQL

To connect to the MSSQL server from the command line, use the sqlcmd utility:

sqlcmd -S localhost -U SA -P '<YourPassword>'

Common T-SQL Commands

List Databases:
```
SELECT name FROM sys.databases;
GO
```
Create a Database:
```
CREATE DATABASE MyNewMSSQLDB;
GO
```
Use a Database:
```
USE MyNewMSSQLDB;
GO
```

Create a Table:

CREATE TABLE Customers (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(255),
    City varchar(255)
);
GO

Insert Data:

INSERT INTO Customers (Name, City) VALUES ('Cardinal', 'Stavanger');
GO

Select Data:
```
SELECT * FROM Customers;
GO
```

Docker Installation

Running MSSQL in a Docker container is a great way to quickly set up a development or test environment.

Pull the MSSQL Image:

docker pull mcr.microsoft.com/mssql/server:2019-latest

Run the MSSQL Container:
```
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong!Passw0rd>" \
   -p 1433:1433 --name sql1 -d mcr.microsoft.com/mssql/server:2019-latest
```
This command starts a new container named sql1, accepts the EULA, sets the SA password, maps port 1433, and runs it in detached mode.

Connect to the Container:

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourStrong!Passw0rd>'

Kubernetes Deployment

Deploying MSSQL on Kubernetes can be done using a Deployment and a PersistentVolume.

Create a Secret for the Password:

apiVersion: v1
kind: Secret
metadata:
  name: mssql-secret
type: Opaque
data:
  SA_PASSWORD: <your-base64-encoded-password>

Create a PersistentVolumeClaim:

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mssql-data
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 10Gi

Create a Deployment and Service:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mssql
  template:
    metadata:
      labels:
        app: mssql
    spec:
      containers:
        - name: mssql
          image: mcr.microsoft.com/mssql/server:2019-latest
          ports:
            - containerPort: 1433
          env:
            - name: ACCEPT_EULA
              value: "Y"
            - name: SA_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mssql-secret
                  key: SA_PASSWORD
          volumeMounts:
            - name: mssqldb
              mountPath: /var/opt/mssql
      volumes:
        - name: mssqldb
          persistentVolumeClaim:
            claimName: mssql-data
---
apiVersion: v1
kind: Service
metadata:
  name: mssql-service
spec:
  selector:
    app: mssql
  ports:
    - protocol: TCP
      port: 1433
      targetPort: 1433
  type: LoadBalancer

Configuration and Tuning

Configuration: MSSQL can be configured using the mssql-conf tool or by setting environment variables in Docker.
Key Configuration Options:
- memory.memorylimitmb: Sets the memory limit for the SQL Server process.
- network.tcpport: Changes the TCP port that SQL Server listens on.
Tuning:
- Indexing: Use clustered and non-clustered indexes to improve query performance.
- Query Store: Use the Query Store feature to capture a history of queries, plans, and runtime statistics.
- Monitoring: Use Dynamic Management Views (DMVs) and tools like SQL Server Management Studio (SSMS) or Azure Data Studio to monitor performance.

Overview​

Installation​

Ubuntu​

Basic Concepts and Commands​

Connecting to MSSQL​

Common T-SQL Commands​

Docker Installation​

Kubernetes Deployment​

Configuration and Tuning​