Chapter 4. Creating and Managing a Windows Azure SQL Server Database

We're going to start building the database for our sales business domain, which will serve the customer website, administrator website, phone application, and order processor worker role. We'll cover the following topics:

• Creating a database in the portal

• Building the database using Entity Framework Code First Migrations

• Different tools for managing Azure SQL Servers and databases

• Backing up and restoring a database

Once we've covered these topics, we'll have complete local and Azure SQL databases and understand how to manage these databases using a number of different tools.

Note

Microsoft Azure services are continually expanding, improving, and changing, and Azure SQL Databases are no different. The Web and Business editions are due to be retired in April 2015, where they will be replaced with Basic, Standard, and Premium tiers, which will be available as a preview before then. These new tiers offer six levels of services for...

Creating a database using the Azure management portal

We're going to start by creating a database called AzureBakerySales for the sales websites in the Azure management portal:

1. From the NEW service menu, go to DATA SERVICES | SQL DATABASE | QUICK CREATE, and we will see this wizard that can be used to quickly create a basic database:

   

2. By default, this creates a WEB EDITION database with the lowest MAX SIZE allocation (1 GB) and the default collation (SQL_Latin1_General_CP1_CI_AS). It's possible to change the EDITION and MAX SIZE settings in the SCALE tab of the portal.

3. Instead of QUICK CREATE, we'll use the CUSTOM CREATE option, which gives us more control over the database options:

   1. From the NEW service menu, go to DATA SERVICES | SQL DATABASE | CUSTOM CREATE.

   2. Enter the details for the database in the NEW SQL DATABASE dialog. I've chosen to use BUSINESS as EDITION with 20 GB of MAX SIZE (this can be changed) the default COLLATION (http://msdn.microsoft.com/en-us/library/ms143726.aspx), and New...

Building a database using Entity Framework (EF) Code First Migrations

Entity Framework (EF) is Microsoft's object-relational mapper (ORM) for .NET, which allows developers to easily work with relational data inside their applications using domain objects. Instead of manually writing data access layers to read/write and parse data, as you would by using native ADO.NET, using an ORM saves time and effort. When we use EF in our projects, we have a number of different options to create our database and entities:

• Database-First: With this technique, we can create our database (or use an existing database) in a tool such as SQL Management Studio using SQL scripts or the designer. In Visual Studio, we can create an ADO.NET Entity Data Model (EDM) to create entities and map them to existing tables.

• Model-First: This is similar to Database-First, where we use an EDM to design our entities, but then, we let EF to create our database from it.

• Code First: Using the Code First approach, we get more...

Managing SQL Azure Servers and databases

Although we've just built our database using EF Code First Migrations, we still need to be able to view and sometimes modify our data and also perform activities such as adding users or creating federations.

To do this, we have a number of tools available:

• The SQL management portal

• SQL Server Management Studio (SSMS)

• Visual Studio SQL Server Object Explorer

• The PowerShell console

Managing a database through the portal

We're going to take a look at managing a database through the management portal:

1. In the database workspace, select the database we just created and click on MANAGE on the bottom toolbar:

   

2. We should see an alert appear, asking if we want to add our machine's public IP address to the firewall list:

   

3. Click on YES and YES again when it asks if you want to manage the database:

   

4. If nothing happens, check that the popup is not blocked.

5. We should now see a login screen for the database, so enter the SA details we noted earlier and click on Log on:

   

6. Once we...

Backing up and restoring databases

Although Microsoft Azure SQL Databases are replicated to different servers and data centers for disaster recovery, currently, they are not backed up to allow users to restore databases after accidental data loss. There are a number of ways of achieving backup and restore; we'll look at using automated exports to create backups on schedule and then restore them to a new database.

Automated exports

Automated exports allow us to back up databases to bacpac files stored in Azure blob storage (a blob storage is used to store unstructured binary and text data, you can learn more about blobs at http://msdn.microsoft.com/en-us/library/azure/ee691964.aspx) on a schedule. We can control how often backups are made and how long they are kept.

First, we need to create a storage account for the exports to be stored in. From the NEW service button, go to DATA SERVICES | STORAGE | QUICK CREATE, enter a URL, and choose the LOCATION/AFFINITY GROUP, SUBSCRIPTION, and REPLICATION...

Summary

We've now got the foundations for our sales business domain, so we can start building our applications. The production business domain will also have a database built using EF Code First Migrations, and this will not be covered in the book, but all the code will be available in the samples.

Next, we're going to build the sales customer website. This will allow customers to make and manage orders, which will implement an OAuth authentication provider and the administrator website. This will allow administrators to manage customers and orders and will implement Azure Active Directory authentication.

Questions

1. What impact would unchecking the ALLOW WINDOWS AZURE SERVICES TO ACCESS THE SERVER checkbox have, when creating a database from the portal?

2. Describe the Database-First approach to creating an Entity Framework data model

3. In an EF entity, what is a navigation property, and which property modifier must be used?

4. What is special about IdentityDbContext?

5. How do we relate a user entity (such as Customer) to an authenticated user?

6. How do we enable migrations in an EF project?

7. When we change our EF model and want to capture the change in migrations, what do we do?

8. What EF cmdlet do we use to build the database from our entity model?

9. What are the differences in features between using SSMS with Azure SQL Server and SQL Server?

10. If we wanted to design an Azure SQL Database table with a GUI editor, which tool would be the best choice?

11. Which SQL Azure PowerShell cmdlet would you use to delete a database?

12. Which SQL Azure PowerShell cmdlet would you use to set a firewall rule on an Azure SQL Database Server...

Answers

1. We'd have to manually configure the database server firewall to allow our services to access the database.

2. With this technique, we can create our database (or use an existing database) in a tool such as SQL Management Studio using SQL scripts or the designer, then in Visual Studio, we can create an ADO.NET EDM to create entities and map them to existing tables.

3. The Navigation properties allow us to access related entities through a given entity. We use the virtual modifier on navigation properties.

4. It extends a normal DbContext with access to the ASP.NET authentication users and roles tables.

5. Add a navigation property to our user entity in the ApplicationUser entity.

6. Enter the enable-migrations cmdlet in the NuGet package manager console.

7. Use the add-migration command with a label to indicate the model change, which will create a new migration with the differences from the previous migration.

8. update-database.

9. Azure SQL Server only has Database and Security options, which are a limited subset...