17.1.3- Transactions and Databases

Transactions and Databases

There are many criteria that go into choosing a database. For example:

• Capacity – can the database support the amount of information we want to store?
• Scalability – can it support the number of simultaneous users we're expecting?
• Cost – how much does the database system cost?
• Administration – how hard or easy is it to administer the system?
• Compatibility– does the database work with our other systems?
• Performance – how many queries can the database engine execute in a given time?

When it comes to working with transactions, one of the most important of these criteria is the database's compatibility. As we stated earlier, COM+ provides us with the ability to automatically undo changes made to a system when a transaction fails and is rolled back. When COM+ rolls back the failed transaction, any database servers that participated in that transaction are told to undo the changes that they made to their databases. Of course, in order for this to happen it's important that the database software is capable of reversing these changes for us.

It's not only important for the database software to be able to support rollback – it's also important for us (as developers) to know whether the database software is capable of supporting rollback. If we write an application whose transactions depend on database software that is not capable of rollback, then there's every chance that we will end up with errors in our data.

As we've already said, the idea of writing our own custom-built system for rolling back failed transactions is unfeasibly terrifying! So it's clear that we need to find a database that will do it for us.

In this section we will look at three different types of database software, reminding ourselves about them and exploring how they play in the COM+ and ASP world. We'll look at:

• Microsoft Access, or the Jet Database
• Microsoft SQL Server
• Microsoft Data Environment (MSDE)

It's worth noting at this stage that it's possible to use COM+ to manage transactional data stored in non-Microsoft databases, providing they are XA-compliant. In particular, COM+ is able to work with transaction state persisted in Oracle databases. However, we won't cover these other database engines any further in this book.

Each of these databases allows access to data that is stored in a relational data format. As we've seen, we can retrieve information from these databases using standard SQL commands, through various different interfaces. When working with ASP, the most prevalent data access method is ADO. There are many differences between these three databases when looking at the criteria we mentioned earlier. The critical criteria for us to examine is the compatibility of the database with COM+.

Microsoft Access

The Microsoft Access database uses the Microsoft Jet database engine (which is why you may also see it referred to as the Jet database). This database includes the Access front-end to a relational database as well as the database itself. The Jet database tables for a single database are stored in a single data file, usually with the extension .mdb. The general way of accessing this database is through an ODBC driver or OLE DB provider, via ADO.

There are a couple of notable advantages to using the Jet database for ASP. First, like MSDE, it is free from any license fee. Second, it comes with its own front-end for quickly creating and modifying databases.

However, the Jet engine is not able to support more than 5–10 simultaneous users; moreover, there are problems inherent in relying on a single file to store all database information. But as far as COM+ is concerned, the bigger problem with the Jet database is that it does not support a resource manager interface – so it is incapable of managing durable data during a transaction.

There is a way round this – but it's the difficult route again! If you're using an Access database to maintain transactional data then you will need to access it via the ODBC resource dispenser. A resource dispenser also manages state, and is independent of any resource manager, but it does not give you automatic support for database transactions and rollbacks.

This means that if your transaction were to not complete successfully, then any changes made to the database will not be automatically reversed. The database code in your application will need to detect that the transaction didn't complete, and will need to reverse the changes. In order to do this, the application must track the changes made to the database in the first place – and it's the developer's responsibility to write the code to achieve this.

Microsoft SQL Server

Microsoft SQL Server is a relational database engine that is available for the Windows NT platform. It is a true database server system – as opposed to the file-based system used by the Jet database. This gives it vastly increased power, scalability, and robustness advantages over Jet. The two most common versions of SQL Server are SQL Server 6.5 and SQL Server 7.0.

When working with COM+, the biggest advantage of using SQL Server is that it supports access to its data via a resource manager. The resource manager will support the maintenance of durable data – which means that any changes made to the database system during a transaction will be automatically reversed if the transaction does not complete successfully. In other words, when a transaction fails, COM+ sends a message to SQL Server via the resource manager, telling it to undo the changes made during the course of the transaction.

This means that the application doesn't have to track database changes – COM+ and the resource manager will do it all for us.

Microsoft Data Engine

Microsoft released the Microsoft Data Engine (MSDE) early in 1999. This new data engine is designed to alleviate some of the problems that the Jet database was running up against when dealing with larger and more active web sites.

Previously, users of Microsoft data engines had a stark choice when scaling up from the Jet engine – the most obvious choice was SQL Server. This conversion path was relatively straightforward, but not 100% foolproof. So MSDE was intended to provide a kind of halfway house – based on SQL Server technology, but with the option of using Access as a 'front-end' user interface for easy management of the data bases involved.

Essentially, MSDE is SQL Server 7.0 without the scalability and management components. However, the database structures are identical, and both MSDE and SQL Server are true database systems. This helps to alleviate the problems of the file-base Jet system. Another advantage of MSDE is that it provides full SQL Server compatibility free of charge to developers using the Microsoft Visual Studio programs, as well as the ability to distribute applications developed using MSDE without any license fees.

From the point of view of COM+, MSDE is able to use the same resource manager that is employed when working with SQL Server – which means that MSDE gives us all the transaction rollback capabilities of SQL Server.