Chapter 5 - Developing Mobile Application

Changing demographics and the popularity of laptop computers are fueling an explosive growth in mobile applications. Many organizations are decentralizing parts of their companies to save money and provide employees with a more flexible work environment. In addition, workers increasingly use computers when away from the office. According to Dataquest 1998 Mobile Computing Forecast (gartner12.gartnerweb.com/dq/), the mobile computing market is expected to grow more than 18 percent per year by 2002.

Increasingly, people use computers when they are on the road. For example, sales personnel might use a computer to enter orders, update customer information, or manipulate financial information. When they reconnect to the network, new or updated information must be merged into the enterprise databases. Similarly, any changes made to the enterprise databases while the user was offline must be copied to the user's computer. The application logic that is required to synchronize the multiple databases can be complex. For example, the application must resolve conflicts if two remote users update the same information independently while offline.

This chapter describes why Microsoft SQL Server version 7.0 offers a compelling database and development architecture for implementation and administration of mobile applications. It also compares SQL Server 7.0 functionality with that of another product for mobile applications, Sybase Adaptive Server Anywhere 6.0.

Evaluating Mobile Database Solutions

For many users, developing, deploying, and administering mobile applications can be challenging without the right tools and the appropriate infrastructure. Many enterprise-class relational database management systems (RDBMSs) were designed for client/server–based computing. Client/server applications typically assume that the user has a permanent connection to a server. For this reason, there is no local data store and no facility for working offline. A traditional client/server application can be modified to work in a mobile environment, but the code required to ensure that data on the client and server remains synchronized is considerable.

To develop scalable mobile applications, you must rely on services in the operating system and RDBMS to distribute and synchronize updates made on either the mobile client or the central database. The operating system and RDBMS should provide these basic features for developing a mobile database application:

• An RDBMS engine that can run on both the client and the server and that can synchronize updates easily on either the client or the server. 

• Administration tools that allow database administrators to deploy client code and data easily, as well as manage synchronization between many clients and servers. 

• Tools that allow developers to migrate existing client/server applications easily to applications that support mobile clients. 

RDBMS Client and Server Engines

When evaluating database applications for mobile clients, you should ask these questions about the RDBMS client and server engines:

• Does the vendor offer a single RDBMS engine that scales from a mobile client to an enterprise-class server? 

• Do the local and central RDBMS engines support atomic transactions? 

• Does the RDBMS engine support two-way merge replication, which guarantees delivery of data in a disconnected environment? 

• Does the RDBMS engine support heterogeneous replication to other RDBMS engines? 

• Will the RDBMS engine scale to support multiple mobile clients? 

• What are the system requirements for running the database engine and replication engine on the client? 

Administration and Total Cost of Ownership

When evaluating database applications for mobile clients, you should ask these questions about administration and total cost of ownership:

• How easy is it to manage security for multiple mobile clients?

• What tools and built-in functions are provided for resolving conflicts when users independently update the same record with different information? 

• Can the administration tools be customized easily to meet the specific needs of an organization? 

• Do the tools take advantage of existing skill sets, or will the database administrator need to learn new procedures and programming languages to administer the database efficiently? 

• Can the database administrator manage a multiple clients as a single logical group? 

• How well are the administration and system-monitoring tools integrated into the operating system? 

• How easy is it to deploy both the database engine and the databases on the client? 

Cost-Effective Application Development

When evaluating database applications for mobile clients, you should ask these questions about the tools for developing mobile applications:

• How easy is it to convert an existing client/server application to a mobile application? 

• What tools are available for upsizing tables, schemas, data, and referential-integrity information from a desktop database such as Microsoft Access or Microsoft FoxPro to an RDBMS engine that supports multiple mobile clients? 

• What tools are available for building best-of-breed, three-tiered applications? 

Supporting Mobile Applications with SQL Server 7.0

Microsoft SQL Server 7.0 is a full-featured, enterprise-class RDBMS that runs on Microsoft Windows NT, Windows NT Server, Enterprise Edition, Microsoft Windows 95, and Microsoft Windows 98 operating systems.

Scalability and Performance

Customer questions about scalability and performance can include the following:

• How easily can the application move from a laptop to an enterprise-class server? 

• How many concurrent clients can a single server support for a given online transaction processing (OLTP) or decision-support scenario? 

• What is the response time for executing complex queries on large databases? 

• Is the RDBMS designed to support backup, restore, and maintenance of very large databases? 

An important benefit that SQL Server offers for mobile applications development is that applications written for SQL Server run unchanged from Windows 95 or Windows 98 laptops to Windows NT Server, Enterprise Edition, multiprocessor clusters. SQL Server 7.0 is the first RDBMS engine to provide a single codebase that can scale from a mobile client to a high-end enterprise-class server.

 

SQL Server 7.0 handles the needs of both small and large organizations. Recent benchmarks from the Transaction Processing Performance Council (TPC) show that SQL Server 7.0 is among the best-performing RDBMSs available for the Windows NT operating system. For more information about TPC benchmarks, see https://www.tpc.org.

Support for Smaller Databases

SQL Server 7.0 includes the following support features for smaller databases located on either a mobile client or a workgroup server. In these environments, ease of management is the primary concern.

Dynamic memory

Dynamic memory improves performance by optimizing memory allocation and usage. The simplified design minimizes contention with other resource managers. This feature simplifies management of SQL Server on the Windows 95 or Windows 98 and Windows NT Workstation operating systems because administrators do not need to predefine the cache size for particular databases.

Dynamic space management

The database can grow and shrink automatically within configurable limits, minimizing the need for intervention by the database administrator. It is not necessary to preallocate space and manage data structures. Dynamic space management also simplifies administration of mobile clients.

Automatic statistics maintenance

SQL Server maintains statistics on data distribution to improve query performance. This means that application developers can rely on the query optimizer for the latest statistics rather than overriding the query optimizer with specific hints in their Transact-SQL statements.

Scalable storage

The disk format and storage subsystem provides storage that is scalable from very small to very large databases. Specific changes include:

• Simple mapping of database objects to files.

  This provides less complex management and more tuning flexibility by mapping database objects to specific disks to balance I/O load. 

• Efficient space management*.* 

  Page sizes have increased from 2 KB to 8 KB, 64 KB I/O, column limits have lifted, variable-length character fields have increased to 8 KB, and the ability to add and delete columns from existing tables without unloading and reloading the data has been added. 

• Support for terabyte-size databases*.* 

  Redesigned utilities support large databases efficiently. 

Support for Large Databases

SQL Server 7.0 includes the following features to support very large databases that are typically located in a managed-server environment. In this environment, efficiency and performance are the primary concerns.

Hash joins and merge joins

Hash, merge, and nested loops joins are available for processing complex queries. Multiple join types can be used within a single query. The query optimizer supports specialized join operations, such as star schema joins.

Superior cache management

In general, larger I/O sizes support higher throughput rates. In SQL Server 7.0, the page size is 8 KB, extents are 64 KB, and most I/O operations use 64 KB blocks. Smart I/O is the key to enhancing performance. More efficient read aheads, physical row-order scans, and parallel I/O also improve performance.

Parallel queries

SQL Server 7.0 provides intraquery parallel execution across multiple processors. Steps in a single query are executed in parallel, delivering the optimum response time. Users can take advantage of symmetric multiprocessing (SMP) hardware for complex analysis.

Dynamic row-level locking

Full row-level locking is implemented for both data rows and index entries. Dynamic locking automatically chooses the optimal level of lock (row, page, multiple page, table) for all database operations. This feature provides optimal concurrency levels.

Large memory support

SQL Server 7.0, Enterprise Edition, supports memory addressing greater than 4 gigabytes (GB) with Alpha processor-based systems running the Windows NT Server 4.0 operating system.

Read-ahead

Smart read-ahead logic improves performance and eliminates the need for manual tuning.

Backup and restore

The parallel backup and restore utilities in SQL Server 7.0 are limited only by device speeds. Very high server transaction processing rates are maintained during full online backups.

Bulk copy program (BCP)

The bcp utility offers fast import and export transfer capabilities. The bcp utility uses OLE DB and works in conjunction with the query processor to load and unload data quickly.

Sybase Scalability and Performance

Sybase SQL Anywhere Studio is a family of RDBMS server products from Sybase, Inc., that includes the Adaptive Server Enterprise and Adaptive Server Anywhere 6.0 server engines. Both server engines have similar architectures but are based on different code bases. This means that applications written for Sybase Adaptive Server Enterprise may not be compatible with Sybase Adaptive Server Anywhere, and vice versa. The result is additional development, testing, and administration to assure application compatibility when using both database products.

The Sybase Adaptive Server Anywhere 6.0 server engine is not primarily offered as an enterprise-class database, so there are no TPC benchmarks available. Organizations would need to upsize to Sybase Adaptive Server Enterprise to obtain a comparison. For a comparison of Microsoft SQL Server and Sybase Adaptive Server Enterprise benchmarks, see the TPC Web site at https://www.tpc.org/.

Sybase Adaptive Server Anywhere 6.0 does not support dynamic memory allocation. Typically, a database in a mobile or disconnected environment requires additional memory only for short periods of time to process queries. Sybase Adaptive Server Anywhere 6.0 requires a fixed amount of memory while the application is running. There also is an administrative overhead because the cache size on the client must be preassigned by using startup parameters.

Sybase claims that Adaptive Server Anywhere 6.0 can run in as little as 1 megabyte (MB) of memory. However, this figure does not take the following factors into consideration:

• A 2-MB cache size, which is required for any meaningful work. 

• If you use Java, the Java Virtual Machine requires an additional 2 MB per database, and the database memory cache must be increased to use Java in the database. This memory cannot be reallocated.

• A default memory requirement of 2 MB for the SQL remote synchronization engine.

In a real-world application, Sybase Adaptive Server Anywhere 6.0 users need from 6 MB through 8 MB of memory on a client computer, about the same as for SQL Server 7.0. Because mobile clients are running all the code on the client, Windows 95 or Windows 98–based users need at least 32 MB of memory, and Windows NT Workstation users need at least 48 MB. These figures may vary depending on the number and size of applications running on the client.

Advanced Replication Functionality

SQL Server 7.0 offers three types of replication:

• Snapshot 

  The replication agent copies an entire view of data to another computer. The destination database view is overwritten with the new version. 

• Transactional 

  Transactions (INSERT, UPDATE, or DELETE statements) executed on one computer are replicated to another computer. 

• Merge 

  Updates on any computer are replicated to another computer at a later time. 

  For applications with disconnected users, merge replication is the most frequently used form of replication. Merge replication maintains consistency between mobile clients and the central server. It supports bidirectional updates, meaning that during the synchronization process, new or updated records on the mobile client are copied to the server, and vice versa, to ensure that both copies remain synchronized. If two mobile clients change the same data, SQL Server applies criteria that you established to resolve the conflict on the central database automatically. 

SQL Server 7.0 uses a publish and subscribe metaphor to set up and administer replication. A Publisher offers publications, which contain articles (tables or views), to which other SQL Server databases or Open Database Connectivity (ODBC) data sources can subscribe. A Subscriber receives publications. A distributor retrieves and stores modified data from the Publisher and sends the data to the Subscriber. In the case of merge replication, the Subscriber can be either another SQL Server database or a Microsoft Access 2000 database.

When a subscription is initiated, the Publisher sends the Subscriber an initial snapshot of the publication. This creates the necessary database objects (schemas and data) for the Subscriber. This customizable script is generated automatically by SQL Server.

After the initial snapshot is set up, only changes made on the Publisher (not the entire publication) are sent to the Subscriber. Each time a record is added, modified, or deleted, SQL Server detects the changes and sends the appropriate Transact-SQL statement to a distribution server. If many clients are subscribing to a single SQL Server database, an administrator may assign the role of distribution server to another instance of SQL Server to improve performance. The distribution engine can send updates to Subscribers, either on demand or as scheduled. The Subscriber also can initiate merge replication.

SQL Server 7.0 allows you to publish a subset of rows and columns from a table. For example, sales representatives typically cover a specific geographic territory. In this case, you would replicate only the data relevant to each sales representative. SQL Server allows you pass parameters to publications, which simplifies the process of supporting many subscribers. For example, an administrator may want to pass the username and computer name to a publication as a parameter so that each sales representative receives data about their sales region only.

 

The architecture required to support merge replication may vary significantly depending on these factors:

• The number of users the system must support 

• Whether users will be dialing in over phone lines or ISDN lines, or connecting directly to the local area network (LAN) when synchronizing 

• The size and number of tables that require synchronization 

• The anticipated frequency of updates (daily, weekly, or other) 

A carefully considered architecture and rigorous operational procedures are key to the successful implementation of a mobile application. For example, it is not practical to have multiple clients simultaneously trying to synchronize with a single database. This not only strains the physical resources of the network, but it also means that updates to the central database occur so frequently that nobody has an up-to-date version of the data.

To solve this problem, the central database should be partitioned over several servers that are located as geographically close as possible to the mobile client. The following diagram demonstrates how a database might be partitioned to support multiple clients.

Performance and scalability of merge replication can be greatly enhanced by:

• Scheduling replication between client and server databases for off-peak times.

• Refreshing look-up tables on mobile clients only when needed. 

• Replicating only the rows and columns specifically required by each mobile client. 

• Implementing procedures that avoid simultaneous synchronizing by multiple clients. 

• Using anonymous subscriptions. 

SQL Server 7.0 uses a variety of communication methods to synchronize updates:

• Remote Access Services (RAS), to provide native connectivity to a LAN 

• A standard Internet connection (FTP)

SQL Server 7.0 assures delivery of updates regardless of the communication protocol. For more information about network library support, see SQL Server Books Online.

Sybase Merge Replication

Sybase Adaptive Server Anywhere 6.0 uses a message-based replication system called SQL Remote to synchronize updates between a client and the server. Sybase SQL Remote uses the File, FTP, MAPI, SMTP, and VIM message-based protocols.

There are several problems inherent to relying on message-based protocols:

• The user must have e-mail installed and configured. 

• If you choose SMTP or VIM, messages generated by Sybase Adaptive Server Anywhere 6.0 can appear in the user's inbox. Users might delete messages generated by SQL Remote and cause synchronization to fail. 

• The File protocol is a file-sharing system that can be difficult to manage and administer. 

• The administrator must enter each user's messaging address separately. 

Replication with Heterogeneous Databases

Many organizations must support databases from a wide range of vendors. SQL Server 7.0 supports replication to and from heterogeneous data sources using ODBC or OLE DB. For example, Microsoft Access 2000 supports bidirectional merge replication natively. SQL Server 7.0 supports transactional replication to any heterogeneous Subscriber. Heterogeneous Publishers, however, must support the SQL Merge Agent application programming interface (API) to participate in bidirectional merge replication. For more information about bidirectional merge replication, see SQL Server Books Online.

SQL Server 7.0 includes a SQL Merge control object that manages heterogeneous replication. The SQL Merge control object provides the functionality of the Merge Agent and is used in conjunction with subscriptions to merge publications. The same properties that can be set by invoking the SQL Merge control object are available using the replication Merge Agent utility.

The following illustration shows a heterogeneous replication scenario. In this example, a heterogeneous Publisher is configured to exchange updates with a central SQL Server database. The central SQL Server Publisher then synchronizes changes to the three client Subscribers. All of these Publishers and Subscribers are managed by SQL Server Enterprise Manager using SQL Distributed Management Objects (SQL-DMO).

Sybase Interoperability

Sybase Adaptive Server Anywhere 6.0 supports heterogeneous replication through the Sybase Open Client Interface. However, you must use the Sybase Replication Server to gain access to heterogeneous data sources. If you want Replication Server to be a Subscriber to Sybase Adaptive Server Anywhere 6.0, you must use the Replication Agent for Sybase Adaptive Server Enterprise.

Choosing a Development Architecture for Mobile Applications

Choosing the appropriate architecture and best-of-breed tools is essential to the success of any application, and the Microsoft Windows Distributed interNet Applications (DNA) architecture is a key tool for developing mobile applications.

Windows DNA

Most companies are turning to distributed computing and the Internet to provide users with fast access to information. Windows DNA is a framework for building a new generation of computing solutions that brings together the worlds of personal computing and the Internet. Windows DNA is the first application architecture that fully embraces and integrates both the Web and client/server models of application development.

By using the Windows DNA architecture, developers can build scalable, multitier business applications that can be delivered over any network, provide open access to diverse data sources across different platforms, and be freely accessed by any client computing platform. Windows DNA allows organizations to develop cross-platform applications that can access data sources on any server environment including Windows NT, Unix, and Systems Network Architecture (SNA)–based systems. By using Windows DNA architecture, organizations can capitalize on their existing technology infrastructure while also adopting new technologies (such as the Internet and World Wide Web) to meet new requirements.

The Windows DNA architecture has three tiers:

• User interface and navigation 

  This layer is the user interface plus basic validation code. Typically, this is a Web browser or a full-featured front-end client. 

• Business process 

  This layer, which includes middleware components and system services, handles application logic. 

• Integrated storage 

  This layer is the database or another unstructured storage layer. 

 

For more information about Microsoft Windows DNA, see https://www.microsoft.com/net/.

In a mobile computing environment, the business process and integrated storage layers also must reside on the client. The Microsoft Component Object Model (COM) and Distributed COM (DCOM) components provide the foundation for component integration in a distributed environment. This offers several advantages when developing mobile applications:

• If designed properly, a COM component can run on either a Windows operating-system client or server without modification. This means that mobile clients can share the same code as LAN-based clients. 

• Microsoft Visual Studio 6.0 (which includes Microsoft Visual C++, Microsoft Visual Basic, Microsoft Visual J++, and Microsoft Visual FoxPro) is the complete development tools suite for building enterprise applications for the Windows DNA framework. Developers can also choose from third-party development tools to create Microsoft SQL Server applications.

• Microsoft ActiveX Data Objects (ADO), an API for OLE DB, provides an object-based interface to a wide range of data sources, on both the client and the server. 

Sybase Applications

Sybase Adaptive Server Anywhere 6.0 can participate in multitiered applications. However, heterogeneous joins are not supported without using Sybase OmniConnect.

Sybase Adaptive Server Anywhere 6.0 supports Java in the database engine. In theory, Java code that is written to run in the database server can be moved to run elsewhere. However, the code relies heavily on Java extensions that make sense only in the context of the database server. Moving the code to another location could break it.

Web-based Solutions

Using a Web browser for mobile applications presents special challenges for the developer. If the Web application interacts with a database or uses server-side scripting, you must install Microsoft Personal Web Server (PWS) on the client to provide offline functionality. PWS is included with the Windows 98 operating system and can be installed from the Windows 95 and Windows NT Workstation Web sites. Unattended installation of PWS is supported. Applications written for the Windows NT Server built-in Web server, Internet Information Services (IIS) version 4.0, should run unchanged on PWS as long as there are no references to server-specific APIs.

IIS 4.0 is a fully programmable Web server that offers developers the following basic features:

• Active Server Pages 

  You can write server-based scripts in Visual Basic, Scripting Edition; Microsoft JScript; or PerlScript. 

• ADO 

  You can access any OLE DB or ODBC data source through the ADO object model by using any of the Visual Studio tools.

• COM and Microsoft Transaction Server 

  You can reference COM components, including those in Microsoft Transaction Server (MTS), part of Windows NT Server, written in a variety of languages by using Visual Studio tools. 

• Transactional Web page support 

  You can call MTS-supported databases by using ASP scripts. 

• Session management 

  You can use session-level or user-level variables on the server to store user-specific information. 

Client-Side and Server-Side Debugging

By using Visual J++ 6.0 and Visual InterDev 6.0 (included in the Microsoft Visual Studio development system), developers delivering Web-based applications can take advantage of COM components and SQL Server. By including Microsoft FrontPage, the Web-based developer has a consistent and easy-to-use tool set for Rapid Application Development (RAD) that provides both component and site creation ability and content development ability. Visual J++ 6.0 and Visual InterDev 6.0 both support rich server and client-side debugging. By using either tool, a developer can debug from the client-side script through the ASP page and into the COM and MTS components.

Sybase Web-based Solutions

Sybase Adaptive Server Anywhere 6.0 comes with an offline Web server called Sybase PowerDynamo, which includes tools for deploying Web pages to clients. PowerDynamo uses a superset of JScript for building server-side scripts. Database access and data formatting are provided by extensions to JScript.

PowerDynamo provides the basic functionality for developing a Web application and accessing it offline. However:

• Programmers must learn proprietary extensions to JScript for connecting to a database and formatting data. 

• PowerDynamo does not support COM components or calls to external programs, which limits application functionality, compatibility, and extensibility. 

• Although Sybase Adaptive Server Anywhere 6.0 provides transaction support, developers cannot define a Web page as a single transaction. 

Conversion of Existing Applications to Support Mobile Clients

In many cases, organizations will convert existing client/server applications or file-sharing applications to support mobile clients. Client/server applications based on SQL Server 7.0 should not require significant changes. For example, developers might add refresh or synchronize features to an application that initiates the synchronization process with the central SQL Server database.

To take advantage of SQL Server's built-in merge replication features and scalability from the desktop to the enterprise, you should convert your application to SQL Server 7.0.

Migrating Data

Microsoft provides resources (tools, services, and documentation) to guide you in your migration of data to SQL Server 7.0. For more information about migrating data to Microsoft SQL Server 7.0, see https://www.microsoft.com/sql.

Microsoft SQL Server 7.0 Data Transformation Services (DTS) dynamically imports or exports data to and from any ODBC or OLE DB data source. DTS includes a graphical tool for scripting data transformations. This feature can be used for moving data to SQL Server or for performing regularly scheduled data transformation operations.

Microsoft Access 2000 will provide direct support for the Jet engine and the Microsoft Database Engine (MSDE). MSDE, which will ship with Microsoft Office 2000, shares the same code base as SQL Server 7.0. Developers can use Access 2000 to upsize existing Access applications to SQL Server or to develop new applications. The Access 2000 upsizing tool will move not only all the tables and data to SQL Server but also will migrate the referential integrity, Access views, and index definitions to SQL Server.

By using Microsoft Visual FoxPro 5.0, applications can be moved to SQL Server. The SQL Server Upsizing Wizard (part of Visual FoxPro) moves all the tables, views, referential integrity, index definitions, and data to SQL Server.

Migrating Applications

If your application currently runs on another database, only ODBC or OLE DB connectivity is required to move the data using DTS. For more information about migrating applications to run on Microsoft SQL Server 7.0, see https://www.microsoft.com/sql.

Administering Mobile Applications

Mobile applications can require more administration than LAN-based applications. In environments that need to support thousands of mobile clients, there can be an increase in administrative tasks, including:

• Installing the client database engine, databases, and application code. If offline Web access is required, you might need to install Microsoft PWS.

• Establishing security for users. 

• Configuring and monitoring merge replication. 

• Managing conflict resolution. 

• Backing up remote databases. 

Installation

Microsoft Systems Management Server (SMS) is the preferred solution for installing SQL Server 7.0, PWS, and client application code on multiple clients. Among other features, SMS offers unattended software distribution of off-the-shelf programs and in-house applications. For more information about Microsoft SMS, see https://www.microsoft.com/smserver/default.asp.

An alternative to using SMS is to perform an unattended installation and configuration of SQL Server and PWS. Applications created with Visual Studio also can be configured for unattended installation.

Do not preload the SQL Server databases for the application, because the SQL Server Snapshot Agent does this automatically when you set up replication.

Administration Tools Architecture

SQL Server Enterprise Manager is a snap-in component for the Microsoft Management Console (MMC), a universal tool for managing the Microsoft BackOffice family of products and third-party server products. MMC, which is supported on the Windows 95 and Windows 98 and Windows NT operating systems, is a COM-based component that can be controlled from any programming tool that supports COM, such as Visual Basic, Visual C++, PowerBuilder, or Delphi.

SQL Server also offers COM components that can be used to write customized applications to fully administer a SQL Server database: SQL-DMO, SQL-Namespace (SQL NS), SQL-DTS, and replication components. These objects are so robust that they were used by Microsoft to build SQL Server Enterprise Manager, the primary tool for performing administrative tasks on SQL Server 7.0.

Any number of SQL Servers can be centrally administered, either by using a graphical tool such as SQL Server Enterprise Manager or by using a COM-based interface such as SQL-DMO. You can write SQL-DMO scripts in any programming language that supports COM, including Windows Script Host (WSH) for Windows 98 and Windows NT, Microsoft Visual Studio, Microsoft Office Visual Basic for Applications (VBA), IIS 4.0 ASP, PowerSoft PowerBuilder, and any third-party tools that support COM; for example:

• Graph a database size across a group of servers by using Microsoft Excel with SQL-DMO. 

• Create a program that backs up a database by using Visual Basic with SQL-DMO. 

• Create an ASP application that sets up replication based on input from an administrator. 

By using SQL-DMO, a programmer can write a few lines of code to back up an entire group of registered servers running SQL Server databases. The SQL-DMO object hierarchy and sample code is included with SQL Server 7.0, Microsoft Developers Network (MSDN), and the Microsoft Knowledge Base.

SQL Server provides dynamic self-management, which allows the server to monitor and manage itself and minimize repetitive tasks. SQL Server 7.0 also offers more than 40 wizards, as well as task pads, to help database administrators.

Multiserver Administration Features

Many database administration tasks can be repetitive and time-consuming. SQL Server 7.0 allows administrators to manage a group of servers as a single entity. In a multiserver environment, the master SQL Server server distributes jobs and receives events (notification of job success or failure) from the target SQL Server server. The master server stores the central copy of all jobs to be executed on the target servers. For example, you can write a single backup task that applies to all servers in your organization.

All jobs are managed by SQL Server Agent, which helps database administrators schedule maintenance functions such as backups, SQL Server programs, or external programs. It can maintain a list of database administrators and their work schedules and provides appropriate notification by pager or e-mail regarding the success or failure of a particular task. SQL Server notifies operators when an unexpected error occurs on the server, or starts a program that can take corrective action.

These features provide organizations with proactive management tools that reduce the cost of administering multiple SQL Servers.

Sybase Administration Tools

Sybase Adaptive Server Anywhere 6.0, PowerDynamo, and Adaptive Server Enterprise can be administered from Sybase Central, which is similar to MMC, but has some significant differences:

• There is no published API or COM interface for extending Sybase Central; therefore, database administrators cannot automate the administration of Sybase Central from other applications. 

• Database administrators must use Watcom SQL scripting to automate tasks. Watcom SQL, like Transact-SQL, is a proprietary language. Watcom SQL requires more code than SQL-DMO does to perform similar tasks. Additionally, the choice of programming tools for Watcom SQL is limited.

• There is no support for debugging Watcom SQL from Sybase. 

Sybase Adaptive Server Anywhere 6.0 has no built-in scheduling capabilities and no alert notification. Proactive management of a server is not possible using this product alone.

Security

The SQL Server 7.0 security model supports Windows NT–based users and groups and supports SQL Server users and roles.

By improving integration with Windows NT, SQL Server 7.0 provides increased flexibility. Database permissions can be assigned directly to Windows NT users or groups, and you can define SQL Server roles to include Windows NT users and groups and SQL Server users and roles.

A SQL Server user can be a member of multiple SQL Server roles. This allows database administrators to manage SQL Server permissions through Windows NT groups or SQL Server roles, rather than directly through individual user accounts. System-defined server and database roles such as the dbcreator, securityadmin, and sysadmin fixed server roles provide flexibility and improved security.

A user passes through two security stages when working in SQL Server 7.0: authentication and permissions* *validation. The authentication stage identifies the user accessing a login account and verifies only the ability of the user to connect with SQL Server. If authentication is successful, the user connects to SQL Server. The user then receives permissions to access databases on the server by using an account in each database mapped to the user's login. The permissions validation stage controls the activities the user is allowed to perform in the SQL Server database. You can bypass this account mapping by granting permissions directly to Windows NT groups or users.

When users connect to SQL Server, the activities they can perform are determined by the permissions granted to their security accounts, Windows NT groups, or role hierarchies to which their security accounts belong. The user must have the appropriate permissions to perform any activity. The ability to assign logins and specify permissions is provided by SQL Server Enterprise Manager, Transact-SQL, and SQL-DMO.

Sybase Security

Sybase Adaptive Server Anywhere 6.0 provides limited support for Windows NT–based integrated security. Although login rights can be granted to Windows NT users, they cannot be granted to a Windows NT user group. This means that every user must be entered individually, regardless of whether database security or integrated security is used.

In addition, Sybase Adaptive Server Anywhere 6.0 does not support database security roles, which makes it more difficult to set up an application's administration infrastructure.

Merge Replication

The following steps are needed to configure merge replication for a remote user:

1. Set up the merge publications and articles on the central database to which the mobile client will receive a subscription. 

2. Register the mobile client in SQL Server Enterprise Manager. 

3. Subscribe the mobile client to the publications. 

If possible, set up an anonymous subscription from the client. For normal subscriptions, detailed information about each Subscriber is stored at the Publisher, and performance information about each Subscriber is kept at the Distributor. However, this information is not stored about an anonymous Subscriber.

If you have multiple Subscribers or do not want the overhead of maintaining detailed information, you can allow anonymous subscriptions. Additionally, this can be useful if you want to allow Subscribers using anonymous FTP over the Internet.

Many organizations partition a database by user. For example, a sales representative needs to see accounts only in his territory. One way to do this is to set up a separate publication for each user so that each publication receives only the appropriate data. SQL Server simplifies this process by allowing you to pass the username and computer name as a parameter to the Publisher, so one publication can handle many users.

If you have multiple clients that require customized publications, you should consider automating the procedure with SQL-DMO, which eliminates the time-consuming task of setting up clients one by one.

After replication is enabled between the central database and mobile client, you might want to monitor replication activities. SQL Server Enterprise Manager includes a replication monitor that logs, by Subscriber (unless they are anonymous), the success or failure of all replication activities. Because this information is stored in SQL Server tables, you can generate reports of replication activities easily.

Windows NT is the preferred operating system for your management console because it supports the Windows NT application event log and performance monitor. You also can set up alerts that provide notification by e-mail or pager when problems occur. For information about alert messages, see SQL Server Books Online.

Sybase Merge Replication

Sybase Adaptive Server Anywhere 6.0 uses a separate module called SQL Remote for managing mobile clients. For each remote user, the database administrator must specify a username, a transport protocol, and an address, rather than relying on the operating system for this information.

Sybase Adaptive Server Anywhere 6.0 also requires administrators to install the initial snapshot to the mobile client. SQL Server 7.0 executes the initial snapshot automatically.

Conflict Resolution

Replication conflicts are minimal if the data is partitioned correctly. By assigning ownership of data, you can minimize synchronization conflicts. For example, you might not want to allow sales representatives to change data in other sales representatives' territories. However, you do want to allow a sales manager to change data in several territories. SQL Server 7.0 provides a flexible approach to handling conflict resolution by managing conflicts automatically using built-in rules or custom rules.

By default, SQL Server 7.0 uses priority-based conflict resolution. Every Subscriber and Publisher is assigned a number between 0 and 100. Whenever a conflict occurs, the changes made by the client with the highest priority rating win.

A benefit of this priority rule is that the conflict-resolution mechanism will not undo changes made by the highest priority user. It does not prevent lower priority clients from making subsequent changes to the rows; however, when a conflict occurs, high priority rated replicas always win.

In the event that the priority rule results in a tie, then the change at the central database server wins. For all synchronizations, one site always acts as the central database server.

If you do not want to use a priority-based approach, you can use SQL-DMO to create your own conflict-resolution agent. You can use Visual Studio, C++, or any other language that supports COM, to write the conflict-resolution agent.

Sybase Conflict Resolution

Sybase Adaptive Server Anywhere 6.0 supports conflict resolution with table triggers. However, you must write a conflict-resolution script for every published table. There is no support for priority-based resolution, and there are no defaults. Writing a reliable conflict-resolution script is difficult without priority-based resolution.

Watcom SQL is the only language supported for resolving conflicts in Sybase Adaptive Server Anywhere 6.0.

Backup

It may not be necessary to back up remote databases because the data was replicated from a central database. If local backups are required, administrators can create a simple backup script to perform the backup (or restore). A backup or restore script can be executed either by SQL Server Agent at a predefined time or from an application using the SQL-DMO interface. Other database administration tasks can be managed in a similar way. SQL Server includes a database administration wizard that generates maintenance scripts for you.

Specifications

The following table outlines the differences between Microsoft SQL Server 7.0 and Sybase Adaptive Server Anywhere 6.0.

Topic	Microsoft SQL Server 7.0	Sybase Adaptive Server Anywhere 6.0*
Client Hardware Requirements
IBM PC or compatible	Yes	Yes
DEC Alpha	Yes	No
RAM (total system)	32 MB	32 MB1
Disk space (without help/samples)	66 MB (41 MB if shared components already installed)2	50 MB
Operating System Platforms3
Windows 95 or Windows 98	Yes	Yes
Windows 3.1	No	Yes
Windows NT	Yes	Yes
Novell Netware	No	Yes
Network Support
Named pipes (Windows NT only)	Yes	No
TCP/IP sockets	Yes	Yes
Novell Netware IPX	Yes	Yes
Appletalk	Yes	No
Banyan VINES	Yes	No
Shared memory (local only)	Yes	No
NetBIOS	No	Yes
SQL Compliance	SQL-92	SQL-92
Database Features
Transaction processing	Yes	Yes
INSERT, UPDATE, DELETE triggers	Yes	Yes
Stored procedures	Yes	Yes
Extended stored procedures (dynamic link libraries)	Yes	Yes
Bidirectional and scrollable cursors	Yes	Yes
Row-level locking	Yes	Yes
Cost-based query analyzer	Yes	Yes
Parallel query	Yes	No
Heterogeneous joins	Yes	No
Online backup and recovery	Yes	Yes
Binary large object (BLOB) support	Yes	Yes
Symmetric multiprocessing (SMP) support	Yes	Yes
Embedded SQL support	Yes	Yes
Unicode support	Yes	Yes
Import/export	Any ODBC data source	xBASE, ASCII, Lotus, DIF
Microsoft Distributed Transaction Coordinator (MS DTC)	Yes	No
Database Statistics
Databases per server	32,767	255
Index types	Clustered and nonclustered	Nonclustered
Maximum database size	1,048,516 terabytes	12 terabytes
Columns per index	16	999
Object name length (in characters)	128	128
Table Statistics
Indexes per table	249	32,767
Maximum table size	Limited to database size	1,024 GB
Tables per query	256	32,767
Tables per database	2 billion	32,767
Columns per table	1,024	999
Row size	8,060 bytes	2 GB
BLOB size per column	2 GB	32,767 bytes
Rows per table	Limited to database size	Maximum table size
Security
Integrated Windows NT security	Yes	Yes
Support for Windows NT groups	Yes	No
Roles-based security	Yes	No
Replication 4
Transactional (near real time)	Yes	No
Merge (two-way)	Yes	Yes
Snapshot	Yes	No
Homogeneous	Yes	No
Administration Tools
Central management console	Yes	Yes
Programmable management console	Yes	No
Group management	Yes	No
DTS	Yes	No
Performance monitoring	Yes (Windows NT)	Yes
Query analyzer	Yes	Yes
Query profiler for tracing SQL network traffic	Yes	No
Wizards	Yes	Yes
Alert notification	Yes	No

* Source: Sybase Adaptive Server Anywhere Reviewer's Guide

1 Sybase documentation states from 8 MB through 16 MB. This does not include the operating system.

2 The Microsoft SQL Server figure includes the master system database.

3 Sybase Central, PowerDynamo, and other tools require the Windows NT, Windows 95 or Windows 98 operating systems.

4 Sybase recommends Replication Server for robust replication.