SQL Server 2016 High Availability Unleashed / Edition 1

by Paul Bertucci, Raju Shreewastava

Overview

Book + Content Update Program

SQL Server 2016 High Availability Unleashed provides start-to-finish coverage of SQL Server’s powerful high availability (HA) solutions for your traditional on-premise databases, cloud-based databases (Azure or AWS), hybrid databases (on-premise coupled with the cloud), and your emerging Big Data solutions.

This complete guide introduces an easy-to-follow, formal HA methodology that has been refined over the past several years and helps you identity the right HA solution for your needs. There is also additional coverage of both disaster recovery and business continuity architectures and considerations. You are provided with step-by-step guides, examples, and sample code to help you set up, manage, and administer these highly available solutions. All examples are based on existing production deployments at major Fortune 500 companies around the globe.

This book is for all intermediate-to-advanced SQL Server and Big Data professionals, but is also organized so that the first few chapters are great foundation reading for CIOs, CTOs, and even some tech-savvy CFOs.

• Learn a formal, high availability methodology for understanding and selecting the right HA solution for your needs
• Deep dive into Microsoft Cluster Services
• Use selective data replication topologies
• Explore thorough details on AlwaysOn and availability groups
• Learn about HA options with log shipping and database mirroring/ snapshots
• Get details on Microsoft Azure for Big Data and Azure SQL
• Explore business continuity and disaster recovery
• Learn about on-premise, cloud, and hybrid deployments
• Provide all types of database needs, including online transaction processing, data warehouse and business intelligence, and Big Data
• Explore the future of HA and disaster recovery

In addition, this book is part of InformIT’s exciting Content Update Program, which provides content updates for major technology improvements! As significant updates are made to SQL Server, sections of this book will be updated or new sections will be added to match the updates to the technologies. As updates become available, they will be delivered to you via a free Web Edition of this book, which can be accessed with any Internet connection. To learn more, visit informit.com/cup.

How to access the Web Edition: Follow the instructions inside to learn how to register your book to access the FREE Web Edition.

Product Details

ISBN-13:	9780672337765
Publisher:	Pearson Education
Publication date:	07/25/2017
Series:	Unleashed
Pages:	432
Product dimensions:	7.10(w) x 9.00(h) x 1.00(d)

About the Author

Paul Bertucci is the founder of Data by Design (www.dataXdesign.com) a database consulting firm with offices in the United States and Paris, France. He has more than 30 years of experience with database design, data modeling, data architecture, data replication, performance and tuning, distributed data systems, big data/Hadoop, data integration, high availability, disaster recovery/business continuity, master data management/data quality, and system architectures for numerous Fortune 500 companies, including Intel, Coca-Cola, Symantec, Autodesk, Apple, Toshiba, Lockheed, Wells Fargo, Merrill-Lynch, Safeway, Texaco, Charles Schwab, Wealth Front, Pacific Gas and Electric, Dayton Hudson, Abbott Labs, Cisco Systems, Sybase, and Honda, to name a few. He has written numerous articles, company and international data standards, and high-profile courses such as “Performance and Tuning” and “Physical Database Design” for Sybase and “Entity Relationship Modeling” courses for Chen & Associates (Dr. Peter P. Chen). Other Sams books that he has authored include the highly popular Microsoft SQL Server Unleashed series (SQL Server 2000, 2005, 2008 R2, 2012, and 2014), ADO.NET in 24 Hours, and Microsoft SQL Server High Availability.

He has deployed numerous traditional database systems with MS SQL Server, Sybase, DB2, and Oracle database engines, big data databases with Hadoop, and non-SQL databases (value pair) such as Oracle’s NoSQL and Cassandra NoSQL. He has designed/architected several commercially available tools in the database, data modeling, performance and tuning, data integrity, data integration, and multidimensional planning spaces.

Paul is also an experienced leader of global enterprise architecture teams for multi-billion dollar companies and lead global teams in data warehousing/BI, big data, master data management, identity management, enterprise application integration, and collaboration systems. He has held positions such as chief data architect for Symantec, chief architect and director of Shared Services for Autodesk, CTO for Diginome, and CTO for both LISI and PointCare. Paul speaks regularly at many conferences and gatherings worldwide, such as SQL Saturday’s, Ignite, TechEd, MDM Summit, Oracle World, Informatica World, SRII, MIT Chief Data Officer symposium, and many others.

Paul received his formal education in computer science and electrical engineering from UC Berkeley (Go, Bears!). He lives in the beautiful Pacific Northwest (Oregon) with the three children who still live at home (Donny, Juliana, and Nina) and lives near the other two, “working” adult children, Marissa and Paul Jr., who live in Portland.

Paul can be reached at [e-mail].

Table of Contents

Introduction xvii
Part I: Understanding High Availability
1 Understanding High Availability 1
Overview of High Availability 1
Calculating Availability 6
Availability Example: A 24×7×365 Application 6
The Availability Continuum 8
Availability Variables 10
General Design Approach for Achieving High Availability 13
Development Methodology with High Availability Built In 14
Assessing Existing Applications 16
What Is a Service Level Agreement? 17
High Availability Business Scenarios 18
An Application Service Provider 18
Worldwide Sales and Marketing—Brand Promotion 19
Investment Portfolio Management 20
Call-Before-You Dig Call Center 20
Microsoft Technologies That Yield High Availability 21
Summary 22
2 Microsoft High Availability Options 23
Getting Started with High Availability 23
Creating a Fault-Tolerant Disk: RAID and Mirroring 26
Increasing System Availability with RAID 27
Mitigating Risk by Spreading Out Server Instances 33
Microsoft Options for Building an HA Solution 35
Windows Server Failover Clustering (WSFC) 36
SQL Clustering 37
AlwaysOn Availability Groups 39
Data Replication 40
Log Shipping 41
Database Snapshots 42
Microsoft Azure Options and Azure SQL Databases 43
Application Clustering 45
Summary 46

Part II: Choosing the Right High Availability Approaches
3 Choosing High Availability 47
A Four-Step Process for Moving Toward High Availability 47
Step 1: Launching a Phase 0 HA Assessment 49
Resources for a Phase 0 HA Assessment 49
The Phase 0 HA Assessment Tasks 49
Step 2: Gauging HA Primary Variables 52
Step 3: Determining the Optimal HA Solution 53
A Hybrid High Availability Selection Method 53
Step 4: Justifying the Cost of a Selected High Availability Solution 75
ROI Calculation 75
Adding HA Elements to Your Development Methodology 76
Summary 76

Part III: Implementing High Availability
4 Failover Clustering 79
Variations of Failover Clustering 80
How Clustering Works 81
Understanding WSFC 82
Extending WSFC with NLB 86
How WSFC Sets the Stage for SQL Server Clustering and AlwaysOn 87
Installing Failover Clustering 89
A SQL Clustering Configuration 94
An AlwaysOn Availability Group Configuration 95
Configuring SQL Server Database Disks 96
Summary 97
5 SQL Server Clustering 99
Installing SQL Server Clustering Within WSFC 100
Potential Problems to Watch Out for with SQL Server Failover Clustering 113
Multisite SQL Server Failover Clustering 114
Scenario 1: Application Service Provider with SQL Server Clustering 114
Summary 117
6 SQL Server AlwaysOn and Availability Groups 119
AlwaysOn and Availability Groups Use Cases 119
Windows Server Failover Clustering 120
AlwaysOn Failover Clustering Instances 120
AlwaysOn and Availability Groups 122
Combining Failover with Scale-out Options 125
Building a Multinode AlwaysOn Configuration 125
Verifying SQL Server Instances 126
Setting Up Failover Clustering 126
Preparing the Database 129
Enabling AlwaysOn HA 129
Backing Up the Database 130
Creating the Availability Group 131
Selecting the Databases for the Availability Group 132
Identifying the Primary and Secondary Replicas 133
Synchronizing the Data 135
Setting Up the Listener 138
Connecting Using the Listener 141
Failing Over to a Secondary 141
Dashboard and Monitoring 143
Scenario 3: Investment Portfolio Management with AlwaysOn and Availability Groups 145
Summary 148
7 SQL Server Database Snapshots 149
What Are Database Snapshots? 150
Copy-on-Write Technology 154
When to Use Database Snapshots 155
Reverting to a Snapshot for Recovery Purposes 155
Safeguarding a Database Prior to Making Mass Changes 157
Providing a Testing (or Quality Assurance) Starting Point (Baseline) 157
Providing a Point-in-Time Reporting Database 158
Providing a Highly Available and Offloaded Reporting Database from a Database Mirror 159
Setup and Breakdown of a Database Snapshot 160
Creating a Database Snapshot 161
Breaking Down a Database Snapshot 165
Reverting to a Database Snapshot for Recovery 166
Reverting a Source Database from a Database Snapshot 166
Using Database Snapshots with Testing and QA 167
Security for Database Snapshots 168
Snapshot Sparse File Size Management 168
Number of Database Snapshots per Source Database 168
Adding Database Mirroring for High Availability 168
What Is Database Mirroring? 169
When to Use Database Mirroring 171
Roles of the Database Mirroring Configuration 171
Playing Roles and Switching Roles 172
Database Mirroring Operating Modes 172
Setting Up and Configuring Database Mirroring 173
Getting Ready to Mirror a Database 174
Creating the Endpoints 176
Granting Permissions 178
Creating the Database on the Mirror Server 178
Identifying the Other Endpoints for Database Mirroring 180
Monitoring a Mirrored Database Environment 182
Removing Mirroring 185
Testing Failover from the Principal to the Mirror 187
Client Setup and Configuration for Database Mirroring 189
Setting Up DB Snapshots Against a Database Mirror 190
Reciprocal Principal/Mirror Reporting Configuration 190
Scenario 3: Investment Portfolio Management with DB Snapshots and DB Mirroring 192
Summary 194
8 SQL Server Data Replication 195
Data Replication for High Availability 195
Snapshot Replication 196
Transactional Replication 196
Merge Replication 196
What Is Data Replication? 198
The Publisher, Distributor, and Subscriber Metaphor 199
Publications and Articles 200
Filtering Articles 201
Replication Scenarios 205
Central Publisher 206
Central Publisher with a Remote Distributor 207
Subscriptions 208
Pull Subscriptions 208
Push Subscriptions 209
The Distribution Database 209
Replication Agents 210
The Snapshot Agent 210
The Log Reader Agent 212
The Distribution Agent 212
The Miscellaneous Agents 213
User Requirements Driving the Replication Design 213
Setting Up Replication 214
Enabling a Distributor 215
Publishing 217
Creating a Publication 217
Creating a Subscription 220
Switching Over to a Warm Standby (Subscriber) 226
Scenarios That Dictate Switching to the Warm Standby 226
Switching Over to a Warm Standby (the Subscriber) 227
Turning the Subscriber into a Publisher (if Needed) 227
Monitoring Replication 227
SQL Statements 228
SQL Server Management Studio 228
The Windows Performance Monitor and Replication 230
Backup and Recovery in a Replication Configuration 231
Scenario 2: Worldwide Sales and Marketing with Data Replication 233
Summary 236
9 SQL Server Log Shipping 237
Poor Man’s High Availability 237
Data Latency and Log Shipping 238
Design and Administration Implications of Log Shipping 239
Setting Up Log Shipping 240
Before Creating Log Shipping 241
Using the Database Log Shipping Task 242
When the Source Server Fails 252
Scenario 4: Call Before Digging with Log Shipping 252
Summary 255
10 High Availability Options in the Cloud 257
A High Availability Cloud Nightmare 258
HA Hybrid Approaches to Leveraging the Cloud 259
Extending Your Replication Topology to the Cloud 260
Extending Log Shipping to the Cloud for Additional HA 262
Creating a Stretch Database to the Cloud for Higher HA 264
Using AlwaysOn and Availability Groups to the Cloud 265
Using AlwaysOn and Availability Groups in the Cloud 267
Using Azure SQL Database for HA in the Cloud 268
Using Active Geo Replication 270
HA When Using Azure Big Data Options in the Cloud 271
Summary 271
11 High Availability and Big Data Options 273
Big Data Options for Azure 274
HDInsight 276
Machine Learning Web Service 276
Stream Analytics 276
Cognitive Services 276
Data Lake Analytics 277
Data Lake Store 277
Data Factory 278
Power BI Embedded 278
Microsoft Azure Data Lake Services 278
HDInsight Features 279
Using NoSQL Capabilities 279
Real-Time Processing 280
Spark for Interactive Analysis 280
R for Predictive Analysis and Machine Learning 280
Azure Data Lake Analytics 281
Azure Data Lake Store 282
High Availability of Azure Big Data 283
Data Redundancy 283
High Availability Services 285
How to Create a Highly Available HDInsight Cluster 285
Accessing Your Big Data 295
The Seven-Step Big Data Journey from Inception to Enterprise Scale 297
Other Things to Consider for Your Big Data Solution 299
Azure Big Data Use Cases 300
Use Case 1: Iterative Exploration 300
Use Case 2: Data Warehouse on Demand 300
Use Case 3: ETL Automation 301
Use Case 4: BI Integration 301
Use Case 5: Predictive Analysis 301
Summary 301
12 Hardware and OS Options for High Availability 303
Server HA Considerations 304
Failover Clustering 304
Networking Configuration 306
Clustered Virtual Machine Replication 307
Virtualization Wars 307
Backup Considerations 308
Integrated Hypervisor Replication 310
VM Snapshots 310
Disaster Recovery as a Service (DRaaS) 311
Summary 311
13 Disaster Recovery and Business Continuity 313
How to Approach Disaster Recovery 314
Disaster Recovery Patterns 316
Recovery Objectives 321
A Data-centric Approach to Disaster Recovery 322
Microsoft Options for Disaster Recovery 323
Data Replication 323
Log Shipping 325
Database Mirroring and Snapshots 326
Change Data Capture 327
AlwaysOn and Availability Groups 328
Azure and Active Geo Replication 330
The Overall Disaster Recovery Process 330
The Focus of Disaster Recovery 331
Planning and Executing Disaster Recovery 338
Have You Detached a Database Recently? 339
Third-Party Disaster Recovery Alternatives 339
Disaster Recovery as a Service (DRaaS) 340
Summary 340
14 Bringing HA Together 341
Foundation First 341
Assembling Your HA Assessment Team 343
Setting the HA Assessment Project Schedule/Timeline 344
Doing a Phase 0 High Availability Assessment 345
Step 1: Conducting the HA Assessment 346
Step 2: Gauging HA Primary Variables 348
High Availability Tasks Integrated into Your Development Life Cycle 350
Selecting an HA Solution 352
Determining Whether an HA Solution Is Cost-Effective 354
Summary 357
15 Upgrading Your Current Deployment to HA 359
Quantifying Your Current Deployment 360
Scenario 1 Original Environment List 361
Deciding What HA Solution You Will Upgrade To 363
Scenario 1 Target HA Environment List 365
Planning Your Upgrade 367
Doing Your Upgrade 368
Testing Your HA Configuration 369
Monitoring Your HA Health 370
Summary 372
16 High Availability and Security 373
The Security Big Picture 374
Using Object Permissions and Roles 376
Object Protection Using Schema-Bound Views 377
Ensuring Proper Security for HA Options 380
SQL Clustering Security Considerations 380
Log Shipping Security Considerations 381
Data Replication Security Considerations 383
Database Snapshots Security Considerations 384
AlwaysOn Availability Group Security Considerations 384
SQL Server Auditing 385
General Thoughts on Database Backup/Restore, Isolating SQL Roles, and Disaster Recovery Security Considerations 388
Summary 389
17 Future Direction of High Availability 391
High Availability as a Service (HAaaS) 391
100% Virtualization of Your Platforms 392
Being 100% in the Cloud 394
Advanced Geo Replication 395
Disaster Recovery as a Service? 396
Summary 397
Conclusion 398
Index 399