Migration from Sybase SQL Server to Microsoft SQL Server is manageable if the appropriate steps are taken. The code base for the two products was the same through their respective 4.2 releases. They still have a similar architecture, although each release of both products introduces new differences. Both databases use Transact-SQL as a common language, although the dialects have diverged since the 4.2 releases.
Sybase must ensure its SQL Server operates the same across the several platforms it supports, including the UNIX, Novell NetWare, DEC Vax, and Windows NT operating systems. Microsoft is committed to making Microsoft SQL Server the best database running on Windows NT. This frees Microsoft to take advantage of any feature available in Windows NT, while Sybase is limited to features it can implement across all of the platforms it supports.
In addition, Microsoft has devoted development resources to building Windows-based graphical tools and other ease-of-use features into Microsoft SQL Server.
The following section lists some of the features introduced in later versions of Microsoft SQL Server and Sybase SQL Server. Because Microsoft SQL Server version 4.21a and Sybase SQL Server version 4.9.2 are similar, the following lists features introduced by Microsoft after version 4.21a and by Sybase after version 4.9.2. Both companies have independently implemented many of the same features, as well as features that are unique to Sybase SQL Server or Microsoft SQL Server. Both SQL Server products have included features such as increased compliance with ANSI SQL and improved support for administering large databases. Sybase sites do not lose these important features when they migrate to Microsoft SQL Server, but they gain new Microsoft SQL Server features such as SQL Mail, scheduled tasks, and increased integration with Microsoft Office.
The only difference between the Sybase and Microsoft versions of SQL Server 4.2 was that Microsoft SQL Server included some features Sybase added in Sybase SQL Server 4.8. Sybase SQL Server 4.9.2 was not significantly different than its version 4.8. There have been two major versions of Sybase after 4.9.2: System 10 and System 11.
Sybase introduced several new features in System 10 including:
The behavior of subqueries was changed so that their results complied with the ANSI SQL rules on subqueries.
System 10 added support for being able to define primary and foreign keys as part of the standard Data Manipulation Language (DML).
System 10 added support only for forward-only ANSI-style cursors.
System 10 introduced a new chained transaction model that supports ANSI-compliant implicit transactions.
System 10 added support for specifying transaction isolation levels.
Dump-and-load operations improved to perform parallel I/O.
System 10 added support to run effectively on computers having multiple CPUs.
System 10 implemented 'logical threads' on Windows NT. These are not as effective as using native Windows NT threads but are required by Sybase's architecture for running on multiple platforms.
System 10 introduced the ability to give users special privileges while retaining their unique logins by assigning them to server roles (sa_role, sso_role, and oper).
System 10 introduced the ability to provide audit traces of actions taken by users.
Sybase introduced additional new features in System 11 including:
The System 11 buffer manager supports user-defined data caches up to 16K or 8 pages.
System 11 can read multiple pages with one read request up to 16K or 8 pages.
System 11 introduced the ability to logically partition tables with nonclustered indexes in an effort to reduce contention among concurrent tasks.
In System 11, each user gets a private syslogs cache, which improves logging performance.
Optimizer hints allow users to specify hints in their SQL statements that direct the server to use particular indexes, a particular size of prefetch buffer, or a page-aging algorithm.
The housekeeper task is similar to the Microsoft SQL Server lazywriter process. It flushes old, dirty pages (pages containing updates that have not yet been written to disk) from the buffer cache to free the buffers for other processes.
Microsoft SQL Server versions 4.2, 4.21, and 4.21a were all similar and largely compatible with Sybase SQL Server versions up to 4.9.2. In the 4.2x versions of SQL Server, Microsoft had laid the foundations of integrating the DBMS engine with Windows NT, taking full advantage of Windows NT features such as asynchronous I/O and threading, while maintaining external compatibility with the OS/2 version of SQL Server 4.2. There have been two major versions of Microsoft SQL Server since 4.21a, SQL Server 6.0 and SQL Server 6.5. In these two releases, Microsoft added new capabilities to the Windows NT-based foundation laid with the 4.2x servers.
These are some of the new features that were introduced with Microsoft SQL Server 6.0:
The behavior of subqueries was changed so that their results complied with the ANSI-SQL rules on subqueries.
SQL Server 6.0 added support for being able to define primary and foreign keys as part of the standard Data Manipulation Language (DML).
SQL Server 6.0 added complete support for all the ANSI-style cursors, including INSENSITIVE and SCROLL cursors, and all fetch options (FIRST, LAST, NEXT, PRIOR, RELATIVE, and ABSOLUTE).
The Microsoft SQL Server native APIs, ODBC, and DB-Library, can build their cursors as server cursors, reducing memory use on the clients and reducing network traffic.
SQL Server 6.0 added support for specifying transaction isolation levels (read uncommitted, read committed, repeatable read, and serializable).
Dump-and-load operations were improved to perform parallel I/O.
SQL Server 6.0 added support for running effectively on computers having multiple CPUs.
SQL Server 6.0 added support for replicating data between servers as a standard component of SQL Server, not as a separately purchased component. Microsoft replication is also easy to administer.
SQL Server 6.0 uses Windows NT components to send alerts driven by system events and to schedule automatic execution of routine maintenance tasks.
SQL Server 6.0 uses the Windows MAPI (Messaging API) to allow Transact-SQL statements to send e-mail. SQL Server also uses MAPI to execute SQL statements embedded in e-mail.
SQL Server 6.0 uses Windows NT trusted connections to eliminate the need for separate database logins, which keeps passwords from being transmitted across the network. SQL Server can also use Windows NT RPC protocols to encrypt all data in SQL Server packets.
SQL Server 6.0 added support for asynchronous, parallel I/O for serial operations, such as table scans and range searches.
SQL-DMF is the infrastructure that supports the management of multiple SQL Servers. SQL-DMF includes SQL Enterprise Manager for easy, graphical administration of SQL Servers sites. SQL-DMF also includes SQL Server Distributed Management Objects (SQL-DMO), a set of COM objects used by SQL Enterprise Manager and callable from user-written COM applications.
SQL Server 6.0 added support for the ANSI-exact numeric data types, decimal and numeric. It also added a new auto-increment identity constraint for numeric data types.
SQL Server 6.0 liberated the packet size from the set size of 512 bytes. The default is now 4K. Servers can be configured with their own default sizes, and each application can set its own packet size.
SQL Server 6.0 added support for the ANSI CASE statement, which simplifies the specification of conditional logic in SQL statements.
SQL Server 6.5 built on the success of SQL Server 6.0 by introducing several new features:
SQL Server 6.5 includes a Database Maintenance Plan Wizard for scheduling core maintenance functions and a Web Wizard for integrating SQL Server with Internet Web pages.
SQL Server 6.5 supports replicating data to non-SQL Server databases through ODBC drivers.
SQL Trace is an easy-to-use tool for administrators to trace the SQL statements in their networks.
SQL Server 6.5 adds a new table option that provides row locking on INSERT statements.
SQL Server 6.5 and MS DTC add support for distributed transactions, which update multiple servers in a single transaction. This is performed with a simple two-phase commit interface that is much simpler than the old DB-Library two-phase commit functions.
Fallback support allows a server to operate as a fallback to a primary. If the primary fails, the fallback server takes over its role.
SQL Server 6.5 supports the Simple Network Management Protocol (SNMP) for integration with network management tools.
Several user options were added to set ANSI-standard behavior. Join syntax and behavior were changed to the ANSI-standard.
SQL Server 6.5 added support of the CUBE and ROLLUP aggregate functions to aid in building reports.
Bound connections allow separate connections to become joined in a single transaction and transaction lock space.