C H A P T E R  15

Cloud Computing for the DBA

Imagine for a moment that there was no electric company. The utility wires carrying electrical current from the street to your business would be nonexistent. Since we need electricity to power our critical devices and appliances, like computers and coffee makers, we would need to build a power source. To do this, first we need to decide on renewable sources like solar and wind power or nonrenewable sources like coal or gas. After obtaining all the necessary building permits and capital, we are ready to start construction of the power generator.

Let’s assume that we have created enough power capacity to handle our load. Life is grand, and our online widget sales business is taking off. To handle this success, we acquire more servers, which in turn, require more power from our power generators. Now, we must build out additional power sources to accommodate this new demand. As you can see, power supply and consumption are huge burdens for our business. Thankfully, in reality, we have electric companies that simply provide us more power when we need it. On the flip side, if we do not need to power those new servers anymore, the power company does not give us that electricity. In the end, the power company simply bills us for the power consumption we actually use.

This is the main idea behind cloud computing, where the cloud vendor is providing us with computing and storage power in the form of servers running in their datacenters. If we need more computing power, we can easily ask for it, and likewise, if we no longer need the computing power, we can decommission it on the fly. This service-based approach allows us to focus more on our business problems than on the technical issues that result from our application of technology to solve those problems.

There is much talk today about the true definition of “cloud computing.” As with any new technological concept, a myriad of definitions are available for you to read. Different people may have different opinions on what cloud computing is. For this book, we will define cloud computing as it’s defined by the National Institute on Standards and Technology (NIST). The NIST defines cloud computing as “. . . a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, server, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction . . . .” The complete definition can be found at the following web address: http://csrc.nist.gov/publications/nistpubs/800-145/SP800-145.pdf. According to this document, some essential characteristics, like on-demand, self-service, and rapid elasticity, are needed to support the cloud model. The definition also defines three service models for cloud delivery. These three service models form the basis of what you, as the cloud computing consumer, can purchase in today’s marketplace.

Cloud Service Models

BuyCarsFromMePlease is a new fictitious startup company that sells used cars on the Internet. You have been hired by the founders to manage all things technical. The first tasks are to ensure that you have a web site up and host a simple catalog of used cars from the neighborhood dealers and private owners. As a technological guru, you hired the phone company to provide you the Internet access and purchased and provisioned the web servers, database servers, and network. You also configured security and published the online catalog web application to the web servers. In a nutshell, you have implemented a complete on-premise solution.

Within a short period of time, the management team has hired a few contractors to come in and make modifications to the web application. Since there is no more money to purchase hardware, you look for alternative ways to provide these contractors with development and test environments.

Infrastructure as a Service

Some cloud vendors like Amazon.com and RackSpace offer the ability to host a variety of operating systems for you to use in their datacenters. The vendor provides you remote access to these images and then it’s up to you to patch the operating system and install and maintain your application. In this case, the vendor is providing the network, physical server, and storage for you, and you worry about the rest. Under the covers, your operating system is hosted in a virtual machine environment, which allows easy provisioning and decommissioning of operating system images. You pay for only what you use.

Infrastructure as a service (IaaS) is one solution that would provide the contractors with a cheap sandbox to play in.

Software as a Service

The management team at BuyCarsFromMePlease has now asked you to provide e-mail, calendaring, and messaging for all the employees of the company. Before you go out and purchase an additional server to host Microsoft Exchange, consider what options are available in the cloud. With software as a service (SaaS) cloud service model, the provider not only takes care of the infrastructure, but also manages the servers, databases, security, and the actual application. You (as the consumer) use the application. Hotmail and Gmail are good real-world examples of SaaS implementations today. Microsoft has all of its office applications bundled up into an offering called Office 365 (http://office365.microsoft.com). Office 365 is a subscription-based service that combines Microsoft Office web applications (i.e., Excel, PowerPoint, Word, and OneNote) with online tools like SharePoint, Exchange, and Lync to provide you with a great experience in collaboration and productivity. Office 365 is a pure SaaS offering. Other than Internet connectivity, there is no additional infrastructure for you to worry about supporting.

Platform as a Service

Somewhere in the middle of IaaS and SaaS lies a cloud service offering known as platform as a service (PaaS). To understand PaaS is to comprehend the core user scenarios PaaS provides a solution for. Consider the scenario where the management team of BuyCarsFromMePlease has decided to run a television advertisement during the Super Bowl. The Super Bowl event in the United States is watched by tens of millions people worldwide. The management team is expected at least 1% (about 1 million) of the expected 100 million viewers to visit the company’s website during the Super Bowl. To handle this workload on the premises, we would have to purchase additional hardware, rack the units, ensure adequate power and cooling, set up networks, manage security, and install operating systems, software patches, and applications. After the Super Bowl ended and the demand subsided, we would still have all this excess capacity in our datacenters.

A better solution would be to take our application, web page files, databases, and support DLLs and publish them to a server in the cloud. When the application is published, we would simply tell the host to spin up 200 more instances of the web host to accommodate the new demand from the Super Bowl ad. When the Super Bowl ends and the demand returns to normal, you can change the instances down to just a few. This is elastic computing, and this is the value of PaaS.

With PaaS, the cloud service vendor manages the network, storage, and virtualization as with IaaS, but it also manages the operating systems, patching, database servers, and security. As consumers of PaaS, we focus on solving our own business problems. In the case of BuyCarsFromMePlease, we build the web page and deploy it to the cloud. Microsoft’s PaaS offering is known as Windows Azure.

Figure 15-1 shows an overview of the three cloud servicing models discussed in this chapter. Notice how the vendor manages more or less as you move throughout the service models.

Figure 15-1. Cloud service models

Windows Azure

Windows Azure is Microsoft’s platform for running applications in the cloud. The Windows Azure platform, shown in Figure 15-2, consists of five main components: Compute, Storage, Networking, Identity, and Marketplace.

Figure 15-2. Windows Azure platform

It is important to note that any of the services in Figure 15-2 can be leveraged independently. For example, if your application just needs to store a large amount of information, you can just use the blob store within Windows Azure Storage service.

Roles

The Windows Azure Compute service contains three types of roles:

• A Web role can be thought of as an instance of Windows Server running Internet Information Server. A deployed web application to this role can immediately take advantage of the load balancing and scalability that is inherent in the Windows Azure architecture.
• A Worker role is similar to an instance of Windows Server without Internet Information Server configured. Leverage this role where you want to run code just like the code used within a Windows Service.
• The Virtual Machine (VM) role is a recent addition to the Windows Azure platform. This role allows you to upload your own Windows Server 2008 R2 Standard or Enterprise edition image and have it run inside the Windows Azure platform.

The VM role sounds a lot like IaaS. However, it’s not a full IaaS solution. Even though you have full control over the operating system and can make a remote desktop connection to the hyper-v image, certain restrictions, such as lack of persisted state, may make your implementations different than those servers running on premises.

An overview of the Windows Azure VM role can be found at the following web site: http://msdn.microsoft.com/en-us/library/gg433107.aspx. The VM role is not the end answer for a true IaaS offering. Features within this space are constantly being added, and by the time you read this, Microsoft may have additional IaaS options for running your virtual machine in its datacenter.

Storage

Windows Azure Storage provides a storage options including a message queue, blob storage, and table storage. An in-depth discussion on data storage options can be found with the TechNet Wiki article, “Data Storage Offerings on the Windows Azure Platform” located at the following web site: http://social.technet.microsoft.com/wiki/contents/articles/data-storage-offerings-on-the-windows-azure-platform.aspx. As a DBA, you may hear about table storage in Azure.

Table storage in Windows Azure is simply a collection of row-like entities, which can each contain up to 255 properties. With table storage, there is no schema, no way to represent relationships, no way to perform join operations, no stored procedures, no triggers, nor any relational database management–type functions.

What Azure Table storage can do is scale, and it does this extremely well. Unlike SQL Azure, a Windows Azure Table can be up to 100TB in size. These tables can be propagated via Windows Azure content delivery network (CDN) halfway around the world, such that your Asian customers see the same query performance as your European customers. The CDN also enables developers to deliver high-bandwidth content including a smooth streaming of media. More information on the CDN can be found at the following web site: www.microsoft.com/windowsazure/cdn.

AppFabric

One of the differentiators of Windows Azure as compared with other PaaS providers is Windows Azure AppFabric (AppFabric). The AppFabric contains functionality related to networking and identity management. One component of the AppFabric is the service bus. This feature enables Azure applications to interact with applications both on and off premises. It does this without complex firewall configurations. The service bus can also be used to facilitate communication between two separate private networks.

The AppFabric Access Control feature provides identity management and access control to web applications and services. It integrates with enterprise directories, such as Active Directory, and web identities, like Windows Live ID, Google, Yahoo!, and Facebook. Using Access Control the integration is seamless and enables your applications to exercise complete control over the level of access that each user or group has defined.

Case Study: OCCMundial.com

Headquartered in Mexico City, OCCMundial.com connects jobs seekers with job opportunities and eases the hiring process for businesses. One of the competitive advantages of OCCMundial is a recommendation system called OCCMatch. This system matches job openings to candidate resumes. Before adopting the Windows Azure platform, the existing ASP.NET application was hosted on a few dozen Windows Server 2008 machines with a SQL Server 2005 database server. The existing on-premise solution could scale to compute 25,000 resumes against 20,000 job offers. While this was good for the inception of the technology, OCCMundial wanted to scale the application to connect 1.5 million resumes with 80,000 job listings. To do this, given the current configuration, they would have to spend almost half a million dollars on hardware upgrades alone. Leveraging Windows Azure, OCCMundial.com spins up to 200 compute nodes, allowing it to execute millions of OCCMatch operations in parallel. Its new Windows Azure design allows the company to process 1.5 million resumes two to three times per week.

In this example, OCCMundial had an existing .NET web application leveraging SQL Server. Porting this to Windows Azure was straightforward, because Visual Studio supports both on-premise and cloud based applications.

Case Study: 3M

3M is a science-based company and has developed thousands of innovative products including Post-It Notes and Thinsulate insulation. One area of science that 3M works in is visual attention models. Consider the case where you are designing a new company logo, or web site, or hotel lobby. The question may be asked of how people would respond to these designs. To what areas of the new logo are people first drawn, and which areas are not noticed? These questions are really important to designers. 3M has developed a web-based application called the 3M Visual Attention Service (VAS). The VAS is a service where designers can upload a picture or graphical design and get, in return, a heat map of which areas of the image were most likely to attract a viewer’s attention. A sample of the result is shown in Figure 15-3.

Figure 15-3. Visual hot spots using 3M’s Visual Attention Service

For designers to reliably leverage this application, 3M needed to build out a scalable infrastructure. Additional computing resources would also be needed at peak times before the holiday season. Rather than build out the datacenter to meet these requirements, 3M leveraged Windows Azure and SQL Azure for the solution.

The VAS application was written with a Silverlight browser plug-in which allows the end-user to edit and modify the images they submit to the VAS engine. The VAS application relies on the Access Control Service of Windows Azure to handle authentication to the system. Images that are uploaded are managed in a SQL Azure database and in Windows Azure blog storage. A big benefit to 3M is that the concern over daily database management is nonexistent, because SQL Azure has built-in data protection, self-healing, and disaster recovery. A detailed architectural view of the solution is shown in Figure 15-4.

Figure 15-4. 3M VAS solution architecture

The architecture allows for easy dynamic scaling with Windows Azure compute resources. At times of high volume, administrators can simply change the number of Web and Worker roles needed to fulfill the demand. In the end, 3M pays just for what compute resources it needs.

Since the inception of Windows Azure in 2009, Microsoft continues to invest in the platform. While Windows Azure and developing applications for the cloud are topics outside of the scope of this book, many books and articles have been written on the subject. For a quick overview of the features within Windows Azure, check out the following web site: www.microsoft.com/windowsazure/features. Microsoft has a plethora of free webcasts, videos, virtual labs, and podcasts available on topics around Azure including security, the AppFabric, SQL Azure, and many others. Check out this information at www.microsoft.com/events/series/azure.aspx.

SQL Azure

Up until now, we have been talking mostly about the Windows Azure platform in general. One important piece of Windows Azure is SQL Azure. SQL Azure is not a default install of SQL Server running inside a virtual machine on the public Internet. While it may be tempting to think of it this way, it’s not correct. SQL Azure is a flavor of the traditional Microsoft SQL Server integrated within the Windows Azure platform. The end result is a cloud-based relational database service that offers enterprise-class availability, scalability, and security and is self-healing.

Understanding SQL Azure

With SQL Azure, you can, with a click of a button, provision and deploy relational databases solutions. These solutions come with manageability, high availability, scalability, and a familiar development model out of the box. SQL Azure database abstracts the logical administration from the physical administration. With respect to database administration, you continue to administer databases, logins, users, and roles, but Microsoft administers the physical hardware, such as hard drives, servers, and storage. For example, you will not find any command or statements that involve the disk in SQL Azure. Commands like xp_fixeddrives do not exist in the SQL Azure world, since Microsoft takes care of everything to do with disk provisioning and usage. This can also been seen within the CREATE DATABASE statement. It has been simplified to the following syntax convention:

A DBA should not worry so much about job security, because there is still plenty to manage. Even though we no longer are concerned with the physical environment, we will still be managing your applications with respect to security, query tuning, index management, and everything that’s logical. Using SQL Azure, we won’t be concerned with high availability or disaster recovery, because a SQL Azure database is automatically replicated to three different areas within the Microsoft datacenters.

The SQL Azure platform also manages load balancing, and in case of a server failure, it fails over transparently. Patching and upgrading of the server are all done automatically and transparent for us. This may sound like the holy grail of database servers. In some scenarios, it is, and in others, not so much. SQL Azure has a sweet spot, and it revolves around these core scenarios:

Web applications: The cloud and Windows Azure specifically is a prime scenario for web application hosting. With SQL Azure as a relational database store, application developers can store transactions, content, and streaming media—all in a highly available and fault-tolerant environment.

Departmental applications: SQL Azure is a great target for Tier 2, Tier 3, and Microsoft Access applications and for those applications that use SQL Server Express edition. SQL Azure provides an excellent opportunity to organize these various smaller applications into one place. This also makes it easier to audit and control access to the content.

Data hubs: Consider the scenario where you have remote sales force staff members querying catalogs and creating orders on their phones or laptops. These data need to be uploaded to a corporate server, and fresh product data needs to be sent from corporate to our remote employees. With SQL Azure and the Sync Framework, you can create sync between the remote employee’s phone or laptop, a SQL Azure database and the on-premise database. You can read more about the SQL Azure Data Sync from the following web site: http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-data-sync-overview.aspx. There are links off of this page to webcasts and articles that go in depth on the subject.

Independent software vendor software plus services: Software vendors can write hosted software solutions without the concern of building out infrastructure to support it. Microsoft’s Azure platform is not only about you writing your own applications for your own business needs. There is an online marketplace established where you can write applications and sell them or subscribe to real-time information feeds from commercial data providers and leverage this information in your own application. For more information on the Windows Azure Marketplace, check out www.microsoft.com/windowsazure/marketplace.

Getting Started

To sign up for Azure or to leverage the free trial offer check out the following website: www.microsoft.com/windowsazure.

The free trial requires the use of a credit card in case you go over your allocated time or disk space allowance.

If you click the Buy button on the Windows Azure web page, you will be presented with three offers:

• Pay as you go: Pay as you go is self-explanatory; you only pay while you are using computing power or disk space.
• Subscription: If you want to save more money and make a six-month commitment to the platform, you can check out the reduced cost by looking at the subscriptions.
• Member offers: Special deals for people who are MSDN subscribers. In some cases, if you have MSDN already, you may have free access to Azure for a given duration. Check the MSDN web site for the latest offerings, because things like this change much fast than text within this book!

As you make your way through the registration pages, you will be asked for a subscription type. In Azure, subscriptions are the billing boundary. Within a subscription, there is an account owner and a service administrator. These can be assigned to different Windows Live IDs, or to the same one. In general, the account owner will be responsible for the billing, whereas the service administrator is responsible for provisioning and managing the technical piece of Azure (i.e., deploying applications, creating SQL Azure databases, etc.). If you are just kicking the tires of Azure, you will probably use the same Live ID account for all of these accounts.

Assuming you have subscribed to Azure, there are two main web portals to note. Account owners will be interested in the Microsoft Online Services Customer Portal (MOSCP); see http://mocp.microsoftonline.com. This web site will show you the subscriptions you are currently using and an option to purchase more services and subscriptions. The MOSCP is shown in Figure 15-5.

Figure 15-5. Microsoft Online Services Customer Portal

Service administrators and those wishing to do something technical with Azure will leverage the Management Portal (https://windows.azure.com). This web site is the primary portal for creating and managing anything Azure related: SQL Azure databases, Windows Azure Compute and Storage, deploying applications, and so on. This portal is shown in Figure 15-6.

Figure 15-6. Windows Azure Management Portal

To get started with SQL Azure, we create a SQL Azure Server. A SQL Azure Server is a logical grouping of databases and acts as the central administrative point. Each SQL Azure Server has a fully qualified domain name, which gets created when you create a new server. This name is in the form servername.database.windows.net. The server name is automatically generated for you, and you will not be able to change this name.

Let’s go ahead and create a new SQL Azure Server. If we click the New Database Server button on the Management Portal, the Database node will be displayed. From this node, we can either create a new SQL Azure Server or create a new SQL Azure Database. On the left-hand side of the portal, you will see a list of the subscriptions the currently logged in Windows ID has assigned to it. If you are using the free trial, you may only see one subscription. To create a database, we need to first create the SQL Azure Server. If you click the Create a new SQL Azure Server button, you will be presented with the dialog shown in Figure 15-7.

Figure 15-7. SQL Azure Create Server dialog

When we create a new SQL Azure Server, it’s bound to a subscription. The subscription drives the price and all that monetary good stuff. Once we select a subscription, we are asked in what region we want the server to be placed. This depends on where the majority of the users who are accessing this instance of SQL Azure will be. Our options for this location include, North Central US, South Central US, North Europe, West Europe, East Asia, and Southeast Asia. The next page will ask for us for an administrator login and password. This is similar to the sa user found in on-premise SQL Server.

SQL Azure was developed with security in mind. By default, there is no external access to any of the databases that you create. The next page will ask you to define firewall rules to enable access to your SQL Azure server. By default, there are no rules. Figure 15-8 has been populated with a few sample rules to depict how you may want to enable certain scenarios like access from your home network.

Figure 15-8. Firewall rules page of the SQL Azure Create Server dialog

When you click the Finish button, your SQL Azure Server will be created. Information about the server can be displayed by clicking the server name under the Subscriptions tab in the Management Portal. You will notice that there is already a master database created for us. The Properties tab on the right side of the screen shows important information about your new SQL Azure Server. An example of this information is shown in Figure 15-9.

Figure 15-9. Properties panel of a SQL Azure Server within the Management Portal

From the example, the SQL Azure Server is called wzoi2cq3ut. One of the big advantages with SQL Azure is that it uses Tabular Data Stream (TDS) over the TCP/IP protocol. This is the same TDS protocol that is used by on-premise SQL Server. Thus, if we have an existing application that wants to leverage SQL Azure, all that is needed is to change the server name in the connection string from the on-premise name to the fully qualified DNS name of the SQL Azure Server.

Leveraging TDS over TCP/IP also means that we can connect and manage SQL Azure databases using familiar tools, including SQLCMD and SQL Server Management Studio 2008 R2 and above.

Managing a SQL Azure Database

At this point, we could continue using the Management Portal to create our database, but we can also use SQL Server Management Studio. To connect to our SQL Azure Server, we simply put the fully qualified DNS name in the connection string, as shown in Figure 15-10.

Figure 15-10. Connection dialog of SQL Server Management Studio

Provided your local IP is within the firewall and you supplied the right login and password, you should see the familiar Object Explorer enumerated with various database-level objects. Note that SQL Azure only supports SQL Authentication at this time. One of the things you will notice after a successful connection is the Object Explorer tree does not contain a lot of the nodes it contains when connected to on-premise SQL Server. This is because a SQL Azure database is not the same as an on-premise SQL Server. When you connect to a specific SQL Azure Server, you will see all your databases within that server. From a physical perspective, this is a façade, because in reality, these databases are physically spread across the environment to various nodes. Each node services many databases, so it is unlikely that any of your databases are on the same physical server. This architecture allows for SQL Azure to load balance and automatically move databases within the cluster and to take advantage of the available capacity and to maintain the published service level agreement (SLA).

With Microsoft taking care of the physical placement of the database, you won’t find any stored procedures that deal with the file system. Some other objects you may notice missing are SQL Server Agent. There is no job scheduling service available in SQL Azure at the time of this writing. A workaround would be to leverage a Windows Azure Compute node or an on-premise SQL Server Agent that would make a call to the SQL Azure database. Connecting to a SQL Azure database via SQL Server Management Studio yields the Object Explorer tree shown in Figure 15-11.

Figure 15-11. Object Explorer connected to a SQL Azure database

You’ll notice in Figure 15-11 that the build number for SQL Azure is different from that of the current SQL Server releases. SQL Server 2008 starts with 10.00 and SQL Server 2008 R2 starts with 10.5. Currently in SQL Azure, the build is 10.25. SQL Azure updates on its own schedule about every 3–6 months, so the version numbers will always be different than what you will find on an on-premise SQL Server installation

From SSMS, we can create a new database by selecting Create Database from the context menu of the Databases node or by using the CREATE DATABASE command within a New Query window. While the amount and quality of dialogs may be increased in later versions of SQL Server, the Create Database context menu item simply puts a template script in the Query Editor window. To create a database, let’s issue the following command in the query editor window:

Upon a refresh of the Object Explorer, we can see the newly created database.

Migrating Data into SQL Azure

There are many ways to get data into a SQL Azure database. You could run a script that contains a bunch of INSERT statements or explore some other ways, including these:

• The bulk copy utility (BCP.exe): BCP is a command-line tool that has been around for a long time. Out of the box, it can upload data to a SQL Azure database. More information on the BCP tool can be found here: http://blogs.msdn.com/b/sqlazure/archive/2010/05/21/10014019.aspx.
• SQL Server Integration Services (SSIS): SSSIS is a powerful tool for creating simple and complex dataflows. To SSIS SQL Azure is just another datasource. Keep in mind that, since OLE DB is not supported with SQL Azure, to use SSIS, you will have to use the ADO.NET library.
• SQL Azure Migration wizard: CodePlex is an open source community that provides free tools to the public based on the Microsoft platform. One of the projects on CodePlex is the SQL Azure Migration Wizard. This wizard will help you migrate SQL Server 2005/2008 databases to SQL Azure. Not only will the wizard move the database, it has the ability to analyze trace files against Azure. This is very important for application compatibility testing.

To get some real data in SQL Azure, let’s use the free SQL Azure Migration wizard available for download at the following web site: http://sqlazuremw.codeplex.com. The zip file will expose the SQLAzireMW.exe application. Launching this will run the wizard.

One of my favorite databases is pubs. It’s still available bundled with Northwind and available as a free download at http://archive.msdn.microsoft.com/northwind. After downloading this zip file, simply connect to a local SQL Server instance and run the INSTPUBS.SQL script against your server, and you will have pubs and party like its 1999 all over again.

To get pubs in SQL Azure, we could have executed the script directly against our SQL Azure database, but we would need to make a slight modification to the script to remove the USE statements. Changing database contexts via an existing connection to SQL Azure is not something you can do. You would need to break the connection and reconnect to the other database to perform this action in SQL Azure. For purposes of demonstration, let’s assume the pubs database is installed in an on-premise SQL Server. When we launch SQLAzureMW.exe, we are presented with the wizard screen shown in Figure 15-12.

Figure 15-12. SQLAzureMW Select Process page

To migrate our PUBS database, click the SQL Database button, and click Next. At this point, we will be asked to connect to the source SQL Server that has the database we want to migrate. Provide the necessary connection information, and select “pubs” from the “Select source” page in the wizard. The next page will allow us to select which objects we want to migrate over. By default, this wizard will migrate all database objects including stored procedures, tables, user defined data types, and views. After this page, the wizard will confirm that you are ready to start generating the SQL Script. While the wizard is generating the script, you will see the status of the migration. This is the first step in the migration, and the wizard is essentially scripting the objects and using BCP.exe to push out the data into a file on the local directory. The Results Summary page is displayed in Figure 15-13.

Figure 15-13. Results Summary page for the first step in migration

Next, the wizard will ask you for the location of the SQL Azure database. Remember that “User name” in this case is not just the administrative user name we defined but includes “@servername” at the end of the username, as shown in Figure 15-14.

Figure 15-14. Connection dialog

After connecting to the SQL Azure server, you will be asked to which database to add pubs. Since we do not have pubs, we can click the Create Database button on the Setup Target Server Connection panel and create a new database called pubs. The next page will execute the script and BCP of the original data against the new SQL Azure database. Results of this are shown in Figure 15-15.

Figure 15-15. Target Server Response dialog

Now, we have a copy of pubs living in our SQL Azure database!

Understanding SQL Azure specific views

A few system views are only available on a SQL Azure database. If you connect to your SQL Azure Server and enumerate the system views within the master database, you will see these. You can notice these special SQL Azure–only ones by a small lock on the bottom right side of the view icon.

To return information about the bandwidth used by each database, we can use the sys.bandwidth_usage view. When we copy the pubs database to SQL Azure, we see exactly how much bandwidth was consumed since that operation. This view returns two rows for every database with one row showing data ingress and the other showing data egress. The results for a SELECT * FROM sys.bandwidth_usage is shown in Table 15-1.

The quantity field is in kilobytes (KB).

The sys.database_usage returns one row for each day of your subscription. The view returns the type of database (i.e., business or web) and the maximum number of databases of that SKU type that existed for that day. This view is useful to give you a guesstimate of the cost per day of the SQL Azure database.

The sys.firewall_rules view returns the list of firewall rules associated with the SQL Azure database. Running a query of this view would show us the firewall rules we defined in Figure 15-8.

The sys.dm_db_objects_impacted_on_version_change view is a database-scoped view that provides early warning for objects that will be impacted by version upgrades. Version upgrades happen, and with SQL Azure, you need to stay current. Even though Microsoft does everything possible to remain backward compatible, there is some chance that things may break if not tested.

Backing Up and Restoring Data with SQL Azure

At the time of this writing there is no way to easily backup and restore your database within SQL Azure. You may be curious as to why such a fundamental maintenance task is missing from the standard capabilities of SQL Azure and the answer lies in the fact that a SQL Azure database inherently is highly available. Multiple copies of your database are created automatically as soon as you create the database. These multiple copies protect you from loss of the entire database. Server failures and failover happen automatically for SQL Azure databases and require no interaction or configuration on your part.

However, the story is still incomplete. What if someone calls you and says, “I accidently dropped a table can you restore a backup”? This is where there is no good answer as of yet. Your database as a whole is protected, but there is no good way to restore a single object such as an inadvertently dropped table.

Right now, you can use SSIS or an SQLCMD script to automate the data creation or regeneration. You may also look into creating a database copy. Of course, you would need to make a copy before this disaster happens. To create a database copy, simple execute the following command: CREATE DATABASE Pub2 AS COPY OF Pubs. Note that this will create a separate database of the same size as your original database. If you are counting your pennies, maintaining a separate database may add up in cost and will certainly add up in storage space if your databases are large.

There is system view called sys.dm_database_copies. This view will show information on the status of the copy operation. For relatively small databases, there won’t be a lot of useful information here, because the copy would happen so quickly the percentage_complete data would be meaningless. If the database failed to copy, the error_state column would reflect this.

Storing More Than Relational Data

The first effort with SQL Azure is to develop a highly scalable and robust relational database engine that can work with your Windows Azure applications and with on-premise data. Microsoft is not stopping at relational data. At the time of this writing, the SQL Azure Reporting feature is in Community Technology Preview (otherwise known as beta release). The first iteration of SQL Azure Reporting Services will include the ability to create and execute reports based off of data stored in Windows Azure or a SQL Azure database. Although the first version won’t be at feature parity with the on-premise SQL Server Reporting Services, it’s a step in the business intelligence direction. Although there is no official word on details regarding Microsoft’s story for SQL Server Analysis Services in the cloud, you can bet there is something in the works.

Summary

Cloud computing is a technology that’s been around for some time. Rapid advancements in technology reduce the costs of building powerful datacenters, enabling companies like Amazon, Microsoft, and Google to intentionally design datacenters to rent out excess capacity. These offerings are more mature now and moving beyond the Infrastructure and Software as a Service spaces into Platform as a Service. This is where Microsoft has placed its bets with Windows Azure.

SQL Azure, which is a component of the Windows Azure platform, is a cloud-based highly scalable relational database based on SQL Server. In this chapter, we explored how to get started with SQL Azure and some of the issues and benefits of this platform. Keep in mind that this technology is evolving at a rapid pace, and limitations discussed in this chapter may no longer be limitations shortly after this book is published. For the latest information, always check the web sites.