Upsizing (database)
Encyclopedia
Upsizing is the term coined by Microsoft
to describe the process of upgrading Microsoft Access
Database to a Microsoft SQL Server
. 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.
The first strategy is often seen as the first step towards complete migration on a SQL server on the stage 2, and can be seen as a part of the strategy 2. For peculiarities of every strategy see the table.
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
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
to describe the process of upgrading Microsoft Access
Microsoft Access
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
Database to a Microsoft SQL Server
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
. 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 from Microsoft to get access to a remote database via ADOActiveX Data ObjectsMicrosoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB...
. - Using Access Projects (available in Microsoft Access 2000Microsoft AccessMicrosoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
and higher) which allow more "native" integration with Microsoft SQL ServerMicrosoft SQL ServerMicrosoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
.
The first strategy is often seen as the first step towards complete migration on a SQL server on the stage 2, and can be seen as a part of the strategy 2. For peculiarities of every strategy see the table.
| Parameter | Strategy 1 | Strategy 2 |
|---|---|---|
| Database access | Via ODBC using ADO ActiveX Data Objects Microsoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB... , somewhat slower than strategy 2. |
Native integration with Microsoft SQL Server Microsoft SQL Server Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network... using Access Project (.adp), data access using ADO ActiveX Data Objects Microsoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB... |
| Integration | Can be integrated with (almost) any RDBMS | Native support only for Microsoft SQL Server |
| The role of Access | Serves as database front-end with full features of Microsoft Access Microsoft Access Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of... JET Engine Microsoft Jet Database Engine The Microsoft Jet Database Engine is a database engine on which several Microsoft products have been built. A database engine is the underlying component of a database, a collection of information stored on a computer in a systematic way... and VBA VBA VBA can mean:* Virtual backup appliance, a virtual machine used to back up other virtual machines* Visual Basic for Applications, the application edition of Microsoft's Visual Basic programming language... . (compare to thick client) |
Serves as database front-end with limited functionality. Local tables are not allowed. Queries are generally transformed into database views View (database) In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions... , VBA code manipulating the data is transformed into procedures stored on server. (compare to thin client Thin client A thin client is a computer or a computer program which depends heavily on some other computer to fulfill its traditional computational roles. This stands in contrast to the traditional fat client, a computer designed to take on these roles by itself... ). |
Performing the upsizing
As any data migrationData migration
Data migration is the process of transferring data between storage types, formats, or computer systems. Data migration is usually performed programmatically to achieve an automated migration, freeing up human resources from tedious tasks...
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 DAOData Access ObjectsJet Data Access Objects is a deprecated general programming interface for database access on Microsoft Windows systems. It is unrelated to the data access object design pattern used in object-oriented software design.- History :...
to ADOActiveX Data ObjectsMicrosoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB...
. - Substantial change of Microsoft JETMicrosoft Jet Database EngineThe Microsoft Jet Database Engine is a database engine on which several Microsoft products have been built. A database engine is the underlying component of a database, a collection of information stored on a computer in a systematic way...
SQLSQLSQL is a programming language designed for managing data in relational database management systems ....
to Transact-SQLTransact-SQLTransact-SQL is Microsoft's and Sybase's proprietary extension to SQL. SQL, often expanded to Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements...
. - Substantial adaptation of object (e.g. tables, columns etc.) names.
- Reconception of the source code to optimally employ the new functionality of ADOActiveX Data ObjectsMicrosoft's ActiveX Data Objects is a set of Component Object Model objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB...
and Microsoft SQL ServerMicrosoft SQL ServerMicrosoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
of the "upsized" solution.