Transactional (OLTP) ---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Online transaction processing (OLTP) systems, often simply referred to as transactional systems, are designed to process small, quick, interactive workloads for which users expect a fast response time. For such workloads, it is generally expected that atomicity, consistency, isolation, and durability (ACID) properties will be maintained and response time will be less than one second. These are often business-critical workloads that require high levels of performance, security, availability, and, in case of a disaster, near-zero data loss. Often these systems are at the very core of business operations, and any downtime, data loss, or security breach can impact business operations and result in unpleasant publicity. The demanding requirements for these business-critical systems drive system design and development, and the tasks become more challenging with large volumes of data, users, and transactions rates. For such systems, it is expected that stress testing or benchmarking will be done to ensure that the system can scale to the desired projected volumes.

To achieve these demanding goals and still operate within constrained budgets drives designers to look for creative solutions, which are adopted based on the project needs. Such solutions include scaling-out the transactional workload, assigning servers for read-only workloads, dropping ACID requirements for some workloads (for example, shopping cart data), asynchronous processing of some work, and relaxing disaster recovery requirements for some data domains.

Best Practices

The following resources provide some best practices and some pitfalls to avoid when using transactional systems. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)

Case Studies and References

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

  • Fully understand user requirements and use scenarios, including the following:

    • The number of concurrent users

    • Transactions

    • Performance and scalability

    • High availability/disaster recovery (HA/DR) requirements, including windows for completing maintenance tasks

    Also review the Technical Reference Guide for Operational (OLTP) systems, because some customers use the terms "transactional” and “operational” interchangeably. Note that the Considerations and Best Practices sections in the guide for operational systems also apply to transactional systems.

  • Understand the HA/DR requirements for the system. For tier-one applications, there can be very stringent data availability requirements, such as no data loss, necessitating design considerations dictated as much by HA/DR as by the performance requirements. Refer to the Technical Reference Guides on HA/DR for additional information.

  • Determine whether the transactional system will run concurrent with reporting. Some degree of concurrent reporting is common because reporting replicas might have an unacceptable latency for some reports. Consider using Read Committed Snapshot Isolation (RCSI), but note that using RSCI does bring associated trade-offs. RSCI might help with the potential blocking issues, but it will not help with the resources required for the combined transactional processing and reporting. It might be necessary to offload the reporting workload to another server if lack of resources becomes an issue. Some approaches for creating a second copy for reporting include:

    • Database mirroring

    • Log shipping

    • SAN-based replication

    • Transactional or peer-to-peer replication

  • Choosing between scale-up and scale-out is a critical architectural decision. Understand whether the customer intends to interoperate with, or migrate to, cloud-based systems. Refer to the scale-up and scale-out Technical Reference Guides for additional information.

  • Determine how the current deployment compares with previously deployed versions. Understand hardware requirements and operational considerations. If using an independent software vendor (ISV)-provided system, it is more likely that other installations have used the application before, and based on those experiences, capacity planning information can be more realistic and dependable.

  • Blocking and deadlocks can occur with increasing volumes of concurrent transactions and on the data access pattern. As discussed above, RSCI can help with blocking issues. Generally, reducing large numbers of deadlocks requires application code access path patterns.


Following are the full URLs for the hyperlinked text.

1 Top SQL Server 2005 Performance Issues for OLTP Applications

2 Scripts and Tools for Performance Tuning and Troubleshooting SQL Server 2005

3 Diagnosing Transaction Log Performance Issues and Limits of the Log Manager

4 Resolving DTC Related Waits and Tuning Scalability of DTC

5 Initializing a Transactional Replication Subscriber from an Array-Based Snapshot

6 Analyzing Deadlocks with SQL Server Profiler

7 Deadlock Troubleshooting, Part 1

8 bwin: Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabytes

9 ServiceU Corporation: Online Event Software Gets Disaster Recovery with SQL Server 2005 Database Mirroring

10 The Progressive Group: Progressive Prepares for Future Growth, Gains Agility with SQL Server 2005