Self-Hosted Database Server Recommendations

TempWorks Software supports Microsoft SQL Server database software. The following information is applicable regardless of the deployment scenario selected. 

Microsoft SQL Server

Microsoft SQL Server Standard Edition is the typical version of Microsoft SQL Server recommended for deployments. For deployments for more than 100 users SQL Server Enterprise edition is recommended. Notice that if transactional volume in the TempWorks Software deployment becomes very high, this could have a large effect on overall application performance. To minimize any adverse performance results, an in-depth testing cycle is suggested before the production go-live date and after any planned customizations.

Versions 

The TempWorks Software database can use Standard, Business Intelligence, and Enterprise versions of the Microsoft SQL Server product suite. Make sure that you have a good understanding of the performance benefits and implications associated with which version of Microsoft SQL Server is deployed. The version of Microsoft SQL Server deployed will affect how many computer processors and RAM are available to the Microsoft SQL Server. Before purchasing or installing TempWorks Software, make sure you understand the differences between- and benefits of each of the Microsoft SQL Server versions. To learn more, visit the following link: http://www.microsoft.com/en-us/sqlserver/editions.aspx .

Data Size Impact 

The data which is first migrated into the TempWorks Software database and subsequently entered into the system each day, month and year can have a significant effect on the overall TempWorks Software system performance. The Microsoft SQL Server housing the TempWorks Software data is very important to providing acceptable performance. In addition, as the total number of records (both per entity and over the whole system) is important in determining an overall database size. Users in the system, the information about those users, the data that each user will own, the business use cases that each user will run, and the frequency at which those use cases occur are equally all important. This is also why each TempWorks Software deployment truly is unique and it is difficult to do a side-by-side comparison of one TempWorks Software deployment to another, even though they may have generally the same number of users or data size. Carefully consider all of the business requirements and other unique deployment details which will shape the deployment.

Data Archiving 

When significant data growth is expected, data archiving should be considered to offload non-business critical data and to improve overall system performance.

General Guidelines to Optimize Performance on the Database Server

Hardware is one way to boost TempWorks Software application performance, but by itself may not elicit optimal performance if the following points are not considered. It is also important to notice that performance optimization is deployment specific. This means a specific method of optimization for one customer may have no change or even an adverse effect on another customer. That is why it is so important to deeply understand exactly how the customer will be using the system and what the proposed optimization is doing. 

Disk Configuration 

In addition to the TempWorks Software Implementation Guide, Microsoft preferred practices for disk configuration are available online. See the following Microsoft articles when you are considering Microsoft SQL Server configuration and settings: 

Memory 

Use as much dedicated memory (RAM) per Microsoft SQL Server instance as possible for the computer that is running Microsoft SQL Server and supporting the TempWorks database. The memory allocated to Microsoft SQL Server is very important, adjust as necessary. It is required practice to not run other applications on the Microsoft SQL Server computer which supports the TempWorks database. 

Virtualization

TempWorks provides technical support for virtualizing SQL Server under the following conditions:

  • Configurations that are validated through Microsoft's Server Virtualization Validation Program.

  • Configurations are pre-approved through TempWorks Consulting Services

  • For best performance the SQL Server VM should have direct access to disk arrays, not using Virtual Filesystems (VFS/VMFS/VirtualDisks)

  • TempWorks database files stored on Virtual Filesystems (VFS/VMFS/VirtualDisks) must be pre-approved by TempWorks Consulting Services.

For more information regarding virtualizing SQL Server visit these sites:

Small (up to 20 users) or Medium (less than 100 users) Deployment Scenario:

Server Specifications 

Processor

Small - 1  Cores
Medium - min 2 Cores (
more cores required depending on system load)

RAM

Small - min. 32 GB
Medium - min. 64 GB

Windows Server

Windows Server 2012 r2 or later

SQL Server Edition

SQL Server 2016 Standard or Enterprise 64-bit with latest CU

* TempWorks is compatable with SQL Server Standard and Enterprise editions. Larger installations will have performance benefits from Enterprise edition features. Please contact your TempWorks representative for more information.  SQL Server Editions Comparison 

Disk Configuration 

OS / System Paging file

Dedicated RAID 1, 5 or 10 - SATA, SAS, SSD, or better

SQL Data Files (.mdf)

Dedicated RAID 5 or 10 - SATA, SAS, SSD, or better
IOPS (Read) - Average: 800   Peak: 2000
IOPS (Write) - Average: 300   Peak: 1000
Thoughput (Read) - Average: 100 MB/s   Peak: 1000 MB/s
Thoughput (Write) - Average: 5 MB/s   Peak: 30 MB/s

SQL Log Files (.ldf)

Dedicated RAID 1 - min. 500 GB - SATA, SAS, SSD, or better
IOPS (Read) - Average: 200   Peak: 400
IOPS (Write) - Average: 500   Peak: 2500
Thoughput (Read) - Average: 10 MB/s   Peak: 80 MB/s
Thoughput (Write) - Average: 10 MB/s   Peak: 70 MB/s

SQL TempDB Data file (tempdev)

Dedicated RAID 5 or 10 - min. 250 GB - SATA, SAS, SSD, or better

S QL FileStream  (FileStream Overview)

Dedicated RAID 5 or shared with OS volume or SQL Data volume. 

* Enterprise Infinity store documents using SQL FileStream technology, separating the document volume from core data. Documents can take up to 80% of a TempWorks installation capacity. The SQL FileStream volume can be placed on cheaper, larger and slower storage volumes while keeping core data on fast high performant drives. Typical installations place this volume on SATA drives to reduce hardware costs.

Large Deployment Scenario over 100 users :

Server Specifications 

Processor

min. 4 Cores (more cores required depending on system load)

RAM

min. 128 GB 

Windows Server

Windows Server 2012 r2 or later

SQL Server Edition

SQL Server 2016 Enterprise 64-bit with latest CU

Disk Configuration 

OS / System Paging file

Dedicated RAID 1, 5 or 10 - SAS 10K, SSD, or better

SQL Data Files (.mdf)

Dedicated RAID 10 - SAS 15K, SSD, or better
IOPS (Read) - Average: 800   Peak: 4000
IOPS (Write) - Average: 300   Peak: 2000
Thoughput (Read) - Average: 100 MB/s   Peak: 1500 MB/s
Thoughput (Write) - Average: 10 MB/s   Peak: 50 MB/s

SQL Log Files (.ldf)

Dedicated RAID 1 - min. 500 GB - SAS 15K, SSD, or better
IOPS (Read) - Average: 200   Peak: 400
IOPS (Write) - Average: 1000   Peak: 2500
Thoughput (Read) - Average: 10 MB/s   Peak: 80 MB/s
Thoughput (Write) - Average: 10 MB/s   Peak: 70 MB/s

SQL TempDB Data file (tempdev)

Dedicated RAID 10 - min. 250 GB - SAS 10K, SSD (recommended), or better

SQL FileStream  (FileStream Overview)

Dedicated RAID 5 or shared with OS volume or SQL Data volume. 

* Enterprise Infinity store documents using SQL FileStream technology, separating the document volume from core data. Documents can take up to 80% of a TempWorks installation capacity. The SQL FileStream volume can be placed on cheaper, larger and slower storage volumes while keeping core data on fast high performant drives. Typical installations place this volume on SATA drives to reduce hardware costs.

Note: It is highly recommended that large deployment customers use a dedicated SQL Server Reporting Services server.

Reporting Services Server Specifications 

Processor

min. 1 Cores

RAM

min. 64 GB

Disk Configuration

Any redundant RAID configuration is acceptable

With any of the SQL Server deployment scenarios, it is recommended to start with the RAM mentioned above and expand as necessary. Additional processors may also be added as necessary. Proper system monitoring will determine when RAM and processors should be added to this server configuration. 

Large Deployment / High Availability - Example Diagram

Note: This diagram is not a true representation of the number of computers possibly required .

Related Articles

  • None