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:

Reasons for consolidation to a SQL Server environment

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

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 mo

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server High Availability, Scalability and Reliability 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 Get SQL Server log shipping functionality without Enterprise Edition Monitor database mirroring and replication after a SQL Server upgrade Upgrade live applications to SQL Server 2005 for high availability SQL Server High Availability, Scalability and Reliability Research Microsoft SQL Server Performance Monitoring and Tuning Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Determining the source of full transaction logs in SQL Server Improving SQL Server full-text search performance New GROUP BY option provides better data control in SQL Server 2008 Microsoft SQL Server 2008 Resource Governor primer Examining data files when SQL Server tempdb is full Testing transaction log autogrowth behavior in SQL Server Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness SQL Server Migration Strategies and Planning Using Microsoft Hyper-V for SQL Server consolidation Migrating to SQL Server 2008 and leveraging new features The challenges of SQL Server consolidation Testing a SQL Server environment before an upgrade SQL Server Consolidation Fast Guide SQL Server consolidation strategies and best practices Does upgrading to SQL Server 2008 fit your business? A guide to advanced new features in SQL Server Management Studio 2008, part 2 A guide to basic new features in SQL Server Management Studio 2008, part 1 SQL Server virtualization pros and cons: Weigh the performance impact

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

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

Note:

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:

[TABLE]

From SQL Server 2005, SQL Server Management Studio:

[TABLE]

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.

[TABLE]

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