Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying SQL Server 2014 with Powershell v5 Cookbook
  • Table Of Contents Toc
  • Feedback & Rating feedback
SQL Server 2014 with Powershell v5 Cookbook

SQL Server 2014 with Powershell v5 Cookbook

By : Donabel Santos
3.4 (5)
Buy this Book
close
close
SQL Server 2014 with Powershell v5 Cookbook

SQL Server 2014 with Powershell v5 Cookbook

3.4 (5)
By: Donabel Santos
Buy this Book

Overview of this book

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 (15 chapters)
close
close
close
Preface
Icon
Preface
Icon
What this book covers
Icon
What you need for this book
Icon
Who this book is for
Icon
Sections
Icon
Conventions
Icon
Reader feedback
Icon
Customer support
Lock Free Chapter
1
1. Getting Started with SQL Server and PowerShell
chevron up
Icon
1. Getting Started with SQL Server and PowerShell
Icon
Introduction
Icon
Working with the sample code
Icon
Installing SQL Server using PowerShell
Icon
Installing SQL Server Management Objects
Icon
Loading SMO assemblies
Icon
Exploring the SQL Server PowerShell hierarchy
Icon
Discovering SQL-related cmdlets and modules
Icon
Creating a SQL Server Instance Object
Icon
Exploring SMO Server Objects
2
2. SQL Server and PowerShell Basic Tasks
Icon
2. SQL Server and PowerShell Basic Tasks
Icon
Introduction
Icon
Listing SQL Server instances
Icon
Discovering SQL Server services
Icon
Starting/stopping SQL Server services
Icon
Listing SQL Server configuration settings
Icon
Changing SQL Server Instance configurations
Icon
Searching for database objects
Icon
Scripting SQL Server Stored Procedures
Icon
Creating a database
Icon
Altering database properties
Icon
Dropping a database
Icon
Changing database owner
Icon
Creating a table
Icon
Creating a view
Icon
Creating a stored procedure
Icon
Creating a trigger
Icon
Creating an index
Icon
Executing a query/SQL script
Icon
Performing bulk export using Invoke-SqlCmd
Icon
Performing bulk export using the bcp command-line utility
Icon
Performing bulk import using BULK INSERT
Icon
Performing bulk import using the bcp command-line utility
Icon
Connecting to an Azure SQL database
Icon
Creating a table in an Azure SQL database
3
3. Basic Administration
Icon
3. Basic Administration
Icon
Introduction
Icon
Creating a SQL Server instance inventory
Icon
Creating a SQL Server database inventory
Icon
Listing installed hotfixes and Service Packs
Icon
Listing running/blocking processes
Icon
Killing a blocking process
Icon
Checking disk space usage
Icon
Setting up WMI server event alerts
Icon
Detaching a database
Icon
Attaching a database
Icon
Copying a database
Icon
Executing SQL query to multiple servers
Icon
Creating a filegroup
Icon
Adding a secondary data file to a filegroup
Icon
Increase data file size
Icon
Moving an index to a different filegroup
Icon
Checking index fragmentation
Icon
Reorganizing/rebuilding an index
Icon
Running DBCC commands
Icon
Setting up Database Mail
Icon
Listing SQL Server Jobs
Icon
Adding a SQL Server operator
Icon
Creating a SQL Server Job
Icon
Adding a SQL Server event alert
Icon
Running an SQL Server Job
Icon
Scheduling a SQL Server Job
4
4. Security
Icon
4. Security
Icon
Introduction
Icon
Listing SQL Server service accounts
Icon
Changing SQL Server service account
Icon
Listing authentication mode
Icon
Changing authentication mode
Icon
Listing SQL Server log errors
Icon
Listing failed login attempts
Icon
Enabling Common Criteria compliance
Icon
Listing logins, users, and database mappings
Icon
Listing login/user roles and permissions
Icon
Creating a user-defined server role
Icon
Creating a login
Icon
Assigning permissions and roles to a login
Icon
Creating a database user
Icon
Assigning permissions to a database user
Icon
Creating a database role
Icon
Fixing orphaned users
Icon
Creating a credential
Icon
Creating a proxy
5
5. Backup and Restore
Icon
5. Backup and Restore
Icon
Introduction
Icon
Changing database recovery model
Icon
Checking last backup date
Icon
Creating a backup device
Icon
Listing backup header and FileList information
Icon
Creating a full backup
Icon
Creating a backup on Mirrored Media Sets
Icon
Creating a differential backup
Icon
Creating a transaction log backup
Icon
Creating a filegroup backup
Icon
Restoring a database to a point-in-time
Icon
Performing an online piecemeal restore
Icon
Backing up database to Azure Blob storage
Icon
Restoring database from Azure Blob storage
6
6. Advanced Administration
Icon
6. Advanced Administration
Icon
Introduction
Icon
Connecting to LocalDB
Icon
Creating a new LocalDB instance
Icon
Listing database snapshots
Icon
Creating a database snapshot
Icon
Dropping a database snapshot
Icon
Enabling FileStream
Icon
Setting up a FileStream filegroup
Icon
Adding a FileTable
Icon
Adding full-text catalog
Icon
Adding full-text index
Icon
Creating a memory-optimized table
Icon
Creating a database master key
Icon
Creating a certificate
Icon
Creating symmetric and asymmetric keys
Icon
Setting up Transparent Data Encryption
7
7. Audit and Policies
Icon
7. Audit and Policies
Icon
Introduction
Icon
Enabling/disabling change tracking
Icon
Configuring SQL Server Audit
Icon
Listing facets and their properties
Icon
Listing policies
Icon
Exporting a policy
Icon
Importing a policy
Icon
Creating a condition
Icon
Creating a policy
Icon
Evaluating a policy
Icon
Running and saving a profiler trace event
Icon
Extracting the contents of a trace file
8
8. High Availability with AlwaysOn
Icon
8. High Availability with AlwaysOn
Icon
Introduction
Icon
Installing the Failover Cluster feature on Windows
Icon
Enabling TCP and named pipes in SQL Server
Icon
Enabling AlwaysOn in SQL Server
Icon
Creating and enabling the HADR endpoint
Icon
Granting the CONNECT permission to the HADR endpoint
Icon
Creating an AlwaysOn Availability Group
Icon
Joining the secondary replicas to Availability Group
Icon
Adding an availability database to an Availability Group
Icon
Creating an Availability Group listener
Icon
Testing the Availability Group failover
Icon
Monitoring the health of an Availability Group
9
9. SQL Server Development
Icon
9. SQL Server Development
Icon
Introduction
Icon
Importing data from a text file
Icon
Exporting records to a text file
Icon
Adding files to a FileTable
Icon
Inserting XML into SQL Server
Icon
Extracting XML from SQL Server
Icon
Creating an RSS feed from SQL Server content
Icon
Applying XSL to an RSS feed
Icon
Creating a JSON file from SQL Server
Icon
Storing binary data in SQL Server
Icon
Extracting binary data from SQL Server
Icon
Creating a new assembly
Icon
Listing user-defined assemblies
Icon
Extracting user-defined assemblies
10
10. Business Intelligence
Icon
10. Business Intelligence
Icon
Introduction
Icon
Listing items in your SSRS Report Server
Icon
Listing SSRS report properties
Icon
Using ReportViewer to view your SSRS report
Icon
Downloading an SSRS report in Excel and as a PDF
Icon
Creating an SSRS folder
Icon
Creating an SSRS data source
Icon
Changing an SSRS report's data source reference
Icon
Uploading an SSRS report to Report Manager
Icon
Downloading all SSRS report RDL files
Icon
Adding a user with a role to SSRS report
Icon
Creating folders in an SSIS package store and MSDB
Icon
Deploying an SSIS package to the package store
Icon
Executing an SSIS package stored in a package store or filesystem
Icon
Downloading an SSIS package to a file
Icon
Creating an SSISDB catalog
Icon
Creating an SSISDB folder
Icon
Deploying an ISPAC file to SSISDB
Icon
Executing an SSIS package stored in SSISDB
Icon
Listing SSAS cmdlets
Icon
Listing SSAS instance properties
Icon
Backing up an SSAS database
Icon
Restoring an SSAS database
Icon
Processing an SSAS cube
11
11. Helpful PowerShell Snippets
Icon
11. Helpful PowerShell Snippets
Icon
Introduction
Icon
Documenting PowerShell script for Get-Help
Icon
Getting history
Icon
Getting a timestamp
Icon
Getting more error messages
Icon
Listing processes
Icon
Getting aliases
Icon
Exporting to CSV and XML
Icon
Using Invoke-Expression
Icon
Testing regular expressions
Icon
Managing folders
Icon
Manipulating files
Icon
Compressing files
Icon
Searching for files
Icon
Reading an event log
Icon
Sending an e-mail
Icon
Embedding C# code
Icon
Creating an HTML report
Icon
Parsing XML
Icon
Extracting data from a web service
Icon
Using PowerShell remoting
12
A. PowerShell Primer
Icon
A. PowerShell Primer
Icon
Introduction
Icon
Understanding the need for PowerShell
Icon
Setting up the environment
Icon
Running PowerShell scripts
Icon
Learning PowerShell basics
Icon
Scripting syntax
Icon
Converting scripts into functions
Icon
Listing notable PowerShell features
Icon
Exploring more PowerShell
13
B. Creating a SQL Server VM
Icon
B. Creating a SQL Server VM
Icon
Introduction
Icon
Terminologies
Icon
Downloading software
Icon
VM details and accounts
Icon
Creating an empty virtual machine
Icon
Installing Windows Server 2012 R2 as guest OS
Icon
Installing VMware tools
Icon
Making a snapshot as a baseline
Icon
Configuring a domain controller (optional)
Icon
Creating domain accounts
Icon
Installing SQL Server 2014 on a VM
Icon
Configuring Reporting Services in native mode
Icon
Installing sample databases
Icon
Installing PowerShell V5
Icon
Using SQL Server on a Windows Azure VM
14
Index
Icon
Index

Working with the sample code

This recipe simply walks you through how you can work with the scripts in this book. Samples in this book have been created and tested against SQL Server 2014 on Windows Server 2012 R2.

How to do it...

If you want to use your current machine without creating a separate VM, as illustrated in Create a SQL Server VM section in Appendix B, follow these steps to prepare your machine:

  1. Install SQL Server 2014 on your current operating system—either Windows 7 or Windows Server 2012 R2. A list of supported operating systems for SQL Server 2014 is available at http://msdn.microsoft.com/en-us/library/ms143506.aspx.
  2. Microsoft provides really good documentation (both MSDN and TechNet) on how to install SQL Server, and the different ways you can install SQL Server. I encourage you to read the installation tutorial, as well as the installation notes that come with your software (https://msdn.microsoft.com/en-us/library/ms143219.aspx)
  3. Install PowerShell v5. At the time of writing this book, only the Windows Management Framework (WMF) 5.0 Production Preview was available. You can download it from http://www.microsoft.com/en-us/download/details.aspx?id=48729. The installation instructions are also bundled in the download. At the time of writing this book, WMF 5.0 Production Preview can be installed on Windows 7 SP1, Windows 8.1 Pro/Enterprise, Windows Server 2008 R2, Windows Server 2012, and Windows Server 2012 R2.
  4. By the time this book is in your hands, PowerShell v5 might be available and be bundled in the newer Microsoft operating systems. This download includes, as stated in the download page, updates to Windows PowerShell, Desired State Configuration (DSC) of Windows PowerShell and Windows PowerShell ISE. It also includes Package Management and Network Switch cmdlets.
  5. If you are planning to use the console, set the execution policy to RemoteSigned in the console. This setting will enable us to run the scripts presented in this book.
    1. Right-click on Windows PowerShell on your taskbar and choose Run as Administrator.
    2. Set execution policy to RemoteSigned by executing the following on the console:
      Set-ExecutionPolicy RemoteSigned
  6. If you are planning to use the PowerShell Integrated Scripting Environment (ISE), set the execution policy to RemoteSigned. We will be using the improved ISE in many samples in this book.
    1. Right-click on Windows PowerShell on your taskbar and choose Run ISE as Administrator.
    2. Set the execution policy to RemoteSigned by executing the following on the script editor:
      Set-ExecutionPolicy RemoteSigned

See also

Visually different images
CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
SQL Server 2014 with Powershell v5 Cookbook
End of Section 1
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist download Download options
font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon