- Upsizing (database)
Upsizing is the term coined by
Microsoft to describe the process of upgradingMicrosoft Access Database to aMicrosoft SQL Server . This allows to continue using Microsoft Access as a databasefront-end whereas the actual back-end is served by a separate local or remote SQL Server allowing much higher productivity and data volumes. Microsoft Access from the version 2000 on has a special "Upsizing Wizard" which facilitates the data migration to the proprietary Microsoft SQL Server. No otherRDBMS are currently supported for upsizing.Upsizing strategies
There are two strategies how database can be migrated from Access to a SQL Server.
#UsingODBC from Microsoft to get access to a remove database via ADO.
#UsingAccess Project s (available in Microsoft Access 2000 and higher) which allow more "native" integration withMicrosoft SQL Server .The first strategy is often seen as the first step towards complete migration on a SQL server on the stage 2 [ Thomas Groß,Claudia Clemens "Upsizing von Access zu SQL Server", dotnetpro 6/2004] , and can be seen as a part of the strategy 2. For peculiarities of every strategy see the table.
Performing the upsizing
As any
data migration procedure Microsoft Access database upsizing requires fundamental refactoring of the database structure and source code. Even though some aspects of this procedure seem to be automatized by the "Upsizing Wizard", there are still many points requiring human intervention. Following changes have to be done on upsizing
#Complete change of data access interface from DAO to ADO.
#Substantial change of Microsoft JETSQL toTransact-SQL .
#Substantial adaptation of object (e.g. tables, columns etc) names.
#Reconception of the source code to optimally employ the new functionality of ADO andMicrosoft SQL Server of the "upsized" solution.Notes and references
Wikimedia Foundation. 2010.