Chapter 20 - Migrating Sybase Applications to MS SQL Server 7.0

Article
01/28/2010

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 chapter outlines the steps to take in a migration and helps database application developers anticipate issues, based on the experiences of other customers. This chapter 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 chapter addresses the differences between Sybase T-SQL and Microsoft Transact-SQL statements, and differences in the applications and administrative procedures. Issues regarding conversion from CT-Library applications or the porting of ODBC applications from Sybase to Microsoft SQL Server databases are not addressed.

Understanding the Migration Process

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:

Review the architectural differences between Microsoft SQL Server and Sybase SQL Server that require changes to administrative procedures.
Migrate data and objects using Microsoft SQL Server Data Transformation Services (DTS).
Review Sybase stored procedures, triggers, SQL scripts, and applications for necessary language changes.
Make the necessary changes to client code. Sybase SQL statements issued by applications must reflect changes to object names forced by keyword conflicts. The Sybase application SQL must reflect any changes required to comply with Microsoft Transact-SQL syntax.
Test the client code.
Make required changes to the customer's administrative procedures.
Review the new features available in Microsoft SQL Server and make changes to take advantage of these features.

Reviewing Architectural Differences

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 dynamic link libraries (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.

Client Configuration and Net-Libraries

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 is referenced at connect time. For example, if an advanced entry is created with SERVER=XYZ, the server XYZ is 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.

For more information about configuring the client Net-Libraries, see SQL Server Books Online.

System Databases

The table provides an 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 an explanation of the Microsoft SQL Server system database structure, see "System Databases and Data" later in this chapter.

Keyword Conflicts

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 OUTER PIPE REPEATABLE REPLICATION RESTRICT RIGHT SCROLL SERIALIZABLE SESSION_USER SYSTEM_USER TAPE THEN UNCOMMITTED UPDATETEXT WHEN

Migrating Tables and Data

Using Data Transformation Services (DTS) allows 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 Wizard allows 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 Wizard to transfer your Sybase data into Microsoft SQL Server in a few steps.

Launch Enterprise Manager, click the Tools menu, and choose Data Transformation Services, Import into SQL.
In the Choose a Data Source dialog box, choose Sybase System 11 as the Source. Choose the DSN that corresponds to your Sybase data source.
In the Choose a Destination dialog box, choose Microsoft SQL Server 7.0 OLEDB Provider, select the database server, and then choose the required authentication mode for the selected database server.
In the Specify Table Copy or Query dialog box, choose Copy tables.
In the Select Source Tables dialog box, choose Select All. Click the gray box found within the Transform column of the Select Source Tables dialog box to change column names, data types, nullability, size, precision, and even write code to make unique transformations to your data before importing data into Microsoft SQL Server.
Run the data migration package immediately, or run it at a later time. The DTS Wizard shows you the progress and status of the data migration, step by step.

Reviewing Sybase T–SQL and Microsoft Transact–SQL Differences

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.

Transaction Management

Both Sybase and Microsoft support explicit transactions managed with the statements BEGIN TRANSACTION, SAVE, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.

Rollback Trigger

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.

Chained Transactions

Sybase System 10 introduced chained transactions, which are transactions that 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 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 @@OPTIONS function 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.

Transaction Isolation Levels

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

Cursors

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

Error checking is implemented differently by Sybase and Microsoft SQL Server. Sybase cursors report errors through @@sqlstatus, and 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.

Index Optimizer Hints

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 often optimizes 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 SQL Server Books Online.

Optimizer Hints for Locking

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 in "System Stored Procedures" later in this chapter.

For more information about locking hints, see SQL Server Books Online.

Server Roles

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 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 serverwide 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. While 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 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.

Raising Errors

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 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 enters the message in the error log. Messages raised with severities from 19 through 25 require the WITH LOG parameter.

PRINT

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

Partitioned Tables vs. Row Locking

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 SQL-92 NULL Behavior

Both Sybase 11+ and Microsoft SQL Server version 6.5 and later support SQL-92–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 SQL Server 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).

Subquery Behavior

Sybase SQL Server 4.9.2 and Microsoft SQL Server 4.2x subqueries are not SQL-92 standard; subqueries can return duplicate rows.

Sybase System 10 defaults to SQL-92 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 support only SQL-92 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.

System Stored Procedures

The Microsoft and Sybase implementations of the system stored procedures sp_addmessage, sp_dboption, and sp_configure are not the same.

sp_addmessage

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 to support alerts (severity ranges from 1 through 25).

Microsoft SQL Server stores user messages in master.dbo.sysmessages, and Sybase stores them in master.dbo.sysusermessages.

sp_dboption

The table lists the parameters for sp_dboption that are different for Sybase and Microsoft implementations.

Sybase parameters	Microsoft parameters
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, and 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.

sp_configure

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 chapter. 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 the @@OPTIONS function. @@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 the Microsoft Knowledge Base article 156498.

DUMP/LOAD

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 back up and restore operations, see SQL Server Books Online.

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.

Understanding Database Administration Differences

Microsoft SQL Server 7.0 offers several tools for database administration.

Graphical 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:

Manage logins, permissions, and users.
Create scripts.
Manage backup devices and databases.
Back up databases and transaction logs.
Manage tables, views, stored procedures, triggers, indexes, rules, defaults, and user-defined data types.
Create full-text indexes, database diagrams, and database maintenance plans.
Import and export data.
Transform data.
Perform various Web administration tasks.

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 on the Windows NT and Windows 95/98 operating systems. Because SQL Server Enterprise Manager is a 32-bit application, it cannot be installed on computers running 16-bit operating systems.

Auditing

SQL Server Profiler is a graphical tool that allows 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 allows 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 reducing the performance of an application. This can be useful when migrating an application from a file-based architecture to a client/server architecture, since the last step involves optimizing the application for its new client/server environment.

Examples of engine events include:

Login connects, fails, and disconnects.
Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
Remote procedure call (RPC) batch status.
The start or end of a stored procedure.
The start or end of statements within stored procedures.
The start or end of a Transact-SQL batch.
Errors written to the Microsoft SQL Server error log.
Locks acquired or released on a database object.
Open cursors.

Data about each event can be captured and saved to a file or a SQL Server table for later analysis.

Threshold Manager

Microsoft SQL Server uses two tools to manage transaction logs in a manner equivalent to the Sybase Threshold Manager:

SQL Server Enterprise Manager allows you to set up a system of periodic, scheduled backups.
SQL Server Agent monitors Windows NT Performance Monitor counters. You must set up an alert to execute a backup of the transaction log when the appropriate threshold is exceeded.

Rebuilding master

Sybase 4.x rebuilt its 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.

Graphical Query Analysis

SQL Server Query Analyzer is a graphical query tool that visually allows 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.

Migration Checklist

You must make the following changes to your Sybase database and applications before migrating to Microsoft SQL Server 7.0:

Change references to chained transaction mode to either unchained transactions or Microsoft implicit transactions. Change @@trainchain references to @@OPTIONS. Change @@transtate references to @@ERROR logic.
Convert ROLLBACK TRIGGER to savepoints.
Change transaction isolation levels from Sybase numeric-level identifiers to Microsoft string-based identifiers.
Move user-supplied stored procedures from sybsystemprocs to master.
Delete (preferably) or change index and locking optimizer hints to Microsoft format.
Change permanent temporary tables to global tables.
Change range and add severity to user-defined messages.

Remove:
- Arguments from the PRINT statement.
- Sybase server roles.
- User-defined data cache references.
- Thresholds.
- Table partitioning.
Make required syntax changes:
- Change DUMP and LOAD statements to BACKUP and RESTORE statements.
- Change cursor processing (change @@sqlstatus to @@FETCH_STATUS).
- Identity column default name changes to IDENTITYCOL.
- SET TRANSACTION ISOLATION LEVEL.
- SELECT statement optimizer hints.
- sp_addmessage (message range and severity).
- Reserved Microsoft keywords.
- DBCC.
- RAISERROR.
Change message ranges to >= 50,000.
Change argument substitution such as C printf.

Optionally, you can choose to make the following changes to your Sybase database and applications before migrating to Microsoft SQL Server 7.0:

Change tuning options for read ahead.
Change scrollable server cursors.
Encrypt stored procedures in syscomments.
Replace nested IF statements with the CASE statement.
Use RPCs with result sets in INSERTS.
Schedule automatic maintenance tasks, alerts, and replication.
Use extended stored procedures for mail notification, paging, scheduling.
Log user messages to the Windows NT application log and/or the SQL Server error log by using xp_logevent.
Change trusted connections and NT_username.
Start up stored procedures automatically.
Change EXECUTE strings.
Use the SQL Server Agent autostart functionality.
Change the SELECT statement to use SQL-92–style joins (INNER JOIN, CROSS JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN).

System Databases and Data

Microsoft SQL Server systems have four system databases:

master

The master database records all of the system-level information for a 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.
tempdb

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.
model

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 a SQL Server system.
msdb

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, each database has its own set of files that can grow independently of the others.

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 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 the 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.