Professional Microsoft SQL Server 2012 Administration

Professional: Microsoft® SQL Server® 2012 Administration......Page 7
About The Authors......Page 11
About The Technical Editors......Page 13
Credits......Page 15
Acknowledgments......Page 17
Contents......Page 21
 How This Book Is Structured......Page 39
 Source Code......Page 42
 P2P.wrox.com......Page 43
 SQL Server 2012 Ecosystem......Page 45
  Development DBA......Page 46
  Business Intelligence DBA and Developer......Page 47
  Database Files and Transaction Log......Page 48
  SQL Native Client......Page 49
  Standard System Databases......Page 50
  Synonyms......Page 52
  Dynamic Management Objects......Page 53
  SQL Server 2012 Data Types......Page 54
  Edition Overview......Page 61
  Licensing......Page 62
 Summary......Page 64
Chapter 2: Installing SQL Server 2012 Best Practices......Page 65
  Hardware Options......Page 66
  Software and Install Options......Page 71
 Installing SQL Server......Page 73
  Unattended Installs......Page 74
  Attended Installations......Page 80
 Installing Analysis Services......Page 84
  Multidimensional and Data Mining Mode (UDM Mode)......Page 85
  Tabular Mode......Page 86
 Installing PowerPivot for SharePoint......Page 87
 Post-Install Configuration......Page 89
  Configuring SQL Server Settings for Performance......Page 90
  tempdb......Page 91
  Configuring SQL Server Setting for Security......Page 93
  SQL Server Configuration Manager......Page 94
  Uninstalling Analysis Services......Page 95
 Summary......Page 96
 Why Upgrade to SQL Server 2012?......Page 97
  Independent Software Vendors and SQL Community Contributions......Page 98
  In-Place Upgrading......Page 99
  Side-by-Side Upgrade......Page 101
  Pre-Upgrade Steps......Page 102
  Pre-Upgrade Tools......Page 103
  SQL Server 2012 Deprecated Database Features......Page 111
  Upgrading Reporting Services......Page 112
  Poor Query Performance After Upgrade......Page 113
 Summary......Page 114
 Configuration and Administration Tools......Page 115
  SQL Server Configuration Manager......Page 116
  Startup Parameters......Page 117
  Startup Stored Procedures......Page 121
  Partially Contained Databases......Page 122
  Dedicated Administrator Connection......Page 123
  Rebuilding the System Databases......Page 125
  Reports......Page 126
  Configuring SQL Server in Management Studio......Page 129
  Error Logs......Page 134
  Activity Monitor......Page 135
  sp_who and sp_who2......Page 140
  sys.dm_exec_sql_text......Page 141
  Central Management Servers and Server Groups......Page 142
 Trace Flags......Page 143
  SQLDumper.exe......Page 145
  SQLDiag.exe......Page 146
 Summary......Page 148
Chapter 5: Automating SQL Server......Page 149
  Maintenance Plan Wizard......Page 150
  Maintenance Plan Designer......Page 153
 Automating SQL Server with SQL Server Agent......Page 155
  Jobs......Page 156
  Schedules......Page 161
  Operators......Page 162
  Alerts......Page 165
  Access to SQL Agent......Page 170
  SQL Server Agent Proxies......Page 171
 Configuring SQL Server Agent......Page 174
  General Properties......Page 175
  Advanced Properties......Page 176
  Job System Properties......Page 177
 Database Mail......Page 178
  Security......Page 179
  Configuration......Page 180
  Using Token Replacement......Page 184
  Using WMI......Page 187
  Multiserver Administration — Using Master and Target Servers......Page 189
 Summary......Page 190
 Asynchronous Messaging......Page 191
  SQL Server Service Broker Versus Other Message Queues......Page 192
  Enabling......Page 193
  Contracts......Page 195
  Queues......Page 196
  Services......Page 197
  Routes......Page 198
  Conversation Groups......Page 200
  Sending Messages......Page 201
  Receiving Messages......Page 204
  Sending Messages Between Databases......Page 205
  Sending Messages Between Instances......Page 206
  External Activation......Page 207
 Summary......Page 209
 Introduction to the CLR......Page 211
  SQL Server as a .NET Runtime Host......Page 213
  T-SQL versus CLR......Page 214
  Enabling CLR Integration......Page 215
  The Non-Visual Studio Way......Page 216
  Using Microsoft SQL Server Data Tools......Page 218
 Securing CLR......Page 220
  Windows System Monitor......Page 221
  SQL Profiler......Page 222
  Dynamic Management Views (DMVs)......Page 223
 Summary......Page 224
 Authentication Types......Page 225
  SQL Authentication......Page 226
  SQL Versus Windows Authentication......Page 227
 Authorizing Securables......Page 228
  Server Securables......Page 229
  Database Securables......Page 233
  Permission Chains......Page 234
  Cross Database Permission Chains......Page 235
 Row Level Security......Page 237
 Summary......Page 238
Chapter 9: Change Management......Page 241
 Creating Solutions and Projects......Page 242
  Creating a Connection......Page 243
 Policy-Based Management......Page 244
  Policy-Based Management Overview......Page 245
  Policy-Based Management Step by Step......Page 246
  Scripting Policy-Based Management......Page 253
  Policy-Based Management Implementation......Page 254
  Database Triggers......Page 256
  Server Triggers......Page 261
 Scripting Overview......Page 262
  sqlcmd......Page 263
  PowerShell......Page 267
 Data-Tier Applications......Page 269
  Version Tables......Page 273
 Summary......Page 275
Chapter 10: Configuring the Server For Optimal Performance......Page 277
  The Performance Tuning Cycle......Page 278
  Defining Good Performance......Page 279
  Focus on What's Most Important......Page 280
  SQL Statements......Page 281
 What the Production DBA Needs to Know About Performance......Page 282
  Optimizing the Server......Page 283
 CPU......Page 285
  Cache......Page 286
  Hyper-threading......Page 287
  Multicore......Page 288
  System Architecture......Page 290
  Physical Address Space......Page 292
  The Page File......Page 293
  Page Faults......Page 294
 I/O......Page 295
  Disks......Page 296
  Storage Considerations......Page 299
  Designing a Storage System......Page 301
  Large Storage System Considerations: SAN Systems......Page 306
  Server Configuration......Page 308
  Fragmentation......Page 313
 Summary......Page 315
 Application Optimization......Page 317
 The Silent Killer: I/O Problems......Page 318
  Database File Placement......Page 319
  tempdb Considerations......Page 320
 Table and Index Partitioning......Page 323
  Why Consider Partitioning?......Page 324
  Creating a Partition Function......Page 325
  Creating a Partition Scheme......Page 328
  Creating Tables and Indexes......Page 329
  Row Compression......Page 334
  Page Compression......Page 335
  Estimating Space Savings......Page 337
  Data Compression Considerations......Page 339
 CPU Considerations......Page 340
  Affinity Mask......Page 341
  Max Degree of Parallelism (MAXDOP)......Page 344
  Affinity I/O Mask......Page 345
  Tuning SQL Server Memory......Page 346
  64-bit Versions of SQL Server 2012......Page 349
  Data Locality......Page 350
  Index Creation Memory Option......Page 351
  Minimum Memory per Query......Page 352
  The Basic Elements of Resource Governor......Page 353
  Using Resource Governor from SQL Server 2012 Management Studio......Page 357
  Monitoring Resource Governor......Page 358
 Summary......Page 359
Chapter 12: Monitoring Your SQL Server......Page 361
  Establishing a Baseline......Page 362
 Choosing the Appropriate Monitoring Tools......Page 363
 Performance Monitor......Page 365
  CPU Resource Counters......Page 366
  Disk Activity......Page 368
  Memory Usage......Page 374
  Performance Monitoring Tools......Page 377
 Monitoring Events......Page 379
  The Default Trace......Page 381
  SQL Trace......Page 382
  Event Notifications......Page 396
  SQL Server Extended Events......Page 399
 Monitoring with Dynamic Management Views and Functions......Page 420
  What's Going on Inside SQL Server?......Page 421
  Viewing Blocking Information......Page 424
  Index Usage in a Database......Page 425
  Indexes Not Used in a Database......Page 426
  View Queries Waiting for Memory Grants......Page 427
  Filegroup Free Space......Page 428
  Buffer Pool Memory Usage......Page 429
  Monitoring the SQL Server Error Log......Page 430
 Management Data Warehouse......Page 431
  Viewing Data Collected by the System Data Collection Sets......Page 432
  Creating Your Own Data Collection Set......Page 434
  Examining the Data You Collected......Page 436
 SQL Server Standard Reports......Page 437
 System Center Management Pack......Page 439
 System Center Advisor......Page 440
 Summary......Page 441
 Physical Query Processing Part One: Compilation and Recompilation......Page 443
  Compilation......Page 444
  Recompilation......Page 445
  Tools and Commands for Recompilation Scenarios......Page 452
  Parser and Algebrizer......Page 454
  Optimization......Page 456
 Physical Query Processing Part Two: Execution......Page 461
  Database I/O Information......Page 462
  Working with the Query Plan......Page 463
  Estimated Execution Plan......Page 464
  Actual Execution Plan......Page 468
  Index Access Methods......Page 471
  Fragmentation......Page 482
  Statistics......Page 483
  Join Algorithms......Page 484
  Data Modification Query Plan......Page 487
  Query Processing Enhancements on Partitioned Tables and Indexes......Page 488
  Gathering Query Plans for Analysis with SQL Trace......Page 490
 Summary......Page 491
 Noteworthy Index-Related Features in SQL Server......Page 493
  What's New for Indexes in SQL Server 2012......Page 494
  Index Features from SQL Server 2008R2, SQL Server 2008, and SQL Server 2005......Page 496
  Understanding Indexes......Page 499
  Creating Indexes......Page 502
  Why Use Both Partitioned Tables and Indexes?......Page 503
  Creating Partitioned Tables......Page 504
 Index Maintenance......Page 505
  Cleaning Up Indexes......Page 506
 Improving Query Performance with Indexes......Page 508
 Database Tuning Advisor......Page 512
 Too Many Indexes?......Page 513
 Summary......Page 515
 Replication Overview......Page 517
  Replication Components......Page 518
  Replication Types......Page 520
  Single Publisher, One or More Subscribers......Page 522
  Multiple Publishers, Single Subscriber......Page 524
  Multiple Publishers Also Subscribing......Page 525
  Updating Subscriber......Page 526
  Peer-to-Peer......Page 527
  Setting Up Distribution......Page 528
  Implementing Snapshot Replication......Page 531
  Implementing Transactional and Merge Replication......Page 541
  Setting Up Peer-to-Peer Replication......Page 542
  Configuring Peer-to-Peer Replication......Page 543
  Replication Monitor......Page 546
  Replication DMVs......Page 549
  sp_replcounters......Page 550
 Summary......Page 551
Chapter 16: Clustering SQL Server 2012......Page 553
  What Clustering Can Do......Page 554
  What Clustering Cannot Do......Page 555
  Alternatives to Clustering......Page 556
 Clustering: The Big Picture......Page 558
  How Clustering Works......Page 559
  Clustering Options......Page 562
  Upgrading Your SQL Server 2012 Cluster In Place......Page 564
  Rebuilding Your Cluster......Page 565
  Preparing the Infrastructure......Page 567
  Preparing the Hardware......Page 568
  Before Installing Windows 2011 Clustering......Page 571
  Installing Windows Server 2008 Failover Clustering......Page 572
  Preparing Windows Server 2008 for Clustering......Page 575
 Clustering Microsoft Distributed Transaction Coordinator......Page 576
  Step by Step to Cluster SQL Server......Page 578
  Test, Test, and Test Again......Page 584
 Managing and Monitoring the Cluster......Page 586
 Troubleshooting Cluster Problems......Page 587
  Gathering Information......Page 588
  Working with Microsoft......Page 589
 Summary......Page 590
Chapter 17: Backup and Recovery......Page 591
  Data Modification Failure......Page 592
  Local Disasters......Page 594
  Backup/Recovery Plan......Page 595
  Disaster Recovery Planning......Page 598
  Creating the Disaster Recovery Plan......Page 600
  Maintaining the Plan......Page 602
  How Backup Works......Page 603
  Copying Databases......Page 606
  Backup Compression......Page 614
  Comparing Recovery Models......Page 615
  Choosing a Model......Page 617
  Switching Recovery Models......Page 618
  Backing Up History Tables......Page 619
  Permissions Required for Backup and Restore......Page 620
  Backing Up System Databases......Page 621
  Verifying the Backup Images......Page 622
  How Restore Works......Page 623
  Recoverability Requirements......Page 625
  Data Usage Patterns......Page 626
  Maintenance Time Window......Page 627
  Other High-Availability Solutions......Page 628
  Using SQL Server Management Studio......Page 629
  Database Maintenance Plans......Page 633
  Using Transact-SQL Backup Commands......Page 635
 Managing Backups......Page 637
  Restore Process......Page 638
  SQL Server Management Studio Restore......Page 643
  Restoring System Databases......Page 646
  SQL Server Table Partitioning......Page 648
  Partitioned View......Page 649
 Summary......Page 650
Chapter 18: SQL Server 2012 Log Shipping......Page 651
  Log Shipping to Create a Warm Standby Server......Page 652
  Log Shipping as a Disaster Recovery Solution......Page 653
  Log Shipping as a Report Database Solution......Page 654
  Secondary Server......Page 655
 Log Shipping Process......Page 656
  Identical Capacity Servers......Page 657
  Initial Configuration......Page 658
  Deploying with Management Studio......Page 660
 Monitoring and Troubleshooting......Page 668
  Monitoring with Management Studio......Page 669
  Troubleshooting Approach......Page 670
  Synchronizing Dependencies......Page 671
  Switching Roles from the Primary to Secondary Servers......Page 674
  Redirecting Clients to Connect to the Secondary Server......Page 676
 Database Backup Plan......Page 677
  SQL Server 2012 Data Mirroring......Page 678
  SQL Server 2012 Replication......Page 679
  Removing Log Shipping with T-SQL Commands......Page 680
 Log-Shipping Performance......Page 681
  With Downtime Approach......Page 682
 Summary......Page 683
 Overview of Database Mirroring......Page 685
 Operating Modes of Database Mirroring......Page 687
 Database Mirroring In Action......Page 689
  Preparing the Endpoints......Page 690
  Preparing the Database for Mirroring......Page 696
  Establishing the Mirroring Session......Page 697
  High-Safety Operating Mode with Automatic Failover......Page 699
  High-Performance Operating Mode......Page 701
  sys.database_mirroring......Page 702
  sys.database_mirroring_endpoints......Page 704
  Automatic Failover......Page 705
  Manual Failover......Page 708
  Forced Failover......Page 710
  Principal Is Lost......Page 711
  Mirror Is Lost......Page 712
  Mirror and Witness Are Lost......Page 713
  Monitoring Using System Monitor......Page 714
  Monitoring Using Database Mirroring Monitor......Page 716
  Setting Thresholds on Counters and Sending Alerts......Page 720
  Troubleshooting Setup Errors......Page 722
  Troubleshooting Runtime Errors......Page 723
  Automatic Page Repair......Page 724
  Hardware, Software, and Server Configuration......Page 725
  Database Availability During Planned Downtime......Page 726
  Client Redirection to the Mirror......Page 728
 Mirroring Multiple Databases......Page 729
 Database Mirroring and Other High-Availability Solutions......Page 730
  Database Mirroring versus Log Shipping......Page 731
 Mirroring Event Listener Setup......Page 732
 Database Snapshots......Page 736
 Summary......Page 737
Chapter 20: Integration Services Administration and Performance Tuning......Page 739
  Integration Services Uses......Page 740
  The Main Parts of Integration Services......Page 741
 Administration of the Integration Services Service......Page 743
  Configuration......Page 744
  Event Logs......Page 748
  Monitoring Activity......Page 749
  Using Management Studio for Package Management......Page 750
  Deployment......Page 753
  Configuring the SSIS Catalog......Page 756
  Deploying Packages......Page 758
  Configuring Packages......Page 760
 Execution and Scheduling......Page 763
  Running Packages with DTExec......Page 764
  Running Packages with DTExecUI (Package Deployment Model)......Page 765
  Running Packages with the Execute Package Tool (Project Deployment Model)......Page 766
  Scheduling Execution with SQL Server Agent......Page 767
  An Overview of Integration Services Security......Page 769
  Securing Packages in Package Deployment Model......Page 770
 Summary......Page 772
Chapter 21: Analysis Services Administration and Performance Tuning......Page 773
 Tour of Analysis Services......Page 774
  MOLAP Components......Page 775
  Analysis Services Architectural Components......Page 776
 Administering Analysis Services Server......Page 777
  Server Properties......Page 778
  Required Services......Page 779
  Analysis Services Scripting Language......Page 780
  Deploying Analysis Services Databases......Page 781
  Processing Analysis Services Objects......Page 785
  Backing Up and Restoring Analysis Services Databases......Page 789
  Synchronizing Analysis Services Databases......Page 792
  Monitoring Analysis Services Events......Page 793
  Creating Traces for Replay......Page 794
  Using Flight Recorder for After-the-Fact Analysis......Page 795
  Storage Modes......Page 796
  Partition Configuration......Page 797
  Designing Aggregations in the MOLAP Model......Page 799
  Database Role......Page 802
  Database Role Permissions......Page 804
  Applying Security to Analysis Services in the Tabular Model......Page 806
 Summary......Page 807
 SQL Server Reporting Services Configuration Manager......Page 809
  The Service Account......Page 812
  The Web Service URL......Page 813
  Reporting Services Databases......Page 815
  The Report Manager URL......Page 817
  E-mail Settings......Page 818
  Execution Account......Page 819
  Encryption Keys......Page 820
  Scale-out Deployment......Page 821
 Report Server Properties......Page 822
  General Properties Page......Page 823
  Execution Properties Page......Page 824
  Logging Properties Page......Page 825
  Security Properties Page......Page 826
  Advanced Properties Page......Page 827
 The Report Execution Log......Page 828
 Report Builder......Page 830
 Report Manager......Page 839
  Managing Report Manager......Page 840
  Managing Reports......Page 847
 Summary......Page 858
 Components of Integration......Page 859
  PowerPivot......Page 860
  Reporting Services......Page 862
  Power View......Page 863
  Using Data Connections in Excel......Page 864
  PerformancePoint Data Refresh......Page 870
  Visio Services Data Refresh......Page 871
  PowerPivot Data Refresh......Page 873
 Summary......Page 880
 Introduction to SQL Azure......Page 881
  Services Layer......Page 882
 Configuring SQL Azure......Page 883
  Server and Database Provisioning......Page 884
  Throttling and Load Balancing......Page 888
  Configuring SQL Azure Firewalls......Page 889
  Connecting to SQL Azure......Page 891
  Creating Logins and Users......Page 892
 Working with SQL Azure......Page 894
  Object Explorer for SQL Azure......Page 896
 What's Missing in SQL Azure......Page 898
 Summary......Page 899
Chapter 25: Alwayson Availability Groups......Page 901
  Availability Group Replicas and Roles......Page 902
  Types of Failover Supported......Page 903
  Allowing Read-Only Access to Secondary Replicas......Page 904
  Configure a New Availability Group......Page 906
  Configure an Existing Availability Group......Page 914
  Availability Group Failover Operation......Page 916
  Suspend an Availability Database......Page 917
  Client Application Connections......Page 918
 Active Secondary for Secondary Read-Only......Page 919
  Secondary Replica Client Connectivity......Page 920
  Performance......Page 922
 Backup on the Secondary Replica......Page 923
  Evaluate Backup Replicas Metadata......Page 924
 AlwaysOn Group Dashboard......Page 925
 Monitoring and Troubleshooting......Page 927
 Summary......Page 928
Index......Page 929
Advertisement......Page 962