Introduction to SQL Server

Modules: Course Overview · Introduction to SQL Server · SQL Server Client Tools · Introduction to T-SQL · Additional Services and Options

Table of Contents

1. Course Overview
2. Introduction to SQL Server

• 2.1 What is an RDBMS?
• 2.2 What is SQL Server?
• 2.3 SQL Server installation
• 2.4 Demo: Installation on Windows
• 2.5 Summary

3. SQL Server Client Tools

• 3.1 Client tools overview
• 3.2 Demo: Client Tools
• 3.3 Summary

4. Introduction to T SQL

• 4.1 T-SQL: DML (Data Manipulation Language)
• 4.2 T-SQL: Schemas, DDL and data types
• 4.3 Other objects in SQL Server
• 4.4 Demo: T-SQL Examples (Notebook)
• 4.5 Summary

5. Additional services and options

• 5.1 SQL Server Analysis Services (SSAS)
• 5.2 SQL Server Integration Services (SSIS)
• 5.3 SQL Server Reporting Services (SSRS)
• 5.4 Hybrid features in SQL Server
• 5.5 Summary

1. Course Overview

This course is a quick introduction to Microsoft SQL Server. It covers the fundamental concepts needed to understand what SQL Server is and how it works. The main themes covered are:

• What is a RDBMS (Relational Database Management System)
• How SQL Server can be installed
• How to access and what tools are available
• How T-SQL, the primary language for communicating with SQL Server, is structured
• What additional services and features come with SQL Server

At the end of this course, you will know how Microsoft SQL Server can help you manage your data challenges and needs.

2. Introduction to SQL Server

2.1 What is an RDBMS?

A RDBMS (Relational Database Management System) is a database management system that manages data in one or more tables. These tables are linked together via relationships.

Structure of a table

A table has two main elements:

Fundamental Data Operations

Data in an RDBMS can be:

• Inserted (INSERT)
• Deleted (DELETE)
• Updates (UPDATE)
• Consulted (SELECT)

Data Characteristics

• Data in an RDBMS is always structured, except when using an unstructured data type like XML.
• Data is organized via indexes, similar to a telephone book — this is how it is physically stored in the table.
• Tables can depend on each other via constraints. For example, an orders table might require that an entry exist in the customers table first, which ensures integrity between these tables.

RDBMS use cases

RDBMS are used in virtually every industry:

• Financial services
• Airlines
• E-commerce
• Health (healthcare)
• Business Operations

Use cases can be grouped into two broad categories:

Note: It is possible to use the same database system for both, but the database design and storage optimizations will be different.

Popular RDBMS

• Oracle Database
• MySQL
• Microsoft SQL Server
• PostgreSQL
• SQLite

2.2 What is SQL Server?

Microsoft SQL Server is an RDBMS developed by Microsoft. Here are the main stages of its evolution:

Version history

SQL Server is a very mature but constantly updated product.

SQL Server Editions

SQL Server is available in several editions, with different features:

Available features depend not only on the edition, but also on the specific version of SQL Server.

2.3 Installing SQL Server

On-premises or cloud?

The first decision when installing SQL Server is where to install it:

• On-premises: on your own servers
• Cloud: on servers in the cloud (Azure, AWS, GCP, etc.)

Important: SQL Server is an unmanaged box product. No matter where you install it, you are responsible for its operation and operation. This is different from the managed SQL offerings available in the clouds.

Installation on Windows

To install SQL Server on Windows:

1. Download the installer from the official Microsoft website (e.g.: SQL Server 2022 CU5 — Cumulative Update 5)
2. Run the included installer

Alternative via PowerShell (DBAtools):

Install-DbaInstance

This command is part of the DBAtools module for PowerShell.

Installing on Linux

SQL Server can also be installed on Linux in two ways:

1. Direct download and installation
2. Image container — executable on Linux or Windows

Starting via container with the new SQLCMD

# Démarrer un container SQL Server avec une base de données attachée en une seule ligne
sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak

See the SQLCMD GitHub repository for more details.

Installing on Kubernetes

For enterprise-ready environments with containers, SQL Server can be deployed on a Kubernetes cluster.

Course demonstration environment

2.4 Demo: Installation on Windows

PowerShell script — Preparing the environment

File: 02/demos.ps1

# Install Choco
[Net.ServicePointManager]::SecurityProtocol = [Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12
Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))

# Install Tools
choco install vscode -y
choco install googlechrome -y

# Refresh Path
$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine") + ";" + [System.Environment]::GetEnvironmentVariable("Path","User")

# Add VSCode Powershell Extension and update Package Management
code --install-extension ms-vscode.PowerShell
Install-PackageProvider Nuget -Force
Install-Module -Name PowerShellGet -Force 
Install-Module -Name PackageManagement -Force 

# Download Installer
Start-Process https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Detailed installation steps (Custom Installation)

1. Go to the SQL Server download page
2. Choose the Developer edition (free, sufficient for our needs)
3. Click on Download now
4. Run the downloaded tool
5. Choose Custom installation (allows you to see all the configurable options)
6. Select a location for the installation media and click Install
7. (~1 GB data — speed depends on internet connection)
8. The installer starts with planning steps — skip them and go directly to the Installation tab
9. Choose New SQL Server standalone installation or add features to an existing installation

In the installation wizard, it is possible to:

• Configure features to install (Database Engine, SSAS, SSIS, SSRS, etc.)
• Choose snack (charset)
• Configure the service account
• Set Authentication mode (Windows or Mixed Mode)
• Configure data directories

2.5 Summary

• SQL Server is a RDBMS developed by Microsoft
• It is available in multiple editions with different features
• Features depend on both edition and specific version
• SQL Server is available on Windows, Linux, and as container

3. SQL Server Client Tools

3.1 Overview of client tools

There are a wide variety of tools for accessing SQL Server. The three main ones covered in this course are:

• SSMS — SQL Server Management Studio
• ADS — Azure Data Studio
• sqlcmd — command line tool

Other tools also exist: MSSQL-CLI, PowerShell modules and plugins, .NET connectors, etc.

Common points between SSMS, ADS and sqlcmd

Tool comparison

Tip: In many cases, use all three tools** because they serve different use cases. This is not a “one-size-fits-all” scenario.

Who are these tools for?

SQL client tools (SSMS, ADS, sqlcmd, MSSQL-CLI, etc.) are primarily designed for developers and administrators. End users access data through other interfaces (business applications, BI dashboards, etc.) and should not have to write queries.

3.2 Demo: Client Tools

PowerShell script — Installing and using client tools

File: 03/demos.ps1

# Installing Tools
# https://github.com/microsoft/go-sqlcmd
# choco install sqlcmd -y
# https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
choco install sql-server-management-studio -y
# https://learn.microsoft.com/en-us/azure/data-studio/download-azure-data-studio
choco install azure-data-studio -y

# SQLCMD
# We could spin up a SQL Container using this (requires a container runtime):
# sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak
sqlcmd -Q "SELECT @@version"
sqlcmd -S localhost -Q "SELECT @@version"

Invoke-WebRequest -o C:\temp\AdventureWorks2019.bak https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.bak
$RestoreCMD = "RESTORE DATABASE AdventureWorks2019 FROM  DISK = N'C:\temp\AdventureWorks2019.bak' " `
            + " WITH MOVE 'AdventureWorks2019' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019.mdf', " `
            + " MOVE 'AdventureWorks2019_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Log.ldf'"
sqlcmd -Q $RestoreCMD

# ADS
Start-process "C:\Program Files\Azure Data Studio\azuredatastudio.exe"

# SSMS
Start-Process "C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Common7\IDE\Ssms.exe"

Using sqlcmd

sqlcmd is already installed with SQL Server, but it is the older version. There is a new version based on the Go language with additional features.

# Installer la nouvelle version de sqlcmd via Chocolatey
choco install sqlcmd -y
# Ou via winget, ou depuis le dépôt GitHub

Main sqlcmd parameters:

# Exécuter une requête directement (SQL Server local)
sqlcmd -Q "SELECT @@version"

# Spécifier le serveur avec -S
sqlcmd -S localhost -Q "SELECT @@version"

# Démarrer un container SQL Server (nouvelle version sqlcmd)
sqlcmd create mssql

Downloading and restoring the AdventureWorks2019 database

The AdventureWorks2019 database is used as demo data throughout the course.

# Télécharger AdventureWorks2019
Invoke-WebRequest -o C:\temp\AdventureWorks2019.bak `
  https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.bak

-- Restaurer la base de données AdventureWorks2019
RESTORE DATABASE AdventureWorks2019 
FROM DISK = N'C:\temp\AdventureWorks2019.bak'
WITH 
  MOVE 'AdventureWorks2019' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019.mdf',
  MOVE 'AdventureWorks2019_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_Log.ldf'

Azure Data Studio (ADS)

ADS is particularly appreciated for:

• Its support for notebooks (individually executable code cells with results included)
• Its extensions system
• Its cross-platform compatibility

Notebooks can be used to: transmit data, tell a story, create troubleshooting manuals, etc.

SQL Server Management Studio (SSMS)

SSMS is the reference tool for administrators. It offers advanced features for:

• replication
• High availability (AlwaysOn)
• Complete server management

3.3 Summary

• There are a wide variety of client tools for working with SQL Server
• The tools presented are all free and maintained by Microsoft
• They are distinguished by their supported operating system and target groups
• Given their different feature sets, different tools are suitable for different use cases — this is not a one-size-fits-all solution

4. Introduction to T SQL

4.1 T-SQL: DML (Data Manipulation Language)

T-SQL (Transact-SQL) is the primary language for communicating with SQL Server. It comes in two main categories:

T-SQL also offers:

• Full support for flow control and error handling
• Working with variables
• Executing instructions in reversible transactions
• Creation of reusable procedures and functions

Note: Available features vary between versions and editions of SQL Server.

Main DML statements

SELECT — Read data

-- Récupérer toutes les colonnes et tous les enregistrements
SELECT * FROM TableA

-- Récupérer des colonnes spécifiques
SELECT FirstName, LastName, DateOfBirth FROM TableA

-- Combiner des colonnes (ex. : FullName)
SELECT FirstName + ' ' + LastName AS FullName FROM TableA

-- Filtrer les résultats avec WHERE
SELECT * FROM TableA WHERE Country = 'CA'

-- Trier les résultats
SELECT * FROM TableA ORDER BY LastName

-- Limiter le nombre de résultats
SELECT TOP 5 * FROM TableA

INSERT — Insert data

-- Insérer avec VALUES
INSERT INTO TableA (FirstName, LastName, DateOfBirth, Country)
VALUES ('Jean', 'Dupont', '1990-01-15', 'CA')

-- Insérer à partir d'une autre table (INSERT ... SELECT)
INSERT INTO TableA (FirstName, LastName)
SELECT FirstName, LastName FROM AdventureWorks2019.Person.Person

UPDATE — Modify data

-- Mettre à jour des enregistrements selon une condition
UPDATE TableA SET Country = 'DE' WHERE Country = 'GER'

DELETE — Delete data

-- Supprimer des enregistrements selon une condition
DELETE FROM TableA WHERE Country IS NULL

-- Supprimer selon une date
DELETE FROM TableA WHERE DateOfBirth > '1900-01-01'

JOINs — Combine data from multiple tables

-- INNER JOIN : retourne uniquement les enregistrements qui correspondent dans les deux tables
SELECT FirstName, LastName, PhoneNumber 
FROM Person.Person Per
INNER JOIN Person.PersonPhone Pho ON Per.BusinessEntityID = Pho.BusinessEntityID

-- Avec filtre (WHERE + LIKE pour pattern matching)
SELECT TOP 5 FirstName, LastName, PhoneNumber 
FROM Person.Person Per
INNER JOIN Person.PersonPhone Pho ON Per.BusinessEntityID = Pho.BusinessEntityID
WHERE NOT PhoneNumber LIKE '___-___-____'
ORDER BY FirstName

T-SQL also supports LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and subqueries.

4.2 T-SQL: Schemas, DDL and data types

Schemas

All objects in a SQL Server database are organized into schemas. Two schemas exist by default:

It is possible to create as many schemas as necessary to organize tables by department, use case, etc.

DDL — Create and modify objects

CREATE TABLE

-- Créer une table avec colonnes et types de données
CREATE TABLE [dbo].[TableA] (
    [FirstName]   [nvarchar](50)  NOT NULL,
    [LastName]    [nvarchar](50)  NOT NULL,
    [DateOfBirth] [date]          NULL,
    [Country]     [nvarchar](3)   NULL
) ON [PRIMARY]

ALTER TABLE — Modify a table

-- Ajouter une colonne
ALTER TABLE [dbo].[TableA] 
    ADD Test [nvarchar](3) NOT NULL

-- Supprimer une colonne
ALTER TABLE [dbo].[TableA] 
    DROP COLUMN Test

DROP TABLE — Drop a table

-- Supprimer entièrement la table
DROP TABLE TableA

Data Types

SQL Server supports many types of data grouped into several categories:

Why are data types important?

1. Storage space

On a table with billions of rows, the choice of data type can have a huge impact on storage.

2. Available operators

For example, you can perform date calculations only on date/time types, not on character strings.

3. Applicable functions

Some functions only apply to certain types. Ex.: EOMONTH() (end-of-month) only works with date type columns.

4. Unicode vs non-Unicode

• nvarchar/nchar: unicode storage (supports all character sets)
• varchar/char: storage non-unicode (ASCII)

If you don’t need unicode, using varchar saves space.

5. NULL vs NOT NULL

• NULL: the value may be absent (unknown)
• NOT NULL: the value must always be present

4.3 Other objects in SQL Server

Functions

SQL Server supports three types of functions:

SQL Server offers both built-in functions and user-defined functions (UDF) that you can define yourself.

-- Scalar function : calculer la fin du mois
SELECT EOMONTH(DateOfBirth) AS LastDayOfBirthMonth FROM TableA

-- Aggregate function : compter les enregistrements
SELECT COUNT(*) FROM TableA

-- Aggregate function : utilisation avec GROUP BY
SELECT Country, COUNT(*) AS NbPersonnes FROM TableA GROUP BY Country

Stored Procedures

stored procedures are precompiled collections of SQL statements and procedural logic. Their advantages:

-- Exemple de création d'une stored procedure
CREATE PROCEDURE GetPersonsByCountry
    @Country NVARCHAR(3)
AS
BEGIN
    SELECT FirstName, LastName, DateOfBirth
    FROM dbo.TableA
    WHERE Country = @Country
END

-- Appel de la stored procedure
EXEC GetPersonsByCountry @Country = 'CA'

Views

views allow complex queries to be simplified by encapsulating them in a reusable object. They can be used as tables in queries.

-- Créer une vue
CREATE VIEW vw_PersonWithPhone AS
SELECT p.FirstName, p.LastName, ph.PhoneNumber
FROM Person.Person p
INNER JOIN Person.PersonPhone ph ON p.BusinessEntityID = ph.BusinessEntityID

-- Utiliser la vue
SELECT * FROM vw_PersonWithPhone

System Tables

Each SQL Server database contains system tables that store metadata. The most common:

-- Récupérer les colonnes et leurs types pour la table TableA
SELECT c.Name AS ColumnName, ty.Name AS DataType
FROM sys.tables t 
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE t.Name = 'TableA' 
  AND ty.Name <> 'sysname'

4.4 Demo: T-SQL Examples (Notebook)

File: 04/demos.ipynb — Azure Data Studio Notebook

This notebook demonstrates fundamental T-SQL operations interactively. Each cell can be run individually, and the results are included in the notebook.

Create and use a database

-- Créer une nouvelle base de données
CREATE DATABASE TestDB

-- Changer le contexte vers TestDB
-- (toutes les requêtes suivantes s'exécutent dans TestDB)
USE TestDB

Create table

-- Créer la table TableA avec 4 colonnes
-- FirstName et LastName : nvarchar(50), NOT NULL (obligatoires)
-- DateOfBirth : date, NULL (optionnel)
-- Country : nvarchar(3), NULL (optionnel)
CREATE TABLE [dbo].[TableA] (
    [FirstName]   [nvarchar](50) NOT NULL,
    [LastName]    [nvarchar](50) NOT NULL,
    [DateOfBirth] [date]         NULL,
    [Country]     [nvarchar](3)  NULL
) ON [PRIMARY]

Consult data (initially empty table)

-- Retourne toutes les colonnes et toutes les lignes (aucune ligne pour l'instant)
SELECT * FROM TableA

Change table structure

-- Ajouter une colonne Test
ALTER TABLE [dbo].[TableA] 
    ADD Test [nvarchar](3) NOT NULL

SELECT * FROM TableA

-- Supprimer la colonne Test
ALTER TABLE [dbo].[TableA] 
    DROP COLUMN Test

View metadata

-- Récupérer les colonnes et leurs types via les system tables
SELECT c.Name, ty.Name 
FROM sys.tables t 
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE t.Name = 'TableA' 
  AND ty.Name <> 'sysname'

Insert data

-- INSERT avec VALUES
INSERT INTO TableA (FirstName, LastName, DateOfBirth, Country)
VALUES ('Ben', 'Weissman', '01.01.1900', 'GER')

SELECT * FROM TableA

-- INSERT avec SELECT (syntaxe alternative)
INSERT INTO TableA (FirstName, LastName, DateOfBirth, Country)
SELECT 'John', 'Doe', '01.01.1901', 'US'

SELECT * FROM TableA

-- Tentative d'insertion sans toutes les colonnes NOT NULL → va échouer
-- (FirstName seul, LastName est NOT NULL)
INSERT INTO TableA (FirstName)
SELECT FirstName FROM AdventureWorks2019.Person.Person A 

-- INSERT correct : FirstName et LastName depuis AdventureWorks2019
-- Notez : référencer une autre base via son nom complet (cross-database query)
INSERT INTO TableA (FirstName, LastName)
SELECT FirstName, LastName FROM AdventureWorks2019.Person.Person A 

SELECT * FROM TableA

Delete data

-- Supprimer les lignes où Country est NULL (importées sans country)
DELETE FROM TableA WHERE Country IS NULL

SELECT * FROM TableA

-- Supprimer les lignes où DateOfBirth est postérieure au 01.01.1900
DELETE FROM TableA WHERE DateOfBirth > '01.01.1900'

SELECT * FROM TableA

Modify data

-- Corriger le code pays de 'GER' à 'DE'
UPDATE TableA SET Country = 'DE' WHERE Country = 'GER'

SELECT * FROM TableA

Delete table

-- Supprimer entièrement la table
DROP TABLE TableA

-- Confirmation : la table n'existe plus (va retourner une erreur)
SELECT * FROM TableA

Queries on AdventureWorks2019

-- Changer le contexte vers AdventureWorks2019
USE AdventureWorks2019

-- Récupérer toutes les personnes
SELECT FirstName, LastName FROM Person.Person

-- Limiter à 5 résultats
SELECT TOP 5 FirstName, LastName FROM Person.Person

-- Trier par prénom
SELECT TOP 5 FirstName, LastName FROM Person.Person ORDER BY FirstName

-- JOIN : ajouter le numéro de téléphone
SELECT TOP 5 FirstName, LastName, PhoneNumber 
FROM Person.Person Per
INNER JOIN Person.PersonPhone Pho ON Per.BusinessEntityID = Pho.BusinessEntityID
ORDER BY FirstName

-- Filtrer les numéros de téléphone qui ne correspondent pas au format ###-###-####
SELECT TOP 5 FirstName, LastName, PhoneNumber 
FROM Person.Person Per
INNER JOIN Person.PersonPhone Pho ON Per.BusinessEntityID = Pho.BusinessEntityID
WHERE NOT PhoneNumber LIKE '___-___-____'
ORDER BY FirstName

4.5 Summary

• T-SQL can be used via DML and DDL to modify data structures and data stored in tables
• Objects in SQL Server are organized into schemas
• data types in SQL Server determine a column’s storage requirements and overall capabilities
• functions, stored procedures and views can be used to create reusable code and modules

5. Additional services and options

5.1 SQL Server Analysis Services (SSAS)

Analysis Services is actually two solutions in one because it is available in two distinct modes:

Multidimensional mode

Tabular mode

Although Tabular mode is often presented as more modern, both modes remain very relevant depending on the use case.

Typical architecture with SSAS

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│                 │     │                 │     │                 │
│  SQL Server     │────▶│  Analysis       │────▶│  BI Tool        │
│  (Data          │     │  Services       │     │  (Excel,        │
│   Warehouse)    │     │  (Data Mart)    │     │   Power BI)     │
│                 │     │                 │     │                 │
└─────────────────┘     └─────────────────┘     └─────────────────┘

• SQL Server → Data Warehouse (data source)
• SSAS → Data Mart (analytics layer)
• BI Tool → Visualization and analysis layer (Excel, Power BI, etc.)

Tools needed for SSAS and SSIS

To use Analysis Services and Integration Services, Visual Studio Data Tools is required:

# Installer Visual Studio Community edition (gratuite même en environnement commercial
# si utilisée uniquement pour le développement SQL Server)
choco install visualstudio2019community -y

# Installer l'extension SSIS pour Visual Studio
choco install ssis-vs2019 -y

Visual Studio Community is free, even in commercial environments, if used exclusively for SQL Server development.

5.2 SQL Server Integration Services (SSIS)

SSIS (SQL Server Integration Services) is the integrated ETL solution for SQL Server.

Overview

Supported data sources

SSIS can connect to a wide variety of sources:

• CSV files
• Excel files
• Databases Oracle
• SQL Server
• And many other sources via connectors

Structure of an SSIS solution

Integration Services solutions are organized into packages (.dtsx files):

Solution SSIS
└── Package.dtsx
    └── Control Flow (Orchestration)
        ├── Tâche 1 : Execute SQL Task (TRUNCATE TABLE)
        ├── Tâche 2 : Data Flow Task
        │   ├── Source (ex. : OLE DB Source → AdventureWorks)
        │   ├── Transformation (optionnel : conversion, nettoyage)
        │   └── Destination (ex. : OLE DB Destination → TestDB)
        └── Tâche 3 : (autres tâches si nécessaire)

Elements of an SSIS package

Control Flow — The orchestration layer. It may contain:

• Execute SQL Task: Execute SQL queries (e.g.: TRUNCATE TABLE)
• Data Flow Task: Copy/transform data from a source to a destination
• File System Task: File operations
• FTP Task: FTP file transfer
• Send Mail Task: Sending emails
• Loops: Foreach Loop Container, For Loop Container
• Variables and parameters

Data Flow Task — The data transfer layer:

Demo: Transferring a table with SSIS

1. Open Visual Studio
2. File → New Project → Search for “SSIS”
3. Choose Integration Services Project → Next
4. Name the project (e.g.: IntegrationServicesProject1) → Create
5. A Package.dtsx file is automatically created
6. In the SSIS toolbox, drag a Data Flow Task into the Control Flow
7. Double-click the Data Flow Task to edit it
8. Add an OLE DB Source → configure the connection to AdventureWorks2019
9. Add an OLE DB Destination → configure the connection to TestDB
10. Link Source → Destination with the data flow (data path)
11. Run package (F5 or Start button)

Note: Third-party tools can add even more tasks and components to SSIS.

5.3 SQL Server Reporting Services (SSRS)

SSRS (SQL Server Reporting Services) is the integrated visualization layer of SQL Server.

Features

Example of use

• Report filterable by cost center and month
• Report filterable by employee and year
• Reports sent automatically at defined intervals (e.g. every Monday morning)

Current trend

In recent years, more and more organizations are migrating to Power BI or Power BI Paginated Reports rather than SSRS. SSRS seems to be gradually losing its relevance in the market.

5.4 Hybrid features in SQL Server

Particularly in SQL Server 2022, many hybrid features have been introduced to allow businesses to adopt the cloud while keeping SQL Server as an on-premises product.

Azure Arc Enablement

Azure Arc allows you to link metadata from your SQL Server to the Azure cloud.

Operation:

• SQL Server continually reports metrics and log files to Azure
• Allows you to manage a multitude of SQL Servers from different locations (and even other public clouds) via Azure

Features offered:

Compatibility:

Azure Synapse Link for SQL Server

This feature allows you to replicate OLTP tables from your SQL Server to an Azure Synapse workspace in near real-time.

Architecture:

┌──────────────┐     ┌─────────────────────────┐     ┌──────────────────┐
│              │     │                         │     │                  │
│  SQL Server  │────▶│  Self-hosted Integration│────▶│  Azure Data Lake │
│  (On-prem)   │     │  Runtime                │     │  Storage (ADLS)  │
│              │     │                         │     │       │          │
└──────────────┘     └─────────────────────────┘     │       ▼          │
                                                      │  Dedicated SQL   │
                                                      │  Pool (Synapse)  │
                                                      └──────────────────┘

Operation:

1. Install a self-hosted integration runtime
2. This uses an Azure Data Lake Storage (ADLS) as an intermediary
3. It feeds a dedicated SQL pool in Azure Synapse
4. Once configured, retrieve the list of SQL Server tables
5. Select tables to replicate to Synapse

Current Limitations: This feature comes with some important restrictions as of the course date.

SQL Managed Instance Link

This feature allows you to create a link between your SQL Server on-premises and an Azure SQL Managed Instance in the cloud.

Operation:

• An on-premises SQL Server and a SQL Managed Instance in the cloud are connected via Managed Instance Link (ML Link)
• Users are accessing their SQL Server on-premises normally
• The Managed Instance in the cloud can be used as a failover or for hybrid scenarios

5.5 Summary

• Analysis Services (SSAS) can be used for quick analytical insights (Multidimensional mode with MDX, or Tabular mode with DAX)
• Integration Services (SSIS) is SQL Server’s integrated ETL solution
• Reporting Services (SSRS) can be used to visualize data and send reports based on scheduled subscriptions
• SQL Server 2022 (and some earlier versions) provides strong integration with the Microsoft Azure cloud via Azure Arc, Azure Synapse Link, and SQL Managed Instance Link

6. Appendix: Training files

Directory structure

introduction-sql-server/
├── 02/
│   ├── demos.ps1                          # Script PowerShell : installation SQL Server
│   └── introduction-to-sql-server-slides.pdf
├── 03/
│   ├── demos.ps1                          # Script PowerShell : outils clients
│   └── sql-server-client-tools-slides.pdf
├── 04/
│   ├── demos.ipynb                        # Notebook Azure Data Studio : T-SQL
│   └── introduction-to-t-sql-slides.pdf
├── 05/
│   ├── demos.ps1                          # Script PowerShell : Visual Studio + SSIS
│   └── additional-services-and-options-slides.pdf
├── exercise-files.zip
└── texte_vocal.txt

Useful links

• SSMS Download
• Azure Data Studio Download
• SQLCMD (new Go version)
• AdventureWorks Sample Database
• DBAtools (PowerShell)

Search Terms

sql · server · microsoft · databases · data · tools · ssis · client · installing · services · azure · installation · rdbms · sqlcmd · ssas · t-sql · types · ads · adventureworks2019 · database · ddl · delete · dml · environment