Pro Sql Azure

300 Pages·2010·15.136 MB·English

by  Scott Klein, Herve Roggero (auth.)

Pro SQL Azure
(cid:2) (cid:2) (cid:2)
Scott Klein
Herve Roggero
Pro SQL Azure
Copyright © 2010 by Scott Klein and Herve Roggero
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-4302-2961-2
ISBN-13 (electronic): 978-1-4302-2962-9
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol
with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only
in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of
the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are
not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject
to proprietary rights.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Technical Reviewer: Fabio Claudio Ferracchiati
Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell,
Jonathan Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes,
Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft,
Matt Wade, Tom Welsh
Coordinating Editor: Anita Castro
Copy Editor: Tiffany Taylor
Compositor: Bytheway Publishing Services
Indexer: BIM Indexing & Proofreading Services
Artist: April Milne
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233 Spring Street,
6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail orders-ny@springer-
sbm.com, or visit www.springeronline.com.
For information on translations, please e-mail [email protected], or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our
Special Bulk Sales–eBook Licensing web page at www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have
any liability to any person or entity with respect to any loss or damage caused or alleged to be caused
directly or indirectly by the information contained in this work.
To my parents, Richard and Carolyn. —Scott
To my dear wife, Frederique. —Herve
Contents at a Glance
(cid:2) About the Author..................................................................................................xiii
(cid:2) About the Technical Reviewer.............................................................................xiv
(cid:2) Acknowledgments................................................................................................xv
(cid:2) Chapter 1: Getting Started with SQL Azure.............................................................1
(cid:2) Chapter 2: Design Considerations.........................................................................23
(cid:2) Chapter 3: Setting Up and Configuring.................................................................45
(cid:2) Chapter 4: Security...............................................................................................65
(cid:2) Chapter 5: Data Migration and Backup Strategies...............................................87
(cid:2) Chapter 6: Programming with SQL Azure...........................................................117
(cid:2) Chapter 7: OData with SQL Azure.......................................................................147
(cid:2) Chapter 8: Reporting Services with SQL Azure...................................................169
(cid:2) Chapter 9: Windows Azure and ASP.NET............................................................185
(cid:2) Chapter 10: Designing for High Performance.....................................................205
(cid:2) Chapter 11: SQL Azure Data Sync Services........................................................229
(cid:2) Chapter 12: Performance Tuning........................................................................245
(cid:2) Appendix A: Houston...........................................................................................263
(cid:2) Appendix B: SQL Azure Quick Reference............................................................267
(cid:2) Index...................................................................................................................277
iv
Contents
(cid:2) About the Authors................................................................................................xiii
(cid:2) About the Technical Reviewers...........................................................................xiv
(cid:2) Acknowledgments................................................................................................xv
(cid:2) Chapter 1: Getting Started with SQL Azure.............................................................1
Introduction to Cloud Computing.......................................................................................1

Who Is Doing What in the Cloud?.............................................................................................................1

Typical Cloud Services.............................................................................................................................2
Discovering the Microsoft Azure Platform.........................................................................2

Why Microsoft Azure?...............................................................................................................................3

About Geographic Locations.....................................................................................................................4

Storing Data in Azure................................................................................................................................5
SQL Azure Primer...............................................................................................................6

Registering for Azure................................................................................................................................7

Creating a Database in SQL Azure............................................................................................................8

Configuring the Firewall...........................................................................................................................9

Connecting with SQL Server Management Studio..................................................................................11

Creating Logins and Users......................................................................................................................15

Assigning Access Rights........................................................................................................................17

Understanding Billing for SQL Azure......................................................................................................18
Limitations in SQL Azure..................................................................................................19

Security..................................................................................................................................................19

Backups..................................................................................................................................................20

Objects....................................................................................................................................................20
v
(cid:2) CONTENTS

Miscellaneous.........................................................................................................................................21

Drivers and Protocols.............................................................................................................................21
Conclusion.......................................................................................................................22
(cid:2) Chapter 2: Design Considerations.........................................................................23
Design Factors.................................................................................................................23

Offsite Storage........................................................................................................................................23

High Availability......................................................................................................................................23

Performance...........................................................................................................................................24

SQL Data Sync Framework.....................................................................................................................26

Direct vs. Serviced Connections.............................................................................................................26

Pricing....................................................................................................................................................27

Security..................................................................................................................................................28

Review of Design Factors.......................................................................................................................28
Design Patterns...............................................................................................................29

Direct Connection...................................................................................................................................29

Smart Branching.....................................................................................................................................29

Transparent Branching...........................................................................................................................30

Sharding.................................................................................................................................................30

Offloading...............................................................................................................................................35

Aggregation............................................................................................................................................36

Mirroring.................................................................................................................................................36
Combining Patterns.........................................................................................................37

Transparent Branching + RWS...............................................................................................................37

Cascading Aggregation...........................................................................................................................38
Sample Design: Application SLA Monitoring....................................................................39

Pre-Azure Application Architecture........................................................................................................39

Azure Implementation............................................................................................................................40
Other Considerations.......................................................................................................41
vi
(cid:2) CONTENTS

Blob Data Stores.....................................................................................................................................41

Edge Data Caching.................................................................................................................................41

Data Encryption......................................................................................................................................42
Conclusion.......................................................................................................................43
(cid:2) Chapter 3: Setting Up and Configuring.................................................................45
Creating Your Azure Account...........................................................................................45
Managing Your Azure Projects.........................................................................................47
Azure Server Administration............................................................................................48

Server Information..................................................................................................................................49

Firewall Settings.....................................................................................................................................50

Databases...............................................................................................................................................51
Creating Databases, Logins, and Users...........................................................................54

Databases...............................................................................................................................................55

Logins and Users....................................................................................................................................55
Connecting to a SQL Azure Database..............................................................................56

Connecting Using ADO.NET....................................................................................................................57

Connecting from the Entity Framework..................................................................................................58
Conclusion.......................................................................................................................63
(cid:2) Chapter 4: Security...............................................................................................65
Overview..........................................................................................................................65

Confidentiality.........................................................................................................................................65

Integrity..................................................................................................................................................66

Availability..............................................................................................................................................66
Securing Your Data..........................................................................................................68

Encryption...............................................................................................................................................69

Hashing...................................................................................................................................................71

Certificates.............................................................................................................................................75
Access Control.................................................................................................................79
vii
(cid:2) CONTENTS

Authentication (AUTHN)..........................................................................................................................79

Authorization (AUTHZ).............................................................................................................................80

Firewall...................................................................................................................................................84
Compliance......................................................................................................................84
Conclusion.......................................................................................................................84
(cid:2) Chapter 5: Data Migration and Backup Strategies...............................................87
Migrating Databases and Data to SQL Azure...................................................................87

Generate and Publish Scripts Wizard.....................................................................................................88

SQL Server Integration Services.............................................................................................................98

Bcp.......................................................................................................................................................109
SQL Azure Backup Strategies........................................................................................113

Copying a Database..............................................................................................................................113

Knowing When a Copy Is Complete......................................................................................................114

Automating a Database Copy...............................................................................................................114

Maintaining a Backup History...............................................................................................................114
Conclusion.....................................................................................................................115
(cid:2) Chapter 6: Programming with SQL Azure...........................................................117
Application Deployment Factors....................................................................................117

On-Premise Application........................................................................................................................118

Azure-Hosted Application.....................................................................................................................118

Which to Choose?.................................................................................................................................119
Connecting to SQL Azure...............................................................................................119

ADO.NET...............................................................................................................................................120

ODBC....................................................................................................................................................124

Sqlcmd.................................................................................................................................................126
WCF Data Services.........................................................................................................132

Creating a Data Service........................................................................................................................132

Connecting the Service to the Model....................................................................................................133
viii
(cid:2) CONTENTS

Creating the Client Application.............................................................................................................135

Creating the User Interface...................................................................................................................137

Running the Application.......................................................................................................................139
Record Navigation in WCF Data Services......................................................................140

Disabling Internet Explorer’s Feed Reading View.................................................................................141

Viewing the Final Results.....................................................................................................................142
Azure Programming Considerations..............................................................................145
Conclusion.....................................................................................................................146
(cid:2) Chapter 7: OData with SQL Azure.......................................................................147
OData Overview.............................................................................................................147

OData Producers...................................................................................................................................148

OData Consumers.................................................................................................................................151
Enabling OData on an Azure Database..........................................................................152

Getting Started at SQL Azure Labs.......................................................................................................152

Understanding Anonymous Access......................................................................................................154

Understanding the Access Control Service..........................................................................................154

Implementing Security Best Practices..................................................................................................155
Viewing OData-Enabled SQL Azure Data.......................................................................155
Viewing Data through an OData Consumer...................................................................157
Building Two OData Consumer Applications.................................................................158

Simple Demo App.................................................................................................................................159

Windows Mobile 7 Application.............................................................................................................160
Conclusion.....................................................................................................................166
(cid:2) Chapter 8: Reporting Services with SQL Azure...................................................169
Starting a SQL Azure–Based Report..............................................................................169
Creating the SQL Azure Data Source.............................................................................171
Creating the Report Design............................................................................................176
ix
(cid:2) CONTENTS
Deploying the Report.....................................................................................................177
Creating a Subreport......................................................................................................178

Adding the Subreport to the Main Report.............................................................................................181
Conclusion.....................................................................................................................183
(cid:2) Chapter 9: Windows Azure and ASP.NET............................................................185
Creating a Windows Azure Service................................................................................185
Creating a Windows Azure Project................................................................................190

Configuring Your Development Environment........................................................................................190

Creating Your First Visual Studio Cloud Project....................................................................................190

Connecting a GridView to SQL Azure....................................................................................................196
Deploying an ASP.NET Application in Windows Azure...................................................198
Conclusion.....................................................................................................................204
(cid:2) Chapter 10: Designing for High Performance.....................................................205
General Performance Concepts.....................................................................................205

Chatty vs. Chunky.................................................................................................................................205

Lazy Loading.........................................................................................................................................205

Caching.................................................................................................................................................206

Asynchronous User Interface................................................................................................................206

Parallel Processing...............................................................................................................................207

Shards..................................................................................................................................................207

Coding Strategies Summary.................................................................................................................207
Building a Shard............................................................................................................208

Designing the Shard Library Object......................................................................................................209

Managing Database Connections.........................................................................................................210

Reading Using the Shard......................................................................................................................213

Caching.................................................................................................................................................215

Updating and Deleting Records in the Shard........................................................................................216

Adding Records to the Shard................................................................................................................218
x