Microsoft Azure SQL Database Step by Step

Chapter 5. Security and backup

—Eric Boyd

The topics of security, availability, and disaster recovery top the list of concerns that customers raise when considering the public cloud. These are certainly not new concerns introduced with the cloud; customers have been architecting solutions to deal with these same concerns since long before the cloud. The cloud is simply unfamiliar territory that causes these foundational concerns to be revisited. Thus, customers need these top concerns addressed with reasonable solutions before the public cloud is a viable option. Microsoft does a great job of putting customers’ concerns at ease on these topics with the security processes and certifications that are in place in Microsoft Azure, along with the features of the platform that provide customers with the control and visibility they need.

In this chapter, we discuss security and backup concerns in the cloud. We start by explaining the general security responsibilities of any public cloud vendor, and then talk more specifically about security in Microsoft Azure and Microsoft Azure SQL Database. You will learn how to secure SQL Database by configuring the firewall as you create custom firewall rules and define users and permissions.

Security and backup often go hand in hand. Notwithstanding all other security-related concerns, how “secure” is your business if you have no backup in the event of an unforeseen disaster? So toward the end of this chapter, you will also learn how to copy and back up SQL Database, and how to schedule automated backups.

Addressing major cloud concerns

Two of the most common concerns users raise when considering public cloud platforms are security and business continuity, sometimes referred to as disaster recovery. Security is an overloaded term, and it can mean a lot of different things depending on the individual and context. So it is easier to think about security concerns by dividing them into two major categories: security concerns that are the sole responsibility of the public cloud vendor (Microsoft, in the case of Azure), and security concerns that are either the customer’s responsibility or the combined responsibility of the customer and public cloud vendor.

Security responsibilities of the public cloud vendor

Some security concerns can be managed and addressed only by public cloud vendors, because customer access is limited to higher-level abstractions over the raw computing infrastructure, resources, and services. The customer typically cannot gain direct access to things like network routers, switches, and firewalls, as well as physical servers and the hypervisor, which is the software layer that virtualizes the hardware for multiple operating systems to run on a single physical server. As a result, it is very important to have a reputable cloud vendor with a successful history that you can count on and trust. But you cannot rely only on faith in a vendor, you also need transparency and insight into the resources and practices of your cloud vendor, and this includes their security practices and procedures, as described in the following sections.

Physical data center

Access to the physical data center—including entry inside the outermost security fence, entry into the building, and access to the physical infrastructure and hardware—must be managed with secure policies that are consistently enforced. You want it to be extremely difficult, and ideally impossible, for an unauthorized person to gain physical access to your servers.

Privacy from vendor personnel

The personnel who are authorized to gain access to the computing infrastructure and resources should still not be able to access your data, unless you explicitly grant them permission to do so. Because you don’t manage the foundational infrastructure, the vendor must ensure that it’s secure with the appropriate safeguards to prevent their personnel from accessing your data without your permission.

Isolating tenants

As is the case with vendor personnel, you don’t want other tenants of the cloud vendor to be able to gain access to your data and applications. (Multitenancy is an architecture in which a single infrastructure component serves multiple customers, where each customer is called a tenant.) When you are using multitenant services, this is a concern that must be managed by the vendor.

Preventing cyber attacks

A malicious attack, such as a denial-of-service attack, could occur against your applications and services, or at a broader level against the cloud vendor’s services. When these kinds of attacks occur, you want the cloud vendor to detect them and prevent them from causing a service outage.

Shared security responsibilities

Other security concerns are either the customer’s responsibility or are shared between the customer and the cloud vendor. Whenever a security concern can be affected by the customer’s configuration, implementation, or software, it cannot be the responsibility of the cloud vendor alone. The customer must secure aspects of their application to resolve these security concerns.

Meeting compliance requirements

A number of industries and organizations must meet regulatory requirements because of the nature of their businesses and the data they handle. These requirements often span the physical data center, applications running in the data center, and management processes across both of these areas. As a result, you need to understand what compliance certifications your cloud vendor has achieved. But you also need to understand that you also have a responsibility to meet the requirements that are outside of the cloud vendor’s control that are application-centric and specific to your implementation.

Auditing activities

Much like compliance, knowing who did what and when they did it is a responsibility that is shared between the cloud vendor and the customer. Only the cloud vendor can track and provide an audit log of the activities that occur in the platform services. But it’s the customer’s responsibility to track the application-level activities. Because accurate and detailed auditing is a common requirement for most compliance certifications, it’s an important capability both for your cloud vendor and your applications to provide. A core requirement for effective auditing requires you to provide unique credentials for every user and ensure that users do not share their credentials. If multiple users share a single account, you cannot possibly know exactly who performed an activity logged for that account.

Keeping electronic intruders out

Let’s not forget the hackers who try to profit from stealing data and other hackers who just want to be malicious and aim to create chaos. You need to keep both of those types of hackers out. The cloud vendor must protect the infrastructure and core services and keep the electronic intruders out at that level. It’s your responsibility to protect your applications from exploitations, and if you manage the virtual machines (VMs) in an Infrastructure-as-a-Service (IaaS) model, you must patch and secure your operating system whenever it is exploited.

Security in Microsoft Azure

Microsoft invests a lot of effort and talent into making sure Azure is a secure and reliable public cloud. Microsoft also does a great job being transparent and providing insight into the security and privacy practices of Microsoft Azure. One of the ways they do this is via a website called the Microsoft Azure Trust Center, which can be found at http://azure.microsoft.com/en-us/support/trust-center/. The Microsoft Azure Trust Center provides detailed information on Microsoft’s practices that enable security, privacy, and compliance in Microsoft Azure. It is a great resource to gain a deeper understanding of security in Microsoft Azure and to find answers to your Microsoft Azure security questions.

Although Microsoft invests a lot of effort into ensuring SQL Database is a secure and reliable service, you still need to do a number of things to create a secure experience when using it. In the following sections, you will walk through step-by-step procedures that help to secure SQL Database. You will begin by securing access to and communication with SQL Database. Then you will walk through application-level security concerns such as SQL injection attacks and data encryption.

Securing SQL Database

SQL Database provides security features for authentication and authorization that are similar to those found in SQL Server. In addition to those features, SQL Database provides capabilities to secure access based on the client’s IP address and ensures that the communication channels are encrypted and secure. In the following step-by-step procedures, you will walk through securing your SQL Database service.

Creating a SQL Database

To demonstrate various security and backup practices, the procedures in this chapter work a WineCloudDb SQL Database similar to the one you’ve created in other chapters. If you don’t already have this database (or if you deleted it), you can create it now to follow along with this chapter. Otherwise, if you have a WineCloudDb database already on a SQL Database server from any other chapter in this book, you can skip ahead to the next procedure, “Configuring SQL Database Firewall.”

You will use SSMS to create the database. Then you will execute the script in Listing 5-1 to create some tables and populate them with some data.

LISTING 5-1 Script to create the WineCloudDb database

CREATE TABLE Wine(
WineId int IDENTITY PRIMARY KEY,
Name nvarchar(50) NOT NULL,
Category nvarchar(15) NOT NULL,
Year int);

CREATE TABLE Customer(
CustomerId int IDENTITY PRIMARY KEY,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
FavoriteWineId int,
CONSTRAINT FK_Customer_Wine FOREIGN KEY (FavoriteWineId) REFERENCES Wine(WineId));

SET IDENTITY_INSERT Wine ON;
INSERT Wine (WineId, Name, Category, Year) VALUES
(1, 'Chateau Penin', 'Bordeaux', 2008),
(2, 'McLaren Valley', 'Cabernet', 2005),
(3, 'Mendoza', 'Merlot', 2010),
(4, 'Valle Central', 'Merlot', 2009);
SET IDENTITY_INSERT Wine OFF;

SET IDENTITY_INSERT Customer ON;
INSERT Customer (CustomerId, FirstName, LastName, FavoriteWineId) VALUES
(1, 'Jeff', 'Hay', 4),
(2, 'Mark', 'Hanson', 3),
(3, 'Jeff', 'Phillips', 2);
SET IDENTITY_INSERT Customer OFF;

To create the WineCloudDb database, follow these steps:

1. Launch SSMS. An easy way to do this is to press the Windows key, type sql server management studio on the Start screen, and press Enter.

2. In the Connect To Server dialog, do the following:

a. For Server Name, type <servername>.database.windows.net. This is the fully qualified name to the SQL Database server, where <servername> should be replaced by the name assigned to your server.

b. For Authentication, select SQL Server Authentication from the drop-down list. (SQL Database does not support Windows Authentication.)

c. For Login and Password, type the user name (we’ve been using saz) and password you assigned the server when you created it.

d. Click the Connect button. The server now appears as a node in the Object Explorer.

3. Right-click the server node in the Object Explorer, and choose New Query to open a new query window connected to the master database.

4. In the new query window, type CREATE DATABASE WineCloudDb and press F5 to execute the script. This creates a new WineCloudDb database on the server.

5. Expand the Databases node in the Object Explorer. If the new WineCloudDb database is not visible, right-click the Databases node and choose Refresh.

6. Right-click the WineCloudDb database in the Object Explorer, and choose New Query to open a new query window connected to the WineCloudDb database.

7. Type the code shown in Listing 5-1 into the query window (or paste it in from the listing file downloaded from the book’s companion website).

8. Press F5 to execute the script.

9. Close the query window. (You don’t need to save the changes unless you want to.)

Configuring SQL Database Firewall

In a typical SQL Server deployment, SQL Server is set up inside the firewall, not in a demilitarized zone (DMZ), which is a subnetwork that exposes externally-facing services to a larger and untrusted network. In this manner, an on-premises SQL Server database is not accessible outside of the internal network. In the cloud, however, services are intended to be accessible and thus they cannot be limited to the internal network. These networking requirements make it more difficult to reduce the surface area for attack in the cloud than it is to do the same with SQL Server in your data center. Microsoft Azure closes this gap with a feature that is unique to SQL Database called SQL Database Firewall.

SQL Database Firewall restricts access to SQL Database based on the origin IP address of the connection. It is an opt-in model, which means that by default all connections to SQL Database are blocked unless a rule has been added that explicitly allows an IP address to connect. Rules can be defined for individual IP addresses as well as IP address ranges. SQL Database Firewall rules allow connections to be made to a SQL Database server, they do not authorize access to objects within the SQL Database instance. Once a connection has been permitted and established, SQL Database will authenticate the user and then authorize access to the requested database objects.

Tip

In addition to server-level firewall rules created using the Microsoft Azure management portal, you can configure database-level firewall rules using the sp_set_database_firewall_rule stored procedure found in each database. For more information on configuring database-level firewall rules, visit http://msdn.microsoft.com/en-us/library/jj553530.aspx.

SQL Database server-based firewall rules are defined for an entire SQL Database server, they are stored in the master database, and they allow clients to connect to any database within that server. These rules can be edited directly in the master database, and they can also be managed using the Microsoft Azure management portal. SQL Database firewall rules have a name, a starting IP address, and an ending IP address. This arrangement allows you to create a single rule for multiple consecutive IP addresses, for example, and an entire IP subnet. You can also create a rule for a single IP address by making the starting IP address and ending IP address the same.

Creating custom firewall rules

SQL Database Firewall will prevent all connections to SQL Database unless explicitly allowed via a firewall rule. This includes your attempts to connect to SQL Database even using the SQL Database management portal. As a result, the IP address you are trying to connect from needs to be included in the firewall rules.

Follow these steps to add your IP address to the server-based firewall rules:

1. Log in to the Microsoft Azure management portal at https://manage.windowsazure.com. This brings you to the main portal page showing ALL ITEMS.

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

3. Click the SERVERS link at the top of the page. This displays a list of your Microsoft Azure SQL Database servers, as shown in Figure 5-1.

FIGURE 5-1 List of SQL Database servers in the Microsoft Azure management portal

4. In the NAME column, click the server that contains the WineCloudDb database. This opens a page with links for the selected server.

5. Click the CONFIGURE link at the top of the page. This displays the SQL Database server firewall configuration, as shown in Figure 5-2.

FIGURE 5-2 SQL Database server firewall configuration

6. If you followed similar procedures in Chapter 1, “Getting started with Microsoft Azure SQL Database,” and Chapter 2, “Configuration and pricing,” you should already see one rule named ClientIPAddress_yyyy-mm-dd_hh:mm:ss in place for your current IP address, or at least, for the IP address you were using at the time if you are now in a different location. In this case, click the X to the right of the rule (the X appears only when you hover the mouse over the rule) to remove it now.

7. In the Allowed IP Addresses section, your current IP address should appear to the right of CURRENT CLIENT IP ADDRESS. Click the ADD TO THE ALLOWED IP ADDRESSES link to the right of your IP address to add it now.

With this change, you will be able to connect to the SQL Database server from your current IP address, wherever you happen to be. Of course, if you are connecting from the same IP address you used in Chapters 1 and 2, the new rule you just added is the same rule as the one you just removed in the previous step. In this case, the purpose of this exercise was solely to demonstrate how to delete a rule.

New firewall rules don’t take effect until you click the SAVE button at the bottom of the page. Before you do that, add another rule so that you can also connect from your home office. To create the home office rule, follow these steps:

1. In the RULE NAME text box beneath the list of existing rules, type Home Office. Note that the rule name cannot contain either forward slash (/) or backslash (\) characters, nor can it end with a period (.) character.

2. In the START IP ADDRESS and END IP ADDRESS boxes, type the IP address range of your home office. (Type the same IP address in both text boxes to specify a single IP address rather than a range.) The page should appear similar to Figure 5-3.

FIGURE 5-3 New firewall rules for the current location and the Home Office

3. Click the SAVE button at the bottom of the page.

You have now updated the firewall rules. Prior to doing so, the home office IP address or addresses you specified would be blocked from connecting to your SQL Database server using any mechanism, including SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), your own custom applications using ADO.NET or Entity Framework, and even the SQL Database management portal itself.

Allowing Microsoft Azure services to connect

If you want, Microsoft Azure services can also be blocked from connecting to your SQL Database server. Finding out the IP addresses of the Microsoft Azure originating requests can be difficult and complex to manage, so SQL Database provides a feature that enables you to allow or block requests from other Azure services without having to know the specific IP addresses for the request. If you allow Azure services to connect to your SQL Database server using this feature, all Azure services are allowed to connect through the SQL Database server firewall. They will still need to authenticate and be authorized in your SQL Database server to do anything with your databases, but they won’t be blocked from connecting by the firewall.

When you create a server, you’ll see a check box you can select to enable Microsoft Azure services to access your SQL Database server. (You used this check box in Chapter 1; see Figure 1-5.) This check box is selected by default, and if you left it selected when you created your SQL Database server (and as we instructed in Chapter 1), Azure services are allowed to connect to your SQL Database server. Once the server has been created, you can easily toggle the setting to allow or block Azure services through the firewall by clicking the YES and NO options for WINDOWS AZURE SERVICES beneath Allowed Services at the bottom of the firewall rules page. (See the bottom of Figure 5-3.)

When you choose to allow Microsoft Azure services, a firewall rule is added to your SQL Database server with an IP range of 0.0.0.0 to 0.0.0.0. This is a special range that allows all Microsoft Azure services to connect to your SQL Database server, and it does not appear with your other rules on the firewall rules page. (You can determine whether or not the 0.0.0.0 rule is in place based on whether YES or NO is selected for WINDOWS AZURE SERVICES at the bottom of the page.)

In addition to managing server-based firewall rules in the Microsoft Azure management portal as you’ve just done, the SQL Database Management REST API can also be used in scenarios where you want to manage the SQL Database firewall from an application or script. (The REST API is explained in Chapter 8, “Designing and tuning for scalability and high performance.”)

Authenticating and authorizing users

Once a client connection is permitted through the SQL Database Firewall, SQL Database then needs to authenticate credentials and log in users. SQL Database manages and authenticates users using SQL Server Authentication, just like on-premises SQL Server. Unlike SQL Server, however, Windows Authentication is not supported. (More information about differences between Microsoft Azure SQL Database and SQL Server can be found in Chapter 3, “Configuration and pricing.”)

When Microsoft Azure creates a SQL Database server, it also creates a master database on the server. One of the responsibilities of the master database is managing SQL Database server logins. The initial login is created from the user name and password you provide when creating a SQL Database server. (We’ve been using saz in our examples.) That user name and password gets used as the server-level principal for your SQL Database server, similar to the sa user in SQL Server. You can use that login to connect to your SQL Database server from your applications, but doing so is not a good practice because this login has full permissions in the database and does not allow you to provide fine-grained access. To restrict access and achieve more fine-grained access control, you need to create new SQL Database logins and users.

Creating SQL Database logins

To create a new login and user in SQL Server, you could use the graphical interface in SQL Server Management Studio. Unfortunately, those dialogs and forms are not available in SSMS when you are connected to a SQL Database server. As a result, you will need to execute direct T-SQL commands inside a query window instead.

Follow these steps to create a new SQL Database server login:

1. Launch SSMS. An easy way to do this is to press the Windows key, type sql server management studio on the Start screen, and press Enter.

2. In the Connect To Server dialog, enter the appropriate credentials, as shown in Figure 5-4. Be sure to choose the server that has the WineCloudDb database on it. Also, remember that the server name must be suffixed with .database.windows.net and you must provide the login you specified when you created the server. (We’ve been using saz in our examples.)

FIGURE 5-4 The Connect To Server dialog in SQL Server Management Studio

3. Click the Connect button.

4. Once connected, your SQL Database server will be listed in the Object Explorer pane. Right-click on the server name, and choose New Query as shown in Figure 5-5. This opens a new query window connected to the master database.

FIGURE 5-5 Opening a new query window for the master database in SQL Server Management Studio

5. Type the following T-SQL statement: CREATE LOGIN WineCloudDbLogin WITH PASSWORD='<Password>'

Note

Replace <Password> with a strong password that you select for your new login. The password must satisfy the requirements of the password policy. For more information about the strong password policy, see http://msdn.microsoft.com/en-us/library/ms161962.aspx.

6. Press F5 (or click the Execute button in the toolbar) to run the script.

You have now created a new SQL Database server login; however, this login isn’t authorized to do anything with the SQL Database server. You now need to create a user for this login and then either grant the user server-level or database-level permissions.

Granting server-level permissions

SQL Database has two server-level security roles, loginmanager and dbmanager, as shown in Table 5-1. These roles make it possible for users other than the server-level principal to manage security and databases. A user must be created for a login in the master database to assign server-level roles.

TABLE 5-1 SQL Database server-level roles

As when creating and managing logins, you will need to manage users and role assignments using T-SQL scripts, because the SQL Server Management Studio user interfaces are not available when you are connected to a SQL Database server. Working in the same query window that’s connected to your SQL Database server from the previous procedure, follow these steps to create a new user with server-level permissions:

1. Delete the T-SQL code in the query window left over from the previous procedure.

2. Type the following T-SQL statements into the code window: CREATE USER WineCloudDbUser FROM LOGIN WineCloudDbLogin GO EXEC sp_addrolemember 'loginmanager', 'WineCloudDbUser' EXEC sp_addrolemember 'dbmanager', 'WineCloudDbUser'

3. Press F5 (or click the Execute button in the toolbar) to run the script. This code creates a new user named WineCloudDbUser that is associated with the existing login named WineCloudDbLogin that you created in the previous procedure, and then adds the new user to theloginmanager and dbmanager roles.

You have now created a new user in the master database named WineCloudDbUser and granted this user permissions to manage logins and databases within the scope of the SQL Database server. This will allow you to delegate permissions to others to manage the SQL Database server without needing to distribute the server-level principal credentials.

Granting database-level permissions

One of the goals when working on security is to minimize the surface area and reduce exposure to attack. One of the principles that gets applied to this problem is the principle of least privilege, which basically means, give users just enough access to perform their required job functions, and not any more or less. This minimizes users’ access, which in turn minimizes the overall surface area for attack.

When using SQL Database, you don’t want to grant all users server-level permissions; instead, you want to give users just enough permissions to do what they need. For example, if you have an application that only needs to read data from a database, you don’t want to give the user the application is connecting as permissions to write data. In this case, you can create a new user who has read-only database permissions. This involves creating a new login in the master database and then creating a new user associated with that login in the WineCloudDb database. Create the new read-only user now by following these steps:

1. Delete the T-SQL code in the query window left over from the previous procedure. (Remember that this query window is still connected to the master database.)

2. Type the following T-SQL statement into the code window: CREATE LOGIN WineCloudDbReadonlyLogin WITH PASSWORD='<Password>'

Note

Once again, replace <Password> with a strong password for the new login.

3. Press F5 (or click the Execute button in the toolbar) to run the script. This code creates a new login named WineCloudDbReadonlyLogin in the master database.

4. In Object Explorer, expand the Databases node to reveal the list of databases on the server (which should include the WineCloudDb database).

5. Right-click the WineCloudDb database, and choose New Query, as shown in Figure 5-6. This opens a new query window connected to the WineCloudDb database.

FIGURE 5-6 Opening a new query window for the WineCloudDb database

6. Type the following T-SQL statements into the code window connected to WineCloudDb: CREATE USER WineCloudDbReadonlyUser FROM LOGIN WineCloudDbReadonlyLogin GO
EXEC sp_addrolemember 'db_datareader', 'WineCloudDbReadonlyUser'

You have now created a new login in the master database of your SQL Database server. You created a user for that login in the WineCloudDb database and granted that user permissions to read data. Granting database-level permissions enables you to grant least-privilege permissions to users and minimize the attack surface of your SQL Database server.

Connecting to a SQL Database server with database-level permissions

The previous procedure walked through creating a SQL Database login named WineCloudDbReadonlyLogin and an associated user named WineCloudDbReadonlyUser that is granted read-only permission for the WineCloudDb database. This login could be used by a custom application connecting to the database, or you might want to grant a developer read-only access to a database in order to diagnose a production issue. In the latter scenario, it is likely that the developer would want to use SSMS. However, when that developer attempts to connect with SSMS using theWineCloudDbReadonlyLogin credentials, SSMS will display an error message as shown in Figure 5-7.

FIGURE 5-7 The SSMS Connect To Server error message when the login doesn’t have access to master

This error occurs because, by default, SSMS attempts to access the master database when it connects, but the login provided has no permissions on the master database—it just has read-only permissions on WineCloudDb. The resolution is to use the advanced version of the Connect To Server dialog, which allows you to specify a particular database that you want to access other than master, which in our case, is WineCloudDb. To use the advanced Connect To Server dialog in SSMS, follow these steps:

1. If you’ve closed SSMS since the previous procedure, start it up again to display the Connect To Server dialog. If SSMS is still open, click Connect | Database Engine in the Object Explorer toolbar menu as shown in Figure 5-8 to display the Connect To Server dialog.

FIGURE 5-8 Connecting to Database Engine in the SSMS Object Explorer

2. For Server name, type the name of your SQL Database server that contains the WineCloudDb database. (Remember to add the suffix .database.windows.net.)

3. For Authentication, choose SQL Server Authentication.

4. For Login, type WineCloudDbReadonlyLogin.

5. For Password, type the password you assigned to WineCloudDbReadonlyLogin in the previous procedure.

6. Click the Options button at the bottom right of the dialog. This changes the Connect To Server dialog to its advanced version, which includes a Connect To Database text box with which you can specify a database for the connection other than master.

7. In the Connect To Database text box, type WineCloudDb as shown in Figure 5-9.

FIGURE 5-9 The advanced Connect To Server dialog lets you specify a particular database for the connection

8. Click the Connect button. Now, instead of displaying the error message shown in Figure 5-8, SSMS connects successfully.

You have now connected to the WineCloudDb SQL Database using SSMS with a limited access account that has only db_datareader permissions. As explained, this is useful when you need to give a team member read-only access to a production database to diagnose issues or conduct some analysis on the data.

Microsoft Azure SQL Database provides security capabilities that include firewall and server-level roles. You can also secure SQL objects with database-level roles and permissions in just the same way as you can with SQL Server. This enables you to follow the principle of least privilege and grant users only the permissions they need, no more and no less. Doing so reduces the attack surface and helps maintain security. It is a good practice to follow this principle when developing applications that connect to your SQL Database, and when providing other team members credentials for SQL Database to use for development, troubleshooting, and analysis.

Backing up SQL Database

The inconvenient truth is that disasters do occur, and they come in various shapes and sizes. A disaster could result from a malicious attack, a service outage, or something as simple as bad code that deleted or corrupted data. When disaster strikes, you need to be able to recover from it and continue on with your business. Having a good business continuity and disaster recovery plan is the key to successfully working through a catastrophe. One essential component of your disaster recovery plan is backups. Beyond recovering from disasters, backups are commonly used to set up new environments and to troubleshoot issues that might be occurring only in a specific environment.

When developers are initially introduced to Microsoft Azure SQL Database, they often mistakenly think that backups are not needed because SQL Database provides high-availability features by default. The need for backups is similar but different than the need for high availability. High-availability capabilities help ensure that your database is accessible when small-scale infrastructure downtime occurs—for example, when there is excessive load on a server or when a server needs to be rebooted during routine maintenance. High availability does not help when something unexpected deletes or corrupts the database, because those changes will get replicated across all high-availability nodes. So you need a backup-and-restore strategy even with the built-in, high-availability features of SQL Database.

The backup-and-restore process in Microsoft Azure SQL Database is different than what you might be used to in SQL Server, because the traditional T-SQL statements BACKUP and RESTORE are not supported. Instead of traditional backups in SQL Server, BACPAC files are used to back up and restore with SQL Database.

Copying a database

Transactional consistency is important to maintain when backing up a transactional system like SQL Database or SQL Server. BACPAC files do not provide transactional consistency, because a BACPAC is by copying tables individually, and modifications could occur between the time that the first table and last table are copied. So the first thing you need to do when backing up a SQL Database server is create a copy of the database that isn’t being modified using the Database Copy feature.

The Database Copy feature creates a new database from an existing SQL Database that is transactionally consistent when the copy finishes. It does this by replicating any changes that are made to the source database while the database is copying at the end of the process. Database copies can be created either on the same SQL Database server or on a different server within the same region.

Follow these steps to create a copy of the WineCloudDb database:

1. In SSMS, connect to your SQL Database server that contains the WineCloudDb database using the login you used to create the server. (We’ve been using saz in our examples.) Once connected, the SQL Database server will be listed in the Object Explorer pane on the left.

2. Right-click on the server name, and choose New Query as shown in Figure 5-5. This opens a new query window connected to the master database.

3. Type the following T-SQL statement into the code window: CREATE DATABASE WineCloudDbCopy AS COPY OF WineCloudDb

4. Press F5 (or click the Execute button in the toolbar) to run the script. This starts the process of copying the WineCloudDb database to a new database named WineCloudDbCopy.

You have now started copying your WineCloudDb database to a new database named WineCloudDbCopy. In this procedure, you copied a source database to a destination database on the same SQL Database server, but you could also copy your database to another SQL Database server, as long as the destination database server is within the same Microsoft Azure region as the source database server. You can do this by executing the CREATE DATABASE statement in a query window connected to the master database on the destination server. Then just prefix the source database name in the CREATE DATABASE...AS COPY OF statement with the name of the source server—like so, for example:

CREATE DATABASE WineCloudDbCopy AS COPY OF muea4g022x.WineCloudDb

Monitoring the progress of a database copy operation

When you execute the CREATE DATABASE...AS COPY OF statement to copy a database, it returns with success or failure quickly. However, copying a database is an asynchronous process and the result of the CREATE DATABASE...AS COPY OF statement is not actually the result of copying the database—it is just the result of beginning the asynchronous copy operation. You can determine the status of the database copy operation running in the background by querying the sys.databases and sys.dm_database_copies views.

While the database is copying, the state_desc column of the sys.databases view will return COPYING. If the copy process fails, the state_desc column returns SUSPECT. And if the copy completes successfully, the state_desc column returns ONLINE.

The new destination database gets created early in the database copy process. If a failure occurs at any time during the database copy, the database will be left in an incomplete state and you will need to delete the new database using the DROP DATABASE statement. You can also cancel the database copy operation while it is running by executing the DROP DATABASE statement on the new destination database.

Follow these steps to monitor the copy database progress:

1. In SSMS, connect to your SQL Database server that contains the WineCloudDbCopy database you created in the previous procedure, using the login you used to create the server. (We’ve been using saz in our examples.) Once connected, the SQL Database server will be listed in the Object Explorer pane on the left.

2. Right-click on the server name, and choose New Query as shown earlier in Figure 5-5. This opens a new query window connected to the master database.

3. Type the following T-SQL statement into the code window: SELECT name, state_desc FROM sys.databases WHERE name = 'WineCloudDbCopy'

4. Press F5 (or click the Execute button in the toolbar) to run the script. This returns the state of the new destination database from the sys.databases view, as shown in Figure 5-10. Of course, if you allowed enough time for the copy operation to complete since starting it in the previous procedure, the state_desc column will report ONLINE, not COPYING.

FIGURE 5-10 The results from the sys.databases query during a database copy operation

You can obtain additional details about the copy operation (start date, completion percentage, error details, and more) by joining the sys.databases view with the sys.dm_database_copies view on the database_id column as follows:

SELECT *
FROM sys.dm_database_copies AS c INNER JOIN sys.databases AS d ON c.database_id = d.database_id
WHERE d.name = 'WineCloudDbCopy'

Note

The sys.dm_database_copies view will return a result only while the copy is in progress. Once the copy has completed, this view returns no results.

The database copy operation can also be monitored in the Microsoft Azure management portal, as shown in Figure 5-11.

FIGURE 5-11 The Microsoft Azure management portal database list during a database copy operation

Once the operation completes, you have created a transactionally consistent copy of your database, and you are now ready to export it as a BACPAC.

Exporting a BACPAC

As you learned in Chapter 4, “Creating a Microsoft Azure Storage account,” SQL Database provides BACPAC import and export capabilities that enable you to easily migrate databases between SQL Database and SQL Server. These import and export capabilities also provide a simple and reliable way to back up and restore databases in SQL Database (provided that you first create a transactionally consistent copy).

The storage service you use in Microsoft Azure to store BACPAC files is Microsoft Azure Blob Storage. Blob Storage is a service designed for storing binary files that are very large in size. The service supports two types of blobs: block blobs and page blobs, either of which can be used for storing BACPAC files. At the time of this writing, the maximum size of a block blob is 200 gigabytes (GB) and the maximum size of a page blob is one terabyte (TB).

Blob Storage is an ideal service for storing BACPAC files. To use the SQL Database export feature, you need a Microsoft Azure Storage account. If you haven’t already set up a Microsoft Azure Storage Account named mywinestorage, follow the steps found in Chapter 4 in the section “Creating a Microsoft Azure Storage account.”

Follow these steps to export a BACPAC to the mywinestorage storage account:

1. Log in to the Microsoft Azure portal at https://manage.windowsazure.com. This brings you to the main portal page showing ALL ITEMS.

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

3. Click on the WineCloudDbCopy database (the transactionally consistent copy of the WineCloudDb database you just created).

4. Click the EXPORT button at the bottom of the page to display the Export Database.

5. For FILENAME, you can leave the default name assigned by the portal (WineCloudDbCopy suffixed with the current date and time), or enter a name of your own choosing for the BACPAC file.

6. For BLOB STORAGE ACCOUNT, choose your blob storage account from the drop-down list (or choose Create A New Storage Account from the drop-down list to create a new storage account on the fly). Once you choose a storage account, the CONTAINER drop-down list appears.

Tip

Best practice is to create the storage account in the same Microsoft Azure region as the SQL Database to avoid data-transfer costs between regions. See Chapter 2 for more information on SQL Database pricing and recommendations.

7. For CONTAINER, you can choose an existing container from the drop-down list (such as the dbimport container you created in Chapter 4), or you can create a new one. For this exercise, choose Create A New Container from the drop-down list.

8. For NEW CONTAINER NAME, type dbbackups.

9. For SERVER LOGIN NAME, the text box is automatically populated with your server-principal user. (We’ve been using saz in our examples.)

10. For SERVER LOGIN PASSWORD, enter the password for your login. The EXPORT DATABASE dialog should appear similar to Figure 5-12.

FIGURE 5-12 The Export Database Settings dialog in the Microsoft Azure management portal

11. Click the checkmark icon in the lower-right side of the dialog to begin the export. Once the export has completed, a notification that the export was successful appears at the bottom of the page.

You have now exported your WineCloudDbCopy database to a BACPAC file in Blob Storage. The BACPAC file provides a portable backup of your SQL Database that you can archive or restore to another SQL Database on Microsoft Azure, or to a local SQL Server database in your own data center. You can download your exported BACPAC file from the Microsoft Azure management portal or a third-party storage client by browsing to the file in your storage account and container.

Importing a BACPAC

Creating a backup is not very valuable unless you can also restore from it. SQL Database makes restoring a BACPAC file simple using the Import Database feature.

Follow these steps to import your BACPAC:

1. If you’ve closed the Microsoft Azure Management Portal since the last procedure, log in to the Microsoft Azure management portal at https://manage.windowsazure.com. This brings you to the main portal page showing ALL ITEMS.

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

3. Click the NEW button at the bottom of the page.

4. Click IMPORT, as shown in Figure 5-13. This displays the IMPORT DATABASE dialog.

FIGURE 5-13 Restoring a database by using the Import option in the management portal

5. Click the folder icon to the left of the BACPAC URL text box. This opens the BROWSE CLOUD STORAGE dialog.

6. An explorer tree that displays your Microsoft Azure Storage accounts and their containers appears on the left side of the dialog. Expand the mywinestorage account you used in the previous Export procedure to display the containers inside of it.

7. Click the dbbackups container you used in the previous Export procedure to display its contents on the right. You can see the BACPAC file that got created by the Export operation, as shown in Figure 5-14.

FIGURE 5-14 The contents of the dbbackups container displayed in the BROWSE CLOUD STORAGE dialog

8. Click the exported BACPAC file to select it.

9. Click the Open button. This returns you to the IMPORT DATABASE dialog with the URL of the .bacpac file populated in the BACPAC URL text box.

10. For NAME, change the database name to WineCloudDbRestored.

11. For SERVER, choose any available server from the drop-down list to host the database. (Always keeping in mind that this should be a server in the same region as the storage account to avoid bandwidth costs.) For this exercise, you can just restore the BACPAC file to the same server you exported the WineCloudDbCopy database from, but you can also choose another existing server from the drop-down list. (You can also choose New SQL Database Server from the drop-down list to create a new server on the fly.) Once you choose a server, the SERVER LOGIN NAME and SERVER LOGIN PASSWORD text boxes appear, and the SERVER LOGIN NAME text box is automatically populated with your administrator login name.

12. For SERVER LOGIN PASSWORD, type the password for your login. The IMPORT DATABASE dialog should appear similar to Figure 5-15.

FIGURE 5-15 Importing a BACPAC file from Microsoft Azure Storage into a new SQL Database server.

Note

By default, a database imported from a .bacpac file will be a 1-GB, Web edition database. If you need a larger database, select the CONFIGURE ADVANCED DATABASE SETTINGS check box at the bottom of the IMPORT DATABASE dialog. This converts the simple IMPORT DATABASE dialog into a two-page wizard, where the second page provides you with options to set the SQL Database edition and size. For more information, see the section “Configuring database edition and size” in Chapter 2.

13. Click the checkmark icon in the lower-right side of the dialog to begin the import. Once the import has completed, a notification that the import was successful appears at the bottom of the page, and the new WineCloudDbRestored database appears in the list of SQL Database instances.

You have now created a database by importing a previously exported BACPAC file. Note that you can also restore your BACPAC to any SQL Database server, including SQL Database servers in other subscriptions, by uploading it to a storage account in the target subscription.

Scheduling BACPAC exports

It is nice to be able to manually export and import BACPAC files. This allows you to easily take a snapshot of a database before making major modifications or capture a database at a point in time to restore into another environment. Manually executing exports is a very efficient and reliable backup strategy. But you will also typically need backups to occur on a regular and consistent schedule. You can do this effectively by scheduling automated backups using the SQL Database Automated Export feature. Automated Export is currently in Preview and is not considered Generally Available (GA), which means it doesn’t get the same guarantees and support as features that are GA. It is also free, and that could also change as it leaves Preview and becomes GA.

As we explained, BACPAC files that you create manually do not provide transactional consistency unless you first use the database copy feature to create a transactionally consistent database copy, and then create the BACPAC from the copy. Fortunately, when you schedule a BACPAC export schedule to back up a database automatically, the generated BACPAC is transactionally consistent. Thus, automated BACPAC exports provide an effective and reliable backup strategy for SQL Database.

To set up an automated BACPAC export schedule, follow these steps:

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

3. Click WineCloudDb in the NAME column of the database list.

4. Click the CONFIGURE link at the top of the page.

5. For EXPORT STATUS under Automated Export, click the AUTOMATIC button to display the automated export configuration.

6. For STORAGE ACCOUNT, choose mywinestorage.

7. For FREQUENCY, configure how often you want the database exported by the entering the export interval in days (with the default being every 7 days) and the start date of the export in UTC time (with the default being today at midnight).

8. For RETENTION, enter the number of days to keep the exports. (The default is 30 days.) Leave the Always Keep At Least One Export File check box selected to guarantee that at least one export will always be retained ever after the retention period expires for all exports.

9. For SERVER LOGIN NAME, type the login you used to create the server. (We’ve been using saz in our examples.)

10. For SERVER LOGIN PASSWORD, type the password for your login, and then wait a brief moment while the password is verified. The CONFIGURE database screen should appear similar to Figure 5-16.

FIGURE 5-16 The automated export configuration of a SQL Database

11. Click the SAVE button at the bottom of the page.

You have now successfully configured an automated backup schedule for the WineCloudDbCopy database.

Summary

Security, availability, and disaster recovery are top concerns when you are considering using a public cloud. Microsoft addressed concerns about these issues by providing a secure, reliable, and highly available public cloud platform with Microsoft Azure. It is very important for data services in the public cloud to be secure and have good solutions for disaster recovery.

SQL Database provides good security and disaster-recovery features. In this chapter, you learned how to add security at multiple levels, including IP security policies using SQL Database Firewall and authentication and authorization using SQL Database logins, user, and roles. You also learned about the backup and restore capabilities provided by SQL Database, which helps you simplify disaster-recovery planning by creating transactionally consistent database copies, exporting and importing BACPAC files, and automating transactionally consistent BACPAC export schedules. Using these capabilities of SQL Database, you can create a secure, reliable, and highly available solution using Microsoft Azure and SQL Database.