Chapter 6 - Migrating Sybase Databases to SQL Server 2000

  • Article

Microsoft® SQL Server™ and the Sybase relational database systems were developed together until their respective 4.2 versions. This commonality provides for a unique, low-cost opportunity for Sybase customers to migrate their databases to SQL Server and, therefore, to benefit from the new functionality of SQL Server 2000.

This chapter outlines the steps to follow in migrating Sybase databases to SQL Server 2000 and presents information to help database application developers anticipate migration issues. The information in this chapter is intended for Sybase database administrators (DBAs) and managers who are planning to migrate a database application to SQL Server 2000. It assumes the reader is familiar with Sybase databases (SQL Server or Adaptive Server Enterprise).

This chapter also describes the differences between Sybase T-SQL and SQL Server Transact-SQL statements, and differences in administrative procedures. Issues regarding conversion from CT-Library applications or the porting of ODBC applications from Sybase to SQL Server databases are not addressed.

Why Migrate to SQL Server 2000?

Cc917625.spacer(en-us,TechNet.10).gif Cc917625.spacer(en-us,TechNet.10).gif

Reasons why you should consider migrating your Sybase application to SQL Server 2000 are:

  • Performance-ready for the enterprise edition 

  • Scalability and performance of very large databases 

  • Dynamic self-management 

  • Ease of use 

  • Integration with Microsoft Windows® 2000, Microsoft Office, and Microsoft .NET Enterprise Servers 

  • Accessible business intelligence 

  • Single, scalable code base 

Performance-Ready for the Enterprise Edition

SQL Server 2000 provides outstanding performance on the Microsoft Windows 2000 operating system and has delivered record-setting results on industry standard benchmarks. In November 2000, SQL Server 2000, running on Windows 2000, set a new world record for overall performance in the TPC-C benchmarks with an overall rate of 505,302 tpm/C and a cost of $19.80/tpmC. At that time, this overall level of performance was approximately 15% higher than the best results posted using IBM's DB2 and over twice as high as any results posted using Oracle.

Companies running on systems with 32 CPUs or fewer can enjoy the benefits of SQL Server 2000, a database that offers top performance and can be very cost-effective.

Scalability and Performance of Very Large Databases

SQL Server 2000 is the foundation for the Microsoft TerraServer Web site. TerraServer accesses 1 terabyte of satellite images of the world. Currently, the site has received more than 1 billion hits, with peak loads of more than 29 million hits and almost 18 million database queries in a day.

The following statistics from the first 21 days of operation demonstrate that SQL Server 2000 is capable of providing huge database capacity and outstanding transaction throughput:

  • Total number of hits: 317,670,000

  • Average hits per day: 15,130,000

  • Peak hits per day: 28,940,000

  • Average database queries per day: 8,810,000

  • Peak database queries per day: 17,760,000

Dynamic Self-Management

A common task for a DBA is to monitor 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 requires. By providing dynamic self-management, SQL Server 2000 helps to reduce both serious errors and the amount of errors caused by disk and memory allocation.

Ease of Use

SQL Server 2000 is easy to use. SQL Server 2000 provides graphical tools, task pads, and more than 30 wizards to help DBAs automate and schedule routine tasks. For example, the Index Tuning Wizard helps to determine how to best tune an application, and the graphical SQL Query Analyzer can provide graphical execution plans for database statements, helping in identifying performance bottlenecks.

Sybase does not offer the same tools found in SQL Server 2000 for automating and scheduling tasks, and proactively alerting DBAs if there is any problem.

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.

Integration with Microsoft Windows 2000, Microsoft Office, and Microsoft .NET Enterprise Servers

With SQL Server 2000 and Microsoft Excel 2000, Microsoft offers desktop analysis, enabling Excel users for the first time to analyze gigabytes and terabytes of data with Microsoft SQL Server 2000 Analysis Services. SQL Server 2000 integrates with Microsoft Windows NT® security and systems management to provide greater productivity and ease of use for users. A single security model offers single-user login when accessing a database. Management tools such as Microsoft Windows NT Event Viewer offer automated alerts; for example, the server can send a page or e-mail if there are any database issues. Microsoft Transaction Server, part of Windows NT Server, allows easy building of multitier applications. SQL Server also integrates closely with Microsoft Exchange Server, Microsoft Systems Management Server, and Microsoft SNA Server. All of these features provide ease of use to the administrator.

Users can be more productive if they can use their favorite data analysis tool as the interface for data warehouses.

Accessible Business Intelligence

SQL Server 2000 includes comprehensive data warehousing capabilities, such as integrated Data Transformation Services (DTS), SQL Server 2000 Analysis Services, and even graphical tools that help model your data warehouse. SQL Server 2000 enables easy, cost-effective development of data warehouses, and Microsoft Office 2000 provides easy-to-use capabilities for exploring and analyzing 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 a SQL Server-based application. The capabilities of SQL Server 2000 can benefit 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.

Single, Scalable Code Base

Sybase offers different database engines in different editions of the program. This means that code written for to one edition may not run the same way on another edition. In contrast, SQL Server 2000 is developed from a single code base that scales from a laptop running on Microsoft Windows 95 or Microsoft Windows 98 operating systems to multiprocessor clusters running on Microsoft Windows 2000 Server, Enterprise Edition. This offers 100 percent application compatibility. Microsoft Data Engine (MSDE) is a new, SQL Server 2000-compatible data engine that ships with Microsoft Office 2000. With Office 2000, applications can be developed, accessed, and managed using the Microsoft Access 2000 as the interface and MSDE as the storage engine.

Only one version of an application, which runs on different hardware environments, would have to be developed and supported. 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.

Understanding the Migration Process

Cc917625.spacer(en-us,TechNet.10).gif Cc917625.spacer(en-us,TechNet.10).gif

These areas must be considered for migrating databases from Sybase Adaptive Server Enterprise to SQL Server 2000:

  • Data and object definitions

  • Changes to Transact-SQL and system stored procedures

  • Administrative changes 

The steps to follow for the migration process are:

  1. Review the architectural differences between SQL Server 2000 and the Sybase server that require changes to administrative procedures. 

  2. Migrate data and objects using DTS. 

  3. Review Sybase stored procedures, triggers, SQL scripts, and applications for necessary language changes. 

  4. 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 SQL Server Transact-SQL syntax. 

  5. Test the client code. 

  6. Make required changes to administrative procedures of the customer. 

  7. Review the new features available in SQL Server 2000 and make changes to take advantage of these features. 

Reviewing Architectural Differences

Cc917625.spacer(en-us,TechNet.10).gif Cc917625.spacer(en-us,TechNet.10).gif

To start a successful migration, you should understand the basic architecture and terminology associated with SQL Server 2000. Furthermore, you should be familiar with the architectural differences between the database servers.

Client Configuration and Net-Libraries

Although it is easier to use Microsoft DB-Library to migrate Sybase applications that use CT-Library, to take advantage of SQL Server 2000 functionality, it is recommended that 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.

SQL Server-based clients should use the appropriate Microsoft SQL Server Network Library components shown in this table.

Net-Library

Win32® DLL

TCP/IP Windows Sockets

Dbnetlib.dll

Named Pipes

Dbnmpntw.dll

Multiprotocol

Dbmsrpcn.dll

Novell IPX/SPX

Dbnetlib.dll

Banyan VINES

Dbmsvinn.dll

AppleTalk

Dbmsadsn.dll

VIA (Giganet)

Dbmsgnet.dll

VIA (Servernet II)

Dbmsgnet.dll

The SQL Server Client Network Utility is a client-side component, installed in the SQL Server program group that is used to manage the client-side Net-Libraries.

On the Alias tab of the SQL Server Client Network Utility, you can alias a server multiple times with different network libraries or connection parameters specified:

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 the osql utility is run with a /Sxyz switch.

Network library 

The 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 SQL Server servers are compatible with the client software of the other, provided the software is limited to the features in SQL Server 4.2. SQL Server servers can host Sybase clients, and Sybase servers can host SQL Server clients. Since the release of SQL Server 4.2, the servers diverge by introducing new data types, new Transact-SQL statements, new ANSI SQL statements, and new administrative procedures. Sybase customers migrating to SQL Server 2000 should convert the client software to use OLE DB providers or ODBC drivers. This software is included with SQL Server 2000 or can be downloaded from https://msdn2.microsoft.com/sqlserver/default.aspx.

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

System Databases

The following table provides a quick overview of the implementation of Sybase Adaptive Server Enterprise and SQL Server 2000 system databases.

Database item

Sybase Adaptive Server Enterprise (SQL Server 10+)

SQL Server 2000

Comments

System 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 returns the oldest open transaction.

Replace all references to syslogshold with DBCC OPENTRAN logic.

For a complete explanation of the Microsoft SQL Server system database structure, see "System Databases and Data" in SQL Server Books Online.

Database Size

Sybase Adaptive Server Enterprise for Windows NT supports up to 128 disk devices per database. Each device can be up to 8 gigabytes (GB). This means that a database cannot grow larger than 1 terabyte. Instead of devices, SQL Server 2000 uses files, which can be configured to grow dynamically and can be grouped into filegroups. For more information about files and filegroups, see "Physical Database Files and Filegroups" in SQL Server Books Online.

Furthermore, SQL Server 2000 supports databases up to 1,048,416 terabytes in size. SQL Server 2000 Enterprise Edition supports distributed partitioned views, which allow groups of database servers and an enterprise application to distribute workload. For more information about Partitioned Views, see "Designing Partitions" in SQL Server Books Online.

Data Types

For a comprehensive list of system-defined data types, see "Data Types" in SQL Server Books Online.

Keyword Conflicts

Before you transfer your entire Sybase production system to SQL Server 2000, migrate your Sybase database to a test SQL Server 2000 database, and then fully test your applications and administrative procedures against this test database, including stress testing.

Review your Sybase T-SQL statements and the names of database objects for keyword conflicts before transferring your objects and data to SQL Server 2000.

The following tables list Sybase System 11 keywords that do not function in SQL Server 2000 and keywords that are reserved by SQL Server 2000. If your Sybase T-SQL statements use any of the listed keywords, replace them with other words before you migrate your Sybase database to SQL Server. For a comprehensive list of reserved keywords, see "Reserved Keywords" in SQL Server Books Online.

Sybase System 11 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

SQL Server 2000 reserved keywords

 

 

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

Cc917625.spacer(en-us,TechNet.10).gif Cc917625.spacer(en-us,TechNet.10).gif

DTS enables importing and exporting data between multiple heterogeneous sources that use an OLE DB-based architecture (such as Microsoft Excel spreadsheets and flat text files) and transferring databases and database objects (for example, indexes and stored procedures) between multiple computers running SQL Server 2000. 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.

Using the DTS Import/Export Wizard, you can interactively create DTS packages that use OLE DB and ODBC to import, export, validate, and transform heterogeneous data. Using the wizards, you can also copy schema and data between relational databases. To use the this wizard, you must have an ODBC or OLE DB driver for your Sybase server on the computer on which you are running DTS

To transfer your Sybase data into SQL Server using the DTS Import/Export Wizard

  1. Create an ODBC data source for the Sybase database. 

  2. Start SQL Server Enterprise Manager, on the Tools menu, point to Data Transformation Services, and then click Import Data. 

  3. In the Choose a Data Source dialog box, select Other (ODBC Data Source) as the Source, and then select the DSN that corresponds to your Sybase data source.

  4. In the Choose a Destination dialog box, select Microsoft SQL Server OLEDB Provider, select the database server, and then select the required authentication mode for the selected database server.

  5. In the Specify Table Copy or Query dialog box, select Copy table(s) and view(s) from the source database.

  6. In the Select Source Tables dialog box, click Select All. Click the gray box found in the Transform column of the Select Source Tables dialog box to change column names, data types, nullability, size, and precision, and even to write code to make unique transformations to your data before importing data into SQL Server. 

  7. Run the data migration package immediately or at a later time. The DTS Import/Export Wizard will show you the progress and status of the data migration, step by step. 

Reviewing the Differences Between Sybase T-SQL and Transact-SQL

Cc917625.spacer(en-us,TechNet.10).gif Cc917625.spacer(en-us,TechNet.10).gif

Some differences between Sybase T-SQL and 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 SQL Server 2000 support explicit transactions managed with the BEGIN TRANSACTION, SAVE, COMMIT TRANSACTION, and ROLLBACK TRANSACTION statements.

ROLLBACK Triggers

The Sybase ROLLBACK TRIGGER statement rolls back only the work performed by the statement that fired the trigger.

In SQL Server 2000, 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

In Sybase System 10, chained transactions were introduced. Chained transactions 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]

In SQL Server version 6.5, implicit transactions were introduced. Implicit transactions function in the same way as Sybase chained transactions. 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 SQL Server.

Sybase stored procedures are tagged with the transaction mode (chained or unchained) with which they were created, and 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 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 SQL Server.

The Sybase @@tranchained global variable, indicating the current transaction mode (0 = unchained, 1 = chained), has no SQL Server equivalent. The 2 bits in the 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 global variable, indicating whether a transaction is in progress, successful, or canceled, has no SQL Server equivalent. Replace @@transtate logic with either @@ERROR checking or SET XACT_ABORT ON to enable SQL Server to roll back a transaction automatically when an error occurs.

The Sybase sp_procxmode system stored procedure, used to control the transaction modes of stored procedures, has no SQL Server equivalent. Sybase procedures must have COMMIT statements matched with BEGIN TRANSACTION statements or be removed from the stored procedures before migrating to SQL Server.

Transaction Isolation Levels

Sybase identifies its transaction isolation levels with numbers, and SQL Server identifies the levels with character tags. Scan for SET TRANSACTION ISOLATION LEVEL statements and change the Sybase level specifications to SQL Server 2000 specifications.

Sybase

SQL Server 2000

0

READ UNCOMMITTED

1

READ COMMITTED

2

REPEATABLE READ

3

SERIALIZABLE
Cursors

SQL Server 2000 supports the Sybase cursor statements except for a minor difference in syntax for the DEALLOCATE CURSOR:

DEALLOCATE CURSOR cursor_name

The keyword CURSOR is not used by SQL Server with the DEALLOCATE cursor statement:

DEALLOCATE cursor_name

For more information about cursors, see "Cursors" in SQL Server Books Online.

Cursor Error Checking

Sybase and SQL Server 2000 implement error checking differently. Sybase cursors report errors through @@sqlstatus, and SQL Server 2000 reports errors through @@FETCH_STATUS. As shown in the table, Sybase and SQL Server report different values.

@@sqlstatus

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

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 performance 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 will be optimizing based on outdated statistics.

SQL Server 2000 updates statistics automatically, so the SQL Server query optimizer is more likely than the Sybase query optimizer to make the best choice of index use. In addition, SQL Query Analyzer can help programmers and DBAs determine the system I/O bottlenecks. The automatically updated statistics, the accurate query optimizer, and the ability to troubleshoot using SQL 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 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 SQL Server does not support them. SQL Server automatically uses READ AHEAD (RA) processing when it is appropriate. This behavior can be tailored with new RA options on sp_configure (see "System Stored Procedures" in this chapter). For more information about locking hints, see "Locking Hints" in SQL Server Books Online.

Server Roles

SQL Server 2000 does not support the Sybase server roles of sa_role, sso_role, and oper. The GRANT and REVOKE statements referencing these roles must be removed.

In SQL Server, the sysadmin fixed server 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 proc_name function, which validates a username, is not supported by SQL Server and must be removed.

The following table describes the fixed server roles in SQL Server 2000.

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

bulkadmin

Can perform bulk insert operations

You can get a list of the fixed server roles by executing sp_helpsrvrole, and you can get the specific permissions for each role by executing 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 SQL Server 2000.

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 SQL Server RAISERROR statement requires positional argument substitution, like printf in the C language, 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 SQL Server RAISERROR statement includes a WITH LOG parameter so the server will enter 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 SQL Server 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 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 or later, 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.

SQL Server 2000 supports row-level locking on all table types, and does not support the keyword PARTITION on the ALTER TABLE statement. SQL Server 2000 Enterprise Edition allows for updatable distributed partitioned views. These views allow you to spread a table across multiple servers and access the full table data from any one of the servers.

For more information about locking, see "Locking Architecture" in SQL Server Books Online.

Setting ANSI NULL Behavior

Both Sybase version 11 and later and SQL Server version 6.5 and later support SQL-92-compliant NULL behavior; however, the syntax in the two systems is different.

Sybase

SQL Server 2000

SET ANSINULL {ON|OFF}

SET ANSI_NULLS {ON|OFF}
SET ANSI_WARNINGS {ON|OFF}

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

SQL Server 2000 and Sybase use the same syntax for defining identity columns. The SQL Server 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 Sybase and SQL Server 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 SQL Server ANSI_NULLS and ANSI_WARNINGS options.

The following table lists the Sybase-specific options that do not have exact SQL Server equivalents, and lists the SQL Server options that most closely match the desired Sybase behavior. To understand the differences between the Sybase statement and the alternative offered, review SQL Server Book Online carefully. The table does not list the options that Sybase and SQL Server share.

Sybase option

SQL Server 2000 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).

Join Syntax

Sybase joins use an old style syntax in which the join takes place in the WHERE statement. This syntax is supported in SQL Server 2000 for backward compatibility only. It will not be supported in future releases. When migrating your database from Sybase to SQL Server 2000, you should update the joins to an SQL-92 compliant syntax.

Sybase

SQL Server 2000

SELECT a.col1, b.col1
FROM table1 a, table2 b
WHERE a.col1 *= b.col2

SELECT a.col1, b.col1
FROM table1 a
LEFT OUTTER JOIN table2 b
ON a.col1 = b.col2

These two queries can return different results because the FROM clause is evaluated before the WHERE clause. For example, if a search condition were added to the WHERE clause, it would be evaluated at different times in the query. In the Sybase syntax, it would be evaluated along with the join. In the SQL-92–standard syntax, it would be evaluated after the join and reduce the result set.

For more information, see "Specifying Joins in FROM or WHERE Clauses" in SQL Server Books Online.

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 earlier subquery behavior can be set to ON to ease migration. Sybase 10 is backward compatible to the non–SQL-92I behavior if the SET DUP_IN_SUBQUERY option is set to ON. Sybase 11 and SQL Server 2000 only support 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 they do not cause errors. If you are migrating from a Sybase version earlier than version 10, you also must review queries that have subqueries.

Grouping Results

SQL Server 2000 requires that all nonaggregate columns in the select list be named in the GROUP BY clause if it is used. Sybase allows the nonstandard method of not explicitly naming all columns in the group by statement. The same functionality can be achieved using derived tables in SQL Server 2000.

Sybase

SQL Server 2000

SELECT au_lname, city, count(*) FROM authors GROUP BY city

SELECT au_lname, city, (select count(*) FROM authors WHERE city = a.city) FROM authors a

System Stored Procedures

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

sp_addmessage

In Sybase systems, the range for user-defined message numbers starts at 20,000. In SQL Server 2000, the range starts at 50,000 and also requires a severity to be specified (range is from 1 through 25) to support alerts.

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

sp_dboption

The following table shows the parameters for sp_dboption that are different for Sybase and SQL Server 2000 implementations.

Sybase

SQL Server 2000

ABORT TRAN ON LOG FULL

No equivalent.

ALLOW NULLS BY DEFAULT

ANSI NULL DEFAULT

AUTO IDENTITY

No equivalent.

DDL IN TRAN

SQL Server 2000 allows DDL in transactions.

IDENTITY IN NONUNIQUE INDEX

No equivalent.

Sybase requires a checkpoint in the affected database after sp_dboption completes, and SQL Server 2000 automatically check points the affected database. Also, SQL Server 2000 allows DDL in transactions without requiring the system administrator to set any server or database options.

sp_configure

You can manage and optimize SQL Server 2000 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.

SQL Server 2000 has 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 sp_configure are quite different in Sybase and SQL Server 2000. Detailing all of the differences is beyond the scope of this chapter. For more information about the options for sp_configure, see SQL Server Books Online.

sp_configure allows members of the sysadmin fixed server role to set defaults for user options, such as SQL-92 options, although individual connections can change the settings later. The current state of a connection's settings are made visible to it through the @@OPTIONS. @@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, search for the Microsoft Knowledge Base Article "156498" at https://support.microsoft.com/.

DUMP/LOAD

The DUMP statement is included in SQL Server 2000 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 "Backing Up and Restoring Databases" in SQL Server Books Online.

Use DTS to perform imports and exports on a regular basis with SQL Server 2000.

DUMP/LOAD statements

Sybase Adaptive Server Enterprise

SQL Server 2000

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 SQL Server DBCC OPENTRAN statement.

Understanding Database Administration Differences

Cc917625.spacer(en-us,TechNet.10).gif Cc917625.spacer(en-us,TechNet.10).gif

Microsoft SQL Server 2000 offers several tools for database administration.

Graphical Administration

SQL Server Enterprise Manager allows for 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 2000 or Windows NT 4.0, and as part of the client software on computers running Windows 2000, Windows NT 4.0, Windows 95, or Windows 98. Because SQL Server Enterprise Manager is a 32-bit application, it cannot be installed on computers running 16-bit operating systems.

Auditing

SQL Profiler is a graphical tool that enables system administrators to monitor engine events in SQL Server 2000. SQL Profiler captures a continuous record of server activity in real time. SQL 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 Profiler, you can replay previously captured traces. This tool helps 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:

  • 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 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

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

  • SQL Server Enterprise Manager enables you to set up a system of periodic, scheduled backups. 

  • SQL Server Agent monitors System Monitor, available in Windows 2000, counters. You must set up an alert to execute a backup of the transaction log when the appropriate threshold is exceeded. 

Rebuilding master

In Sybase 4.x, the master databases were rebuilt by the bldmstr utility. In later versions of Sybase, the sybinit utility is used to rebuild the master database.

SQL Server 2000 has a Rebuild Master utility that provides the same functionality.

Graphical Query Analysis

SQL Query Analyzer is a graphical query tool that enables you to visually analyze the plan of a query, execute multiple queries simultaneously, view data, and obtain index recommendations. SQL Query Analyzer provides the graphical diagram of the showplan information, which is used to report data retrieval methods chosen by the query optimizer.

Migration Checklist

Cc917625.spacer(en-us,TechNet.10).gif Cc917625.spacer(en-us,TechNet.10).gif

You must make the following changes to your Sybase database and applications before migrating to SQL Server 2000.

  1. Change references to chained transaction mode to either unchained transactions or SQL Server 2000 implicit transactions. Change @@trainchain references to @@OPTIONS. Change @@transtate references to @@ERROR logic. 

  2. Convert ROLLBACK TRIGGER to savepoints. 

  3. Change transaction isolation levels from Sybase numeric-level identifiers to SQL Server 2000 string-based identifiers. 

  4. Move user-supplied stored procedures from sybsystemprocs to master. 

  5. Delete (preferably) or change index and locking optimizer hints to SQL Server 2000 format. 

  6. Change permanent temporary tables to global tables. 

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

    • DUMP and LOAD statements change to BACKUP and RESTORE statements 

    • 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 SQL Server 2000 keywords 

    • DBCC 

    • RAISERROR 

  8. Change message ranges to >= 50,000. 

  9. 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 SQL Server 2000:

  • 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, and scheduling. 

  • Log user messages to the Windows 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). 

Cc917625.spacer(en-us,TechNet.10).gif