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

Installing SQL Server Management Objects

SQL Server Management Objects (SMO) was introduced with SQL Server 2005 to allow SQL Server to be accessed and managed programmatically. SMO can be used in any .NET language, including C#, VB.NET, and PowerShell. Since SQL Server does not ship with many cmdlets, SMO is the key to automating most SQL Server tasks. SMO is also backwards compatible with previous versions of SQL Server, extending support all the way to SQL Server 2000.

SMO comprises two distinct classes: the Instance classes and the Utility classes.

The Instance classes are the SQL Server objects. Properties of objects such as the server, databases, and tables can be accessed and managed using the instance classes.

The Utility classes are helper or utility classes that accomplish common SQL Server tasks. These classes belong to one of four groups: Transfer, Backup, and Restore classes, or the Scripter class.

To gain access to the SMO libraries, SMO needs to be installed and the SQL Server-related assemblies need to be loaded.

Getting ready

There are a few ways to install SMO:

  • If you are installing SQL Server 2014 or already have SQL Server 2014, SMO can be installed by installing Client Tools SDK. Get your install disk or image ready.
  • If you want just SMO installed without installing SQL Server, download the SQL Server Feature 2014 Pack.

How to do it...

If you are installing SQL Server or already have SQL Server, perform the following steps:

  1. Load up your SQL Server install disk or image, and launch the setup.exe file.
  2. Select New SQL Server standalone installation or add features to an existing installation.
  3. Choose your installation type and click on Next.
  4. In the Feature Selection window, make sure you select Client Tools SDK.
    How to do it...
  5. Complete your installation

After this, you should already have all the binaries needed to use SMO.

If you are not installing SQL Server, you must install SMO using the SQL Server Feature Pack on the machine you are using SMO with. The steps are as follows:

  1. Open your web browser. Go to your favorite search engine and search for SQL Server 2014 Feature Pack.
  2. Download the package.
  3. Double-click on the SharedManagementObjects.msi to install.

There's more...

By default, the SMO assemblies in SQL Server 2014 will be installed in <SQL Server Install Directory>\120\SDK\Assemblies. This is shown in the following screenshot:

There's more...