Building a SQL Server System Architecture to Support Multiple Migrated Microsoft Access Databases

Writers: Paul Delcogliano

Technical Reviewer: Matt Nunn

Applies To: SQL Server 2000 Service Pack 3a (SP3a)

Summary: This document guides database administrators and developers through the architectural design choices to be made during Microsoft® Access to Microsoft SQL Server™ migration, specifically for scenarios where multiple Access databases are being migrated.

On This Page

Who Should Read This
What You Must Know
Introduction
Migration Strategies
SQL Server Backup and Restore Architecture
Security Scenarios
Data Integration Issues
Cost Considerations
Conclusion

Who Should Read This

This paper provides guidelines for database administrators, architects, and developers interested in migrating Microsoft® Access databases to Microsoft SQL Server™. Read this paper if you are responsible for designing and developing multiple database applications using Microsoft Access and interested in migrating them to SQL Server.

What You Must Know

To use this paper to build SQL Server 2000 solutions, you should have experience designing and developing database applications with Microsoft Access or SQL Server. For more information about SQL Server, see https://www.microsoft.com/sql.

Introduction

As a desktop database, Access is well suited for small, departmental applications. These applications may start as one user’s project. For example, an employee realizes that productivity can be increased if a paper-based process is automated with an Access application. Other users in the department recognize that they can take advantage of the application if additional features are added. As more features are added, more employees run the application. As time goes by, more and more Access applications are deployed for different business units.

With increased use, the limitations of a desktop database become apparent. Access security, performance, and disaster recovery features are not robust enough to manage an enterprise-level application. Because you need a new solution, you decide that the applications should be migrated to SQL Server.

Migrating Access applications to SQL Server is a relatively straightforward process. Access provides a tool, the Upsizing Wizard, to help you perform the migrations. However, you will need a plan before you begin the upsizing process. This paper discusses different migration strategies and reasons for choosing one strategy instead of another. It focuses on a range of architectural decisions and scenarios, and presents guidelines to help you choose the most appropriate approaches and techniques.

Migration Strategies

Migrating from Microsoft Access to a SQL Server 2000 solution involves substantial planning. The first element to consider in the plan is how to best arrange the databases in SQL Server.

The architectural approaches described in this guide are based on two common Access scenarios. The first scenario involves related decentralized or distributed Access databases throughout your enterprise. In this scenario, an identical full-blown Access application, complete with forms, reports, and perhaps security, is installed at branch locations. The decentralized nature of the application forces headquarters to merge all branch office data into a central Access database on a periodic basis, so that company reports can be generated and appropriate analysis performed. This scenario, shown in Figure 1, will be referred to as the Distributed Access scenario throughout this paper.

Figure 1   The Distributed Access scenario, a typical distributed Access application with many Access databases acting as part of one application

In the second scenario, separate unrelated Access databases run independent of each other. Departments create databases for their specific needs. For example, the Sales department uses one database, and the Human Resources department uses another. Each of these databases is self-contained. Business logic, security, and reporting for each database are maintained independently. Databases do not share common data, application logic, or reporting requirements. This scenario, shown in Figure 2, will be referred to as the Departmental Access scenario throughout this paper.

Figure 2   The Departmental Access scenario, with separate unrelated Access databases dispersed throughout an organization

Three possible migration strategies exist for these Access application scenarios. In the first strategy, all Access databases are aggregated into one SQL Server database. With the second strategy, each Access database is migrated into its own SQL Server database on a single instance of SQL Server. The third migration strategy employs SQL Server named instances. Each Access database is migrated to its own SQL Server instance.

The migration strategy chosen greatly affects the architectural decisions you make during migration. Decisions like choosing a SQL Server edition, selecting backup and recovery options, implementing security, consolidating data between all of the Access databases, and issues about performance and application architecture, are based on the migration strategy chosen. The details of each migration strategy are discussed in the following sections.

Migrating to One Database on the Default SQL Server Instance

Multiple Access databases migrated to one SQL Server 2000 database, shown in Figure 3, is the best option for Access applications that are classified in the Distributed Access scenario described previously. Centralizing data into one SQL Server database eases database management and makes all data available in real time from any location (security permitting).

Figure 3   Migration strategy 1, with aggregation of decentralized Access applications into one SQL Server database

Database management is simplified by having all data on one server and instance. One administrator can manage all of the data and users. A simplified disaster recovery plan (discussed later) can be created for the database on the server.

Planning this migration involves preventing duplicate data and properly securing the data. The security that existed in the decentralized Access databases needs to be maintained after the centralized architecture is implemented. Your migration plan should include designs for a new application to support the centralized database.

Migrating to Many Databases on the Default SQL Server Instance

This migration strategy, shown in Figure 4, is essentially a one-to-one mapping of Access databases to SQL Server databases. A SQL Server database is created on the default SQL Server instance for each Access database migrated.

Figure 4   Migration strategy 2, with independent Access databases mapping to SQL Server databases created on the default instance

Managing each database is slightly more involved than in the first migration strategy (one centralized SQL Server database). In this strategy (many SQL Server databases on the default instance), multiple databases need to be maintained. Disaster recovery may be unique for each database. Your migration plans should include decisions about security. Authentication, which was originally performed by the individual Access databases, needs to be thoughtfully planned. You don’t want users from one database to have access to the other databases on the server.

This migration is relatively straightforward. Some existing application logic may be reused, requiring changes only to database connections. Having all of the databases on one instance means each database shares in the memory pool available to SQL Server. Additionally, all of the databases share the same SQL Server system tables.

Relatively small Access databases, such as those described in the Departmental Access scenario, qualify for this migration strategy. These databases ordinarily have minimal management, security, and performance requirements, easing their migration to SQL Server under this strategy.

Migrating to Many Databases on Many Instances

This migration path, shown in Figure 5, uses multiple named instances to migrate the Access databases to SQL Server. Each Access database is installed on its own named instance. Named instances maintain their own copies of the system and user tables. Security can be configured differently for each instance, preventing unwanted users from having any access to a particular instance.

Figure 5   Migration strategy 3, with each Access database created on a new named instance, so that databases operate independently of one another

Named instances are an excellent solution to perform server consolidation, application hosting, or for technical support. Having many instances on one computer means you need fewer operating system licenses. Each instance can have its own administrator, making multiple instances a good solution for hosting companies who host SQL Server space.

This strategy uses instances to segment users, keeping administrators of one database restricted from accessing other instances on the same server.

SQL Server Instances

The strategies described previously mention default and named SQL Server instances. For you to determine if your architectural plan should implement named instances, you need to understand what a SQL Server 2000 instance is. SQL Server 2000 supports two types of instances, default and named. A SQL Server 2000 instance is a copy of the database engine running on the server. A maximum of 16 instances can be running on one server at a time.

The default instance operates the same way the SQL Server database engine did in earlier versions of SQL Server. All databases in the default instance share memory and authentication mode settings, among other things. The first two migration strategies listed previously use the default SQL Server instance.

Named instances are multiple copies of the database engine running concurrently on the same server. Named instances have their own system and user databases, network connection address, and memory pool, and run independently of each other even though they are installed on the same physical server. The third migration strategy enlists named instances in its architectural solution. Named instances, in effect, logically reflect deployment of databases to multiple physical servers, but consolidate these multiple SQL Server instances on a single computer.

The decision tree, shown in Figure 6, highlights some of the choices you will make to determine which migration strategy you should apply to your solution.

Figure 6   Migration strategy decision

The management and performance capabilities of your SQL Server solution will be greatly affected by the SQL Server edition you choose to perform the migration. Not all features are available in all editions of SQL Server. Table 1 lists editions and some of their available features. The list of SQL Server editions omits mention of SQL Server Developer Edition, which is discussed later, and cannot be used in production. It only includes those editions suitable for use in your migration plans. For more in-depth information about the differences between SQL Server editions, see https://www.microsoft.com/sql/evaluation/overview/default.asp.

Table 1   Database engine features for various SQL Server editions

Database engine feature	Enterprise Edition	Standard Edition	Desktop Engine (MSDE 2000)
Multiple instance support	Supported	Supported	Supported
Failover clustering	Supported	Not applicable	Not applicable
Log shipping	Supported	Not applicable	Not applicable
Federated database server	Supported	Not applicable	Not applicable

The differences between each edition are briefly described:

• Enterprise Edition fully supports all features. It is well suited for large Web sites, data warehouses, or high volume transactional applications.

• Standard Edition provides full scale database capabilities. It is useful for department or workgroup applications.

• MSDE 2000 is freely available and distributable. This edition has a concurrent workload governor. The workload governor is designed to limit the performance of the database engine any time more than eight operations are running at the same time. For more information about MSDE, see https://www.microsoft.com/sql/msde/.

A Developer Edition is also available. All of the features available with the Enterprise Edition are available with the Developer Edition. The Developer Edition is only licensed for use during development and test. It cannot be deployed in a production environment.

Figure 7   Features and functionality for various SQL Server editions

Purchasing SQL Server 2000 Standard Edition or Enterprise Edition requires that you purchase the appropriate software license. Microsoft offers three different SQL Server 2000 licenses:

• Processor license. Designed for Web-based or Web-based and Windows-based applications. A single license is required for each CPU in the operating system instance running SQL Server.

• Server plus device CAL. Favorable for applications that are not Web based. The server running SQL Server requires a license. Each client device requires a license to connect to the server.

• Server plus user CAL. Favorable for applications that are not Web based. The server running SQL Server and each user require a license. Users can connect to SQL Server from various devices, such as desktop computers, laptops, or personal digital assistants (PDAs).

For more information about SQL Server 2000 licenses, see SQL Server 2000 How-To-Buy.

Figure 8   Licensing requirements

The feature set required by your application should be used to determine which edition of SQL Server you deploy. Match your feature list with the appropriate SQL Server edition to select the best edition for your solution. In most cases, either the Standard Edition or Enterprise Edition is an appropriate solution. MSDE 2000 can be synchronized with Standard Edition or Enterprise Edition to create distributed applications. Database developers will find that the Developer Edition provides all of the functionality they need to create robust database applications. By listing the features your migrated application must support, you can choose the proper SQL Server edition.

All editions of SQL Server provide robust options for backing up and restoring your databases. These are discussed in the next section.

SQL Server Backup and Restore Architecture

Your strategic migration plan should include plans for disaster recovery. In some cases, backing up and restoring SQL Server databases is only slightly more complex than backing up Access databases. SQL Server offers several different recovery models. The models are listed in order of exposure to data loss, from highest loss to lowest loss:

• Simple Recovery

• Bulk-Logged Recovery

• Full Recovery

The various SQL Server recovery models offer different levels of protection against data loss. Designing a disaster recovery solution involves analyzing the database’s availability and recovery requirements. When designing your solution, ask the following questions:

• Must your application maintain 100 percent availability? Can the database be offline during any part of the day?

• What is your financial cost if the database is down?

• What amount of downtime is tolerable if you experience media failure, such as a disk drive failure?

• What are the consequences of lost data?

• Can lost data be easily reloaded?

• Do you have system or database administrators?

• Is bulk data loading used to populate tables on a regular basis?

The answers to these questions will help you determine which recovery model to implement. Each model addresses different requirements for performance, disk and backup media space, and data loss protection. When you choose a recovery model, you must consider the tradeoffs between the following business requirements:

• Exposure to data loss

• Disk space required for the transaction log

• Backup and recovery procedures requiring little maintenance

The Simple Recovery model allows the database to be recovered to the point of the last backup. You cannot restore the database to the point of failure or to a specific point in time using this model. Simple Recovery is best suited for migrated Access databases used strictly for reporting or that do not have heavy transaction demands. Simple Recovery is not a recommended solution for production databases where loss of data is intolerable.

The Bulk-Logged Recovery model provides protection from media failure combined with the best performance and minimal log space usage for certain large scale or bulk copy operations. Bulk copy operations involve moving large amounts of data into or out of SQL Server tables, or views using certain T-SQL commands or command-line tools. Bulk-Logged Recovery minimally logs bulk copy operations. If the log is damaged or bulk operations occurred since the last backup, that data will be lost. Therefore, point-in-time recovery is not available with the Bulk-Logged Recovery model. Access applications that import data from files are excellent candidates for Bulk-Logged Recovery. Data lost between the last backup and bulk copy operation can quickly be restored by reimporting the files.

For complete recovery from media failure, use the Full Recovery model. The Full Recovery model uses database backups and transaction log backups to provide complete protection. It uses the most disk space of the three models, but provides the greatest level of disaster recovery. If one or more data files are damaged, recovery can restore all committed transactions. Transactions that were in-process at the time of the failure are rolled back. Full recovery is the optimal solution for migrated Access applications requiring point-in-time recovery. Applications in this category are those that can’t afford to lose transactions and have dedicated administrators who can quickly restore data using the appropriate backup sets in the event of a failure.

Choosing a SQL Server Recovery Model Based on Migration Strategy

Table 2 summarizes the benefits and tradeoffs of data loss exposure of each recovery model.

Table 2   SQL Server recovery models

Model	Benefits	Data loss exposure	Point in time recovery	Access database candidates
Simple	Keeps space requirements small by reclaiming log space. Permits bulk copy operations.	No transaction log so must redo changes since the most recent database or differential backup.	Can recover to last backup. Changes made after backup must be redone.	Large databases, with little administrative requirements.
Bulk-Logged	Transaction log space not reclaimed but bulk operations still use minimal log space. Permits bulk copy operations.	If log is damaged or bulk operations occurred after last backup, changes must be redone.	Can recover to last backup. Changes made since the last backup must be redone.	Large databases that can quickly reload data from bulk copy operations.
Full	No work will be lost because of a lost or damaged data file, although disk space requirements are higher than for Simple or Bulk-Logged. Can recover to a random point in time (for example, before application or user error).	In usual cases, none. Changes made since the most recent log backup must be redone, if the log is damaged.	Can recover to any point in time.	Migrated applications with smaller databases or which require complete protection from media failure.

We recommend that each Access database and its corresponding workgroup information file be backed up. On the SQL Server side, there are system databases that should be backed up regardless of the recovery model chosen. Your disaster recovery plan should include regular backups of the following system databases:

• Master

• Msdb

• Model (if modified)

• Distribution (when server is configured as a replication distributor)

In the event of a system or a database failure resulting from a hard drive failure, you may need to restore these system databases.

Depending on the migration strategy chosen, you may be required to back up more than one set of system databases. Recall that the third migration strategy uses multiple SQL Server instances. As mentioned previously, instances have their own copies of master, msdb, model, and distribution databases. Each instance’s copies of these databases should be included in the backup plan.

The decision tree, shown in Figure 9, highlights some of the choices you will make regarding your application’s data loss and recovery requirements.

Figure 9   Data loss and recovery requirements

You can set the recovery model differently for each database on SQL Server. One way to set the recovery model is to view the database’s properties in SQL Server Enterprise Manager. For migration strategy two (many SQL Server databases on the default instance) and strategy three (SQL Server named instances), a mixture of recovery models may be appropriate.

Figure 10 shows the database properties dialog box. On the Options tab, there is a combo box that allows you to set the recovery model for the database. Choose the desired recovery model and click OK.

Figure 10   Northwind Properties dialog box, from the SQL Server Enterprise Manager client tool

Maintaining High Availability

Disaster recovery is an important part of any migration strategy. System availability is another. Building redundancy into the SQL Server solution can be done by using a few different features.

One reason you may be migrating to SQL Server is because of the inadequate performance of Access in zero downtime environments. Access applications supporting a Web site or high volume transactions are in this category. Any downtime experienced by these applications can result in lost revenue and potentially lost customers. Your migration plan should include a discussion and analysis of the application availability requirements.

SQL Server solutions can be designed to be available all the time. SQL Server provides features for maintaining highly available systems including:

• Failover clustering

• Log shipping

• Transactional replication

These features are designed to provide zero downtime for your database applications. Failover clustering and log shipping are only available with the Enterprise Edition of SQL Server 2000 (and the Developer Edition during development).

Failover clustering offers high availability through the use of multiple servers clustered together. SQL Server 2000 clusters consist of multiple servers (the actual number of servers is limited by the operating system) sharing a common set of resources, like hard disk drives. Data is stored on a common disk drive. All servers in the cluster have access to the disk drive.

If one server in the cluster fails, or is brought down for routine maintenance, the application does not need to be taken offline. Another server in the cluster automatically comes online and will continue processing query requests. To implement a failover cluster, you will need Windows Clustering, available with Microsoft Windows Server™ 2003, Enterprise Edition, Windows Server 2003, Datacenter Edition, Windows® 2000 Advanced Server, or Windows 2000 Datacenter Server.

Table 3 lists some of the advantages and disadvantages of using failover clustering.

Table 3   Failover clustering advantages and disadvantages

Advantages	Disadvantages
High server availability. Failover automatically occurs if primary server fails.	Extra expense. Requires multiple servers with additional licensing costs. No protection against disk array failures. Servers should be in the same location.

Failover clustering is an excellent solution for applications that require automatic failover capabilities. An example of an application that requires failover protection is a bed-and-breakfast reservation system built in Access on one server. If the server fails, the bed-and-breakfast cannot take reservations, resulting in lost revenues. However, with failover clustering, a secondary server would be brought online and continue to take reservations while the primary server was not operating.

Where manual failover is acceptable, log shipping or transactional replication should be employed. When a server is not operating, your administrator brings the standby server online.

Log shipping is the process of sending transaction logs to a server not used during ordinary operations. This technique involves a warm standby server. The logs are restored on the server, keeping the production server and standby server synchronized. The standby server is manually brought online if the production server fails as a result of a disaster. While the standby server is restoring transaction logs, the database is in exclusive mode and is unusable.

Table 4 lists some of the advantages and disadvantages of using log shipping.

Table 4   Log shipping advantages and disadvantages

Advantages	Disadvantages
Recovery of all database objects, such as security permissions or table schema changes, is possible. Fast data recovery.	The database is unusable during the restore process. Cannot apply changes to select database objects. Restore process restores all objects. Manual failover.

Log shipping is a good solution for those applications where minimal downtime is acceptable. For example, consider an Access application that provides sports results on a time-delayed basis. If the server fails when migrated to SQL Server, the delay between the server failing and manually restoring the warm standby server minimally disrupts operations. Users will be temporarily inconvenienced but the statistics company will not lose revenue from the server that is not operating.

With log shipping, some data may be lost because of the latency between transaction log backups. Your exposure to losing data is determined by the length of time between transaction log backups. Longer intervals between backups increase your exposure to data loss.

SQL Server offers another option for maintaining a warm standby server. Its transactional replication feature allows you to copy, distribute, and modify data across your enterprise. Transactional replication is supported by all SQL Server editions. However, MSDE 2000 can only be a subscriber.

Transactional replication (not to be confused with Merge replication, which is discussed later) operates by replicating the data on one server (known as the publisher) to one or more servers (known as the subscriber). It can be implemented on a table level, giving it more granularity than log shipping. Changes made to table schemas or security permissions are not replicated. For this reason, transactional replication is not a preferred method for maintaining a standby server for disaster recovery. The preferred method is to use log shipping.

Migrated Access applications that will benefit the most from transactional replication are those that are experiencing performance problems because of heavy reporting from a transactional database, and which need better fault tolerance. By using transactional replication, you can offload the application’s reporting to another database, with the added benefit that if a failure occurs on the transactional database, you can manually change to the reporting database and continue ordinary operations.

Table 5 lists some of the advantages and disadvantages of using transactional replication.

Table 5   Transactional replication advantages and disadvantages

Advantages	Disadvantages
Data can be read on the subscriber while changes are applied. Can be implemented at the database object level, such as a table.	The use of OLE DB or ODBC to apply changes decreases performance. Manual failover.

The decision tree, shown in Figure 11, highlights some of the choices you will make to determine your application’s availability requirements.

Figure 11   Availability requirements

After deciding how available your application needs to be, the next step in the migration plan is security. Duplicating Access security models in SQL Server presents challenges. The next section discusses security models as they pertain to the various migration strategies and Access scenarios.

Security Scenarios

Planning security is an important part of your migration. Differences between the Access scenarios influence your security migration plans. The Distributed Access scenario requires user consolidation and careful construction of roles. The Departmental Access scenario entails securing separate databases from unauthorized access.

Windows and SQL Server Security

Your plan should include decisions about which authentication model to deploy. SQL Server supports two authentication models, Windows authentication mode (Windows authentication) and mixed mode (Windows and SQL Server authentication). Authentication modes can be switched between Windows and mixed mode at any time from the Enterprise Manager SQL Server Properties dialog box, as shown in Figure 12.

Figure 12   Authentication modes

Remember that the authentication mode setting applies to all databases in a SQL Server instance, either the default instance or a named instance. You cannot specify different authentication modes for databases in the same instance. If you need different authentication modes for different databases, you will need to create each database in its own instance.

Windows authentication integrates security with the underlying operating system. Mixed mode allows users to authenticate to SQL Server using either Windows accounts or SQL Server accounts. In mixed mode, SQL Server maintains a database of user accounts. This list of users is not associated with a Windows account. SQL Server users authenticating in a mixed mode environment can supply SQL Server account information to successfully connect to the database, and do not have to have their Windows logon explicitly authorized in the database.

The preferred, more secure method of authentication for applications without a Web interface is Windows authentication. For Web applications, SQL Server authentication is the better choice. The main reason is that Windows mode would require a Windows account on your network for each Web user to log on to the database. The administrative overhead associated with maintaining so many Windows accounts can be overwhelming and limits scalability.

Beyond declaratively granting or denying access to users, groups, and roles (discussed later), you can programmatically grant or deny access to data. T-SQL stored procedures and functions can determine who the user is, or what group the user belongs to. The stored procedure can be constructed to return only the data related to the user. For example, if sales data from all branches is stored in one table, a stored procedure can include the user name as a criterion in its where clause to restrict the result set to contain only records belonging to the user (or group).

Security Considerations for Migration Strategy One

In the Distributed Access scenario, each Access database maintains its own security. Because the first migration strategy (one centralized SQL Server database) involves consolidating distributed Access databases into one SQL Server database, your most important challenge will be to reproduce the security model brought about by physical separation.

In the Distributed Access scenario, users in one branch were restricted from other branch data by physical access. For example, users in the North branch’s database could not see data in the South branch’s database because they didn’t have physical access to the database. Figure 13 depicts this security model.

Figure 13   Security considerations for Distributed Access scenario

In the SQL Server environment proposed by migration strategy one (one centralized SQL Server database), all of the data and users are combined into one database, as shown in Figure 14.

Figure 14   Aggregated application database

To replicate the Access security model caused by the former physical separation in SQL Server, you will need to implement role-based or group-based security. Roles are applicable in mixed mode, and groups apply to Windows mode. For migration strategy one (one centralized SQL Server database), Windows mode is the best option because the application is most likely available to internal users only (for example, those within the corporate firewall).

Regardless of which mode you select, the implementation of each is the same. Roles and groups are collections of users. The only difference is that roles are collections of SQL Server users, and groups come from the Windows operating system and are collections of Windows users. The rest of this section uses the word group to stand interchangeably for a group or a role, to discuss security.

After a group is created, it is then granted or denied permissions to database objects. All users in a specified group inherit the permissions of the group.

Figure 15   Conceptual security model, with users segregated into groups

In most cases, a group can be created that corresponds to a branch office. For example, users who work in the North branch could be added to a group called NorthBranch. Permissions for this group would be set up, and stored procedures written so that users in the group would have access to data pertaining to the North branch, and be restricted from data in other branches.

Security Considerations for Migration Strategies Two and Three

The security requirements for the remaining migration strategies (many SQL Server databases on the default instance or SQL Server named instances) are relatively straightforward. Users from your Access applications should map one-to-one to user security accounts (Windows or SQL Server). Groups or roles can be created to mimic any security groups your Access applications had.

In multiple instance scenarios, each instance maintains its own system databases. Because these security requirements may be different for each instance, granting and denying permissions can be customized to each SQL Server instance.

In the Departmental Access scenario, the Sales Access database has its own administrator, the Human Resources database has its own administrator, and so on for all of the databases in the scenario. Because migration strategy two uses one SQL Server instance to store all of the migrated databases, one SQL Server administrator has control over all of the databases in the instance.

The most important security difference between strategy two (many SQL Server databases on the default instance) and strategy three (SQL Server named instances) is that strategy three provides more latitude for restricting administrator access to a database. Each instance can have its own administrator, thus preventing, for example, the Sales administrator from accessing the Human Resources database.

Firewall Considerations

Firewall issues need to be considered when using migration strategy three (SQL Server named instances). If SQL Server is configured to use TCP/IP as the network protocol, you may need to adjust port access in your corporate firewall. Each SQL Server instance uses a different TCP/IP port. The default instance uses port 1433. However, port assignments for named instances are user configurable. If you don’t assign a port number during installation of the instance, a port number is automatically assigned by SQL Server.

Web applications that connect to a named instance require the named instance’s port number to make a successful connection. If this is the case, you have to open the instance’s port on your corporate firewall for the application to communicate with the database.

The decision tree, shown in Figure 16, highlights some of the choices you will make to implement security in your migrated applications.

Figure 16   Security requirements

Data Integration Issues

So far, migration in terms of server management has been discussed. Discussions about SQL Server editions, security issues, and server availability contribute to a robust migration plan. However, your plan must also include discussions about data migration.

Depending on your migration scenario, importing Access data into SQL Server may be as simple as upsizing an Access table to a SQL Server table, or as complicated as consolidating data from multiple Access databases into one SQL Server database. There are several solutions available to you that import Access data into SQL Server. The Access Upsizing Wizard, and the SQL Server Bulk Copy and Data Transformation Services (DTS) are among them. Although any one of these solutions can be used to import data, your specific migration strategy will determine which solution to employ.

The Access Upsizing Wizard or Bulk Copy should be used for migration strategy two (many SQL Server databases on the default instance) or strategy three (SQL Server named instances).

These methods are best used when a direct mapping exists between an Access table and a SQL Server table, and no data transformation is necessary.

The most challenging scenario to import data is the Distributed Access scenario. In this scenario, you will deal with issues surrounding duplicate data. For example, each branch may have its own lookup tables. Common lookup tables must be aggregated into one table in the proposed database. To eliminate the effects of duplicate data, your plan should include a detailed analysis of the data in each branch database.

DTS is a recommended solution for importing data from multiple Access databases in the Distributed Access scenario. By using DTS scripting capabilities, you can interrogate the data before importing it. The interrogation process could include script code that determines if the record is a duplicate. Depending on how you write the script, duplicate records could be deleted or stored in a temporary table for investigation later.

Figure 17   Creating and editing DTS packages by using the DTS Package editor

DTS transformations have several options available that make them appealing for this scenario, such as the name of a file to store rows with exceptions, how many errors to allow before stopping the import process, and how many records to process in a batch. Figure 18 shows some of the options available to a transformation task.

Figure 18   Transform task properties

In addition to the options available to each transformation task, the DTS package has several options, including which script file to execute and a setting for the task’s priority that adds to its appeal as a solution in this scenario. Figure 19 shows the workflow options.

Figure 19   Workflow options

The decision tree, shown in Figure 20, illustrates some of the choices you will make regarding data migration in your migrated applications.

Figure 20   Data migration requirements

Cost Considerations

As you evaluate each migration strategy, remember that costs are important in your final decision. The costs associated with each strategy are higher as the strategies become more complex. The SQL Server editions, from least expensive to most expensive, are:

• MSDE 2000 (free)

• Standard Edition

• Enterprise Edition

In addition to software and licensing costs, hardware costs rise as your solution implements more enterprise features. For example, if your solution requires failover clustering, you will need a second server. You will incur all of the costs associated with purchasing a new server, including items such as operating system licenses and extended warranties. Another potential hardware cost comes from within your organization. Some information technology companies bill their departments for the amount of space used by the department’s databases.

Running your applications with multiple instances also affects your hardware costs, although not necessarily negatively. Servers with multiple instances require larger hard drives, and more memory and processing power to run each instance simultaneously. Servers configured for multiple instances will cost more than those servers that are only running the default instance. However, overall costs may actually be reduced because having one robust server running many instances could be less expensive than purchasing many servers, each to run its own instance.

Maintaining the server and administering the database requires at least one database administrator (DBA). Access administration requirements are relatively simple compared to SQL Server. An experienced DBA or DBA team will increase payroll costs. Developers experienced with SQL Server development will also increase payroll.

The simplest way to reduce costs is to reduce the complexities of the application environment. Build redundancy into one server instead of using clustering for fault tolerance. One server with redundant hardware is less expensive than two servers arranged in a cluster. A side benefit to removing the cluster from your solution is that you may be able to use the Standard Edition instead of the Enterprise Edition of SQL Server (assuming you don’t require any other enterprise features). The Standard Edition is less expensive than the Enterprise Edition.

A well documented, tested disaster recovery plan is essential. Not only can it save your job in the event of a disaster, but it can reduce costs associated with application downtime. The less time the application is unavailable, the lower total costs incurred.

Conclusion

A successful migration from multiple Access databases to SQL Server will only occur if you have a good plan. Knowing all of the variables in advance will enable you to perform the migration efficiently. This paper is intended to make you aware of some of those variables. When designing your migration plan, be sure to include discussions of the following issues:

• Access application architecture. Are the Access databases distributed or decentralized?

• SQL Server architecture. Does your application benefit from using multiple SQL Server instances?

• Licensing models. How many concurrent users or devices will be connected at a time? Does it make sense to purchase a processor based license?

• Backup and recovery models. What level of data loss are you comfortable with? How easy is it to re-create data from bulk import operations?

• Server or application availability. What level of downtime are you (and your users) willing to accept?

• Security. Should you use Windows or mixed mode SQL Server security? Does the data need to restrict access to certain users?

• Data migration. Do you need to perform special processing, like removing duplicates from your data, before importing it from Access into SQL Server?

• Costs. Which SQL Server version is best suited for your application environment? Will you need additional DBAs to maintain the database?

The most important decision you will make is what SQL Server configuration you need to migrate the Access databases. After that determination is made, the rest of the migration process should go smoothly.

Figure 21 summarizes all of the decision points discussed in this paper.

Figure 21   Summary of all decision points discussed in the paper