Microsoft Corporation
1998
Introduction
Why Migrate to Microsoft SQL Server 7.0?
Understanding the Migration Process
Reviewing Architectural Differences
Migrating Tables and Data
Reviewing Sybase T-SQL and Microsoft Transact-SQL Differences
Understanding Database Administration Differences
Migration Checklist
Conclusion
Appendix: System Databases and Data
Summary: Microsoft® SQL Server™ and the Sybase database were developed together until their respective 4.2 versions. This similarity provides a unique, low-cost opportunity for Sybase customers to migrate to Microsoft SQL Server and benefit from the new functionality of Microsoft SQL Server version 7.0. This article is intended for Sybase database administrators (DBAs) and managers who are planning a database application migration to Microsoft SQL Server 7.0. It outlines the steps to take in a migration as well as issues that database application developers should anticipate, based on the experiences of other customers.
Microsoft® SQL Server™ and the Sybase database were developed together until their respective 4.2 versions. This similarity provides for a unique, low-cost opportunity for Sybase customers to migrate to Microsoft SQL Server and benefit from the new functionality of Microsoft SQL Server version 7.0.
This document outlines the steps to take in a migration and helps database application developers anticipate issues, based on the experiences of other customers. This document is intended for Sybase database administrators (DBAs) and managers who are planning a database application migration to Microsoft SQL Server 7.0. It assumes the reader knows Sybase databases (SQL Server or Adaptive Server Enterprise).
This document addresses the differences between Sybase T-SQL and Microsoft Transact-SQL statements, and differences in administrative procedures. Issues regarding conversion from CT-Library applications or the porting of Open DataBase Connectivity (ODBC) applications from Sybase to Microsoft SQL Server databases are not addressed.
Below, you will find seven reasons why you should consider migrating your Sybase application to Microsoft SQL Server 7.0.
Sybase offers different database engines in their different editions. This means that when you write code to one edition, it may not run the same way on another edition. In contrast, Microsoft SQL Server 7.0 is developed from one single codebase that scales from a laptop running the Microsoft Windows® 95/98 to multiprocessor clusters running the Microsoft Windows NT® Server operating system, Enterprise Edition, thereby offering 100 percent application compatibility. Microsoft Data Engine (MSDE) is a new, SQL Server 7.0-compatible data engine that ships with Microsoft Office 2000. With Office 2000, applications can be developed, accessed, and managed using the Microsoft Access 2000 front-end and MSDE as the back-end engine.
You should only have to develop and support one version of your application that runs on different hardware environments. This is especially important if you want to create mobile applications that run the same, whether they are on a laptop, desktop, server, or clustered server.
Microsoft SQL Server 7.0 is easy to use. Included in Microsoft SQL Server 7.0 are graphical tools, task pads, and more than 30 wizards to help DBAs automate and schedule routine tasks. For example, the Index Tuning Wizard helps you determine how to best tune your application, and the graphical SQL Server Query Analyzer helps you visualize the execution plans for database statements and identify performance bottlenecks.
Sybase does not offer the same breadth and depth of tools found in Microsoft SQL Server 7.0 for automating and scheduling tasks, and proactively alerting DBAs if there is any problem.
A common DBA task is monitoring the use of memory and disk space. In some scenarios, such as mobile applications, remote users cannot ask a DBA to tune and monitor the application. Other databases require you to monitor the use of the system carefully to ensure that the application has the amount of memory and disk space it needs. SQL Server 7.0 helps significantly to both reduce serious errors and the amount of errors caused by disk and memory allocation by providing dynamic self-management.
An increasing number of applications run on laptops (mobile applications). Mobile knowledge workers often do not even know what a database is, let alone how to tune a database. Additionally, DBAs must be available during critical periods, such as the close of the fiscal year. Memory and disk-space management should not occupy their valuable time.
Microsoft SQL Server 7.0 provides outstanding performance on the Windows NT platform, and the Beta 3 version of SQL Server 7.0 delivered record-setting results on industry standard benchmarks.
On Windows NT, the Beta 3 version of SQL Server 7.0 holds the record for the highest performance with Baan and PeopleSoft, the highest average backup and restore throughput, and the highest single-node TPC-C performance. It also holds the record for the best TPC-C price/performance in the industry. These records were established on a Beta 3 version of Microsoft SQL Server 7.0 at the SQL Server 7.0 launch on November 16, 1998.
Companies running on systems with 8 CPUs or fewer can enjoy the benefits of SQL Server 7.0, a database that can offer top performance and be very cost-effective.
The TerraServer project is a Microsoft-sponsored Web site that accesses 1 terabyte of satellite images of the world. It has received over 1 billion hits to date, with peak loads of over 29 million hits and almost 18 million database queries in a day.
The following statistics from the first 21 days of operation demonstrate that Microsoft SQL Server 7.0 is capable of providing huge database capacity and outstanding transaction throughput.
SQL Server 7.0 and TerraServer prove that you can host a very large database in a cost-effective manner.
SQL Server 7.0 includes comprehensive data warehousing capabilities, such as integrated data transformation services, Microsoft SQL Server Online Analytical Processing (OLAP) services, and even graphical tools that help you model your data warehouse. SQL Server 7.0 enables easy, cost-effective development of data warehouses, and Microsoft Office 2000 provides easy-to-use capabilities for exploring and analyzing your data. By using complementary products from members of the Microsoft Data Warehousing Alliance, you can integrate even more advanced data warehousing tools that will work seamlessly with your SQL Server-based application. The capabilities of SQL Server 7.0 can benefit your users with data lineage. When data is coming in from many sources (manufacturing databases, sales databases, channel databases), the lineage feature helps identify the origin of the data.
Creating a data warehouse can be a very complex process, and integrated tools make the process of developing your data warehouse more intuitive and efficient. By using the additional tools available from the vendors in the Data Warehousing Alliance, you can be assured that a Microsoft SQL Server solution will meet developer requirements and user needs equally well.
With SQL Server 7.0 and Microsoft Excel 2000, Microsoft offers desktop analysis, enabling Excel users for the first time to analyze gigabytes and terabytes of data with OLAP Services. SQL Server 7.0 integrates with Windows NT security and systems management to provide users greater productivity and ease of use. A single security model offers single-user login when accessing a database. Management tools such as Microsoft Windows NT Event Viewer offer automated alerts; the server can page or e-mail you if there are any database issues. Microsoft Transaction Server, part of Windows NT Server, allows easy building of multitier applications. SQL Server also integrates tightly with Microsoft Exchange Server, Microsoft Systems Management Server, and Microsoft Systems Network Architecture (SNA) Server. All of these features provide ease of use to the administrator.
Your users can be more productive if they can use their favorite data analysis tool as the interface for data warehouses.
Three areas must be considered during a migration from Sybase Adaptive Server Enterprise to Microsoft SQL Server: data and object definitions, Transact-SQL and system stored procedure language changes, and administrative changes.
The steps of the migration process are:
The Data Definition Language (DDL) syntax of Sybase Adaptive Server Enterprise differs from that of Microsoft SQL Server when used to define most database objects. Conversely, Sybase and Microsoft DLLs share many system stored procedure similarities. You must address the major architectural differences between Sybase and Microsoft SQL Server as part of the migration.
Although it is easier to use Microsoft DB-Library to migrate Sybase applications that use CT-Library, in order to take full advantage of Microsoft SQL Server version 7.0 functionality, it is recommended that Microsoft SQL Server-based clients use either an OLE DB provider or an ODBC driver to connect with the server. Microsoft supplies two components, Ntwdblib.dll (DB-Library) and Sqlsrv32.dll (ODBC driver), to replace the Sybase client components.
Microsoft SQL Server-based clients should use the appropriate Microsoft SQL Server Network Library components.
| Net-Library | Win32® DLL |
| TCP/IP Windows Sockets | Dbmssocn.dll |
| Named Pipes | Dbnmpntw.dll |
| Multiprotocol | Dbmsrpcn.dll |
| Novell IPX/SPX | Dbmsspxn.dll |
| Banyan VINES | Dbmsvinn.dll |
| AppleTalk | Dbmsadsn.dll |
One of the Microsoft SQL Server client-side components installed in the SQL Server program group, the SQL Server Client Network Utility, is used to manage the client-side Net-Libraries. The user can choose which Net-Library to use as the default. By using a Net-Library and the network name of the server, the user can make advanced entries to connect to servers that use other protocols. The Advanced Entry dialog box holds three components:
Server Alias
The label by which the entry will be referenced at connect time. For example, if an advanced entry is created with SERVER=XYZ, the server XYZ will be used for the connection when osql is run with a /Sxyz switch.
Network Library
The Microsoft SQL Server Net-Library used by the client to connect to the aliased server. Select the check box that corresponds with the appropriate Net-Library.
Connection Parameters
The network address of the server. For example, if it is a Windows Sockets entry, specify the port and socket address of the server. If it is a Named Pipes or Multiprotocol entry, specify the network name of the server.
Sybase and Microsoft servers are compatible with each other's client software, provided the software is limited to SQL Server 4.2 features. Microsoft servers can host Sybase clients, and Sybase servers can host Microsoft clients. Since version 4.2, the servers diverge with their introduction of new data types, new Transact-SQL statements, new ANSI-based statements, and new administrative procedures. Sybase customers migrating to Microsoft SQL Server 7.0 should convert the client software to use OLE DB providers or ODBC drivers. This software is included with Microsoft SQL Server 7.0 or can be downloaded free from www.microsoft.com/data/default.htm (connect time charges may apply).
For more information about configuring the client Net-Libraries, see the "SQL Server [7.0] Books Online" product documentation. If you want to download the "SQL Server Books Online," they are available at http://msdn.microsoft.com/support/).
The following table provides a quick overview of the implementation of Sybase and Microsoft system databases.
| Database item | Sybase Adaptive Server Enterprise (SQL Server 10+) | Microsoft SQL Server 7.0 | Comments |
| Stored Procedures | Stored in the sybsystemprocs database. | Stored in the master database. | Change references from sybsystemprocs to master. |
| Memory Management | Beginning with System 11, user-defined data caches allow each user to create an area of data cache that can be reserved for specific objects. | Dynamic memory management techniques eliminate the need for administrative configuration of memory. | |
| Information about the oldest open transaction | The syslogshold table records the oldest open transaction. | The DBCC OPENTRAN statement records the oldest open transaction. | Replace all references to syslogshold with DBCC OPENTRAN logic. |
For a full explanation of the Microsoft SQL Server system database structure, see the Appendix at the end of this document.
Migrate your Sybase databases to a test Microsoft database, and fully test your applications and administrative procedures against the test Microsoft database, including stress testing, before you transfer the entire production system to Microsoft SQL Server.
Review your T-SQL statements and the names of SQL Server objects for keyword conflicts before transferring your objects and data to Microsoft SQL Server.
The following table lists Sybase System 11 keywords that will not function in Microsoft SQL Server and keywords that are reserved by Microsoft SQL Server. If your T-SQL statements use any of the listed keywords, replace them with other words before you migrate your Sybase database to Microsoft SQL Server.
| Sybase System 11 keywords | Microsoft SQL Server | reserved keywords |
| ARITH_OVERFLOW AT CHAR_CONVERT ENDTRAN ERRORDATA MAX_ROWS_PER_PAGE NATIONAL NOHOLDLOCK NUMERIC_TRANSACTION ONLINE PARTITION REPLACE ROLE ROWS SHARED STRIPE SYB_IDENTITY SYB_RESTREE UNPARTITION USER_OPTION USING |
CASE COALESCE COMMITTED CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER DISTRIBUTED DROP FLOPPY FULL IDENTITY IDENTITYCOL INNER INSENSITIVE JOIN |
LEFT NOCHECK NULLIF OUTERPIPE REPEATABLE REPLICATION RESTRICT RIGHT SCROLL SERIALIZABLE SESSION_USER SYSTEM_USER TAPE THEN UNCOMMITTED UPDATETEXT WHEN |
Using DTS enables you to import and export data between multiple heterogeneous sources that use an OLE DB-based architecture, such as Microsoft Excel spreadsheets and flat text files, and to transfer databases and database objects (for example, indexes and stored procedures) between multiple computers running Microsoft SQL Server version 7.0. You can also use DTS to transform data so it can be used more easily to build data warehouses and data marts from an online transaction processing (OLTP) system.
The DTS Import Wizard and DTS Export Wizard enable you to interactively create DTS packages that use OLE DB and ODBC to import, export, validate, and transform heterogeneous data. The wizards also enable you to copy schema and data between relational databases.
Use the DTS Import Wizard to transfer your Sybase data into Microsoft SQL Server in a few steps.
Figure 1. Specify Table Copy or Query dialog box
Figure 2. Select Source Tables dialog box
Figure 3. Step-by-step progress and status of data migration
Some differences between Sybase T-SQL and Microsoft Transact-SQL and system stored procedures must be addressed to ensure a successful migration. The following issues affect Transact-SQL in scripts, applications, triggers, and stored procedures.
Both Sybase and Microsoft support explicit transactions managed with the statements BEGIN TRANSACTION, SAVE, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.
The Sybase ROLLBACK TRIGGER statement rolls back only the work performed by the statement that fired the trigger.
In Microsoft SQL Server version 7.0, you must replace the ROLLBACK TRIGGER statements with paired SAVE TRANSACTION (tr1)… ROLLBACK TRANSACTION (tr1) statements to roll back a single Transact-SQL statement without affecting the rest of the transaction. Sybase applications that currently use ROLLBACK TRIGGER should be changed to issue SAVE TRANSACTION (tr1), fire the trigger, and then issue the ROLLBACK TRANSACTION (tr1) statement if needed before executing any other Transact-SQL statements.
Sybase System 10 introduced chained transactions, which are transactions that have implicit starting points but must be explicitly committed. A connection can put itself in to or out of a chained transaction state with the SET statement:
SET CHAINED [ON | OFF]
Microsoft SQL Server version 6.5 introduced a similar feature called Implicit Transactions that functions in the same way as Sybase chained transactions. Microsoft SQL Server implicit transactions are also controlled by the SET statement:
SET IMPLICIT_TRANSACTIONS [ON | OFF]
Change the SET CHAINED statements in Sybase applications to SET IMPLICIT_TRANSACTION statements for Microsoft SQL Server.
Sybase stored procedures are tagged with the Transaction mode (chained or unchained) with which they were created, while Microsoft SQL Server procedures operate in the Transaction mode that exists when they are executed. Therefore, Sybase procedures can have COMMIT TRANSACTIONS that are not matched with a BEGIN TRANSACTION statement; this is not allowed in Microsoft SQL Server procedures. Scan all Sybase procedures created in chained mode for COMMIT TRANSACTION statements that do not have matched BEGIN TRANSACTION statements. Either remove the COMMIT TRANSACTION from or add a BEGIN TRANSACTION to the procedure before it is migrated to Microsoft SQL Server.
The Sybase @@tranchain variable, indicating the current Transaction mode (0=unchained, 1=chained), has no Microsoft SQL Server equivalent. The 2 bits in the Microsoft SQL Server variable @@options report the mode of implicit_transactions:
IF (@@options & 2) > 0
PRINT 'Implicit_transactions on'
ELSE
PRINT 'Implicit_transactions off'
The Sybase @@transtate variable, indicating whether a transaction is in progress, successful, or canceled, has no Microsoft SQL Server equivalent. Replace @@transtate logic with either @@error checking or SET XACT_ABORT ON to enable Microsoft SQL Server to roll back a transaction automatically when an error occurs.
The Sybase system stored procedure sp_procxmode, used to control the Transaction modes of stored procedures, has no Microsoft SQL Server equivalent. Sybase procedures must have COMMIT statements matched with BEGIN TRANSACTION statements or be removed from the stored procedures before migrating to Microsoft SQL Server.
Sybase identifies its transaction isolation levels with numbers, and Microsoft SQL Server identifies the levels with character tags. Scan for SET TRANSACTION ISOLATION LEVEL statements, and change the Sybase-level specifications to Microsoft specifications.
Note Microsoft SQL Server 7.0 implements REPEATABLE READ in the same way as SERIALIZABLE.
| Sybase | Microsoft SQL Server |
| 0 | READ UNCOMMITTED |
| 1 | READ COMMITTED |
| 2 | REPEATABLE READ |
| 3 | SERIALIZABLE |
Microsoft SQL Server supports the Sybase cursor statements except for a minor difference in syntax for the DEALLOCATE CURSOR; the keyword CURSOR is not used by SQL Server with the DEALLOCATE cursor statement.
Sybase: DEALLOCATE CURSOR cursor_name
Microsoft: DEALLOCATE cursor_name
Error checking is implemented differently by Sybase and Microsoft SQL Server. Sybase cursors report errors through @@sqlstatus, whereas Microsoft reports errors through @@fetch_status. In addition, Microsoft and Sybase report different values.
| Sybase @@sqlstatus | Microsoft @@fetch_status |
| -2 = Row deleted from result set | |
| -1 = End of result set | |
| 0 = Success |
0 = Success |
| 1 = Type mismatch | |
| 2 = End of result set |
Sybase allows different stored procedures to open cursors with identical names. Each cursor with the same name gets a separate result set. Microsoft SQL Server considers the scope of a cursor name to be the current session. The server does not allow different stored procedures that are executed by the same connection to open cursors with duplicate names.
Microsoft SQL Server cursors default to optimistic concurrency control, which does not place shared locks on tables. Sybase cursors generally default to pessimistic concurrency control, which places shared locks on the underlying tables. The pessimistic concurrency can reduce concurrency in high-use environments.
Optimizer hints are important in Sybase implementations because Sybase does not update index statistics automatically. The Sybase query optimizer is not always reliable because it will often be optimizing based on outdated statistics.
Microsoft SQL Server 7.0 updates statistics automatically, so the Microsoft query optimizer is more likely than the Sybase query optimizer to make the best choice of index use. In addition, the graphical SQL Server Query Analyzer helps programmers and DBAs determine the system I/O bottlenecks. The automatically updated statistics, the accurate query optimizer, and the ability to troubleshoot using the graphical SQL Server Query Analyzer are all reasons to delete Sybase optimizer hints from the statements, not simply replace them. For more information about implementing optimizer hints, see the "SQL Server [7.0] Books Online" product documentation. If you want to download the "SQL Server Books Online," they are available at http://msdn.microsoft.com/support/.
A range of table-level locking hints can be specified by using the SELECT, INSERT, UPDATE, and DELETE statements to direct Microsoft SQL Server to the type of locks to be used. Table-level locking hints can be used when you need a finer control of the types of locks acquired on an object. These locking hints override the current transaction isolation level for the session.
The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default-locking behavior only when absolutely necessary. Disallowing a locking level can adversely affect concurrency.
To implement optimizer hints for locking manually, you must remove the System 11 hints PREFETCH, LRU, or MRU because Microsoft SQL Server does not support them. Microsoft SQL Server automatically uses READ AHEAD (RA) processing when it is appropriate. This behavior can be tailored with new RA options on sp_configure, discussed later in this document in "System Stored Procedures."
For more information about locking hints, see the "SQL Server [7.0] Books Online" product documentation. If you want to download the "SQL Server Books Online," they are available at http://msdn.microsoft.com/support/.
The Sybase server roles of sa_role, sso_role, or oper are not supported by Microsoft SQL Server. GRANT and REVOKE statements referencing these roles must be removed.
In Microsoft SQL Server, the sysadmin role has functions equivalent to the Sybase sa_role and sso_role. By using the GRANT statement, you can give individual users permissions to perform the operator actions of dumping databases and transactions, but you cannot give them permissions to load databases and transactions.
The Sybase function proc_name, which validates a user's name, is not supported by Microsoft SQL Server and must be removed.
The following table describes the fixed server roles in Microsoft SQL Server 7.0.
| Fixed server role | Permission |
| sysadmin | Can perform any activity in SQL Server. |
| serveradmin | Can set server-wide configuration options and shut down the server. |
| setupadmin | Can manage linked servers and startup procedures. |
| securityadmin | Can manage logins and CREATE DATABASE permissions and read error logs. |
| processadmin | Can manage processes running in SQL Server. |
| dbcreator | Can create and alter databases. |
| diskadmin | Can manage disk files. |
You can get a list of the fixed server roles from sp_helpsrvrole, and get the specific permissions for each role from sp_srvrolepermission.
Each database has a set of fixed database roles. Although roles with the same names exist in each database, the scope of an individual role is only within a specific database. For example, if Database1 and Database2 both have user IDs named UserX, adding UserX in Database1 to the db_owner fixed database role for Database1 has no effect on whether UserX in Database2 is a member of the db_owner role for Database2.
The following table describes the fixed database roles in Microsoft SQL Server 7.0.
| Fixed database role | Permission |
| db_owner | Has all permissions in the database. |
| db_accessadmin | Can add or remove user IDs. |
| db_securityadmin | Can manage all permissions, object ownerships, roles, and role memberships. |
| db_ddladmin | Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements. |
| db_backupoperator | Can issue DBCC, CHECKPOINT, and BACKUP statements. |
| db_datareader | Can select all data from any user table in the database. |
| db_datawriter | Can modify any data in any user table in the database. |
| db_denydatareader | Can deny or revoke SELECT permissions on any object. |
| db_denydatawriter | Can deny or revoke INSERT, UPDATE, and DELETE permissions on any object. |
The Sybase version of RAISERROR allows argument substitution in any order, but the arguments must be of data type varchar or char.
The Microsoft SQL Server RAISERROR statement requires positional argument substitution, like the C language's printf, but supports integer and string substitution: %d, %i, %s. This RAISERROR statement also supports the specification of a severity level (range 1 through 25).
The Microsoft RAISERROR statement includes a WITH LOG parameter so that the server will enter the message in the error log. Messages raised with severities from 19 through 25 require the WITH LOG parameter.
The Sybase version of PRINT allows argument substitution; the Microsoft version does not. The most straightforward solution is to change any Sybase PRINT that uses argument substitution to a RAISERROR with a severity of 10 or lower. Another solution is to print a string built of substrings in Microsoft SQL Server:
DECLARE @msg VARCHAR(255)
SELECT @msg = 'The object ' + @tablename + 'does not allow duplicate keys.\n'
PRINT @msg
In Sybase 11+, partitions are supported only on user tables that do not have clustered indexes. This System 11 feature helps to reduce the blocking caused by the lack of row-level locking.
Microsoft SQL Server supports row-level locking on all table types, and does not support the keyword PARTITION on the ALTER TABLE statement.
Setting ANSI NULL Behavior - Both Sybase 11+ and Microsoft SQL Server version 6.5 and later support ANSI-compliant NULL behavior. However, the syntax in the two systems is different.
Sybase:
SET ANSINULL {ON|OFF}
Microsoft SQL Server:
SET ANSI_NULLS {ON|OFF}
SET ANSI_WARNINGS {ON|OFF}
Microsoft SQL Server supports setting options that define whether columns in CREATE TABLE statements take the ANSI NULL defaults:
SET ANSI_NULL_DFLT_ON {ON|OFF}
SET ANSI_NULL_DFLT_OFF {ON|OFF}
IDENTITY Columns - Microsoft and Sybase use the same syntax for defining identity columns. The Microsoft default name for an identity column is IDENTITYCOL; the Sybase default name is SYB_IDENTITY. All references to SYB_IDENTITY must be changed to IDENTITYCOL.
SET Statement - Since their respective 4.2 versions, the SET statement options implemented by both vendors have diverged. Although the new options may have the same or similar names, their defined characteristics can be slightly different. For example, the full effect of the Sybase ANSINULLS option combines behavior defined by the Microsoft options ANSI_NULLS and ANSI_WARNINGS.
The following table lists the Sybase-specific options that do not have exact Microsoft equivalents, and lists the Microsoft options that most closely match the desired Sybase behavior. Review the Microsoft documentation carefully to understand the differences between the Sybase statement and the alternative offered. The table does not list the options that the two vendors share.
| Sybase option | Microsoft option |
| ANSINULLS | ANSI_NULLS, ANSI_WARNINGS |
| ANSI_PERMISSIONS | No equivalent. |
| ARITHABORT can take overflow or truncated options. | ARITHABORT does not support options. |
| ARITHIGNORE can take overflow option. | ARITHIGNORE does not support options. |
| CHAINED | IMPLICIT_TRANSACTION |
| CLOSE ON ENDTRAN | CURSOR_CLOSE_ON_COMMIT |
| CHAR_CONVERT | Set with either ODBC or DB-Library connect options. |
| CURSOR ROWS | No equivalent. |
| DUP_IN_SUBQUERY (System 10 only) | No equivalent. |
| FIPSFLAGGER takes ON/OFF. | FIPSFLAGGER takes a FIPS-level identifier. |
| FLUSHMESSAGE | No equivalent. |
| PREFETCH | See READ AHEAD processing. |
| ROLE | No equivalent. |
| SELF_RECURSION | No equivalent. |
| STATISTICS SUBQUERYCACHE | No equivalent (STATS TIME and I/O supported). |
| STRING_RTRUNCATION | No equivalent. |
| TABLE COUNT | No equivalent. |
| TRANSACTION ISOLATION LEVEL {0|1|3} | Levels specified with strings (like READ COMMITTED). |
Sybase SQL Server 4.9.2 and Microsoft SQL Server 4.2x subqueries are not ANSI-standard; subqueries can return duplicate rows.
Sybase System 10 defaults to ANSI-standard behavior, but the old subquery behavior can be turned on to ease migration. Sybase 10 is backward compatible to the non-ANSI behavior if the SET DUP_IN_SUBQUERY option is turned ON. Sybase 11 and Microsoft SQL Server 7.0 only support ANSI subquery behavior. If you are migrating an application from Sybase 10 and the system uses SET DUP_IN_SUBQUERY ON, you must review the Sybase queries so that they do not cause errors. If you are migrating from a Sybase version previous to 10, you also must review queries that have subqueries.
The Microsoft and Sybase implementations of the system stored procedures sp_addmessage, sp_dboption, and sp_configure are not the same.
In Sybase systems, the range for user-defined message numbers starts at 20,000. In SQL Server, the range starts at 50,000 and also requires a severity to be specified (range is from 1 through 25) to support alerts.
Microsoft SQL Server stores user messages in master.dbo.sysmessages, and Sybase stores them in master.dbo.sysusermessages.
The following table lists the parameters for sp_dboption that are different for Sybase and Microsoft implementations.
| Sybase | Microsoft |
| ABORT TRAN ON LOG FULL | No equivalent. |
| ALLOW NULLS BY DEFAULT | ANSI NULL DEFAULT |
| AUTO IDENTITY | No equivalent. |
| DDL IN TRAN | SQL Server version 7.0 allows DDL in transactions. |
| IDENTITY IN NONUNIQUE INDEX | No equivalent. |
Sybase requires a checkpoint in the affected database after sp_dboption completes, whereas Microsoft SQL Server automatically checkpoints the affected database. Also, Microsoft SQL Server allows DDL in transactions without requiring the system administrator to set any server or database options.
You can manage and optimize Microsoft SQL Server resources through configuration options by using SQL Server Enterprise Manager or the sp_configure system stored procedure. The most commonly used server configuration options are available through SQL Server Enterprise Manager; all configuration options are accessible through sp_configure.
Compared to earlier versions, SQL Server version 7.0 has more internal features for self-tuning and reconfiguring. These features reduce the need to set server configuration options manually. You should consider the effects on your system carefully before setting these options.
The options for the Sybase and Microsoft versions of sp_configure are quite different. Detailing all of the differences is beyond the scope of this document. Sybase DBAs should review the SQL Server documentation for sp_configure options.
sp_configure allows members of the sysadmin fixed server role to set defaults for user options, such as ANSI options, although individual connections can later change the settings. The current state of a connection's settings are made visible to it through a global variable @@options. The variable @@options returns a numeric value that records the current option settings. For more information about a stored procedure that returns a character list of the options recorded by @@options, see Microsoft Knowledge Base Article Q156498 in the MSDN Library.
The DUMP statement is included in Microsoft SQL Server version 7.0 for backward compatibility. It is recommended that the BACKUP statement be used instead of the DUMP statement. In future versions of SQL Server, DUMP will not be supported. For more information about database backup and restore operations, see "Backing Up and Restoring Databases" in the "SQL Server [7.0] Books Online" product documentation. If you want to download the "SQL Server Books Online," they are available at http://msdn.microsoft.com/support/.
Use DTS to perform imports and exports on a regular basis with Microsoft SQL Server.
| DUMP/LOAD statements | Sybase Adaptive Server Enterprise | Microsoft SQL Server 7.0 |
| dump devices | FILE | DISK |
| listing | LISTONLY | Not supported in the same way; closest statement is HEADERONLY. |
| HEADERONLY only lists the first dump. | HEADERONLY lists information about all dumps in a device. | |
| Striping | STRIPE=n | Remove the STRIPE=n parameter from the Sybase DUMP and LOAD statements and set the sp_configure backup threads parameter to n. |
Replace all logic that uses the Sybase syslogshold table to determine the oldest outstanding transaction with logic that uses the Microsoft DBCC OPENTRAN statement.
Microsoft SQL Server 7.0 offers several tools for database administration.
SQL Server Enterprise Manager allows easy enterprise-wide configuration and management of SQL Server and SQL Server objects. SQL Server Enterprise Manager provides a powerful scheduling engine, administrator alert capability, and a built-in replication management interface. You can also use SQL Server Enterprise Manager to:
By default, SQL Server Enterprise Manager is installed by SQL Server Setup as part of the server software on computers running Windows NT, and as part of the client software on computers running Windows NT and Windows 95/98. Because SQL Server Enterprise Manager is a 32-bit application, it cannot be installed on computers running 16-bit operating systems.
SQL Server Profiler is a graphical tool that enables system administrators to monitor engine events in Microsoft SQL Server 7.0. SQL Server Profiler captures a continuous record of server activity in real-time. SQL Server Profiler enables you to monitor events produced through SQL Server, filter events based on user-specified criteria, and direct the trace output to the screen, a file, or a table. Using SQL Server Profiler, you can replay previously captured traces. This tool helps application developers identify transactions that may be deteriorating the performance of an application. This can be very useful when migrating an application from a file-based architecture to a client-server architecture, because the last step involves optimizing the application for its new client-server environment.
Examples of engine events include:
Data about each event can be captured and saved to a file or an SQL Server table for later analysis.
Microsoft SQL Server uses two tools to manage transaction logs in a manner equivalent to the Sybase Threshold Manager.
Sybase 4.x rebuilt their master databases using the bldmstr utility. In later versions of Sybase, the sybinit utility is used to rebuild the master database.
Microsoft SQL Server 7.0 has a Rebuild Master utility that provides the same functionality.
SQL Server Query Analyzer is a graphical query tool that visually enables you to analyze the plan of a query, execute multiple queries simultaneously, view data, and obtain index recommendations. SQL Server Query Analyzer provides the SHOWPLAN option, which is used to report data retrieval methods chosen by the SQL Server query optimizer.
You must make the following changes to your Sybase database and applications before migrating to Microsoft SQL Server 7.0.
Optionally, you can choose to make the following changes to your Sybase database and applications before migrating to Microsoft SQL Server 7.0:
When you plan a migration, you must address changes in administrative procedures, migrating object definitions and data, and resolving differences in Transact-SQL statements and system stored procedures. After you use Microsoft SQL Server DTS to migrate to Microsoft SQL Server 7.0, you can make the necessary changes to your Sybase code.
Microsoft SQL Server systems have four system databases:
The master database records all system-level information for an SQL Server system. It records all login accounts and all system configuration settings. The master database records the existence of all other databases and the location of the primary files that contain the initialization information for the user databases. The master database records the initialization information for SQL Server; always have a recent backup of master available.
The tempdb database holds all temporary tables and temporary stored procedures and fills any other temporary storage needs. The tempdb database is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. The tempdb database is re-created every time SQL Server is started, so the system starts with a clean copy of the database. Because temporary tables and stored procedures are automatically dropped on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.
The tempdb database grows automatically as needed. Each time the system is started, tempdb is reset to its default size. You can avoid the overhead of having tempdb grow automatically by using ALTER TABLE to increase the size of tempdb.
The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb, which uses the model database, is created every time SQL Server is started, the model database must always exist on an SQL Server system.
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and for recording operators.
In SQL Server version 7.0, every database, including the system databases, has its own set of files and does not share those files with other databases. The default location for these files is the C:\Mssql7\Data directory.
| Database file | Physical file name | Default size, typical setup |
| master primary data | Master.mdf | 7.5 MB |
| master log | Mastlog.ldf | 1.0 MB |
| tempdb primary data | Tempdb.mdf | 8.0 MB |
| tempdb log | Templog.ldf | 0.5 MB |
| model primary data | Model.mdf | 0.75 MB |
| model log | Modellog.ldf | 0.75 MB |
| msdb primary data | Msdbdata.mdf | 3.5 MB |
| msdb log | Msdblog.ldf | 0.75 MB |
In SQL Server 7.0, all of the databases have their own set of files that can grow independently of each other.
Each database in Microsoft SQL Server contains system tables that record the data needed by the SQL Server components. The successful operation of SQL Server depends on the integrity of information in the system tables; therefore Microsoft does not support users who directly update the information in the system tables.
Microsoft provides a complete set of administrative tools that allow users to fully administer their system and manage all users and objects in a database. Users can use the administration utilities, such as SQL Server Enterprise Manager, to directly manage the system. Programmers can use the SQL-DMO API to include complete functionality for administering SQL Server in their applications. Programmers building Transact-SQL scripts and stored procedures can use the system stored procedures and Transact-SQL DDL statements to support all administrative functions in their systems.
An important function of SQL-DMO, system-stored procedures, and DDL statements is to shield applications from changes in the system tables. Microsoft sometimes needs to change the system tables in later versions of SQL Server to support new functionality being added in that version. Applications issuing SELECT statements that directly reference system tables are frequently dependent on the earlier format of the system tables. Customers may not be able to upgrade to a later version of SQL Server until they have rewritten applications that are selecting data from system tables. Microsoft considers the system-stored procedures, DDL, and SQL-DMO published interfaces, and seeks to maintain the backward compatibility of these interfaces.
Microsoft does not support triggers defined on the system tables, because they may alter operation of the system.
Another important tool for querying the SQL Server catalog is the set of information schema views. These views comply with the information schema defined in the SQL-92 standard. These views provide applications with a standards-based component for querying the SQL Server catalog.
------------------------------------------------------------------------------------------------
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 efforts 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 document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
Microsoft, BackOffice, Win32, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
Other trademarks and tradenames mentioned herein are the property of their respective owners.
The names of companies, products, people, characters, and/or data mentioned herein are fictitious and are in no way intended to represent any real individual, company, product, or event, unless otherwise noted.