Book Image

SQL Server 2014 with Powershell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with Powershell v5 Cookbook

By: Donabel Santos

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)
14
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