This section addresses three areas that must be considered in a migration from Sybase to Microsoft SQL Server: data and object definitions, Transact-SQL and system stored procedure language changes, and administrative changes. It then discusses how to take advantage of the new features available in Microsoft SQL Server 6.5.
The steps of the migration process are:
The architectures of Microsoft and Sybase SQL Servers are nearly identical, and most of the administrative procedures remain the same in the two systems. Databases are created on devices. The Data Definition Language (DDL) syntax for defining tables, views, stored procedures, defaults, rules, and most other objects has only a few differences between the two systems. Most of the system stored procedures are the same. Space within a database is still managed in allocation units and extents.
Although the architectures of Microsoft and Sybase SQL Servers are similar, this section briefly discusses the major architectural differences that must be addressed as part of the migration from Sybase to Microsoft SQL Server.
Microsoft SQL Server clients can use either the Microsoft DB-Library DLL or an ODBC driver to connect with the server. The following Microsoft-supplied components replace the Sybase client components.
| Platform | DB-Library | ODBC Driver |
| Win32® | Ntwdblib.dll | Sqlsrv32.dll |
| Win16 | Msdblib3.dll/W3dblib.dll | Sqlsrvr.dll |
W3dblib.dll is the old name for the Win16 DB-Library DLL and is common between Microsoft and Sybase SQL Server clients. When practicable, Win16 applications for Microsoft SQL Server should be changed to Msdblib3.dll, which contains the same code as the W3dblib.dll but eliminates possible conflicts with the Sybase version of W3dblib.dll. The Win32 Ntwdblib.dll is specific to Microsoft SQL Server.
Microsoft SQL Server clients should use the appropriate Microsoft Net-Library components.
| Net-Library | Win32 DLL | Win16 DLL |
| TCP/IP Windows Sockets | Dbmssocn.dll | Dbmssoc3.dll |
| Named Pipes | Dbnmpntw.dll | Dbnmp3.dll |
| Multi-Protocol | Dbmsrpcn.dll | Dbmsrpc3.dll |
| Novell SPX/IPX | Dbmsspxn.dll | Dbmsspx3.dll |
| Banyan Vines | Dbmsvinn.dll | Dbmsvin3.dll |
| DECNet | Dbmsdecn.dll | N/A |
| AppleTalk | Dbmsadsn.dll | N/A |
When the Microsoft SQL Server client software is installed on a computer, one of the components installed in the SQL Server program group is the SQL Client Configuration Utility. This utility is used to manage the client-side Net-Libraries. The user can choose which Net-Library to use as the default. The user can also make advanced entries to connect to servers that cannot be reached by simply using a Net-Library and the network name of the server. The advanced entries hold three components:
The label by which the entry will be referenced at connect time. For example, if an advanced entry is created with SERVER=XYZ, then when isql is run with a /Sxyz switch, this is the entry that will be used for the connection.
The name of the Net-Library module to use without the .dll extension. For example, specify dbmsrpcn, not dbmsrpcn.dll.
The network address of the server. If this is a Windows Sockets entry, it can be the port and socket address of the server. If it is a named pipes or multi-protocol entry, it is the network name of the server.
Note that Sybase and Microsoft maintain a 4.2 level of compatibility in their servers for clients running each other's client software. Microsoft servers can host Sybase clients, or Sybase servers can host Microsoft clients, provided they are limited to the features in SQL Server 4.2. They cannot use features introduced in System 10, System 11, SQL Server 6.0, or SQL Server 6.5. These include new data types, new Transact-SQL statements, new ANSI-based statements, or new administrative procedures. Sybase sites planning to migrate to Microsoft SQL Server 6.0 or 6.5 and planning to use 6.0 or 6.5 features must convert the clients to Microsoft client software. Also, the current Microsoft SQL Server ODBC drivers cannot be used with Sybase servers, although ODBC drivers from other vendors can operate with both systems.
Configuring the client Net-Libraries is covered in more detail in the Microsoft SQL Server Administrator's Companion.
Beginning with System 10, Sybase SQL Servers store the system stored procedures in a database named sybsystemprocs. In Microsoft SQL Server, there is no sybsystemprocs database; all of the system stored procedures are stored in the master database. If user sites have scripts installing objects into sybsystemprocs, the scripts should be changed to use the master database.
Microsoft SQL Server versions 6.0 and 6.5 contain a new system database called the msdb database. This database is allocated on two new devices, msdbdata for data and msdblog for the log. The msdb database stores the data needed to support the new alerts, scheduled tasks, and Microsoft's implementation of replication (all of which are discussed later in the paper).
The default size of the master device in Microsoft SQL Server versions 6.0 and 6.5 is 25 MB, the default size of the master database is 17 MB. There is no sybsystemprocs database.
The following table shows the sizes of system devices and databases in the various versions of Microsoft SQL Server and Sybase SQL Server.
| SQL Server version | master device size | master database size | sybsystemprocs device size |
| Sybase 4.9.2 | 14 MB | None | |
| Sybase System 10 | 17 MB | 10 MB | |
| Sybase System 11 | 21 MB* | 16 MB | |
| Microsoft 6.0 | 25 MB | 17 MB | None |
| Microsoft 6.5 | 25 MB | 17 MB | None |
* If the pubs2 sample database is also installed the size of a System 11 master device will be 28 MB.
Sybase master databases have a table sysusermessages that holds user-defined messages. In Microsoft master databases, the user-defined messages are stored in the sysmessages table along with the system messages.
Sybase System 11 introduced the concept of user-defined data caches. These caches allow each user to create an area of data cache that can be reserved for specific objects. Binding a specific object to a particular user-defined data cache prevents other objects from flushing the object's data pages from the cache.
System 11 data caches can be subdivided into pools of 2K, 4K, 8K, or 16K. The larger pools provide the basis for large I/Os, which are reads that read 2, 4, or 8 pages at a time.
Microsoft SQL Server 6.5 provides a pintable option on sp_tableoptions that prevents a table's pages from being flushed from the data cache. However, Microsoft SQL Server does not support user-defined data caches. See sp_tableoptions in this paper for more details.
System 11 databases contain a table syslogshold, which records the oldest open transaction. In Microsoft SQL Server versions 6.0 and 6.5 the DBCC OPENTRAN statement performs the same function. All references to syslogshold need to be replaced with DBCC OPENTRAN logic.
The Microsoft SQL Server Setup program automatically installs the DB-Library DLL and the Microsoft SQL Server ODBC driver, because Microsoft considers ODBC to be one of the native SQL Server APIs. Sybase installs only the CT-Library driver, which is its DB-Library equivalent.
SQL Executive is a separate process that runs as a Windows NT service on the server with SQL Server. It is the process that manages Microsoft SQL Server's replication, alerts, and scheduled tasks.
The Microsoft Distributed Transaction Coordinator (MS DTC) is a service that allows SQL Server applications to distribute transactions across multiple servers. It includes a simple API for managing transactions using two-phase commits that are easier to use than the two-phase commit functions originally implemented in DB-Library.
Moving data from the Sybase server to Microsoft SQL Server requires that the objects containing that data be checked first to ensure that their definitions will work correctly in Microsoft SQL Server.
All sites should first migrate their Sybase databases to a test Microsoft database and fully test their applications and administrative procedures against the test Microsoft database. This should include stress testing, if possible, before transferring the entire production system over to Microsoft SQL Server.
Sybase sites must review their SQL statements and the names of SQL Server objects for keyword conflicts, then they can transfer their objects and data to Microsoft SQL Server.
The first check is to ensure that none of the objects in your database has a name that conflicts with keywords in SQL Server.
The following Sybase System 11 keywords are not keywords in Microsoft SQL Server 6.5. Any statements using these keywords must be changed to work in a Microsoft environment.
| 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 |
SQL Server 6.5 Keywords not in System 11
The following Microsoft SQL Server 6.5 keywords are not listed as keywords in the Sybase System 11 documents. Objects containing these names in a Sybase database must be renamed before they can be moved to a Microsoft SQL Server, or the site must verify that all references to the objects use quoted identifiers.
| CASE | COALESCE | COMMITTED |
| CROSS | CURRENT_DATE | CURRENT_TIME |
| CURRENT_TIMESTAMP | CURRENT_USER | DISTRIBUTED |
| DROP | FLOPPY | FULL |
| IDENTITY | IDENTITYCOL | INNER |
| INSENSITIVE | JOIN | LEFT |
| INSENSITIVE | NOCHECK | NULLIF |
| OUTER | PIPE | REPEATABLE |
| REPLICATION | RESTRICT | RIGHT |
| SCROLL | SERIALIZABLE | SESSION_USER |
| SYSTEM_USER | TAPE | THEN |
| UNCOMMITTED | UPDATETEXT | WHEN |
Two processes can be used to transfer objects and data from a Sybase SQL Server to a Microsoft SQL Server. The most user-friendly tool to use is the Transfer Manager component of SQL Enterprise Manager; the other method is to use SQL scripts to transfer the object definitions and the bcp utility to transfer the data.
The Transfer Manager component of SQL Enterprise Manager was built to allow easy transfer of databases or specific objects in databases from one Microsoft SQL Server to another. It addresses moving data when DUMP/LOAD cannot be used because SQL Servers may have different sort orders, different code pages, or residing on different hardware platforms (Intel, Alpha, PowerPC, or MIPS). It was also written to support Sybase SQL Servers as the source of a transfer. To transfer a Sybase database to a Microsoft SQL Server:
Before starting the transfer, check the names of all the objects (tables, columns, views, and so on) against the Microsoft SQL Server list of keywords that are not keywords in Sybase SQL Servers. Objects whose names are Microsoft keywords can still be transferred by using the Quoted Identifier option. After the object has been transferred, decide if the old name should be retained; this forces users trying to access the object to always use SET QUOTED_IDENTIFIERS ON. Consider recreating the object to eliminate the keyword conflict and changing all programs and Transact-SQL scripts to reflect the new name. A table or one of a table's columns can be renamed by selecting the table into a table with a new name, verifying the data in the new table, dropping the old table, and recreating all indexes on the table. If the table name does not have to be changed, use sp_rename to change the new table name to the old table name after dropping the old table.
Transfer Manager does not convert extended characters if the database is using them. Extended characters are the characters in a code page that evaluate to a value larger than 128 and represent characters such as European accented letters and other special characters.
Sybase sites that already have SQL scripts to create a database and all of its objects can use the following process instead of using Transfer Manager. Sites without such scripts can generate them by using the Sybase SQL Manager utility.
Care must be taken to ensure that the formats of the tables (number, size, and data types of columns) are the same in the Microsoft SQL Server as they are in the Sybase server. The same bcp options should be used on both the transfer out of Sybase and the transfer in to Microsoft. For more information about bcp, see the Sybase and Microsoft documentation.
This section discusses the differences between the Sybase and Microsoft Transact-SQL dialects and system stored procedures that must be addressed for a successful migration.
The following issues affect Transact-SQL in scripts, applications, triggers, and stored procedures.
Both Sybase and Microsoft SQL Servers support explicit transactions managed with the statements BEGIN TRANSACTION, SAVE TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.
Sybase SQL Server has a ROLLBACK TRIGGER statement that rolls back only the work done by the statement that fired the trigger. Microsoft SQL Server does not support ROLLBACK TRIGGER. The ROLLBACK TRIGGER statements must be replaced with paired SAVE TRANSACTION tr1… ROLLBACK TRANSACTION tr1 statements, which can roll back a single SQL statement without affecting the rest of the transaction. Applications using ROLLBACK TRIGGER should instead issue SAVE TRANSACTION tr1 immediately before executing the statement that fires the trigger, execute the statement that fires the trigger, and then issue the ROLLBACK TRANSACTION tr1 statement (if needed) before executing any other SQL statements.
Sybase System 10 introduced chained transactions, which have implicit starting points but must be explicitly committed. A connection can put itself into or out of a chained transaction state with the SET statement:
SET CHAINED [ON | OFF]
Microsoft SQL Server 6.5 introduced a similar feature called implicit transactions. Microsoft implicit transactions function the same as Sybase chained transactions. Microsoft implicit transactions are also controlled by the SET statement:
SET IMPLICIT_TRANSACTIONS [ON | OFF]
SET CHAINED statements in Sybase applications must be changed to SET IMPLICIT_TRANSACTION statements.
Sybase stored procedures are tagged with the transaction mode (chained or unchained) with which they were created, while Microsoft 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 procedures. Because of this, all Sybase procedures created in chained mode must be scanned for COMMIT TRANSACTION statements that do not have matched BEGIN TRANSACTION statements. Either remove the COMMIT or add a BEGIN TRANSACTION to the procedure before it is migrated to Microsoft.
The Sybase @@tranchain variable indicates the current transaction mode (0=unchained, 1=chained). Microsoft SQL Server has no @@tranchain variable. The 2 bit in the Microsoft variable @@options reports the mode of implicit_transactions:
IF (@@options & 2) > 0
PRINT 'Implicit_transactions on'
ELSE
PRINT 'Implicit_transactions off'
The Sybase variable @@transtate indicates whether a transaction is in-progress, successful, or aborted. Microsoft SQL Server has no @@transtate variable. Replace @@transtate logic with either @@error checking or SET XACT_ABORT ON for the Microsoft SQL Server to automatically roll back a transaction when an error occurs.
Microsoft SQL Server has no equivalent for the sp_procxmode system stored procedure, which Sybase sites use to control the transaction modes of stored procedures. Sybase procedures must have COMMIT statements matched with BEGIN TRANSACTION statements or be removed from the stored procedures.
While Sybase identifies its transaction isolation levels with numbers, Microsoft identifies the levels with character tags. Scan for SET TRANSACTION ISOLATION LEVEL statements and change the Sybase level specifications to Microsoft specifications.
| Sybase | Microsoft |
| 0 | READ UNCOMMITTED |
| 1 | READ COMMITTED |
| 3 | REPEATABLE READ or SERIALIZABLE |
Microsoft's current implementation of REPEATABLE READ is the same as the implementation of SERIALIZABLE. Sybase's use of the number 3 corresponds with the SERIALIZABLE label.
This section will discuss differences in cursors that must be addressed for a successful migration. The new cursor scrolling options offered in Microsoft's cursor implementation, which sites can start using after migrating, are discussed in "Using New Microsoft Features."
Microsoft SQL Server supports the Sybase cursor statements, except for a minor difference in the DEALLCATE CURSOR statement. In Microsoft SQL Server, the keyword CURSOR is not used with the DEALLOCATE cursor statement.
Sybase: DEALLOCATE CURSOR cursor_name
Microsoft: DEALLOCATE cursor_name
Error checking is different in the two SQL Server implementations. Sybase cursors report errors through @@sqlstatus, while 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 will not allow different stored procedures that are executed by the same connection 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.
Sybase SQL Server supports index optimizer hints based on either index name or index ID:
SELECT * FROM MyTable (index Idx1)
or
SELECT * FROM MyTable (1)
Microsoft SQL Server uses a similar format. The only difference is that Microsoft SQL Server requires an equal sign (=) when using index names:
SELECT * FROM MyTable (index = Idx1)
Microsoft SQL Server treats the NOHOLDLOCK, HOLDLOCK, and SHARED optimizer hints in the same way as Sybase SQL Server. Microsoft SQL Server also introduces new locking optimizer hints, which are discussed in "Using New Microsoft Features."
Microsoft SQL Server does not support the System 11 hints PREFETCH, LRU, or MRU. These must be removed. Microsoft SQL Server automatically uses READ AHEAD processing when it is appropriate. This behavior can be tailored with new RA options on sp_configure, which is discussed in "System Stored Procedures."
Microsoft SQL Server does not support the Sybase server roles of sa_role, sso_role, or oper. GRANT and REVOKE statements referencing these roles must be removed. In a Microsoft SQL Server system the SA login has the functions for the Sybase sa_role and sso_role. By using the GRANT statement, individual users can be given permissions to do the operator actions of dumping databases and transactions but cannot be given permissions to load databases and transactions.
Also, the Sybase function proc_name, which validates a user's name, is not supported and must be removed.
Microsoft SQL Server limits the names of temporary tables to 20 bytes, including the number sign (#). Sybase allows up to 30 bytes in a temporary table name.
The Sybase version of RAISERROR allows argument substitution in any order, but the arguments must be varchar or char. Microsoft's version of RAISERROR requires positional argument substitution, like C's printf, but supports integer and string substitution: %d, %i, %s. Microsoft's RAISERROR statement also supports the specification of a severity level (range 1 through 25). Microsoft's RAISERROR adds a WITH LOG parameter, which tells the server to put 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, while the Microsoft version does not. The easiest 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
Microsoft SQL Server does not support the keyword PARTITION on the ALTER TABLE statement. In System 11, partitions are supported only on user tables that do not have clustered indexes. Partitioned tables reduce lock contention on inserts by providing multiple page chains. A user can insert into one partition without blocking another user concurrently inserting into another partition. This new System 11 feature alleviates the blocking caused by the lack of row-level locking.
Microsoft SQL Server 6.5 supports row locking on inserts, which is another method for reducing insert lock contention:
sp_tableoption MyTable, 'insert row lock', true
The Microsoft row-locking option is available on all table types, not only on user tables without clustered indexes as in System 11 partitioned tables.
Both SQL Server 6.5 and System 10 and 11 support ANSI-compliant NULL behavior. However, the syntax in the two systems is different and must be converted as part of a migration:
Sybase:
SET ANSINULL {ON|OFF}
Microsoft SQL Server 6.5:
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}
The Microsoft and Sybase syntax for defining identity columns is the same. 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.
If an identity column is used in an index, it can generate lock contention on the index. In Microsoft SQL Server 6.5, this can be reduced by enabling insert row-level locking on the table by using sp_tableoption.
For more information about identity columns, see "Using New Microsoft Features."
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 two Microsoft options ANSI_NULLS and ANSI_WARNINGS.
The following table lists the Sybase-specific options that do not exist with Microsoft SQL Server and the Microsoft options that most closely match the behavior of the Sybase option. The defined behaviors may not be an exact match, so users must review the Microsoft documentation carefully. The table does not list the options that are similar between the two vendors.
| Sybase option | Microsoft option |
| ANSINULLS | ANSI_NULLS, ANSI_WARNINGS. |
| ANSI_PERMISSIONS | No equivalent. |
| ARITHABORT can take overflow or trunc. 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) | DBCC trace flag 242. |
| FIPSFLAGGER takes ON/OFF | FIPSFLAGGER takes a FIPS level identifier. |
| FLUSHMESSAGE | No equivalent. |
| PREFETCH | See Microsoft READ AHEAD processing. |
| ROLE | No equivalent. |
| SELF_RECURSION | No equivalent. |
| STATISTICS SUBQUERYCACHE | No equivalent (STATS TIME and IO supported). |
| STRING_RTRUNCATION | No equivalent. |
| TABLE COUNT | No equivalent. |
| TRANSACTION ISOLATION LEVEL {0|1|3} | Levels specified with strings (like READ COMMITED). |
Subqueries have non-ANSI behavior in Sybase SQL Server 4.9.2 and Microsoft SQL Server 4.2x versions. For example, subqueries return duplicate rows. System 10 and Microsoft SQL Server versions 6.0 and 6.5 default to ANSI-standard behavior, but the old subquery behavior can be turned on to ease migration. System 10 sites do this with the SET DUP_IN_SUBQUERY ON; Microsoft SQL Servers do it with DBCC TRACEON 242. System 11 only supports ANSI subquery behavior.
The Microsoft and Sybase implementations of the system stored procedures sp_addmessage, sp_dboption, and sp_configure are not the same. Sybase sites must change their use of these stored procedures to match the Microsoft implementation as part of the migration from Sybase SQL Server to Microsoft SQL Server.
In Sybase systems, the range for user-defined message numbers starts at 20000. In Microsoft SQL Server the range starts at 50000. Microsoft also requires a severity to be specified (range is 1 through 25) to support alerts. Microsoft SQL Server stores user messages in master.dbo.sysmessages, while Sybase stores them in master.dbo.sysusermessages.
The following table lists the Sybase specific sp_dboption parameters that do not match Microsoft's sp_dboption parameters and the Microsoft feature that most closely matches the Sybase behavior.
| Sybase | Microsoft |
| ABORT TRAN ON LOG FULL | No equivalent. |
| ALLOW NULLS BY DEFAULT | ANSI NULL DEFAULT. |
| AUTO IDENTITY | No equivalent. |
| DDL IN TRAN | Version 6.5 allows DDL in transactions. |
| IDENTITY IN NONUNIQUE INDEX | No equivalent. |
Sybase SQL Servers require a checkpoint in the affected database after sp_dboption completes, while Microsoft SQL Server 6.0 and 6.5 automatically checkpoint the affected database. Also, Microsoft SQL Server 6.5 allows DDL in transactions without requiring the SA to set any server or database options.
The Sybase and Microsoft versions of sp_configure were similar in the 4.2x releases. The internal architecture of Microsoft SQL Server has changed significantly in the 6.0 and 6.5 versions as a result of Microsoft's effort to tightly integrate the database with Windows NT. Therefore, the post-4.2x sp_configure options are quite different between the two vendors. Detailing all of the differences is beyond the scope of this paper. Sybase DBAs should review the SQL Server documentation for information about the Microsoft tuning options reflected in sp_configure.
Microsoft configuration options can be set using either sp_configure or SQL Enterprise Manager. Also, Microsoft continues to increase the self-tuning capabilities of Microsoft SQL Server, thereby reducing the number of options administrators have to monitor and manage.
Microsoft's sp_configure allows the SA 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. @@options returns a numeric value that records the current option settings. See Microsoft Knowledge Base Article Q156498 "INF: How to Determine the Current Settings for @@options," (MSDN™ Library, Knowledge Base) for a stored procedure that returns a character list of the options recorded by @@options.
The Sybase DUMP and LOAD statements take a FILE parameter for dump devices. The Microsoft SQL Server 6.x versions of the DUMP and LOAD statements do not use a FILE parameter. Instead, they use a DISK parameter.
Microsoft SQL Server does not support the Sybase LISTONLY parameter. The Microsoft HEADERONLY parameter does list information about all dumps in a device, while the Sybase version of HEADERONLY lists only the first dump. So LOAD statements using the LISTONLY parameter should be changed to use the HEADERONLY parameter.
For striping, remove the STRIPE=n parameter from the Sybase DUMP and LOAD statements and set the sp_configure backup threads parameter to n.
Dumps from Sybase databases cannot be loaded directly into Microsoft databases. If DUMP and LOAD are used to move a database, the target database should be allocated exactly the same in regard to device usage, log allocation, data allocation, and segment allocation as the source database. Failure to allocate the target database the same as the source database can lead to multiple 2558 errors after the load completes.
All logic using the Sybase syslogshold table to determine the oldest outstanding transaction must be replaced with logic that uses the Microsoft DBCC OPENTRAN statement.
The following administrative differences between Sybase and Microsoft SQL Server must be addressed for a successful migration:
Sybase introduced auditing in System 10. Microsoft SQL Server does not have an auditing component. SQL Trace can have filters defined to capture some auditing information. For information about the xp_sqltrace and xp_logevent extended system stored procedures, see the Microsoft SQL Server documentation.
As noted previously, Microsoft SQL Server does not support the Sybase sa_role, sso_role, or oper roles. These functions are part of the Microsoft SA login. Administrative practices based on individual user logins having these Sybase server roles applied must be redesigned.
Microsoft SQL Server does not have a Threshold Manager for managing transaction logs. The equivalent functionality is provided by using the following:
Use SQL Enterprise Manager to set up a system of periodic, scheduled dumps.
Use the SQL Alerter utility to set up Windows NT Performance Monitor alerts to dump transaction logs when they fill to a specified percentage.
Sites running the 4.x versions of Microsoft or Sybase SQL Server rebuild their master databases using the bldmstr utility. In Systems 10 and 11, the sybinit utility is used to rebuild the master database. In Microsoft SQL Server versions 6.0 and 6.5, the Setup program rebuilds the master database. Setup has an option to rewrite the master database and reset the configuration values to their defaults. This process can also be used to change the code page and sort order of a server, although the databases would have to be recreated and the data reloaded.
Microsoft SQL Server contains replication as a standard part of the product; it is not an add-on component. Microsoft replication is easy to manage and based in a publication/subscription model. One server publishes data in its tables to make it available for replication. Other servers can then subscribe to that publication. Sites should review Microsoft's implementation of replication to determine how it will support their replication needs.
The replication is performed by several processes that run as part of the SQL Executive, which runs as a Windows NT service on the same server as SQL Server. The SQL Executive contains three replication processes:
Replication is administered from the SQL Enterprise Manager graphical administration tool. Microsoft replication also replicates to ODBC data sources, such as DB2 or Oracle. For more information, see the Microsoft SQL Server Administrator's Companion.
Microsoft SQL Server offers new features not found in Sybase SQL Server. Sites should investigate these capabilities and determine which ones to incorporate into their new systems.
The following new Transact-SQL features can be used by Sybase sites after they migrate to Microsoft SQL Server.
Microsoft SQL Server still supports the /* comment text */ syntax for comments. In addition, it accepts comments in the style:
-- comment out everything to the end of the line.
Comments using the -- format can also be nested.
The EXECUTE statement now supports executing strings:
USE master
GO
DECLARE @DBName VARCHAR(30)
DECLARE DBCursor CURSOR FOR
SELECT name FROM sysdatabases ORDER BY name
OPEN DBCursor
FETCH DBCursor INTO @DBName
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE ('DBCC CHECKDB(' + @DBName + ')' )
FETCH DBCursor INTO @DBName
END
GO
The executed string can also be a text parameter, so users are not limited to executing only 255-byte statements. Syntax checking and object verification of the executed string are not performed until execution time.
The CASE statement can be used for assignment and lets users simplify the conditional logic in SQL expressions. It is ANSI SQL-92 compliant and is allowed anywhere an expression is used:
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
"Shortened Title" = CONVERT(varchar(30), title),
Price = price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, title
COMPUTE AVG(price) BY type
go
CASE can be used to replace nested IF statements with code that is easier to understand and maintain.
Microsoft Transact-SQL supports scrollable cursors:
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR...
The behaviors of cursor options are discussed in detail in Microsoft Knowledge Base Article Q156489 " INF: Overview of SQL Server, ODBC, and DB-Library Cursors" (MSDN Libarary, Knowledge Base). To summarize:
The cursor is implemented as a forward-only and read-only cursor. The cursor is instantiated as a temporary table at cursor open; no changes that affect the rows in the cursor are reflected in the cursor unless it is closed and reopened.
Similar to the INSENSITIVE cursor except that the resulting cursor is also scrollable.
The cursor is implemented as a keyset cursor. A set of the keys for all the qualified rows is built when the cursor is opened. As the user scrolls around the cursor, commited updates to nonkey columns are reflected. Inserts made while the user is scrolling the cursor are not visible. Deleted rows return an @@fetch_status of –2. Updates of key columns appear as a delete of the row with the old key value (@@fetch_status = –2) followed by an insert of a row with the new key value (which is not visible to the cursor).
Microsoft SQL Server also supports all of the FETCH options: FIRST, LAST, NEXT, PRIOR, RELATIVE, and ABSOLUTE, where the RELATIVE(n) options asks for the row n rows from the current cursor position and ABSOLUTE(n) asks for the nth row in the result set. Some of these options are supported only with SCROLL cursors.
The default cursors offer the most consistency because they reflect all committed changes as the user scrolls through the cursor. INSENSITIVE cursors offer the greatest concurrency because they maintain no locks on the underlying tables.
Microsoft SQL Server continues to support the original SQL Server specific syntax of *= and =* syntax for specifying outer joins and now it also supports the ANSI-style join syntax with the operators {INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]}. The ANSI join operators also follow the ANSI definitions for join behavior. Using the ANSI-style join operators is recommended rather than using the SQL Server *= and =* syntax. The ANSI-style and SQL Server-style join syntax cannot be mixed in the same SQL statement.
Microsoft SQL Server supports the following new optimizer hints:
Uses update locks instead of the default shared locks and holds them until the end of the transaction.
Puts a shared lock on the table and holds it until the end of a transaction if HOLDLOCK is also specified.
Puts an exclusive lock on the table and holds it until the end of a transaction if HOLDLOCK is also specified.
Takes shared page locks when the server would normally take a table lock.
Forces the optimizer to use a nonclustered index that covers an ORDER BY, instead of doing a table scan with the sorted work table. With the new read-ahead processing, the table scan and work table can be faster overall, but the first row cannot be returned until the result set has been built. FASTFIRSTROW speeds the return of the first row by forcing the optimizer to use the nonclustered index; however, overall performance can be reduced.
The UPDLOCK hint can help Sybase applications that have high levels of deadlocking. If several users are doing concurrent processing such as the following, there is a potential to deadlock:
Task1 can acquire shared locks at step 2, then Task2 can acquire another set of shared locks at step 2 before Task1 has a chance to start step 3. When Task1 attempts to update at step 3, it blocks on Task2's shared locks, and when Task2 attempts to update at step 3, it blocks on Task1's shared locks. If the HOLDLOCK keyword is changed to UPDLOCK, then Task2 is blocked at step 2 until Task1 commits its transaction, thereby eliminating the possibility of a deadlock.
Microsoft SQL Server 6.5 supports both types of temporary tables supported by Sybase:
Name starts with the number sign (#), visible only to the connection creating the table. Dropped when they are explicitly dropped, the connection terminates, or when the stored procedure terminates (If they were created by a stored procedure).
Created in tempdb with standard table name:
USE TEMPDB
GO
CREATE TABLE MyTable (cola int, colb char(8))
GO
GRANT ALL ON MyTable TO public
GO
Table can be referenced by all users connected to the server. It will be dropped if the server is recycled, unless it is first dropped explicitly by the owner. Connections currently in a user database must fully qualify the table in order to reference it.
Microsoft SQL Server also supports a third type of temporary table called global temporary tables. These tables are identified by names beginning with double number signs (##). Global temporary tables are visible to all users on the system. They persist until either explicitly dropped or all connections referencing them disconnect.
Sybase SQL Server supports only the IDENTITY constraint on numeric columns with a scale of 0. In addition to supporting the IDENTITY property on numeric columns, Microsoft SQL Server also supports the IDENTITY property on decimal columns with a scale of 0 and on integer, smallint, and tinyint columns.
Microsoft SQL Server does not limit IDENTITY columns to starting with 1 and incrementing by 1. The constraint can be specified as:
IDENTITY (seed, increment)
where
seed
Is the value generated for the first row inserted
increment
Is the value by which each new row's identity value is incremented
The default for seed and increment are 1. The new identity function IDENT_SEED reports a columns seed value, and IDENT_INCR reports its increment value. Microsoft also introduced a new global variable, @@identity, which records the last identity value assigned.
Microsoft SQL Server 6.5 users can easily set the behavior of their connection to be ANSI compliant by executing SET ANSI_DEFAULTS ON. This statement sets ON eight different ANSI options. The Microsoft SQL Server ODBC driver automatically sets ANSI_DEFAULTS ON if it detects it is connecting to SQL Server version 6.5.
The SELECT statement on Microsoft SQL Server supports the WITH CUBE and WITH ROLLUP aggregate operators. These operators aid in the generation of reports.
The INSERT and UPDATE statements have been expanded to include the ability to place default values in all the columns or some of the columns in a table:
INSERT INTO MyTable DEFAULT VALUES
INSERT INTO MyTable VALUES ('abc', DEFAULT, 100.02)
UPDATE MyTable SET cola = DEFAULT WHERE colb = 'abc'
If no default has been defined for a column and the columns accept NULL, NULL is used. If the column is a timestamp column, the next timestamp value is used.
The DBCC statement offers the following new options:
Reduces the size of a database.
Displays the connection's current settings for the DBCC trace flags.
Displays the link list for a table so fragmentation can be assessed.
This section discusses new options in the Microsoft SQL Server system stored procedures.
sp_configure offers several new options.
SQL Server's use of read-ahead processing on table scans and range searches can be controlled by the following parameters:
The number of cache hits a read-ahead request can have before it is cancelled.
The number of cache misses that occur during a horizontal traversal before read-ahead is triggered.
The delay for the read-ahead process, in milliseconds.
Determines how far ahead the read-ahead process will read before idling.
The number of concurrent requests the read-ahead service will manage.
The number of threads used to service read-ahead requests.
sp_tableoption allows users to activate insert row-level locking for a table and also "pin" the table or make its pages stay in memory.
Insert row locking can alleviate page-lock contention in cases where the structure of a table directs all inserts to a single page. In a table with no clustered index, all inserts are made to the last page. In a table with an index built over an identity column, the key inserts are directed to the last page of the index. Insert row locking can alleviate the lock contention these structures cause.
SQL Server reads table pages from disk to internal memory cache. If SQL Server needs memory for new data, it can flush pages already in the cache to make room for new data. If an administrator uses sp_tableoption to mark a table as a pin table, the table's pages in the cache are "pinned" and are not eligible to be flushed. This can increase performance for tables that are referenced frequently, but it should be used with caution. Marking too many tables as pin tables can adversely affect SQL Server's ability to effectively manage memory. Pinning does not affect writing the data page back out to disk if it has been modified, so it has no impact on recovery.
The following are new administrative features Sybase sites can take advantage of after migrating to Microsoft SQL Server.
Microsoft SQL Server 6.5 is not limited to dumping databases and transaction logs; it supports dumping and loading individual tables. SQL Server 6.5 also allows loading a transaction log to a specific point in time. For example, if an administrator finds that a user did something wrong with the data at 3:00 p.m. on Thursday, he or she can reload the database and recover it to that point.
If administrators need to be able to recover individual tables, they are no longer required to define a separate database for each table with all of the administrative overhead that requires. They can keep logically related tables in one database where they can be managed as a unit but still have the flexibility of loading specific tables if the need arises.
Tables with text and image columns are not eligible for table loads and table loads cannot be run to a point in time.
Microsoft SQL Server 6.x also supports dumping to various media: local dump devices, tapes, floppy drives, a named pipe, and network dump devices. It supports the dismount/nodismount, unload/load, init/noinit, and retaindays parameters. It also introduces a new expiredate parameter, which has the same effect as retaindays but a target date can be specified instead of the number of days a dump is to be retained.
Microsoft SQL Server does not have a separate Backup Server process.
All logic using the syslogshold table to determine the oldest outstanding transaction must be replaced with DBCC OPENTRAN logic.
SQL Server logins can be integrated with Windows NT user accounts with the following benefits:
SQL Server login accounts based on Windows NT user IDs can be created. After logging onto Windows NT, the user can then make a trusted connection to SQL Server without having to specify a separate SQL Server login and password.
The user's login is validated once by Windows NT at login. There is no need to send a password across the network to SQL Server for database login validation.
When a user connects to SQL Server with integrated security, the user's Windows NT domain is stored in master.dbo.sysprocesses.nt_domain, and the user's Windows NT account ID is stored in master.dbo.sysprocesses.nt_username. These columns can be referenced in views and triggers for data access validation.
Microsoft SQL Server still supports standard Sybase login security. It can also run with mixed security where some users connect with standard SQL Server logins while others connect through Windows NT Integrated Security.
SQL Server can be instructed to execute specific stored procedures each time SQL Server is started. This function can be used to load startup data or execute procedures to ensure their execution plans are loaded into the procedure cache. Auto-execution procedures are controlled through:
Makes a procedure a startup procedure.
Removes a procedure from the startup list.
Lists the current startup procedures.
Vendors of applications running on SQL Server often encode some of their system's logic in stored procedures. In order to protect their proprietary code many of these vendors deleted the text of their stored procedures in the syscomments table during their setup. This loss of the source for the stored procedures can create upgrade problems.
Microsoft SQL Server versions 6.0 and 6.5 offer a better mechanism. The CREATE PROCEDURE statement accepts a WITH ENCRYPTION parameter. When this is specified, the source of the procedure is stored in an encrypted format in syscomments. Casual readers cannot view the source, but it is still available to SQL Server if it is needed for future Microsoft SQL Server upgrades.
The SQL Distributed Management Framework (SQL-DMF) is a group of the SQL Server core components that ease administration of Microsoft SQL Servers. SQL-DMF allows automation of both periodic maintenance tasks and responses to potential problems. Using SQL-DMF, sites can set up the administration tasks for their SQL Servers to allow for "Lights Out" operation with minimal administrative needs.
Administrative procedures based on Transact-SQL statements are also supported, which allows sites to build scripts to manage the administration of their systems. SQL-DMF offers a user-friendly alternative for extending and automating the administration of SQL Servers.
SQL Distributed Management Objects
The programming API for SQL-DMF is SQL Distributed Management Objects (SQL-DMO). SQL-DMO is a collection of OLE Automation objects that define all of the entities present in a database (tables, views, logs, users, and so on), and their typical administrative actions. SQL-DMO is used by the SQL Server administration tools, but any OLE Automation-compliant programming language can also use the SQL-DMO objects to build custom database administration functions. In conjunction with a rapid development environment such as Visual Basic, SQL-DMO can be used to quickly develop robust custom administration applications.
SQL Enterprise Manager
SQL Enterprise Manager is a Win32 graphical tool that uses SQL-DMO to administer the Microsoft SQL Servers on a network. SQL Enterprise Manager can represent all of the SQL Servers on a network (and all of their contained objects, such as users, tables, databases, logs, and indexes) in a tree structure similar to that used for files in Windows Explorer.
SQL Enterprise Manager takes the administration of SQL Servers from a command-prompt environment (isql) to a graphical environment with all of the productivity and ease-of-use that it implies. SQL Enterprise Manager can be used for the traditional administrative tasks of object creation, database dumping and loading, database sizing, and login and user ID management. It can also be used for the new Microsoft SQL Server features such as scheduling tasks, defining alerts, and defining replication.
SQL Enterprise Manager also includes a new Database Maintenance Plan Wizard that can quickly set up a maintenance schedule for a database. This includes checking data integrity and allocation, updating index statistics, and performing dumps and loads.
SQL Executive
SQL Executive is a Windows NT service application that runs with SQL Server. SQL Executive is the component that implements several operations that users can specify with SQL Enterprise Manager and SQL-DMO applications. SQL Executive replaces the SQL Monitor component of SQL Server 4.21a.
Auto-restart of SQL Server
The SQL Executive service can be configured to automatically restart SQL Server if it should stop. This capability can be defined during setup, or later with SQL Enterprise Manager, or in the Services application in Control Panel.
Alerts and Scheduled Tasks
Two primary features of SQL-DMF that sites should explore after migrating to Microsoft SQL Server are the use of alerts and scheduled tasks.
Alerts are raised when specific conditions are encountered, for example, when a transaction log reaches 80 percent of capacity or a severity 19 message is written to the SQL Server error log. An alert can drive an SQL statement, which could be a stored procedure. By building a stored procedure for any feature offered by a Microsoft SQL Server, such as SQL Mail and extended stored procedures, the alerts can drive corrective action without requiring administrative intervention, or they can generate e-mails and pages to administrators.
Scheduled tasks are tasks that are executed at specific times. The tasks administrators schedule are the execution of either an SQL statement or a command-prompt command. For example, an administrator can schedule the execution of a program that has the code to perform DBCC checks of all the databases on a server, then scan the DBCC output for messages about which the administrators should be alerted. All of the periodic maintenance actions for a server can be automated using scheduled tasks.
SQL Server adds several counters to the Windows NT Performance Monitor counters so that administrators can use the same tool to monitor the performance of both Windows NT and SQL Server. In addition, the SQL Alerter utility allows alerts to be defined on the SQL Server counters, so that performance issues can be brought automatically to the administrators' attention. For example, a server can be set up to page and e-mail the administrators any time the number of SQL Server tasks blocked on locks exceeds a specified value. SQL Server 6.5 also supports user-defined SQL Server counters in the Performance Monitor, giving sites greater flexibility in monitoring their systems.
SQL Trace is a graphical utility that can be used to see the SQL statements transmitted from client computers to a SQL Server. With its filtering capabilities, SQL Trace users can focus on any subset of client-to-server connections. SQL Trace filters can also be defined to perform some auditing functions.
Microsoft Distributed Transaction Coordinator (MS DTC) manages distributed transactions between two or more SQL Server systems. MS DTC runs as a Windows NT service enabled through SQL Server's administrative interface. Applications use MS DTC through an OLE-based API to manage explicit distributed transactions. The application uses the MS DTC transaction commands instead of the SQL Server transaction statements and enrolls each connection that it wants to have participating in the distributed transaction. When the application issues a commit to MS DTC, MS DTC transparently manages a two-phase commit protocol with all the SQL Server connections enrolled in the transaction. The application is shielded from the complexities of the old DB-Library two-phase commit functions.
The Microsoft version of Open Data Services is an API that programmers can use to code DLLs to work as extended stored procedures. The DLLs coded as extended stored procedures are called in a manner similar to that of a stored procedure, seamlessly extending SQL Servers functionality. The extended stored procedures can implement any kind of C or C++ code to trigger actions outside of SQL Server and return external information to SQL Server. Extended stored procedures can use return status codes and output parameters, just as regular stored procedures do.
SQL Server uses extended stored procedures to implement some of the functionality unique to Microsoft SQL Server. The names of many of these stored procedures start with xp_ and are used to administer integrated security and SQL Mail. Some special extended stored procedures are:
Submits a command-prompt command to Windows NT:
EXEC xp_cmdshell "net send /domain:SQL_USERS 'SQL Server shutting
down in 10 minutes. No more connections allowed.'", no_outputLogs a user-defined message to the SQL Server error log and optionally to the Windows NT event log:
SELECT @message = 'The table "' @tabname '" is not owned by the user "' @username '."'
EXEC xp_logevent 60000, @message, informationalAllows character substitution in string variables:
DECLARE @string varchar (255)
EXEC xp_sprintf @string OUTPUT, 'INSERT INTO %s VALUES (%s, %s)', 'table1', '1', '2'Reads data from the string into the argument locations given by each format argument:
DECLARE @filename varchar (255)
EXEC xp_sscanf 'sync -b -fauthors10.tmp', 'sync -b -f%s',
@argument1=@filename OUTPUTReturns the version information for SQL Server, along with the version information for the Windows NT running on the server computer with SQL Server.
SQL Mail provides a set of extended stored procedures that can be used to send e-mail or pages from SQL Server through a MAPI (Messaging API)-compliant e-mail system. SQL Mail messages can consist of short text strings, the output of queries, or an attached file. SQL Mail can be called from Transact-SQL, stored procedures, and triggers. It can be used by alerts and scheduled tasks. SQL Mail can also be set up to receive e-mails containing SQL queries. SQL Server will execute the query and send the results back to the client through e-mail.