Expert Scripting and Automation for SQL Server DBAs

Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse.The market has seen a trend towards there being a much smaller ratio of DBAs to SQL Server instances. Automation is the key to responding to this challenge and continuing to run a reliable database platform service.guides you through the process of automating the maintenance of your SQL Server enterprise.**__Expert Scripting and Automation for SQL Server DBAs__**Detailed discussion and examples on metadata-driven automationComprehensive coverage of automated responses to break/fix scenariosAutomate the SQL Server build processCreate intelligent, metadata-drive routinesAutomate common maintenance tasksCreate automated responses to common break/fix scenariosMonitor multiple instance from a central locationUtilize T-SQL and PowerShell for administrative purposes**Who This Book Is For**__**Expert Scripting and Automation for SQL Server DBAs**__ Contents at a Glance 6 Contents 8 About the Author 12 About the Technical Reviewer 14 Introduction 16 Chapter 1: T-SQL Techniques for DBAs 18 Using the APPLY Operator 18 Understanding XML 19 Converting Results Sets to XML 21 Using FOR XML RAW 21 Using FOR XML AUTO 28 Using FOR XML PATH 30 Extracting Values from XML Documents 34 query Method 34 value and nodes Methods 35 exist Method 36 Pulling the Methods Together 36 Efficient Looping 37 Summary 39 Chapter 2: PowerShell Fundamentals 40 Getting Started with PowerShell 40 PowerShell Environments 40 Enabling Script Execution 43 Language Fundamentals 44 Comments 44 Standards and Aliases 45 Data Types 48 Variables 48 Piping, Filtering, and Controlling Flow 50 Importing Modules 54 sqlps Module 55 Navigating an Instance 57 Summary 58 Chapter 3: SQL Server Agent Multi-Server Environments 59 Using Central Management Servers 59 Registering a Central Management Server 59 Creating Server Groups 61 Registering Servers 62 Using Central Management Servers 63 Running Queries with Central Management Servers 63 Evaluating Policies with Central Management Servers 65 Configuring Server Agent for Multi-Server Environments 67 MSDB Roles 68 Configuring Master and Target Server 69 Prerequisite Tasks 69 Configuration Tasks 72 Creating Multi-Server Jobs 77 Creating the Job in Object Explorer 78 Creating the Job with T-SQL 84 Limitations of the PowerShell Job Step 91 Summary 91 Chapter 4: Metadata-Driven Automation 92 Creating Intelligent Routines 92 Configuring a Consistent Port Number 92 Profiling an Instance Workload 96 Query Store Metadata 98 Remove Ad Hoc Query Plans 98 sys.query_store_query_text 99 sys.query_store_query 100 sys.query_store_plan 101 sys.query_store_runtime_stats 103 Identifying the Most Expensive Queries 106 Dynamic Index Rebuilds 107 Enforcing Policies 111 Analyzing Performance Counters 115 Summary 119 Chapter 5: Automation Techniques with SSIS 120 SSIS Concepts 120 SSIS Components 120 Getting Started 121 Creating a Simple Package 123 Configuring Precedence Constraints 129 Testing the Package 132 Adding Extra Functionality 133 Creating Event Handlers 134 Run Against Each Server in the Enterprise 151 Automation Recipes 155 Verifying Backups 155 Respond to Operating System Events 163 Summary 171 Chapter 6: Building an Inventory Database 172 Inventory Database Platform Design 172 Inventory Database Logical Design 175 Normalization 176 1NF 177 2NF 180 3NF 180 Testing Normalization 181 Inventory Database Physical Design 184 Creating the Database 187 Summary 190 Chapter 7: Automating Instance Builds 191 Building the Instance 191 Performing a Simple Installation 194 Using a Configuration File 197 Parameterizing the Script 206 Configuring the Instance 207 Configure Database Scoped Configurations 207 Configure the Port Number 211 Updating the Inventory Database 212 Orchestrating the Build 216 Summary 218 Chapter 8: Driving Automation Through Monitoring 219 Working with Data Collection and the Management Data Warehouse 219 Understanding Data Collection 219 Data Caching 220 Data Collector Security 220 Data Collection Roles 220 Management Data Warehouse Roles 221 Simple Implementation of Management Data Warehouse 221 Implementing the Data Warehouse 222 Implementing the Predefined System Data Collectors 223 Viewing Collected Data 224 Accessing Data with T-SQL 230 Custom Implementation of Management Data Warehouse 232 Create a Custom Collector Set 232 Create a Custom Collector Item 234 Advanced Monitoring Techniques 238 Architecting the Custom Collection Solution 238 Building the Solution 239 Creating the Database Table 240 Creating the SSIS Package 241 Deploying the Project 253 Scheduling the Custom Data Collection 259 Using Monitoring to Drive Automation 264 Summary 266 Chapter 9: Automating Routine Maintenance and Break/Fix Scenarios 267 Automating Routine Maintenance 267 Automating Patching Activity 267 Creating a PowerShell Script 270 Automating Environment Refreshes 272 Creating the SSIS Package 274 Automating Break/Fix Scenarios 283 Designing a Response to 9002 Errors 284 Automating a Response to 9002 Errors 285 Summary 293 Index 294

Pro SQL Server 2012 Administrationis a one-stop source for all the knowledge you'll need to professionally manage a database under Microsoft SQL Server 2012. SQL Server administration is a complex field requiring an unusually broad and well-rounded skill set. A gooddatabase administratormust be part system administrator, part database design expert, and part SQL tuning optimization guru. Above all, he or she must be capable of restoring a damaged database at any time. Database administration requires excellent people skills for dealing with management and clients, and solid technical skills for dealing with the hardware and the software.

Pro SQL Server 2012 Administration addresses all aspects of database administration, regardless of the size of the implementation. Install SQL Server 2012 and create new database environments. Develop a sound backup strategy for your database. Perform recovery as needed, which many consider to be job #1. Authors Ken Simmons and Sylvester Carstarphen also help you automate your work throughthe use of policy-based management. Performance optimization gets good coverage, so that you can render assistance when developers need help with problem queries.

Rareamong database and other technical books is solid guidance on the people side of the equation. Success as a database administrator comes from working well with others, becoming involved in the community, and building the right combination of technical knowledge and people skills to solve problems and get things done professionally and amicably. Simmons and Carstarphen pay special attention to these aspects of the job in this new edition of their comprehensive book on SQL Server administration.

• Helps you manage a SQL Server 2012 database at professional level
• Covers the gamut of database administration skills
• Provides guidance on performance optimization

What you’ll learn

• Install SQL Server 2012 and create new databases
• Implement backup & recovery strategies to protect your database, and your job
• Diagnose and resolvequery performance problems
• Apply policy-based management for consistency across all databases in an organization
• Turn every interaction with a client, manager or executive into a stepping-stone towards career success

Who this book is for

Pro SQL Server 2012 Administration is aimed at junior and seasoned database administrators who are looking to develop their knowledge and skill set in Microsoft SQL Server 2012. Readers should already have some exposure to databases in general, or to SQL Server in specific.

Table of Contents

1. What's New?
2. Pre-Install Considerations
3. Choosing a High-Availability Solution
4. Installing and Upgrading
5. Post Installation
6. Multi-Server Administration
7. Security
8. Administering Database Objects
9. Indexing for Performance
10. Managing Backups
11. Restore and Recovery Strategies
12. Automating Routine Maintenance
13. Monitoring Your Server
14. Auditing
15. Extended Events Interface
16. Managing Query Performance
17. Secrets to Excelling as a Professional DBA
18. What's Next?

Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse. The market has seen a trend towards there being a much smaller ratio of DBAs to SQL Server instances. Automation is the key to responding to this challenge and continuing to run a reliable database platform service. Expert Scripting and Automation for SQL Server DBAs guides you through the process of automating the maintenance of your SQL Server enterprise. Expert Scripting and Automation for SQL Server DBAs shows how to automate the SQL Server build processes, monitor multiple instances from a single location, and automate routine maintenance tasks throughout your environment. You will also learn how to create automated responses to common or time consuming break/fix scenarios. The book helps you become faster and better at what you do for a living, and thus more valuable in the job market. Extensive coverage of automation using PowerShell and T-SQL Detailed discussion and examples on metadata-driven automation Comprehensive coverage of automated responses to break/fix scenarios What You Will Learn Automate the SQL Server build process Create intelligent, metadata-drive routines Automate common maintenance tasks Create automated responses to common break/fix scenarios Monitor multiple instance from a central location Utilize T-SQL and PowerShell for administrative purposes Who This Book Is For Expert Scripting and Automation for SQL Server DBAs is a book for SQL Server database administrators responsible for managing increasingly large numbers of databases across their business enterprise. The book is also useful for any database administrator looking to ease their workload through automation. The book addresses the needs of these audiences by showing how to get more done through less effort by implementing an intelligent, automated-processes service model using tools such as T-SQL, PowerShell, Server Agent, and the Management Data Warehouse. Pro SQL Server 2012 Administration is a one-stop source for all the knowledge you'll need to professionally manage a database under Microsoft SQL Server 2012. SQL Server administration is a complex field requiring an unusually broad and well-rounded skill set. A good database administrator must be part system administrator, part database design expert, and part SQL tuning optimization guru. Above all, he or she must be capable of restoring a damaged database at any time. Database administration requires excellent people skills for dealing with management and clients, and solid technical skills for dealing with the hardware and the software.Pro SQL Server 2012 Administration addresses all aspects of database administration, regardless of the size of the implementation. Install SQL Server 2012 and create new database environments. Develop a sound backup strategy for your database. Perform recovery as needed, which many consider to be job #1. Authors Ken Simmons and Sylvester Carstarphen also help you automate your work through the use of policy-based management. Performance optimization gets good coverage, so that you can render assistance when developers need help with problem queries. Rare among database and other technical books is solid guidance on the people side of the equation. Success as a database administrator comes from working well with others, becoming involved in the community, and building the right combination of technical knowledge and people skills to solve problems and get things done professionally and amicably. Simmons and Carstarphen pay special attention to these aspects of the job in this new edition of their comprehensive book on SQL Server administration.Helps you manage a SQL Server 2012 database at professional level Covers the gamut of database administration skills Provides guidance on performance optimization Pro SQL Server 2012 Administration is a one-stop source for all the knowledge you'll need to professionally manage a database under Microsoft SQL Server 2012. SQL Server administration is a complex field requiring an unusually broad and well-rounded skill set. A good database administrator must be part system administrator, part database design expert, and part SQL tuning optimization guru. Above all, he or she must be capable of restoring a damaged database at any time. Database administration requires excellent people skills for dealing with management and clients, and solid technical skills for dealing with the hardware and the software. Pro SQL Server 2012 Administration addresses all aspects of database administration, regardless of the size of the implementation. Install SQL Server 2012 and create new database environments. Develop a sound backup strategy for your database. Perform recovery as needed, which many consider to be job #1. Authors Ken Simmons and Sylvester Carstarphen also help you automate your work through the use of policy-based management. Performance optimization gets good coverage, so that you can render assistance when developers need help with problem queries. Rare among database and other technical books is solid guidance on the people side of the equation. Success as a database administrator comes from working well with others, becoming involved in the community, and building the right combination of technical knowledge and people skills to solve problems and get things done professionally and amicably. Simmons and Carstarphen pay special attention to these aspects of the job in this new edition of their comprehensive book on SQL Server administration. Pro SQL Server Administration brings SQL Server administration into the modern era with strong coverage of hybrid cloud environments, In-Memory OLTP, and installation on Server Core. This comprehensive guide to SQL Server Administration for todayĺls DBA helps you to administer the new and key areas of SQL Server, including Columnstore indexes and the In-Memory OLTP feature set introduced in 2014. You will also be guided through the administration of traditional areas of SQL Server, including how to secure your instance, monitor and maintain your instance, and to use features such as AlwaysOn to make your instance highly available. Also covered is the use of SQL Server features to scale out read-only workloads. Pro SQL Server Administration is an all-new book taking up-to-date and modern approach that you'll want and need to further your career as a SQL Server database administrator. Extensive coverage of hybrid cloud environments involving Azure SQL Database Detailed discussions on all new, key features, including AlwaysOn and in-memory support Comprehensive coverage of key skills, such as monitoring, maintenance and indexing Front Matter....Pages i-xvi T-SQL Techniques for DBAs....Pages 1-22 PowerShell Fundamentals....Pages 23-41 SQL Server Agent Multi-Server Environments....Pages 43-75 Metadata-Driven Automation....Pages 77-104 Automation Techniques with SSIS....Pages 105-156 Building an Inventory Database....Pages 157-175 Automating Instance Builds....Pages 177-204 Driving Automation Through Monitoring....Pages 205-252 Automating Routine Maintenance and Break/Fix Scenarios....Pages 253-279 Back Matter....Pages 281-284