Expert Scripting and Automation for SQL Server DBAs

✍ Scribed by SpringerLink (Online service); Carter, Peter A

• Publisher: Apress
• Year: 2016
• Tongue: English
• Leaves: 297
• Edition: 1st ed. 2016
• Category: Library

✦ Synopsis

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 DBAsguides you through the process of automating the maintenance of your SQL Server enterprise.

Expert Scripting and Automation for SQL Server DBAsshows 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 scenariosWhat 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 DBAsis 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.

✦ Table of Contents

Contents at a Glance......Page 6
Contents......Page 8
About the Author......Page 12
About the Technical Reviewer......Page 14
Introduction......Page 16
Using the APPLY Operator......Page 18
Understanding XML......Page 19
Using FOR XML RAW......Page 21
Using FOR XML AUTO......Page 28
Using FOR XML PATH......Page 30
query Method......Page 34
value and nodes Methods......Page 35
Pulling the Methods Together......Page 36
Efficient Looping......Page 37
Summary......Page 39
PowerShell Environments......Page 40
Enabling Script Execution......Page 43
Comments......Page 44
Standards and Aliases......Page 45
Variables......Page 48
Piping, Filtering, and Controlling Flow......Page 50
Importing Modules......Page 54
sqlps Module......Page 55
Navigating an Instance......Page 57
Summary......Page 58
Registering a Central Management Server......Page 59
Creating Server Groups......Page 61
Registering Servers......Page 62
Running Queries with Central Management Servers......Page 63
Evaluating Policies with Central Management Servers......Page 65
Configuring Server Agent for Multi-Server Environments......Page 67
MSDB Roles......Page 68
Prerequisite Tasks......Page 69
Configuration Tasks......Page 72
Creating Multi-Server Jobs......Page 77
Creating the Job in Object Explorer......Page 78
Creating the Job with T-SQL......Page 84
Summary......Page 91
Configuring a Consistent Port Number......Page 92
Profiling an Instance Workload......Page 96
Remove Ad Hoc Query Plans......Page 98
sys.query_store_query_text......Page 99
sys.query_store_query......Page 100
sys.query_store_plan......Page 101
sys.query_store_runtime_stats......Page 103
Identifying the Most Expensive Queries......Page 106
Dynamic Index Rebuilds......Page 107
Enforcing Policies......Page 111
Analyzing Performance Counters......Page 115
Summary......Page 119
SSIS Components......Page 120
Getting Started......Page 121
Creating a Simple Package......Page 123
Configuring Precedence Constraints......Page 129
Testing the Package......Page 132
Adding Extra Functionality......Page 133
Creating Event Handlers......Page 134
Run Against Each Server in the Enterprise......Page 151
Verifying Backups......Page 155
Respond to Operating System Events......Page 163
Summary......Page 171
Inventory Database Platform Design......Page 172
Inventory Database Logical Design......Page 175
Normalization......Page 176
1NF......Page 177
3NF......Page 180
Testing Normalization......Page 181
Inventory Database Physical Design......Page 184
Creating the Database......Page 187
Summary......Page 190
Building the Instance......Page 191
Performing a Simple Installation......Page 194
Using a Configuration File......Page 197
Parameterizing the Script......Page 206
Configure Database Scoped Configurations......Page 207
Configure the Port Number......Page 211
Updating the Inventory Database......Page 212
Orchestrating the Build......Page 216
Summary......Page 218
Understanding Data Collection......Page 219
Data Collection Roles......Page 220
Simple Implementation of Management Data Warehouse......Page 221
Implementing the Data Warehouse......Page 222
Implementing the Predefined System Data Collectors......Page 223
Viewing Collected Data......Page 224
Accessing Data with T-SQL......Page 230
Create a Custom Collector Set......Page 232
Create a Custom Collector Item......Page 234
Architecting the Custom Collection Solution......Page 238
Building the Solution......Page 239
Creating the Database Table......Page 240
Creating the SSIS Package......Page 241
Deploying the Project......Page 253
Scheduling the Custom Data Collection......Page 259
Using Monitoring to Drive Automation......Page 264
Summary......Page 266
Automating Patching Activity......Page 267
Creating a PowerShell Script......Page 270
Automating Environment Refreshes......Page 272
Creating the SSIS Package......Page 274
Automating Break/Fix Scenarios......Page 283
Designing a Response to 9002 Errors......Page 284
Automating a Response to 9002 Errors......Page 285
Summary......Page 293
Index......Page 294