Figure 1. Updating subscribers.
Microsoft SQL Server, which up to release 6.5 was available only on Windows NT, runs comfortably on a powerful notebook computer. For many mobile database application users, however, Windows NT is considered overkill. As a result, many mobile database applications have been deployed on Sybase SQL Anywhere, Oracle Lite, CA-OpenIngres/Desktop, or other desktop DBMSs on Windows 95 and even Windows 3.11 platforms. By opening up SQL Server 7.0 to Windows 95 and 98, Microsoft is making a visible entry into the mobile database market.
I described mobile databases in a miniseries in this column from September to November 1997. Last month, Robin Schumacher described the latest beta of Microsoft SQL Server 7.0 in detail in his article, "Sphinx Awakens." In this monthıs column I bring these topics together by focusing on Microsoft SQL Server 7.0 as a mobile database.
SQL Server 7.0 for Windows 95 workstations is a full-featured RDBMS that is appropriate for mobile database applications on desktop workstations and mobile platforms. The product is based on the same code as SQL Server 7.0 for Windows NT and therefore has the same features, except for three limitations. On Windows 95, it does not support SMP or asynchronous I/O, and its security features are not integrated with the operating systemıs security mechanisms because the operating system does not support these features. This lack of support is unfortunate because asynchronous I/O can be advantageous in some mobile database applications. In other scenarios where mobile databases are shared among different users, tightly integrated security would also be useful.
Despite these shortcomings, Microsoft SQL Server 7.0 for Windows 95 only requires 4MB memory and 1.5MB disk space per database, with full release 7.0 functionality. This makes it very attractive for mobile database applications on less powerful portable and desktop Windows 95 platforms. In fact, you can ship SQL Server 7.0 as a data manager embedded with an application, and users are actually unaware that they are using a DBMS under the application. On another positive note, the fact that SQL Server 7.0 has the same features on Windows NT and Windows 95 makes it possible to port applications and databases easily between these two platforms. The result is that you can deploy the same database and application seamlessly on more powerful and less costly laptops, as requirements and budgets may dictate in a heterogeneous mobile database architecture.
Although you require a full-function DBMS on the mobile platform, this DBMS must be easy to use and manage. I discussed performance, security, concurrency control, backup, and recovery in my miniseries last year. You have to remember that the mobile database user is just another user. You cannot expect them to be DBAs. Donıt expect them to perform extensive database administration and system management tasks in addition to their normal tasks. These users are also often farther than a telephone call away from the organizationıs support center or in a different time zone from the support staff. Even the most slick, flashy, useful, and efficient mobile database application will fail if it places too much administrative burden on its users.
In Microsoft SQL Server 7.0, as many as 20 configuration parameters have been removed and many others have been simplified to achieve more automatic configuration and tuning. Whereas in previous releases you had to adjust these settings manually, the server now dynamically adjusts its memory and lock resource use ı both increasing the resources when required and releasing them automatically when they are no longer needed. Similarly, database files can expand automatically from their originally specified size. Each time the file fills, it increases by a specified growth increment. This feature eliminates the need for a DBA to issue SQL alter statements at awkward times. It also reduces the need to monitor disk space utilization constantly, which is a great relief for mobile database users who are away from their support center for long periods of time. SQL Server 7.0 does not implement databases on database devices and segments anymore, but a database now consists of two or more Windows files, which are much easier to manage. You can create an entire database and all its files through the new create database statement.
Mobile databases can also have an impact on system management personnel and the procedures they follow. With mobile database architectures, they often have to manage a larger collection of hardware, software, and related components, which are not always directly accessible. Because of roving users, the mobile database platforms are usually not connected to managed networks. It may be very costly to have system management personnel visit all the mobile database users. It might also be very inconvenient and unproductive to have all the mobile database users bringing their machines in for database or application software upgrades. In an organization with a large number of mobile database users on the road, scheduling upgrades and running with two releases of the applications and the database may turn into a logistical nightmare.
The Microsoft Management Console (MMC), a new point-and-click user interface and framework for BackOffice server management, has a new snap-in component called SQL Server Enterprise Manager. Robin Schumacher described the functions of the SQL Server Enterprise Manager in detail in his review in June 1998. For mobile databases, the Enterprise Manager has new server administration features, which let you manage many servers using one centralized server. You can group servers into logical units called domains and perform cross-server transactions and multistep jobs from a single designated master server. It can address a number of management topologies for connected and disconnected sites. For mobile databases in particular, it employs a pull model, in which the target sites pull the jobs from the master agent when they are connected. Once pulled, the jobs run without requiring a connection to the master agent. With this functionality, the mobile database users can download and install application and database upgrades without having to return to the support center.
For a large mobile workforce, this pull model reduces the deployment window of a new release considerably. Although all the upgrades can take place asynchronously, which is preferable for an autonomous and mobile user population, you do not need to schedule the upgrades and be able to run with two releases for a lengthy period of time. For control purposes, the target sites can report the status of the jobs back to the master agent; this happens the next time a connection is established. These jobs are normal SQLAgent jobs, which run as though someone had entered them using the user interface. The target sites can execute these jobs as part of a schedule as they would any other job. The Maintenance Plan wizard, which you use to configure integrity checks, optimizations, database backups, and transaction log dumps, can set up these operations for multiple databases as multiserver jobs, so the plans will be applied on all the servers when they connect. You can broadcast similar jobs to the mobile databases for regular backup and maintenance runs.
Although the new Microsoft SQL Server 7.0 is not 100 percent self-managing ı as a matter of fact, no desktop DBMS satisfies this requirement ı it is encouraging to see that SQL Server 7.0 is nevertheless getting closer to achieving that goal.
A mobile database application can have a considerable impact on the laptop computer on which it runs, because the client application and the database server must run on the same machine. Compared to the total resources normally available on such systems, a mobile database application requires a significant amount of processing, memory, and disk space resources to run effectively. These systems must therefore be as efficient and use as little resources as possible.
Microsoft has greatly improved the query processor in SQL Server 7.0. It now uses hash join, merge join, and hash aggregation techniques, which are big improvements over the nested-loop join technique, which was the only join technique supported by SQL Server 6.5. It uses index intersection and union techniques on multiple indexes to filter data before it retrieves rows from the database. All the indexes on a table are maintained concurrently and constraint evaluations are part of the query processorıs execution plan. These features improve the resource utilization and execution speed of many query and update operations. Microsoft has removed many of the query and optimization limitations of SQL Server 6.5. It is less sensitive to index-selection issues, resulting in less tuning work. A single query can now reference 32 tables and use more than 16 internal work tables. Compile-time predicate transitivity and constant folding greatly improve the quality of query plans.
The query processor also regathers statistics automatically, using fast sampling. This process ensures that it uses the most current statistics and eliminates regular statistics maintenance runs. The latter, again, reduces the periodic maintenance schedule, which is very useful for DBA-less mobile databases.
As I discussed in my October 1997 column, replication is an important component of most mobile database architectures. In most of these architectures, you have to use replication, specialized middleware, or home-grown software to communicate between the mobile databases and the host database ı most architectures use the mobile DBMS vendorıs replication facilities. How the replication mechanisms record transactions and the way in which the mobile databases communicate with the host database are very important, but the configurations supported by the replication facilities can have an even bigger impact on the mobile database architecture. You require a replication configuration that suits the organizationıs data movement requirements ı it should not be necessary to force the mobile database information architecture to fit in with the capabilities of the replication facilities.
In SQL Server 7.0, the publish-and-subscribe replication facilities of release 6.5 have been extended to three different types. These fall into three different categories on the autonomy vs. consistency scale:
Transactional replication occurs when transactions are incrementally distributed to subscribers as changes are made on the publisherıs database. This is useful to apply the changes made on the host database to all the mobile databases and to apply the changes made to a single mobile database to the host database. With this type of replication you can get update conflicts if the same data is updated in more than one mobile database. Therefore, this is a low-autonomy, high-consistency solution. You would use this solution for the type of system where the same data should not be updated on more than one mobile database, or where you want to enforce data consistency very strictly. However, I would not use this approach for time-critical data, such as airline reservations because the delay between the updates (during replication recording) and the conflict checking (during replication propagation) may be too long.
Snapshot replication occurs when you use a snapshot of the data in a publication at the publisher to replace the entire replicated dataset at the subscribers on a periodic basis. This method is useful to populate initially or periodically refresh mobile databases from the host database. It is a no-autonomy, high-consistency solution. You would typically use it to manage personal mobile data marts from a departmental data mart or a central data warehouse where the mobile users never update the mobile databases, but their mobile databases are periodically refreshed with new data from the host database. You can also use snapshot replication for more update-intensive configurations, for example, where the mobile database users update their mobile databases and periodically roll up their updates to the host database. This would work correctly as long as the updates take place on nonoverlapping data sets, for example, where a userıs snapshot can be replaced at the target host database without affecting other data sets periodically replaced from other snapshots.
Merge replication occurs when the participating sites can make autonomous changes to the replicated data, and at a later time all the changes made at the various sites are merged. Merge replication does not guarantee transactional consistency, but you can use it in scenarios where changes are made to the same data in multiple mobile databases. It is a high-autonomy, low-consistency solution. You would use this for applications where the mobile database users either apply their updates to disjointed data sets, or where it doesnıt matter if a few consistencies are overridden on the host database. An example would be traveling salespeople, where they usually sell different products to different clients in different regions, or for scenarios where it is not a big deal if the consolidated sales view is out by one or two units. However, you cannot make the same compromises on customersı orders, remuneration data, or medical patient data!
A very useful configuration for mobile databases is what Microsoft labels Updating Subscribers. (See Figure 1.) In this scenario, one subscriber has to update the publisherıs and his or her own database in a single transaction, where full transactional consistency is achieved through a two-phase commit protocol involving the subscriberıs database and the publisherıs database. Because the transaction takes place on the publisherıs database, conflicts are immediately detected and, in that way, avoided. Because the transaction also takes place on the updating subscriberıs database, its user cannot suffer from the "missing update" phenomenon. This is where the subscriberıs update temporarily goes "missing" (from his own database, as perceived by the user) while it is being asynchronously replicated back to his own database ı and typically it cannot replicate back to the database as the table is locked trying to find the"missing" update.
Depending on the replication configuration, the changes can then trickle asynchronously through to the other subscribers if they use transactional replication, or they can download the changes, also asynchronously, if they use snapshot replication. This configuration is useful for a semiautonomous, high-consistency implementation, for example, when salespeople sell a limited inventory of products, such as cars or farm implements, or for airline reservation-type applications. However, you have to take care using it for high volume updates, due to the traffic and conflict checking at the publisherıs database. It is also not so useful for disconnected subscribers ı as is often the case with mobile database users. However, you can still use it with mobile databases if the update frequency is low enough and the transactional consistency is important enough to require a connection every time a subscriber requires an update. For example, a successful farm-implements salesperson probably only sells one or two harvesters in a week!
Microsoft is reportedly planning an implementation of Queued Updating Subscribers for SQL Server 7.1, where the updates from the subscriber can be queued to the publisher and from there be fed to the other subscribers. Although this scenario achieves slightly lower transactional consistency (because of the possible conflict at the publisher), its "fire and forget" approach is very useful for disconnected mobile updating subscribers.
SQL Server 7.0ıs replication facilities are now built directly into the database server and are configured and managed efficiently and relatively easily through the SQL Server Enterprise Manager. You can also set up the replication configurations through scripts, which makes it easier to deploy to large numbers of mobile database users. The replication facilities are now so closely tied to the DBMS server that the execution of stored procedures can be replicated. This is much more efficient than replicating the effects of a stored procedure because they were captured as dynamic operations in a transaction log. Subscribers can also subscribe through ODBC or OLE/DB interfaces, which makes it possible to replicate to DB2, Oracle, and Microsoft Access subscribers. The replication facilities also include enhancements for anonymous pull subscriptions, and they have built-in support for data replication and distribution to the Internet. SQL Server 7.0 also includes COM interfaces through which you can programmatically access its store-and-forward replication services. This allows non-SQL Server applications to use the SQL Server 7.0 replication infrastructure to publish their data.
Before Microsoft released SQL Server 7.0 on Windows 95, I considered Sybase SQL Server Anywhere, along with OpenIngres Desktop, my favorite mobile database DBMSs. Sybase SQL Server Anywhere was one of the first desktop DBMSs with relatively complete relational functionality and offline asynchronous replication facilities. OpenIngres/Desktop was a very close second with an equally powerful DBMS, with similar relational support, time-based events, and cascading replication.
The release of SQL Server 7.0 as an extremely viable mobile DBMS for Windows 95 ı especially with its new replication facilities ı can have a tremendous impact on the mobile database market. We all know how Microsoft can barge in on a seemingly established market segment. However, the opposition is not standing still. Sybase announced a new version of Sybase SQL Server Anywhere, called Sybase Adaptive Server Anywhere 6.0, which should be available by the time you read this ı even, reportedly, on palmtops running Windows CE. How much more mobile can you get? It will be very interesting to see who dominates the mobile database rally in the long run.
Figure 1. Updating subscribers.