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

Opening a SQL Azure and Windows Azure One Month Pass or Introductory Special Account

Microsoft offers a number of special pricing schemes for Windows Azure and SQL Azure accounts. If you’re a MSDN subscriber you can take advantage of special offers, such as the Windows Azure Platform Benefits for MSDN Subscribers, which recently had its duration increased:

 

If you aren’t an MSDN Premium or higher subscriber, you can still get one of two temporary benefits for SQL Azure  and Windows Azure:

• Windows Azure One Month Pass: Up to 31 days of free SQL Azure and Windows Azure use; no credit card required. Available to the first 500 developers who request a pass on the first day of the month or later. The pass is valid only for the calendar month in which the request is received.
• Introductory Special: Three months free use of an 1-GB Azure database, 25 free Windows Azure compute hours per month and other benefits.

Either benefit requires a Windows Live ID.

One Month Pass

Following are the steps to sign up for the Windows Azure One-Month Pass:

1. Open the Windows Azure One-Month Pass page, a Windows Azure Web Role:

 

2. Request a pass by clicking the here link to open your email client, adding your company name and zip code, and marking an “offers and campaigns” box:

3. Wait for a response in two to four business days. Mine arrived about 6 hours after the request and appeared as follows, except for redacted account and password information:

Subject: RE: Developers: Windows Azure Platform One Month Pass Request

Many thanks for your email request for a Windows Azure one month pass*. Your pass is valid for the month of August 2010.

• Your Windows Azure account ID is: Redacted
• Your Windows Azure account password is: Redacted

Sign in to the Windows Azure Developer Portal and use the pass to access your Windows Azure account.

1. Please note: your Windows Azure account will automatically cancel at the end of August 2010.
2. For USA developers, no-cost phone and email support with the Front Runner for Windows Azure Platform program.
3. After the pass expires, get a Developer Accelerator account from as low as $59.95 a month to test and develop on Windows Azure. See the Azure Offers here.
4. Startups - get MSDN Azure offers, low cost development tools and production licenses with BizSpark - join here.
5. Get the Tools. To get started, download and install these tools:  Download Microsoft Web Platform Installer and Windows Azure Tools for Microsoft Visual Studio.
6. Why Windows Azure - learn why Azure is a great cloud computing platform with these fun videos.
7. Learn about Azure. Learn how to put up a simple application on to Windows Azure and about PHP on Windows Azure.
8. Take the Windows Azure virtual lab. Read about Developing a Windows Azure Application and view the series of Web seminars designed to quickly immerse you in the world of the Windows Azure Platform.
9. Dig Deeper into Windows Azure: Download the Windows Azure Platform Training Kit and visit the Windows Azure Development Center.

*Terms and Conditions:
This offer is good for developers only in the 50 United States. Windows Azure Platform pass limited to the first 500 to respond each calendar month beginning August 1 and ending October 31, 2010 and is not redeemable for cash. Limit one Windows Azure Platform pass per person.

This Windows Azure Platform pass is non-transferable and cannot be combined with any other Windows Azure Platform pass. Taxes, if any, are the sole responsibility of the recipient. Any Windows Azure Platform pass returned as non-deliverable will not be re-sent. Please allow 2-4 business days for arrival via email of your Windows Azure Platform pass.

At the end of each calendar month your Windows Azure Platform account user ID and password will be deactivated. All deployments, databases, data, services and storage accounts associated with this user ID and password will also be deleted automatically. You will not be able to recover the information.

Use of the Windows Azure Platform is subject to the Microsoft Online Use Rights (http://www.microsoft.com/licensing/about-licensing/online-use-rights.aspx )  and to the Microsoft Online Subscription Agreement 9 https://mocp.microsoftonline.com/Site/Mocp_Eagreement.aspx?country=USA&lang=en ).

Creating an Azure Account from the Pass

Note: If you receive an error message during the following process, wait a minute or two and repeat the offending step. Most errors you receive in portal operations are recoverable, not fatal.

1. Open the Windows Azure Developer Portal and sign in with the Windows Live ID and password you received in the pass response message. Your summary page appears as follows except for the redacted Project Name and Service Administrator values:

2. Click the SQL Azure tab to open the My Project page for SQL Azure:

 

3. Click the project line below the Project Name bar to open the Terms of Use page and click I accept to accept the Terms of Use to open the Database -  Create Server page.

4. Type and write down an Administrator Username and Administrator Password to use for administer a new 1-GB SQL Azure Web Edition database:

5. Click Create Server to create the master database for the new server instance, which the portal names automatically:

6. Click the Firewall Settings tab to open the SQL Azure Server Firewall Settings list and mark the All Microsoft Services Access to This Server check box, which adds a Microsoft Services rule with an IP Address Range 0.0.0.0 – 0.0.0.0 to permit Windows Azure projects to connect to the server:

7. You can restrict access to the server and its database(s) by specifying a range of allowable IP addresses. Click Add Rule to open the Add Firewall Rule dialog, which displays the IP address of the current connection. OakLeaf has a commercial DSL account with five available fixed IP addresses, so the following entries grant access to anyone using one of the five computers assigned to these IPs:

If you want to allow everyone access to the server, specify an IP range of 0.0.0.0 to 255.255.255.255. SQL Azure uses SQL Server security for logins and database users, not Windows integrated authentication.

8. Click Submit to add the rule to the list:

9. Click the Databases tab and the Create Database button to open the database name and size dialog. Type a name for the database and accept the Web edition and 1 GB size properties:

 

10. Click Create to create the new 1-GB database, NwindSQLAzure for this example:

 

11. Select the entry for the new database you created, NwindSQLAzure for this example, and click the Test Connectivity button to open the Database Connectivity Test dialog:

12. Click Connect to verify the connection with the following green message:

13. Click Close to return to the Databases list and, with the new database selected, click the Connection Strings button to display the ODBC connection strings for use by Access or ADO.NET for .NET front ends you create:

ADO.NET: Server=tcp:nd02dcs58a.database.windows.net;Database=NwindSQLAzure; User ID=SQLAzureAdmin@nd02dcs58a;Password=myPassword; Trusted_Connection=False; Encrypt=True;

ODBC: Driver={SQL Server Native Client 10.0}; Server=tcp:nd02dcs58a.database.windows.net; Database=NwindSQLAzure; Uid=SQLAzureAdmin@nd02dcs58a;Pwd=myPassword; Encrypt=yes;

14. Click Close and close the SQL Azure portal connection.

Stay tuned for a link to another post with the details of using the SQL Server Migration Assistant v4.2 for Azure to link a Microsoft Access 2010 Northwind.accdb front end to migrated tables.

Introductory Special Benefits

Following are the steps to sign up with a credit card for the Introductory Special benefits:

1. Open the Microsoft Online Services Portal (MOCP), click the Sign In link, provide your Windows Live ID credentials, and add profile information, if missing, to activate the portal:

2. Click here to open a page with the details of Introductory Special pricing:

3. If you’re satisfied with the Introductory Special pricing close the page to return to MOCP’s Services page and click Buy Now to open the Shopping Cart:

 

4. Click Checkout to open the Payment Options page and accept the default (and only) New Credit Card option.

5. Click Next to open the Payment Information page:

6. Enter the required credit card details and click Next to create the account.

Under construction: Additional details will follow in updates.

 

Technorati Tags: Windows Azure Platform,Windows Azure,Azure,SQL Azure,Azure AppFabric,Introductory Special Pricing,Introductory Special,MSDN Benefits,Cloud Computing

Installing the SQL Server Migration Assistant 2008 for Access v4.2: FAIL and Workaround

Installing the SQL Server Migration Assistant v4.2 for Access from the download page mentioned in the Windows Azure and Cloud Computing Posts for 8/12/2010+ post’s Easily migrate Access data to SQL Azure item failed due to a problem with downloading the license file that’s required to run SSMA for Access.

Background

The following Overview, Requirements and Licensing information for SSMA for Access v4.2 is from the download page:

Overview

Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access to SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. SSMA 2008 for Access also supports migrating to SQL Azure.

SSMA for Access converts Access database objects to SQL Server database objects, loads those objects into SQL Server, and then migrates data from Access to SQL Server. You can also link Access tables to SQL Server tables so that you can continue to use your existing Access front-end applications with SQL Server or SQL Azure.

SSMA for Access supports migration from Access 97 and higher to any edition of SQL Server.

Requirements for the SSMA for Access:

Supported Operating Systems: Windows 7; Windows Server 2003; Windows Server 2008; Windows Server 2008 R2; Windows Vista; Windows XP

Microsoft Windows Installer 3.1 or a later version.

The Microsoft .NET Framework version 2.0 or a later version. The .NET Framework version 2.0 is available on the SQL Server product media. You can also obtain it from the .NET Framework Developer Center Web site.

The ability to access the computer that hosts the target instance of SQL Server.

DAO provider version 12.0 or 14.0. You can install DAO provider from Microsoft Office 2010/2007 product or download it from Microsoft web site.

Microsoft SQL Server Native Client (SNAC) version 10.5 and above for migrating to SQL Azure. You can install SNAC from Microsoft SQL Server 2008 R2 product or download it from Microsoft web site (SQL Server 2008 R2 Feature Pack).

At least 1 GB RAM.

Licensing

SSMA is a FREE download, and is available for use by any customer or partner with no charge or any obligations. However, the product requires a license key for activation. The key can be obtained after quick and simple registration. Microsoft reserves the right to use an e-mail address or phone number provided during registration in order to contact a customer regarding his/her SSMA experience. To help you with the registration process, a License Key Required dialog box opens the first time that you start the SSMA program.

Problem Description

Following are the steps I took to reproduce the problem on several machine:

1. Download SSMA 2008 for Access 4.2.zip from the Download Center’s Microsoft SQL Server Migration Assistant for Access v4.2 page and save it to a compressed folder.

2. Click Run to execute the SSMA 2008 for Access 4.2.exe installer file from the compressed folder and open the initial Setup Wizard dialog.

Click Next.

3. If you don’t have required prerequisites installed, you are prompted to install them:

 

Click Download to open the Microsoft Access 2010 Runtime page, which installs the Access 14 DOA libraries.

Download the ZIP file, then extract and run the AccessRuntime2010.exe or AccessRuntime2010_x64.exe file (~175 MB) to install the prerequisite software.

4. Click Next, accept the end-user license agreement, click Next again to bypass the Usage Report Settings dialog and open the Choose Setup Type dialog:

5. Click Complete to open the Ready to Install dialog and click Install to begin installation.

6. When setup completes, click Finish to dismiss the Setup Wizard.

Note: Setup installs the executable and related files in the C:\Microsoft SQL Server Migration Assistant 2008 for Access folder and subfolders. The Sample Console Scripts subfolder contains four sample scripts that work with the SQL Server Migration Assistant 2008 for Access Command Prompt application.7. Setup installs a SQL Server Migration Assistant 2008 for Access node in the Start menu with the following items:

• Help
• SQL Server Migration Assistant 2008 for Access (32-bit)*
• SQL Server Migration Assistant 2008 for Access Command Prompt
• SQL Server Migration Assistant 2008 for Access
• ReadMe

* Only appears when you install SSMA on a 64-bit machine.

7. Click the  SQL Server Migration Assistant 2008 menu item for Access to open the License Management dialog:

Note: The license folder defaults to C:\Users\UserName\AppData\Roaming\Microsoft SQL Server Migration Assistant\a2ss-2008.

8. Click the license registration page link, sign in with your Windows Live ID, and complete any missing required information items.

9. Click Finish, Enable File Download in the warning bar at the top of Internet Explorer’s window and wait for a File download dialog, which never appears. Instead, the License Registration page refreshes.

Note: The problem occurs for me with Internet Explorer 8 on 32-bit machines running Vista Ultimate, as well as on 64-bit Windows Server 2008 R2 as a host OS and virtual client with Windows 7 Professional running as a guest OS.

It’s interesting to note that Lucas A. Romão apparently didn’t encounter this problem when writing Instalando o SSMA - SQL Server Migration Assistant for Access (in Brasilian Portuguese) on 8/15/2010, the same date as this post.

Workaround

To get a handle on the problem, I installed Eric Lawrence’s Fiddler2 and captured the session of previous steps 7 through 9. Here’s Fiddler2’s capture with the download request highlighted at #61 (click image for full size 1024px capture):

Manually executing the download request by double-clicking line #61 produced the expected result:

You can obtain the license file here, if you encounter the preceding problem:

Note: If the file downloads with SSMA 2008 for Access 4.2.license as its name, save it to your C:\Users\UserName\AppData\Roaming\Microsoft SQL Server Migration Assistant\a2ss-2008 folder and change its name to access-ssma.license as shown here:

 

Return focus to the License Management dialog and click Refresh License.

 

Note: If you receive a message that the license key failed to refresh, check the ssma.log file in the C:\Users\UserName\AppData\Roaming\Microsoft SQL Server Migration Assistant\a2ss-2008/logs folder. The following two errors result from failing to copy the license file to the correct folder and not changing its name to access-ssma.license:

Click OK to open SSMA 2008 for Access with the Migration Wizard active:

 

Update 8/21/2010: A complementary Using the SQL Server Migration Assistant 2008 for Access post that My Migrating a Moderate-Size Access 2010 Database to SQL Azure with the SQL Server Migration Assistant post (updated 8/21/2010) describes migrating and linking an Access application to an SQL Azure file will be posted shortly.

Technorati Tags: SQL Server Migration Assistant,SSMA,SSMA v4.2,SSMA 2008,Microsoft Access,Microsoft Access 2010,Access 2010,SQL Azure,Azure