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: https://www.microsoft.com/en-us/sql-server/sql-server-2022-comparison

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: 

• Using Files and Filegroups - http://msdn.microsoft.com/en-us/library/ms179316(v=sql.105).aspx  
  This article discusses best practices for data and log files among other relevant topics. The main point to take from this article is that the Microsoft SQL Server Data Files should not be on the same physical disk set as the Microsoft SQL Server Log Files. This article is also available in Microsoft SQL Server 2008 Books Online.

• SQL Server Technical Articles: Physical Database Storage Design - http://technet.microsoft.com/en-us/library/cc966414.aspx
  
  

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:

• Microsoft Support KB
• VMware SQL Server Solutions page

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

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