Professional Microsoft SQL Server 2014 Administration
, by Jorgensen, Adam; Ball, Bradley; Wort, Steven; Loforte, Ross; Knight, Brian
- ISBN: 9781118859131 | 1118859138
- Cover: Paperback
- Copyright: 9/9/2014
Learn to take advantage of the opportunities offered by SQL Server 2014
Microsoft's SQL Server 2014 update means big changes for database administrators, and you need to get up to speed quickly because your methods, workflow, and favorite techniques will be different from here on out. The update's enhanced support of large-scale enterprise databases and significant price advantage mean that SQL Server 2014 will become even more widely adopted across the industry. The update includes new backup and recovery tools, new AlwaysOn features, and enhanced cloud capabilities. In-memory OLTP, Buffer Pool Extensions for SSDs, and a new Cardinality Estimator can improve functionality and smooth out the workflow, but only if you understand their full capabilities. Professional Microsoft SQL Server 2014 is your comprehensive guide to working with the new environment. Authors Adam Jorgensen, Bradley Ball, Ross LoForte, Steven Wort, and Brian Knight are the dream team of the SQL Server community, and they put their expertise to work guiding you through the changes.
- Improve oversight with better management and monitoring
- Protect your work with enhanced security features
- Upgrade performance tuning, scaling, replication, and clustering
- Learn new options for backup and recovery
Professional Microsoft SQL Server 2014 includes a companion website with sample code and efficient automation utilities, plus a host of tips, tricks, and workarounds that will make your job as a DBA or database architect much easier. Stop getting frustrated with administrative issues and start taking control. Professional Microsoft SQL Server 2014 is your roadmap to mastering the update and creating solutions that work.
Adam Jorgensen is the President of Pragmatic Works and the Executive Vice President of PASS with more than 15 years experience.
Bradley Ball is a MCITP 2005, 2008, and 2012 MCSE DBA with more than a decade of experience who is currently the Data Platform Management Lead with Pragmatic Works specializing in Data Platform solutions.
Steven Wort has been working with SQL Server since 1993, who joined Microsoft in 2000, and now works as an Architect in the CRM Service Engineering team.
Ross LoForte is a Technology Architect at the Microsoft Technology Center Chicago focused on Microsoft SQL Server solutions, with more than 20 years of business development, project management, and SQL experience.
Brian Knight is the founder of Pragmatic Works and co-founder of SQLServerCentral.com and JumpStartTV.com
Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.
INTRODUCTION xxxvii
CHAPTER 1: SQL SERVER 2014 ARCHITECTURE 1
SQL Server 2014 Ecosystem 1
New Important Features in 2014 2
Production DBA 2
Development DBA 3
Business Intelligence DBA and Developer 4
SQL Server Architecture 5
Database Files and Transaction Log 5
SQL Server Native Client 6
Standard System Databases 7
Schemas 9
Synonyms 10
Dynamic Management Objects 10
SQL Server 2014 Data Types 12
Editions of SQL Server 18
Edition Overview 18
Licensing 21
Summary 22
CHAPTER 2: INSTALLING SQL SERVER 2014 BEST PRACTICES 23
Planning the System 24
Hardware Options 24
Software and Install Options 29
Installing SQL Server 32
New Installs 32
Side-by-Side Installs 32
Upgrades 33
Attended Installations 33
Unattended Installs 37
Installing Analysis Services 43
Multidimensional and Data Mining Mode (UDM Mode) 44
Tabular Mode 46
Installing PowerPivot for SharePoint 47
Burning in the System 48
Post-Install Configuration 49
Configuring SQL Server Settings for Performance 49
tempdb 51
Configuring SQL Server Settings for Security 52
SQL Server Configuration Manager 54
Back It Up 54
Uninstalling SQL Server 54
Uninstalling Reporting Services 55
Uninstalling Analysis Services 55
Uninstalling the SQL Server Database Engine 55
Troubleshooting a Failed Install 55
Summary 56
CHAPTER 3: UPGRADING SQL SERVER 2014 BEST PRACTICES 57
Why Upgrade to SQL Server 2014? 57
Risk Mitigation—the Microsoft Contribution 58
Independent Software Vendors and SQL Community Contributions 59
Upgrading to SQL Server 2014 59
In-Place Upgrading 60
Side-by-Side Upgrade 62
In-Place Upgrade versus Side-by-Side Upgrade Considerations 62
Pre-Upgrade Steps and Tools 63
Pre-Upgrade Steps 63
Pre-Upgrade Tools 64
Backward Compatibility 72
Unsupported and Discontinued Features in SQL Server 2014 72
SQL Server 2014 Deprecated Database Features 74
Other SQL Server 2014 Changes Affecting Behavior 74
SQL Server Component Considerations 75
Upgrading Full-Text Catalog 75
Upgrading Reporting Services 75
Upgrading to 64-Bit 76
Post-Upgrade Checks 76
Summary 77
CHAPTER 4: MANAGING AND TROUBLESHOOTING THE DATABASE ENGINE 79
Configuration and Administration Tools 80
SQL Server Configuration Manager 80
Startup Parameters 82
Startup Stored Procedures 84
Partially Contained Databases 86
Troubleshooting Tools 87
Dedicated Administrator Connection 87
Rebuilding the System Databases 89
SQL Server Management Studio 90
Reports 90
Configuring SQL Server in SQL Server Management Studio 93
Filtering Objects 97
Error Logs 97
Activity Monitor 98
Monitoring Processes in T-SQL 104
sp_who and sp_who2 104
sys.dm_exec_connections 105
sys.dm_exec_sql_text 105
Multiserver Management 106
Central Management Servers and Server Groups 106
Trace Flags 107
Getting Help from Support 109
SQLDumper.exe 109
SQLDiag.exe 110
Summary 112
CHAPTER 5: AUTOMATING SQL SERVER 113
Maintenance Plans 114
Maintenance Plan Wizard 114
Maintenance Plan Designer 119
Automating SQL Server with SQL Server Agent 122
Jobs 123
Schedules 128
Operators 129
Alerts 133
SQL Server Agent Security 138
Service Account 138
Access to SQL Agent 138
SQL Server Agent Proxies 139
Configuring SQL Server Agent 143
General Properties 143
Advanced Properties 144
Alert System Properties 145
Job System Properties 146
Connection Properties 147
History Properties 147
Multiserver Administration 147
Using Token Replacement 148
Event Forwarding 150
Using WMI 151
Multiserver Administration—Using Master and Target Servers 152
Summary 154
CHAPTER 6: SERVICE BROKER IN SQL SERVER 2014 155
Asynchronous Messaging 155
SQL Service Broker Overview 157
SQL Server Service Broker versus Other Message Queues 158
Configuring SQL Server Service Broker 159
Setting Broker State 159
Message Types 161
Contracts 162
Queues 163
Services 165
Routes 166
Priorities 167
Conversation Groups 168
Using SQL Server Service Broker 169
Sending Messages 169
Receiving Messages 172
Sending Messages between Instances 174
External Activation 176
Log User Example 177
Summary 184
CHAPTER 7: SQL SERVER CLR INTEGRATION 185
Introduction to CLR 185
SQL Server as a .NET Runtime Host 187
Application Domains 187
T-SQL versus CLR 187
Enabling CLR Integration 188
Creating CLR Assemblies 189
The Non–Visual Studio Way 189
Using Microsoft SQL Server Data Tools 191
CLR Integration Security 193
Performance Monitoring 194
Windows System Monitor 194
Extended Events 196
Dynamic Management Views (DMVs) 197
CLR Integration Design Goals 197
Summary 198
CHAPTER 8: SECURING THE DATABASE INSTANCE 199
Authentication Types 200
SQL Authentication 200
Windows Authentication 201
SQL versus Windows Authentication 202
Logins and Users 202
Authorizing Securables 202
Server Securables 203
Database Securables 209
Permission Chains 210
Cross-Database Permission Chains 211
Row-Level Security 213
Summary 214
CHAPTER 9: IN-MEMORY OLTP 215
Using and Implementing In-Memory OLTP 215
Enabling In-Memory OLTP 216
In-Memory OLTP Structures 218
Records 218
Indexes 219
CPU Considerations 224
Virtualization Considerations 224
Memory Considerations 225
Managing Memory with Resource Governor 227
Creating Natively Compiled Stored Procedures 230
Overview of the Analyze, Migrate, and Report (ARM) Tool 233
Summary 246
CHAPTER 10: CONFIGURING THE SERVER FOR OPTIMAL PERFORMANCE 247
What Every DBA Needs to Know About Performance 248
The Performance Tuning Cycle 248
Configuration 250
Power Configuration 250
Instant Database File Initialization 252
Trace Flags 256
Defining Good Performance 256
Focus on What’s Most Important 257
What the Developer DBA Needs to Know About Performance 258
Users 258
SQL Statements 259
Data Usage Patterns 259
Database Schema 259
What the Production DBA Needs to Know About Performance 260
Optimizing the Server’s Hardware 261
Hardware Management 262
CPU 263
x64 263
Cache 264
Hyper-Threading 264
Multicore Terminology 265
Memory 266
Physical Memory 266
Physical Address Space 267
Virtual Memory Manager 267
The Page File 267
Page Faults 268
I/O 269
Network 270
Magnetic Disks 271
Solid State/Flash Drives 274
Storage Considerations 274
Summary 278
CHAPTER 11: OPTIMIZING SQL SERVER 2014 281
Application Optimization 281
Defining a Workload 282
The Silent Killer: I/O Problems 282
SQL Server I/O Process Model 282
Database File Placement 283
tempdb Considerations 284
SQL Server Internals and File Allocations 288
Table and Index Partitioning 290
Why Consider Partitioning? 290
Creating a Partition Function 292
Creating Filegroups 294
Creating a Partition Scheme 295
Data Compression 296
Row Compression 296
Page Compression 299
Estimating Space Savings 300
Monitoring Data Compression 302
Data Compression Considerations 303
Understanding SQL Server and CPUs 304
NUMA and Hot Add CPUs 305
Cache Coherency 306
Affinity Mask 307
Max Degree of Parallelism (MAXDOP) 307
Cost Threshold for Parallelism 308
Memory Considerations and Enhancements 310
Buffer Pool Extensions 310
Tuning SQL Server Memory 312
Data Locality 314
Max Server Memory 315
Resource Governor 316
The Basic Elements of Resource Governor 317
Using Resource Governor from SQL Server 2014 Management Studio 321
Monitoring Resource Governor 322
Summary 323
CHAPTER 12: MONITORING YOUR SQL SERVER 325
The Goal of Monitoring 326
Determining Your Monitoring Objectives 326
Establishing a Baseline 327
Comparing Current Metrics to the Baseline 327
Choosing the Appropriate Monitoring Tools 328
Performance Monitor 329
CPU Resource Counters 331
Disk Activity 332
Memory Usage 338
Performance Monitoring Tools 341
Monitoring Events 343
The Default Trace 345
system_health Session 346
SQL Trace 347
Event Notifications 351
SQL Server Extended Events 354
Monitoring with Dynamic Management Views and Functions 375
What’s Going on Inside SQL Server? 377
Viewing the Locking Information 380
Viewing Blocking Information 380
Index Usage in a Database 381
Indexes Not Used in a Database 382
View Queries Waiting for Memory Grants 383
Connected User Information 384
Filegroup Free Space 384
Query Plan and Query Text for Currently Running Queries 385
Memory Usage 385
Buffer Pool Memory Usage 385
Monitoring Logs 386
Monitoring the SQL Server Error Log 386
Monitoring the Windows Event Logs 387
SQL Server Standard Reports 387
System Center Advisor 387
Summary 390
CHAPTER 13: PERFORMANCE TUNING T-SQL 391
Overview of Query Processing 392
Generating Execution Plans 394
Statistics 394
SQL 2014 Query Optimization Improvements—The New Cardinality
Estimator 395
Using the New Cardinality Estimator 395
Identifying SQL Query Performance Tuning Issues 396
Monitoring Query Performance 396
What to Do When You Find a Slow-Performing Query 397
Generating the Query Plans 398
Reading the Query Plan 400
Data Access Operators in Query Plans 403
Table Scan 404
Clustered Index Scan 406
Nonclustered Index Scans 408
Connecting Access Operators 411
Join Operators 412
Nested Loop or Loop Join 412
Hash Join 413
Merge Join 415
Data Modification Query Plan 417
Query Processing on Partitioned Tables and Indexes 419
Partition-Aware Operations 419
Parallel Query Execution Strategy for Partitioned Objects 421
Analyzing Query Performance in a Production Environment 421
System Dynamic Management Views (DMVs) 422
Putting It All Together 423
Summary 424
CHAPTER 14: INDEXING YOUR DATABASE 425
What’s New for Indexes in SQL Server 2014 426
About Indexes and Partitioned Tables 430
Understanding Indexes 430
Creating Indexes 435
Using Partitioned Tables and Indexes 436
Index Maintenance 437
Monitoring Index Fragmentation 438
Cleaning Up Indexes 439
Improving Query Performance with Indexes 440
Database Tuning Advisor 445
Summary 448
CHAPTER 15: REPLICATION 449
Replication Overview 450
Replication Components 450
Replication Types 452
Replication Models 454
Single Publisher, One or More Subscribers 454
Multiple Publishers, Single Subscriber 454
Multiple Publishers Also Subscribing 456
Updating Subscriber 457
Peer-to-Peer 458
Implementing Replication 460
Setting Up Snapshot Replication 460
Setting Up Distribution 460
Implementing Snapshot Replication 464
Implementing Transactional and Merge Replication 479
Peer-to-Peer Replication 480
Setting Up Peer-to-Peer Replication 480
Configuring Peer-to-Peer Replication 481
Scripting Replication 484
Monitoring Replication 485
Replication Monitor 485
Performance Monitor 487
Replication DMVs 488
sp_replcounters 489
Summary 489
CHAPTER 16: CLUSTERING SQL SERVER 2014 491
Clustering and Your Organization 492
What Clustering Can Do 492
What Clustering Cannot Do 493
Choosing SQL Server 2014 Clustering for the Right Reasons 494
Alternatives to Clustering 494
Clustering: The Big Picture 497
How Clustering Works 497
Clustering Options 502
Upgrading SQL Server Clustering 504
Don’t Upgrade 504
Upgrading Your SQL Server 2014 Cluster In-Place 505
Rebuilding Your Cluster 505
Back-Out Plan 507
Which Upgrade Option Is Best? 507
Getting Prepared for Clustering 507
Preparing the Infrastructure 508
Preparing the Hardware 509
Clustering Windows Server 2012 R2 511
Before Installing Windows 2012 R2 Clustering 511
Installing Windows Server 2012 R2 Failover Clustering 512
Preparing Windows Server 2012 R2 for Clustering 516
Clustering Microsoft Distributed Transaction Coordinator 517
Clustering SQL Server 2014 518
Step-by-Step Instructions to Cluster SQL Server 519
Installing the Service Pack and Cumulative Updates 526
Test, Test, and Test Again 527
Managing and Monitoring the Cluster 529
Troubleshooting Cluster Problems 530
How to Approach Windows Failover Clustering Troubleshooting 530
Doing It Right the First Time 531
Gathering Information 531
Resolving Problems 532
Working with Microsoft 532
Summary 533
CHAPTER 17: BACKUP AND RECOVERY 535
Backup and Restore Enhancements 536
SQL Server Backup to URL 536
SQL Server Managed Backup to Windows Azure 536
Encryption for Backups 536
Overview of Backup and Restore 537
How Backup Works 537
Copying Databases 539
Backup Compression 553
Comparing Recovery Models 554
Choosing a Model 556
Switching Recovery Models 557
Backing Up History Tables 558
Permissions Required for Backup and Restore 559
Backing Up System Databases 559
Full-Text Backup 561
Verifying the Backup Images 561
How Restore Works 562
Preparing for Recovery 563
Recoverability Requirements 564
Data Usage Patterns 565
Maintenance Time Window 565
Other High-Availability Solutions 566
Developing and Executing a Backup Plan 567
Using SQL Server Management Studio 568
Database Maintenance Plans 572
Using Transact-SQL Backup Commands 576
Managing Backups 578
Backup and Restore Performance 578
Performing Recovery 579
Restore Process 579
SQL Server Management Studio Restore 583
T-SQL Restore Command 588
Restoring System Databases 588
Archiving Data 590
SQL Server Table Partitioning 590
Partitioned View 591
Summary 592
CHAPTER 18: SQL SERVER 2014 LOG SHIPPING 593
Log-Shipping Deployment Scenarios 593
Log Shipping to Create a Warm Standby Server 594
Log Shipping as a Disaster-Recovery Solution 595
Log Shipping as a Report Database Solution 596
Log-Shipping Architecture 597
Primary Server 598
Secondary Server 598
Monitor Server 598
Log-Shipping Process 599
System Requirements 599
Network 600
Identical Capacity Servers 600
Storage 600
Software 600
Deploying Log Shipping 601
Initial Configuration 601
Deploying with Management Studio 602
Deploying with T-SQL Commands 610
Monitoring and Troubleshooting 611
Monitoring with SQL 2014 Management Studio 612
Monitoring with Stored Procedures 613
Troubleshooting Approach 613
Managing Changing Roles 614
Synchronizing Dependencies 614
Switching Roles from the Primary to Secondary Servers 617
Switching Between Primary and Secondary Servers 619
Redirecting Clients to Connect to the Secondary Server 620
Database Backup Plan 621
Integrating Log Shipping with Other High-Availability Solutions 622
SQL Server 2014 Data Mirroring 622
Windows Failover Clustering 622
SQL Server 2014 Replication 623
Removing Log Shipping 624
Removing Log Shipping with Management Studio 624
Removing Log Shipping with T-SQL Commands 624
Log-Shipping Performance 625
Upgrading to SQL Server 2014 Log Shipping 626
Minimum Downtime Approach 626
With Downtime Approach 626
Deploy Log-Shipping Approach 627
Summary 627
CHAPTER 19: DATABASE MIRRORING 629
Overview of Database Mirroring 630
Operating Modes of Database Mirroring 631
Database Mirroring In Action 634
Preparing the Endpoints 635
Preparing the Database for Mirroring 640
Initial Synchronization between Principal and Mirror 641
Establishing the Mirroring Session 642
High-Safety Operating Mode Without Automatic Failover 643
High-Safety Operating Mode with Automatic Failover 643
High-Performance Operating Mode 645
Database Mirroring and SQL Server 2014 Editions 646
Database Mirroring Role Change 646
Automatic Failover 647
Manual Failover 650
Forced Failover 651
Monitoring Using Database Mirroring Monitor 652
Setting Thresholds on Counters and Sending Alerts 656
Preparing the Mirror Server for Failover 658
Hardware, Software, and Server Configuration 658
Database Availability During Planned Downtime 660
SQL Job Configuration on the Mirror 661
Client Redirection to the Mirror 662
Database Mirroring and Other High-Availability Solutions 663
Database Mirroring versus Clustering 663
Database Mirroring versus Transactional Replication 664
Database Mirroring versus Log Shipping 664
Database Mirroring versus Availability Groups 664
Database Snapshots 665
Summary 666
CHAPTER 20: INTEGRATION SERVICES ADMINISTRATION AND PERFORMANCE TUNING 667
A Tour of Integration Services 668
Integration Services Uses 668
The Main Parts of Integration Services 669
Project Management and Change Control 671
Administration of the Integration Services Service 671
An Overview of the Integration Services Service 672
Configuration 672
Event Logs 675
Monitoring Activity 676
Administration of Integration Services Packages in Package Deployment Model 677
Using Management Studio for Package Management 677
Deployment 679
Administration of Integration Services Packages in Project Deployment Model 683
Configuring the SSIS Catalog 683
Deploying Packages 685
Configuring Packages 687
Execution and Scheduling 690
Running Packages in SQL Server Data Tools 691
Running Packages with the SQL Server Import and Export Wizard 691
Running Packages with DTExec 691
Running Packages with DTExecUI (Package Deployment Model) 692
Running Packages with the Execute Package Tool (Project Deployment Model) 693
Scheduling Execution with SQL Server Agent 694
Running Packages with T-SQL 696
Applying Security to Integration Services 696
An Overview of Integration Services Security 696
Securing Packages in Package Deployment Model 697
Summary 699
CHAPTER 21: ANALYSIS SERVICES ADMINISTRATION AND PERFORMANCE TUNING 701
Tour of Analysis Services 702
MOLAP Components 703
Tabular Model Components 704
Analysis Services Architectural Components 704
Administering Analysis Services Server 705
Server Properties 706
Required Services 707
Analysis Services Scripting Language 708
Administering Analysis Services Databases 709
Deploying Analysis Services Databases 710
Processing Analysis Services Objects 712
Backing Up and Restoring Analysis Services Databases 717
Synchronizing Analysis Services Databases 721
Analysis Services Performance Monitoring and Tuning 722
Monitoring Analysis Services Events 722
Using Flight Recorder for After-the-Fact Analysis 723
Summary 723
CHAPTER 22: SQL SERVER REPORTING SERVICES ADMINISTRATION 725
SQL Server Reporting Services
Configuration Manager 726
The Service Account 728
The Web Service URL 729
Reporting Services Databases 731
The Report Manager URL 733
E-mail Settings 734
Execution Account 734
Encryption Keys 736
Scale-Out Deployment 737
The Report Execution Log 739
Report Manager 740
Managing Report Manager 740
Managing Reports 747
Summary 761
CHAPTER 23: SQL SERVER 2014 SHAREPOINT 2013 INTEGRATION 763
Components of Integration 763
PowerPivot 764
Reporting Services 766
Power View 768
Data Refresh 771
Using Data Connections in Excel 771
PerformancePoint Data Connections 776
Visio Services Data Refresh 778
PowerPivot Data Refresh 780
Summary 788
CHAPTER 24: SQL DATABASE ADMINISTRATION AND CONFIGURATION 789
Getting to Know Windows Azure SQL Database 789
SQL Database Architecture 790
Client Layer 791
Services Layer 791
Platform Layer 791
Infrastructure Layer 791
Understanding the Differences 791
Configuring SQL Database 792
Server and Database Provisioning 792
Throttling and Load Balancing 800
Configuring SQL Database Firewalls 801
Connecting to SQL Database 802
Administering SQL Database 804
Creating Logins and Users 805
Assigning Access Rights 807
Working with SQL Database 807
Backups with SQL Database 807
Object Explorer for SQL Database 809
What’s Missing in SQL Database 810
Summary 811
CHAPTER 25: ALWAYSON AVAILABILITY GROUPS 813
Architecture 814
Availability Group Replicas and Roles 815
Availability Modes 815
Types of Failover Supported 816
Allowing Read-Only Access to Secondary Replicas 817
Availability Group Example 818
Configure a New Availability Group 819
Configure an Existing Availability Group 829
Availability Group Failover Operation 833
Suspend an Availability Database 834
Resume an Availability Database 835
Client Application Connections 836
Active Secondary for Secondary Read-Only 837
Read-Only Access Behavior 837
Secondary Replica Client Connectivity 838
Performance 839
Backup on the Secondary Replica 841
Evaluate Backup Replicas Metadata 842
AlwaysOn Group Dashboard 843
Monitoring and Troubleshooting 845
Summary 847
INDEX 849
What is included with this book?
The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.
The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.
Please wait while the item is added to your bag...
