Book description
Over 150 real-world recipes to simplify database management, automate repetitive tasks, and enhance your productivity
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.
Table of contents
-
SQL Server 2014 with PowerShell v5 Cookbook
- Table of Contents
- SQL Server 2014 with PowerShell v5 Cookbook
- Credits
- About the Author
- Acknowledgments
- About the Reviewers
- www.PacktPub.com
- Preface
-
1. Getting Started with SQL Server and PowerShell
- Introduction
- Working with the sample code
- Installing SQL Server using PowerShell
- Installing SQL Server Management Objects
- Loading SMO assemblies
- Exploring the SQL Server PowerShell hierarchy
- Discovering SQL-related cmdlets and modules
- Creating a SQL Server Instance Object
- Exploring SMO Server Objects
-
2. SQL Server and PowerShell Basic Tasks
- Introduction
- Listing SQL Server instances
- Discovering SQL Server services
- Starting/stopping SQL Server services
- Listing SQL Server configuration settings
- Changing SQL Server Instance configurations
- Searching for database objects
- Scripting SQL Server Stored Procedures
- Creating a database
- Altering database properties
- Dropping a database
- Changing database owner
- Creating a table
- Creating a view
- Creating a stored procedure
- Creating a trigger
- Creating an index
- Executing a query/SQL script
- Performing bulk export using Invoke-SqlCmd
- Performing bulk export using the bcp command-line utility
- Performing bulk import using BULK INSERT
- Performing bulk import using the bcp command-line utility
- Connecting to an Azure SQL database
- Creating a table in an Azure SQL database
-
3. Basic Administration
- Introduction
- Creating a SQL Server instance inventory
- Creating a SQL Server database inventory
- Listing installed hotfixes and Service Packs
- Listing running/blocking processes
- Killing a blocking process
- Checking disk space usage
- Setting up WMI server event alerts
- Detaching a database
- Attaching a database
- Copying a database
- Executing SQL query to multiple servers
- Creating a filegroup
- Adding a secondary data file to a filegroup
- Increase data file size
- Moving an index to a different filegroup
- Checking index fragmentation
- Reorganizing/rebuilding an index
- Running DBCC commands
- Setting up Database Mail
- Listing SQL Server Jobs
- Adding a SQL Server operator
- Creating a SQL Server Job
- Adding a SQL Server event alert
- Running an SQL Server Job
- Scheduling a SQL Server Job
-
4. Security
- Introduction
- Listing SQL Server service accounts
- Changing SQL Server service account
- Listing authentication mode
- Changing authentication mode
- Listing SQL Server log errors
- Listing failed login attempts
- Enabling Common Criteria compliance
- Listing logins, users, and database mappings
- Listing login/user roles and permissions
- Creating a user-defined server role
- Creating a login
- Assigning permissions and roles to a login
- Creating a database user
- Assigning permissions to a database user
- Creating a database role
- Fixing orphaned users
- Creating a credential
- Creating a proxy
-
5. Backup and Restore
- Introduction
- Changing database recovery model
- Checking last backup date
- Creating a backup device
- Listing backup header and FileList information
- Creating a full backup
- Creating a backup on Mirrored Media Sets
- Creating a differential backup
- Creating a transaction log backup
- Creating a filegroup backup
- Restoring a database to a point-in-time
- Performing an online piecemeal restore
- Backing up database to Azure Blob storage
- Restoring database from Azure Blob storage
-
6. Advanced Administration
- Introduction
- Connecting to LocalDB
- Creating a new LocalDB instance
- Listing database snapshots
- Creating a database snapshot
- Dropping a database snapshot
- Enabling FileStream
- Setting up a FileStream filegroup
- Adding a FileTable
- Adding full-text catalog
- Adding full-text index
- Creating a memory-optimized table
- Creating a database master key
- Creating a certificate
- Creating symmetric and asymmetric keys
- Setting up Transparent Data Encryption
-
7. Audit and Policies
- Introduction
- Enabling/disabling change tracking
- Configuring SQL Server Audit
- Listing facets and their properties
- Listing policies
- Exporting a policy
- Importing a policy
- Creating a condition
- Creating a policy
- Evaluating a policy
- Running and saving a profiler trace event
- Extracting the contents of a trace file
-
8. High Availability with AlwaysOn
- Introduction
- Installing the Failover Cluster feature on Windows
- Enabling TCP and named pipes in SQL Server
- Enabling AlwaysOn in SQL Server
- Creating and enabling the HADR endpoint
- Granting the CONNECT permission to the HADR endpoint
- Creating an AlwaysOn Availability Group
- Joining the secondary replicas to Availability Group
- Adding an availability database to an Availability Group
- Creating an Availability Group listener
- Testing the Availability Group failover
- Monitoring the health of an Availability Group
-
9. SQL Server Development
- Introduction
- Importing data from a text file
- Exporting records to a text file
- Adding files to a FileTable
- Inserting XML into SQL Server
- Extracting XML from SQL Server
- Creating an RSS feed from SQL Server content
- Applying XSL to an RSS feed
- Creating a JSON file from SQL Server
- Storing binary data in SQL Server
- Extracting binary data from SQL Server
- Creating a new assembly
- Listing user-defined assemblies
- Extracting user-defined assemblies
-
10. Business Intelligence
- Introduction
- Listing items in your SSRS Report Server
- Listing SSRS report properties
- Using ReportViewer to view your SSRS report
- Downloading an SSRS report in Excel and as a PDF
- Creating an SSRS folder
- Creating an SSRS data source
- Changing an SSRS report's data source reference
- Uploading an SSRS report to Report Manager
- Downloading all SSRS report RDL files
- Adding a user with a role to SSRS report
- Creating folders in an SSIS package store and MSDB
- Deploying an SSIS package to the package store
- Executing an SSIS package stored in a package store or filesystem
- Downloading an SSIS package to a file
- Creating an SSISDB catalog
- Creating an SSISDB folder
- Deploying an ISPAC file to SSISDB
- Executing an SSIS package stored in SSISDB
- Listing SSAS cmdlets
- Listing SSAS instance properties
- Backing up an SSAS database
- Restoring an SSAS database
- Processing an SSAS cube
-
11. Helpful PowerShell Snippets
- Introduction
- Documenting PowerShell script for Get-Help
- Getting history
- Getting a timestamp
- Getting more error messages
- Listing processes
- Getting aliases
- Exporting to CSV and XML
- Using Invoke-Expression
- Testing regular expressions
- Managing folders
- Manipulating files
- Compressing files
- Searching for files
- Reading an event log
- Sending an e-mail
- Embedding C# code
- Creating an HTML report
- Parsing XML
- Extracting data from a web service
- Using PowerShell remoting
- A. PowerShell Primer
-
B. Creating a SQL Server VM
- Introduction
- Terminologies
- Downloading software
- VM details and accounts
- Creating an empty virtual machine
- Installing Windows Server 2012 R2 as guest OS
- Installing VMware tools
- Making a snapshot as a baseline
- Configuring a domain controller (optional)
- Creating domain accounts
- Installing SQL Server 2014 on a VM
- Configuring Reporting Services in native mode
- Installing sample databases
- Installing PowerShell V5
- Using SQL Server on a Windows Azure VM
- Index
Product information
- Title: SQL Server 2014 with PowerShell v5 Cookbook
- Author(s):
- Release date: December 2015
- Publisher(s): Packt Publishing
- ISBN: 9781785283321
You might also like
book
Powershell Core 6.2 Cookbook
Make use of hands-on recipes for many tasks that are typically encountered in both the on-premises …
book
Microsoft SQL Server 2014 Unleashed
The industry’s most complete, useful, and up-to-date guide to SQL Server 2014. You’ll find start-to-finish coverage …
book
Pro PowerShell for Database Developers
helps you master PowerShell application development by continuing where other books leave off. There are no …
book
Microsoft SQL Server 2012 Performance Tuning Cookbook
With this book you’ll learn all you need to know about performance monitoring, tuning, and management …