office 2007 Enterprise key Access 2010 and SQL Azu

This post comes from Russell Sinclair who is in New Orleans showing off the newest Access 2010 features at TechEd.

Microsoft SQL Azure is a new cloud-based relational database built on SQL Server that provides highly available, scalable, multi-tenant database services hosted by Microsoft in the cloud. It eliminates the need to install, setup and manage SQL Server, freeing IT to handle day-to-day operations rather than having to manage and maintain an on-premise server. It also enables access to the database from any location with an internet connection,office 2007 product key, providing the ability to create connected applications that are accessible from anywhere.

Access 2010 supports connections to SQL Azure over ODBC, opening up opportunities for Access users and technology providers to create rich experiences using cloud computing. For Information Workers, this gives an easy way to connect directly from Access to cloud-based relational databases, enabling ease of use and providing flexibility to IT. Access users now have more choices to integrate rich client-server applications that can connect directly to both on-premise or cloud databases, creating unique and agile solutions.
Getting Started with SQL Azure
In order to use SQL Azure, you need to create an account and choose a plan that suits your needs. For a limited time, Microsoft is providing an introductory special that is free of charge and includes access to a single 1GB SQL Azure database, Windows Azure and AppFabric services. This is a great way to try out the features available before committing to a monthly plan.

SQL Azure requires that you specify the IP addresses that have access to the database. You can set the valid IP addresses in the Firewall Settings area of the administration tools provided on the SQL Azure site. Adding your current IP is easy as the dialog will show you this information. Check with your network administrator if you want to open a range of addresses that corresponds with the IPs on which your users might connect.

The SQL Azure administration site also allows you to create your database and users, but you will need to use SSMA or another SQL management tool in order to create your tables and other database objects.

Access supports connecting to SQL Azure over ODBC (linked tables and pass-through queries), but you need to use the “SQL Server Native Client 10.0” driver available as part of the SQL Server 2008 R2 release. You should install SQL Server 2008 R2 Management Studio (SSMS) as well since it provides updated functionality for working with SQL Server in a mode that is compatible with SQL Azure. The easiest way to install the ODBC drivers and SSMS is to download SQL Server 2008 Express. You can choose not to install the database engine but you may find it useful as you will need access to a SQL Server instance in order to migrate your database from Access (see details below). A redistributable version of the updated ODBC driver is available as part of the SQL Server 2008 R2 Feature Pack.
Connecting from Access 2010
Once you have completed the necessary installations and configuration, you can connect to your SQL Azure database using a standard ODBC linked table. ODBC links to SQL Azure have a couple of requirements of which you need to be aware. The following points detail the settings you should use when creating an ODBC DSN to a SQL Azure database.

· Use the SQL Server Native Client 10.0 driver, updated with change in SQL Server 2008 R2

· Use SQL authentication when connecting. SQL Azure does not support NT authentication.

· Login ID should be in the format “user@server”, where server is the server name specified in the SQL Azure administration site without the “.database.windows.net”. I.E. If your assigned server is “abcserver.database.windows.net”, and your Login ID is “myuser”, use “myuser@abcserver”

· Set the default database to the database you want to use for this connection. This will ensure that the connection goes to the right database instead of the master database if the login has access to multiple databases on the account

· Check the option to use strong encryption for data. SQL Azure will automatically enforce this option but you might want to set it explicitly

With the ODBC DSN successfully configured, you can now link from Access to any tables or views in the SQL Azure database as you would to any SQL Server database, or create pass-through queries to execute custom SQL on the server.
Migrating to SQL Azure
When migrating your database to SQL Server 2008 or SQL Azure, we recommend that you use the SQL Server Migration Assistant (SSMA) for Access, available from the SQL Server Migration webpage, instead of using the Upsizing Wizard included with Access. This tool is similar to the Upsizing Wizard but is tailored to the version of SQL Server you are targeting. SSMA will not use deprecated features in the migration and may choose to use some of the new features such as new data types included with SQL Server 2008.

In the summer of 2010, the SQL Server team will release an updated version of SSMA 2008 that will support migration of Access databases directly to SQL Azure. Until that time, migrating your database is a simple, three-step process

· Migrate your database to a non-Azure SQL Server (preferably 2008) database

· Use SQL Server 2008 R2 Management Studio to generate a SQL Azure compatible database creation script from your database

· Run the scripts created in SSMS on the SQL Azure database

The Upsizing Wizard included with Access is a general-purpose tool to help migrate to SQL Server and does not target a specific version of the server. Some of the features used by the Upsizing Wizard have been deprecated in SQL Server 2008 and are used in order to maintain backwards-compatibility with previous versions. Although SQL Server 2008 does not prevent you from using many deprecated features, SQL Azure directly blocks them.

Once your database is migrated to SQL Server you can generate scripts to recreate your database and the data it contains in SQL Azure. Open SSMS, connect to your local server,Office Home And Business Product Key, right-click the database in question and choose Tasks – Generate Scripts to open the Generate and Publish Scripts wizard (note, do not select the menu option “Script Database As…” as this does not provide the same functionality). Step through the wizard, selecting options appropriate to your migration, until you reach the Set Scripting Options screen. On this screen,office 2007 Enterprise key, click the Advanced button to show the Advanced Scripting Options screen shown below. There are three important settings in this dialog:

1. Script for the database engine type: SQL Azure Database. This setting is essential when scripting your database to SQL Azure. It specifies that the script should only use SQL Azure-compatible scripting,Cheap Office Home And Student 2010, avoiding the use of unsupported deprecated SQL Server features or language.

2. Types of data to script: Schema and data. This setting specifies that the script will include the information necessary to recreate all of your data on the SQL Azure. This saves you from having to migrate the data manually. You can set this to “Schema only” if the data is not required.

3. Script Triggers: True. If you want your triggers to be migrated set this option to True as it will default to False the first time you run the tool.


Once your script is created, open Management Studio, connect to the SQL Azure database and run the script against that database to complete the job.

You’re now ready to use the full functionality of the SQL Azure database from Access.
Unsupported Features
Unfortunately, there are a few Access features we cannot support on SQL Azure due to various restrictions or issues:

· The Upsizing Wizard does not support directly upsizing to SQL Azure. As stated above, you need to migrate to an intermediary SQL Server database first and we suggest you use SSMA to do so

· ADPs are not supported as they rely on features that are not supported in SQL Azure (including ADO 2.x connections)

· ODBC connections are not supported in Access versions prior to Access 2010

· Exporting tables to SQL Azure using the export functionality built in to Access (export to ODBC or DoCmd.TransferDatabase) will fail if the tables contain any date/time fields – use SSMA

In addition to these restrictions,Microsoft Office 2007 Professional Key, you should read through the SQL Azure help to determine what features of SQL Server 2008 are and are not supported. SQL Azure explicitly blocks use of features that are considered deprecated in SQL Server 2008. There are also many features that are not supported in the current release, such as extended properties and cross-database queries.
Exciting Features
We feel that SQL Azure and Access 2010 are opening some great new scenarios for Access users. We can’t wait to see what solutions you come up with.
<div