[转]Moving Your Access 2002 Database to SQL Server

Microsoft SQL 2000 Technical Articles
Moving Your Access 2002 Database to SQL Server
 

Adam Cogan
Microsoft Regional Director, Austrailia

December 2004

Applies to:
   Microsoft Access 2002
   Microsoft SQL Server 2000 Service Pack 3a (SP3a)

Summary: Use this features comparison in preparation for migrating your Access 2002 database back end to SQL Server 2000. (37 printed pages)

Contents

Prerequisites
Introduction
SQL Server Tools
Architecture
Scalability and Performance
Working with Data
Conclusion
Glossary

Prerequisites

All comparisons in this paper are made with the assumption that the following software is used:

  • Microsoft Access 2002 or later
  • Microsoft SQL Server 2000 Standard Edition or Enterprise Edition

It is also assumed that your data is currently stored in an Access database (.mdb) file, and not in SQL Server, and that you are not using an Access Data Project (ADP) that supports many of the SQL Server features described in this paper.

Who Should Read This

This paper is for Access developers, Microsoft Visual Basic developers, and .NET developers that are familiar with the features of Access and are considering moving their back-end infrastructure (data and queries) to Microsoft SQL Server.

Readers need to be familiar with these Access features:

  • Basic SQL
  • Importing data from and exporting data to various formats
  • Backing up and restoring data
  • Implementing security

This paper is designed to assist new SQL Server developers by comparing Access and SQL Server features.

Introduction

Microsoft Access developers generally consider a move to SQL Server for performance, security, and stability reasons. This process is known as upsizing, and developers will find a number of key differences while migrating from Access to SQL Server. It is crucial that these differences are noted and appropriate action is taken to ensure a seamless and incident-free migration from Access to SQL Server.

Microsoft SQL Server is an enterprise-level data management system. It encapsulates industry-standard security, scalability, and manageability. In addition, there is support for Extensible Markup Language (XML) and Internet queries.

Tip   The process of migration from Access to SQL Server is not covered here.
For more information about migration, see Migrating Your Access Database to Microsoft SQL Server 7.0. (Note: This article was written for SQL Server 7.0 and was not updated.).
Tip   The differences between data replication and database security is not covered here.
For more information about implementing replication in SQL Server, see Implementing Replication in the SQL Server 2000 SDK documentation.
For more information about security in SQL Server, see Managing Security Accounts in the SQL Server 2000 SDK documentation.

SQL Server Tools

Using the main menu in Access database window, you can create a query, design a database, or browse data. To export data from your database, click File, and then click Export. To import data to your database, click File, click Get External Data, and then click Import.

SQL Server provides a suite of powerful tools that simplifies the process of browsing, querying, importing, and exporting data. They are:

  • SQL Server Enterprise Manager
  • SQL Server Query Analyzer
  • Data Transformation Services
  • SQL Server Profiler

SQL Server Tools to Design Databases and Queries, and Browse Data

With SQL Server you use two tools to perform database maintenance tasks, and browse and edit data. These are SQL Server Enterprise Manager and SQL Server Query Analyzer. Access forms developers planning to move their forms to .NET will also find Microsoft Visual Studio .NET useful, as it provides an integrated way of creating and managing your SQL Server database and your data access forms within the same development environment.

SQL Server Enterprise Manager

SQL Server Enterprise Manager is the application bundled with SQL Server to design and manage your database, as shown in Figure 1, and browse through data, as shown in Figure 2. Enterprise Manager also provides functionality to:

  • Manage tables, fields, and data; table relationships; stored procedures; views; triggers; functions; and user defined data types.
  • Create database diagrams
  • Create database backups and restore data
  • Manage database logons and object permissions
  • Import data from and export data to a variety of formats that use Data Transformation Services (DTS)

Figure 1. SQL Server Enterprise Manager replaces the main Access dialog box for designing and managing your database.

Figure 2. Use Enterprise Manager to browse and edit data in much the same way as Access.

SQL Server Query Analyzer

SQL Server Query Analyzer is a fully featured graphical query tool that replaces the main Access query designer. It allows you to:

  • Create and debug queries
  • Run multiple simultaneous queries
  • View data
  • Export data (by clicking Query, and then clicking Results to File)
  • Optimize queries (by clicking Query, and then clicking Show Execution Plan)
  • Debug advanced queries (by clicking Tools, clicking Object Browser, and then clicking Debug)
    Tip   Query Analyzer supports the previously described features and provides syntax highlighting for easy viewing and debugging of queries, as shown in Figure 3. Although you can write stored procedures inside Enterprise Manager as shown in Figure 4, Access developers will find Query Analyzer more feature-rich.

    Figure 3. Query Analyzer replaces the Access query designer and adds features like syntax highlighting and query debugging.

    Figure 4. Writing advanced stored procedures inside Enterprise Manager is not as easy as in Query Analyzer

The Create Query by Using Wizard feature in Access has no equivalent in SQL Server. You must create queries using the query designer or SQL Server statements.

Visual Studio .NET

With Visual Studio .NET you can manage your database and database objects in much the same way as Enterprise Manager, as shown in Figure 5. Depending on your version of Visual Studio .NET, you can create a database project that allows you to:

  • Design and execute stored procedures, views, triggers, and functions
  • Browse tables
  • View data

This feature is useful for .NET developers because it offers an integrated method of database management. Developers can develop applications and manage their database within one application.

Figure 5. Visual Studio .NET provides an integrated way to manage your data

For more information about which versions of Visual Studio .NET support which database management features, see Visual Database Tools Editions.

SQL Server Tools to Import and Export Data

Data Transformation Services

Data Transformation Services (DTS) allow you to import data from and export data to various data sources that use an OLE DB-based architecture, such as Microsoft Excel. DTS replaces the Access import and export functions (as shown in Figure 7), and also provides functionality to:

  • Export data from and import data to another SQL Server database
  • Export and import data to and from various formats such as Excel (.xls files), comma-separated values (.csv files), and Microsoft Access (see Figure 6)
  • Perform transformations on data

Figure 6. Use DTS to import from and export to a variety of data formats.

DTS is more powerful than the Access import and export commands. Many tasks that are performed in an Access import process are done in multiple steps (for example, populating temporary tables and running multiple queries to perform the transformation) that can be performed in one step in DTS. You can perform data transformations, such as copying data from one table to another using a SQL query, or execute VBScript code to transform parts of the data before insertion into the destination table, as shown in Figure 8.

Figure 7. DTS replaces the Access import and export wizards and allows powerful data transformations.

Figure 8. DTS performs powerful transformations on data that would take much longer in Access.

SQL Server Profiler

SQL Server Profiler is an essential tool for optimizing the performance of your database. It is especially useful after a migration from a client-only system such as Access. It shows all commands executed on the server, such as connections opened and closed, and database transactions, as shown in Figure 9. This helps identify any transactions that are particularly lengthy or resource-intensive.

Figure 9. SQL Server Profiler monitors database activity to aid in performance optimization.

For more information about using these SQL Server tools, see Migrating Your Access Database to Microsoft SQL Server 7.0. (Note: This article was written for SQL Server 7.0 and was not updated.)

Architecture

There are several differences, similarities, and disadvantages between the architecture of Access and SQL Server. These include differences in:

  • Data access models
  • Table design
  • Relationships
  • Indexing
  • Data query types
  • SQL Server also includes powerful features to optimize and simplify data manipulation, including:
  • Triggers
  • Temporary tables
  • User-defined functions

System Requirements

Minimum System Requirements

Because SQL Server has more features and is more scalable than Access, it has slightly more demanding system requirements. Table 1 compares the minimum system requirements between the two systems.

Table 1. Minimum system requirements for SQL Server and Access

  Access SQL Server
Processor Pentium 75 megahertz (MHz) Pentium 166 MHz
Memory 8 megabytes (MB), plus 4 MB for each application running simultaneously, plus 128 MB for Microsoft Windows XP 128 MB RAM or more
Hard disk space 30 MB 270 MB (full installation)
Operating system Microsoft Windows Server 2003, Windows XP, Windows 2000, Windows NT 4.0 with Service Pack 6 (SP6), Windows Millennium Edition, Windows 98 Second Edition, Windows 98, or Windows 95 Microsoft Windows Server 2003, Windows XP, Windows 2000, Windows NT 4.0, Windows 98 Second Edition, Windows 98, Windows 95, or Windows CE

Realistic System Requirements

The minimum requirements listed in Table 1 are unrealistic in a typical operational environment. System requirements depend mainly on the quantity of data and the number of concurrent users.

In a scenario of 10 concurrent users and a 1 gigabyte (GB) database, the system specified in Table 2 is recommended for running Access or SQL Server in a production environment.

Table 2. Recommended system requirements for SQL Server and Access

  Recommended
Processor Pentium III 650 MHz
Memory 384 MB
Hard disk space 2 GB
Operating system Microsoft Windows Server 2003 or Windows 2000

SQL Server Versions

SQL Server 2000 is available in six editions:

  • Enterprise
  • Standard
  • Personal
  • Developer
  • Desktop Engine (MSDE)
  • SQL Server CE (a compatible version for Windows CE)

Table 3 shows the operating system requirements for the different SQL Server editions.

Table 3. Operating system requirements for different SQL Server editions

Operating System Enterprise Edition Standard Edition Personal Edition Developer Edition Desktop Engine (MSDE) SQL Server CE
Windows Server 2003, Standard Edition Yes Yes Yes Yes Yes No
Windows Server 2003, Enterprise Edition Yes Yes Yes Yes Yes No
Windows Server 2003, Datacenter Edition Yes Yes Yes Yes Yes No
Windows XP Professional No No Yes Yes Yes No
Windows CE No No No No No Yes
Windows 9x No No Yes No Yes No

Engine Implementation

The Jet database engine in Access differs from SQL Server in that it is not permanently running as a service as SQL Server does, but is started every time a user opens a Jet database file (.mdb file) using Access or some other data access method. When a user closes an .mdb file and the file is no longer in use, the Jet engine is unloaded from memory.

The key difference is that if there are no users currently accessing the .mdb file, it is possible to copy or move this file to another location using Windows. In the case of SQL Server, the SQL Server service is constantly running and is connected to the SQL Server database files (.mdf files) that are registered with it. To copy an .mdf file, you either have to stop the SQL Server service or detach the .mdf file from the current SQL Server service before it can be moved.

Data Access Models

Access is a client-only relational database management system (RDBMS). This means that all data processing such as sorting and filtering is done on a single computer.

Access developers generally try to emulate the client/server approach by splitting their database. Typically, in an environment where multiple concurrent users use Access, an Access database is set up on each client computer. This database contains forms, reports, saved queries, and Microsoft Visual Basic for Applications (VBA) form code. All data is kept in an Access database on a central server, which is returned to the client machines when requested. This scenario requires extensive resources from both the network and client. This structure is shown in Figure 10.

Figure 10. Split Access database (red indicates workload)

In this scenario, no data processing is done on the server. When a client requests data, the entire data set is sent through the network to the client, and any processing is done on the client machine.

For example, a financial company has a database with a million records in its Accounts Receivable table (Access .mdb file). An Access application wants to display the sum total of the accounts receivable (one calculated field). To achieve this, Access must transfer the entire table over the network and perform the calculations on the workstation.

This can cause serious performance problems on the server and on the network. Multiple requests for large amounts of data will consume server resources, and passing entire sets of data over a network connection will considerably slow the network.

SQL Server, however, is a pure client/server RDBMS. This means that the client and server both share the processing load. The client (for example, a .NET Windows application) sends a request for data with any parameters, and the server performs any sorting and filtering and returns only the filtered set of data to the client. This structure is shown in Figure 11.

Figure 11. SQL Server helps reduce network traffic and server load by distributing processing tasks between the client and server.

Because SQL Server handles all filtering and sorting on the server, only the specified result set is returned. This helps reduce network traffic significantly, because less data is passed to and from the client and server. This also helps reduce the amount of server processing, because it does not have to return as many records as it would in Access.

Data Types

There are several differences in data types between Access and SQL Server. Most of these data types are automatically converted when upsizing, although it is important to verify this in your SQL Server database after upsizing. Table 4 shows the differences in data types between Access and SQL Server. Note that there are also some unsupported data types.

Table 4. Comparing Access and SQL Server data types

Jet (Access) SQL Server
Text char, nchar, varchar, nvarchar
Memo text, ntext
Byte tinyint
Integer smallint
Long Integer integer
Single real
Double float
Replication ID uniqueidentifier
Decimal decimal
Date/Time smalldatetime, datetime, timestamp
Currency smallmoney, money
AutoNumber int + identity property
Yes/No bit
OLE Object image
Hyperlink <no equivalent>
<no equivalent> binary, varbinary
Tip   In Access, auto-number columns are automatically generated as soon as the user starts editing a new record. In SQL Server, the auto-number is only generated when the record is saved. Be careful to redesign any existing logic that depends on the auto-number value in Access.

User-Defined Data Types

SQL Server allows you to define custom data types, called user-defined data types (UDDT). UDDTs are based on existing SQL Server data types. Also, constraints can be added directly to the types to:

  • Specify a default value. (A value that will be entered automatically into a field if no value has been specified for that record.)
  • Set the maximum field size.
  • Set whether the field can be null.

UDDTs become invaluable when specifying fields in tables whose properties may change in the future. For example, if you defined a unique identifier field of base SQL Server data type varchar(15) (string of length 15 characters) and defined all related stored procedures to accept a parameter of type varchar(15), changing the length or data type of the field would become a major maintenance issue. All stored procedures and tables would need to be changed to reflect the changes to the data type.

A better solution would be to create a UDDT called "CodeType", for example, and define the length and base data type in the UDDT. All stored procedures and the table definitions would use this UDDT, so if the field size increased, it would be a matter of changing the definition of the UDDT.

UDDTs are defined through the Enterprise Manager, as shown in Figure 12.

Figure 12. Specifying UDDTs for use in your database objects in SQL Server

Table Design

Tables are represented similarly in both Access and SQL Server. Both database management systems (DBMSs) are relational; that is, related data is stored in logical tables linked by unique identifiers. The table design interface is similar in Access and SQL Server, as shown in Figure 13.

Figure 13. Similar design for tables in Access and SQL Server

Relationships

In Access, you can specify rules on fields in tables, such that when a value in one table changes, values in related tables will automatically update (cascaded update).

In SQL Server, you can create the same rules through the diagram designer in Enterprise Manager (as shown in Figure 14). SQL Server supports five classes of constraints:

  • NOT NULL. Specifies that the column cannot contain a NULL value.
  • CHECK. Restricts the values that can be entered into a column. The following code creates an Employee table that adds a CHECK constraint to the Salary field so that the value is between 10,000 and 1,000,000. 
    CREATE TABLE Employee
    (
    EmployeeID          int        PRIMARY KEY,
    Name               char(50),
    Address          char(50),
    Salary         money,
    CONSTRAINT chk_Salary CHECK (Salary BETWEEN 10000 and 1000000)
    )
  • UNIQUE. Ensures all values in a column for a table are unique. This is commonly used for ID columns.
  • PRIMARY KEY. Identifies the column or set of columns whose values uniquely identify a row in a table.
  • FOREIGN KEY. Sets the relationships between tables. The following code creates an EmployeePosition table that references the EmployeeID in the previously created Employee table. 
    CREATE TABLE EmployeePosition
    (
    EmployeePositionID    int        PRIMARY KEY,
    EmployeeID           int        FOREIGN KEY
    REFERENCES Employee(EmployeeID)
    ON DELETE CASCADE
    Position          char(50)
    )

    Figure 14. SQL Server supports similar relationships to Access

The ON DELETE clause has two options:

  • CASCADE. Specifies that if an employee's record is deleted from the Employee table, any record with a matching EmployeeID in the EmployeePosition table will also be deleted.
  • NO ACTION. Specifies that the EmployeePosition record will not be affected if its referenced parent record in the Employee table is deleted.

SQL Server also supports the ON UPDATE clause, which specifies the action to be taken if a parent record is updated. It also supports the CASCADE and NO ACTION options.

Note that relationships in SQL Server are not as flexible as they are in Access. In Access, you can:

  • Cascade, update, or delete update from a table to itself.
  • Cascade, update, or delete update foreign keys in a table where the Required property is set to Yes.

Although SQL Server does not support these two options, this may lead to more robust databases that are less prone to relationship and key problems.

Cascading Update Circular References Not Supported

Unlike Access, SQL Server does not permit circular referential integrity. Say for example, there is a senior employee in a company's sales department. In the database, the employee's Employee Type is Senior and Category is Sales. However, in the database, the Employee Type Senior is in the Sales Category. As shown in Figure 15, the database structure to permit this creates a circular reference, and SQL Server does not allow it. You will receive something similar to the following error if you attempt to create circular update constraints:

Unable to create relationship 'FK_EmployeeType_Employee'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_EmployeeType_Employee' on table 'EmployeeType'
may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.

This is because there is the potential to cause an infinite loop if one field is updated in any one of the tables. In this example, updating one CategoryID field would cause the next CategoryID field to be updated (due to Cascading Update referential integrity), which would cause the next CategoryID field to update, and so on.

Figure 15. Circular cascading update constraints cause errors in SQL Server.

To circumvent this issue in SQL Server, you will need to remove the referential integrity constraints from the tables, and create a trigger on each table to perform the updates. For more information about using triggers, see Enforcing Business Rules with Triggers.

Improvements to Indexing

In Access, indexes can be built on one or many fields in a table, known as a composite key.

SQL Server handles indexing in much the same way. Indexed tables are actually sorted on the hard disk and stored in sorted order. This is called clustering. Clustering refers to SQL Server sorting and storing data on the hard disk based on the clustered index. If a field is indexed and not clustered, SQL Server must first query the index to find the data, which can slow performance.

For example, an Employees table could have a unique identifier called EmployeeID. However, this table is mostly searched based on the FirstName field. Data access is optimized for the FirstName column by defining an index on the EmployeeID field and setting its clustered property to true (as shown in Figure 16). Because it is clustered, it is physically stored on the hard disk in sorted order, making data access more efficient.

Figure 16. Setting a table index to use clustering in SQL Server for performance benefits

Access Queries vs. SQL Server Views

Views in SQL Server are similar to queries in Access, as shown in Figure 17 and Figure 18. They allow you to specify a filtered set of data, potentially collated from multiple tables and other views.

Views are useful for handling security-related issues. For example, if you want a group of users to be able to view information about a product order, but not the credit card details linked to the payment, you would:

  1. Create a view that only retrieved the non-sensitive fields from the orders table.
  2. Deny any access to the orders table by the group of users.
  3. Allow access to the view by the group of users.

    Figure 17. Queries in Access

    Figure 18. Views in SQL Server

Views, unlike queries, can also take advantage of indexing, which can significantly improve the performance of an application, whereas queries frequently perform certain joins or aggregations. An indexed view allows indexes to be created on views, where the result set of the view is stored and indexed in the database.

Access Queries vs. SQL Server Stored Procedures

SQL Server uses stored procedures to query and perform calculations on data. The main advantage of stored procedures is that they are compiled the first time they are run. This means that SQL Server will calculate the most optimal way to execute the stored procedure and store this execution plan in memory. Subsequent execution of the stored procedure will be much faster, because SQL Server has already worked out the best path to take to run the query.

Stored procedures are created and modified within the SQL Server Enterprise Manager, much like Access queries are edited in Access (see Figure 19). Stored procedures are similar to Access queries in that they accept input parameters.

Figure 19. Stored procedures to query and perform calculations on data

Because stored procedures are written in T-SQL, they offer an advantage over Access queries because conditional logic and calculations can be used to modify or return data or perform some other function, as shown in Figure 20.

Figure 20. Using T-SQL to perform conditional logic and calculations within queries

With SQL Server, you can also debug your stored procedures, which is helpful when working with stored procedures that contain complex business logic. The debugger supports setting breakpoints, defining watch expressions, and creating step-by-step procedures, as shown in Figure 21.

Figure 21. Advanced query debugging in SQL Server

Access Queries vs. SQL Server User-Defined Functions

Along with built-in functions in SQL Server, you can also specify custom blocks of T-SQL statements. These are known as user-defined functions (UDFs). Implemented in much the same way as functions in programming languages, UDFs are a powerful feature that allows code reuse and encapsulation of business logic. UDFs can return a single (scalar) value, or return a table.

Scalar UDFs

For example, you could write a UDF to accept a money value, perform tax calculations, and return the tax-inclusive price. This function could then be called from any stored procedure that required a tax calculation.

Table UDFs

SQL Server 2000 introduced a table data type, which can return a data table from a function. Using table data types in your UDFs is much more efficient than creating and dropping physical tables to perform queries on subsets of data. They are stored and manipulated in memory and do not require any disk access.

For more information about user-defined functions, see User-Defined Functions.

Triggers on Tables and Views

SQL Server has added support for triggers. Triggers are stored procedures that execute when data in a table is updated, deleted, or inserted. Triggers can be set to run when a specific row or field is updated. Note that triggers can be used to enforce referential integrity much like constraints. However, constraints are more efficient than triggers and should be used whenever possible.

Triggers can be used to perform custom actions when data in a table changes. For example, you could set up a trigger to compare the inserted or updated data to data in another field in another table, and update that data accordingly, or display a custom error message. For more information about using triggers to enforce business rules, see Enforcing Business Rules with Triggers.

Triggers can be created through SQL Server Enterprise Manager and in a Visual Studio .NET database project, as shown in Figure 22.

Figure 22. Triggers created in a Visual Studio .NET database project

Scalability and Performance

SQL Server offers significant improvements over Access for scaling your database solution to meet increased business demands. Also, improved client/server architecture distributes the processing load and results in faster performance.

Support for More Concurrent Users

Access supports a maximum of 255 concurrent users, and as such is not a feasible enterprise-level data storage solution. In a production environment, it is common to experience major performance issues as well as data corruption with as few as 20 users attempting to use the Access database simultaneously over a network.

SQL Server supports a concurrent user base that is limited only by available system memory, and because of its optimized query processing engine and ability to simultaneously use multiple computers, processors, and hard drives, it can scale to meet any enterprise requirements.

Support for a Larger Database

Access supports a maximum database size of 2 GB plus linked tables. Although use of linked tables theoretically enables you to store much more data, it is common to experience performance issues and network problems due to the amount of data being processed. For more information, see the Engine Implementation section earlier in this paper.

SQL Server has vastly improved storage capabilities, allowing for 1,048,516 terabytes of data to be stored efficiently across multiple devices.

Log Files Keep a Record of All Database Activity

SQL Server has an advantage over Access in that all transactions (database updates, insertions, and deletions) are kept in a log file. This log records the changes to the data and enough information to later undo the modifications made during each transaction, if necessary.

Tools such as Lumigent Log Explorer allow you to look into a SQL Server transaction log and undo transactions manually (see Figure 23). For more information, see the Lumigent Web site.

Figure 23. Lumigent Log Explorer provides full control over your SQL Server database by looking at all past transactions.

Database and Log Files Split Across Multiple Devices

Access databases are stored as single .mdb files. As such, they can only be stored and run on a single machine. This can cause issues when the database and user base grow, because the processing power and storage space become constrained by the hardware on the single database server.

Databases in SQL Server are a group of physical files managed by SQL Server. These files comprise, as a bare minimum, a transaction log file (with extension .ldf), and a primary data file (with extension .mdf). SQL Server databases can also have one or more secondary data files (with extension .ndf). The primary data file is used as the starting point for the database and contains data and references to the secondary data files.

When working with a large database, storing the transaction log and multiple data files on separate computers enables you to harness the processing power of multiple computers. It also helps you use storage space across multiple computers or hard disks.

More Robust Queries

  • Access developers may have encountered an Out of Memory or Query too Complex error when attempting to run a query, a form, or a report based on a query. This usually occurs because the query you are attempting to execute contains more table joins than Access can handle. To circumvent this issue, Access developers often are forced to waste resources in redesigning queries and restructuring tables.

SQL Server has been redesigned to support much more flexible queries. Inside a single query, you can use up to:

  • 256 tables in the SELECT statement
  • Approximately 256 KB of query text
  • 4096 columns in the SELECT statement

It is also important to note that Access supports up to 50 nested subqueries, whereas SQL Server supports a maximum of only 32.

Working with Data

Creating data queries in Access is different from creating data queries in SQL Server. There are differences in the query language and the query designer. SQL Server also supports stored procedures, a flexible and efficient way to store data queries, as well as user-defined functions, which facilitate reuse of business logic. Also, SQL Server provides a much more powerful failure recovery model than Access.

Querying Data

Query Optimization

When data is queried remotely in Access, all of the data is returned to the client, and any filtering and sorting is done on the client side. Because SQL Server data is usually queried over a network from a client, major network bandwidth issues can occur. Therefore, when moving your back end to SQL Server, it is important to redesign your queries so as to return only the required set of data to the client (rather than the whole data set). For example, a query behind an Access form would be:

SELECT * FROM Customers

The previous query would return the entire Customers table when the form was opened. In SQL Server, this query would have to be optimized to only return the current record. The SQL query would be in the form:

SELECT * FROM Customers WHERE CustomerID = 'C00010'

This would return just one row/record. Every time the user navigated to the next or previous record in the form, the CustomerID would change, and the database would need to be re-queried to retrieve the current record.

This server-side filtering method helps to reduce network traffic by performing the filtering and sorting on the database server and only returning the minimum amount of required data.

Query Types

Access provides several methods to view and design queries for your data. Table 5 lists the possible options when migrating built-in Access query types to SQL Server.

Table 5. Options for converting your queries from Access to SQL Server

Access query type SQL Server migration options
Select A SELECT statement can be used in a T-SQL file, a stored procedure, or a view. SELECT statements can also be designed using the built-in SQL Server query designer, which is similar to the Access query designer (see Figure 24).
Crosstab A crosstab can be implemented as a T-SQL file, a stored procedure, or a view. Temporary tables can be used to query the data sets required for the crosstab in memory. The temporary tables can be joined and queried to retrieve the required crosstab data.

Converting Access crosstab data to work in SQL Server can be a lengthy task. You may consider a third-party application to automate some of the steps.

A more flexible, efficient, and extensible solution for crosstab queries is SQL Server Analysis Services. Using Analysis Services, you can build online analytical processing (OLAP) data cubes to enable the generation of complex, dynamic reports. For a detailed explanation of using SQL Server Analysis Services on your data, see Analysis Services.
Make table A make table can be implemented as a T-SQL statement that uses the SELECT INTO clause to copy data from one table to another.
Update An update statement can be stored as a T-SQL statement or a stored procedure that uses the UPDATE clause.
Append An append statement can be stored as a T-SQL statement or a stored procedure that uses the INSERT INTO clause.
Delete A delete statement can be stored as a T-SQL statement or a stored procedure that uses the DELETE FROM clause.

Figure 24. Designing SELECT queries, similar in Access and SQL Server

Query Language Features

Table 6 summarizes the main differences in query language features supported in Access and SQL Server (excerpted from the Access 2002 Desktop Developer's Handbook by Paul Litwin, et al, SYBEX Inc., 2001).

Table 6. Access and SQL Server differences in data queries

Feature Supported by Access SQL with Jet 4 SQL-92 Extensions Supported by SQL Server 2000 T-SQL
Security (GRANT, REVOKE, and so on) Yes Yes
Transaction support (COMMIT, ROLLBACK, and so on) Yes Yes
Views (CREATE VIEW) Yes Yes
Temporary tables No Yes
Joins in FROM clause Yes Yes
Joins in UPDATE and DELETE statements Yes No
Support for FULL OUTER JOIN and UNION JOIN No Yes
Support for subqueries in the SET clause of UPDATE statements No Yes
Support for multiple tables in DELETE statements Yes No
SELECT DISTINCTROW Yes No
SELECT TOP Yes No
Cursors (DECLARE CURSOR, FETCH, and so on) No Yes
Domain support (CREATE DOMAIN, ALTER DOMAIN, and so on) No Yes
Support for check constraints Yes Yes
Assertions (CREATE ASSERTION, DROP ASSERTION, and so on) No No
Row value constructors No No
CASE expressions No Yes
Full referential integrity support in CREATE TABLE statement No Yes
Standardized system tables and error codes No No
Standard data types Yes Yes
Standard string operators No Yes
Standard wildcard characters Yes Yes
Support for VBA functions Yes No
Additional aggregate functions Yes No
TRANSFORM statement Yes No
Parameters in queries or stored procedures Yes Yes
SELECT INTO statement Yes Yes

For more information about designing your Access queries in SQL Server, see Migrating Your Access Database to Microsoft SQL Server 7.0. (Note: This article was written for SQL Server 7.0 and was not updated.).

Ability to Script Objects

Structured Query Language (SQL) is the standard language used by Access and SQL Server for data access and manipulation. The latest revision to the SQL language is called SQL-92, named for the year it was completed. Microsoft has added some of its own extensions to the base SQL language, which vary between the two DBMS solutions.

Access supports SQL-92 plus Jet 4 ANSI-92 extensions, which add support for managing transactions using SQL.

The Jet 4 ANSI-92 extensions also add support for easy management of database security. However, some features, such as setting and changing database object ownership, are not supported.

In SQL Server 2000, Microsoft has added custom extensions to the base SQL-92 language. These extensions add script support for some important features, such as:

  • Stored procedures
  • Distributed transactions
  • Operating system functions
  • More flexible subqueries
  • Aliases in queries
  • Backup and restoration of data

The T-SQL language is a powerful extension to the standard SQL set of commands. It provides all the functionality necessary to:

  • Retrieve, modify, delete, and add data to database tables
  • Accept and return parameters
  • Perform calculations
  • Run built-in and user-defined functions
  • Copy data between servers

T-SQL is like a cross between Access queries and VBA in that data queries can be combined with conditional logic and calculations.

Note that SQL Server completely supports the SQL-92 standard, so that use of the extensions is not required.

Table Variables: Useful for Complex Queries

If you want to perform calculations in Access on a set of joined tables, you create a query defining the joins. In an application using that data, every time this query is used in an SQL SELECT statement, all of the tables need to be rejoined, which is potentially a resource-intensive operation (particularly in a multiple-user environment).

For example, if you want to delete all customers whose first name starts with the letter "A", and delete all the customer orders and order histories, in Access you would:

  1. Create a SELECT query that gets all the required Customer IDs: 
    SELECT Customers.CustomerID
    FROM Customers
    WHERE Customer.FirstName LIKE 'A%'
  2. Wrap the previous SELECT query into three DELETE queries to delete all required customers, orders, and order histories: 
    DELETE FROM Orders
    WHERE Orders.CustomerID IN
    (
    SELECT Customers.CustomerID
    FROM Customers
    WHERE Customer.FirstName LIKE 'A%'
    )
    And
    DELETE FROM OrderHistory
    WHERE OrderHistory.CustomerID IN
    (
    SELECT Customers.CustomerID
    FROM Customers
    WHERE Customer.FirstName LIKE 'A%'
    )
    And
    DELETE FROM Customers
    WHERE Customer.FirstName LIKE 'A%'

This is an inefficient way to perform this operation, because a resource-intensive LIKE filter would have to be run on the Customers table for every delete operation. Performing such wildcard character WHERE filters would present major performance issues should the Customers table grow to millions of records.

A more efficient way to perform this operation is to use table variables, a feature available in SQL Server. Table variables are used like regular tables in SQL syntax. However, they differ from regular tables in that they are stored temporarily in memory, and not on the hard disk. Because memory access is significantly faster than hard disk access, table variables become useful when performing multiple operations on the same set of filtered or joined data.

To implement the previous example using a table variable, you would:

  1. Declare the table: 
    DECLARE @tmpCustomerIDs TABLE (CustomerID nvarchar(50))
  2. Get the filtered set of records and store them in the table variable: 
    INSERT INTO @tmpCustomerIDs (CustomerID)
    (SELECT CustomerID FROM Customers WHERE Customers.ContactName LIKE 'A%')
  3. Perform all delete operations on customers, orders, and orders histories using values from the table variable: 
    DELETE FROM Orders
    WHERE Orders.CustomerID IN
    (
    SELECT CustomerID
    FROM @tmpCustomerIDs
    )
    And
    DELETE FROM OrderHistory
    WHERE OrderHistory.CustomerID IN
    (
    SELECT CustomerID
    FROM @tmpCustomerIDs
    )
    And
    DELETE FROM Customers
    WHERE Customers.CustomerID IN
    (
    SELECT CustomerID
    FROM @tmpCustomerIDs
    )

Temporary tables are another mechanism offered by SQL Server to efficiently perform operations on a dynamic set of data. Unlike table variables, they stay in memory longer and so can require more locks on data and logging resources.

Recovering from System Failure

Most Access developers have encountered an Unrecognized database format error when attempting to open a corrupt database (as shown in Figure 25). When a system failure occurs (such as an operating system failure or power outage), your options are:

  • Attempt to recover data from the corrupt .mdb file using the Access compact and repair tool, and then import the recovered data into a blank database to minimize the number of corrupt records. This is not a failsafe process, and data can be lost.
  • Restore from a recent backup, which would result in wasted resources used to re-enter lost data.
  • Run the Jet compact tool, Jetcomp.exe. This is often more effective than running the compact and repair tool. However, there is still no guarantee that all data will not be corrupted.
  • Submit your corrupted database to a third-party database recovery specialist, who will use proprietary methods to extract data from the database. This is potentially a costly process, and can have security implications because external parties will be handling your data.

    Figure 25. Error when attempting to open a corrupted Access database

SQL Server offers much more control over data recovery. You can select one of three recovery models for each SQL Server database to determine how your data is backed up and what your exposure to data loss is. The three recovery models are:

  • Simple Recovery. Allows the most recent backup to be recovered.
  • Full Recovery. Allows the database to be recovered to the point of failure. This model requires the most system resources and disk space (for logging).
  • Bulk-Logged Recovery. Allows the database to be recovered to the point of the last log backup. This requires less system resources and disk space than the Full Recovery model, although there is a higher chance of having to re-enter data manually.
  • These recovery models give you the flexibility to select the best way to recover from system failure balanced with availability of system resources.
Tip   The major advantage SQL Server data backups offer over Access backups is that they can be done while the database is running, without requiring users to log off. This increases the availability of the database to users and allows a much higher uptime.

Comparing Databases

In Access, keeping your live production database up-to-date with the latest structural changes is an ongoing project. The database needs to be taken offline quickly to make structural changes and convert data, but this can be difficult when people are relying on the system. The data conversion can also take time because new fields and relationships might have been added.

To make structural changes to an Access database, the following usually takes place:

  1. The developers work on the Application Database and make structural changes to the database containing the data.
  2. Changes to the database containing the data are tracked and Update Queries, DAO, or ADO code is written to make the update.
  3. After development is complete, the databases need to be taken offline while updates are made manually.

Third-party applications such as SSW Data Renovator help minimize system unavailability and reduce the likelihood of mistakes by automating part of this process. SSW Data Renovator compares the new database with the production database, generates reports on all the differences between the two, and provides a wizard-style interface to automatically move data into the new structure.

Although SQL Server has the benefit of not requiring the database to be taken offline to make structural updates, database administrators still must:

  • Analyze all database schemas and change logs for structural changes.
  • Manually create migration scripts to push to the target database.

Third-party utilities, such as Red-Gate SQL Compare or SSW SQL Deploy help automate this task by:

  • Comparing all objects in both databases, including stored procedures, relationships, tables, views, and user-defined functions
  • Reporting on all differences
  • Generating migration scripts that can then be run directly on the target database

Conclusion

Microsoft SQL Server 2000 is an enterprise-level database solution with vastly improved scalability, maintenance, and database recovery features in comparison to Microsoft Access 2002. Because it is based on client/server architecture, SQL Server is quite different from Access in the way it processes and sends data over a remote connection. SQL Server also offers many features to make the task of querying data, reusing business logic, and backing up data more simple and flexible.

Glossary

ADO.NET
A data access model provided with the Microsoft .NET Framework. It is designed specifically for the Web with scalability, statelessness, and XML in mind.
Client/server architecture
A software architecture that promotes scalability by allowing multiple clients to make requests and receive results from a central server or group of servers. The processing load is shared between the client and the server.
Clustering
A method of indexing and sorting data directly on the hard disk to enable extremely fast querying of data.
Data Transformation Services
A tool supplied with SQL Server that allows you to import data to and export data from various data sources that use an OLE DB-based architecture, such as Microsoft Excel.
OLAP
Online analytical processing. A data storage model to help you analyze your business data from different points of view. For example, you can use OLAP to view all products sold in a certain region above a certain price during a certain time period.
SQL Server Enterprise Manager
A tool supplied with SQL Server to provide easy management of database objects, users, backups, and database permissions.
SQL Server Profiler
A tool supplied with SQL Server to help you optimize queries by identifying any database transactions that are particularly lengthy or resource-intensive.
SQL Server Query Analyzer
A tool supplied with SQL Server to enable writing and debugging of database queries.
T-SQL
Transact-SQL. An extension of the SQL-92 query language standard, providing extended features in SQL Server, such as stored procedures, backup and restoration of data, and distributed transactions.
UDDT
User-defined data types. A feature in SQL Server that allows you to create your own data types based on existing SQL Server base data types. UDDTs promote more strict business rules on data.
UDFs
User-defined functions. Custom blocks of T-SQL statements that facilitate reuse of business logic throughout your database application.
Visual Studio .NET
An integrated development environment (IDE) so that developers can visually build a broad range of Microsoft .NET-connected applications. It provides powerful tools for designing, building, testing, and deploying .NET-enabled Web and Windows applications.
XML
Extensible Markup Language. A widely adopted standard way of representing text and data in a format that can be processed without much human or machine action.

posted on 2009-02-14 23:30  Ja  阅读(825)  评论(0)  编辑  收藏  举报

导航