How to Migrate from Access to SQL Server via Upsizing Concept

When your database expands you may wish to migrate to a SQL system using the Upsizing concept.

By using the Upsizing Wizard in MS Access, you can use your MS Access application for accessing SQL Server databases. For upsizing an Access databases to a SQL Server database, you will first have to set a target where you want the the process of upsizing to occur. You can make use of any edition of SQL Server, the resulting databases will not have much differences. Although you can upsize Access data to SQL Server by just linking the two as well, using the Upsizing Wizard will allow you to directly make use of SQL server databases, through the use of Access Database Project ( ADP). ADP is created automatically when you begin using the Upsizing Wizard.How To Migrate From Access To SQL Server Via Upsizing Concept

Upsizing Application using the Upsizing Wizard

Upsizing Concept Explained Using SQL Server Files From Ms AccessWith the growing businesses, an increasing numbers of business owners have begun making use of client – server software like SQL Server. Thus making file – server software outdated and irrelevant. However for those business owners who had till now been using file – server software, a sudden shift to client – server is too much to handle. But if you are the one who is using MS Access to manage your databases, then shifting to SQL Server should not be a problem.

All you need to do is; make use of the Upsizing Wizard in MS Access. This will create a duplicate of your Access database in a SQL Server. Thus giving you access to your database in a client server application. Through this wizard your table structures, indexes, validation rules, defaults, autonumbers, and relationships, can all be recreated.

Before Using the Upsizing Wizard

Follow the given steps before you begin using the wizard:

  1. Take Backups – Although the wizards does not make any attempts to make changes to your data or lose data in the process, backing it up, to be on the safe side is always the wise thing to do.
  2. Check Databases – Make sure your Access database is health. If necessary, use a professional tool to repair accdb or mdb file, before proceeding.
  3. Sufficient Storage Space – When your Access databases have been upsized to SQL databases, they would occupy twice the amount of space. Make sure there is enough hard drive space on your system before you initiate the process of upsizing.
  4. Set Default Printer – A default printer is necessary as upsizing wizard creates snapshots for reports when the process is completed.

Running the Wizard

After you have undertaken the above steps, it is now time to initiate the upsizing process. It is important to know that after you have performed upsizing on your Access databases, you will get a brand new SQL Server database, and your original Access databases will be left unchanged. The process of upsizing will have to be initiated from your Access database, in the Database Tools Tab. From this tab you will have to go to the Move Data Section, and then select SQL Server.

This will activate the Upsizing Wizard, in which you will have to proceed step by step. In majority of the steps you only have to give your preference, and then move on to the next step, till you reach the final step, and complete the process of upsizing.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix SQL Server and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.