Saturday, August 21, 2010

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.

image 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):

imageThe 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

image 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.

image 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):

image

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:

image

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:

image

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:

image

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:

  image

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:

image 

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

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

image

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.

image

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:

image

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

image

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:

image

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

image 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):

image 

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

image 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):

OakmontAccessSQLAzureMigrationErrorList800px

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):image

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):

imageGlobe 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:

image

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):

image

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

image 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:

imageThe 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.

4 comments:

  1. Hi, thanks for all this good information.

    I would like to know if multiple simultaneuos users (at least 5) can use the database once it has been placed in SQL Azure? I want 4 simultaneous users to make changes to an MS-Access 2010 database that I have created.

    Thanks for your help!

    ReplyDelete
  2. There will be no problem with five or more simultaneous users with this Access database (or any other) migrated to SQL Azure. A few hundred simultaneous users updating the tables in an OLTP scenario might slow operations down, but you can increase the SQL Azure database size to add more CPU and memory resources. A large number of read-only users can be accommodated by the default 1 GB size.

    --rj

    ReplyDelete
  3. If I had to guestimate, how much money should I set apart to pay for the Azure services?

    Assuming that I have a database under 1 GB used only to write invoices and store general customer information.

    Can I pay only for the SQL Azure services or do need something else? This is only for 4 employees who will create approximately 30 invoices/day/per employee.

    I did read that it says $9.99 per database up to 1GB per month for the web edition. But something tells me that I'm missing something. That seems to be too cheap when I compared to the other Azure service fees they charge.

    I am trying to decide if it would be better to use SQL Azure or the services offered by AccessHosting[dot]com.

    Any feedback will be very useful. All this Azure stuff seems hard to understand and a bit confusing for newbies like me.

    ReplyDelete
  4. Esau,

    $9.99 is it for a 1GB SQL Azure database. There are additional bandwidth charges: $0.10/GB in, $0.15/GB out. Bandwidth charges will be unnoticible for the activities you describe, so you total cost per month shouldn't exceed $10.00.

    The advantage of AccessHosting is that the Access front end is in the cloud too, so users don't need Access. However, runtime Access is free, so that's not a major issue.

    --rj

    ReplyDelete