SQL Server Middleware

In simple terms, SQL Server middleware—such as OLE DB or .NET providers and ODBC and Java Database Connectivity (JDBC) database access drivers—provides a connection between an application and a database.The SQL Server middleware processes application requests and presents them to a back-end database in a language, structure, or syntax that conforms to the requirements of the database server, then returns the data to the application. Although SQL Server middleware is built into many products, if you need to support legacy and heterogeneous systems or increase performance and transaction processing speeds, investigating ISV middleware can be worth your effort. Middleware lets developers who work in an environment with heterogeneous and legacy systems focus on developing efficient applications with tools that use relational databases, without having to learn how to access data that's stored on heterogeneous systems. SQL Server middleware also lets you use a common programming model for different heterogeneous databases so you don't have to use a different access method for each target database.

The Basics of Choosing a Middleware Product

When evaluating SQL Server middleware, you'll want to be sure that products you're interested in support your network's transmission protocol, OSs, hardware platforms, and all your applications and databases. These products should be easy to deploy to your users, have built-in support for network installation and configuration, and provide desktop management and configuration functions.

After you determine that a product is compatible with your infrastructure, performance is the next concern. Generally, performance results from an application's design, the network infrastructure it runs on, the hardware on which the data resides, and whether or not the product supports any type of load balancing out of the box. Many middleware vendors offer flexible licensing options; check out the vendor's licensing strategy to be sure you'll pay for only the capabilities your organization is using. Let's delve more deeply into SQL Server middleware performance, usability, and security.

Performance

To judge a middleware driver's performance, look for whether it supports stored procedures, snapshot transaction, connection pooling, and load balancing. Stored procedures,or parameterized queries, are stored and run within the database engine, and in most cases the database server saves an execution plan for a stored procedure. Because stored procedures can use the precompiled execution plan, you'll generally see improved performance. Stored procedures also enhance application security because you can add security attributes (e.g., permissions) to a stored procedure.

Snapshot transaction isolation is a new feature in SQL Server 2005 that lets you achieve greater levels of concurrent access to data by allowing a transaction to read a snapshot of the database as it existed at the start of the transaction or query. This feature also reduces the number of reader-writer blocking occurrences.

Pooling connections enhances application performance and scalability because it reduces the number of times a database server must open a new connection to service database connection requests. If middleware supports pooling, it maintains several open pools concurrently—reserving at least one pool for each type of configuration— letting multiple requests share the same pool.

Additional features to look for in a middleware ISV's product are out-of-the-box load balancing and a performance-tuning tool. For example, some of DataDirect Technologies' middleware products include the company's Performance Tuning Wizard. Such a tool is also a good indication that the product you're considering is a mature one.

Another performance factor is protocol support. If middleware supports the native communications protocol that a database server understands (for example, SQL Server uses the Tabular Data Stream—TDS— protocol), you won't need to install additional network or data-access libraries on the client. This can make communication between the client and the database more efficient and simplify deployment considerations.

Enterprise-Oriented Features

If you're working with heterogeneous systems, look at middleware products that support linked server technology. Linked server functionality is useful when your applications require an immediate interactive connection between SQL Server 2005 and other database platforms.

The Microsoft Distributed Transaction Coordinator (DTC) provides an object-oriented application programming interface that facilitates transaction processing between distributed Windows 2000 Server Service Pack 1 (SP1) and later platforms. It also lets you configure security administration settings to reduce security risks inherent in distributed network environments.

If you're using SQL Server 2005, make sure that any ISV middleware you're interested in supports all the new SQL Server 2005 features, such as support for the recent T-SQL enhancements, the new XML and varbinary(max) data types, and the new SQL Server 2005 schema. You'll also want to make sure products you are considering support Unicode if you expect both multilingual support and that your applications create character-based data that can be transferred and used between different OSs.

Security

If your Windows environment depends on Active Directory (AD) to provide your users secure access to the applications that run on their desktop, make certain that any middleware product you choose supports integration with AD. AD provides a more

secure environment than SQL Server authentication, which uses SQL Server logins and passwords to authenticate users. Windows authentication, supported on Windows Server 2003 Service Pack 1 (SP1) and later, uses Kerberos 5 and NT LAN Manager (NTLM) authentication to verify a user or host identity. If the database server runs on Windows 2000 Server or Windows 2003, it will use Kerberos 5 authentication if AD is running on the server and the client supports Kerberos 5. In all other cases, the database server will use NTLM authentication to validate a network username.

Know Your Needs and Wants

Typically, most organizations purchase ISV SQL Server middleware to gain capabilities, such as heterogeneous database access, that aren't offered by the database server's native middleware. During your middleware-purchase investigation process, examine the vendor documentation and support policy and speak with a few of the vendor's customers, if possible, about their experience with the product. Look for a product that supports your needs, provides good overall performance, is easy to deploy and maintain, and integrates directly into your standard security scheme.The table below lists middleware vendors that sell OLE DB or .NET providers and ODBC and JDBC data-access drivers for SQL Server. You'll notice variances in pricing and supported SQL Server functions between these products; let your decision be guided by the applications you're running and the OSs and platforms on which your back-end databases reside.

See Associated Buyer's Guide