SQL Server 2014 with PowerShell v5 Cookbook

Over 150 real-world recipes to simplify database management, automate repetitive tasks, and enhance your productivity
Preview in Mapt

SQL Server 2014 with PowerShell v5 Cookbook

Donabel Santos

3 customer reviews
Over 150 real-world recipes to simplify database management, automate repetitive tasks, and enhance your productivity
Mapt Subscription
FREE
$29.99/m after trial
eBook
$33.60
RRP $47.99
Save 29%
Print + eBook
$59.99
RRP $59.99
What do I get with a Mapt Pro subscription?
  • Unlimited access to all Packt’s 5,000+ eBooks and Videos
  • Early Access content, Progress Tracking, and Assessments
  • 1 Free eBook or Video to download and keep every month after trial
What do I get with an eBook?
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with Print & eBook?
  • Get a paperback copy of the book delivered to you
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with a Video?
  • Download this Video course in MP4 format
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
$0.00
$33.60
$59.99
$29.99p/m after trial
RRP $47.99
RRP $59.99
Subscription
eBook
Print + eBook
Start 30 Day Trial

Frequently bought together


SQL Server 2014 with PowerShell v5 Cookbook Book Cover
SQL Server 2014 with PowerShell v5 Cookbook
$ 47.99
$ 33.60
SQL Server 2012 with PowerShell V3 Cookbook Book Cover
SQL Server 2012 with PowerShell V3 Cookbook
$ 35.99
$ 25.20
Buy 2 for $35.00
Save $48.98
Add to Cart
Subscribe and access every Packt eBook & Video.
 
  • 5,000+ eBooks & Videos
  • 50+ New titles a month
  • 1 Free eBook/Video to keep every month
Start Free Trial
 

Book Details

ISBN 139781785283321
Paperback760 pages

Book Description

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.

Table of Contents

Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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

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

Authors

Table of Contents

Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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

Book Details

ISBN 139781785283321
Paperback760 pages
Read More
From 3 reviews

Read More Reviews

Recommended for You

Instant Windows Powershell 3.0 Windows Management Instrumentation Starter Book Cover
Instant Windows Powershell 3.0 Windows Management Instrumentation Starter
$ 19.99
$ 14.00
SQL Server 2012 with PowerShell V3 Cookbook Book Cover
SQL Server 2012 with PowerShell V3 Cookbook
$ 35.99
$ 25.20
Mastering Windows PowerShell Scripting Book Cover
Mastering Windows PowerShell Scripting
$ 39.99
$ 28.00
Microsoft SQL Server 2012 Performance Tuning Cookbook Book Cover
Microsoft SQL Server 2012 Performance Tuning Cookbook
$ 35.99
$ 25.20
Windows Server 2012 Automation with PowerShell Cookbook Book Cover
Windows Server 2012 Automation with PowerShell Cookbook
$ 32.99
$ 23.10
PowerShell 3.0 Advanced Administration Handbook Book Cover
PowerShell 3.0 Advanced Administration Handbook
$ 29.99
$ 21.00