Overview

The MS SQL DBMS service is offered by Swisscom in different flavours, which have different degrees of management respectively grant different degrees of autonomy to the customers.

This documentation starts with an overview which cover basically the configuration which are valid for all service flavours, stepping then in the particularity of each service.

The Microsoft SQL Server product is a system that includes the installation and configuration of a MS-SQL DBMS. The basic configurations are done according to Microsoft Best Practices.

The MS-SQL DBMS standalone is offered in three flavours

• Unmanaged
• DBA Mode
• Managed

in the following versions

• MS-SQL Server 2017 (last cumulative update and GDR if apply) on Windows 2016
• MS-SQL Server 2019 (last cumulative update and GDR if apply) on Windows 2019
• MS-SQL Server 2022 (last cumulative update and GDR if apply) on Windows 2019

and editions

• Standard
• Enterprise
• Developer

Details about the features differences between editions can be found here Microsoft editions comparisons

Service matrix

The following table summarize the main differences between the offered services:

Service	MS SQL DBMS unmanaged	MS SQL DBMS DBA Mode	Managed MS SQL DBMS
SQL Setup 2017	X	X	X
SQL Setup 2019	X	X	X
SQL Setup 2022	X	X	X
DBMS Basic configuration	X	X	X
Managed OS	o	X	X
DBMS System DB' Backup (ABB or local jobs)	o	X	X
DBMS Maintenance Jobs	o	X	X
DBMS service monitoring	o	o	X
Permission Model	o	o	X
DBMS Support*	o	o	X
Day 2 actions	o	X **	X
SQL AlwaysOn	o	o	X

X = available

o = not available

*Please note that performance issues or analysis are not part of the support except infrastructure issues.

**Day 2 actions are available, but the support is limited to guarantee their availability only. Misfunctions due to wrong DBMS configurations are not covered. However professional services can be requested to analyze issues and eventually to repristinate the default status.

Service capabilities

The purpose of the following table is to gain a quick overview of the available features or allowed actions on the different services.

Capability/Service	MS SQL DBMS unmanaged*	Managed MS SQL DBMS DBA mode	Managed MS SQL Server
RDP connections	X	O	O
OS admin access	X	O	O
Access local drives	X	P**	P**
Additional application installation	X	O	O
Additional SQL components installation (SSIS, SSRS etc.)	X	O	O
Load OS certificates	X	O	O
Load SQL certificates	X	X	O
Sysadmin (DBMS) access	X	X	O
Remote WMI/Powershell Access	X	P***	P***
Transparent Data Encryption (TDE)****	X	X	X
TLS Encryption****	X	X	X

X = available

O = not available

*The service is IaaS with full rights for the customer. Therefore all action are allowed but the configuration is self service.

P = partially available (refer to Technical Product Description for details)

**Access local drive: access to drive X for local backup

***Remote WMI/Powershell Access

**** TDE and TLS are not available on SQL AlwaysOn

Licensing

There are two licensing model that can be chosen:

• BYOL (Bring your own licensing): customers have their own SQL licenses. It is important to have enough licenses to cover all CPU used by the DBMS systems.
• Swisscom SQL licenses: Swisscom provides the SQL licensing.

SQL Sever installation and configurations

This chapter covers the basic setup configurations.

SQL Server installed components

The following general configurations apply to all MS-SQL DBMS flavours. Specific configurations for the unmanaged and managed flavours can be found in the services related chapters below.

Installed features:

• .NET Framework 4.0
• Database Engine Services
• SQL Server Agent
• Management Tools Complete (SQL Management Studio)
• SQL Server Replication
• Full-Text and Semantic Extraction for Search
• Data Quality Services
• Client Tools Connectivity
• Client Tools Backwards Compatibility

SQL Server system configurations

The configurations below apply to all service flavours and are part of the basic setup.

Item	Configuration
Supported OS	Windows 2016, Windows 2019
Binaries path	C:\Program Files\SQL Server
Data Volume	D:\
Log Volume	E:\
TempDb Volume	T:\
Backup Volume	X:\
Local FW rule	1433
Disk Chunk SQL Volume	64 Kb

This is an information message

Remark

If Networker ABB is not available in the tenant, make sure to foresee enough disk space on drive X: in order to store full and transaction log backup file during the 5 days retention time. Backup compression is enabled which in normal cases reduce the backup size to the half of the original DB size. However if you store for instance file in pdf or jpg format the compression rate will be close to 0 causing the backup files to have more or less the same size of the DB.

If Networker ABB is available size the X: drive in order to be able to use for Import/Export DB actions (if needed).

SQL Services

Service	Startup	Service Account
SQL Full-text Filter Daemon Launcher (MSSQLSERVER)	Manual	NT Service\MSSQLFDLauncher
SQL Server Browser	Disabled	n/a

SQL Server configurations

Setting	Konfiguration
Min. SQL Memory	8 GB
Max. SQL Memory	VM total capacity minus 4 GB (4 GB reserved for OS)
CPU Affinity Mask	Default
Authentication	Mixed Mode
Max Degree of Parallelism	Equal no. of CPU, max. 8
Login Auditing	Failed Login only
Data Default Location	D:\SQL_Server\Default\UserData\
Log Default Location	E:\SQL_Server\Default\UserLog\
TempDB Default Location	T:\SQL_Server\Default\TempDB\
TempDB Data Files	TempDB is distributed on multiple files according to the number of VM cores
Backup Default Location	X:\SQL_Dump\Default\
xp_cmdshell	disabled
CLR Support	disabled
Backup Compression	enabled
Named Pipes	disabled
Port	1433
Trace Flags	-T3226

Service types

MS-SQL DBMS Unmanaged

Description

The Microsoft SQL Server product is an unmanaged system that includes the installation and configuration of a MS-SQL DBMS.

There are no SQL backup neither SQL maintenance jobs in place. Customer are free to define their maintenance.

States

As the DBMS is unmanaged, the states correspond to the Virtual Machine's states, see below:

• Running
• Suspended
• Stopped
• Deleted

Actions

Basically day 2 actions are implemented for managed services only. For the unmanaged DBMS the following actions are available:

• Create Database
• Delete Database
• Stop Services
• Start Services

Permissions

Customers will be granted with full admin rights at OS and SQL Level.

Services

Service	Startup	Service Account
SQL Server (MSSQLServer)	Automatic	Local System
SQL Server Agent (MSSQLServer)	Automatic	Local System
SQL Full-text Filter Daemon Launcher (MSSQLSERVER)	Manual	NT Service\MSSQLFDLauncher
SQL Server Browser	Disabled	n/a

SQL configurations

As customers are granted with admin rights at OS and SQL level, any modifications of the default settings are possible.

MS-SQL DBMS Managed

Description

The managed Microsoft SQL Server product is a system that includes the installation and configuration of a managed OS (Managed OS TPD) along with managed MS-SQL DBMS on top. Configurations on OS and DBMS are made according to Microsoft Best Practices.

Swisscom take over the management of the DBMS, but not of the databases. Refer please to the above service matrix for an overview about managed items.

Preconditions and basics

The user needs permission on entitlements to request the blueprint via the catalog or the API.

The managed DBMS is joined automatically by the setup to the resource domain. The domain join is mandatory and cannot be changed.

Managed OS differences

Basically the underlying managed OS is the same as described onto Managed OS TPD.

The following differences are implemented:

Permissions at OS level

Permissions at the OS level are reserved for Swisscom. RDP connections are not allowed.

Connections to the SQL Server can be established through SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT) or Visual Studio (VS) from a remote host, see Access Layout.

Tempadmin mode

As the OS admin rights are reserved to Swisscom the tempadmin mode is not available.

SQL configurations and options

SQL Services

Service	Startup	Service Account
SQL Server (MSSQLServer)	Automatic	Resource domain service account
SQL Server Agent (MSSQLServer)	Automatic	Resource domain service account
SQL Full-text Filter Daemon Launcher (MSSQLSERVER)	Manual	NT Service\MSSQLFDLauncher
SQL Server Browser	Disabled	n/a

SQL customer permissions

On managed DBMS the customer is not granted with sysadmin. In order to enable access to database and do certain configuration on the DBMS a permission model has been implemented.

Note:: It's important to understand the permission model in order to be able to choose the right service.

The permission model is described in detail here Permissions Model.

Backup and Restore

As part of the managed service a database backup is implemented.

For a detailed description of backup and restore see this documentation Backup and Restore.

Maintenance Plans

With the managed DBMS standard plans for DBMS and database maintenance (like reindexing) are pre installed.

The plans and scheduling are described in detail in this documentation Maintenance plans.

Swisscom DBMS access

Swisscom adds the following groups/users to the DBMS in order operate it, for day 2 actions execution and backup functionalities.

Group/account	Permissions	Purpose
resourceDomain\SYS-ESC-deployID	sysAdmin	Execution account for day 2 actions
resourceDomain\ABB-ESC-deployID	sysAdmin	Networker SQL account
resourceDomain\DL_ESC-MOS-SQL-CoreService_S_10	sysAdmin	Local group for administrative service accounts

This accounts and group should not be removed neither modified.

Monitoring

The system will be monitored as Managed OS.

The database management system will be monitored as Managed MS SQL.

In addition to the system volume, the SQL drives as well the SQL service will be monitored.

MS SQL DBMS DBA Mode

Description

The MS SQL DBMS DBA mode uses the same basic blueprint described in the MS SQL overview.

Basically, setup and configurations are the same as the MS SQL DBMS Managed service, however the DBMS DBA Mode is an independent service with an own blueprint and the following two main differences:

• Customers gets sysadmin rights on the DBMS, therefore the permission model is not applied (see details in the following chapters)
• The DBMS is not managed, however the day 2 actions implemented in the managed flavour will be available (refer please to the service matrix )

This service is indicated for customers who want to take full control of the DBMS having a managed OS as base.

This setup is recommended for the deployment of application which require higher rights at DBMS level (like MS Sharepoint) or if features/configurations are required, which are not supported by the Managed DBMS through SSMS or jobs (for instance xp_cmdshell, enabling CLR).

Local access to the system through RDP (i.e. tempadmin) is not allowed analogous to Managed DBMS.

SQL Permissions

In the order form customers will define a SQL login which will be granted with sysadmin server role membership.

The permission model of the MS SQL DBMS managed service will not be applied. This means that also the physical owner of the database will not be set automatically.

The day 2 actions have been adapted so that the stored procedures execution through SSMS or SSDT remote connection can be executed by member of the sysadmin role only. Day 2 actions executed through vRA, portal or API will work based on the entitlements. Furthermore their behaviour can be different as the in the Managed service, please check the explanation in the user manual SSMS actions, chapter 1.1, for details.

Included functionalities

The functionalities are the same as the Managed DBMS, however note the restrictions listed in the next chapter.

Configurations

After installation the customer is responsible for DBMS configuration and maintenance. Please be aware that some configurations can affect performance, security or the DBMS behaviour.

Security

Customers fully control the access to the DBMS and data. As members of the sysAdmin role, they will be able to fully control the access granting also higher privileges to users or group.

It is recommended to grant higher rights to DBA's or users with good SQL knowledge only. As mentioned above, Swisscom will configure specific permissions and objects (i.e. scs_AdminDB) in order to provide backup, maintenance, patching and day 2 actions. Misconfiguration of permissions or objects can lead to malfunctioning of the capabilities.

Swisscom is not taking any responsibility for failures due to misconfigurations.

The following chapters summarize the most important possible issues caused by wrong security handling.

Swisscom access

As sysadmin role members completely control the access to the DBMS, they can theoretically limit or deny access of Swisscom groups or service account. Therefore Swisscom cannot guarantee that the built-in functionalities will always work properly. Also changing the password of the scs_sa will restrict Swisscom access.

For instance removing the ABB-ESC-deployID account from the sysadmin role will cause the backup to fail.

Swisscom sets the same access group described in the chapter Swisscom DBMS Access.

Day 2 actions / scs_AdminDB

If the resourceDomain\SYS-ESC-deployID is not granted with sysAdmin rights or even completely removed from the DMBS, day 2 actions started through portal, vRA, vRO or API call won't work anymore.

Furthermore if modifications are taken at scs_AdminDB, for instance changing permissions, deleting jobs etc. the behaviour of day 2 actions could be compromised as well as operation routines (backup, maintenance).

Note: permissions to execute the stored procedures are not set through Swisscom for this service. Therefore if users, who are not member of sysadmin role and should be entitled to execute stored procedures of the scs_AdminDB through SSMS, have to be granted with permissions.

Backup

For system without SQL ABB the backup will be executed through local backup jobs. This jobs can be modified or even deleted compromising the ability to restore data or even to recover the DBMS.

Independently if SQL ABB is installed or not, member of the sysAdmin rights or user granted with higher msdb privileges can configure parallel backups which can brake the backup chain compromising the ability to restore data.

Maintenance plans

For the maintenance plans jobs and objects (i.e. reindexing, check DB consistency and statistics maintenance) the same considerations as for the backup chapter apply. The impact is less severe as misconfigured backup jobs, however wrong scheduled maintenance plans can cause performance issues.

This is an information message

Note to Swisscom jobs:

All jobs created by the Swisscom setup follows a name convention having the prefix SCS_jobname. This way you will able to identify the jobs very easily.

Monitoring

The system will be monitored as Managed OS. Please note that only the drive C:\ will be monitored.

There will be no DBMS monitoring in place from Swisscom side as we have no control over the DBMS. This include also the SQL service.

The customer can put in place a monitor use WMI and/or remote powershell applying the procedure described in the OS documentation.

The WMI access doesn't grant rights to read the SQL services state. However with this powershell command I*nvoke-Sqlcmd -Query "SELECTservicename, status_desc FROM sys.dm_server_services" -ServerInstance myservername* the state of the service can be retrieved.

Support

Swisscom is basically not responsible for DBMS because of the higher rights. Therefore customers cannot open INC related to DBMS issues. If support from Swisscom DBA's is needed customer can however open a service request, requiring consulting (professional services) which will charged separately from the service.

Service change

Changing the service from DBA mode to Managed SQL or from Managed SQL to DBA mode is not allowed and not foreseen.

MS SQL AlwaysOn

Overview

SQL AlwaysOn (SQLAO) aims to ensure high availability for SQL database in order to avoid downtime during patching of VM's or ESX hosts maintenance.

Terminology and short description

Term	Description
Advanced service level	vMotion between data center is active. ICT service continuity RTO 4h
Gold	Data centers in Zürich/Olten
Platinum	Data centers in Bern/Zollikofen
Standard service level	vMotion between data center is disabled. ICT service continuity "best effort"
Storage Fast-SRev	Up to 1500 IOPS /TB Average latency < 6ms max 10 TB pro VM /max 10 Disks
Storage Ultrafast-SRev	Up to 5000 IOPS /TB Average latency < 2ms max 10 TB pro VM /max 10 Disks

In order to setup a SQLAO a WSFC(Windows Server Failover Cluster) is needed as base. For details about the WSFC build refer please to OS technical description. Focus of the SQLAO is to avoid downtime during OS patching, ESX host maintenance or in case of VM crash. The handling in disaster case, i.e. if a data center is completely down, corresponds to the service level of the underlying infrastructure (VM service level) and Windows Server Failover Cluster (WSFC) capabilities.

Note: For communications between the AO nodes some tenants have specific network rule sets for accessing the File Share Witness (there is 1 FSW deployed per tenant). We recommend to check with the tenant owner about these specifics. In some tenants this is solved by adding a "Security Tag" named "FSW-AlwaysOn" in the portal, in the "General" tab section of the cluster when deploying a new SQLAO cluster.

AlwaysOn settings at glance

• SQL 2016/2017/2019 Enterprise Edition configured for AlwaysOn
• Availability mode: synchronous-commit mode
• Automatic failover
• Backup on primary
• Seeding enabled

Service specific options

• Service level: advanced default (mirrored storage), option for standard (single site storage). The chosen service level cannot be changed.
• Storage: UltraFast-SRev default, option for Fast-SRev. The volume size cannot be changed after provisioning.

Restrictions

• Unmanaged is not available
• DBA mode is not available
• Developer and Standard Editions are not available
• Customer maintenance mode is not available
• Reconfiguration of VM's, i.e. CPU or RAM is not available

Note: As VM reconfiguration is not available size your system so that it can fit your load at order time

MS SQL AlwaysOn setup

SQLAO is built on the existing SQL standalone managed service described in the chapter MS SQL DBMS Managed. Please note the limitations described in the Service Capabilities chapter.

Objects naming convention

The AD objects will follow the specified naming convention below.

The name convention is mandatory, i.e. neither the listener nor the availability group names can be chosen.

Name	Convention
Listener names	Listener 1: AO-DeploymentID-0-L01 Listener 2: AO-DeploymentID-0-L02
SQL Service Account (same account for both nodes)	SA-ESC-DeploymentID-0
Availability group name	AG-L01, AG-L02, AG-L03

SQL AO specific configurations

In the table below are listed the configuration that have to be modified against the actual Managed SQL 2016 Service.

Configuration	Change	Description
Min Memory	8 GB	It is recommended to let 4 GB for the OS. For this reason the minimum amount of memory will be 8. 4 GB will be reserved for the OS, therefore the SQL instance will have a minimum of 4 GB RAM.
Max Memory	704 GB	For the same reason described for max core the maximum memory will be set to 704 GB in order to fit in the distributed memory across CPU sockets.
Max CPU	22 Cores	The max core size is set to 22 CPU per VM to avoid memory remote access and therefore optimize performance. This configuration depends on the underlying ESX HW. I.e. the VM cores will fit in one CPU socket.
Cores increase number	8, 16 max 22	Basically SQLAO host critical DB's with need for performance. For this reason the increase steps for CPU are higher as for the standalone service.
Port	5022	Port for data synchronization.
Port	1433	SQL instance
Version	SQL 2016 only	Base is managed Managed SQL 2016/2017/2019.
Edition	Enterprise only	Enterprise Edition is chosen to enable fully SQL AO functionalities.
Service	Service option	SQL AlwaysOn enabled.
Trace flag	9567	Enables compression of the data stream for Always On Availability Groups during automatic seeding.
Trace flag	9592	Enables log stream compression for synchronous availability groups.

SQL AlwaysOn specific settings

Configuration	Setting	Description
Listener	Multiple on port 1433	Up to 3 listener can be configured. The name will be set according to AD naming convention.
Availability Group	Multiple	For each listener an availability group will be automatically created.
Failover mode	Automatic	The failover mode is set to automatic and cannot be changed.
Availability mode	Synchronous-commit mode	To guarantee automatic failover, this mode is mandatory.
Readable Replica	Disabled	This setting can be changed through a day 2 action.
Seeding	Enabled	To avoid backup/restore processing to add databases to the replica automatic seeding will be default enabled.
Backup	On primary	Based on the existing SQL service jobs.

Note: These settings cannot be modified.

Customer permissions and actions

At SQL instance level the same permissions as the standalone services are applied. Furthermore the following stored procedure have been added to the SQL instances in order to enable the customer DBA to execute the actions listed below.

Action	Stored procedure's name	Description
Add DB to availability group	customer.sp_AO_addDBtoAG	An existing DB can be added to an existing availability group.
Remove DB from availability group	customer.sp_AO_removeDBfromAG	A DB can be removed from an availability group. The DB will be then removed on the replica, but not from the primary.
Move availability group	customer.sp_AO_moveAG	An availability group can be moved from one node to the other.
Fix SQL logins	customer.sp_AO_fixLogin	This action fix SQL logins across instances, i.e. orphaned logins. The procedure doesn't replicate the SQL login and doesn't work for Windows Login. It can be used with SQL logins which have been created manually on the replica.

At portal, vRA and API level the following day 2 actions have being implemented:

• Add database to availability group
• Move availability group
• Remove database from availability group
• Set availability group replica to readable

Note: Setting the replica to readable can affect system's performance.

Remarks on SQL AO high availability

Despite the SQL AO service grants high availability to databases joined to an AG(Availability), it doesn't for objects configured outside the database. Especially the following items should be considered:

Logins

There is no replication mechanism for logins in place. This means that you will have to replicate new logins manually. SQL logins will have another sid on the replica, if you create it without extracting the sid of the primary (see FAQ code hint). For this reason we recommend to work, if possible, with Windows user and/or groups. If logins are not configured on the replica in case of failover the databases cannot be accessed.

Scheduled jobs

The standard backup and maintenance jobs are configured on both nodes and don't need replication. Custom SQL Jobs are not replicated to the secondary replica. If jobs are configured on primary replica only, in case of a failover, nothing will happen on the replica.