Professional Microsoft SQL Server 2012 Administration

10. : Configuring the Server for Optimal Performance -- What Every DBA Needs to Know About Performance -- What the Developer DBA Needs to Know About Performance -- What the Production DBA Needs to Know About Performance -- CPU -- Memory -- I/O -- Summary -- 11. : Optimizing SQL Server 2012 -- Application Optimization -- The Silent Killer: I/O Problems -- Table and Index Partitioning -- Data Compression -- CPU Considerations -- Memory Considerations and Enhancements -- Resource Governor -- Summary -- 12. : Monitoring Your SQL Server -- The Goal of Monitoring -- Choosing the Appropriate Monitoring Tools 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

An essential how-to guide for experienced DBAs on the most significant product release since 2005!

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

• Serves as a must-have how-to guide on the new features of the 2012 release of Microsoft SQL Server
• Walks you through ways to manage and monitor SQL Server and automate administration
• Guides you through SQL Azure for cloud computing, data replication, and data warehouse performance improvements
• Addresses client-connectivity enhancements, new features that ensure high availability of mission-critical applications, and tighter integration with Excel and SharePoint for data analysis and reporting
• Highlights major changes to the suite of SQL Server BI tools, including integration, reporting, and analysis services
• Includes real-world case examples and best practices from Microsoft insiders

If you're a database administrator eager to get started with the newest version of SQL Server, then this book is essential reading.

An essential how-to guide for experienced DBAs on the most significant product release since 2005!

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

• Serves as a must-have how-to guide on the new features of the 2012 release of Microsoft SQL Server
• Walks you through ways to manage and monitor SQL Server and automate administration
• Guides you through SQL Azure for cloud computing, data replication, and data warehouse performance improvements
• Addresses client-connectivity enhancements, new features that ensure high availability of mission-critical applications, and tighter integration with Excel and SharePoint for data analysis and reporting
• Highlights major changes to the suite of SQL Server BI tools, including integration, reporting, and analysis services
• Includes real-world case examples and best practices from Microsoft insiders

If you're a database administrator eager to get started with the newest version of SQL Server, then this book is essential reading.

Annotation An essential how-to guide for experienced DBAs on the most significant product release since 2005!Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security. Serves as a must-have how-to guide on the new features of the 2012 release of Microsoft SQL ServerWalks you through ways to manage and monitor SQL Server and automate administrationGuides you through SQL Azure for cloud computing, data replication, and data warehouse performance improvementsAddresses client-connectivity enhancements, new features that ensure high availability of mission-critical applications, and tighter integration with Excel and SharePoint for data analysis and reportingHighlights major changes to the suite of SQL Server BI tools, including integration, reporting, and analysis servicesIncludes real-world case examples and best practices from Microsoft insidersIf you're a database administrator eager to get started with the newest version of SQL Server, then this book is essential reading Chapter 10: Configuring the Server for Optimal Performance; What Every DBA Needs to Know About Performance; What the Developer DBA Needs to Know About Performance; What the Production DBA Needs to Know About Performance; CPU; Memory; I/O; Summary; Chapter 11: Optimizing SQL Server 2012; Application Optimization; The Silent Killer: I/O Problems; Table and Index Partitioning; Data Compression; CPU Considerations; Memory Considerations and Enhancements; Resource Governor; Summary; Chapter 12: Monitoring Your SQL Server; The Goal of Monitoring; Choosing the Appropriate Monitoring Tools