Upsizing is the term coined by
Microsoft
Microsoft Corporation is an American multinational corporation and technology company, technology conglomerate headquartered in Redmond, Washington. Founded in 1975, the company became influential in the History of personal computers#The ear ...
to describe the process of upgrading
Microsoft Access
Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational database, relational Access Database Engine (ACE) with a graphical user interface and software-development tools. It is a member of the Microsof ...
Database to a
Microsoft SQL Server
Microsoft SQL Server is a proprietary relational database management system developed by Microsoft using Structured Query Language (SQL, often pronounced "sequel"). As a database server, it is a software product with the primary function of ...
. This allows to continue using Microsoft Access as a database
front-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 other
RDBMS are currently supported for upsizing.
Upsizing strategies
There are two strategies how database can be migrated from Access to a SQL Server.
#Using
ODBC
In computing, Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems. An ...
from Microsoft to get access to a remote database via
ADO.
#Using Access Data Projects (available in
Microsoft Access 2000 and higher) which allow more "native" integration with
Microsoft SQL Server
Microsoft SQL Server is a proprietary relational database management system developed by Microsoft using Structured Query Language (SQL, often pronounced "sequel"). As a database server, it is a software product with the primary function of ...
.
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
Data migration is the process of selecting, preparing, extracting, and transforming data and permanently transferring it from one computer storage system to another. Additionally, the validation of migrated data for completeness and the decommi ...
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
Jet SQL
Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel")
is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
to
Transact-SQL
Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, vari ...
.
#Substantial adaptation of object (e.g. tables, columns etc.) names.
#Reconception of the source code to optimally employ the new functionality of
ADO and
Microsoft SQL Server
Microsoft SQL Server is a proprietary relational database management system developed by Microsoft using Structured Query Language (SQL, often pronounced "sequel"). As a database server, it is a software product with the primary function of ...
of the "upsized" solution.
Notes and references
{{DEFAULTSORT:Upsizing (Database)
Microsoft database software