SQL Server 2014 with PowerShell v5 Cookbook

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

SQL Server 2014 with PowerShell v5 Cookbook

This ebook is included in a Mapt subscription
Donabel Santos

2 customer reviews
Over 150 real-world recipes to simplify database management, automate repetitive tasks, and enhance your productivity
$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
Subscribe and access every Packt eBook & Video.
 
  • 4,000+ eBooks & Videos
  • 40+ New titles a month
  • 1 Free eBook/Video to keep every month
Start Free Trial
 
Preview in Mapt

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 2 reviews

Read More Reviews

Recommended for You

Mastering Windows PowerShell Scripting Book Cover
Mastering Windows PowerShell Scripting
$ 39.99
$ 34.00