Migrate and consolidate Access databases to SQL Server

Reasons for migrating Access databases to SQL Server

Here are the main reasons for moving peripheral Access databases to a SQL Server environment:

• Multi-user access to a database. In time, Access databases could become popular among your users and that can be problematic. An Access database can handle only a few users at a suitable performance level. When more users start to use the application, the Access database does not scale well. SQL Server can handle thousands of users and therefore is ideal for multi-user environments.

• Re-designing and rewriting fewer and better applications. Now might be a good time to rewrite existing applications in order to have fewer and better ones. It is likely that many Access applications were developed by people outside the professional IT department, where Access is not part of the main infrastructure. The code and database were probably not developed according to the existing development standards and may have been poorly designed and coded due to lack of knowledge or lack of time. Your entire organization could benefit from standardizing and unifying applications and data by centralizing common processes.

• Interchanging data with the external world. SQL Server offers better tools to communicate with other environments such as Oracle, DB2 and SAP. Through the Data Transformation Services/Integration Services (DTS/SSIS) packages, data interchange with those environments can be automated or integrated in the existing applications.

• More sophisticated ways to analyze and solve performance problems. The tools include SQL Profiler, execution plans in Query Analyzer, Index Tuning Wizard and the use of filegroups to spread data across disks. SQL Server also has a better locking mechanism built specifically for a multi-user environment.

• SQL Server database security improvements over Access. Advances come with Windows and SQL Server authentication to manage users and access to multiple databases; database roles; and for many ways to grant permissions to the data .

Reasons for ...

To read more you must become a member of SearchSQLServer.com

As an existing member of the TechTarget network please activate your SearchSQLServer.com account 

By joining SearchSQLServer.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.

TechTarget cares about your privacy. Read our Privacy Policy

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server High Availability, Scalability and Reliability SQL Server high availability: Options and caveats High availability and the database Are data warehouses made for the cloud? Top load balancing methods for SQL Server Maintaining high availability of SQL Server virtual machines Creating fault-tolerant SQL Server installations Scaling up vs. scaling out with SQL Server 2008 How to configure storage in SQL Server database with more writes than reads SQL Server database replication tutorial Licensing a standby server for SQL Server replication SQL Server High Availability, Scalability and Reliability Research Microsoft SQL Server Performance Monitoring and Tuning Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Database Management and Administration Using traces in SQL Server Profiler Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V How to create SQL Server virtual appliances for Hyper-V

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

consolidation to a SQL Server environment

The reasons for consolidating the migrated SQL Server databases into a central environment include:

• Central control and central management of the databases. Having an automated and centralized maintenance and backup process where everything is controlled and handled by a single source makes management much easier.

• Moving control of databases and/or applications to the IT department saves time for other departments. In most cases, application development and maintenance is not their primary role.

• Having a centralized source of data for the applications. You can keep and use common data in a centralized location, which avoids the need for duplicate and inconsistent data of the same type.

• High availability. There are a number of methods for keeping a centralized SQL Server instance available, such as log shipping, clustering, replication, data mirroring and database snapshot. Caring for decentralized databases is a very complicated process. You have to save and maintain a backup or a secondary database for each existing database.

• Easier software updates. Upgrading database server versions becomes easier in a centralized location. It is also more comfortable to update centralized data because the update occurs only once. An example would be updating static tables when you need to.

The Access database and SQL Server are from the same vendor and therefore have much in common. The migration is comparably easy and straightforward. Furthermore, you can keep the existing Access application and only migrate the database. This architecture of having Access as the client application and SQL Server as the database -- even if it is just a temporary solution until the application is re-written -- is a very native one and supported by Microsoft as a single point of reference. An Access application can connect to SQL Server through ODBC at first and you can later modify it to work with OLE DB, which provides better performance. However, moving from ODBC to OLE DB Access may involve modifications in the application code.

Some free tools from Microsoft can assist you with the migration process. Here are descriptions for two of the most common migration methods:

• Migrating the database with the SQL Server Migration Assistant for Access
  SQL Server Migration Assistant for Access (SSMA) is a friendly tool that simply produces potential SQL Server schema and its SQL scripts. Through this tool, you can analyze the migrated scripts and objects, make changes and then run the scripts against the SQL Server database. You can download the SQL Server Migration Assistant for Access and find out how to use the tool and what to look for at Migrating Access to SQL made (almost) easy.

• Migrating the database using the Import / Export Wizard
  The native Export/Import wizards in SQL Server 2000 (DTS) and in SQL Server 2005 (SSIS) allow you to migrate the Access database basic schema and data into SQL Server.

Note:

1. If the destination object does not exist, it is created. If the object is a table, it is created without keys and relationships.
2. The queries from the Access database are converted into SQL Server Views.
3. Most of the transformation errors are related to long text fields and dates.

The best migration method is to use both tools: SSMA to migrate the schema and DTS/SSIS to migrate the data.

Here is how you run the DTS/SSIS wizards:
In SQL Server 2000, from the Enterprise Manager:

[IMAGE] Tools ⇒ Data Transformation Services ⇒ Import data. [IMAGE] In the Welcome window, click Next. [IMAGE] In the "Choose a Data Source" window, choose "Microsoft Access" in the "Data Source" list, and provide the Access *.mdb file in the "File name" field. Click Next. [IMAGE] In the "Choose a
destination" form: choose:

• "Microsoft OLE DB
  Provider for SQL Server"
  
• Your SQL Server instance name in the "Server" field
• Authentication information and the destination database name in the "Database" field.

Click Next. [IMAGE] To copy all the objects from the Access database, click Next. [IMAGE] Choose the objects you would like to transfer from Access in the "Source" list and then the destination object names in the "Destination" list. If the Destination name does not exist, the object will be created. You can change the destination object's structure or transform the data passed to it by clicking the "…" in the "Transform" list. [IMAGE] You can run the import
immediately or save it as a SQL Server DTS package for reuse. Click Next. [IMAGE] In the summary page check that the Wizard will do what you asked it to and click Finish. The Import will run. Few objects might fail to be imported. You can double click on the failed line and view the error.

From SQL Server 2005, SQL Server Management Studio:

[IMAGE] Right click on the database ⇒ Tasks ⇒
Import data. [IMAGE] In the Welcome window, click Next [IMAGE] In the "Choose a Data Source" window, choose "Microsoft Access" in the
"Data Source" list, and
provide the Access *.mdb file in the "File name" field. Click Next. [IMAGE] In the "Choose a destination" form, choose "SQL Native Client", your SQL Server instance name in the "Server" field, Authentication information and the destination database name in the "Database" field. Click Next. [IMAGE] To copy all the objects
from the Access database, click Next. [IMAGE] Choose the objects you would like to transfer from Access in the "Source" list (by enabling the specific object or enabling the "Source" header to choose all objects). Choose the destination object names in the "Destination" list. If the Destination name does not exist, the object will be created. You can change the destination object's structure by clicking the Edit Mapping" [IMAGE] You can run the import
immediately or save it as a SSIS package for reuse. Click Next. [IMAGE] In the summary page check that the Wizard
will do what you asked it
to and click Finish.
The Import will run. Few objects might fail to be imported. You can double click on the failed line and view the error.

There are also other methods to export the Access database structure, such as third-party tools such as (i.e., XMLSpy) and ASP.NET 2.0 (http://aspalliance.com/542).

It is a good idea to consolidate department Access databases into a centralized SQL Server environment for scalability, reliability and centralized management. This may help the departmental staff by transferring the responsibility for the applications to the IT group where the tasks probably belong. The probable result will be more robust and better-performing applications.

Please refer to the Microsoft's Managing Microsoft SQL Server 2000 after Migrating Multiple Microsoft Access Databases to a Single SQL Server 2000 Database for more information.




ABOUT THE AUTHOR:   

Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services and more.
Copyright 2007 TechTarget






Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.