Pro SQL Azure

Pro SQL Azure

by Scott Klein, Herve Roggero
Released October 2010
Publisher(s): Apress
ISBN: 9781430229612

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

SQL Azure represents Microsoft's cloud-based delivery of its enterprise-caliber, SQL Server database management system (formerly under the code name "Oslo"). Pro SQL Azure introduces you to this new platform, showing you how to program and administer it in a variety of cloud computing scenarios. You'll learn to program SQL Azure from Silverlight, ASP.NET, WinForms, and from SQL Reporting Services. You'll also understand how to manage the platform by planning for scalability, troubleshooting performance issues, and implementing strong security.

  • Shows how to use SQL Azure from Silverlight, ASP.NET, and more

  • Covers management, scalability, and troubleshooting

  • Addresses the all-important issue of securing your data

Table of contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewer
  4. Acknowledgments
  5. 1. Getting Started with SQL Azure
    1. 1.1. Introduction to Cloud Computing
      1. 1.1.1. Who Is Doing What in the Cloud?
      2. 1.1.2. Typical Cloud Services
    2. 1.2. Discovering the Microsoft Azure Platform
      1. 1.2.1. Why Microsoft Azure?
      2. 1.2.2. About Geographic Locations
      3. 1.2.3. Storing Data in Azure
    3. 1.3. SQL Azure Primer
      1. 1.3.1. Registering for Azure
      2. 1.3.2. Creating a Database in SQL Azure
        1. 1.3.2.1. Using the SQL Azure Portal
        2. 1.3.2.2. Using a T-SQL Command
      3. 1.3.3. Configuring the Firewall
      4. 1.3.4. Connecting with SQL Server Management Studio
      5. 1.3.5. Creating Logins and Users
        1. 1.3.5.1. Creating a New Login
        2. 1.3.5.2. Creating a New User
      6. 1.3.6. Assigning Access Rights
      7. 1.3.7. Understanding Billing for SQL Azure
    4. 1.4. Limitations in SQL Azure
      1. 1.4.1. Security
      2. 1.4.2. Backups
      3. 1.4.3. Objects
      4. 1.4.4. Miscellaneous
      5. 1.4.5. Drivers and Protocols
    5. 1.5. Conclusion
  6. 2. Design Considerations
    1. 2.1. Design Factors
      1. 2.1.1. Offsite Storage
      2. 2.1.2. High Availability
      3. 2.1.3. Performance
        1. 2.1.3.1. Throttling
        2. 2.1.3.2. Application Design Considerations
      4. 2.1.4. SQL Data Sync Framework
      5. 2.1.5. Direct vs. Serviced Connections
      6. 2.1.6. Pricing
      7. 2.1.7. Security
      8. 2.1.8. Review of Design Factors
    2. 2.2. Design Patterns
      1. 2.2.1. Direct Connection
      2. 2.2.2. Smart Branching
      3. 2.2.3. Transparent Branching
      4. 2.2.4. Sharding
        1. 2.2.4.1. Shard Concepts and Methods
        2. 2.2.4.2. Read-Only Shards
        3. 2.2.4.3. Read-Write Shards
      5. 2.2.5. Offloading
      6. 2.2.6. Aggregation
      7. 2.2.7. Mirroring
    3. 2.3. Combining Patterns
      1. 2.3.1. Transparent Branching + RWS
      2. 2.3.2. Cascading Aggregation
    4. 2.4. Sample Design: Application SLA Monitoring
      1. 2.4.1. Pre-Azure Application Architecture
      2. 2.4.2. Azure Implementation
    5. 2.5. Other Considerations
      1. 2.5.1. Blob Data Stores
      2. 2.5.2. Edge Data Caching
      3. 2.5.3. Data Encryption
    6. 2.6. Conclusion
  7. 3. Setting Up and Configuring
    1. 3.1. Creating Your Azure Account
    2. 3.2. Managing Your Azure Projects
    3. 3.3. Azure Server Administration
      1. 3.3.1. Server Information
      2. 3.3.2. Firewall Settings
      3. 3.3.3. Databases
    4. 3.4. Creating Databases, Logins, and Users
      1. 3.4.1. Databases
      2. 3.4.2. Logins and Users
        1. 3.4.2.1. Logins
        2. 3.4.2.2. Users
    5. 3.5. Connecting to a SQL Azure Database
      1. 3.5.1. Connecting Using ADO.NET
      2. 3.5.2. Connecting from the Entity Framework
    6. 3.6. Conclusion
  8. 4. Security
    1. 4.1. Overview
      1. 4.1.1. Confidentiality
      2. 4.1.2. Integrity
      3. 4.1.3. Availability
    2. 4.2. Securing Your Data
      1. 4.2.1. Encryption
      2. 4.2.2. Hashing
      3. 4.2.3. Certificates
    3. 4.3. Access Control
      1. 4.3.1. Authentication (AUTHN)
      2. 4.3.2. Authorization (AUTHZ)
        1. 4.3.2.1. Creating Logins and Users
        2. 4.3.2.2. Schemas
      3. 4.3.3. Firewall
    4. 4.4. Compliance
    5. 4.5. Conclusion
  9. 5. Data Migration and Backup Strategies
    1. 5.1. Migrating Databases and Data to SQL Azure
      1. 5.1.1. Generate and Publish Scripts Wizard
        1. 5.1.1.1. Starting the Wizard
        2. 5.1.1.2. Choosing Target Objects
        3. 5.1.1.3. Setting Advanced Options
        4. 5.1.1.4. Saving and Publishing
        5. 5.1.1.5. Reviewing the Generated Script
        6. 5.1.1.6. Fixing the Script
        7. 5.1.1.7. Executing the Script Against an Azure Database
      2. 5.1.2. SQL Server Integration Services
        1. 5.1.2.1. Creating an Integration Services Project
        2. 5.1.2.2. Clearing Any Preexisting Data
        3. 5.1.2.3. Building a Migration Package
        4. 5.1.2.4. Executing Your Migration Package
        5. 5.1.2.5. Verifying the Migration
        6. 5.1.2.6. Other Cases to Consider
      3. 5.1.3. Bcp
        1. 5.1.3.1. Invoking BCP
        2. 5.1.3.2. Exporting the Data
        3. 5.1.3.3. Importing the Data
        4. 5.1.3.4. Putting the Export and Import Together
    2. 5.2. SQL Azure Backup Strategies
      1. 5.2.1. Copying a Database
      2. 5.2.2. Knowing When a Copy Is Complete
      3. 5.2.3. Automating a Database Copy
      4. 5.2.4. Maintaining a Backup History
    3. 5.3. Conclusion
  10. 6. Programming with SQL Azure
    1. 6.1. Application Deployment Factors
      1. 6.1.1. On-Premise Application
      2. 6.1.2. Azure-Hosted Application
      3. 6.1.3. Which to Choose?
    2. 6.2. Connecting to SQL Azure
      1. 6.2.1. ADO.NET
        1. 6.2.1.1. Making the Connection
        2. 6.2.1.2. Using a Data Reader
        3. 6.2.1.3. Using a Dataset
      2. 6.2.2. ODBC
      3. 6.2.3. Sqlcmd
    3. 6.3. WCF Data Services
      1. 6.3.1. Creating a Data Service
      2. 6.3.2. Connecting the Service to the Model
      3. 6.3.3. Creating the Client Application
      4. 6.3.4. Creating the User Interface
      5. 6.3.5. Running the Application
    4. 6.4. Record Navigation in WCF Data Services
      1. 6.4.1. Disabling Internet Explorer's Feed Reading View
      2. 6.4.2. Viewing the Final Results
    5. 6.5. Azure Programming Considerations
    6. 6.6. Conclusion
  11. 7. OData with SQL Azure
    1. 7.1. OData Overview
      1. 7.1.1. OData Producers
      2. 7.1.2. OData Consumers
    2. 7.2. Enabling OData on an Azure Database
      1. 7.2.1. Getting Started at SQL Azure Labs
      2. 7.2.2. Understanding Anonymous Access
      3. 7.2.3. Understanding the Access Control Service
      4. 7.2.4. Implementing Security Best Practices
    3. 7.3. Viewing OData-Enabled SQL Azure Data
    4. 7.4. Viewing Data through an OData Consumer
    5. 7.5. Building Two OData Consumer Applications
      1. 7.5.1. Simple Demo App
      2. 7.5.2. Windows Mobile 7 Application
    6. 7.6. Conclusion
  12. 8. Reporting Services with SQL Azure
    1. 8.1. Starting a SQL Azure–Based Report
    2. 8.2. Creating the SQL Azure Data Source
    3. 8.3. Creating the Report Design
    4. 8.4. Deploying the Report
    5. 8.5. Creating a Subreport
      1. 8.5.1. Adding the Subreport to the Main Report
    6. 8.6. Conclusion
  13. 9. Windows Azure and ASP.NET
    1. 9.1. Creating a Windows Azure Service
    2. 9.2. Creating a Windows Azure Project
      1. 9.2.1. Configuring Your Development Environment
      2. 9.2.2. Creating Your First Visual Studio Cloud Project
      3. 9.2.3. Connecting a GridView to SQL Azure
    3. 9.3. Deploying an ASP.NET Application in Windows Azure
    4. 9.4. Conclusion
  14. 10. Designing for High Performance
    1. 10.1. General Performance Concepts
      1. 10.1.1. Chatty vs. Chunky
      2. 10.1.2. Lazy Loading
      3. 10.1.3. Caching
      4. 10.1.4. Asynchronous User Interface
      5. 10.1.5. Parallel Processing
      6. 10.1.6. Shards
      7. 10.1.7. Coding Strategies Summary
    2. 10.2. Building a Shard
      1. 10.2.1. Designing the Shard Library Object
      2. 10.2.2. Managing Database Connections
      3. 10.2.3. Reading Using the Shard
      4. 10.2.4. Caching
      5. 10.2.5. Updating and Deleting Records in the Shard
      6. 10.2.6. Adding Records to the Shard
    3. 10.3. Managing a Shard
      1. 10.3.1. Managing Exceptions
      2. 10.3.2. Managing Performance
      3. 10.3.3. Working with Partial Shards
      4. 10.3.4. Managing Transaction Consistency
      5. 10.3.5. Managing Foreign Key Constraints
    4. 10.4. Creating Vertical Partition Shards
    5. 10.5. Conclusion
  15. 11. SQL Azure Data Sync Services
    1. 11.1. Understanding the Data Sync Service
      1. 11.1.1. Why the Need?
      2. 11.1.2. The Basic Scenario
    2. 11.2. Configuring Synchronization
      1. 11.2.1. Agreeing to the Terms of Service
      2. 11.2.2. Creating a Sync Group
      3. 11.2.3. Defining the Hub and Member Databases
      4. 11.2.4. Selecting Tables to be Synchronized
      5. 11.2.5. Modifying a Sync Group
    3. 11.3. Manually Synchronizing
      1. 11.3.1. Looking at the Database Changes
      2. 11.3.2. Looking at the Synchronized Data
    4. 11.4. Scheduling Data Synchronization
    5. 11.5. Synching an On-Premises Database with SQL Azure
    6. 11.6. Data Sync Best Practices
    7. 11.7. Conclusion
  16. 12. Performance Tuning
    1. 12.1. What's Different with SQL Azure
      1. 12.1.1. Methods and Tools
      2. 12.1.2. Coding Implications
    2. 12.2. Tuning Techniques
      1. 12.2.1. Dynamic Management Views
      2. 12.2.2. Connection Pooling
      3. 12.2.3. Execution Plans
      4. 12.2.4. Indexing
      5. 12.2.5. Indexed Views
      6. 12.2.6. Stored Procedures
      7. 12.2.7. Provider Statistics
      8. 12.2.8. Application Design
    3. 12.3. Conclusion
  17. A. Houston
    1. A.1. Launching Houston
    2. A.2. Using Houston
  18. B. SQL Azure Quick Reference
    1. B.1. Supported T-SQL
    2. B.2. Partially Supported T-SQL
    3. B.3. Unsupported T-SQL
    4. B.4. Supported Data Types

Product information

  • Title: Pro SQL Azure
  • Author(s): Scott Klein, Herve Roggero
  • Release date: October 2010
  • Publisher(s): Apress
  • ISBN: 9781430229612