Migrating a Moderate-Size Access 2010 Database to SQL Azure with the SQL Server Migration Assistant

The SQL Server Migration Assistant for Access (SSMA) v4.2 is a recent update that supports migrating Access 97 to 2010 databases to SQL Azure cloud databases in addition to on-premises SQL Server 2005 through 2008 R2 instances.

Luke Chung’s Microsoft Azure and Cloud Computing...What it Means to Me and Information Workers white paper explains the many benefits and few drawbacks from using SQL Azure cloud databases with Access front ends. Luke is president of FMS, Inc., a leading developer of software for Microsoft Access developers, and publishes products for the SQL Server, Visual Studio .NET, and Visual Basic communities.

Note: You cannot migrate SQL Server databases for Access Data Projects (ADPs) to operable SQL Azure tables because SQL Azure doesn’t support OLE DB connections currently.

It’s common to use the Northwind or smaller sample databases to demonstrate SSMA v4.2’s capabilities. For example, Chapter 28, “Upsizing Access Applications to Access Data Projects and SQL Azure,” of my Microsoft Access 2010 In Depth book (QUE Publishing) describes how to upsize Northwind.accdb to an SQL Azure database. Most departmental and line of business (LOB) databases have substantially more records than Northwind, so it might not demonstrate performance issues that could arise from increased data size. This article uses the Oakmont database for a fictitious Oakmont University Department-Employees-Courses-Enrollments database, which has tables illustrated by this Access 2010 Relationships diagram (click image for full size, 1024px, capture):

The following table lists the database tables and their size:

Table Name	Rows
Courses	590
Departments	14
Employees	2,320
Sections	1,770
Enrollments	59,996
Students	29,998
Grades	59,996
Student Transactions	45,711

The size of the Oakmont.accdb Access database is 16,776 KB with one query and form. It has been included as Oakmont,mdb or Oakmont.accdb in the downloadable code of several of my books, including Special Edition Using Micorosoft [Office] Access 97 through 2007 (QUE Publishing), the forthcoming Microsoft Access 2010 In Depth (QUE Publishing), and Admin 911: Windows 2000 Group Policy (Osborne/McGraw-Hill). Steven D. Gray and Rick A. Llevano created the initial version of the database for Roger Jennings' Database Workshop: Microsoft Transaction Server 2.0 (SAMS Publishing).

Update 8/21/2010: Alternatively, you can link your Access front-end to tables in a local (on-premises) SQL Server 2008 [R2] database and then move the database to SQL Azure in a Microsoft data center by using the technique described in my Linking Microsoft Access 2010 Tables to a SQL Azure Database post of 7/28/2010. Luke Chung describes a similar process in his August 2010 Microsoft Access and Cloud Computing with SQL Azure Databases (Linking to SQL Server Tables in the Cloud) white paper.

Upsizing the Oakmont Database to SQL Azure with SSMA for Access v4.2

To upsize the Oakmont database to an SQLAzure database named OakmontSQLAzure, do the following:

1. Download SSMA for Access v4.2 as described in my Installing the SQL Server Migration Assistant 2008 for Access v4.2: FAIL and Workaround post of 8/15/2010.

2. If you don’t have a Windows Azure Platform account, create a SQL Azure account and database with a One-Month SQL Azure Pass as described in my Opening a SQL Azure and Windows Azure One Month Pass or Introductory Special Account post of 8/16/2010. Name the database OakmontSQLAzure instead of NwindSQLAzure.

3. Download and decompress OakmontMdb.zip from a SkyDrive public Oakmont SSMA Upsize folder.

4. Launch SSMA for Access v4.2 from the Start\Microsoft Access SQL Server Migration Assistant for Access\Microsoft Access SQL Server Migration Assistant for Access menu to display the Migration Wizard’s first dialog (click image for full size, 1024px, capture):

5. Click Next to open the Create New Project dialog, replace the project Name with OakmontSQLAzure, and select SQL Azure in the Migrate To list:

6. Click Next to open the Add Access Databases dialog. Click Add Databases to open the Open window, navigate to the folder to which you extracted Oakmont.accdb, and double click the icon to close the window and return to the dialog:

7. Click Next to open the Select Objects to Migrate dialog, expand the node hierarchy, and select Tables to migrate all tables. Migrating the single query is optional:

8. Click Next to open the Connect to SQL Azure dialog, open the SQL Azure tab of the Windows Azure Developer portal where you created the database, and copy the auto-generated server name to the clipboard:

 

9. Return to the Migration Wizard, paste the Server Name to the Server Name text box, type your Administrative User Name and Password, click Browse, and click OK to dismiss a warning to open a two-item list:

 

10. Click OakmontSQLAzure to select it and click Next to open the Link Tables dialog:

11. Mark the Link Tables dialog and click Next to open the Migration Status dialog:

and, after a brief delay, the Synchronize with the Database dialog, which shows that the SQL Azure database has no existing objects. Optionally expand a few of the Local Metadata items.

12. Click OK to close the Synchronize with the Database dialog and return to the Migration Status dialog, which continues adding data to the SQL Azure tables and takes several minutes to complete:

13. When data migration completes, the Connect to SQL Azure dialog opens:

14. ODBC connection strings for SQL Azure require a fully qualified login ID. Add @ and the Server Name to your Administrative User name to create a fully qualified SQL Azure login ID, SQLAzureAdmin@nd02dcs58a for this example, and type your Administrative User password:

15. Click Connect to connect to your new SQL Azure database. Click Yes to close the Security Warning message and dismiss the Wizard:

If you click No, you won’t create Access links to the SQL Azure tables. 

16.  Expand and compare the Access Tables nodes and Tables of SQL Azures dbo schema (click for full-size, 1024px, capture):

 

17. Return focus to the Migration Wizard and review the Migration Status dialog:

The warnings result from nullable primary key columns and other non-fatal issues. Informational messages result primarily from naming issues which don’t affect table use.

18. Click the Convert Selected Objects item link to review the warnings and informational messages in the Error Report (click image for full-size, 800px, capture):

According to SQL Server Management Studio, SSMA changes nullable primary keys to NOT NULL (see step 22 below).

19. Click Close and click Report to review a summary report of the upsizing process (click image for full-size, 950px, capture):

20. Click Close and close SSMA’s window, saving metadata if it’s reported missing. Open Oakmont.accdb in Access 2007 or 2010 and view the Tables group in the Navigation pane (click image for full-size, 800px, capture):

Globe icons denote linked tables and the Wizard adds a SSMA$ prefix and $local suffix to new names of the original ACE tables.

21. Open the SQL Azure database in SQL Server Management Studio 2008 R2 [Express] by typing the login information shown here:

22. Expand the Tables, dbo.Courses, Columns nodes and note that the CourseID primary key column is now flagged NOT NULL click image for full-size, 1024px, capture):

23. Close SSMS 2008 R2 and open the linked Course table in Access 2007 or 2010 Design View:

The ODBC connection string (Description property value is: ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=nd02dcs58a.database.windows.net; UID=SQLAzureAdmin@nd02dcs58a;APP=SSMA;DATABASE=OakmontSQLAzure;;TABLE=dbo.Courses and it does not expose the password.

24. Return focus to the Windows Azure Developer Portal’s SQL Azure Database page and check the size of the upsized database: 43.8 MB, which is almost triple that of the Access database:

The increase in size might be the result of the three replicas that SQL Azure creates for data reliability. A Why are SQL Azure Databases Migrated from Access Database with SSMA v4.2 Three Times Larger? thread is pending a response in the SQL Azure – Getting Started forum.

Update 8/21/2010: Brent Stineman suggested I compare the result of executing SELECT SUM(reserved_page_count) * 8192 FROM sys.dm_db_partition_stats against the database, which returns 40,034,304, less than 10% less than the size from the portal.

Note: The Migrating a Moderate-Size Access 2010 Database to SQL Azure with the SQL Server Migration Assistant post to the OakLeaf Systems blog updated 8/21/2010 is a copy of this article.

Technorati Tags: Access 2010,SQL Azure,SQL Server Migration Assistant,SSMA,SQL Server Management Studio 2008 R2,SSMS 2008 R2,Upsizing Access Databases