Pranab Mazumdar,

Sourabh Agarwal and

Amit Banerjee

Pro SQL Server on Microsoft Azure

Pranab Mazumdar

Bangalore, Karnataka, India

Sourabh Agarwal

Bangalore, Karnataka, India

Amit Banerjee

Bangalore, Karnataka, India

Any source code or other supplementary materials referenced by the author in this text are available to readers at www.apress.com . For detailed information about how to locate your book’s source code, go to www.apress.com/source-code/ . Readers can also access source code at SpringerLink in the Supplementary Material section for each chapter.

ISBN 978-1-4842-2082-5

e-ISBN 978-1-4842-2083-2

DOI 10.1007/978-1-4842-2083-2

Library of Congress Control Number: 2016949375

© Pranab Mazumdar, Sourabh Agarwal, Amit Banerjee 2016

Pro SQL Server on Microsoft Azure

Managing Director: Welmoed Spahr

Lead Editor: Celestin Suresh John

Technical Reviewer: Ravikanth Chaganti

Editorial Board: Steve Anglin, Pramila Balan, Laura Berendson, Aaron Black, Louise Corrigan, Jonathan Gennick, Robert Hutchinson, Celestin Suresh John, Nikhil Karkal, James Markham, Susan McDermott, Matthew Moodie, Natalie Pao, Gwenan Spearing

Coordinating Editor: Prachi Mehta

Copy Editor: Kezia Endsley

Compositor: SPi Global

Indexer: SPi Global

Artist: SPi Global

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/bulk-sales .

This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed.

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.

While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein.

Printed on acid-free paper

Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.

About the Technical Reviewer

Ravikanth is a Principal Engineer and the Lead Architect for Microsoft and VMware private and hybrid cloud solutions in the Enterprise Solutions Group at Dell Inc. He is a multi-year recipient of Microsoft’s Most Valuable Professional (MVP) award in Windows PowerShell. Ravikanth is the author of Windows PowerShell Desired State Configuration Revealed (Apress) and leads Bangalore PowerShell and Bangalore IT Pro user groups. He can be seen speaking regularly at local user group events and conferences in India and abroad about topics ranging from PowerShell to Azure Services.

A371128_1_En_BookFrontmatter_Fige_HTML.jpg

Acknowledgments

Pranab—There are so many people whom, without their support and encouragement, I could not have written this book. First and foremost, my parents (Maa and Baba). They have always been my pillar of strength. Thank you to my wife (Meenakshi) for being so supportive and helping me write this. She actually pushed me to go for it with my hectic days at work. My lovely little daughter (Preesha), who is my world; she actually let me write when it was probably time to play/spend time with her. Several others supported me unconditionally, including my elder sister (Rupa Chatterjee). Thank you for the faith you have in me; I am here at Microsoft because of you. Thanks also to my brother-in-law (Anindya Chatterjee), for motivating me and inspiring me when I needed him most. I would also like to thank my in-laws for believing that I could do this and supporting me. I would like to express my gratitude to all my mentors, colleagues, and friends at Microsoft, as well as all my managers for being supportive about this idea. A big thank you to the Apress team, including John, who encouraged me to write and Prachi, for being so flexible with the timeline. A special thanks to all the reviewers.

Sourabh—A special note of thanks to my wife Sharie, who was very encouraging and supportive of the long hours I put in during the course of the book. I would like to dedicate this book to my mentors, to my teachers for their invaluable lessons, and finally to the publishers for being accommodating and very supportive of our requests.

Amit—A special note of thanks to my wife for being supportive when I worked on this book after spending hours at the office. Without her, this book would not have been possible. She was the one who actually pushed me to write this book. I would like to dedicate my contribution to the book to my mom and dad, who always believed that nothing is impossible if you put your heart into it. And as always, my gratitude goes out to my mentors who have made it possible for me to learn about this ever-evolving product in a seamless manner. Last but not the least, thanks to Apress for being flexible with the schedule and super accommodating, which really helped us complete this book.

Contents

  1. Chapter 1:​ Introduction to Microsoft Azure
    1. Cloud Computing Overview
    2. Characteristics of Cloud Computing
    3. Service Models
      1. Platform as a Service
      2. Infrastructure as a Service
      3. Software as a Service
    4. Microsoft Azure
    5. Azure Services
      1. Compute Offerings
      2. Data Management Offerings
      3. Networking
      4. Developer Services
      5. Identity and Access
      6. Backup
    6. Summary
  2. Chapter 2:​ Azure Architecture
    1. The Azure Services
    2. The Compute
    3. The Storage
    4. The Network
    5. How It Works Together
    6. Update/​Upgrade Domain
    7. Fault Domain
    8. Deployment
      1. Classic Deployment Model
      2. Resource Manager Deployment Model
      3. Deployment Automation
    9. Summary
  3. Chapter 3:​ Microsoft Azure Storage
    1. Azure Storage Service
      1. Blob Storage
      2. Table Storage
      3. Queue Storage
      4. File Storage
      5. Design Decisions
    2. Azure Storage Architecture Internals
      1. Replication Engine
      2. Layers Within a Storage Stamp
      3. Maintaining Availability/​Consistency for Read Requests
      4. Load Balancing of Partition Layer
      5. Load Balancing of the DFS Layer
      6. Load Balancing of DFS Capacity
    3. Durability Offerings with Azure Storage
      1. Azure Premium Storage
      2. Inside Premium Storage
    4. Azure Storage Best Practices
      1. Performance Enhancement Using Blobs
      2. Performance Enhancement Using Tables
    5. Querying Data Best Practices
    6. Summary
  4. Chapter 4:​ Microsoft Azure Networking
    1. Networking Primer
      1. Site-to-Site Connections
      2. Point-to-Site Connections
      3. ExpressRoute
    2. Azure AD Connect
    3. Traffic Manager
    4. Virtual Private Network
      1. Load Balancer
      2. Azure DNS
    5. Summary
    6. Additional References
  5. Chapter 5:​ Deploying SQL Server on Azure VMs
    1. Deploying a Standalone SQL Server Instance
      1. Configuration Settings
    2. Automating the Automation
    3. Post Deployment
    4. Azure Resource Explorer
    5. Azure CLI
    6. Summary
  6. Chapter 6:​ SQL Hybrid Solutions
    1. Hybrid Model Snapshot
    2. Backups to Azure Storage
    3. SQL Server Files in Microsoft Azure Storage
    4. Smart Backup
    5. AlwaysOn Configuration on Azure VMs
    6. Summary
    7. Additional References
  7. Chapter 7:​ All About Performance
    1. Understanding the Virtual Machine Performance
      1. Compute
      2. Network
      3. Storage
      4. Data Disks
      5. Storage Spaces
      6. Tempdb
      7. Database Settings
      8. Service Account Privileges
      9. Backups
    2. Data Files on Azure Blobs
    3. Monitoring
    4. Operational Insights
    5. Cheat Sheet
    6. Summary
  8. Chapter 8:​ Azure SQL Database
    1. SQL Database Architecture
      1. The Tenant Ring
      2. The Control Ring
    2. Azure SQL Database Service Tiers
      1. Elastic Database Pool
      2. Service Tiers:​ Limits and Capabilities
    3. Management Tools
      1. Azure Portal
      2. SQL Server Management Studio
      3. SQL Server Data Tools (SSDT)
      4. Command-Line Utilities and REST APIs
    4. Azure SQL Database versus SQL Server on Azure VM
    5. Migrating to Azure SQL Database
      1. SQLPackage.​exe
      2. SQL Server Management Studio
      3. Performing the Database Migration
    6. Summary
  9. Chapter 9:​ Business Continuity and Security with Azure SQL Database
    1. Azure SQL Database:​ Business Continuity and Disaster Recovery
      1. Local Redundancy
      2. Point-in-Time Restore
      3. Geo-Restore
      4. Geo-Replication
      5. SQL Server Replication
    2. Azure SQL Database:​ Security and Auditing
      1. Firewall Administration
      2. Authentication and Authorization
      3. SQL Database Auditing
      4. SQL Database Threat Detection
      5. Encryption
    3. Summary
  10. Chapter 10:​ Azure SQL Database:​ Performance and Monitoring
    1. What Is a DTU?​
    2. Choosing a Performance Level
    3. Changing the Performance Level
      1. PowerShell to Change the Service Tier or Performance Level
      2. Using Azure Portal to Change the Service Tier or Performance Level
    4. Azure SQL Performance Optimization Features
      1. In-Memory Optimizations
      2. SQL Database Index Advisor
      3. SQL Database Query Performance Insights
    5. Monitoring SQL Database
      1. Using Azure Portal
      2. Using DMV and Catalog Views
      3. Using Extended Events
    6. Summary
  11. Index

About the Authors

About the Authors

A371128_1_En_BookFrontmatter_Figb_HTML.jpg

Pranab Mazumdar is currently working as an Escalation Engineer for the Microsoft Azure SQL Database and Azure SQL Data Warehouse. He will soon be an Embedded Escalation Engineer, working very closely and partnering with the Engineering team. Prior to aligning to the cloud side of the business, he was an Escalation Engineer with the SQL Server team in CSS/GBS, where he worked with the product team to fix bugs in the SQL Server product, thereby making SQL a better and preferred RDBMS. He has been working with Microsoft for close to 12 years, with specializations in SQL Server Engine performance, high availability, and disaster recovery. He has worked with many large corporations with very large and complex SQL deployments.

Apart from SQL, he also worked with operational Insight, formerly known as System Centre Advisor, migrating and helping create new sets of rules and validation processes. He holds a number of Microsoft certifications, including MCAD, MCSD, MCDBA, MSCE, MCTS, MCITP, and MCT. The latest one is his Azure certification. He likes to be connected to his customers and he has been a speaker at TechEd, GIDs, SQL Saturday, SQL Talks, and other community UG events.

A371128_1_En_BookFrontmatter_Figc_HTML.jpg

Sourabh Agarwal currently works as a Senior Premier Field Engineer for the Microsoft Enterprise Services Delivery team. During his decade-long stint at Microsoft, he has worked in different capacities and specializes in providing reactive and proactive consulting on SQL Server and related technologies to Microsoft Enterprise customers across business domains and geographies. His specializations include SQL Database Design, SQL Server performance optimization, HADR, Microsoft Azure, PowerShell Scripting, and Dimension Modeling/Data Warehouse designing.

A371128_1_En_BookFrontmatter_Figd_HTML.jpg

Amit Banerjee currently works as a Senior Program Manager for the Microsoft SQL Server Product Group (Tiger Team). He has a decade of experience with SQL Server-related environments. Prior to this role, he worked as a Senior Premier Field Engineer at Microsoft, specializing in proactive and advisory assistance for SQL Server environments. In the past, he worked for the SQL Server Support team in various capacities, including the Microsoft SQL Server Escalation Services team. This involved fixing/troubleshooting complex issues related to SQL Server over a varied range of environments, including deployments handling from 100 to 10 million users. He worked on SQL Server environments for leading corporations in various business domains by helping them address and rectify SQL related issues for mission- and business-critical applications. He has also contributed to various related tools, including SQL Nexus, SQL Server Backup Simulator, and SQLDIAG/PSSDIAG Configuration Manager and is also the co-author of Professional SQL Server 2012: Internals and Troubleshooting, published by Wrox Press.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset