Professional Cloud Computing with Windows Azure by alice1311


									Chapter 12: Managing SQL
Azure Accounts, Databases,
and DataHubs
          SQL Azure is the SQL Server team’s name for a set of highly
  scalable cloud services based on a customized version of SQL Server 2008
  and related services hosted in Microsoft data centers. SQL Azure is
  intended to provide the capabilities of an enterprise-grade data center
  without the capital cost of its physical facilities or the operating expenses
  for its management or maintenance. SQL Azure provides high availability
  by replicating a total of three redundant copies of your data to separate
  physical servers. SQL Azure achieves scalability while maintaining
  transactional consistency by partitioning data into individual 10GB or
  smaller databases.
        When this chapter was written, only the first of these services—SQL
  Azure Database (SADB) v1, a scalable and available relational database—
  had been released as a Community Technical Preview (CTP). SADB was
  scheduled for commercial availability during Microsoft’s Professional
  Developers Conference (PDC) in mid-November 2009.
         Ultimately, SQL Azure will consist of the following services:

          SQL Azure Database to provide core SQL Server database
           capabilities (release during PDC 2009)
          Data Sync to enable synchronizing cloud and on-premises
           databases with the Microsoft Sync Framework to be released
           “soon after” PDC 2009
          Secure Data Hub, an “aggregation of enterprise, partner, desktop
           and device data within SQL Azure” based on the “Huron”
           incubator project with no current CTP or release timetable
          Business Intelligence (BI) and Reporting Services (RS) with no
           current CTP or release timetable
        This chapter concentrates on creating a SQL Azure account, getting
  connected to an SADB v1 server, and creating user databases with SQL
      Server Management Studio (SSMS) 2008 [Express] and the sqlcmd utility.
      This chapter was written before SADB released to the Web (RTW) as a
      commercial product with service fees. When you connect to the commercial
      version, the credit card you specify when creating your account will be
      charged US$9.99 per month for a database size up to 1GB (Web Edition)
      and US$99.99 per month for up to 10GB of space (Business Edition), plus
      US$0.10 per GB of data ingress and US$0.15 per GB of data egress.
 The current version of this chapter is based on the SQL Azure Database
August 2009 CTP. Sections covering the four other SQL Azure elements
will be added to this online chapter as CTPs become available.
 Service fees for SADB might change on or after commercial release.
Service-level guarantees specific to SADB were not included in Microsoft’s
“Confirming Commercial Availability and Announcing Business Model” blog
post of July 14, 2009 (,
business-model.aspx) .

Tracking SSDS’s Migration to a
Relational Database
             Ray Ozzie announced SQL Server Data Services (SSDS) at the
      MIX08 conference in Las Vegas during his March 5, 2008 keynote address.
      SSDS became available as a CTP later in the first half of 2008 and provided
      an on-demand, pay-per-use data model, which offered the following

             A flexible, schemaless entity model that lets users add new
              attributes to the entity type when needed; the system indexes the
              attributes automatically.
             Simplified data types for attribute values: string, numeric,
              datetime, boolean, and binary.
             REST-based and LINQ-like queries; SOAP was also supported.
             Plain old XML (POX) was the only wire format then available;
              AtomPub was promised.
             No projections; the unit of storage was a complete entity.
             Highly scalable by partitioning of sizes in the 10s of GB.
             Secure data access with SSL, LiveID authentication, and
              authorization at the account (billing unit), authority (collection of
              containers), container (set of entity sets), and type (entity type)
             A promise of data synchronization and bulk uploads with the
              Microsoft Sync platform.
 For more background on the MIX08 SSDS announcement and early
reaction to SSDS in the computer trade press, see the “SQL Server Data
Services to Deliver Entities from the Cloud” post of March 7, 2008, to the
OakLeaf blog (,
data-services-to-deliver.html) and “Spelunking SQL Server Data
Services” (,
             The initial SSDS architects chose the Entity-Attribute-Value (EAV)
      data model implemented by a hierarchical set of Authority, Container, and
      Entity (ACE) objects, rather than the traditional relational data model
      because EAV databases were understood to be easier to scale out to
      extreme sizes typified by Google’s BigTable and Yahoo!’s Hadoop
      examples. The problem with this approach was the difficulty that potential
      customers encountered when attempting to distinguish SSDS tables from
      the similar EAV tables offered by Azure Data Services. Further, the Azure
      marketing team was strongly promoting the benefits of leveraging
      developers’ experience with .NET programming and Visual Studio to ease
      the burden of migrating conventional ASP.NET applications and .NET-
      based services from on-premises data centers to those in the cloud.
      Developers and their management were more interested in leveraging their
      relational database design and Transact-SQL (T-SQL) query skills than the
      potential for increased scalability. Architects and developers also had
      misgivings about the “eventual consistency” properties of EAV tables
     rather than the immediate, transactional update consistency of SQL Server
 On October 27, 2008, Ray Ozzie announced that Windows Azure would
be the name of Microsoft’s new Platform as a Service (PaaS) cloud
computing contender. SSDS was renamed SQL Data Services (SDS) at
PDC 2008.
            Microsoft’s marketing folks are well known for listening intently to
     their users, including millions of developers, and acting on user feedback.
     The SSDS team finally understood the error of their EAV approach about
     one year later and announced a mid-course correction to their SSDS plans
     on March 10, 2009. Also on that day, David Robinson, this book’s
     Technical Editor, added “The no spin details on the new SDS features” post
     228.aspx) to what’s now the SQL Azure Team Blog:

                  Today we announced the details of our plans to accelerate
                  the delivery of core relational database features as part of
                  SDS. There has been quite a bit of buzz about SDS over
                  the past couple weeks and it is great to be able to share the
                  details more broadly.
                  If we flash back about a year ago to Mix 08, Nigel Ellis got
                  up on stage to introduce the community to SDS which, at
                  the time, was a flexible entity based cloud database that
                  you accessed using standard internet protocols. We made
                  this announcement with the promise that more relational
                  capabilities would be coming - and they did. But the
                  universal feedback we received from our TAP partners and
                  other early adopters was the need for a relational database
                  delivered as a service. This was extremely valuable
                  feedback and drove us to more aggressively investigate
                  ways in which we could deliver these features. As a result
                  of that work and based on the progress we’ve since made in
                  the product team, we are announcing that SDS will deliver
                  full relational database capabilities as a service.
           The SDS team changed its name and rebranded the service to SQL
     Azure on July 9, 2009.
 For additional details about SDS’s change from the EAV to relational data
model, see the February 24, 2009, “A Mid-Course Correction for SQL Data
Services” (,
correction-for-sql-data.html) and the March 10, 2009, “SQL
Data Services Abandons REST for TDS API and Knocks My Socks Off”
services-abandons-rest-for-tds.html) posts.

Reviewing Current SQL Azure
Database Documentation and
             MSDN’s SQL Azure Database documentation, which is available
      online at,
      us/library/ee336279.aspx, contains the following chapters:

               Introducing SQL Azure Database
               SQL Azure Database Concepts
               Developer's Guide
               Guidelines and Limitations
               Transact-SQL Reference
               SQL Azure Database Copyright and Legal Information
 The Transact-SQL Reference chapter is especially useful because it
contains lists of supported, partially supported, and unsupported T-SQL
reserved words.
             The Windows Azure team released the Windows Azure Platform
      Training Kit – August [2009] Update for downloading on August 17, 2009,
     53B7B77EDF78&displaylang=en. The kit offers the following SQL
     Azure Database–specific elements in .pptx, .docx, or .htm format:

            Presentations

                   Introduction to SQL Azure
                   Building Applications using SQL Azure
                   Scaling Out with SQL Azure

            Demonstrations

                     Preparing your SQL Azure Account
                     Connecting to SQL Azure
                     Managing Logins and Security in SQL Azure
                     Creating Objects in SQL Azure
                     Migrating a Database Schema to SQL Azure
                     Moving Data Into and Out Of SQL Azure using SSIS
                     Building a Simple SQL Azure App
                     Scaling Out SQL Azure with Database Sharding

            Hands On Labs

                   Migrating Applications to Windows Azure
                   Introduction to SQL Azure
                   Migrating Databases to SQL Azure
            Demonstrations and Hands On Labs require a sample database, so
     you must have a free trial or paid commercial SQL Azure account in which
     to create the database.
 You can find the SQL Azure – Getting Started forum at,
Obtaining and Redeeming an
Azure Invitation Token for an
             Prior to SADB’s commercial release as a paid service in November
      2009, early adopters need an invitation to join the current CTP by
      submitting a GUID token. If free accounts are still available when you read
      this book, go to the Microsoft Connect sign-up page at,
      iteID=547, complete the survey, and wait for an e-mail message from
      SQL Azure Talk (, which directs you to
      visit, where you sign in with a valid
      Windows Live ID and enter your invitation code (token), such as
      53E249D7-56AE-AD22-AF02-5AD53784A47D in the text box (see
      Figure 12-1).
 The preceding invitation code isn’t valid.
  If you previously completed the survey to receive a token for the free
SSDS or SDS trial, you won’t be able to complete the survey at this point
but you should have received an e-mail invitation previously. If not and the
free trial is still in effect, leave a message in the SQL Azure – Getting
Started forum.

            Figure 12-1. The SQL Azure Portal’s Invitation Code page for
            creating a free trial account. [f1201.bmp]

 When this chapter was written, early SQL Azure adopters had requested
continuation of free trial accounts for developers but it wasn’t known if
Microsoft intended to do this. The portal page for creating a paid
commercial SQL Azure account is likely to be similar to Figure 12-1.
            Clicking Submit with a free trial invitation token opens the Terms of
      Use page (see Figure 12-2).

            Figure 12-2. The SQL Azure Terms of Use page.          [f1202.bmp]

             Review the Terms of Use and click I Accept, if you accept them, to
      open the Create Server page. SQL Azure requires assigning a Service
      Administrator account, equivalent to SQL Server’s sa account, with a
      username and password (see Figure 12-3). Free trial accounts are created
      only in the USA_Northwest (Quincy, Washington) data center; you can
      create paid commercial accounts in any other data center, such as
      USA_Southwest (San Antonio, Texas).
 On August 4, 2009, Microsoft announced that all Windows Azure
accounts will be migrated from the Quincy data center to other U.S. data
center(s) before starting commercial service in November 2009 due to a
disagreement with the state of Washington about the payment of sales
taxes on data center construction and hardware costs.
 SQL Azure uses SQL Server authentication only and doesn’t support
Windows authentication.

            Figure 12-3. The SQL Azure Create Server page for assigning
            the Server Administrator Username and Password.

             Click the Create Server button to open the My Projects page, which
      contains a Manage link for the SDS-Only CTP Project trial project’s
      assigned name or the name you selected for a paid commercial project (see
      Figure 12-4).

            Figure 12-4. The SQL Azure Portal’s My Projects management
            page. [f1204.bmp]
Creating a User Database
             On the My Projects page, click the Manage link to open the Server
      Administration page, which enables you to add databases to or drop them
      from the server. Click Create Database to open a dialog that lets you name
      the user database (see Figure 12-5).

             Figure 12-5. The SQL Azure Portal’s Server Administration
             page for adding or dropping user databases.      [f1205.bmp]

 The Server Administration page connects to the selected server’s default
master database. SQL Azure assigns a DNS-compatible name to the
server. SQL Azure doesn’t permit database administrators or users to add
objects directly to master.
             Click Create to close the dialog, add the user database to the server,
      and enable the Drop Database button when you select a user database (see
      Figure 12-6).

             Figure 12-6. The SQL Azure Portal’s Server Administration
             page after adding an empty Northwind user database.

            Alternatively, you can create or drop a user database by opening the
      master database in SSMS 2008 [Express] and executing a CREATE
      DATABASE DatabaseName or DROP DATABASE DatabaseName
      T-SQL command.
 The next section describes how to connect to databases with SSMS 2008
[Express]. You can’t connect to SQL Azure databases with SSMS 2005
            Clicking the upper Connection Strings button opens a message with
      ADO.NET, ODBC, and OLE DB connection strings for the Master
      database that you can copy to the Clipboard:
User ID=rogerj;Password=myPassword;Trusted_Connection=False;



 The tcp: prefix specifies that the connection will use SQL Server’s
native Tabular Data Stream (TDS) protocol over TCP port 1433. SQL
Azure connections are encrypted by default; the server will terminate the
connection if the client doesn’t accept encryption. Connection strings for
paid commercial databases won’t include the ctp. segment.
              With a user database selected, click the lower Connection Strings
       button to open a message that contains examples of ADO.NET, ODBC, and
       OLE DB connection strings for the selected user database (see Figure 12-

             Figure 12-7. Connection strings for ADO.NET, ODBC, and OLE
             DB drivers for the Northwind database.    [f1207.bmp]

User ID=rogerj;Password=myPassword;Trusted_Connection=False;

Driver={SQL Server};;

            Click the ADO.NET entry’s Copy to Clipboard link to save the
      connection string.
 The ability to migrate server connections from a local SQL Server 2008
[Express] instance to SADB in the cloud by simply changing the
connection string is a widely publicized SADB feature.

Connecting to SADB Master and
User Databases with SSMS 2008
             SSMS 2008 is the most convenient tool for executing single and
      batched T-SQL commands to the master and user databases. Unfortunately,
      the standard SSMS 2008 [Express] version’s Object Browser feature isn’t
      compatible with the SADB August 2009 CTP; you’ll also encounter error
      messages during the connection process.
 The team expects to provide support for additional SSMS features in the
commercial version scheduled for release in mid-November 2009.
 SADB wasn’t compatible with the Server Explorer feature of Visual Studio
2008 or 2010 Beta 1 when this chapter was written.
 You need SQL Server 2005 or 2008 [Express] to emulate SADB in
projects that use the Development Fabric. A standalone version of SSMS
2008 Express is available from,
SSMS 2005 [Express] doesn’t work with SADB.
             To connect to the SADB server that you provisioned in the
      preceding section with standard SSMS 2008 versions, launch SSMS 2008
      [Express] and click Cancel in the Connect to Database dialog that opens on
      startup. Choose Tools, Options to open the Options dialog with the
      Environment, General option selected by default, change the Open New
      Query Window in the At Startup list, and click OK to save the change.
  If you attempt to connect to SADB from SSMS startup, you receive the
“Invalid object name ‘sys.configurations’.” error message shown in Figure

            Figure 12-8. The error message generated by attempting to
            connect to SADB on opening an unpatched SSMS version.

             Click SSMS’s New Query toolbar button to open the Connect to
      Database dialog; accept the default Database Engine Server Type; copy the
      ADO.NET connection string sample to the Server Name text box, trim the
      entry to the server name, including the tcp: prefix; and (optionally) mark
      the Remember Password check box.
             Select SQL Server Authentication in the Authentication list, and
      type the server administrator credentials you created in the preceding
      section in the Login and Password text boxes (see Figure 12-9).

            Figure 12-9. Specifying SADB login parameters in the standard
            Connect to Database dialog.    [f1209.bmp]

             SADB doesn’t support the T-SQL USE command and, therefore,
      requires you to specify the database to which to connect. So, click the
      Options >> button to expand the dialog. Accept the <default> or type
      master for the master database or the name of an existing user database in
      the Connect to Database combo box, and mark the Encrypt Connection
      check box (see Figure 12-10).

            Figure 12-10. Specifying the SADB database for an encrypted
            connection. [f1210.bmp]
 SADB connections are encrypted with Transport Level Security (TLS) by
default but marking[I prefer the en-uk term “tick”] the Encrypt Connection
check box will please your organization’s data security auditors.
             Click Connect to open the connection and click OK to dismiss the
      spurious “Unable to apply connection settings” message (see Figure 12-11).

             Figure 12-11. Connecting to an unpatched SADB database
             issues this error message, which you can safely disregard.

             If the user database you specified in the Connect to Database dialog
      doesn’t exist, the connection will silently default to the master database,
      which is indicated by the presence of CloudNode.MasterDb in the
      highlighted Available Databases toolbar list and in the status bar. To create
      the user database, execute the CREATE DATABASE DatabaseName
      command (see Figure 12-12).

             Figure 12-12. The Available Databases list and status bar
             identify the connection’s database name. [f1212.bmp]

 An active connection to a user database is indicated by
CloudNode.dbo.UserDb[…] replacing CloudNode.MasterDb in the
Available Databases list and status bar.
 To conserve resource consumption, SADB automatically closes
connections after five minutes of inactivity. To reopen a closed connection
click the Connect or Change Connection buttons at the extreme left of the
SQL Editor toolbar.
Using the sqlcmd Utility with
              The sqlcmd utility is an alternative to SSMS that lets you manage
       database connections at the command prompt. The following instruction
       with valid server administrator credentials typed or pasted at the Windows
       command prompt opens a connection to the Northwind database running on
       the SADB server created earlier in the chapter:
sqlcmd -S -U rogerj@k8jv7gpmwb
-P Pas$w0rd -d Northwind

            The following table describes common sqlcmd command-line
  Argument      Description
  -?            Show syntax summary
  -S            Server (use complete DNS name)
  -U            Server administrator name in login@servername (name
                only) format
  -P            Server administrator password
  -d            User database name (default is master)
  -i            Query input (T-SQL) file path\name
  -o            Query output file path\name
             Figure 12-13 shows the result of executing sqlcmd -?.

             Figure 12-13. The Windows command prompt displaying the
             sqlcmd syntax summary.[f1213.bmp]

              After connecting to SADB, executing T-SQL statements and batched
       queries with sqlcmd is a simple process. At the sequentially numbered #>
       prompt, type a valid T-SQL command, and press Enter. Type GO and press
       Enter to execute the preceding command(s) and reset the prompt number to
       1. Type quit or exit to close the session. Figure 12-14 shows sqlcmd’s
       window after executing the SELECT name FROM sys.tables
       command against the Northwind database you create with SSMS and a
       modified version of the InstNwind.sql script in the next chapter.
         Figure 12-14. Using sqlcmd to display a list of table names in a
         sample Northwind database. [f1214.bmp]

Comparing SADB with SQL Server
2008 R2 Databases
         SADB servers and databases are virtual objects abstracted from a
  customized, multi-tenant version of clustered SQL Server instances and
  don’t correspond to individual physical server instances. Therefore, you
  administer databases, tables, indexes, tuning, query optimization, logins,
  users, and roles but not physical storage, such as servers, files, and fixed
  disk drives. SADB automatically handles data replication and load-
  balancing for high availability, including transparent fail-over when a
  server dies. Backup is handled by data replication; restore operations after
  data loss or corruption are automatic.
         SADB blocks statements and options that attempt to directly
  manipulate physical resources, such as RESOURCE GOVERNOR, filegroup
  references, and some physical server DDL statements. Attempts to set
  server options, such as SET ANSI_NULLS ON, generate error messages.
  CRL database objects, SQL Service Broker, SQL trace flags, SQL Server
  Profiler and Database Tuning Advisor tools and utilities aren’t available.
          Deprecated data types, such as text, ntext, and image aren’t
  supported but you can substitute varchar(max), nvarchar(max),
  and varbinary(max) for them, as you’ll see in the next chapter. SADB
  currently doesn’t permit use of SQL Server 2008’s geography and
  geometry spatial data types. If you need to encrypt data, you must do so
  in the client application; SADB doesn’t support the column-level or row-
  level encryption introduced by SQL Server 2005 or SQL Server 2008’s
  Transparent Data Encryption (TDE). The “Transact-SQL Reference”
  chapter of MSDN’s SQL Azure documentation
  us/library/ee336281.aspx) includes tables that list supported,
  partially supported, and unsupported T-SQL statements.
         SQL Azure Database is the basic element of SQL Azure. SADB
  provides a highly scalable and available relational database as a public
  cloud computing resource running together with the Windows Azure
  Platform in Microsoft’s newly constructed data centers, except at Quincy,
  Washington. The SADB Web Edition supports transactional-consistent
  databases up to 1GB in size; the Business Edition is limited to 10GB or
         SADB grew out of early adopters’ dissatisfaction with technical
  previews of SQL Server Data Services (SSDS), which offered a flexible,
  highly scalable, schemaless Entity-Attribute-Value data model but was
  missing the relational features that IT groups and developers expected from
  a service carrying the SQL Server name. In March 2009, Microsoft
  announced that SSDS would morph to a fully relational version called SQL
  Data Services (SDS), which enable developers to leverage their data
  architecture and Transact-SQL skills. SDS became SQL Azure Database in
  July 2009 and the first SADB CTP became available to invitees on August
  17, 2009.
        The CTP version required requesting an invitation from the
  Microsoft Connect site that resulted in a token, which was redeemable from
  the SQL Azure portal for a single server with multiple databases at no
  charge. The commercial version, which is expected to release in mid-
  November 2009 at Microsoft’s Professional Developers Conference in Los
  Angeles, will require a credit card to cover a charge of US$9.99 per month
  (Web Edition) or US$99.99 per month (Business Edition), plus US$0.10
  per GB of data ingress and US$0.15 per GB of data egress.
          SADB auto-assigns a unique DNS address to each server. SQL
  Server Management Studio 2008 [Express] and the sqlcmd command-line
  utility connect to SADB with the TCP protocol on port 1433 using SQL
  Server’s traditional Tabular Data Stream format via encrypted
  transmissions. Using TDS lets developers change from connections to local
  SQL Server 2005 or 2008 [Express] instances for development to SADB
  databases in the cloud simply by changing the connection string. Future
  modifications to SSMS 2008 [Express] will result in SADB gaining full-
  featured management by SSMS.
       Microsoft manages SADB’s physical infrastructure, such as server
instances, disk drives and files, as well as backup and restore operations, so
many T-SQL DDL commands aren’t supported. However, developers have
full control over databases, tables, indexes, tuning, query optimization,
logins, users, and roles. SADB doesn’t support deprecated and new SQL
Server 2008 spatial datatypes, nor does it offer data encryption.

To top