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.