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 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

Get more advice on this SQL Server topic: New features integrating Microsoft Office 2007 with SQL Server Download our ebook and see why, when and how to consolidate SQL Servers

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:

	Tools ⇒ Data Transformation Services ⇒ Import data.
	In the Welcome window, click Next.
	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.
	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.
	To copy all the objects from the Access database, click Next.
	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.
	You can run the import immediately or save it as a SQL Server DTS package for reuse. Click Next.
	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:

	Right click on the database ⇒ Tasks ⇒ Import data.
	In the Welcome window, click Next
	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.
	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.
	To copy all the objects from the Access database, click Next.
	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"
	You can run the import immediately or save it as a SSIS package for reuse. Click Next.
	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. Submit a Tip Digg This!     StumbleUpon     Del.icio.us    RELATED CONTENT SQL Server availability Upgrade live applications to SQL Server 2005 for high availability SQL Server high availability when upgrading to SQL Server 2005 Tutorial: Migrating to SANs from local SQL Server disk storage How to restore SQL Server database to transition server during upgrade SQL Server memory configurations for procedure cache and buffer cache Upgrade Active/Active cluster to SQL Server 2005 and Windows 2003 SQL Server backups using SAN database snapshots Tips for moving from SQL Server local disk storage to SANs How to process SQL Server 2005 Analysis Services for data availability SQL Server 2005 log shipping setup using the wizard SQL Server availability Research SQL Server performance and tuning Virtual database storage for SQL Server: Friend or foe? Tutorial: SQL Server 2005 Analysis Services Tutorial: Migrating to SANs from local SQL Server disk storage SQL Server memory configurations for procedure cache and buffer cache Using the OUTPUT clause for practical SQL Server applications Check SQL Server database and log file size with this stored procedure SQL Server PerfMon counters for access methods and buffer manager Find size of SQL Server tables and other objects with stored procedure Monitor SQL Server disk I/O with PerfMon counters SQL Server tempdb best practices increase performance SQL Server upgrades and patches Upgrade live applications to SQL Server 2005 for high availability SQL Server high availability when upgrading to SQL Server 2005 How to restore SQL Server database to transition server during upgrade Top 10 SQL Server Integration Services (SSIS) and DTS tips Upgrade Active/Active cluster to SQL Server 2005 and Windows 2003 FAQ: SQL Server databases how-to Upgrading to SQL Server 2008 advantages and hardware requirements Create an upgrade plan for your move to SQL Server 2005 Table partitioning with SQL Server 2005 Change collation to SQL Server 2005 after upgrading databases 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 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.