Download Expert Scripting and Automation for SQL Server DBAs PDF

Table of Contents

                            Contents at a Glance
Contents
About the Author
About the Technical Reviewer
Introduction
Chapter 1: T-SQL Techniques for DBAs
	Using the APPLY Operator
	Understanding XML
		Converting Results Sets to XML
			Using FOR XML RAW
			Using FOR XML AUTO
			Using FOR XML PATH
		Extracting Values from XML Documents
			query Method
			value and nodes Methods
			exist Method
			Pulling the Methods Together
	Efficient Looping
	Summary
Chapter 2: PowerShell Fundamentals
	Getting Started with PowerShell
		PowerShell Environments
		Enabling Script Execution
	Language Fundamentals
		Comments
		Standards and Aliases
		Data Types
		Variables
		Piping, Filtering, and Controlling Flow
		Importing Modules
	sqlps Module
	Navigating an Instance
	Summary
Chapter 3: SQL Server Agent Multi-Server Environments
	Using Central Management Servers
		Registering a Central Management Server
		Creating Server Groups
		Registering Servers
		Using Central Management Servers
			Running Queries with Central Management Servers
			Evaluating Policies with Central Management Servers
	Configuring Server Agent for Multi-Server Environments
		MSDB Roles
		Configuring Master and Target Server
			Prerequisite Tasks
			Configuration Tasks
		Creating Multi-Server Jobs
			Creating the Job in Object Explorer
			Creating the Job with T-SQL
			Limitations of the PowerShell Job Step
	Summary
Chapter 4: Metadata-Driven Automation
	Creating Intelligent Routines
		Configuring a Consistent Port Number
		Profiling an Instance Workload
		Query Store Metadata
			Remove Ad Hoc Query Plans
				sys.query_store_query_text
				sys.query_store_query
				sys.query_store_plan
				sys.query_store_runtime_stats
			Identifying the Most Expensive Queries
		Dynamic Index Rebuilds
		Enforcing Policies
	Analyzing Performance Counters
	Summary
Chapter 5: Automation Techniques with SSIS
	SSIS Concepts
		SSIS Components
		Getting Started
		Creating a Simple Package
			Configuring Precedence Constraints
			Testing the Package
	Adding Extra Functionality
		Creating Event Handlers
		Run Against Each Server in the Enterprise
	Automation Recipes
		Verifying Backups
		Respond to Operating System Events
	Summary
Chapter 6: Building an Inventory Database
	Inventory Database Platform Design
	Inventory Database Logical Design
		Normalization
			1NF
			2NF
			3NF
		Testing Normalization
	Inventory Database Physical Design
	Creating the Database
	Summary
Chapter 7: Automating Instance Builds
	Building the Instance
		Performing a Simple Installation
		Using a Configuration File
		Parameterizing the Script
	Configuring the Instance
		Configure Database Scoped Configurations
		Configure the Port Number
	Updating the Inventory Database
	Orchestrating the Build
	Summary
Chapter 8: Driving Automation Through Monitoring
	Working with Data Collection and the Management Data Warehouse
		Understanding Data Collection
			Data Caching
			Data Collector Security
				Data Collection Roles
				Management Data Warehouse Roles
		Simple Implementation of Management Data Warehouse
			Implementing the Data Warehouse
			Implementing the Predefined System Data Collectors
			Viewing Collected Data
			Accessing Data with T-SQL
		Custom Implementation of Management Data Warehouse
			Create a Custom Collector Set
			Create a Custom Collector Item
	Advanced Monitoring Techniques
		Architecting the Custom Collection Solution
		Building the Solution
			Creating the Database Table
			Creating the SSIS Package
			Deploying the Project
			Scheduling the Custom Data Collection
	Using Monitoring to Drive Automation
	Summary
Chapter 9: Automating Routine Maintenance and Break/Fix Scenarios
	Automating Routine Maintenance
		Automating Patching Activity
			Creating a PowerShell Script
		Automating Environment Refreshes
			Creating the SSIS Package
	Automating Break/Fix Scenarios
		Designing a Response to 9002 Errors
		Automating a Response to 9002 Errors
	Summary
Index