Database Server

This server role can be run in either a virtual or physical environment. If you already have SQL workloads on your Virtual Infrastructure [VI] then there should be no issues – the key is to ensure that performance is not adversely affected by choosing to virtualise the database servers, so having existing workloads of this type is useful in assuring no issues. However, if the VI has been solely created for the Mobile Working installation, then Totalmobile Technical Services should be involved to ensure no underlying VI bottlenecks would impact on the SQL server (such as SAN/host performance or NFS/iSCSI/FC connectivity).

You can also use shared MsSQL instances if preferred. However, you should determine the performance impact of installing Mobile Working for all applications before committing to this type of installation to ensure it is still acceptable. Generally, as these workloads are closely monitored due to the critical nature of other applications, there are no issues but it’s always prudent to minimise the risk of issues arising.

Existing shared Database server

If you are using a pre-existing MsSQL instance, consider the following:

Capacity: Shared servers must have adequate headroom for the installation. Since the existing server’s specifications will be unknown until the installation starts, you must be confident that the shared server meets the following performance goals:
- Memory: The SQL-Server cache hit ratio should be in the high 90s, ideally greater than 95%, indicating data is being cached properly and that IO is logical (from cache) rather than physical (from disk) in most cases. Physical IO will result in slower performance than Logical IO as physical disks cannot provide the same speeds as RAM.
- CPU: The server CPU queue length should ideally be less than or equal to the number of cores in the server for the majority of the time, indicating the server has no issues servicing incoming SQL requests and row-set responses. A low figure means the server is not waiting unnecessarily for threads to be serviced.
- Disk: The disk queue length should be less than or equal to the number of spindles for each disk partition/LUN, indicating that high IO, be it SQL related or swap file related, is not causing physical IO to be too adversely affected.
Partitions: Totalmobile recommends that where possible, separate partitions for Data, Logs, TempDB and Backups are used to separate the different types of disk access, for example high write log access with high read data access.
RAID: RAID10 is recommended where possible for data, logs & TempDB, with RAID1 as a minimum. RAID5 is adequate for backups only in most cases and should not be used for a database’s primary files (MDF and LDF) if faster alternatives are available.

Dedicated server

If you require or prefer a dedicated server, the following specifications would support up to approximately 1,000 users. These are for a guide only and should not be deemed as a system specification.

CPU: 6x Virtual CPUs (+2.0 GHz)
RAM: 16 GB
OS: Windows 2019/2022 Standard (Inc latest Windows Patches)
Disk Partitions:
- Windows Partition: 70GB (Raid 1 at underlying hardware level)
- TempDB: 20GB (RAID10 at underlying hardware level)
- Data (MDF Files): 500GB (RAID10 at underlying hardware level)
- Logs (LDF Files): 150GB (RAID10 at underlying hardware level)
- Backup (BAK files): 500GB (RAID5 at underlying hardware level)
- TempDB (System DBs): 70GB (RAID10 at underlying hardware level)
- Paging Disk: 1.5 times the size of Physical RAM (RAID5 at underlying hardware level)

The above specifications are a guide only. Totalmobile Technical Services should be involved in the system design to ensure that the system is specified to cope with the expected load.

Multiple MsSQL Named Instances

Totalmobile supports multiple SQL named instances, provided they are set up on specifically defined ports and not the default dynamic port allocation. Multiple instances can be set up on a single server to separate out Live, Test, and Development environments without the need for extra operating system deployments. This still offers a level of protection against an issue affecting a production system, making it a popular choice to maximise utilisation of modern day physical hardware and/or expensive SQL-Server CPU licenses.

MsSQL High Availability

As most environments are now installed as virtual machines, you can choose to protect the availability of machines at the Virtual Infrastructure layer (i.e. vMotion). However if this does not suffice and extra protection is needed, you can configure Mobile Working with an MsSQL Cluster or database mirroring. Using an MsSQL cluster is transparent to Totalmobile and provided it fails over correctly, will not cause any issues with Mobile Working in terms of database availability. However, the Reporting Services aspects will need some design work to ensure the services are resilient.

While MsSQL database mirroring does work, Mobile Working cannot provision for a connection string style change (i.e. Failover_Partner=Partner_B). In order to use database mirroring, you would either need to set up an Alias in DNS with a manual change for failover, or re-configure the connection strings manually.

Database Software Setup

Supported OS Versions:

Windows 2019 Standard Edition - Recommended
Windows 2022 Standard Editon

Supported MsSQL Versions:

MsSQL 2019 Standard or Enterprise – Recommended
MsSQL 2022 Standard or Enterprise
Microsoft SQL-Server Express Edition is specifically not supported

The customer is expected to install the server to a Windows OS level (Including patches and service packs) share and join the server to the LAN and Active Directory domain.

The customer is expected to install SQL-Server including any patches and service packs.

The System Default Locale must reflect the region the server is in The MsSQL instance must be configured with the “mixed mode” security model allowing Windows and SQLServer logons to be used for authentication, as well as default collation and sort order.

Microsoft SQL-Server Reporting Services will need to be installed onto the Database server; with no configuration applied (unless a pre-existing RS instance is being used). This will create a new Windows service and should be set up to run under a Windows user.

SQL Installed Features

The following features should be installed as part of the initial MsSQL install. If they are not installed as part of another instance, any requested features should be installed before the start of the Mobile Working install:

Database Engine Services
Reporting Services - Native (must be installed on the Database server unless an extra MsSQL license is provided)
Integration Services (not required but recommended)
Management Tools – Complete

SQL Server Transparent Database Encryption

TDE is a feature built into SQL Server Enterprise that allows for a database to be encrypted, when being written to the storage area of a machine, including database log and backup files. The process is controlled entirely within the SQL server instance and no changes are required on the client side. Mobile Working has been tested with TDE and fully supports the feature if required.

As the encryption is only applied at the time of writing the blocks, anything contained within memory is not encrypted, but it does allow you to protect the mdf/ldf/bak files.

Optimise for Ad-Hoc Workloads

Due to the dynamic nature of Mobile Working, many of the SQL queries that are executed are classed as ‘ad-hoc’ and therefore will appear in the SQL Query Plan Cache as such. In addition to this, and again due to the dynamic nature of Mobile Working, many of these plans will be registered with a single execution count. This takes up memory within the SQL server instance to manage and store; therefore Totalmobilerecommends setting up the server with Optimise for ad-hoc workloads. This ensures that single use execution plans are not stored in memory, freeing up resources for other purposes such as data caching.

This setting is recommended to be enabled on any dedicated Totalmobile database server, however it should be carefully considered for any shared system. While there are no apparent issues with enabling it, Totalmobile cannot guarantee how other databases and applications will react to the feature being enabled.

For more information on this please see: https://msdn.microsoft.com/en-us/library/cc645587.aspx