Table of Contents
Introduction xiii
Chapter 1 Getting started with Microsoft Azure SQL Database 1
Cloud computing: the concept 1
Instant dynamic provisioning 2
The Microsoft Azure cloud 2
Getting signed up for SQL Database 5
Creating a Microsoft account 5
Creating a Microsoft Azure subscription 7
Creating a server 8
Creating a SQL Database instance 13
Using the SQL Database management portal 15
Designing tables and relationships 17
Inserting data 21
Querying the database 24
Exploring additional portal capabilities 27
Summary 30
Chapter 2 Configuration and pricing 31
Using the Microsoft Azure platform management portal 31
Creating a new database 31
Setting firewall rules 35
Obtaining connection strings 38
Deleting a database 40
Using SQL Server Management Studio 40
Connecting to SQL Database 41
Creating a new database 43
Changing the database edition and maximum size 44
Deleting a database 44
Using PowerShell 44
Installing the Microsoft Azure PowerShell cmdlets 44
Using the PowerShell Integrated Scripting Environment 46
Configuring PowerShell for your Microsoft account 46
Creating a new server 47
Creating a new database 48
Deleting a database 50
Budgeting for SQL Database 50
SQL storage 50
Client bandwidth 51
Backup storage space 51
Backup storage bandwidth 52
Support 53
Optimizing your costs 54
Configuring the database edition and size 55
Summary 56
Chapter 3 Differences between SQL Server and Microsoft Azure SQL Database 57
Size limitations 58
Connection limitation 58
Unsupported features 59
Summary 62
Chapter 4 Migrating databases 63
Making the cases for data migration 63
Migrating data using Transact-SQL scripts 64
Setting up a local SQL Server database 64
Creating the T-SQL scripts 67
Generating T-SQL scripts 68
SQL Data-Tier Applications 70
Creating a Microsoft Azure Storage account 71
Exporting a BACPAC to Microsoft Azure Storage 74
Importing a BACPAC to Microsoft Azure SQL Database 77
SQL Server Bulk Copy (bcp) 80
Migrating Schema 81
Exporting data 83
Importing data 84
SQL Database Migration Wizard 86
Downloading the tool 87
Migrating a database 88
Summary 95
Chapter 5 Security and backup 97
Addressing major cloud concerns 97
Security responsibilities of the public cloud vendor 98
Shared security responsibilities 98
Security in Microsoft Azure 99
Securing SQL Database 100
Creating a SQL Database 100
Configuring SQL Database Firewall 101
Authenticating and authorizing users 105
Backing up SQL Database 112
Copying a database 112
Monitoring the progress of a database copy operation 113
Exporting a BACPAC 115
Importing a BACPAC 117
Scheduling BACPAC exports 120
Summary 122
Chapter 6 Cloud reporting 123
Creating a SQL Server Reporting services virtual machine 125
Creating the virtual machine from the image gallery 126
Configuring SSRS in the virtual machine 128
Opening firewell access to the report server 130
Creating the sample database 132
Using Roport Builder 135
Installing Report Builder 135
Creating a report using Report Builder 137
Using Visual Studio Report Server projects 150
Installing AdventureWorks2012 for SQL Database 152
Installing SSDT Business Intelligence for Visual Studio 2012 154
Creating report using Visual Studio 156
Implementing report security 170
Shutting down the SSRS virtual machine 171
Summary 171
Chapter 7 Microsoft Azure SQL Data Sync 173
Getting to know SQL Data Sync 173
Exporting data from SQL Server to SQL Database 175
Importing data from SQL Database to SQL Server 175
Sharing data between multiple locations 176
Scaling out 178
Creating the SQL Database 180
Working with SQL Data Sync 182
Creating a sync group 182
Creating sync rules 188
Running a manual sync 191
Establishing conflict resolution 196
Creating a automated sync schedule 200
Creating a local SQL Server database 202
Creating a sync agent 204
Pitfalls and best practices 215
Summary 216
Chapter 8 Designing and tuning for scalability and high performance 217
Achieving high performance in the cloud 218
Creating a RESTful web API 218
Creating the sample database 219
Creating a new solution 221
Creating an ASP.NET Web API project 222
Adding an Entity Framework Code First Web API controller 223
Testing the Wine Web API 228
Adding an ADO.NET Web API controller 230
Testing the Customer Web API 233
Managing SQL Database Connections 234
Opening late, closing early 234
Pooling connections 234
Recovering from connection faults 234
Adding the Transient Fault Handling Application Block 235
Using the Transient Fault Handling Application Block with ADO.NET 237
Using the Transient Fault Handling Application Block with Entity Framework 239
Reducing network latency 243
Keeping services close 243
Minimizing round trips 243
Effectively using SQL Database 244
Using the best storage service 244
Optimizing queries 245
Scaling up SQL Database 245
Partitioning data 250
Scaling out with functional partitions 250
Scaling out with shards 251
Summary 260
Chapter 9 Monitoring and management 261
Creating the sample database 261
Monitoring 263
Using the management portal 264
Microsoft Azure Service Dashboard 269
SQL Database management portal 270
Dynamic management views and functions 275
Programming the Service Management REST API 281
Summary 288
Chapter 10 Building cloud solutions 289
Creating the SQL Database 292
Extending the SQL Database 294
Creating a new solution 294
Creating a SQL Server Database project 295
Setting the target platform 296
Importing from SQL Database into the project 297
Adding a new column to the Wine table 300
Deploying the project to Microsoft Azure SQL Database 301
Creating the Order table 305
Creating stored procedures for the Order table 307
Creating the data access layer 312
Introducing the Entity Data Model 313
Creating the Data Access Layer project 314
Creating an Entity Data Model 315
Creating the website 321
Creating a ASP.NET web application project 321
Referencing the data access layer 323
Creating the user interface 324
Testing the website locally 328
Deploying the website to Microsoft Azure 331
Creating the ASP.NET Web API services 336
Adding a Web API controller 337
Testing the Web API 339
Deploying the Web API 340
Creating the Windows Phone application 341
Installing the Windows Phone SDK 8.0 341
Creating the Windows Phone Project 343
Adding Json.NET 343
Creating the App's main page 344
Testing the Windows Phone application 353
Index 357
