You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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, 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.
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.
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.
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, 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, 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, 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.
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, 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.
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.
Comments: (12) Collapse
Ryan, this new capability is essentially 'huron' which was written about on this blog a good while ago, correct? I'd given up hope that we'd get to use this with Access 2010. I've not had time to experiment with this yet but I'm totally stoked. If it's a viable release, I mean not too riddled with faults and limitations, it could be a major game changer in the Access development world. I'm still knocked out by this...and will write more here after I've had a chance to test it out a bit.
@Michael Yes SQL Azure is the evolution of what we used to call Huron. We are looking forward to your feedback!
Looks very good. Great to see Access coming alive again.
A couple of questions: 1) I've seen a report that this seems to work OK with Access 2007 even though you've written, "ODBC connections are not supported in Access versions prior to Access 2010." Does this mean is isn't meant to work, does seem to work but you won't guarantee anything or something else, please?
2) Back in the early Huron days, it was intended that Access databases would be able to sync with (what are now called) SQL Azure databases. Is this still the plan at some time or has that been replaced by the synchronisation available with web databases?
3) Is anyone else having trouble posting comments when using Firefox? Cheers, Alan
@Alan 1) We mean that we focused our testing and support on 2010 for Azure, 2007 and earlier may work but we are not supporting that. 2) I don't have any update on the sync features that Huron was originally looking at.
It works...its cool. In my very limited testing, Access 2010 and sql azure are somewhat faster than using Access 2010 against a similarly remote sql server instance via odbc. I am concerned that sql azure's pricing for very small and low activity databases is still too high. After the month which remains for the free use of sql azure, the lowest tier offering per db is $10 a month. There are a lot of postings on this subject, and Microsoft recently adjusted some of the other pricing tiers for sql azure, but unfortunately not the low end offerings. I know the primary focus for cloud computing has been scalability etc; but even if you don't need to scale, sql azure is great because it gives you a sql db in the sky. It's just too expensive for use with trivial apps. I don't see that it'd cost MSFT much if anything to host small and lightly used db in sql azure. The net result of the current pricing is that many who might otherwise use the service for little apps will bounce off the pricing; they'll never consider sql azure; it won't become a 'no-brainer' part of many application builder's toolkit. What would make sense to me would be $10 a month for 1 g worth of databases; not $10 per db, up to 1 g in size. That's like what I pay my website hosting company. There too, I don't come close to pushing the boundaries of what they offer for the price, but the package allows me to freely experiment with whatever websites and mysql databases I wish. I hope Microsoft will see that it makes sense to make it very easy to experiment with sql azure.
Quote:
End Quote: Can someone give an idea as to when this will take place or has it happened already?
I've managed to migrate one Access database upsized to SQL Server database on to SQL Azure OK. When I tried a second one I got some errors. I went back to the original Access database, fixed the problem there, upsized to SQL Server with no problems, but have now had SQL Azure disconnect me three times in a row while attempting to run the SQL Script in Management Studio. Any idea what might be happening. There seems to be no explanation given.
With regard to my SQL script dipping out, I was running Microsoft Security Essentials, so I turned off Realtime Protection while I ran the SQL script and this time it all ran OK.
The new version of SSMA that includes SQL Azure support will be released in August of 2010.
Hi, So are there any future plans for supporting Access ADP file connections in SQL Azure? Using linked tables in Access files closes off much of the value of using a SQL Server backend (in particular using SQL Server views or stored procedures as recordsources), and is a much more limited solution is it not?
I am finally catching up and reading what SQL Azure can do for Access developers, this is very exciting, can I assume we can have an Azare based database and actual write Access and say Windows phone apps to access the same data?
@pmidgley Unfortunately, it is SQL Azure that blocks ADP connections. SQL Azure does not support OLEDB connections to the database.
@CoffeeSykes you are correct. You can have multiple applications connecting to the same Azure database, including Access and Windows Phone apps.
Comments: (loading) Collapse