Developing Mobile Applications: Comparing Microsoft SQL Server 7.0 to Sybase Adaptive Server Anywhere 6.0
by Andrew Coupe
© 1998 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Due to the nature of ongoing development work and because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
Microsoft, ActiveX, BackOffice, FrontPage, FoxPro, Visual Basic, Visual Interdev, Visual J++, Windows, and Windows NT are registered trademarks of Microsoft Corporation. Java is a trademark of Sun Microsystems, Inc. Other product or company names mentioned herein may be the trademarks of their respective owners.
Microsoft Corporation • One Microsoft Way • Redmond, WA 98052-6399 • USA
Microsoft Part Number: 098-81534
Contents
Introduction.............................................................................................................................................................. 4
Evaluating Mobile Database Solutions.............................................................................................................. 5
RDBMS Client and Server Engines................................................................................................................ 5
Administration and Total Cost of Ownership.............................................................................................. 5
Cost-Effective Application Development.................................................................................................... 6
Supporting Mobile Applications with SQL Server 7.0................................................................................... 7
Scalability and Performance........................................................................................................................... 7
Support for Smaller Databases............................................................................................................... 8
Support for Large Databases.................................................................................................................. 9
Sybase Scalability and Performance.................................................................................................. 10
Advanced Replication Functionality.......................................................................................................... 10
Sybase Merge Replication..................................................................................................................... 13
Replication with Heterogeneous Databases.............................................................................................. 14
Sybase Interoperability.......................................................................................................................... 14
Choosing a Development Architecture for Mobile Applications.............................................................. 15
Windows DNA................................................................................................................................................. 15
Sybase Applications............................................................................................................................... 16
Web-based Solutions...................................................................................................................................... 16
Client-Side and Server-Side Debugging............................................................................................... 17
Sybase Web-based Solutions................................................................................................................ 17
Conversion of Existing Applications to Support Mobile Clients............................................................ 18
Migrating Data........................................................................................................................................ 18
Migrating Applications........................................................................................................................... 18
Administering Mobile Applications.................................................................................................................. 19
Installation....................................................................................................................................................... 19
Administration Tools Architecture....................................................................................................... 19
Multiserver Administration Features................................................................................................... 20
Sybase Administration Tools................................................................................................................ 20
Security............................................................................................................................................................. 21
Sybase Security....................................................................................................................................... 21
Merge Replication........................................................................................................................................... 21
Sybase Merge Replication..................................................................................................................... 22
Conflict Resolution......................................................................................................................................... 22
Sybase Conflict Resolution................................................................................................................... 23
Backup............................................................................................................................................................. 23
Specifications......................................................................................................................................................... 24
Conclusion............................................................................................................................................................... 27
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 are using 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 are using computers when 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 needs to be merged into the enterprise databases. Similarly, any changes made to the enterprise databases while the user was offline need to 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 document discusses 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.
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 amount of 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 the following 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
Ask yourself the following questions when evaluating database applications for mobile clients.
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
· 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
· 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?
Microsoft SQL Server 7.0 will be a full-featured, enterprise-class RDBMS that will run 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 will offer for mobile-applications development is that applications written for SQL Server run unchanged from Windows 9x laptops to Windows NT Server, Enterprise Edition, multiprocessor clusters. SQL Server 7.0 will be 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 will handle 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 the TPC Web site at www.tpc.org/.
Support for Smaller Databases
SQL Server 7.0 will include 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 9x 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 will include 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, will support memory addressing greater than 4 gigabytes (GB) with Alpha processor-based systems running the Windows NT Server 5.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 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 machine, about the same as for SQL Server 7.0. Because mobile clients are running all the code on the client, Windows 9x-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 will offer three types of replication:
· Snapshot
The replication agent will copy 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 will be replicated to another computer.
· Merge
Updates on any computer will be 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 will use 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.
Once 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 will allow 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 will allow you pass parameters to publications, which simplifies the process of supporting many subscribers. For example, an administrator may want to pass the user name and computer name to a publication as a parameter so that each sales representative would receive data about their sales region only.
The architecture required to support merge replication may vary significantly depending on the following 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 thought-out 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 would occur so frequently that nobody would have 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 to the mobile client as possible. 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 will use 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 will assure delivery of updates regardless of the communication protocol. For more information about network library support, see your SQL Server documentation.
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 syncronization 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 need to support databases from a wide range of vendors. SQL Server 7.0 will support replication to and from heterogeneous data sources using ODBC or OLE DB. For example, Microsoft Access 2000 will support bidirectional merge replication natively. SQL Server 7.0 will support 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 your SQL Server documentation.
SQL Server 7.0 will include a SQL Merge control object that manages heterogeneous replication. The SQL Merge control object will provide the functionality of the Merge Agent and will be 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 diagram illustrates a heterogeneous replication scenario. In this example, a heterogeneous Publisher is configured to send and receive updates to a central SQL Server database. The central SQL Server Publisher then synchronizes changes to the three client Subscribers. All 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 the appropriate architecture and best-of-breed tools is key 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 to fully embrace and integrate 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 the Windows DNA Web site at www.microsoft.com/dna.
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 also can 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, both on 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 need to 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 downloaded from the Windows 95 and Windows NT Workstation Web sites. Unattended installation of PWS is supported. Applications written for Windows NT Server’s built-in Web server, Internet Information Services (IIS) version 4.0, should run unchanged on PWS as long as there are not any 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 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 want to 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 may want to 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. The following tools can help you migrate existing applications.
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, visit the SQL Server Web site at www.microsoft.com/sql.
Microsoft SQL Server 7.0 Data Transformation Services (DTS) will dynamically import or export 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 not only move 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, visit the SQL Server Web site at www.microsoft.com/sql.
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 may 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 the SMS Web site at www.microsoft.com/smsmgmt/.
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 will do 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 and third-party server products. MMC, which is supported on both the Windows 9x 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 Scripting 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 will be included with SQL Server 7.0, Microsoft Developers Network (MSDN), and 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 will offer 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 will allow 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 could 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 will provide appropriate notification by pager or e-mail regarding the success or failure of a particular task. SQL Server also will notify operators when an unexpected error occurs on the server, or will start a program that can take corrective action.
These features will 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 to perform similar tasks than SQL-DMO. 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 will support Windows NT-based users and groups and also will support SQL Server users and roles.
By improving integration with Windows NT, SQL Server 7.0 will provide increased flexibility. Database permissions can be assigned directly to Windows NT users or groups. And you can define SQL Server roles to include not only Windows NT users and groups but also 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 will pass through two stages of security 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 needs permissions to access databases on the server, which is done 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 your 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, such 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 they receive the appropriate data only. SQL Server simplifies this process by allowing you to pass the user name and computer name as a parameter to the Publisher. This means one publication could 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.
Once replication is enabled between the central database and mobile client, you may want to monitor replication activities. SQL Server Enterprise Manager will include a replication monitor that logs, by Subscriber (unless they are anonymous), the success or failure of all replication activities. Since 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 will provide notification by e-mail or pager when problems occur. For information about alert messages, see your SQL Server documentation.
Sybase Merge Replication
Sybase Adaptive Server Anywhere 6.0 uses a separate module for managing mobile clients called SQL Remote. For each remote user, the database administrator must specify a user name, 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 will execute the initial snapshot automatically.
Conflict Resolution
Replication conflicts should be minimal if the data is partitioned correctly. By assigning ownership of data, you can minimize synchronization conflicts. For example, you may not want to allow sales representatives to change data in other sales representatives’ territories. However, you may want to allow a sales manager to change data in several territories. SQL Server 7.0 will provide a flexible approach to handling conflict resolution by managing conflicts automatically using built-in rules or custom rules.
By default, SQL Server 7.0 will use 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 wins.
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 already 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 will generate maintenance scripts for you.
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* |
Notes |
|
Client Hardware Requirements |
|
|
|
|
IBM PC or compatible |
Yes |
Yes |
|
|
DEC Alpha |
Yes |
No |
|
|
RAM (total system) |
32 MB |
32 MB |
Sybase documentation states from 8 MB through 16 MB. This does not include the operating system. |
|
Disk space (without help/samples) |
66 MB (41 MB if shared components already installed) |
50 MB |
The Microsoft SQL Server figure includes the master system database. |
|
Operating System Platforms |
|
|
Sybase Central, PowerDynamo, and other tools require Windows 9x or Windows NT. |
|
Windows 9x |
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 |
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 |
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 |
|
|
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 |
|
|
Sybase recommends Replication Server for robust replication. |
|
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 |
Yes |
No |
|
|
Wizards |
Yes |
Yes |
|
|
Alert notification |
Yes |
No |
|
* Source: Sybase Adaptive Server Anywhere Reviewer’s Guide
Microsoft SQL Server 7.0 will make it easy to develop and manage applications that have remote or disconnected users. It is a scalable RDBMS that can be installed on a single client computer or a large server. This allows corporate developers and independent software vendors (ISVs) to write a single application that will run on a wide range of hardware.
SQL Server 7.0 is the preferred choice for mobile applications because:
· It will provide a single code base that can scale from a laptop to a high-end enterprise-class server.
· Dynamic self-configuration will eliminate most configuration requirements on the client.
· Applications can run unchanged on platforms that span from laptops to high-end SMP clustered servers.
· By using Microsoft Visual Studio, SQL Server will provide a well-integrated environment for creating and debugging 32-bit and Web-based database applications. Third-party development tools also will support development using SQL Server 7.0.
· Multiple servers can be managed as a single unit, reducing the time it takes to administer and monitor replication between multiple users. The savings in administrative costs can be significant in a large organization.
· It will provide sophisticated, proactive client tools for monitoring replication.
· Custom administration tools can be created easily with SQL-DMO by using Visual Studio and third-party tools.
For more information about Microsoft SQL Server, see the SQL Server Web site at www.microsoft.com/sql/.