Upsizing (database)

Last updated

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.


Upsizing strategies

There are two strategies how database can be migrated from Access to a SQL Server.

  1. Using ODBC from Microsoft to get access to a remote database via ADO.
  2. Using Access Data Projects (available in Microsoft Access 2000 and higher) which allow more "native" integration with Microsoft SQL Server.

The first strategy is often seen as the first step towards complete migration on a SQL server on the stage 2, [1] and can be seen as a part of the strategy 2. For peculiarities of every strategy see the table.

ParameterStrategy 1Strategy 2
Database accessVia ODBC using ADO, somewhat slower than strategy 2.Native integration with Microsoft SQL Server using Access Data Project (.adp), data access using ADO
IntegrationCan be integrated with (almost) any RDBMS Native support only for Microsoft SQL Server
The role of AccessServes as database front-end with full features of Microsoft Access JET Engine and VBA. (compare to thick client)Serves as database front-end with limited functionality. Local tables are not allowed. [2] Queries are generally transformed into database views, VBA code manipulating the data is transformed into procedures stored on server. (compare to thin client).

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

  1. Complete change of data access interface from DAO to ADO.
  2. Substantial change of Microsoft JET SQL to Transact-SQL.
  3. Substantial adaptation of object (e.g. tables, columns etc.) names.
  4. Reconception of the source code to optimally employ the new functionality of ADO and Microsoft SQL Server of the "upsized" solution.

Notes and references

  1. Thomas Groß,Claudia Clemens "Upsizing von Access zu SQL Server", dotnetpro 6/2004
  2. There are though the ways to store some data locally using the save methods of recordset object

Related Research Articles

Microsoft Access Database manager that is part of the Microsoft Office package

Microsoft Access is a database management system (DBMS) 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 365 suite of applications, included in the Professional and higher editions or sold separately.

In computing, Microsoft's ActiveX Data Objects (ADO) comprises a set of Component Object Model (COM) objects for accessing data sources. A part of MDAC, it provides a middleware layer between programming languages and OLE DB. ADO allows a developer to write programs that access data without knowing how the database is implemented; developers must be aware of the database for connection only. No knowledge of SQL is required to access a database when using ADO, although one can use ADO to execute SQL commands directly.

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 application written using ODBC can be ported to other platforms, both on the client and server side, with few changes to the data access code.

A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary.

ADO.NET is a data access technology from the Microsoft .NET Framework that provides communication between relational and non-relational systems through a common set of components. ADO.NET is a set of computer software components that programmers can use to access data and data services from a database. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems, though it can also access data in non-relational data sources. ADO.NET is sometimes considered an evolution of ActiveX Data Objects (ADO) technology, but was changed so extensively that it can be considered an entirely new product.

OLE DB, an API designed by Microsoft, allows accessing data from a variety of sources in a uniform manner. The API provides a set of interfaces implemented using the Component Object Model (COM); it is otherwise unrelated to OLE. Microsoft originally intended OLE DB as a higher-level replacement for, and successor to, ODBC, extending its feature set to support a wider variety of non-relational databases, such as object databases and spreadsheets that do not necessarily implement.

Data Transformation Services

Data Transformation Services, or DTS, is a set of objects and utilities to allow the automation of extract, transform and load operations to or from a database. The objects are DTS packages and their components, and the utilities are called DTS tools. DTS was included with earlier versions of Microsoft SQL Server, and was almost always used with SQL Server databases, although it could be used independently with other databases.

A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its creator.

The Microsoft Jet Database Engine is a database engine on which several Microsoft products have been built. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.

Microsoft Data Access Components framework

Microsoft Data Access Components is a framework of interrelated Microsoft technologies that allows programmers a uniform and comprehensive way of developing applications that can access almost any data store. Its components include: ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC). There have been several deprecated components as well, such as the Microsoft Jet Database Engine, MSDASQL, and Remote Data Services (RDS). Some components have also become obsolete, such as the former Data Access Objects API and Remote Data Objects.

Microsoft SQL Server Analysis Services, SSAS, is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server. SSAS is used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services, Reporting Services and Analysis Services. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors - Multidimensional and Tabular.

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks.

In ADO.NET, a DataReader is a broad category of objects used to sequentially read data from a data source. DataReaders provide a very efficient way to access data, and can be thought of as a Firehose cursor from ASP Classic, except that no server-side cursor is used. A DataReader parses a Tabular Data Stream from Microsoft SQL Server, and other methods of retrieving data from other sources.

Entity Framework (EF) is an open source object–relational mapping (ORM) framework for ADO.NET. It was originally shipped as an integral part of .NET Framework. Starting with Entity Framework version 6, it has been delivered separately from the .NET Framework.

Microsoft SQL Server Compact is a compact relational database produced by Microsoft for applications that run on mobile devices and desktops. Prior to the introduction of the desktop platform, it was known as SQL Server for Windows CE and SQL Server Mobile Edition.

Microsoft Sync Framework is a data synchronization platform from Microsoft that can be used to synchronize data across multiple data stores. Sync Framework includes a transport-agnostic architecture, into which data store-specific synchronization providers, modelled on the ADO.NET data provider API, can be plugged in. Sync Framework can be used for offline access to data, by working against a cached set of data and submitting the changes to a master database in a batch, as well as to synchronize changes to a data source across all consumers and peer-to-peer synchronization of multiple data sources. Sync Framework features built-in capabilities for conflict detection – whether data to be changed has already been updated – and can flag them for manual inspection or use defined policies to try to resolve the conflict. Sync Services includes an embedded SQL Server Compact database to store metadata about the synchronization relationships as well as about each sync attempt. The Sync Framework API is surfaced both in managed code, for use with .NET Framework applications, as well as unmanaged code, for use with COM applications. It was scheduled to ship with Visual Studio 2008 in late November 2007.

Language Integrated Query is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages, originally released as a major part of .NET Framework 3.5 in 2007.

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

An embedded database system is a database management system (DBMS) which is tightly integrated with an application software; it is "embedded in the application". It is actually a broad technology category that includes

SafePeak Technologies is a software company founded in 2007 in Israel. It markets products for big data related to relational database management systems.