Pro SQL Azure (eBook)

Scott Klein is a Microsoft SQL Server "Most Valuable Professional" and independent consultant specializing in SQL Server performance and business intelligence. Scott is the author of several books, including Professional SQL Server 2005 XML, Professional LINQ, and Pro ADO.NET 4.0 Entity Framework. He was a contributing author to Pro SQL Server 2008 Relational Database Design and Implementation, as well as to the Microsoft SQL Server 2008 Bible. Scott has written many articles for the SQL Server Standard magazine. He holds the MCDBA, MCSD, and MCSE certifications. He is heavily involved in the south Florida community, running two SQL Server user groups and the South Florida SQL Saturday events. Scott also speaks frequently at user groups across south Florida. Scott has nearly 20 years working with SQL Server and consulting in small to enterprise-size environments.

Pro SQL Azure 1
Copyright Page 2
Dedication Page 3
Contents at a Glance 4
Contents 5
About the Authors 13
About the Technical Reviewer 14
Acknowledgments 15
CHAPTER 1 Getting Started with SQL Azure 16
Introduction to Cloud Computing 16
Who Is Doing What in the Cloud? 16
Typical Cloud Services 17
Discovering the Microsoft Azure Platform 17
Why Microsoft Azure? 18
About Geographic Locations 19
Storing Data in Azure 20
SQL Azure Primer 21
Registering for Azure 22
Creating a Database in SQL Azure 23
Configuring the Firewall 24
Connecting with SQL Server Management Studio 26
Creating Logins and Users 30
Assigning Access Rights 32
Understanding Billing for SQL Azure 33
Limitations in SQL Azure 34
Security 34
Backups 35
Objects 35
Miscellaneous 36
Drivers and Protocols 36
Conclusion 37
CHAPTER 2 Design Considerations 38
Design Factors 38
Offsite Storage 38
High Availability 38
Performance 39
SQL Data Sync Framework 41
Direct vs. Serviced Connections 41
Pricing 42
Security 43
Review of Design Factors 43
Design Patterns 44
Direct Connection 44
Smart Branching 44
Transparent Branching 45
Sharding 45
Offloading 50
Aggregation 51
Mirroring 51
Combining Patterns 52
Transparent Branching + RWS 52
Cascading Aggregation 53
Sample Design: Application SLA Monitoring 54
Pre-Azure Application Architecture 54
Azure Implementation 55
Other Considerations 56
Blob Data Stores 56
Edge Data Caching 56
Data Encryption 57
Conclusion 58
CHAPTER 3 Setting Up and Configuring 59
Creating Your Azure Account 59
Managing Your Azure Projects 61
Azure Server Administration 62
Server Information 63
Firewall Settings 64
Databases 65
Creating Databases, Logins, and Users 68
Databases 69
Logins and Users 69
Connecting to a SQL Azure Database 70
Connecting Using ADO.NET 71
Connecting from the Entity Framework 72
Conclusion 77
CHAPTER 4 Security 79
Overview 79
Confidentiality 79
Integrity 80
Availability 80
Securing Your Data 82
Encryption 83
Hashing 85
Certificates 89
Access Control 93
Authentication (AUTHN) 93
Authorization (AUTHZ) 94
Firewall 98
Compliance 98
Conclusion 99
CHAPTER 5 Data Migration and Backup Strategies 100
Migrating Databases and Data to SQL Azure 100
Generate and Publish Scripts Wizard 101
SQL Server Integration Services 111
Bcp 122
SQL Azure Backup Strategies 126
Copying a Database 126
Knowing When a Copy Is Complete 127
Automating a Database Copy 127
Maintaining a Backup History 127
Conclusion 128
CHAPTER 6 Programming with SQL Azure 129
Application Deployment Factors 129
On-Premise Application 130
Azure-Hosted Application 130
Which to Choose? 131
Connecting to SQL Azure 131
ADO.NET 132
ODBC 136
Sqlcmd 138
WCF Data Services 144
Creating a Data Service 144
Connecting the Service to the Model 145
Creating the Client Application 147
Creating the User Interface 149
Running the Application 151
Record Navigation in WCF Data Services 152
Disabling Internet Explorer’s Feed Reading View 153
Viewing the Final Results 154
Azure Programming Considerations 157
Conclusion 158
CHAPTER 7 OData with SQL Azure 159
OData Overview 159
OData Producers 160
OData Consumers 163
Enabling OData on an Azure Database 164
Getting Started at SQL Azure Labs 164
Understanding Anonymous Access 166
Understanding the Access Control Service 166
Implementing Security Best Practices 167
Viewing OData-Enabled SQL Azure Data 167
Viewing Data through an OData Consumer 169
Building Two OData Consumer Applications 170
Simple Demo App 171
Windows Mobile 7 Application 172
Conclusion 178
CHAPTER 8 Reporting Services with SQL Azure 180
Starting a SQL Azure–Based Report 180
Creating the SQL Azure Data Source 182
Creating the Report Design 187
Deploying the Report 188
Creating a Subreport 189
Adding the Subreport to the Main Report 192
Conclusion 194
CHAPTER 9 Windows Azure and ASP.NET 195
Creating a Windows Azure Service 195
Creating a Windows Azure Project 200
Configuring Your Development Environment 200
Creating Your First Visual Studio Cloud Project 200
Connecting a GridView to SQL Azure 206
Deploying an ASP.NET Application in Windows Azure 208
Conclusion 214
CHAPTER 10 Designing for High Performance 215
General Performance Concepts 215
Chatty vs. Chunky 215
Lazy Loading 215
Caching 216
Asynchronous User Interface 216
Parallel Processing 217
Shards 217
Coding Strategies Summary 217
Building a Shard 218
Designing the Shard Library Object 219
Managing Database Connections 220
Reading Using the Shard 223
Caching 225
Updating and Deleting Records in the Shard 226
Adding Records to the Shard 228
Managing a Shard 230
Managing Exceptions 230
Managing Performance 232
Working with Partial Shards 235
Managing Transaction Consistency 236
Managing Foreign Key Constraints 236
Creating Vertical Partition Shards 237
Conclusion 238
CHAPTER 11 SQL Azure Data Sync Services 239
Understanding the Data Sync Service 239
Why the Need? 239
The Basic Scenario 240
Configuring Synchronization 241
Agreeing to the Terms of Service 241
Creating a Sync Group 241
Defining the Hub and Member Databases 244
Selecting Tables to be Synchronized 245
Modifying a Sync Group 246
Manually Synchronizing 246
Looking at the Database Changes 247
Looking at the Synchronized Data 249
Scheduling Data Synchronization 250
Synching an On-Premises Database with SQL Azure 250
Data Sync Best Practices 252
Conclusion 253
CHAPTER 12 Performance Tuning 254
What’s Different with SQL Azure 254
Methods and Tools 254
Coding Implications 255
Tuning Techniques 255
Dynamic Management Views 256
Connection Pooling 259
Execution Plans 259
Indexing 263
Indexed Views 267
Stored Procedures 268
Provider Statistics 269
Application Design 271
Conclusion 271
APPENDIX A Houston 272
Launching Houston 272
Using Houston 273
APPENDIX B SQL Azure Quick Reference 276
Supported T-SQL 276
Partially Supported T-SQL 278
Unsupported T-SQL 281
Supported Data Types 282
Index 285