AzureRiver

Tips on using Microsoft Azure

Connecting MS Access and SQL Azure

leave a comment »

 

 

This post is the first of several blog posts showing how to connect MS Access with SQL Azure. Although the steps are not too difficult the there are a number of different steps involved.

By the end of this blog post we will go through the 7 steps used to create a database on SQL Azure.

 

 

 

What is SQL Azure?

What is SQL Azure? Azure is Microsoft’s cloud solution. As part of their cloud solution Microsoft created a version of SQL Server that runs on Azure. This version of SQL Server is called SQL Azure. For us, the exciting part is we can have a front-end Access database which consists of our forms, reports, and queries. Our back-end will consist of  SQL Azure that is accessible from multiple locations and not just from our local network and/or computer.

How much does  SQL Azure cost? For $10/month you can get a SQL Azure database that is 1gb in size. While there are additional charges for moving data in and out of the database, the data transfer expense is typically small. For our $10 Microsoft provides a SQL Azure database that is always accessible. Microsoft handles all the back-end work hosting the database Azure.  There are no other licensing costs besides the monthly fees for the database and data transfers.

Here are the costs of different sized instances of SQL Azure:

  • 1gb – $10/month
  • 5gb – $50/month
  • 10gb – $100/month
  • 20gb – $200/month
  • 30gb – $300/month
  • 40gb – $400/month
  • 50gb – $500/month
  • $0.10/gb data in
  • $0.15/gb data out

What are the advantages of SQL Azure? There are two large advantages with using SQL Azure. First, we can access our SQL Azure database from anywhere. Having flexible access is useful if a small business has several employees working in different locations. All the employees can use the same database.

The second main advantage to using SQL Azure is having Microsoft host the database. In return for the $10/month fee, Microsoft handles all the details making sure we can access the database 24/7. This saves the business from the associated costs to maintain the database on an in-house server. Depending on the particular business and uses for the database the $10/month fee will be more or less than hosting on our own networks.

 

 

Step 1 – Getting MS Access

The first step is getting the correct version of Access. When using SQL Azure it is easiest to use the latest version of Access which is Access 2010. You can download a trial version of MS Office Professional which includes Access at  http://office.microsoft.com/en-us/try/

 

 

 

Step 2 – Sign up for  SQL Azure

The second step is to create our SQL Azure instance. You will need a credit card to complete this step.

Microsoft office has an introductory offer that gives you a free SQL Azure instance for 3 months. Even for the free offer you will still need a credit card to sign up.

To start the sign up process goto http://www.microsoft.com/en-us/sqlazure/purchase.aspx

 

SQL_Azure_001

 

Next sign in with your windows id:

Sql_Azure_002

 

 

 

Step 3 – Create an account

Next you will see some screens requesting a variety of information to set up the account. Below are the four screens used in this step:

 

SQL_Azure_003

 

Sql_Azure_004

 

Sql_Azure_005

 

Sql_Azure_006

 

 

 

Step 4 – Purchase the introductory Azure offer

Once the account is set up, then purchase the Windows Azure Platform Introductory Offer:

Next are five screens used to purchase the introductory offer:

Sql_Azure_007

 

Sql_Azure_008

 

Sql_Azure_009

Sql_Azure_010

 

Sql_Azure_011

 

 

 

Step 5 – Create a SQL Azure Server

 

If you are not automatically logged in after the previous step then return to http://www.microsoft.com/en-us/sqlazure/default.aspx and choose ‘account’.

Sql_Azure_Silver_001

 

 

Next click on ‘Go To Sql Azure Developer Portal’.

Sql_Azure_Silver_002

 

Re-log in if necessary:

Sql_Azure_Silver_003

 

Once you reach the develop portal you will use a Silverlight application. This Silverlight application is a large improvement over the previous  Azure administrative tool.

In order to create a new SQL Azure instance click ‘New Database Server’

Sql_Azure_Silver_004

 

Now click ‘Create’

Sql_Azure_Silver_005

Create a new server by choosing the appropriate region.

Sql_Azure_Silver_006

 

Specify the administrative login and password.

Sql_Azure_Silver_007

 

Specify the Firewall rules. You will want to create at least one rule to allow the IP address of your computer to access the SQL Azure database.

Sql_Azure_Silver_008

Click ‘Add’ to create a new firewall IP Address. You will see your current IP Address listed.

Sql_Azure_Silver_009

 

Once the firewall is set up click ‘Finish’.

Sql_Azure_Silver_010

 

At this point you have created a SQL Azure server. The server name of the database is called ‘<your server name>.database.windows.net. When you create the connection between Access and the SQL Azure database you will use this server name.

Sql_Azure_Silver_011

 

Congratulations. You have created a server within SQL Azure.

 

 

 

 

 

Step 6 – Create the database

The next step is to create the database. First click ‘Create’ on the database toolbar at the top of the screen.

Sql_Azure_Silver_011

 

Give the database a name, in my case ‘FirstSqlAzureDatabase’.

Choose either the web edition or the business edition.

Choose a maximum size for the database – either 1 gb or 5 gb for a web database. For a business database you can secify up to 50 gb. 

Sql_Azure_Silver_012

 

Congratulations! At this point we have created a SQL Azure database. For a web database of 1gb the cost is $10/month and the first three months are free.

Sql_Azure_Silver_013

 

 

 

 

 

 

Step 7 – Everything is set up so test the connectivity

 

There is one last step and that is to test the connectivity. Press the ‘Test Connectivity’ on the top toolbar.

Sql_Azure_Silver_013

 

Enter your login and password. Also check the box that reads ‘Allow other Windows Azure services to access this server.

 

Sql_Azure_Silver_014

 

You will see a green button with a check mark that reads “Success”!

In the next blog post we will create some tables in our Sql Azure database before connecting the Access database.

Advertisements

Written by azureriver

November 26, 2010 at 4:34 pm

Posted in Azure

Tagged with , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: