Chapter 1. Getting started with Microsoft Azure SQL Database - Microsoft Azure SQL Database Step by Step (2014)

Microsoft Azure SQL Database Step by Step

Chapter 1. Getting started with Microsoft Azure SQL Database

Leonard Lobel

In this chapter, you will create your first database on the Microsoft Azure SQL Database platform—completely from scratch. From scratch means that all you need to follow along is a web browser (the chapter uses Internet Explorer) and Internet access. You will sign up for a Microsoft account (if you don’t already have one), and use your Microsoft account to create and access a free trial subscription to Microsoft Azure. Then we’ll introduce you to the Microsoft Azure management portal, and you’ll quickly get a server and database up and running in the cloud. Finally, you’ll use the SQL Database management portal to design, populate, and query the database.

Image Note

We’ll often refer to Microsoft Azure SQL Database simply as SQL Database. The term SQL Server refers exclusively to on-premises database instances, while the term SQL Database always means the cloud-based Microsoft Azure SQL Database.

Although this book is focused on Microsoft Azure SQL Database, you’ll find it helpful to understand SQL Database in the broader context of the Microsoft Azure platform, and cloud computing in general. This understanding will greatly enhance your appreciation of SQL Database. So, before signing up for a Microsoft account, here’s a brief high-level discussion of cloud computing with Microsoft Azure.

Cloud computing: The concept

Microsoft Azure is Microsoft’s cloud-computing platform. But what exactly is cloud computing? The fact is, there really is no precise definition; indeed, cloud computing is an ambiguous term. Many different types of services exist today that run “in the cloud.” Fundamentally, then, cloud computing refers to the evolution of Internet hosting in which providers (such as Microsoft, Google, Amazon, and others) offer services to consumers and businesses that run on redundant hardware, with system maintenance that’s either partially or fully automated. This is a level of service beyond traditional Internet hosting that emerged with the dot-com bubble in the 1990s. With cloud computing, the Internet is not merely used as a medium for sharing information. Indeed, the cloud leverages the Internet as a way of connecting clients to various infrastructure, platform, and application services with a far greater degree of flexibility and abstraction than previous hosting schemes could possibly offer.

One of the earliest cloud-computing platforms was Amazon Web Services (AWS), introduced back in 2002 by Still today, AWS is prominently positioned as a serious contender in the cloud service industry. Since the mid-2000s, cloud computing has been rapidly gaining popularity, and in 2009, Microsoft unveiled Microsoft Azure (which was called Windows Azure until the name was changed in April 2014). Even as Azure launched, and steadfastly ever since, Microsoft has been expanding its cloud platform with newer and more robust capabilities.

Instant dynamic provisioning

To start with, provisioning on-premises servers on your own is difficult. First you need to purchase and physically install the hardware. Then you need to get the necessary software license or licenses, install the OS, and deploy and configure your application. You’ll also need to ensure acceptable performance levels and continuous uptime in the event of unexpected hardware, software, or network failures. That means configuring load balancing and redundancy using mirroring and clustering technologies. You’ll have to devise a backup strategy and attend to it religiously as part of an overall disaster-recovery plan, which you’ll also need to establish. And once all that is set up, you’ll still need to maintain everything to keep a healthy system running for the lifetime of your application.

Without exaggeration, moving to the cloud eliminates all these burdens.

In short, the idea of applications and services running in “the cloud” means that you’re dealing with intangible hardware resources, which in turn, translates to a maintenance-free runtime environment. You sign up with a cloud-hosting company (Microsoft, in the case of Azure) for access, pay them for how much power (in terms of resources) your applications require (RAM, CPU, storage, bandwidth, scale-out load balancing, and so on), and let them worry about all the rest. Compared to the manual labor and potential for error involved in doing things yourself, it’s both hassle free and risk free.

The Microsoft Azure cloud

With Azure, your applications, services, and data reside in the cloud. The Azure cloud is backed by large, geographically dispersed Microsoft data centers equipped with powerful servers, massive storage capacities, and very high redundancy to ensure continuous uptime.

But the Microsoft Azure environment is much more than a set of conventional web-hosting facilities on steroids. In fact, your cloud-based applications and services don’t actually run directly on these server machines. Instead, sophisticated hypervisor virtualization technology runs on top of all this physical hardware. Your “code in the cloud,” in turn, runs on that virtualization layer. So scaling out during peak season becomes a simple matter of changing a configuration setting that increases the number of running instances to accommodate the increase in demand. When the busy season is over, it’s the same simple change to reduce the instance count and scale back down. Microsoft Azure manages the scaling by dynamically granting more or less hardware processing power to the virtualized runtime environment. The process, often referred to as elastic scaling, is practically instantaneous.

Now consider the same scenario with conventional infrastructure. You’d need to purchase and install servers, bring them online, and add them as members to a load-balanced farm. And then you’d need to take them offline to be decommissioned later when the extra capacity is no longer required. That requires a great deal of work and time—either for you directly or for your hosting company—compared to tweaking some configuration with only a few mouse clicks.

Because cloud solutions can be delivered in lots of different ways, many new terms and buzzwords have infiltrated our vocabulary in recent years. Among them are the various “as-a-service” acronyms, including Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and Software as a Service (SaaS). All these terms obviously refer to services; their differences lie in the level of service. It’s often helpful to think of these terms as gradations of abstraction, starting with the lowest level of the underlying hardware infrastructure. When you’re on-premises, you have no abstraction at all, and you are intimately involved with and responsible for everything from the hardware on up. When you move to the cloud, you can go IaaS, Paas, or SaaS as your needs dictate, where each of those approaches provide increasingly greater abstractions.

Infrastructure as a Service

With IaaS, Microsoft Azure effectively gives you virtual machines (VMs) that are entirely under your control. Just as in an on-premises environment, you’ll be responsible for installing the OS and configuring the machine. It’s easy to build virtual machine images from scratch—or to customize existing virtual machines from a library of preconfigured VMs—and then deploy them to run in the cloud with full network connectivity (even Virtual Private Network [VPN] connections) and configurability. But unlike working on-premises, you’ll never need to handle screwdrivers, hard drives, cables, racks, power supplies, motherboards, RAM, or anything like that ever again. This is true IaaS—abstraction of hardware (networking, storage, severs, virtualization), and nothing else.

With this capability, you could certainly create a VM on Microsoft Azure that runs Microsoft SQL Server; that is, a virtual machine in the cloud that itself is running the full on-premises version of SQL Server. There might be situations where that is entirely justified and valid—for example, if you require full compatibility with on-premises SQL Server, which SQL Database does not provide. (We discuss the differences between these two platforms in Chapter 3, “Differences between SQL Server and Microsoft Azure SQL Database.”) A prime example of this scenario is to deliver cloud reporting with SQL Server Reporting Services (SSRS) running in a Microsoft Azure VM (which you learn how to do in Chapter 6, “Cloud reporting”). But understand that running SQL Server in an Azure VM is completely different than using Microsoft Azure SQL Database. Going with IaaS and SQL Server means that you are still responsible for maintaining your virtual machine or machines in the cloud. This includes installing the operating system from scratch (unless you choose to upload a preconfigured VM from the gallery), installing SQL Server, configuring instances, keeping the software up to date, protecting VMs from faulty software, and backing up data. If your VM crashes, then it’s your crisis. SQL Database is entirely different, which is what makes it a PaaS solution.

Platform as a Service

With PaaS, the abstraction level gets raised above IaaS so that you are also shielded from the operating system, middleware, and runtime layers. This means that Microsoft Azure also provides a platform for your applications and services to run on. You have no control over the platform; you get to manage only applications and data, while the cloud provider manages the rest of the infrastructure. You still get to create and test your applications locally and then upload them to run on Microsoft Azure. (We cover this in Chapter 10, “Building cloud solutions.”). This gives your application incredible scalability without requiring the investment in expensive hardware that such scalability would normally require in any on-premises scenario.

SQL Database, too, is a PaaS solution. It’s still SQL Server, but to deliver a relational database platform (as opposed to infrastructure), certain features that are available on-premises are not supported. With SQL Database, you can provision servers and databases on the fly, without ever interacting with the OS or other underlying infrastructure. You will never need to know or care if your data and log files are stored on a C drive or a D drive, because SQL Database handles all details of physical storage for you. As you’ll learn about in Chapter 3, enjoying the benefits of virtually instantaneous provisioning and risk-free, care-free maintenance also means incurring some loss of control that you normally get to exercise when working with SQL Server on-premises.

Software as a Service

SaaS is at the high end of the abstraction spectrum, where everything from the hardware up to and including the end-user application is handled by the service. There are many cloud SaaS offerings available today, including Office 365, CRM, and

You can create your own SaaS solutions with SQL Database by layering a service or website—also hosted on Azure—over the database. (You’ll do this in Chapter 10.) You could then offer this as a complete solution to your customers, who interact only with the application through their browser or mobile device. Your customers are not concerned with any aspects of infrastructure or platform. They just connect to your application. So, from their perspective, you have delivered true SaaS.

Getting signed up for SQL Database

To start using SQL Database, you need two things: a Microsoft account and a Microsoft Azure subscription. It’s quite possible that you already have a Microsoft account, which was formerly known as a Windows Live ID. This is the same account you might be using today for logging in to various Microsoft websites and services, such as, Hotmail, Xbox LIVE, Windows Phone, OneDrive (formerly SkyDrive), and other Microsoft offerings.

Creating a Microsoft account

The very first step before you can use any Microsoft Azure service is to acquire a Microsoft account if you don’t already have one. This is essentially an email address and password combination you will use to create and access your Microsoft Azure subscription. If you already have a Microsoft account, you can use it now to create a new Azure subscription. There’s no need to create another account, so you can just skip ahead to the next section, “Creating a Microsoft Azure subscription.” Otherwise, you’ll need to create one now.

Image Tip

If you already have a Microsoft account but you want to use a different email address for any reason, you still don’t need to create a new account. You can either rename the existing account or create an alias. See more information.

If you do create a new Microsoft account, the user name can be an email address you already own. Alternatively, you can create a new email address for the account that ends either with or It really makes no difference which you choose, as long as the name you provide has not already been taken by someone else at either or If you do choose to create a new email address, you will also get a new mailbox account at that address, and Microsoft will communicate with you via that mailbox any time it needs to notify you about important information regarding your account.

Whether you use an existing email address or create a new one, you’ll also need to assign a strong password to protect the Microsoft account. Some additional personal information is also required, such as your name, gender, one of two forms of identity confirmation, your country, and your postal/Zip code.

Follow these steps to create your new Microsoft account:

1. Using Internet Explorer, browse to This displays the Create An Account page, as shown in Figure 1-1.


FIGURE 1-1 Signing up for a new Microsoft account

2. Provide your first name and last name.

3. For the Microsoft account user name (which is what you will be logging on to the Microsoft Azure portal with), provide an existing email address. Or click the Or Get A New Email Address link to create a new one available on either or

4. Supply a password, and then reenter it to confirm. The account requires a strong password of at least eight characters that must contain a combination of mixed case, numbers, and symbols.

5. Provide your country and postal/Zip code, birthdate, and gender.

6. Provide a phone number or alternate email address. You must provide at least one of these identity-confirmation methods.

7. Type the random characters generated to prove that you’re a real person.

8. Click the Create Account button.

If you created a new email address in step 3, a mailbox is created for it and you are directed immediately to the Account Summary page. If you provided an existing email address, you will receive an email at that address from the Microsoft account team shortly after clicking Create Account. This email is sent to verify that you do, in fact, own the email address you provided. Your new Microsoft account will not become activated until you click on the verification link provided in the email.

Creating a Microsoft Azure subscription

Now that you have your Microsoft account, it’s time to create an Azure subscription. The subscription is essentially your Microsoft Azure billing account, and that opens the gateway to the full range of services available on Microsoft Azure—including, of course, SQL Database.

In the procedure that follows, you will create a free trial subscription to Microsoft Azure. At the time of this writing, the free trial gives you $200 of credit for 30 days with access to all services. This requires providing credit card information that will be used to bill your subscription after your trial expires.

Image Important

Microsoft Azure pricing and special offers are subject to ongoing change. We strongly recommend that you visit to review the latest pricing structures available. Furthermore, special pricing is available for MSDN subscribers. See for more information.

Follow these steps to create your new Microsoft Azure subscription:

1. Using Internet Explorer, browse to

2. Click the green Try For Free button.

3. On the next page, click the green Try It Now button.

4. If you are not already logged in to your Microsoft account, log in now.

5. You will be taken to the Free Trial Signup page, as shown in Figure 1-2.


FIGURE 1-2 Signing up for a new free trial Microsoft Azure subscription

6. Choose to either receive a text message or phone call as the method to receive a verification code.

7. Enter the code received via the text message or phone call, and click Verify Code.

8. Provide the credit card payment details for billing after the free trial expires.

9. Select the box to indicate that you agree to all the terms.

10. Click the green Sign Up button.

It takes just a few moments to complete setting up your new Azure subscription, and then you’re ready to get started working with SQL Database and all the other Microsoft Azure services.

Creating a server

It’s easy to create a server, which is akin to an instance of SQL Server in the sense that it can host multiple databases. All you need to do is create an administrator account user name with a strong password, and specify the geographical region where the server should be located physically. To achieve the best performance, you should choose the region closest to your consumers. As we discuss in Chapter 2, “Configuration and pricing,” you will also want to be sure that any Microsoft Azure cloud Web sites and services (such as the ones you’ll create in Chapter 10) are hosted in the same region as the SQL Database servers they communicate with. By locating both in the same region, you will avoid the bandwidth-based fee that gets incurred when your cloud sites, services, and databases communicate across different Azure regions. You will also reduce latency, which results in significantly better performance.

SQL Database also has special firewall rules you can set to control exactly which computer or computers can access your database server in the cloud. Minimally, you’ll need to add a rule granting access to the IP address of your computer so that you can access the server from your local machine. For production, you might need to add rules granting access to blocks of IP addresses. You will learn more about firewall rules in Chapters 2 and 5.

Follow these steps to create a new server:

1. Log in to the Microsoft Azure portal at This brings you to the main portal page showing ALL ITEMS, as shown in Figure 1-3.


FIGURE 1-3 The Microsoft Azure Management Portal with no services yet configured

Image Note

The first time you log into the portal, you are welcomed with a message that offers to give you a brief tour. You can take the tour if you wish, or close the message to proceed to the main portal page.

2. As illustrated in Figure 1-4, first click SQL DATABASES in the vertical navigation pane on the left, then click SERVERS at the top of the page, and then click CREATE A SQL DATABASE SERVER.



3. Provide a new server login name—for example, saz.

4. Supply a password for the new server, and then reenter it to confirm. Typical strong password rules apply, which require you to use a combination of mixed case, numbers, and symbols.

5. Choose a region from the drop-down list—for example, East US. For best performance, pick the region you are located in or nearest to.

6. Be sure to leave the ALLOW WINDOWS AZURE SERVICES TO ACCESS THE SERVER check box selected. This makes the server accessible to the Microsoft Azure cloud services that you’ll create or use in other chapters (Microsoft Azure was formerly called Windows Azure). The page should appear similar to Figure 1-5.



7. Click the checkmark icon on the lower-right side of the dialog to complete the settings. After just a few moments, the new server is provisioned and ready to use, as shown in Figure 1-6.


FIGURE 1-6 The new SQL Database server

If you’ve ever prepared a new on-premises server from scratch yourself, you can really appreciate the time and effort you just saved. This server is now available and ready to host databases in the cloud, and SQL Database has automatically assigned a randomly unique (but relatively short) name by which it can be accessed. But before access is granted, the server firewall must be configured. So the next step is to add a firewall rule so that you can connect to the server from your local machine.

The check box mentioned in step 6 added the special IP address, which allows cloud services running on Microsoft Azure to access the SQL Database server. However, you still need to add the IP address of your local machine to access the server from the SQL Database management portal and other tools (such as SQL Server Management Studio and SQL Server Data Tools in Microsoft Visual Studio, which you learn more about in later chapters).

To add a firewall rule for the IP address of your local machine, follow these steps:

1. Click the server name, and then click the CONFIGURE link at the top of the page.

2. To the right of your current detected IP address, click ADD TO THE ALLOWED IP ADDRESSES, as shown in Figure 1-7. A new firewall rule for your IP address is added.


FIGURE 1-7 Adding your local IP address to the list of IP addresses allowed though the firewall

3. Click SAVE at the bottom of the page.

4. Click the back icon (the large back-pointing arrow) to return to the SQL DATABASES page for the new server.

You might need to wait a few moments for the new firewall rule to take effect, although typically it happens very quickly (often within five to ten seconds). If you don’t wait long enough, however, and the rule has not yet taken effect, you can be quite certain that you will not be able to connect to the server from your local machine until it does.

Creating a SQL Database instance

It will be just about as easy to create a database as it was to create the server. You simply need to choose a name for the new database, an edition, a maximum size, a default collation, and of course, the server to host the database on.

In Chapter 2, you’ll learn more about the different options for database edition and maximum size. For right now, the important thing to know is that all these settings (except for the default collation) can be easily changed later on. As part of the elastic scaling provided by SQL Database, you can freely switch back and forth between the Web and Business editions. You can also switch up and down between the sizes (1 GB or 5 GB for the Web edition, or 10 GB through 150 GB for the Business edition) as your changing needs dictate. And if 150 GB is still too small for you, you can partition your database using special sharding techniques, as we explain in Chapter 8, “Designing and tuning for scalability and high performance.”

Follow these steps to create a new SQL Database:

1. If you are continuing from the previous procedure, click the DATABASES link at the top of the page and then skip to step 4. Otherwise, if you have logged out since then and are starting fresh, continue with step 2.

2. Log in to the Microsoft Azure portal at This brings you to the main portal page showing ALL ITEMS, as shown in Figure 1-3 earlier.

3. Click SQL DATABASES in the vertical navigation pane on the left.

4. Click CREATE A SQL DATABASE, as shown in Figure 1-8. This opens the NEW SQL DATABASE dialog.


FIGURE 1-8 The CREATE A SQL DATABASE link on the portal’s SQL DATABASES page

5. Type the name for the new database, WineCloudDb (yes, you’ll be in the wine business).

6. Leave the default settings to create a Web edition database up to 1 GB in size using the SQL_Lating1_GeneralCP1_CI_AS collation.

Image Note

Chapter 2 discusses the Web and Business editions, the maximum database sizes, and the significance of SQL Database collations.

7. Choose the server you created in the previous procedure from the drop-down list. The page should appear similar to Figure 1-9.



8. Click the checkmark icon in the lower right of the dialog to complete the settings.

After a few more moments, the new WineCloudDb database is created and ready to use, as shown in Figure 1-10.


FIGURE 1-10 The new WineCloudDb SQL Database

Using the SQL Database management portal

Up to this point, you’ve used the Microsoft Azure management portal to create a SQL Database server and database. The Azure management portal is an HTML-based interface, which—parenthetically—replaced the earlier Silverlight-based interface back in mid-2012. Actually designing a database, however, is performed using a different portal, the SQL Database management portal, which is still Silverlight-based at the time of this writing. In this section, you’ll learn how to access the SQL Database management portal from the Microsoft Azure management portal.

Follow these steps to access the SQL Database management portal:

1. If you are continuing from the previous procedure, skip to step 4. Otherwise, if you have logged out, continue with step 2.

2. Log in to the Microsoft Azure portal at This brings you to the main portal page showing ALL ITEMS, as shown in Figure 1-3 earlier.

3. Click SQL DATABASES in the vertical navigation pane on the left.

4. Click the WineCloudDb database.

5. Click the DASHBOARD link at the top of the page.

6. Scroll the page down a bit, and find the MANAGE URL link in the quick glance section at the right of the page, as shown in Figure 1-11.


FIGURE 1-11 Finding the link to the SQL Database management portal for database WineCloudDb

7. Click the MANAGE URL link. This opens a new browser tab to the SQL Database portal’s login page.

Image Note

The SQL Database portal is Silverlight-based. If you don’t have Silverlight installed, you will first be prompted to download it before you can use the portal.

8. Type the user name (for example, saz) and password you specified when you created the server, as shown in Figure 1-12.


FIGURE 1-12 The SQL Database management portal login page

9. Click Log On. Once you have authenticated, you are taken to the Summary view of the Administration tab for the database, as shown in Figure 1-13.


FIGURE 1-13 The Summary view shows properties for the WineCloudDb database

Designing tables and relationships

Now you’re ready to create some tables. You’ll create two tables now, though you will extend this design a bit more in other chapters. It’s easy to create a new table, as you’ll see in the next procedure. Just click the Design tab on the left side of the page and then click New Table, as shown in Figure 1-14.


FIGURE 1-14 Creating a new table

This opens the table designer, which you’ll use now to create a table for storing wine products, as shown in Figure 1-15.


FIGURE 1-15 Defining the Wine table using the portal’s table designer

Creating the Wine table

First create the Wine table. Every row in this table is another wine product that your company sells to customers, so you’ll design the Wine table to include the columns WineId (the primary key that uniquely identifies each individual wine product), Name, Category, and Year.

To create the Wine table, follow these steps:

1. Log in to the SQL Database management portal for the WineCloudDb database, as described in the previous procedure.

2. Click the Design tab on the left side of the page.

3. Click New Table. The table designer opens with a default name of Table1, an integer ID column, and two string columns named Column1 and Column2.

4. Change the Table Name to Wine.

5. Change the ID column name to WineId, leaving it as the required primary key.

6. Select the Is Identity? check box for the WineId column. When you insert new wine products into the table, this setting tells SQL Database to automatically assign incremental integer values for this column in each new row.

7. Change the Column1 column name to Name, leaving it as a required nvarchar(50) string.

8. Change the Column2 column name to Category, leaving it as a required nvarchar(15) string.

9. Click Add Column. This adds another integer column named Column1 to the table design.

10. Change the new column name to Year, and leave it as an optional integer (meaning you do not select the Is Required? check box).

11. Click Save in the toolbar at the top of the designer.

You have now created the Wine table, which should appear similar to the image shown in Figure 1-15 earlier.

Creating the Customer table

Now follow a similar procedure to create the Customer table, with the columns CustomerId (the primary key), FirstName, LastName, and FavoriteWineId. The FavoriteWineId column relates to the WineId primary key column in the Wine table, so FavoriteWineId in the Customer table is aforeign key. After creating the Customer table, you will establish a relationship between its FavoriteWineId foreign key column and the primary key column WineId in the Wine table.

To create the Customer table, follow these steps:

1. Click the [WineCloudDb] tab on the top left side of the page.

2. Click the Design tab on the bottom left. This takes you to the same page you used before when you created the Wine table. (See Figure 1-14.)

3. Click New Table.

4. Change the Table Name to Customer.

5. Change the ID column name to CustomerId, leaving it as the required primary key.

6. Select the Is Identity? check box for the CustomerId column.

7. Change the Column1 column name to FirstName, leaving it as a required nvarchar(50) string.

8. Change the Column2 column name to LastName, leaving it as a required nvarchar(15) string.

9. Click Add Column.

10. Change the new column name to FavoriteWineId, leaving it as an optional integer (meaning you do not select the Is Required? check box).

11. Click Save.

Now the database has Wine and Customer tables. These tables store (obviously) your wine products and your customers, though (equally obviously) they are both empty at this point.

Defining the table relationship

Before populating the tables with data, you will establish the foreign-key relationship between FavoriteWineId in the Customer table and WineId in the Wine table. Note that it isn’t strictly necessary to do this. However, it’s best practice to let SQL Database know about the relationships between tables so that it can enforce referential integrity in your data. (For example, SQL Database will ensure that the integer value in each customer’s FavoriteWineId column refers to an existing row in the Wine table that can be located by WineId.) It also helps SQL Database devise more efficient query plans internally when you join on table relationship in your queries.

The SQL Database management portal offers a foreign-key management experience that makes defining the relationship easy, as shown in Figure 1-16.


FIGURE 1-16 Defining table relationships using the foreign-key designer

To define the relationship between the two tables, follow these steps:

1. While still in the Customer table design page, click Indexes And Keys at the top of the page.

2. On the right side of the page, click Add A Foreign Key Relationship. The foreign-key designer appears as shown in Figure 1-16.

3. Select the FavoriteWineId column in the Customer table. This specifies the foreign-key column.

4. Change the foreign-key name (assigned as FK_Customer_0 by default) to FK_Customer_Wine.

5. Click Select A Reference Table, and choose Wine.

6. Click Select A Reference Column, and choose WineId.

7. Click Save.

The relationship is created, and the designer should now appear similar to Figure 1-17.


FIGURE 1-17 A completed table relationship in the foreign-key designer

Inserting data

Most likely, you’ll be migrating from existing on-premises SQL Server databases (as we cover in Chapter 4, “Migrating databases”), or perhaps you’ll be building applications that load data into the database (covered in Chapter 10). But for quick and raw data entry, the SQL Database management portal offers a convenient way to interactively insert rows of data into tables (as you can see in Figure 1-18), without coding Transact-SQL (T-SQL) INSERT statements.


FIGURE 1-18 Populating a table with data

To populate the Wine table with sample products, follow these steps:

1. Log in to the SQL Database management portal for the WineCloudDb database, and navigate to the Wine table design page, as you did in previous procedures.

2. Click Data at the top of the page.

3. Click Add Row.

4. Enter a row with Name, Category, and Year values of Chateau Penin, Bordeaux, and 2008, respectively.

Image Note

Because you selected the Is Identity? check box for the WineId column when you designed the table, the designer displays <auto> to indicate that SQL Database will automatically assign a value for WineId when you save these rows to the database.

5. Repeat steps 3 and 4 to enter three more wines to add to the table as follows:

a. Enter a row for McLaren Valley, Cabernet, 2005.

b. Enter a row for Mendoza, Merlot, 2010.

c. Enter a row for Valle Central, Merlot, 2009.

6. Click Save in the toolbar at the top of the page.

None of the data you entered is actually saved to the database until you click Save in step 6. At that point, the rows are inserted and the display is refreshed to show the WineId primary-key values that were automatically assigned by SQL Database. Being the very first four rows added to the table, those primary keys were assigned the numbers 1 through 4, as shown in Figure 1-19.


FIGURE 1-19 The Wine table populated with rows of data with automatically assigned primary-key identity values

Now add some data to the Customer table. You’ll use the same procedure you just followed for the Wine table. The only additional consideration to keep in mind is that each customer has a foreign-key value that identifies that customer’s favorite wine. Because you informed SQL Database about this foreign-key relationship in a previous procedure, you can only supply a value of 1 through 4 for each customer’s FavoriteWineId column (or other integers for rows that are added to the Wine table in the future). Your only other option is to supply NULL, becauseFavoriteWineId is optional (meaning that it’s OK if the customer’s favorite wine is unknown). Otherwise, as we’ll demonstrate, SQL Database will not permit you to add a customer row with a non-NULL value for FavoriteWineId that does not have a related row in the Wine table.

To populate the Customer table, follow these steps:

1. Navigate to the Customer table design page.

2. Click Data at the top of the page.

3. Click Add Row

4. Enter a row with FirstName, LastName, and FavoriteWineId values of Jeff, Hay, and 4, respectively.

5. Click Add Row again to enter another row for Mark, Hanson, 3.

6. Click Save to save the rows to the database. The two rows are automatically assigned primary key values of 1 and 2 for the CustomerId column.

7. Click Add Row again to enter a third row for Jeff, Phillips, but this time type a 6 for the FavoriteWineId.

8. Click Save. Now SQL Database complains of an error.

9. Expand the Error Details as shown in Figure 1-20. This displays the error message describing the foreign-key conflict that occurred because there is no row in the Wine table with a WineId of 6.


FIGURE 1-20 Error message displayed when attempting to violate a defined foreign-key relationship

10. Correct the problem by changing the 6 to a 2.

11. Click Save. Now Jeff Phillips gets saved to the Customer table. (See Figure 1-21.)


FIGURE 1-21 The Customer table populated with several rows of data

Interestingly, the Jeff Phillips row was assigned a primary key value of 4, skipping over the next available value (which, after Mark Hanson, would have been 3). This is because the number 3 got “used up” during the failed insert attempt that occurred with the foreign-key conflict,

Querying the database

Now you have a database with two related, populated tables. The next logical step is to query this data. Because SQL Database is essentially an adapted version of on-premises SQL Server tailored for running on Microsoft Azure, most typical queries can be expressed using the same T-SQL syntax that works with on-premises SQL Server. So the Customer and Wine tables can be easily joined together to display the names of each customer’s favorite wine. Furthermore, they can be filtered to limit the results by some specific criteria.

The SQL Database management portal has an ad-hoc query window that lets you run T-SQL queries and view their results. To query for customers and their favorite wines, follow these steps:

1. Log in to the SQL Database management portal for the WineCloudDb database.

2. Click New Query at the top of the page to open a new query window.

3. Type the following T-SQL code into the query window:

Customer AS c
LEFT OUTER JOIN Wine AS w ON c.FavoriteWineId = w.WineId
c.LastName, c.FirstName;

4. Click Run at the top of the page. SQL Database executes the query and displays the results in the bottom portion of the query window, as shown in Figure 1-22.


FIGURE 1-22 Creating and running a query that joins customers with their favorite wine

5. Modify the query by adding a WHERE clause. Just before the ORDER BY clause, type WHERE w.Category = ‘Merlot’

6. Click Run again. The query executes once more, this time returning only customers whose favorite wine is a Merlot, as shown in Figure 1-23.


FIGURE 1-23 Applying a query filter using the WHERE clause

7. Change the WHERE clause of the query from w.Category = ‘Merlot’ to w.Year < 2010.

8. Click Run once more. This time, the query filters on the wine year, as shown in Figure 1-24.


FIGURE 1-24 Filtering on the customer’s favorite wine year

In step 3, notice how you joined the Customer and Wine tables in the query’s FROM clause. The LEFT OUTER JOIN ensures that customer rows are returned even if they contain a NULL in FavoriteWineId—that is, even if they have no favorite wine. Using an INNER JOIN instead would automatically exclude customers without a favorite wine.

The initial version of this query had no WHERE clause, and with a LEFT OUTER JOIN, there was no filtering at all. So, at first, it returned every customer and displayed their favorite wine. (NULL would be returned for the wine name and category for customers without a favorite wine.)

By adding the WHERE clause in step 5, you asked SQL Database to filter the results to include only customers whose favorite wine is any kind of Merlot. The Wine table was aliased as w in the FROM clause, so w.Category in the WHERE clause refers to, and filters by, the Categorycolumn in the row joined in from the Wine table. Running this version of the query returns just the two customers with Merlot as their favorite wine category.

In step 7, you changed the WHERE clause to filter by w.Year, which is the year of the wine. Notice how this column is not actually in the result set returned by the SELECT statement, yet it is perfectly valid to filter on it. This version of the query now returns the two customers with favorite wines (in any category) older than 2010. These are Jeff Hay (with a Velle Central Merlot from 2009) and Jeff Phillips (with a McLaren Valley Cabernet from 2005). Mark Hanson’s favorite wine is the Mendoza Merlot, but he is filtered out from these results because that wine is from 2010, and the query is returning only customer rows with favorite wines that are older than 2010.

Exploring additional portal capabilities

In just this first chapter alone, you’ve already accomplished quite a lot with SQL Database. You’ve created a server and a database, defined tables with relationships, populated data, and executed queries. And you’ve done all those things with nothing more than a browser, using the Microsoft Azure management portal and the Microsoft Azure SQL Database management portal. But all that is still just scratching the surface of what’s possible.

The SQL Database management portal has matured greatly since the early days of Microsoft Azure (when it was called the SQL Azure management portal). And you can expect it to continue evolving—quite possibly even by the time this book goes to press. Before concluding the chapter, we recommend you take the time to examine some of these additional capabilities available in the current SQL Database management portal at the time of this writing.

Creating views

Views are essentially encapsulated queries that are stored in the database. In most respects, your queries can treat views just as ordinary tables.

For example, you could create three views that encapsulate the three versions of the query from the previous section “Querying the database.” You might name those three views as follows:

Image CustomersWithFavoriteView

Image CustomersWIthFavoriteMerlotView

Image CustomersWithFavoritePre2010View

With those views in place, it becomes much easier to query the database. For example, you can just select from the CustomersWithFavoriteMerlotView, instead of writing the lengthier version of the query that joins the Customer and Wine tables.

Creating stored procedures

Like views, stored procedures can also encapsulate queries, but they can also do much more than that. In addition to returning data from queries, stored procedures can contain any T-SQL logic. They can also accept parameters, update data, and call other stored procedures.

Stored procedures are commonplace in professional relational databases. They are often used to protect underlying tables from inappropriate usage. They can also build on views, implement business logic, or further abstract details of the underlying database structure—hiding the way that tables, views, and columns are named; how the table relationships are defined; and so on. Essentially, and particularly from the perspective of designing multitiered layered architectures, stored procedures can be effectively leveraged to implement a service layer over your data, at the database level.

Here’s a stored procedure you can create that joins the Customer and Wine tables to return customers with their favorite wine, just as you did in the previous query. But this stored procedure will have some added flexibility; it will accept a @FavoriteWineId parameter so that the results can be limited to returning just those customers whose favorite wine matches the value passed in through this parameter. If NULL is specified for @FavoriteWineId, the stored procedure will return all customers. As before, you will use an OUTER JOIN, so if no parameter value is passed, even customers with no favorite wines will be returned (and NULL will be returned for the name of their favorite wine). You will aptly name this stored procedure GetCustomers.

To create the GetCustomers stored procedure, follow these steps:

1. Log in to the Microsoft Azure SQL Database portal.

2. Click the Design tab on the left side of the page.

3. Click Stored Procedures at the top of the page.

4. Click New Stored Procedure. The stored procedure designer opens with a default name of Stored Procedure1.

5. Change the Stored Procedure name to GetCustomers.

6. Click Add Parameter to create a new parameter named @Parameter1 with a data type of nvarchar(50).

7. Change the parameter name to @FavoriteWineId.

8. Click the drop-down list beneath Select Type to change the parameter data type to int.

9. Type the following code into the code window:

c.FirstName, c.LastName, w.Category, w.Name
Customer AS c
LEFT OUTER JOIN Wine AS w ON c.FavoriteWineId = w.WineId
(@FavoriteWineId IS NULL) OR (c.FavoriteWineId = @FavoriteWineId)
c.LastName, c.FirstName;

10. Click Save at the top of the page.

Your screen show appear similar to Figure 1-25.


FIGURE 1-25 Designing the GetCustomers stored procedure

As we explained, this stored procedure accepts an integer parameter named @FavoriteWineId. The stored procedure encapsulates the same join logic between Customer and Wine in the FROM clause you’ve already seen, but it also adds logic in the WHERE clause to determine if a favorite wine was requested (that is, if the value for the @FavoriteWineId parameter was not passed in as NULL). It therefore returns either all customers (if NULL was passed in for @FavoriteWineId) or only those customers with favorite wines that match the value in @FavoriteWineId.

To test the stored procedure, follow these steps:

1. Click New Query at the top of the page to open a new query window.

2. In the new query window, type EXEC GetCustomers @FavoriteWineId = NULL.

3. Click Run at the top of the page to execute the stored procedure, passing in NULL for the @FavoriteWineId parameter. SQL Database returns all three customers and the names of their favorite wine.

4. Modify the EXEC statement from step 2 by changing NULL to 3, which is the WineId for the 2010 Mendoza Merlot.

5. Click Run again. This time, only Mark Hanson is returned, because (currently) he’s the only customer that has selected 2010 Mendoza Merlot as his favorite wine.

Database administration features

The SQL Database management portal also offers several database administration features you should be aware of, such as DACPAC and BACPAC support (for migration and backup) and event tracking.

The portal supports the Data-tier Application Component Package file format, commonly referred to as DACPAC files. A DACPAC file contains the complete definition of a database, and it can be leveraged for streamlined incremental deployments of the database design. A BACPAC file is similar, except that in addition to the definition of the database, it includes actual data as well. In the management portal, there is full support for importing and exporting DACPAC and BACPAC files. Locally, SQL Server Data Tools (SSDT) in Visual Studio can also be used to define DACPAC files and deploy them to Microsoft Azure SQL Database. You will learn more about DACPAC and BACPAC in later chapters.

You can also track events from the SQL Database management portal. This lets you keep an eye on things like database connections (whether they succeed or fail), deadlocks, and throttling events.


This chapter got you acquainted with Microsoft Azure SQL Database. We began with an overview of Azure and cloud computing, and then demonstrated how easy it is to get signed up for a Microsoft account and an Azure subscription. You then used the Microsoft Azure management portal to quickly create a new server, and then create a new database on that server.

You also learned how to use the SQL Database portal (which you launched from the Microsoft Azure portal using a special management URL) to design the database. You created and populated two related tables, and then you opened a query window to run a few SELECT queries that joined the tables. You also learned about creating views, creating stored procedures, and the availability of other database administration features in the SQL Database management portal.

Now that we’ve introduced you to the SQL Database platform, you’re ready to move on to Chapter 2, where we will delve more deeply into the details of setup and configuration.