# SQL Server - Features

## Architecture

### Topologies <a href="#sqlserverarchitecturefr-topologies" id="sqlserverarchitecturefr-topologies"></a>

Two topologies are available:

* Standalone Instance
* Always On Cluster

### Always On - Topology <a href="#sqlserverarchitecture-alwayson" id="sqlserverarchitecture-alwayson"></a>

The Always On cluster configuration is based on a 3-node topology:

#### **Active Nodes**

* Two nodes located on the same site.
* These nodes are configured to share the load or automatically failover in case of a failure.
* An anti-affinity rule ensures that the active nodes do not coexist on the same hypervisor host, thus enhancing resilience.

#### **Passive Node**

* Located on a secondary site to ensure disaster recovery (DR).
* This node does not handle any active requests and is reserved exclusively for failover in the event of active node failure.

#### Rules and Restrictions for the Passive Node <a href="#sqlserverarchitecture-alwayson-passiverules" id="sqlserverarchitecture-alwayson-passiverules"></a>

The passive node is subject to strict restrictions to comply with Microsoft License Mobility with Failover Rights:

* **No active workload:** The passive node cannot execute SQL queries, reports, or user workloads.
* **Allowed operations:**
  * Database consistency checks.
  * Full backups and transaction log backups.
  * Performance and resource monitoring.
* **Optimized licensing:** With Software Assurance, the use of the passive node is included at no additional cost, provided these restrictions are followed.

#### Benefits of Always On <a href="#sqlserverarchitecture-alwayson-advantages" id="sqlserverarchitecture-alwayson-advantages"></a>

* **Fault tolerance:** Synchronous replication ensures that data is available in real-time on active nodes.
* **Disaster recovery:** Deploying a passive node on a secondary site enhances security and business continuity.
* **Simplified maintenance:** Planned failovers allow updates or technical interventions without service interruption.

#### Monitoring and Compliance <a href="#sqlserverarchitecture-alwayson-monitoring" id="sqlserverarchitecture-alwayson-monitoring"></a>

Specific monitoring tailored for the Always On cluster is in place to:

* Ensure compliance with restrictions related to the passive node.
* Monitor performance and automatic failovers.
* Prevent risks of non-compliance with licensing rules.

### Regions <a href="#sqlserverarchitecture-regions" id="sqlserverarchitecture-regions"></a>

SQL Server is available on both cegedim.cloud's data centers:

* EB4 - Boulogne-Billancourt, France
* ET1 - Labège, France

As part of the Always On topology, an inactive node is automatically deployed in a nearby secondary site to enhance the resilience of the cluster:

* EB5 (Magny-les-Hameaux, France)
* ET2 (Balma, France)

### Hosting and Versions <a href="#sqlserverarchitecture-hostingandversion" id="sqlserverarchitecture-hostingandversion"></a>

<table><thead><tr><th width="160">Hosting type</th><th width="182">SQL Server version</th><th width="198">Operating System</th><th>SQL Server edition</th></tr></thead><tbody><tr><td>Virtual</td><td>2022</td><td>Windows Server 2022</td><td>Standard or Enterprise</td></tr><tr><td>Virtual</td><td>2019</td><td>Windows Server 2019</td><td>Standard or Enterprise</td></tr><tr><td>Virtual</td><td>2017</td><td>Windows Server 2019</td><td>Standard or Enterprise</td></tr><tr><td>Virtual</td><td>2016</td><td>Windows Server 2016</td><td>Standard or Enterprise</td></tr></tbody></table>

### File system <a href="#sqlserverarchitecture-filesystem" id="sqlserverarchitecture-filesystem"></a>

Filesystem layout:

<table><thead><tr><th width="102">Drive</th><th>Label</th><th width="171">Default size</th><th>Description</th></tr></thead><tbody><tr><td>D:\</td><td>MSSQL</td><td>30 GB</td><td>Root instance</td></tr><tr><td>E:\</td><td>MSSQL_USER_DATA</td><td>30 GB</td><td>User databases</td></tr><tr><td>F:\</td><td>MSSQL_USER_LOG</td><td>10 GB</td><td>User databases log</td></tr><tr><td>G:\</td><td>MSSQL_TEMPDB</td><td>10 GB</td><td>TempDB</td></tr></tbody></table>

### Virtual machine name restriction <a href="#sqlserverarchitecture-virtualmachinenamerestriction" id="sqlserverarchitecture-virtualmachinenamerestriction"></a>

Due to prefixes applied to Active Directory objects, the name of the virtual machine provisioned is **restricted to 13 characters maximum** for a cegedim.cloud PaaS SQL Server.

### Ports <a href="#sqlserverarchitecture-ports" id="sqlserverarchitecture-ports"></a>

Ports listing:

<table><thead><tr><th width="163.33333333333331">Port</th><th width="344">Description</th><th>Protocol</th></tr></thead><tbody><tr><td>1433</td><td>Server static port listener</td><td>TCP</td></tr><tr><td>1434</td><td>SQL Server Browser</td><td>UDP</td></tr><tr><td>2382</td><td>SQL Server Analysis Services Browser</td><td>UDP</td></tr><tr><td>2383</td><td>SQL Server Analysis Services listener</td><td>TCP</td></tr><tr><td>5022</td><td>SQL Server BDM/AG Endpoint</td><td>TCP</td></tr></tbody></table>

{% hint style="warning" %}
Only the SQL Server listener and SQL Server Browser ports are opened inbound in the Windows Firewall by default and enforced through a GPO on the Organization unit.
{% endhint %}

### Modules installed <a href="#sqlserverarchitecture-modulesinstalled" id="sqlserverarchitecture-modulesinstalled"></a>

List of modules installed by default during provisioning:

* Database engine
* Replication
* Full-text Search
* Client tools connectivity
* SDK

### Features <a href="#sqlserverarchitecture-features" id="sqlserverarchitecture-features"></a>

This section is to list which feature / capabilities are available to customer, and how to request / perform them:

<table data-header-hidden><thead><tr><th width="157"></th><th></th></tr></thead><tbody><tr><td><strong>Self Service</strong></td><td>Customer can perform action autonomously.</td></tr><tr><td><strong>On Request</strong></td><td>Customer can request for the action to be done to cegedim.cloud support team.</td></tr></tbody></table>

<table><thead><tr><th width="217">Features</th><th width="139" data-type="checkbox">Self Service</th><th width="144" data-type="checkbox">On Request</th><th>Comments</th></tr></thead><tbody><tr><td>Database Collation</td><td>true</td><td>false</td><td><br></td></tr><tr><td>Integration Services</td><td>true</td><td>true</td><td><br></td></tr><tr><td>Analysis Services</td><td>true</td><td>true</td><td><br></td></tr><tr><td>Reporting Services</td><td>true</td><td>true</td><td><br></td></tr><tr><td>Full-Text Search</td><td>true</td><td>true</td><td><br></td></tr><tr><td>Export, Import SQL Server backup</td><td>false</td><td>true</td><td><br></td></tr><tr><td>Create Always On cluster</td><td>true</td><td>true</td><td>Available exclusively for SQL Server 2022 Enterprise edition, consult your service delivery manager for guidance</td></tr></tbody></table>

## Security <a href="#sqlserverarchitecture-security" id="sqlserverarchitecture-security"></a>

### System login <a href="#sqlserverarchitecture-systemlogin" id="sqlserverarchitecture-systemlogin"></a>

The SQL Server PaaS runs exclusively in a Windows environment. The standard system login method is RDP (Remote Desktop Protocol).

In order to connect to the virtual machine, you need to have the required privileges either at the domain level or at the local machine level.

### Instance login <a href="#sqlserverarchitecture-instancelogin" id="sqlserverarchitecture-instancelogin"></a>

Authentication is configured by default in **mixed mode** which provides two login types:

* **SQL Server login**: instance level
* **Active directory user**: domain level - Embedded Windows authentication

Instance login is available locally or remotely:

* **Locally**: once connected in RDP, launch the local SQL Server Management Studio
* **Remotely**: launch the SQL Server Management Studio and specify the target instance

#### Locally <a href="#sqlserverarchitecture-locally" id="sqlserverarchitecture-locally"></a>

SSMS can use the Windows user credentials you're already logged with through RDP to login to the SQL Server instance.

Authentication with an SQL login is also possible locally.

#### Remotely <a href="#sqlserverarchitecture-remotely" id="sqlserverarchitecture-remotely"></a>

Specify a target instance in the server name field enforcing the tcp protocol: `tcp:HOSTNAME\INSTANCENAME`

Just select **"SQL Server Authentication"** and provide the SQL Login with the associated password.

### Authorizations

Authorizations for cegedim.cloud teams are managed by GPO.

### Authorization and passwords <a href="#sqlserverarchitecture-authorizationandpasswords" id="sqlserverarchitecture-authorizationandpasswords"></a>

This section list the password management for the SQL Server PaaS:

<table><thead><tr><th width="221">Password</th><th width="230" data-type="checkbox">Stored by cegedim.cloud</th><th width="191" data-type="checkbox">Stored by Customer</th><th data-type="checkbox">Enforced</th></tr></thead><tbody><tr><td><strong>admin</strong> account</td><td>false</td><td>true</td><td>false</td></tr><tr><td><strong>ANY</strong> other account</td><td>false</td><td>true</td><td>false</td></tr><tr><td><strong>cgdm_admin</strong> account</td><td>true</td><td>false</td><td>true</td></tr><tr><td><strong>monitoring</strong> account</td><td>true</td><td>false</td><td>true</td></tr></tbody></table>

#### Customers

Authorizations for customers are managed by the customers itself.

The customer that request a **SQL Server** instance through **ITCare** will be automatically granted to connect on the instance. He can grant access to any Active Directory user or group afterwards.

### Patching <a href="#sqlserverarchitecture-patching" id="sqlserverarchitecture-patching"></a>

Patchs are installed during "Patch parties" managed by cegedim.cloud every quarter.

An instance can be patched manually exceptionally if security or bug fixes requires it.

### Data location <a href="#sqlserverarchitecture-datalocation" id="sqlserverarchitecture-datalocation"></a>

Datas for cegedim.cloud's SQL Server PaaS are stored on the dedicated virtual machines created upon requesting a PaaS.

These virtual machines and the storage associated are hosted and managed in cegedim.cloud's own data centers.