Guide to Migrating from Sybase to SQL Server 2005

1

Guide to Migrating from Sybase to SQL Server 2005

Guide to Migrating from Microsoft Access to SQL Server 2005

SQL Server Technical Article

Writers: Yuri Rusakov, Alexey Kovaliov, Eduard Kolosov

Technical Reviewer: Darmadi Komo, Irena Balin

Published: December2007

Applies To: SQL Server 2005

Summary:This whitepapercovers migrating Microsoft Access databases to SQL Server2005 and discusses the differences between the two platforms. SQLServer Migration Assistant for Access (SSMA Access) is the best tool for this type of migration; this paper tells you how to use it to mitigate potential problems in database conversion.

Copyright

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

2007 Microsoft Corporation. All rights reserved.

Microsoft, SQL Server, Visual Basic, Visual C#, Visual Studio, and Windowsare either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Guide to Migrating from Microsoft Access to SQL Server 20051

Table of Contents

Introduction

Overview of Access to SQL Server 2005 Migration

Microsoft Access Limitations

Reasons for Migrating Access Databases to SQL Server 2005

Migration Scenarios

Creating a Two-Tier Application

Creating a Client/Server Application

Rewriting the Access Application

Access Version Compatibility

Migration Wizard

Migrating Database Objects

Schema Mapping

Type Mapping

Converting Schema Properties

Default

Nullability

Allow Zero Length

Converting Constraints

Check Constraints

Primary Keys

Foreign Keys

Migrating Indexes

Converting Expressions

Handling Timestamps

Naming

Table Migration Potential Problems

Access Table Does Not Have Unique Index or Primary Key

Access Table Unique Index Contains Multiple Null Values

Primary Key Includes Columns That Allow Null Values

Access Table Contains Dates Out of SQL Server Range

Access Table Contains Boolean Fields With Null Values

Access Objects Have the Same Names as SQL Server Keywords

Access Objects Names Include Special Characters

Hyperlink Column Jump Functionality Is Lost After Upsizing

Memo Column Is Indexed in Access

Jet Syntax Potential Problems

SELECT Statement

Distinctrow Keyword

Select Into Another Database

Incompatible Use of Alias

Logical Expressions in a Select Statement

TOP N Clause

Multiple Dot Syntax

Common Syntax Constructs

Expressions

'&' as Concatenation Operator

'\' Operator (Integer Division)

'Mod' Operator (Modulus Division)

'^' Operator (Raise to Power)

'Eqv' and 'Imp'

Date Literals Declaration

Like Operator

Join to View Converting Issues

Transform…Pivot Statement

Functions

String Functions

Advanced Functions

Domain Aggregate Functions

Numeric/Mathematical Functions

Date Functions

Data Type Conversion Functions

Aggregate Functions

Data Migration

Data Transfer Source

Data Transformation

Technology Used for the Data Migration

Transaction Boundaries

Conclusion

Guide to Migrating from Microsoft Access to SQL Server 20051

Introduction

Ifyou are considering migrating from Microsoft® Access to Microsoft SQL Server®2005, take into account the differences between the two platforms. This whitepaper lists the known migration issues and describes using Microsoft SQLServer Migration Assistant for Access (SSMA Access) as the tool for migration. Thiswhitepaperhas the following sections:

• Overview of Access to SQL Server 2005 Migration. Outlines the reasons why a Microsoft Access user would want to migrateto SQL Server2005, as well asthe ways to approach this task.SSMA Access is useful in scenarios in which the Access frontend will not change, but the back-end data will bemoved to overcome Access limitations and to ensure better robustness and security.The section also contains suggestions for other migration scenarios.
• The Migration Wizard. Describes how to use the SSMA Access Migration Wizard—the easiest way to perform a migration.
• MigratingDatabase Objects. Examines how to convert objects of the Jet database engine to SQL Server2005.(Jet is the Access database engine.) An SSMA Access user can control the way Access data types are mapped to SQL Server2005 and how constraints, indexes, and foreign keys are transferred. As SQL expressionsyntax is differentin SQL Server2005 and Access, the replacements SSMA Accessmakes during the migration are described.
• Table Migration Issues. Explains solutions for various problems that can arisewhenconverting Jet tables. For example, the linked table in Access must have a unique column; if it is missing, SSMA Access can create the required column automatically.Or, some Access columns might have names that are reserved in SQL Server2005, which can create problems after the migration. SSMA Accessprovides a warning so that you know which columns are candidates for renaming.
• Jet Syntax Issues. Describes differences between the syntax of SELECT queries in Access and in SQL Server2005. In most cases, SSMA Access can handle the differences automatically. For example, Access uses as a string concatenation operator, while SQL Server 2005 uses +.SSMA Access makes all necessary replacements when converting the query. In those rare cases when you should make manual updates, this sectionexplains how.
• Functions. Describesthe functions implemented in the Jet database engine, and how they correspond to SQL Server2005 functions.
• Data Migration. Offers an overview of the SSMA Access data migration process.

Overview of Access to SQL Server 2005 Migration

Microsoft Access is a database intended for small projects with few users.It is a good tool for developing business applications, because it is inexpensive and easy to use.Still, you may encounter problems when Access limitations prevent you from scaling the application when yourdatabase grows large and more users need towork with it. At that stage, you can choose themore robust and secure database solutions of SQL Server2005.

While the Access Jet database engine is continually updated with many quality functional and performance improvements, it is still a file-based, desktop-class data store. The Jet engine is not intended or architected to support more demanding business application requirements. (For a detailed comparison ofAccess and SQL Server2005, see Microsoft Access or SQL Server 2005: What's Right in Your Organization?)

Microsoft Access Limitations

Access has several inherent technical limitations. Problems (slow performance, corrupt database, application crashes) may occur if an Access application goes into the Caution or Stop zones for any one of the reasons listed in the following table.

Okay / Caution / Stop
LAN users / 5 / 5–10 / > 10
Internet users / 0 / 1 / > 1
Frequency of use / 1 x day / 2 x day / > 2x day
User permissions / Readonly / Read/write / Read/write
Database size / < 100MB / 100–300MB / 300MB
Data structures / Simple / Moderate / Complex
Database schema design / Good design / Poor design / Ad hoc

Reasons for Migrating Access Databases toSQL Server 2005

Following are the main reasons for moving peripheral Access databases to a SQL Server2005 environment:

• Allow multiuser access to a database. Access databases is popular among users, but can handle only a few users at a suitable performance level. When more users use the application, the Access database does not scale well. SQL Server2005, in contrast,can handle thousands of users, making it ideal for multiuser environments.
• Redesign and rewrite to fewer and better applications.People outside your organization's professional IT department may have developed yourAccess applications. They may not have developed the code and database according to development standards, and they may have lacked sufficient knowledge or time. In these situations, the entire organization can benefit from standardizing and unifying applications and data by centralizing common processes.
• Interchange data with the external world.SQL Server2005 offers better tools to communicate with other environments such as Oracle, IBM DB2, and SAP. Through the Data Transformation Services/Integration Services (DTS/SSIS) packages,you can automate data interchange with those environments or integrate them into existing applications.
• Better analyze and solve performance problems.Compared to Access, SQL Server2005 gives you stronger tools, such as SQL Profiler, execution plans in Query Analyzer, the Index Tuning Wizard, and filegroups to spread data across disks. SQL Server2005 also has a better locking mechanism built specifically for a multiuser environment.
• Benefit from SQL Server 2005 database security improvements.There are many benefits to usingboth SQLServer and Windows authentication to manage users and access to multiple databases, database roles, and for the many ways to grant permissions to the data they provide.

Migration Scenarios

Migrating an Access database to SQL Server2005 moves some or all data and data definitions to the server. After the data is safely transferred to SQL Server2005, you must decide how to interact with the server-based data. The options discussed in this section suggest possible solutions.

Creatinga Two-Tier Application

You can link server-based tables to local tables in Access and use the other existing database objects (queries, forms, reports, modules, and macros) as before. This creates a two-tier application, in which the server running SQL Server2005 stores data, and copies of the interface are stored in Access on the users' computers. Queries run on the client tier. Locally stored forms, reports, modules, and macros provide the remaining elements of the user interface. The design of server-based tables cannot be modified from within Access.

The advantage of this approach

The main advantageof this approach is its low transition cost because front-end database objects do not require modifications. Users continue to interact with a familiar interface, whileSQL Server2005 provides centralized data storage, backup and recovery, a multiuser environment, an integrated security model, and other advantages.After the data is in SQL Server2005, you can begin rewriting portions of the application by using a more robust environment such as Microsoft Visual Studio®.NET and SQL Server2005 Reporting Services. Moving an Access application towards a more scalable and robust platform is referred to as upsizing.When the migration is complete, all data is stored on the database server, and Access continues to operate smoothly as the desktop application.

The disadvantages of this approach

The disadvantages are that queries are still processed locally, and a large amount of network traffic is generated.

Implementation

You can implement thisconfiguration by using either the Access Upsizing Wizard or SSMA Access, but SSMA Access has more advanced functionality, including:

• Multiple database upsizing
• Schema and type mapping
• A Find Database wizard
• Query conversion
• User-friendly interface
• Improved data migration mechanism
• Advanced reporting
• More bug fixes

Creating a Client/Server Application

Microsoft Access introduced Microsoft Access Data Project, a new type of Access file (.adp) designed as a client/server application. Communication between the client and the server takes place through anOLEDB component architecture, which was specifically designed for efficient client/server communication. All tables and data definition objects such as views, stored procedures, and database diagrams are stored on the server. The client stores only code-based or HTML-based objects such as forms, reports, modules, and macros. Most data processing takes place on the server, and only filtered data is sent to the client, thus minimizing network traffic.

The advantage of this approach

It results in an Access application that provides the best performance and scalability, as well as the other positive attributes of SQL Server2005.

The disadvantage of this approach

It requires more development effort because Access objects such as forms, reports, queries, and code must be redesigned to work directly with SQL Server2005. In addition, theData Project feature may be removed in later Microsoft Access versions.

Implementation

This configuration is supported only by the Upsizing Wizard that is supplied as a part of Microsoft Access.

Rewriting the Access Application

In a small percentage of Access upsizing projects, the only possible is to migrate from Access. Perhaps your existing Access application has outgrown Access, and the programcannot meet your organization's scalability, reliability, and performance requirements. Or, you want to migrate all or part of the application to the World Wide Web.

In this scenario, you use your Access application as the startof a new design. You use additional technologies such as Visual Basic®(VB), Active Server Pages (ASP), and Visual Studio .NET to rewrite the Access application. As part of the process, the data moves to SQL Server2005. Additionally, you can migrate other data sources, such as Oracle and IBM DB2 into SQL Server2005 for a centrally managed solution.

The advantage of this approach

The major advantage is the flexibility inherent in this approach. You create an application that can target computers running Windows and the Web with minimum changes. A more professional development environment, such as Visual Studio.NET, offers advantages such as team-based management, source code control, and third-party tools and components. In this scenario, you have a reliable, scalable, and manageable application that can move from the business unit to the enterprise level.

The disadvantage of this approach

The disadvantage of this approach is cost. Because you are discarding the Access application and its database, you are creating a new application with a new design, development, and implementation project. (Third-party products can help cut the cost of front-end conversion. Consider using Access ConverterTM from Microtools.)

Implementation

Following is an example migration sequence:

1. Migrate the Access back-end schema and data. Use either SSMA Access or the Upsizing Wizard (SSMA Access is preferable).
2. Migrate the Access forms to ASP.NET WebForms. Preserve the lookandfeel and the operation of the original Access forms. All Access built-in features, such as auto lookup, implicit updates, shortcuts, and tooltips can be emulated byusing advanced WebForms capabilities.
3. Convert Access queries to stored procedures. SSMA Access can help convert SELECT queries.
4. The generated VB.NET code performs all database interactions and form navigation. If necessary, convert that code to VisualC#®.NET with any number of low-cost VBto C#.NET conversion tools.
5. Manually convert Microsoft Access VBA code to VB.NET or C#.NET. The time requiredto migrate Visual Basic for Applications (VBA) code depends on the volume and techniques employed. For example, applications that depend heavily onthe DAO (Data Access Objects) interface to perform database operations take longer to convert than those that use built-in Access features.

Access Version Compatibility

Note that SSMA Access supports databases used in Access97 and later. If you have databases from an earlier version of Access, you must convert them to a newer version. To convert, open and save the databases in Access97 or a later version before you add them to SSMA Access.

Migration Wizard

If you lack SSMA Access experience and need to migrate a single database, theSSM Access Migration Wizard is the best choice for an easy and quick migration.You can configure the Migration Wizard to run every time SSMA Access starts.The first screen of the Wizard is shown in Figure1.

Figure 1: The first screen of the Migration Wizard

To use the SSM Acess Migration Wizard to migrate databases

1. Step1 of the wizard (see Figure2), prompts foraname and location for your new SSMA Access project. You can confirm the default values or enter your own.

Figure 2: Create a new project

1. Next, add databases to the project, as shown in Figure3.

Figure 3: Add Access databases to the project

You can locate databases manually and add them to the list of databases to migrate, or use the Find Database Wizard, shown in Figure4, which makes database selection easier.

Figure 4: Use the Find Databases Wizard for easier selection