Migrate and consolidate Access databases to SQL Server
By Michelle Gutzait
Rating: -4.14- (out of 5)
|
Microsoft Access is a simple development tool to use and you don't even need a development background in order to write an Access application. There may be many departmental Access databases in your organization that have been built over the years for specific reasons. Major reasons exist, too, for consolidating your departmental Access databases to a central SQL Server database. I'll discuss the benefits of this process and provide links for further analysis and the migration process.
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 ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
Why SQL Server?
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:
- If the destination object does not exist, it is created. If the object is a table, it is created without keys and relationships.
- The queries from the Access database are converted into SQL Server Views.
- 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
|
|
|
|
|
|
