About This Book
This book helps you build a strong foundation to get you comfortable using PowerShell with SQL Server, empowering you to create more complex scripts for your day-to-day job
The book provides numerous guidelines, tips, and explanations on how and when to use PowerShell cmdlets, WMI, SMO, .NET classes, or other components
It offers easy-to-follow, practical recipes to help you get the most out of SQL Server and PowerShell
Who This Book Is For
If you are a SQL Server database professional (DBA, developer, or BI developer) who wants to use PowerShell to automate, integrate, and simplify database tasks, this books is for you. Prior knowledge of scripting would be helpful, but it is not necessary.
What You Will Learn
Explore database objects and execute queries on multiple servers
Manage and monitor the running of SQL Server services and accounts
Back up and restore databases
Create an inventory of database properties and server configuration settings
Maintain permissions and security for users
Work with CLR assemblies, XML, and BLOB objects in SQL
Manage and deploy SSIS packages and SSRS reports
In Detail
PowerShell can be leveraged when automating and streamlining SQL Server tasks. PowerShell comes with a rich set of cmdlets, and integrates tightly with the .NET framework. Its scripting capabilities are robust and flexible, allowing you to simplify automation and integration across different Microsoft applications and components.
The book starts with an introduction to the new features in SQL Server 2014 and PowerShell v5 and the installation of SQL Server. You will learn about basic SQL Server administration tasks and then get to know about some security-related topics such as the authentication mode and assigning permissions. Moving on, you will explore different methods to back up and restore your databases and perform advanced administration tasks such as working with Policies, Filetables, and SQL audits. The next part of the book covers more advanced HADR tasks such as log shipping and data mirroring, and then shows you how to develop your server to work with BLOB, XML, and JSON.
Following on from that, you will learn about SQL Server's BI stack, which includes SSRS reports, the SSIS package, and the SSAS cmdlet and database. Snippets not specific to SQL Server will help you perform tasks quickly on SQL servers. Towards the end of the book, you will find some useful information, which includes a PowerShell tutorial for novice users, some commonly-used PowerShell and SQL Server syntax, and a few online resources. Finally, you will create your own SQL Server Sandbox VMs. All these concepts will help you to efficiently manage your administration tasks.
Style and approach
SQL Server 2014 with PowerShell v5 Cookbook is an example-focused book that provides step-by-step instructions on how to accomplish specific SQL Server tasks using PowerShell. Each recipe is followed by an analysis of the steps or design decisions taken and additional information about the task at hand. Working scripts are provided for all examples so that you can dive in right away.
You can read this book sequentially by chapter or you can pick and choose which topics you need right away.
Chapter 1, Getting Started with SQL Server and PowerShell, provides an introduction on how to work with SQL Server and PowerShell, including an introduction to SQL Server Management Objects (SMO). This chapter provides a recipe to install SQL Server using PowerShell and helps you explore and discover SQL Server-related objects and cmdlets.
Chapter 2, SQL Server and PowerShell Basic Tasks, provides scripts and snippets of code that accomplish some basic SQL Server tasks using PowerShell. Tasks include listing SQL Server instances, discovering SQL Server services, configuring SQL Server, importing/exporting records in SQL Server, and creating objects such as tables, indexes, stored procedures, and functions. Some recipes also teach you how to work with Azure SQL Database.
Chapter 3, Basic Administration, explores how administrative tasks can be accomplished in PowerShell. Some recipes deal with how to create SQL Server instances and database inventories, how to check disk space, running processes, and SQL Server jobs. Other recipes show you how to attach/detach/copy databases, add files to databases, and execute a query to multiple SQL Server instances
Chapter 4, Security, focuses on how to work with SQL Server service accounts, manage logins/users/permissions, and monitor login attempts and also how to work with database roles, credentials, and proxies.
Chapter 5, Backup and Restore, teaches you what you already know about SQL Server backup and restore procedures and shows you how these tasks can be done using PowerShell. Many recipes use SQL Server-specific cmdlets, such as Backup-SqlDatabase and Restore-SqlDatabase wherever possible, but also utilize SQL Server Management Objects (SMO) to get more information on backup metadata. Some recipes also help you tackle backup and restore to Azure BLOB storage.
Chapter 6, Advanced Administration, discusses some of the most advanced features of SQL Server and how you can work with them in PowerShell. Recipes in this chapter include how to work with LocalDB, database snapshots, Filestream, FileTable, Full-Text Index, memory-optimized tables, security objects such as certificates, symmetric and asymmetric keys, and setting up Transparent Data Encryption (TDE).
Chapter 7, Audit and Policies, focuses on how to work with SQL Server tracking and auditing capabilities and SQL Server Policy Based Management (PBM). This chapter also explores how to work with SQL Server Profiler trace files and events programmatically.
Chapter 8, High Availability with AlwaysOn, covers specific recipes that can help you manage and automate SQL Server AlwaysOn, including how to install the failover cluster feature, enabling AlwaysOn, creating AlwaysOn availability groups and listeners, and testing the availability group failover.
Chapter 9, SQL Server Development, provides snippets and guidance on how you can work with XML, XSL, JSON, binary data, files in FileTable, and CLR assemblies with SQL Server and PowerShell.
Chapter 10, Business Intelligence, covers how PowerShell can help you automate and manage any BI-related tasks, including how to manage and execute SQL Server Integration Services (SSIS) packages, list and download SQL Server Reporting Services (SSRS) reports, and backup and restore SQL Server Analysis Services (SSAS) cubes.
Chapter 11, Helpful PowerShell Snippets, covers a variety of recipes that are not SQL Server-specific, but you may find them useful when working with SQL Server and PowerShell. Recipes include snippets for creating files that use timestamps, using Invoke-Expression, compressing files, reading event logs, embedding C# code, extracting data
from a web service, and exporting a list of processes to CSV or XML.
Appendix A, PowerShell Primer, offers a brief primer on PowerShell fundamentals for the SQL Server professional. This chapter includes sections on how to run PowerShell scripts, understand PowerShell syntax, and convert scripts into functions to make them more reusable.
Appendix B, Creating a SQL Server VM, provides a step-by-step tutorial on how to create and configure the virtual machine that was used for this book.
No comments:
Post a Comment