Implementing SQL Server in an OLTP Environment: Introduction

Saturday Jul 22nd 2000 by Eric Charran



Business application development exists as an increasingly complex process. Information technology managers and developers working for an organization that necessitates a business application are required to evaluate and implement appropriate application modeling strategies, software, database technologies and solution models. It is often conceivable that during the application development process, technologies, methodologies and development methods can drastically change, altered by a myriad of changes in each of these areas. The goal of the organization is to develop an application to support current business processes while simultaneously adopting technologies and methodologies that will remain forward compatible with new developments in the information technology field.

Typically, one of the first applications an organization will develop is an application to facilitate the transactional nature of its business. A transactional application will often serve as a platform to accept business information from customers or clients, process this information and deliver a predefined product or service. An Online Transaction Processing (OLTP) application environment will accurately facilitate the development and operation of such an application.

OLTP Environment Characteristics

The OLTP environment consists of several layers. The bottom-most layer in an OLTP environment consists of the data layer. The data layer or data tier is composed of all data required by the organization's business needs to complete business transactions.

The data tier can consist of legacy systems comprised of legacy data housed on mainframes, relational database data housed in SQL Server or Oracle, non-relational data such as Exchange Server data as well as other data, such as data supplied by accounting system software and other applications.

The business logic tier consists of elements that employ business rules and logic to the data tier. The business logic tier maps business and organization rules and procedures to the transactional nature of the application. The business logic tier also is comprised of technologies that facilitate the transactional nature of the organizations business, such as Exchange Server, Internet Information Server and other application services.

The presentation tier consists of the "thin" client portion of the transactional application. The presentation and business object tier, formerly deployed at the client together, then separated, now exists as a "window" to the actual business and data tiers. The presentation layer provides the client with a formatted view of the actual functionality of the business and data layers. The client, existing only as a mechanism to facilitate interaction of the user with the application is typically a small or "thin" deployment that can be implemented across a wide variety of platforms, including the Internet. Technologies such as Active Server Pages (ASP), VBScript, JavaScript and XML are designed to allow for the implementation of a thin client with rich functionality.


courtesy Microsoft Windows DNA

The Microsoft Windows DNA Architecture

Illustrating the tiered solution model

SQL Server 7.0 and the Data Tier

SQL Server 7.0 is an integral part of the data tier. It is designed to work effectively in a number of application implementation strategies and solutions. By nature, SQL Server exists as a multitier client/server database system. In past implementations of OLTP models, SQL Server would participate in a two-tier client/server environment. The client machine would run an exe-based thick client application that would connect to the SQL Server and conduct business transactions. The thick client application housed the business logic and the code to display output to the user.

In a multitier client/server environment, SQL Server 7.0 assumes a more active role in the OLTP process. By using several new features and technologies, SQL Server 7.0 has the ability to assist in the deployment of a distributed OLTP application and a thin client. In a multitier environment, the business logic is located on a separate server. The thin client is responsible for housing the code to display output to the user and accept input.

Through the use of SQL Server features such as application roles, Windows NT authentication as well as database roles and permissions, SQL Server assists in the deployment of a thin client application across a multitude of platforms, including distributed Internet applications.

Middle tier or business logic tools such as Internet Information Server 4.0/5.0 (IIS) and Microsoft Transaction Server (MTS) fully integrate with SQL Server 7.0. This integration also assists in the creation of a thin client. By keeping the client as thin as possible, developers can adopt OLTP needs to new and emerging technologies. For example, developers for an organization can migrate a client application that communicates with MTS from a Visual Basic rich client to a Visual Studio thin client interface using web forms that communicates with MTS and SQL Server via IIS in an Internet or Intranet distributed environment.

Determining the OLTP Roadmap

Determining the implementation of the multitier model involves the recognition and evaluation of several factors. Primarily, organization business management, in conjunction with the information technology department, must develop a vision and scope for the application. Once the technical evaluation of OLTP multitier implementation begins, the scope of how the OLTP application will satisfy business needs should already be complete, supplemented with appropriate documentation.

In addition, business rule discovery and documentation should also be completed and well understood by all parties involved in the development of the application. Once the business rules have been finalized, OLTP strategy evaluations can begin.

    • Determine the Presentation Layer
      • The affirmation of the client type will serve as the foundation for the remainder of the OLTP model planning process. A thin client is preferable to a thick client because of ease of design, distribution and management.
      • Determining the client type involves selecting the platform on which the client will be built. Thin clients can be constructed using Active Server Pages in conjunction with VBScript or Java and distributed in an Internet and Intranet environment. Clients can also be Visual Basic executables, Visual Studio clients as well as comprised of other technologies such as Cold Fusion.
      • The type of thin client is usually a function of the organizations developers core skill set. An organizations management may decide to outsource the client creation process in favor of desired functionality (i.e., an Internet distributed application as opposed to a Visual Basic executable).
    • Determine the Middle Tier
      • The technologies involved in the middle tier are partially determined by the type of client that the organization plans to deploy. The complexity of the business rules and logic in conjunction with other factors also contribute to the evaluation and approval of middle tier technologies. Factors such as user community size, network traffic as well as client management and administration requirements all assist in determining which technologies will be deployed as part of the middle tier.
        • Technologies
          • Microsoft Transaction Server
            • Client Load balancing including distribution and management of multiple client transactions
            • Management of Business Objects and Rules


            • Direct communication with the SQL Server 7.0 data tier
          • Microsoft Internet Information Server
            • Web Application Services including secured authentication of clients (users)
            • Hosting of dynamic web content including Active Server applications and other Internet distributed applications
            • Support of transactional web applications
    • Determine the Data tier
      • The data platform chosen by the organization will be a function of the businesses existing database technologies, the capability of the database to integrate and merge heterogeneous data from various sources as well as the personnel to assist in development and maintenance of the RDBMS.
      • SQL Server 7.0s features lower total cost of ownership, the time required for traditional DBA activities as well as supplement the consolidation and presentation of data from relational and non-relational sources. Using features like Data Transformation Services (DTS), OLE DB, as well as its enhanced and redesigned transactional management system, SQL Server 7.0 is often the optimal choice for an organizations OLTP needs.
        • SQL Server Technologies
          • Data Transformation Services (DTS) for the ETL of relational and non-relational data.
          • Reduction of administrative tasks through increased dynamic resource allocation and self-management and enhanced transaction management.
          • Support for a wide variety of APIs including ADO, OLE DB, DAO, ESQL, T-SQL and XML.
          • Ease of integration with the Microsoft Back Office family and Windows NT authentication

Design the Data Model

High volumes of users or clients requiring concurrent connectivity characterize the Online Transaction Processing environment. Each client can conceivably initiate several data manipulation (DML) statements that SQL Server accepts, logs, processes and executes. As a result, a predefined data model must exist prior to physical model creation. This model, crafted by database developers and administrators should directly support the multitude of small insert, delete and update statements indigenous to a typical OLTP application. Upon designing the logical data model, designers must account for user concurrency, atomicity, speed of information retrieval and the speed with which records can be updated or written.

Characteristics of an OLTP data model include a high degree of normalization. The normalization of data into relational tables optimizes the speed of transactions performed by the OLTP application.

    • Normalization
      • Normalization of tables optimizes the efficiency of the transactions occurring in an OLTP environment. By using formal methods to separate data into multiple related tables, client activities in the database during production can be accommodated more efficiently.
      • An advanced degree of normalization will be conducive to the multitude of DML statements and reads in a transactional environment. This improvement in performance will increase transactional consistency and efficiency.
      • As the degree of normalization increases, the complexity of the data model invariably will increase. The amount of relationships and constraints which must be applied to the model to maintain referential integrity can become equally intensive as well as difficult to administer. Additionally, many complex joins between tables will begin to hinder performance. The determination of the degree of normalization should be comprised of a balance of normalization form methodology and performance considerations.
    • Data Integrity
      • OLTP database design should attempt to closely follow the rules and forms surrounding data integrity. When designing the OLTP model, Entity Integrity, Domain Integrity and Referential Integrity should be employed. Through the use of check constraints, primary keys, column default values and relationship constraints, the model will actively preserve data integrity.
      • Because the data model maintains business rules about the integrity of the data, logic involving appropriate values for columns, relationships and data validation is no longer integrated into the client. This approach allows for the slimming of the client into a purely presentational device.

courtesy Microsoft SQL Server Books Online

Referential Integrity example


      • Procedural integrity also allows for a thin client by assisting in maintaining data integrity through the use of stored procedures, triggers and check constraints. Using these SQL Server programmatic methods to ensure business rules on the data model will allow for the client to exclude the programming required to handle these procedures.
      • SQL Server default and rule objects are not ANSI standard and should be considered as a mechanism for backward compatibility with SQL Server 6.5 only. Column defaults and check constrains should be used instead of these legacy database objects.
    • OLTP Reporting
      • As an organization collects data, an inherent organizational need to analyze the transactional data and supply business users and analysts with this data will arise. However, because of the characteristics of an OLTP environment (multiple users concurrently inserting, modifying and deleting records), reporting becomes a resource intensive process.
      • Reporting in an OLTP environment will add additional traffic to the data model. In addition to the typical DML activities, the database now has to support the reading and selection of data for read purposes. Because of the degree of normalization present in many OLTP models, the multiple joins and tables hinder read performance and increase the resources required for report generation. Simultaneously, the read operations required by reports are obtaining locks on records, utilizing indexes and performing joins to gather required information. These operations will affect the speed and efficiency of transactional operations
      • Online Analytical Processing (OLAP) reporting in the context of a data warehouse will effectively alleviate the strain placed on an OLTP schema by reporting requirements. OLAP or data warehouse solutions will also increase read efficiency for information consumers by gathering and transforming OLTP data into a star schema. The star schema is optimized for read efficiency and can be populated nightly.
    • Entity Design
      • The creation and design of tables (entities) and their fields (attributes) should be a process driven by meetings with the business user. The business support for the application will be vital to the correct design of tables and relationships in the OLTP data model. At each stage of logical modeling, the business user should be given the opportunity to verify and correct the relationships and table structures presented by the database developers.
      • Designing entities in SQL Server requires a database developer to have a full understanding of data types and the ramifications of their use. Specific data types can be used in a variety of situations. However, not all data types fit a design situation as readily as others.
        • Data Type Specifics
          • VARCHAR vs CHAR
            • A varchar is a variable length data type that holds character data. It differs from a char because a char value is a fixed length data type. In other words, a varchar (30) holds up to 30 characters. However, if a 10 character value is stored in this field, the space used in SQL Server is only that of 10 characters. A char(30) containing 10 characters requires SQL Server storage space of 30 characters.
            • Varchar should be used when the length of character data in a field is expected to vary. A char data type should be used when consistently expecting data to be the same length.
            • Nvarchar and Nchar data types support Unicode data (international character data) and require twice the space of their Non-Unicode counterparts. These data types should only be used when anticipating Unicode data.
          • INT vs DECIMAL (NUMERIC)
            • The int data type only accepts whole number numeric data. The decimal data type accepts numeric data with a definable scale and precision. The numeric data type is a synonym for the decimal data type and will not be supported in future releases of SQL Server.
            • Use the int data type where appropriate. Storage space requirements are smaller than that of a decimal. The int data type accepts values ranging from -2,147,483,648 to 2,147,483,647. However, the int data type would be inappropriate to use when attempting to house data such as a phone number (a phone number of 214-748-3650 or 2147483650 would never be accepted into an integer field because it exceeds the value that the int data type can hold. Use a char data type for phone number fields. In this manner, developers and coders can search for area codes and other patterns in the phone number field).
      • Other entity design issues include several considerations that database developers should take into account when designing the logical and physical mode. For example, field names should be logically descriptive and related to their business function and significance.
      • Field names should not include any characters except alphanumeric (preferably alpha) characters. Spaces, control characters, and other symbols all contribute to allowances that developers and coders must make to accommodate outlandish field names.


      • Additionally, field names should be concise and not overly verbose or lengthy. This alleviates the strain placed on developers and coders who must utilize these field names in code.

Design Stored Procedures

The role of stored procedures in a business application is invaluable to the entire solution process. Any business transaction application should make use of stored procedures to execute read and DML statements from SQL Server. Even if the middle business logic tier contains the majority of the business logic, the business layer should use stored procedures to communicate directly with SQL Server. Stored procedures offer a palpable performance advantage even if the procedures are supplying read only data to an application through a business logic tier.

Stored procedures are precompiled in SQL Servers procedure cache. When an application or business tier server product (such as MTS) submits data to SQL Server for processing, they typical steps involved in ad hoc query processing need not occur. Steps such as syntax check, object verification and query plan inception, evaluation and verification need not occur. Because the stored procedure has been created and compiled, these aforementioned steps have already been accomplished and stored in SQL Server procedure cache. The execution plan is read from cache and then executed.

courtesy Microsoft SQL Server Books Online

Reuse of a precompiled

Stored Procedure with

multiple input parameters

and connections


    • Communicating with Stored Procedures
      • Data to the Client
        • Applications utilizing Active X Data Objects (ADO) or OLE DB will interact with stored procedures output on a recordset level. Any read data required by an application for presentation to the user should be retrieved through a stored procedure. The application using ADO or OLE DB will interpret the results of the select statement in a stored procedure as a recordset. Developers can then use the recordset to populate forms, reports or other client interfaces.
      • Data from the Client
        • Applications can directly make reference to a stored procedure, independent of a business logic tier. Using ADO, an application can collect data from the user interface (ASP forms, VB forms), call the stored procedure and pass form data to a stored procedure as an ADO parameter. The SQL Server stored procedure accepts the input parameters and executes the SQL task.
        • Business logic tier products such as MTS facilitate the creation of logical objects which the client application references. When the client makes reference to a desired transactional function (i.e., Add an new customer), the client application calls an MTS object that then initiates the communication with SQL Server on the clients behalf. The MTS object calls the stored procedure that then executes on the SQL Server. Return values from procedure execution can be passed directly to the client.
    • Stored Procedure Design
      • Proper stored procedure programming is similar to the concepts of programming T-SQL batches or T-SQL scripts. The contents of a stored procedure consist of any T-SQL that can be written using a batch or script. All SQL programming techniques should be implemented, including maintaining ANSI compatibility.
      • Stored procedures cannot contain many Data Definition Language (DDL) statements. Views, defaults, new stored procedures or triggers are among some of the objects that stored procedures cannot create when they are called. However, other database objects can be created, such as tables or temporary tables. Stored procedures can also reference other stored procedures.
      • Error handling should be an integral component of a stored procedure. Using error handling techniques such as the RAISERROR statement, T-SQL programmers can included custom defined error messages that can be returned to and interpreted by the client. The client can then present the user with the exact SQL Server error message or further customize it based on programming in the client presentation layer. Integration of custom error messages with the sysmessages table using the sp_addmessage system stored procedure allows for ease of message management and consistency.
      • Input parameters in a stored procedure should be set to a null value. Using this method, the stored procedure allows for a condition where the client application does not pass all expected input parameters. The stored procedure can still execute by evaluating the other parameters.
      • Naming conventions for stored procedures should follow a logical and consistent formula. Similar to a table object naming convention, stored procedure names should not contain control characters, spaces or symbols. Stored procedure names should also avoid utilizing the sp_ prefix as this prefix is used by SQL Server system stored procedures. A more appropriate methodology might be to name a procedure P_ + object name.
    • Transactional Consistency
      • Stored procedures should encompass transactional consistency. In other words, if a business transaction, initiated by the client, passed via a business tier server object (MTS) to SQL Server, requires several updates, deletes and inserts to multiple tables to complete, all of the required SQL DML statements should execute. Otherwise, if a catastrophic event occurs, all SQL statements defining the transaction should be rolled back.
      • Using this method, transactions requiring multiple DML statements will complete in their entirety, or not at all. This will increase data integrity in situations where a multipart transaction begins, completes half of the required SQL DML statements and is interrupted (due to power outages or catastrophic hardware or software failure). When SQL Server is recovered or the server reboots and the SQL Server service restarts, the transaction log is scanned for uncommitted transactions. If the transaction is not committed by the stored procedure, all of the SQL DML initiated in the transaction will be rolled back. The transaction must then be executed again and complete in its entirety.

Optimize Application Design

The design and functionality of the application code is traditionally dependent upon the application developer. However, the role of the SQL Server developer/administrator should also include communication of key concepts to ensure the optimization and functionality of the application. During the entire application development and testing process, specific goals for application design and interface with SQL Server should attempted.

  • Eliminate Unnecessary Network Traffic
    • Through the use of stored procedures, network traffic is inherently reduced. By eliminating the number of times that an application or business object communicates with SQL Server in one transaction or a series of transactions, application performance will be noticeably improved.
    • Stored procedures that contain multiple statements send messages back to the calling application by default. As a result, each time that a successful SQL Statement is executed by a stored procedure, SQL Server must communicate with the application to inform it of the SQL statement success. In cases where the application does not need to evaluate the success of each statement, using the SET NOCOUNT option to on in the declaration section of a stored procedure will disable this feature.
  • Use Small Result Sets
    • In applications where the retrieval of data to the client requires searches through large result sets, it is important for the developer to understand the ramifications of returning large recordsets from SQL Server. An application that requires large result sets from SQL Server will inherently limit user and presentation layer scalability. In addition, remote use of the application will be severely limited. Server I/O and resources as well as network traffic will also increase because of the amount of data being transmitted by SQL Server to the client. Developers should enable the presentation layer to prompt users for sufficient information to decrease the size of results set transmitted back to the client.
  • Set Query Timeouts and Cancellation Methods
    • Setting a timeout on long running queries will also increase the level of efficiency for the larger user community. Long running queries or application requests from clients can degrade performance of a SQL Server by utilizing resources and memory. Identifying long running queries (using SQL Server Profiler) and allowing the client API to cancel long running queries, server performance consistency is maintained.
    • Allowing the client to utilize a cancellation method for long running queries also allows for server performance consistency. However, the cancellation of a query in the middle of a business transaction does not release table locks. In addition, if a transaction is cancelled, appropriate procedures should be in place to rollback the initial portions of the transaction already committed.
  • Avoid Cursors
    • Appropriate parallels can be established between a client side ADO recordset and a SQL Server cursor. Both objects involve row level processing. SQL Server cursors pose performance related issues when employed by a distributed application. If row level processing is required, it should occur on the client. SQL Server tasks should be limited to set-oriented processing and return of data. Because SQL Server maintains row level locks on a cursor data, concurrency as well as application performance will suffer. SQL Server cursors should always be avoided, especially when a transaction requires a set update, delete or insert of data. By executing any of these statements using a cursor, each statement is executed on a row level basis, thereby increasing locks obtained, network traffic and client connection time.
  • Avoid Deadlocking and Blocking
    • Whenever a client application must execute series database operations that are inherently complex in nature, it is important to evaluate the length of a SQL Server transaction that attempts to encompass these operations. SQL Server transactions defined within a stored procedure should exist as logical groups of operations. Long running transactions that obtain locks on database objects increase the chance that blocking will occur. One instance of the client application that is executing a long running transaction can have locks on objects that another instance of the client requires. The second instance (depending on the operation) can be blocked.
    • Smaller more efficient transactions will decrease the occurrence of server blocking and deadlocking as well as network traffic. Application designers should enable the client to terminate a desired
  • Strive for a Stateless Application
    • Application efficiency can also be determined by its degree of state. An applications degree of state can be determined by the frequency and length of connections to the data tier. Applications that maintain state maintain a constant connection to the data tier. Similarly, these applications also decrease efficiency by increasing network traffic, locks on database objects and server overhead. Conversely, an application with a low degree of state connects to the database only to initiate stored procedures and receive data from them. After each database operation, a stateless application will disconnect from the data tier and only reconnect when necessary.
    • When application solution strategy employs a middle tier such as MTS, the middle tier handles all connectivity to the database. It is important to ensure that the middle tier strives for statelessness as well
  • Use Application Roles
    • SQL Server 7.0 application roles allow for permissions to be defined based on the accessing application and not the user. Typically, in a Windows NT environment, an application that directly contacts the data tier through a middle tier such as IIS authenticates users based on their NT credentials. The users NT credentials are examined when they authenticate with IIS and again when they authenticate with SQL Server (If the application and IIS are designed not to use anonymous access). SQL Server, through the use of application roles will examine the users NT credentials to determine if the user can access the SQL Server. The application calls the sp_setapprole system stored procedure to activate the SQL Server application role. The users NT permissions based on their NT credentials are rescinded and the database permissions assigned to the application role are activated.
    • Application roles directly control the users actions in a database while the application role is activated. However, in an environment that employs a different middle tier that controls load balancing and database connectivity such as MTS, application roles may not be necessary. MTS controls all database connectivity and the application makes calls to MTS objects (i.e., COM, COM+ or DCOM). These MTS objects then obtain state in conjunction with the database. MTS also uses roles to determine which NT users can call certain MTS objects. As a result, application roles are unnecessary because the middle tier controls all actions in the data tier.