SQL Server Reference Guide

SQL Azure is Microsoft’s Relational Database Management System (RDBMS) “In the Cloud”, or more accurately, in the Distributed Computing Arena. It’s essentially SQL Server, with various restrictions and architectural changes to make it fit into this environment. I’ve explained it in more depth in this overview. Pay close attention there to the size capabilities, data types and so on.

One of the use-cases for a Distributed Architecture database is to allow departments, or perhaps even multiple companies access common data. For instance, assume that you have a departmental application where subsets of users are sharing a small application that allows them to track common information.

Often these users will not contact IT directly, but simply use the tools they have available. There are multiple ways that they can share data, from shared spreadsheets to programs like Microsoft’s SharePoint server.

In many cases, “power users” are familiar with Microsoft Access — an information-tracking system that can act as a common database. Throughout this tutorial I’ll use Microsoft Access as an example, but keep in mind that essentially this is just an Open Database Connectivity (ODBC) — based application, so this process would work with any Microsoft Office product, or even web pages, and in fact any Microsoft Office program can use these instructions, and the information also holds true for a web-page based application or on in Java, C# and other languages. I’ll cover those in another tutorial — for now, I’ll stick with Microsoft Access since that solves the need my users have.

Another disclaimer — an even better approach is to use the VB-style code within Microsoft Access to properly use Stored Procedures rather than accessing tables, along with many best-practices on both SQL Server and the Microsoft Access side. This tutorial is just for a proof-of-concept type of applications, or for less technical folks to follow to at least protect their data and move it into a more standard format.

Now back to the example I just mentioned. The issue with a local common data store is that the data is sitting on a user’s system, probably not backed up, and probably not highly available. Also, unless the users set up a Virtual Private Network (VPN), they may not be able to connect to the data when not in the office.

Of course, the argument can be made that the users shouldn’t be in control of this level of data if it has these requirements. But realistically this type of thing occurs quite often, and unless the IT team is willing to hunt each one of these down and re-design each one, attaching the data itself to SQL Azure and allowing the users to access it may be the better way to go. You’ll have to decide what you want to do on a case-by-case basis.

So the process I’ll for this tutorial is to create a Microsoft Access “front end” program to access a common set of tables in SQL Azure. That way the users maintain complete control of the look and feel of the screens, reports and so on, and the data is in a common, highly-available area. And, the users pay for the access to that space, decreasing the IT budget.

The outline for attaching an ODBC program (like Microsoft Access) to a SQL Azure database is:

1. Set up the SQL Azure account (one-time process)
2. Create the SQL Azure database
3. Add firewall rules to the SQL Azure database
4. Add users, tables and other database objects to the SQL Azure database
5. Create a DSN or other ODBC connection
6. Connect the SQL Azure database tables to the Microsoft Access application

Let’s take a look at each one of those steps in a little more depth.

Setting up the SQL Azure Account

SQL Azure is an online offering from Microsoft, so the department will need to sign up for an account. The process is fairly straightforward, and is described in depth here: http://msdn.microsoft.com/en-us/windowsazure/sqlazure. Just at the top of that page is a link to create the account — and at the time of this writing there’s a free trial going on.

Once you create the account, you can access the SQL Azure “Portal” from that same link. This shows a list of projects you created in step one.

Figure 1

When you set up your account, you can create a “project” — mine is called “Microsoft” in this screen. Once you creat the SQL Azure project, click on that project name to start the next step.

Creating the Database

After clicking on the project name, the following screen appears in the portal:

Figure 2

There’s actually quite a bit of information going on here. The information for connecting to the database is right on the screen — but there are a few things to do first.

Clicking the “Create Database” button brings up the following panel:

Figure 3

I’ve already done this step, and named my database “WAVS” — it’s from a college project I teach. Notice that it’s here that you set the “size” of the database, by choosing the “edition”. You can choose three editions as of this writing, ranging from 1 to 50 GB in size. You’ll pay for the database (and the number of them) according to this selection. Make that selection and you’re returned to the portal site.

After the database is created, copy out the connection string so you can use it in a few minutes. Open Notepad on your local computer, then click the name of the database in the SQL Azure management portal. From there click the “Connection Strings” button to bring up this panel:

Figure 4

Of course, I’ve wiped out the information in this graphic, since it’s a “live” database — but the point is that you can select these strings and copy and paste them into Notepad to be used later.

Also back at the portal you can set the password for the “main” user, or the equivalent of the DBO account in SQL Azure. You should use a strong password, and of course I don’t recommend that you keep this password anywhere written down.

That’s all you need to do for this step. Click the “Close” button to return to the main portal screen.

Adding Firewall Rules

Next, from the management portal click the “Firewall Settings” tab. That brings up the following panel:

Figure 5

You’ll need to know the IP addresses, or address ranges, of the workstations you want to allow access for the database. Click the “Add Rule” button to add one address or a range of addresses. You’ll see all zeros on this list as well, and that’s something you shouldn’t change, so that Microsoft can access the system for uptime checks. They don’t access your data.

Adding Tables, Users and other Database Objects

Now you’ve got the account and database set up you can access it from other tools. It’s time to add users using anything that allows you to type in Transact-SQL commands. You can use SQL Server Management Studio 2008 R2 or higher, or the SQLCMD command prompt from those versions.

You can also use a web-based tool from Microsoft, located at https://manage-ch1.cloudapp.net/

I’ve opened that tool here, and after I entered the name of the database and my login credentials (set in the portal a few moments ago) I’m shown this screen:

Figure 6

If you’re just typing in commands, or need to work with the system without having any of the management tools from SQL Server installed, this is a good option.

On the “Help Links” page of this tool (click the right or left arrows that ghost up when you hover over the cube graphic) you’ll find some videos and recordings that will help you work with it — and yes, that’s me on those videos!

Now you’re ready to add some tables. Click the “New Table” button to bring up this screen:

Figure 7

Add whatever columns you want, and remember that you need at least one clustered index set up on each table. When you’re done, click the “Save” button. Your tables will show up in the list on the left.

Now it’s time to add some users. Just as in standard Transact-SQL, you create a Login and then a User, and in this case you can do it using commands. Click the “New Query” button to bring up a place to type. You can see the commands I used for a “Bob” account in this screen. Clicking the “Execute” button will run the commands — don’t press F5!

Figure 8

Of course, you should follow standard security guidelines on granting access to database objects using roles and so on. The process is the same for SQL Azure as it is for other editions — use the CREATE statements for the Roles, and the GRANT, DENY and so on verbs for the access to the underlying objects. I won’t show all of those statements here.

Creating a Connection to SQL Azure and Connecting the Objects

Now you’re ready to connect the application to the database you just created. In the case of Microsoft Access, the “application” is actually in several parts. Without doing a training session on Access, the program contains many features, such as screens, reports, views and so on. It can also store data. But many people aren’t aware that you can actually separate the two — meaning that you can have screens and reports running (on multiple user’s computers) that don’t store any data — they “point” to a single source of data somewhere else. And that’s exactly how I’ll use Access in this example — as “front-end” to a SQL Azure database. For this example, I’m using Microsoft Access 2010.

I’ve shown you two tools so far — the management portal, where you do the following:

• Create the account
• Create the database
• Create the firewall rules
• Create a password for the admin account
• Copy the connection strings for the database

The second tool I showed you was the online web database manager, where you do the following:

• Create the database objects (tables, views, stored procedures, etc)
• Create the users
• Assign rights to objects

You can also use SQL Server Management Studio (2008 R2 or higher) and other tools to do that last section of work.

Now it’s time to use the last tool — Microsoft Access. First, you set up the connection for the application to connect to SQL Azure. This is where the information in that Notepad I had you bring up.

Open Microsoft Access and select a blank database.

Figure 9

Click the “Create” button, and then select the “External Data” tab at the top of the page.

Figure 10

Clicking the “ODBC” button brings up the following panel.

Figure 11

This is a very key selection. Selecting the first radio button means you will copy the data into your Access system — and that’s not what you want. You want for this Access database to link to those tables (see the disclaimer at the beginning of this article) and use them from multiple workstations. So you want to choose the second selection. In years gone by, this selection took some pretty serious locks on the SQL Server tables, but things have changed in Microsoft Access that make this an acceptable choice, at least for 5-10 users.

Clicking OK brings up the next panel.

Figure 12

There are two options here. A “File Data Source” (shown) will save the Data Source Name (DSN, or connection info) in a file on the hard drive. That way you can pass it around to other workstations. The second tab, “Machine Data Source” stores this information in the registry only on this workstation, suitable for multiple people that use one system. It won’t affect the panels from here on out, other than in this one you need to record the name of the file. Click the “New” button to bring up the next panel.

Figure 13

In this selection you’ll need to use the driver you downloaded earlier in this article. You want to pick the SQL Server Native Client so that you can connect to the SQL Azure site. Don’t click Next here — instead, click the “Advanced” button to bring up the next panel.

Figure 14

This is where you’ll paste the string from the Notepad session you have running. After you paste it, select the “Verify this connection” box and click the “OK” button. That brings up this panel.

Figure 15

De-select the “Use Trusted Connection” box, and then enter the information for the admin user you created in the SQL Azure Portal. Click the “OK” button to test the connection and then to leave the File DSN pane.

If all that was successful, you should see the list of tables (along with the system tables) from your SQL Azure database.

Figure 16

Click each table you want to work with and then click the “OK” button.

And there you have it — the tables from the SQL Azure database, accessible and ready in Microsoft Access. Now you can create screens, reports, whatever you wish from Microsoft Access, and the data is stored three times in SQL Azure for backup. The users pay for their own data, and you can bring that data back to an on-premise SQL Server any time you like. Another advantage is that the users can work on this data from any location, provided the SQL Azure firewall is set to allow them to do so.