Contents
Chapter 1: Working with Azure Database Services Platform .......................................... 1
Understanding the Service ....................................................................................................... 1
Connectivity Options ............................................................................................................... 3
Sizing & Tiers ......................................................................................................................... 5
Designing SQL Database ......................................................................................................... 8
Multi-tenancy ......................................................................................................................... 9
Index Design ........................................................................................................................ 13
Migrating an Existing Database .............................................................................................. 20
Preparing the Database ......................................................................................................... 20
Moving the Database ............................................................................................................ 22
Using SQL Database ............................................................................................................. 25
Design for Failures ............................................................................................................... 26
Split between Read/Write Applications ................................................................................... 29
Hot Features ........................................................................................................................ 34
Development Environments ................................................................................................... 37
Worst Practices .................................................................................................................... 39
Scaling SQL Database ........................................................................................................... 48
Managing Elasticity at Runtime .............................................................................................. 51
Pooling Different DBs Under the Same Price Cap ..................................................................... 53
Scaling Up ............................................................................................................................ 55
Governing SQL Database ....................................................................................................... 56
Security Options ................................................................................................................... 56
Backup options ..................................................................................................................... 63
Monitoring Options ................................................................................................................ 65
MySQL and PostgreSQL ......................................................................................................... 78
MySQL ................................................................................................................................. 79
PostgreSQL .......................................................................................................................... 81
Summary ............................................................................................................................. 82
Database Server Execution Options On Azure ......................................................................... 84
A Quick Overview of SQL Server 2017 .................................................................................... 85
Installation of SQL Server 2017 on Linux and Docker ............................................................... 87
SQL Server Operations Studio ................................................................................................ 91
Hybrid Cloud Features ........................................................................................................... 94
Azure Storage ....................................................................................................................... 95
Backup to Azure Storage ..................................................................................................... 104
SQL Server Stretched Databases .......................................................................................... 126
Migrate databases to Azure IaaS .......................................................................................... 132
Migrate a Database Using the Data-Tier Application Framework ............................................. 134
Run SQL Server on Microsoft Azure Virtual Machines ............................................................. 137
Why Choose SQL Server on Azure Virtual Machines ............................................................... 137
Azure Virtual Machines Sizes and Preferred Choice for SQL Server .......................................... 139
Embedded Features Available and Useful for SQL Server ....................................................... 145
Design for Storage on SQL Server in Azure Virtual Machines .................................................. 148
Considerations on High Availability and Disaster Recovery Options with SQL Server on Hybrid Cloud and Azure IaaS 152
Hybrid Cloud HA/DR Options ............................................................................................... 153
Azure only HA/DR Options .................................................................................................. 157
Summary .......................................................................................................................... 167
Understanding NoSQL ........................................................................................................ 169
Simpler Options ................................................................................................................. 172
Document-oriented NoSQL ................................................................................................. 173
NoSQL alternatives in Microsoft Azure ................................................................................. 175
Using Azure Storage Blobs .................................................................................................. 175
Understanding Containers and Access Levels ....................................................................... 176
Understanding Redundancy and Performance ...................................................................... 179
Understanding Concurrency ................................................................................................ 192
Understanding Access and Security ..................................................................................... 196
Using Azure Storage Tables ................................................................................................ 201
Planning and Using Table Storage ....................................................................................... 202
Understanding Monitoring ................................................................................................... 208
Using Azure Monitor ........................................................................................................... 215
Using Azure Redis Cache .................................................................................................... 216
Justifying the Caching Scenario ........................................................................................... 216
Understanding Features ...................................................................................................... 223
Understanding Management ............................................................................................... 233
Using Azure Search ............................................................................................................ 240
Using SQL to Implement Search .......................................................................................... 242
Understanding How to Start with Azure Search ..................................................................... 245
Planning Azure Search ........................................................................................................ 248
Implementing Azure Search ................................................................................................ 254
Summary ........................................................................................................................... 261
Azure Data Factory Introduction ........................................................................................... 263
Main Advantages of using Azure Data Factory ....................................................................... 265
Terminology ....................................................................................................................... 266
Azure Data Factory Administration ....................................................................................... 272
Designing Azure Data Factory Solutions ............................................................................... 272
Exploring Azure Data Factory Features using Copy Data ........................................................ 273
Anatomy of Azure Data Factory JSON Scripts ........................................................................ 288
Azure Data Factory Tools for Visual Studio ............................................................................ 297
Working with Data Transformation Activities ......................................................................... 301
Microsoft Data Management Gateway ................................................................................... 314
Considerations of Performance, Scalability and Costs ............................................................. 316
Copy Activities .................................................................................................................... 317
Costs ................................................................................................................................. 321
Azure Data Factory v2 (Preview) .......................................................................................... 322
Azure Data Factory v2 Key Concepts .................................................................................... 322
Summary ........................................................................................................................... 325
How Azure Data Lake Store and Analytics were Born ............................................................ 329
Azure Data Lake Store ........................................................................................................ 330
Key Concepts ..................................................................................................................... 330
Hadoop Distributed File System ........................................................................................... 332
Create an Azure Data Lake Store ......................................................................................... 333
Common Operations on Files in Azure Data Lake Store .......................................................... 336
Copy Data to Azure Data Lake Store ..................................................................................... 341
Considerations on Azure Data Lake Store Performance ........................................................... 361
Azure Data Lake Analytics .................................................................................................... 363
Key Concepts ...................................................................................................................... 363
Built on Apache YARN .......................................................................................................... 364
Tools for Managing ADLA and Authoring U-SQL Scripts .......................................................... 366
U-SQL Language ................................................................................................................. 371
Azure HDInsight .................................................................................................................. 391
Summary ............................................................................................................................ 392
Understanding the Need for Messaging ................................................................................. 394
Use Cases of Uni-Directional Messaging ................................................................................ 396
Using Service Bus ................................................................................................................ 399
Using Event Hubs ................................................................................................................ 409
Understanding Real-Time Analytics ....................................................................................... 418
Understanding Stream Analytics ........................................................................................... 419
Understanding AppInsights .................................................................................................. 422
Summary ............................................................................................................................ 425
Index ................................................................................................................................. 427
Introduction
Today's mission in IT is reducing the overall time-to-market and, at the same time, preserving project constraints like quality and control over costs. With the cloud revolution of the last ten years, we started (finally) to understand the benefit of value-added services implemented in most of the PaaS (Platform-as-a-Service) of the cloud ecosystem.
We got how a platform can give us much more control on the entire development process, by freeing resources that now can be focused on the business and the design. In many cases, choosing a PaaS solution is the best choice, especially for born-in-the-cloud projects; in some other cases, using a IaaS approach can be beneficial, either because
you are migrating from an existing on-premises solution, or because you need a more granular control on the service itself.
This book is around data, and gives you a wide range of possibilities to implement a data solution on Azure, from hybrid cloud up to PaaS services, where we will focus much more. Implementing a PaaS solution requires to cover in detail several aspects of the implementation, including migrating from existing solutions. The next six chapters try to tell the story of Data Services by presenting the alternatives and the actual scope of each one; 5 out of 6 of the chapters are about PaaS, while one of them, mainly focused on SQL Server features for cloud, is related to hybrid cloud and IaaS functionalities.
Chapter 1 (Working with Azure Database Services Platform) we deeply analyze the SQL Database services, trying to bring to the reader the authors' on-the-field experience of designing and managing it for years. We discuss the various SQL Database most important features, trying to propose approaches and solutions to real-world problems.
Chapter 2 (Working with SQL Server on Hybrid Cloud and Azure IaaS) we "downscale" to IaaS. Except for this, we discuss the huge power of SQL Server on VMs and the various scenarios we can address with it. We see how SQL Server can run in VMs and containers, on Linux and how it can be managed with cross-platform tools. But Chapter 2 is not only around SQL Server on VMs: it is around Hybrid Cloud also, mixed environments and complex scenarios of backup/replication, disaster recovery and high-availability.
Chapter 3 (Working with NoSQL alternatives) we want to turn tables on the typical discussion around NoSQL. We choose to not include Cosmos DB in the chapter, either to postpone the topic to a dedicated book, either to highlight how many NoSQL alternatives we have in Azure outside the classics. We center the discussion around Blobs, that are
often under-evaluated, around Tables and Redis to finally approach on Azure Search, one of the most promising managed search services in the cloud ecosystem.
Chapter 4 (Orchestrate data with Azure Data Factory) we discover orchestration of data. We want to emphasize the importance of data activities, in terms of movements, transformation and the modern addressing to the concepts we known as ETL for many years. With Data Factory, you will discover an emerging (and growing up) service to deal
with pipelines of data and even complex orchestration scenarios.
Chapter 5 (Working with Azure Data Lake Store and Azure Data Lake Analytics) we start to build foundations for the big data needs. We discover how Data Lake can help with storing, managing and analyzing unstructured data, stored in their native format while they are generated. We will learn this important lesson around big data: since we
are generating and storing today the data we are using and analyzing tomorrow, we need a platform service to build intelligence on it with minimal effort.
Chapter 6 (Working with In-Transit Data and Analytics) closes the book with a little introduction about messaging and, generally, the in-transit data, to learn how we can take advantage of ingestion to build run-time logics in addition to the most consolidated ones. Messaging is extremely important for several scenarios: almost every
distributed system may use messaging to decouple components and micro-services.
Once messaging is understood, we can apply the event-based reasoning to move some parts of the business rules before the data is written to the final, persistent data store. Eventually,we learn how to implement in-transit analytics.
We hope this can be a good cue to address how to approach data service in this promising momentum of cloud and Platform-as-a-Service. We know this book cannot be complete and exhaustive, but we tried to focus on some good points to discuss the various areas of data management we can encounter on a daily basis.
