Database Server

This server role can be run in either a virtual or physical environment (TotalMobile supports either). If the customer already has SQL workloads on their 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 TotalMobile installation then TotalMobile Technical Services should be involved to ensure no underlying VI bottlenecks would impact on the SQL server [i.e. SAN/host performance, NFS/iSCSI/FC connectivity].

Shared MsSQL instances can also be used if preferable, however the performance impact of installing TotalMobile should be properly determined before committing to this type of installation to gauge the impact on performance for all applications, 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.

In this topic:

Existing shared Database server

If a pre-existing MsSQL instance is being used, the following should be considered:

  • Capacity: Shared servers must have adequate headroom for our application. Since the existing server’s specifications will be unknown until the project kicks off, the customer 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, [e.g. +95%] indicating data is being cached properly & 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 can’t 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: It’s recommended that where possible separate partitions for Data, Logs, TempDB and Backups are used to separate the different types of disk access, e.g. 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 a dedicated server is required, or preferred, then 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 (Raid 10 at underlying hardware level)

    • Data (MDF Files): 500GB (Raid 10 at underlying hardware level)

    • Logs (LDF Files): 150GB (Raid 10 at underlying hardware level)

    • Backup (BAK files): 500GB (Raid 5 at underlying hardware level)

    • TempDB (System DBs): 70GB (Raid 10 at underlying hardware level)

    • Paging Disk: 1.5 times the size of Physical RAM (Raid 5 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 fully supports multiple SQL named instances, provided they are setup on specifically defined ports and not the default dynamic port allocation. Multiple instances can be setup on a single server to separate out live/test/dev environments without the need for extra Operating System [OS] deployments, yet offer a level of protection against an issue affecting a production system so this is 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, the majority of customers 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, TotalMobile can be configured with an MsSQL Cluster or Database Mirroring. Using an MsSQL cluster is transparent to TotalMobile and provided it is fails over correctly, will not cause any issues with TotalMobile in terms of database availability. However, the Reporting Services aspects will need some thought as to how they are designed to ensure the services here are resilient.

As for MsSQL Database Mirroring, although it does work, TotalMobile cannot provision for a connection string style change [i.e. Failover_Partner=Partner_B]. In order to use database mirroring, either an Alias would need to be setup in DNS [With a manual change when failover was needed], or else a manual re-configuration of the connection strings would be needed.

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. Should they not be installed as part of another instance, any requested features should be installed before the start of the TotalMobile 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 & backup files. The process is controlled entirely within the SQL server instance and no changes are required on the client side. TotalMobile has been tested with TDE and fully supports the feature if needing to be implemented.

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 TotalMobile, many of the SQL queries that are executed are classed as ‘Ad-Hoc’ & therefore will appear in the SQL Query Plan Cache as such. In addition to this, and again due to the dynamic nature of TotalMobile, many of these plans will be registered with a single execution count. This takes up memory within the SQL server instance to manage & store, therefore it is recommended to setup 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. Whilst there are no apparent issues with enabling it, but TotalMobile cannot guarantee how other databases & applications will react to the feature being enabled.

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