Managing Microsoft SQL Server 2000 after Migrating Multiple Microsoft Access Databases to a Single SQL Server 2000 Database

Writer: Martin Reid

Technical Reviewer: Matt Nunn

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

Summary: This article discusses some of the issues that you might encounter before and after you have moved Microsoft® Access databases to Microsoft SQL Server™ 2000. This article highlights how the built-in management and performance features of SQL Server 2000 and the Database Management System solve most—if not all—of the performance problems.

Furthermore, this article discusses some hardware and staffing issues you might encounter after you migrate multiple Access databases to SQL Server 2000. In many cases, servers that were adequate before the move may not be adequate after databases, transactions, and users have increased in number.

On This Page

A Quick Look at Some Numbers
Reasons for Moving Your Access Databases to SQL Server
Performance and Administration
Roles
Monitoring for Migrated Databases in SQL Server
Other Issues
Conclusion

A Quick Look at Some Numbers

How many Microsoft® Access databases can be moved? The maximum number of databases that a single SQL Server installation can host is 32,767. Microsoft has stress-tested 4,000 databases on a single instance of SQL Server and 8,000 databases on multiple instances of SQL Server.

Reasons for Moving Your Access Databases to SQL Server

You are probably considering bringing your Access databases into one or more instances of SQL Server. Several sound business reasons and technical reasons support doing this:

• If you have multiple databases that use the Jet engine, you need a current license for each database. In addition, many organizations may use Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) for local storage. Moving your Access databases that use Jet or MSDE onto SQL Server can reduce the license costs for an organization.

• High availability features of SQL Server impact costs. Has your organization wasted resources trying to recover Access databases that use Jet? Many Access applications have outgrown their original specification. For example, small businesses or departments may use Access to develop applications that become popular with users, causing the Jet engine to accommodate an increase in use. When you have a few users, Access is an appropriate tool for many jobs. However, it does not scale as well as SQL Server, which is designed to be accessible to thousands of users.

• In organizations with many Access databases, moving to SQL Server allows central control and, most importantly, central management of all desktop databases. Instead of multiple approaches to database development and management, organizations can handle everything from a single source. Staff who may have spent considerable time working with Access databases that use Jet, (which in most cases is not their primary role), will be free from such responsibilities. This can now be handled by professional information technology (IT) staff, with a more automated approach to management issues.

• In addition to databases that use Jet, an organization may use MSDE-based applications. This makes issuing software updates difficult, relying on nontechnical users to update their own instances of MSDE. Moving these applications directly to SQL Server solves the problem of applying updates to individual instances of MSDE, because there is only one instance of SQL Server to update.

Performance and Administration

SQL Server 2000 contains tools so that an administrator or developer can adjust the database system to perform better for a large number of users. SQL Server also contains tools to automate data backup and secure data, and many other features that are not available in Jet and MSDE. If your organization has Access databases with critical corporate data and you have more than a few users, or if you need to provide access to that data to users who are not connected to your internal network, SQL Server provides a compelling solution.

Access is designed to be a desktop database solution that works best with a low number of concurrent users. Many developers do not permit more than 25 users on professionally designed Access applications. Access also has limited backup features. Although you can create a backup using Microsoft Office Access 2003, this only creates a backup of the .mdb file, similar to copying a Microsoft Word document and saving it to a new location within the file system. Access does not support point-in-time restore nor can you provide exception reports to a named operator, although a developer can create these features.

Access does not natively have performance monitoring features. You can design your own or purchase third-party tools. SQL Server 2000 contains many tools that you can use to both monitor and improve the performance of databases, such as user-friendly graphical query plans, more complex command-line tools available using ISQL, and stored procedures to provide information about server operations.

SQL Server 2000 provides wizards that you or a database administrator can use to monitor and tune performance. Some of these tools, such as the Index Tuning Wizard, are discussed in this document.

Connecting to Your Corporate Data through the Web

You can use Access to create data-driven Web sites. Access supports XML, and therefore you can use it with both classic Active Server Pages (ASP) and the newer .NET languages. However, Access doesn't scale as well as SQL Server, so Access is only a solution for small sites. If you want a true Web-enabled database, Access does not have the Web-enabled analysis tools that are standard with SQL Server. In addition, SQL Server provides a better solution for Web data transaction throughput and security.

OLE DB

After moving the Access databases to SQL Server 2000, many developers use ODBC data source names (DSN) to maintain the connection between the SQL Server 2000 back end and an Access front-end application installed on the users' computers. This option is a simple way to begin the conversion process to full SQL Server 2000 applications.

This setup results in minimal impact on existing internal Access code modules. You may leave this setup in place for months while developers become familiar with the new setup. Other developers move directly to OLE DB because it supports more connections. Moving to OLE DB can require major code modification to reconfigure connection strings within the application. You can anticipate some application rewrite as you move from Data Access Objects (DAO) to ActiveX® Data Objects (ADO). Why does this approach require more work? The DAO language used for Microsoft Visual Basic® for Applications (VBA) modules in older Access applications is optimized for use with the Jet engine.

Should you rewrite applications to communicate with SQL Server 2000 through ADO and OLE DB? For large multiuser applications, the answer is yes. Your investment in time and resources is well rewarded with improved performance. You will send less data by using stored procedures to process data on SQL Server 2000. If you make the investment, you could consider using ADO.NET and build an ASP.NET or Microsoft Visual Studio® .NET solution to replace the Access interface. For more information about migrating from Access to .NET, see the <insert link to Access to .NET papers>.

Query Optimization

We recommend that you convert all Access queries to stored procedures so you can use the database server instead of a client for query processing. Stored procedures are precompiled before the first execution. Each additional execution of the procedure can take advantage of the query plan created by SQL Server 2000.

In addition, stored procedures help you manage the data by taking complex logic once contained in VBA modules and making that logic shareable across applications. Stored procedures also allow developers to include conditional logic within the syntax of T-SQL, thus removing this logic from VBA modules. This means you can change a single stored procedure, and its changes are immediately available to all applications using the database. Stored procedures also improve security, because you can control access to data using permissions granted to users and applications to execute stored procedures. Additionally, it is more secure to have your stored procedures perform data manipulation instead of permitting your users to have direct table access.

SQL Server provides tools to help you optimize queries, such as graphical execution plans (discussed in the next section) and a Query Analyzer tool that provides quick access to many of the built-in functions available using SQL Server.

There are a number of query optimization choices available when planning the move to SQL Server 2000, including the ability to use an Access Data Project (ADP) as the front end to SQL Server 2000. During the process, the Upsizing Wizard converts many of your queries to SQL Server 2000 objects (such as stored procedures, views, and user-defined functions) if you choose this option.

After you have moved your Access tables to SQL Server 2000, it is common practice to retain the relationship to the Jet engine by linking Access front ends to SQL Server 2000 tables instead of using ADPs. The Upsizing Wizard can provide a fast mechanism to convert query objects to your new SQL Server 2000 installation and databases in preparation for a full application move to SQL Server. Using these tools makes the management of this process easier.

For example, you can create a backup copy of the linked database and use it to convert the queries to SQL Server 2000 objects. ADPs can use graphical tools to create objects such as stored procedures, views, and user-defined functions in a familiar Access development interface—a timesaver for Access developers new to SQL Server 2000.

SQL Server 2000 provides an Import Wizard, used to transfer objects from one SQL Server 2000 database to another, or from an Access database. Figure 1 shows the wizard importing several stored procedures. Note that you can import multiple database objects using this tool, a timesaver when working with databases that duplicate functionality, which is likely after multiple Access databases have been moved. For more information, see <Link to access upsizing papers>.

Figure 1   The Import Wizard importing stored procedures

Understanding Graphical Execution Plans

SQL Server 2000 provides several tools for viewing execution plans and investigating poorly performing code. Access can use the Jet engine SHOWPLAN to check queries. However, SQL Server 2000 has more sophisticated tools, such as the graphical execution plan. After you move many Access databases to SQL Server, it is likely that your database administrator (DBA) will encounter many Jet-based queries executing against multiple databases and perhaps servers. Tools such as the graphical execution plan and SQL Server Profiler help make the management of the tracing and query optimization process easier.

To view the execution plan, click Query, and then click Show Execution Plan from the main menu (Figure 2).

Figure 2   Click Show Execution Plan from the Query Plan menu

The code example that follows uses a SELECT statement to return data. Figure 3 shows the execution plan for the query, which returns 10,997 rows.

SELECT
fldConditionID,fldAssetModel,fldAssetNote
FROM
tblAsset

Figure 3   The Graphical Execution Plan with details about the plan

Information about each stage in the execution of a particular statement is available using the graphical execution plan. Placing the pointer over a stage in execution, as shown in Figure 3, provides several layers of information:

• Physical operation. The number of page reads from the disk, not including pages already in the cache.

• Logical operation. The total number of page reads including pages held in cache.

• Row count. The number of rows output by the query, such as the number of rows returned by a specific join.

• Estimated row size. An estimation of the row's size.

• I/O cost. The estimated I/O cost of the statement. Keep this as low as possible for performance reasons.

• CPU cost. CPU activity for the specific operation.

• Number of executes. How many times the specific operation was executed.

• Cost. The cost of the specific operation in percentage terms over the entire query cost.

• Subtree cost. The cost of the specific and all preceding operations in this subtree.

Using the Profiler

When running multiple databases, it is important to be able to view exactly what is happening and how communication between your application front end and the SQL Server 2000 back end actually affects the server. SQL Server 2000 Profiler provides the tools to do this job.

Using SQL Server Profiler, you can run a trace on server activity down to the Microsoft Windows® user level, provided you use Windows authentication. (Security is discussed later in this paper.) SQL Server Profiler also checks the performance of your front-end application. Figure 4 shows the range of events that Profiler can trace.

Figure 4   Profiler showing the range of traceable events

You can feed the trace results to the Index Tuning Wizard, giving an actual view of activity against the database tables. (The Index Tuning Wizard is discussed later in this paper.)

To get the maximum benefit from the process, Profiler should run when the database operates in an actual situation. There is little point in running traces using Profiler while you are the only user of the database. Any statistics produced will be different after you have 40 or 50 users working in the application.

Using Performance Monitor

Performance Monitor is a tool included with the operating system. When you install SQL Server 2000, several counters are added to Performance Monitor that are specific to SQL Server 2000. Figure 5 shows the list of SQL Server-specific counters added to this tool.

Figure 5   Performance Monitor listing SQL Server performance counters

The most important of these counters include:

• SQL Server General Statistics. Logons, logoffs, and user connections.

• SQL Server Cache Manager. The ratio between cache data reads and actual physical disk reads.

• SQL Server Memory Manager. The amount of memory that SQL Server is currently using. High use of available memory can cause performance to drop. The best response to this is to buy more RAM.

• SQL Server Buffer Manager Page reads/sec. The number of physical reads for the server per second.

• SQL Server Buffer Manager Page writes/sec. * *The number of physical page writes for the server per second.

These are a small number of the performance counters available in SQL Server 2000. There are 17 groups of monitors, each with its own individual counters.

File Groups

One performance consideration is the physical location of the data file. In Access, there is a single data file (.mdb) or, in a large system, several linked Access databases. The SQL Server 2000 file group provides a sophisticated way to choose the location for tables and for other data files, and is particularly useful on a multiple disk system. Instead of having all data files in one area of a disk, you can divide data files across several disks. For example, you can place Customer and Order tables in a primary file group and Lookup tables in a secondary file group, speeding up data access. Using multiple file groups creates the same increase in performance as using redundant array of independent disks (RAID).

The default setup using SQL Server 2000 is a single file group. You create multiple file groups manually. Manual creation of file groups may also be required when organizations use the Access Upsizing Wizard to move to SQL Server 2000. Using the wizard, you place all data files into a single file group, the primary file group. If additional file groups are required for performance or organization reasons, they must be created manually.

Using and Tuning Indexes

Indexes depend not only on primary key values assignment but also on the workload of the server, the amount of data being held, the number and type of queries, and the number of joins required to get the data to the user or the application.

There is no single answer about how to improve performance based solely on index use, because each application is different. SQL Server 2000 provides assistance in the form of the Index Tuning Wizard, discussed later in this section, to fine-tune performance based on the day-to-day workload of the server. Another way to find out exactly what indexes have been created on migrated database tables is to run the system stored procedure sp_helpindex using the Query Analyzer. This returns index information on the table passed in, such as executing the procedure and passing in the table name. The test table returns the following information:

• index_name. IX_testtable.

• index_description. Clustered, unique, located on PRIMARY.

• index_keys. CustomerID.

There are two index types available in SQL Server 2000: clustered and nonclustered indexes.

Clustered Index

A clustered index stores the data in the same physical order as the index keys. Instead of using a pointer to locate the data within a page, the clustered index is the actual data and is referenced much like a child node in a tree architecture. There can only be a single clustered index per table, because the data can only be in a single order as defined by the index key.

By default, when you upsize an Access database to SQL Server 2000, each primary key column in the Access database is created in SQL Server without a clustered index. This primary key-based index does not always yield the best performance in SQL Server 2000, and you should consider whether it might be better to choose another column of data to be the clustered index before making the primary key the clustered index. The best way to choose an index is to measure the work actually performed by the database. The Index Tuning Wizard and the tracing tools used by SQL Server 2000 are the best tools for this job.

Clustered indexes work best on columns used within range criteria, because it is highly likely that after you find the beginning of a range, such as a start date, the remainder of the range, because of the physical sorts of the data, is on the same page.

If you have used the Upsizing Wizard to bring your Access databases to SQL Server 2000, one of the first jobs is to redesign tables to use appropriate indexes. For example, using the Northwind database, when the Customers table is upsized using the Upsizing Wizard, the primary key field CustomerID is created, but without a clustered index. (See Figure 6 showing the table in Design mode after converting to SQL Server 2000. In Design mode, the table properties—including indexes—are available and can be manipulated.)

Figure 6   The upsized Northwind Customers table in Design mode

Note that a unique constraint has been created on the Customers table, as opposed to an index. A unique constraint ensures that no duplicate data can be entered into the column.

Note also that the name of the index is aaaaaCustomers_PK. You should rename the index to something more meaningful if you have to directly manipulate indexes using a language like ADO where the index is referred to by name.

Other indexes on the new table are also created. You should review all of the new indexes to ensure that they still meet the original requirements. The graphical tools available in the SQL Server 2000 Enterprise Manager speed up the process of checking, amending, and adding indexes. To view existing indexes and to create new ones, open a table in Design view, right-click the table, and select Indexes/Keys from the shortcut menu. Figure 7 shows the Customers table in Design view with the Index property available. From this screen, you can amend existing indexes such as changing the Customer index from nonclustered to clustered. Using Design view, you can also create additional nonclustered indexes on the table.

Figure 7   Viewing the index construction's properties

Nonclustered Index

Each table can have multiple nonclustered indexes. In a nonclustered index, instead of pointing to a primary key or other indicator, the index is the data. In upsizing, this saves a little disk space (because clustered indexes are duplicated instead of moved, like nonclustered indexes), but queries return more slowly. More indexes mean more management of the table and its data. A nonclustered index contains a pointer to the data values. After a table has been upsized to SQL Server 2000 using the Access wizards, all existing indexes are converted to nonclustered indexes.

Using the Index Tuning Wizard

The Index Tuning Wizard can provide recommendations for using indexes. The advice from the wizard is based on an overall view of the database and its workload.

The wizard requires a workload file, which is a trace carried out by SQL Server 2000 on a particular database. (For details, see Using the Profiler earlier in this paper.) Based on the results of this trace, the wizard recommends the best index structure. Using this tool on poorly performing databases that you have moved from Access can be valuable when managing access and query problems.

Figure 8 shows the Index Tuning Wizard loading a trace file before running. The wizard is available in Enterprise Manager by clicking the Tools menu, and then clicking Wizards. In the resulting wizard's dialog box, expand the Management tree and click Index Tuning.

Figure 8   The Index Tuning Wizard, which loads a trace before running

Backup and Restore, and System Maintenance

With many hundreds of small, perhaps locally critical, Access applications, backup can be a problem for large organizations. Unless you locate your Access databases on a centrally supported file server, you cannot be certain that databases are being backed up regularly. One major advantage of moving your Access databases to SQL Server is that you can use a central backup strategy. It is possible not only to back up single or multiple databases, but also to back up all databases on a particular server at one time. It is often a better practice in terms of system performance to back up a set number of databases over the working day as opposed to all databases at one time. Using SQL Server, you can schedule backup jobs to take place automatically at fixed times during the day without DBA involvement. You can also set up your job to notify your DBA by e-mail should a problem arise during any of the processes. Access provides no comparable backup features to those available using SQL Server, other than copying the .mdb file each day using the operating system.

SQL Server 2000 can use transaction logs to provide full point-in-time restoration of databases. You can restore the database to the exact time of failure, a feature not available using the Jet engine.

Recovery Models

SQL Server provides three different recovery models that you can use to recover data.

Simple Recovery

This model permits you to recover the database to the last full backup, but not to the specific point in time the failure occurred. Use Simple Recovery when you are working with many hundreds of databases, because it requires the least administration. This backup method is practical if you perform backups, but not so frequently as to affect usual system performance.

Full Recovery

Full Recovery allows you not only to back up the database, but you can also record all changes made since the last full backup. Using Full Recovery, you can recover the database to the point of time a failure occurred.

Bulk-Log Recovery

Bulk-Log Recovery combines the Simple Recovery and Full Recovery methods. Not all inserts to the database are logged—only enough to allow the database to be recovered. Because of this, point-in-time recovery is not possible.

The backup and recovery method you use will depend to a large extent on how an organization is using SQL Server and will differ between organizations. Models are flexible and can be changed by executing the ALTER DATABASE command in Query Analyzer.

Maintenance and Management

SQL Server 2000 also provides several tools that can assist with general system maintenance and management. The most commonly used of these is the Database Maintenance Plan. You can create maintenance plans using the Database Maintenance Wizard, and you can schedule them to run at almost any date and time. You can run the maintenance plan against a single database or all databases on a server and:

• Run database integrity checks.

• Update database statistics.

• Perform database backups.

• Move a transaction log to another server.

The Database Maintenance Plan is available under the Management folder in SQL Server 2000. After the scheduled maintenance plan runs, the server can send an e-mail message with the results to the DBA or developer.

The maintenance plan forms part of the automatic administration system used by SQL Server 2000 and is useful, particularly when you have many new databases to monitor and back up.

Windows Authentication

SQL Server 2000 offers a major advance over Access regarding database security, with Windows authentication to manage users and access to multiple databases (using the users Windows Network as part of the server security function). There are two security models available:

• Windows authentication. Using the users Windows Network, user's names and passwords are mapped to SQL Server 2000.

• Mixed mode. Using a combination of the SQL Server 2000 security model and Windows authentication, user's names and passwords are mapped to SQL Server 2000.

We recommend using Windows authentication. If this is not available in your organization, mixed mode must be used. If you can, use Windows authentication.

The Windows authentication model gives you the ability to assign entire Windows user groups to SQL Server 2000, saving time. Instead of granting permissions to individual users, you can grant permission to a specific Windows group. All members of that group inherit the group permissions.

When you use a mixed operating system and network environment, you are restricted to using a mixed security model, either all SQL Server 2000 or a combination of both SQL Server and Windows authentication.

Within many SQL Server databases, you can use a single logon to many different databases, thus helping to ease the management burden of multiple users requiring data access. If you have many separate Access databases, security is difficult to manage and standardize across the entire organization.

Roles

It can be a management challenge to move many new users to the server when you are also moving Access databases. SQL Server 2000 provides a number of security possibilities. The simplest method for addressing multiple users from multiple databases is to use roles. A role allows you to assign many users into functional groupings. For example, you could assign all those with data read permissions who work in the Administration building to a role called Administration. Using roles, you grant database access permissions to the role. Users assigned to that specific role inherit those database access permissions.

Monitoring for Migrated Databases in SQL Server

SQL Server supports automated reporting features to help you manage the Access databases that you move to SQL Server. You can also take advantage of SQL Server agent alerts and SQLMail to reduce manually checking for exceptions on multiple migrated Access databases. If your organization has many hundreds of databases, other monitoring options are available including Microsoft Operations Manager. For further information, see https://www.microsoft.com/mom/.

Other Issues

Up to this point, this paper concentrates on performance and management issues for your newly converted databases. There are other issues to address. For example, non-IT personnel often use Microsoft Access to create small, specific databases. Often, such databases are not properly normalized and contain duplicate copies of data held in central core systems.

The next section addresses this issue and others, including hardware, software, and staffing issues.

Poorly Designed Access Databases

Ideally, a DBA should examine all Access databases before a move, so that remedial work can be performed in advance of any upsizing or migration. However, when you are under pressure to have systems available, there may not be time to perform these operations. Some systems may function well after being upsized, but others may require work to reconfigure table structures and data types after the data is moved to SQL Server 2000. For example, when using the Upsizing Wizard, many data types are converted to nvarchar, which may not be appropriate for your Access data in all cases. All such systems require management from both developers and DBAs.

Professionally Designed Databases

If you upsize professionally designed Access databases to SQL Server, you must address client-side queries, Access-specific VBA, and functions, as you move your Access databases to SQL Server 2000. From a structural point of view, there should be little problem with Access table design, because normalization rules should already be satisfied. Further work on tables may only require changing upsized data types and query names.

One problem is the way Access databases supply data to forms and form controls, such as combo boxes. It is common to find Access applications using unfiltered SQL statements or saved queries to populate forms and form controls. Instead of restricting the records returned to the client, full tables populate many Access form objects. For example, it is common practice to bind a form object directly to a table instead of restricting the records to those actually needed.

After the application has been moved to SQL Server, you should investigate form and recordset performance, because it will have a negative impact on the speed of the application. You might use SSW Performance Pro (a third-party tool available at www.ssw.com.au/ssw), to examine an Access database for performance issues, before you begin the upsizing process.

After you move a database to SQL Server 2000, performance issues may require an adjustment in the coding behind forms, and how such forms and their objects are populated. We recommend that you provide a filter to the form and its objects to restrict the data returned by the server. A user doesn't often need access to a full table of records at one time.

For example, in one converted database, several combo boxes loaded over 32,000 records as a single form opened. This made the application perform somewhat poorly, because all the records were made available at once. To create a better solution, a filter was supplied to a stored procedure (created using the Access graphical tools) and replaced the multiple SQL statements used by the form.

User Databases

You might find hundreds of small Access databases that non-IT staff have created within a single organization. Such staff can be unaware of basic design principles and database normalization.

Some user-built databases duplicate centrally held data, usually with a few fields added to tables, to provide information specific to the local user. Bringing all of an organization's databases into one SQL Server 2000 database is usually the first opportunity to see how much data duplication exists.

Depending on the strategy used by an organization, you should ask several questions about each Access database, preferably before you move it to SQL Server 2000. The questions are also valid following the conversion process, and will directly affect server management:

• What is the purpose of the database?

• Is this database necessary?

• Is this database used only as a local information store?

• Do you need a corporate database to perform this function, or could you use Microsoft Word or some other application?

• Does the database duplicate centrally held information?

• Is it a multiuser database?

• Is the database normalized?

Single user databases are perhaps best left in Access, as are smaller nonessential databases that provide data to specific departments of an organization. The examination of such databases should form part of the initial management survey as you consider consolidation. Databases that are central to the operations of the organization and that contain data essential to the business operation should be in SQL Server 2000 (where it can be more secure and more likely to be backed up).

For redesigning databases to incorporate all the designated data, SQL Server 2000 provides helpful tools and features. Using the diagram tools, you can redesign complete table structures quickly and with little effort. From a graphical window, you can add and amend tables, you can add and change indexes, and you can create and adjust relationships. The Design window in SQL Server 2000 will look familiar to Access developers.

In addition to the graphical design tools available for diagramming, there are multiple wizards and templates, to create objects like Transact SQL, stored procedures, and CREATE TABLE statements.

Making Core Databases Available

In many organizations, data held in large SQL Server 2000 databases is duplicated among smaller, noncore applications. Can an organization's members get the data from these smaller databases without performance and security problems, such as using cross-database joins? SQL Server 2000 provides several mechanisms so that you can share data from multiple databases. Shared data need not be held in SQL Server 2000.

If the noncore databases don't duplicate data held centrally, there are several ways in which to get the information from the core database to the noncore databases for individuals and department users. The most effective method uses the SQL Server Data Transformation Services (DTS). Other methods include read-only replication of databases that make it possible to provide access to other users without giving direct system permissions.

Data Transformation Services

DTS provides a means to access data held within applications in Oracle, Access, Excel, and numerous other data stores, including text files and other instances of SQL Server 2000. DTS provides a means to both import and export data. It is likely that you encountered DTS while upsizing substantial Access databases, because it offers fine-tuning throughout the process. Access does not have import/export tools that are as powerful as DTS. Using DTS makes the process of managing different data stores and making such data available fairly straightforward. You can also use DTS when you move your Access databases onto SQL Server. For example, the ability to move tables and other database objects among SQL Server 2000 databases is a useful feature when moving stored procedures from one upsized database to another. Figure 9 shows the DTS Import/Export Wizard. Note the specific option to move data between SQL Server 2000 databases. You can also import data from Access databases using this wizard.

Figure 9   The DTS Import/Export Wizard, which helps set up the new database

The DTS Import/Export Wizard creates a package that you can execute immediately, or you can execute the package at a specified time. It is also possible to save the DTS package as a Visual Basic file. The following is a Visual Basic .NET code fragment generated by the wizard:

Dim oCustomTask1 As DTS.TransferObjectsTask2
Set oTask = goPackage.Tasks.New("DTSTransferObjectsTask")
Set oCustomTask1 = oTask.CustomTask
    oCustomTask1.Name = "Copy SQL Server Objects"
    oCustomTask1.Description = "Copy SQL Server Objects"
    oCustomTask1.SourceServer = "(local)"
    oCustomTask1.SourceUseTrustedConnection = True
    oCustomTask1.SourceDatabase = "BarTracks"
    oCustomTask1.DestinationServer = "(local)"
    oCustomTask1.DestinationUseTrustedConnection = True
    oCustomTask1.DestinationDatabase = "BarTracks_Version_14w_FESQL"
    oCustomTask1.ScriptFileDirectory = "C:\Program Files\Microsoft

SQL Server\80\Tools"

You can save this file and execute it later, or you can schedule it to run automatically, populating a table with data held in another data store.

Staffing Issues

Any database move results in additional workload for developers and database administrators (DBAs). In moving a large number of smaller Access databases to SQL Server 2000, much of the work occurs before the move. For the most vital applications, rewriting and restructuring tables occurs before they are upsized. Many applications may require work following the move, and the workload of DBAs may increase as a result. There will be more databases to manage, an increased numbers of users, and new performance issues that need to be monitored and addressed. Because of the wide range of management tools available to SQL Server 2000 DBAs, a significant increase in staffing should be avoidable.

It may be most cost effective to employ developers who have prior experience converting complex Access applications to SQL Server 2000 applications and working with such applications after the conversion has taken place.

In many organizations, a small number of SQL Server DBAs are responsible for numerous databases and SQL Server 2000 instances. For example, Microsoft.com has nine DBAs for 1,100 databases running on 125 servers. How many DBAs your organization needs depends on the type of database you are using.

As stated previously, SQL Server 2000 provides graphical tools to assist in server management, thereby reducing the burden on DBAs and other database staff.

Hardware Issues

Your computer hardware can affect a new SQL Server 2000 installation when you merge many upsized Access databases. New investment in hardware depends on the size of the databases and the number of users. Before your upsizing efforts, check the performance of the current infrastructure. Can the hardware handle the additional data and networked users? How much does the organization depend on the databases and how much would a failure cost in lost business or productivity?

It is usually better to have a dedicated SQL Server 2000 server, unless you are working with a small application. You could use a single server if your Access databases are either for single users or for five to ten users who may only use the Access applications on an occasional basis.

For a large database (many users and a large volume of data and transactions), you should always use a disk array. When you consider the cost of disk arrays, compare it to a total data loss. The cost of hard drives is reasonable, and you should use RAID when working with a large database server on which your business relies.

Stored Procedures

In almost every context, with the exception of very small databases, you should convert all Access queries to stored procedures either during the upsizing process or soon afterward. There are two main reasons for this:

• Performance improvement. Stored procedures are precomplied upon first execution.

• Additional security. You can give users rights to use stored procedures instead of direct access to tables, creating a security barrier for your data.

As stated earlier, Access provides graphical tools that you can use to create stored procedures quickly in a familiar environment. You can also use the SQL Server 2000 Query Analyzer to copy existing Access SQL statements and use it to convert to stored procedures. Errors are highlighted and can be easily corrected before statements are wrapped in CREATE PROCEDURE statements, like this:

CREATE PROCEDURE usp_Customers
AS
Select customername from customers
Order by customername

Access developers working with Access SQL and VBA should have little trouble converting their stored procedures. For examples of writing Transact SQL stored procedures, see* *the SQL Server Developer Center on MSDN (msdn.microsoft.com/sql). Also, there are third-party tools available that write stored procedures for you.

SQL Server 2000 creates basic stored procedures using Enterprise Manager. Within Enterprise Manager, several templates help you start using stored procedures and provide examples of correct syntax and conventions.

Conclusion

There are many issues involved when moving multiple (or single) Access databases to SQL Server 2000. Many issues arise before the databases are upsized, but many remain following the process, including performance, security, staffing, and hardware. There are no easy answers, because every project is different. Enterprise Manager, Profiler, Performance Monitor, and other SQL Server 2000 interface tools help with upsized applications.

In some cases, you may need to completely rewrite Access databases to take full advantage of SQL Server 2000. In other cases, especially for smaller systems using ODBC, existing code will work.

There are many advantages in moving to SQL Server, including better performance, greater security, hot backups, and the expediency of using stored procedures, user-defined functions, and other SQL Server 2000 objects to retrieve data for the user. Your initial costs may increase if you need to change or upgrade hardware and operating systems, but a return on investment in increased productivity and security will recover such costs.