47,99 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
PowerShell is Microsoft's new command-line shell and scripting language that promises to simplify automation and integration across different Microsoft applications and components. Database professionals can leverage PowerShell by utilizing its numerous built-in cmdlets, or using any of the readily available .NET classes, to automate database tasks, simplify integration, or just discover new ways to accomplish the job at hand."SQL Server 2012 with PowerShell V3 Cookbook" provides easy-to-follow, practical examples for the busy database professional. Whether you're auditing your servers, or exporting data, or deploying reports, there is a recipe that you can use right away!You start off with basic topics to get you going with SQL Server and PowerShell scripts and progress into more advanced topics to help you manage and administer your SQL Server databases.The first few chapters demonstrate how to work with SQL Server settings and objects, including exploring objects, creating databases, configuring server settings, and performing inventories. The book then deep dives into more administration topics like backup and restore, credentials, policies, jobs.Additional development and BI-specific topics are also explored, including deploying and downloading assemblies, BLOB data, SSIS packages, and SSRS reports.
A short PowerShell primer is also provided as a supplement in the Appendix, which the database professional can use as a refresher or occasional reference material. Packed with more than 100 practical, ready-to-use scripts, "SQL Server 2012 with PowerShell V3 Cookbook" will be your go-to reference in automating and managing SQL Server.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 529
Veröffentlichungsjahr: 2012
Ähnliche
Table of Contents
SQL Server 2012 with PowerShell V3 Cookbook
SQL Server 2012 with PowerShell V3 Cookbook
Copyright © 2012 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: October 2012
Production Reference: 1151012
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84968-646-4
www.packtpub.com
Cover Image by Artie Ng (<[email protected]>)
Credits
Author
Donabel Santos
Reviewers
Edwin Sarmiento
Laerte Poltronieri Junior
Acquisition Editor
Rukhsana Khambatta
Lead Technical Editor
Azharuddin Sheikh
Technical Editors
Charmaine Pereira
Sharvari Baet
Jalasha D'costa
Copy Editors
Alfida Paiva
Brandt D'Mello
Insiya Morbiwala
Aditya Nair
Project Coordinator
Yashodhan Dere
Proofreader
Chris Smith
Indexer
Tejal R. Soni
Production Coordinator
Manu Joseph
Cover Work
Manu Joseph
About the Author
Donabel Santos is a SQL Server MVP and is the senior SQL Server Developer/DBA/Trainer at QueryWorks Solutions, a consulting and training company in Vancouver, BC. She has worked with SQL Server since version 2000 in numerous development, tuning, reporting, and integration projects with ERPs, CRMs, SharePoint, and other custom applications. She holds MCITP certifications for SQL Server 2005/2008, and an MCTS for SharePoint. She is a Microsoft Certified Trainer (MCT), and is also the lead instructor for SQL Server Administration, Development, and SSIS courses at British Columbia Institute of Technology (BCIT).
Donabel is a proud member of PASS (Professional Association of SQL Server), and a proud BCIT alumna (CST diploma and degree). She blogs (www.sqlmusings.com), tweets (@sqlbelle), speaks and presents (SQLSaturday, VANPASS, Vancouver TechFest, and so on), trains (BCIT, QueryWorks Solutions), and writes (Packt, Idera, SSWUG, and so on).
Acknowledgement
Writing a book would not be possible without the unwavering support of family, friends, colleagues, mentors, acquaintances, and an awesome community. This is my first book, a dream come true, so please forgive me if I go overboard with my thanks.
To Eric, thank you… for finding me. Despite long days, sleepless nights, lengthy writing marathons, one smile from you never fails to wipe away my tiredness. Thank you for always supporting me, for believing in me, for helping me reach whichever dreams I dare to chase. I look forward to our journey together—a lifelong of hopes, dreams, and happiness.
To Mama and Papa, I am the luckiest daughter to have you as my parents. Thank you for all the sacrifices you made for me and my brothers. Words are not enough to express how much we love you, and how grateful we will always be.
To JR and RR—you will always be my baby brothers, and I am so proud to be your big sis. To Lisa, my dear sis-in-law, thank you for being part of our family. The whole family adores you. To Veronica, thanks for keeping up with the Santos' quirks. You're cool, girl! Now that the book is done, we can all play more Kinect, Acquire, and Ticket to Ride.
To my in laws—Mom Lisa, Dad Richard, Ama, Aunt Rose, Catherine, David, and Jayden—thank you for always making me feel welcome, for never making me feel I am different from your family. And to my unborn niece Kristina, auntie will teach you and Jayden SQL Server… one of these years.
To Edwin Sarmiento and Laerte Junior—my utmost and sincerest thanks for all the advice and constructive feedback. I have the highest respect for both of you. It is very humbling to work with both of you, and I learned so much from all the corrections and suggestions. Thank you for bearing with me through the revisions, despite your respective hectic schedules and numerous other commitments. I am very grateful.
To Elsie Au, thank you for introducing me to databases. I cannot imagine doing anything else. Thank you for the friendship all these years. To Kevin Cudihee, thank you for all the support all these years, for letting me do two things that I love the most—teaching and SQL Server. To Anne Marie Johnston and Alan Marchant, thank you for giving me fun work with databases. To my students, thank you for learning, sharing, and growing with me.
To BCIT—my second home. To me, BCIT was my place of refuge. When I was at a low point in my life, feeling down and out, and without direction (and afraid of computers!), BCIT provided me a place to learn, grow, and dream again. Now as an instructor, I hope I can help give back to students what BCIT gave me when I was one.
To the SQL community, the SQL family, and the SQL Server MVPs—I am so proud to be part of this group. There are so many smart SQL rockstars that I admire (Brent Ozar, Glenn Berry, Kevin Kline, Brian Knight, Grant Fritchey, Jorge Sergarra, Jeremiah Peschka, Jen Stirrup, and so many others I would love to mention and thank), who are way up there, yet who are always ready to help and inspire anyone who asks. "Community" for this group is not just lip service. It's the SQL way of life. I have learned so much from this community, and I would not be anywhere near where I am today if not for the selfless way this community shares and helps.
To the PowerShell community, thank you to the awesome authors, bloggers, and tweeps. Your articles, blogs, and books have immensely helped folks like me to learn, understand, and get excited about PowerShell.To Microsoft and the SQL Server and PowerShell respective Product Teams —thanks for creating these two amazing products. It doubles the fun for SQL geeks like me!
To the Packt team—Dhwani Dewater, Yashodhan Dere, Azharuddin Sheikh, Charmaine Pereira, Sharvari Baet and the rest of the editors and technical reviewers—thank you for giving me the chance to write this book and helping me as the book writing progressed. It is one of the most humbling, but also one of the most rewarding experiences.
To numerous friends (Shereen Qumsieh, Matthew Carriere, Grace Dimaculangan, Ben Peach, Yaroslav Pentsarskyy, Joe Xing, Min Zhu, Mary Mootatamby, Blake Wiggs, and many others), to all of my mentors and students, acquaintances via twitter (such as @pinaldave, @dsfnet, @StangSCT, @retracement, @NikoNeugebauer, @TimCost), and so many others who have helped, inspired, and encouraged me along the way—thank you.
And most importantly, thank you Lord, for all the miracles and blessings in my life.
About the Reviewers
Edwin Sarmiento is a Microsoft SQL Server MVP from Ottawa, Canada specializing in high availability, disaster recovery, and system infrastructures running on the Microsoft server technology stack. He is very passionate about technology but has interests in music, professional and organizational development, leadership, and management matters when not working with databases. He lives up to his primary mission statement—To help people and organizations grow and develop their full potential as God has planned for them.
He wants the whole world to know that the FILIPINO is a world-class citizen and brings Jesus Christ to the world.
Laerte Poltronieri Junior started in the IT world early, at the age of 12. When 16, he was developing software using Clipper Summer 85 and he used almost all versions. Then in 1998 he was introduced to SQL Server 6.5; since then it was love at first sight and marriage. In 2008, he met PowerShell and as he is an aficionado for automated, smart, and flexible solutions in SQL Server, from this marriage was born a son. And today they are a happy family.
Currently, he is writing a book for Manning Publications.
First of all, I would like to thank God. I have not always been a guy next to him, but I'm learning to give back all the love and affection that he has given me.
My family—my father, an unforgettable super-hero, my beloved mother and grandma, and my dear sister and nephews.
Also, a special thanks to some exceptional professionals and friends who are teaching and mentoring me from the beginning: Buck Woody, Chad Miller, Shay Levy, and Ravikanth Chaganti.
And last but not the least, all the #sqlfamily , #powershell and Simple-Talk friends, you guys simply rock. I owe you all the good things that happened and are happening to me.
www.PacktPub.com
Support files, eBooks, discount offers and more
You might want to visit www.PacktPub.com for support files and downloads related to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at <[email protected]> for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.
Instant Updates on New Packt Books
Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.
Preface
PowerShell is Microsoft's new command-line shell and scripting language that promises to simplify automation and integration across different Microsoft applications and components. Database professionals can leverage PowerShell by utilizing its numerous built-in cmdlets, or using any of the readily available .NET classes, to automate database tasks, simplify integration, or just discover new ways to accomplish the job at hand.
SQL Server 2012 with PowerShell V3 Cookbook provides easy-to-follow, practical examples for the busy database professional. Whether you're auditing your servers, or exporting data, or deploying reports, there is a recipe that you can use right away!
You start off with basic topics to get you going with SQL Server and PowerShell scripts and progress into more advanced topics to help you manage and administer your SQL Server databases.
The first few chapters demonstrate how to work with SQL Server settings and objects, including exploring objects, creating databases, configuring server settings, and performing inventories. The book then dives deeply into more administration topics such as backup and restore, credentials, policies, and jobs.
Additional development and BI-specific topics are also explored, including deploying and downloading assemblies, BLOB data, SSIS packages, and SSRS reports.
A short PowerShell primer is also provided as a supplement in the Appendix, which the database professional can use as a refresher or occasional reference material. Packed with more than 100 practical, ready-to-use scripts, SQL Server 2012 with PowerShell V3 Cookbook will be your go-to reference in automating and managing SQL Server.
What this book covers
Chapter 1, Getting Started with SQL Server and PowerShell explains what PowerShell is, and why you should consider learning PowerShell. It also introduces PowerShell V3 new features, and explains what needs to be in place when working with SQL Server 2012 and PowerShell.
Chapter 2, SQL Server and PowerShell Basic Tasks demonstrates scripts and snippets of code that accomplish some basic SQL Server tasks using PowerShell. We start with simple tasks such as listing SQL Server instances, and creating objects such as tables, indexes, stored procedures, and functions to get you comfortable while working with SQL Server programmatically.
Chapter 3, Basic Administration tackles more administrative tasks that can be accomplished using PowerShell, and provides recipes that can help automate a lot of repetitive tasks. Some recipes deal with instance and database properties; others provide ways of checking disk space, creating WMI alerts, setting up Database Mail, and creating and maintaining SQL Server Jobs.
Chapter 4, Security provides snippets that simplify security monitoring, including how to check failed login attempts by parsing out event logs, or how to administer roles and permissions.
Chapter 5, Advanced Administration shows how PowerShell can help you leverage features such as Policy Based Management (PBM) and encryption using PowerShell. This chapter also explores working with SQL Server Profiler trace files and events programmatically.
Chapter 6, Backup and Restore looks into different ways of backing up and restoring SQL Server databases programmatically using PowerShell.
Chapter 7, SQL Server Development provides snippets and guidance on how you can work with XML, XSL, binary data, and CLR assemblies with SQL Server and PowerShell.
Chapter 8, Business Intelligence covers how PowerShell can help automate and manage any BI-related tasks—from rendering SQL Server Reporting Services (SSRS) reports, to deploying the new SQL Server Integration Services (SSIS) 2012 ISPAC files, to backing up and restoring SQL Server Analysis Services (SSAS) cubes.
Chapter 9, Helpful PowerShell Snippets tackles a variety of recipes that are not SQL Server specific, but you may find them useful as you work with PowerShell. Recipes include snippets for creating files that use timestamps, analyzing event logs for recent system errors, and exporting a list of processes to CSV or XML.
Appendix A, SQL Server and PowerShell CheatSheet provides a concise cheatsheet of commonly used terms and snippets when working with SQL Server and PowerShell.
Appendix B, PowerShell Primer offers a brief primer on PowerShell fundamentals.
Appendix C, Resources lists additional PowerShell and SQL Server books, blogs and links.
Appendix D, Creating a SQL Server VM provides a step-by-step tutorial on how to create and configure the virtual machine that was used for this book.
What you need for this book
Windows Server 2008 R2
SQL Server 2012 Developer
Visual Studio 2010 Professional
Windows Management Framework 3.0 (includes PowerShell 3.0, WMI, and WinRM)
Who this book is for
This book is written for the SQL Server database professional (DBA, developer, BI developer) who wants to use PowerShell to automate, integrate, and simplify database tasks. A little bit of scripting background is helpful, but not necessary.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title via the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or e-mail <[email protected]>.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
Questions
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.
Chapter 1. Getting Started with SQL Server and PowerShell
In this chapter, we will cover:
Introduction
PowerShell is an administrative tool that has both shell and scripting capabilities that can leverage Windows Management Instrumentation (WMI), COM components, and .NET libraries. PowerShell is becoming more prominent with each generation of Microsoft products. Support for it is being bundled, and improved, in a number of new and upcoming Microsoft product releases. Windows Server, Exchange, ActiveDirectory, SharePoint, and even SQL Server, have all shipped with added PowerShell support and cmdlets. Even vendors such as VMWare, Citrix, Cisco, and Quest, to name a few, have provided ways to allow their products to be accessible via PowerShell.
What makes PowerShell tick? Every systems administrator probably knows the pain of trying to integrate heterogeneous systems using some kind of scripting. Historically, the solution involved some kind of VBScript, some good old batch files, maybe some C# code, some Perl—you name it. Sysadmins either had to resort to duct taping different languages together to get things to work the way they intended, or just did not bother because of the complicated code.
This is where PowerShell comes in. One of the strongest points for PowerShell is that it simplifies automation and integration between different Microsoft ecosystems. As most products have support for PowerShell, getting one system to talk to another is just a matter of discovering what cmdlets, functions, or modules need to be pulled into the script. Even if the product does not have support yet for PowerShell, it most likely has .NET or COM support, which PowerShell can easily use.
Notable PowerShell V3 features
Some of the notable features in the latest PowerShell version are:
helps automate the distribution, orchestration, and completion of multi-computer tasks, freeing users and administrators to focus on higher-level tasks.
PSWF leverages Windows Workflow Foundation 4.0 for the declarative framework, but using familiar PowerShell syntax and constructs.
Robust sessions: PowerShell V3 supports more robust sessions. Sessions can now be retained amid network interruptions. These sessions will remain open until they time out.Scheduled jobs: There is an improved support for scheduled tasks. There are new cmdlets in the PSScheduledJob module that allow you to create, enable, and manage scheduled tasks.Module AutoLoading: If you use a cmdlet that belongs to a module that hasn't been loaded yet, this will trigger PowerShell to search PSModulePath and load the first module that contains that cmdlet. This is something we can easily test:What you used to write in V1 and V2 with curly braces and $_ as follows:
can now be rewritten in V3 as:
Before you start: Working with SQL Server and PowerShell
Before we dive into the recipes, let's go over a few important concepts and terminologies that will help you understand how SQL Server and PowerShell can work together:
To get a list of the supported PSProvider objects, type:
You should see something similar to the following screenshot:
Depending on which instance of PSProvider is already available in your system, yours may be slightly different:
PSDrive: Think of your C:\, but for data stores other than the file system. To get a list of PSDrive objects in your system, type:You should see something similar to the following screenshot:
Note that there is a PSDrive for SQLServer, which can be used to navigate, access, and manipulate SQL Server objects.
Execution policy: By default, PowerShell will abide by the current execution policy to determine what kind of scripts can be run. For our recipes, we are going to assume that you will run PowerShell as the administrator on your test environment. You will also need to set the execution policy to RemoteSigned:This setting will allow PowerShell to run digitally-signed scripts, or local unsigned scripts.
Modules and snap-ins: Modules and snap-ins are ways to extend PowerShell. Both modules and snap-ins can add cmdlets and providers to your current session. Modules can additionally load functions, variables, aliases, and other tools to your session.Snap-ins are Dynamically Linked Libraries (DLL), and need to be registered before they can be used. Snap-ins are available in V1, V2, and V3. For example:
Modules, on the other hand, are more like your regular PowerShell .ps1 script files. Modules are available in V2 and V3. You do not need to register a module to use it, you just need to import:
Note
For more information on PowerShell basics, check out Appendix B, PowerShell Primer.
Working with the sample code
Samples in this book have been created and tested against SQL Server 2012 on Windows Server 2008 R2.
Note
To work with the sample code in this book using a similar VM setup that the book uses, see Appendix D, Creating a SQL Server VM.
How to do it...
If you want to use your current machine without creating a separate VM, as illustrated in Appendix D, Creating a SQL Server VM, follow these steps to prepare your machine:
http://msdn.microsoft.com/en-us/library/ms143506.aspx
Install PowerShell V3.Install Microsoft .NET Framework 4.0, if it's not already there.
Download and install Windows Management Framework 3.0, which contains PowerShell V3. At the time of writing this book, the Release Candidate (RC) is available from:
http://www.microsoft.com/en-us/download/details.aspx?id=29939
Alternatively you can go to Start | All Programs | Accessories | Windows PowerShell | Windows PowerShell ISE.
Set execution policy to RemoteSigned by executing the following, on the code editor:Note
If you want to run PowerShell V2 and V3 side by side, you can check out Jeffery Hicks' article, PowerShell 2 and 3, Side by Side:
http://mcpmag.com/articles/2011/12/20/powershell-2-and-3-side-by-side.aspx
See also
http://technet.microsoft.com/en-us/edge/Video/hh533298k
See also the SQL Server PowerShell documentation on MSDN:http://msdn.microsoft.com/en-us/library/hh245198(SQL.110).aspx
Exploring the SQL Server PowerShell hierarchy
In SQL Server 2012, the original mini-shell has been deprecated, and SQLPS is now provided as a module. Launching PowerShell from SSMS now launches a Windows PowerShell session, imports the SQLPS module, and sets the current context to the item the PowerShell session was launched from. DBAs and developers can then start navigating the object hierarchy from here.
Getting ready
Log in to SQL Server 2012 Management Studio.
How to do it...
In this recipe, we will navigate the SQL Server PowerShell hierarchy by launching a PowerShell session from SQL Server Management Studio:
Note the starting path in this window.
Type dir. This should give you a list of all objects directly accessible from the current server instance—in our case, from the default SQL Server instance KERRIGAN. Note that dir is an alias for the cmdlet Get-ChildItem.This is similar to the objects you can find under the instance node in Object Explorer in SQL Server Management Studio.
While our PowerShell window is open, let's explore the SQL Server PSDrive, or the SQL Server data store, which PowerShell treats as a series of items. Type cd\. This will change the path to the root of the current drive, which is our SQL Server PSDrive.Type dir. This will list all Items accessible from the root SQL Server PSDrive. You should see something similar to the following screenshot:Close this window.Go back to Management Studio, and right-click on one of your user databases.Click on Start PowerShell. Note that this will launch another PowerShell session, with a path that points to the database you right-clicked from:Note that the starting path of this window is different from the starting path when you first launched PowerShell in the second step. If you type dir from this location, you will see all items that are sitting underneath the AdventureWorks2008R2 database.
You can see some of the items enumerated in this screen in SQL Server Management Studio's Object Explorer, if you expand the AdventureWorks2008R2 database node.
How it works...
When PowerShell is launched through Management Studio, a context-sensitive PowerShell session is created, which automatically loads the SQLPS module. This will be evident in the prompt, which by default shows the current path of the object from which the Start PowerShell menu item was clicked.
SQL Server 2008/2008 R2 was shipped with a SQLPS mini-shell, also referred to as SQLPS utility. This can also be launched from SSMS by right-clicking on an object from Object Explorer, and clicking on Start PowerShell. This mini-shell was designed to be a closed shell preloaded with SQL Server extensions. This shell was meant to be used for SQL Server only, which proved to be quite limiting because DBAs and developers often need to load additional snap-ins and modules in order to integrate SQL Server with other systems through PowerShell. The alternative way is to launch a full-fledged PowerShell session, and depending on your PowerShell version either load snap-ins or load the SQLPS module.
In SQL Server 2012, the original mini-shell has been deprecated. When you launch a PowerShell session from SSMS in SQL Server 2012, it launches the full-fledged PowerShell session, with the updated SQLPS module loaded by default.
SQL Server is exposed as a PowerShell drive (PSDrive), which allows for traversing of objects as if they are folders and files. Thus, familiar commands for traversing directories are supported in this provider, such as dir or ls. Note that these familiar commands are often just aliases to the real cmdlet name, in this case, Get-ChildItem.
When you launch PowerShell from Management Studio, you can immediately start navigating the SQL Server PowerShell hierarchy.
Installing SMO
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. SMO is the key to automating most SQL Server tasks. SMO is also backward compatible to previous versions of SQL Server, extending support all the way to SQL Server 2000.
SMO is comprised of two distinct classes: instance classes and utility classes.
Instance classes are the SQL Server objects. Properties of objects such as the server, the databases, and tables can be accessed and set using the instance classes.
Utility classes are helper or utility classes that accomplish common SQL Server tasks. These classes belong to one of three groups: Transfer class, Backup and Restore classes, or 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 get SMO installed:
How to do it...
If you are installing SQL Server or already have SQL Server:
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:
There's more...
By default, the SMO assemblies will be installed in <SQL Server Install Directory>\110\SDK\Assemblies.
Loading SMO assemblies
Before you can use the SMO library, the assemblies need to be loaded. In SQL Server 2012, this step is easier than ever.
Getting ready
SQL Management Objects(SMO) must have already been installed on your machine.
How to do it...
In this recipe, we will load the SQLPS module.
How it works...
The way to load SMO assemblies has changed between different versions of PowerShell. In PowerShell v1, loading assemblies can be done explicitly using the Load() or LoadWithPartialName() methods. LoadWithPartialName() accepts the partial name of the assembly, and loads from the application directory or the Global Assembly Cache (GAC):
Although LoadWithPartialName()is still supported and still remains a popular way of loading assemblies, this method should not be used because it will be deprecated in future versions.
Load() requires the fully qualified name of the assembly:
In PowerShell V2, assemblies can be added by using Add-Type:
In PowerShell V3, loading these assemblies one by one is no longer necessary as long as the SQLPS module is loaded:
There may be cases where you will still want to load specific DLL versions if you are dealing with specific SQL Server versions. Or you may want to load only specific assemblies without loading the whole SQLPS module. In this case, the Add-Type command is still the viable method of bringing the assemblies in.
There's more...
When you import the SQLPS module, you might see an error about conflicting or unapproved verbs:
Note
The names of some imported commands from the module SQLPS include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.
This means there are some cmdlets that do not conform to the PowerShell naming convention, but the module and its containing cmdlets are still all loaded into your host. To suppress this warning, import the module with the –DisableNameChecking parameter.
See also
Discovering SQL-related cmdlets and modules
In order to be effective at working with SQL Server and PowerShell, knowing how to explore and discover cmdlets, snap-ins, and modules is in order.
Getting ready
Log in to your SQL Server instance, and launch PowerShell ISE. If you prefer the console, you can also launch that instead.
How to do it...
In this recipe we will list the SQL-Server related commands and cmdlets.
After you execute the line, your output window should look similar to the following screenshot:
To see which of these modules are loaded, type the following in your editor and run:If you have already used any of the cmdlets in the previous step, then you should see both SQLPS and SQLASCMDLETS. Otherwise, you will need to load these modules before you can use them.
To explicitly load these modules, type the following and run:Note that SQLASCMDLETS will be loaded when you load SQLPS.
How it works...
At the core of PowerShell are cmdlets. A cmdlet (pronounced commandlet) can be a compiled, reusable .NET code, or an advanced function, or a workflow that typically performs a very specific task. All cmdlets follow the verb-noun naming notation.
PowerShell ships with many cmdlets and can be further extended if the shipped cmdlets are not sufficient for your purposes.
A legacy way of extending PowerShell is by registering additional snap-ins. A snap-in is a binary, or a DLL, that contains cmdlets. You can create your own by building your own .NET source, compiling, and registering the snap-in. You will always need to register snap-ins before you can use them. Snap-ins are a popular way of extending PowerShell.
The following table summarizes common tasks with snap-ins:
Task
Syntax
List loaded snap-ins
Get-PSSnapin
List installed snap-ins
Get-PSSnapin -Registered
Show commands in a snap-in
Get-Command -Module "SnapinName"
Load a specific snap-in
Add-PSSnapin "SnapinName"
When starting, PowerShell V2, modules are available as the improved and preferred method of extending PowerShell.
A module is a package that can contain cmdlets, providers, functions, variables, and aliases. In PowerShell V2, modules are not loaded by default, so required modules need to be explicitly imported.
Common tasks with modules are summarized in the following table:
Task
Syntax
List loaded modules
Get-Module
List installed modules
Get-Module -ListAvailable
Show commands in a module
Get-Command -Module "ModuleName"
Load a specific module
Import-Module -Name "ModuleName"
One of the improved features with PowerShell V3 is that it supports autoloading modules. You do not need to always explicitly load modules before using the contained cmdlets. Using the cmdlet in your script is enough to trigger PowerShell to load the module that contains it.
The SQL Server 2012 modules are located in the PowerShell/Modules folder of the Install directory:
There's more...
The following table shows the list of the SQLPS and SQLASCMDLETS cmdlets of this version:
CommandType Name
ModuleName
Cmdlet Add-RoleMember
SQLASCMDLETS
Cmdlet Backup-ASDatabase
SQLASCMDLETS
Cmdlet Invoke-ASCmd
SQLASCMDLETS
Cmdlet Invoke-ProcessCube
SQLASCMDLETS
Cmdlet Invoke-ProcessDimension
SQLASCMDLETS
Cmdlet Invoke-ProcessPartition
SQLASCMDLETS
Cmdlet Merge-Partition
SQLASCMDLETS
Cmdlet New-RestoreFolder
SQLASCMDLETS
Cmdlet New-RestoreLocation
SQLASCMDLETS
Cmdlet Remove-RoleMember
SQLASCMDLETS
Cmdlet Restore-ASDatabase
SQLASCMDLETS
Cmdlet Add-SqlAvailabilityDatabase
SQLPS
Cmdlet Add-SqlAvailabilityGroupListenerStaticIp
SQLPS
Cmdlet Backup-SqlDatabase
SQLPS
Cmdlet Convert-UrnToPath
SQLPS
Cmdlet Decode-SqlName
SQLPS
Cmdlet Disable-SqlHADRService
SQLPS
Cmdlet Enable-SqlHADRService
SQLPS
Cmdlet Encode-SqlName
SQLPS
Cmdlet Invoke-PolicyEvaluation
SQLPS
Cmdlet Invoke-Sqlcmd
SQLPS
Cmdlet Join-SqlAvailabilityGroup
SQLPS
Cmdlet New-SqlAvailabilityGroup
SQLPS
Cmdlet New-SqlAvailabilityGroupListener
SQLPS
Cmdlet New-SqlAvailabilityReplica
SQLPS
Cmdlet New-SqlHADREndpoint
SQLPS
Cmdlet Remove-SqlAvailabilityDatabase
SQLPS
Cmdlet Remove-SqlAvailabilityGroup
SQLPS
Cmdlet Remove-SqlAvailabilityReplica
SQLPS
Cmdlet Restore-SqlDatabase
SQLPS
Cmdlet Resume-SqlAvailabilityDatabase
SQLPS
Cmdlet Set-SqlAvailabilityGroup
SQLPS
Cmdlet Set-SqlAvailabilityGroupListener
SQLPS
Cmdlet Set-SqlAvailabilityReplica
SQLPS
Cmdlet Set-SqlHADREndpoint
SQLPS
Cmdlet Suspend-SqlAvailabilityDatabase
SQLPS
Cmdlet Switch-SqlAvailabilityGroup
SQLPS
Cmdlet Test-SqlAvailabilityGroup
SQLPS
Cmdlet Test-SqlAvailabilityReplica
SQLPS
Test-SqlDatabaseReplicaState
SQLPS
To learn more about these cmdlets, use the Get-Help cmdlet. For example:
You can also check out the MSDN article on SQL Server database engine cmdlets:
http://msdn.microsoft.com/en-us/library/cc281847.aspx
When you load the SQLPS module, several assemblies are loaded into your host.
To get a list of SQL Server-related assemblies loaded with the SQLPS module, use the following script, which will work in both PowerShell V2 and V3:
If you want to run on a strictly V3 environment, you can take advantage of the simplified syntax:
This will show you all the loaded assemblies, including their public key tokens:
More information on running PowerShell scripts
By default, PowerShell is running in restricted mode, in other words, it does not run scripts. To run our scripts from the book, we will set the execution policy to RemoteSigned as follows:
Note
See the Execution policy section in Appendix B, PowerShell Primer, for further explanation of different execution policies.
If you save your PowerShell code in a file, you need to ensure it has a .ps1 extension otherwise PowerShell will not run it. Ideally the filename you give your script does not have spaces. You can run this script from the PowerShell console simply by calling the name. For example if you have a script called myscript.ps1 located in the C:\ directory, this is how you would invoke it:
If the file or path to the file has spaces, then you will need to enclose the full path and file name in single or double quotes, and use the call (&) operator:
If you want to retain the variables and functions included in the script, in memory, thus making them available globally in your session, then you will need to dot source the script. To dot source is literally to prefix the filename, or the path to the file, with a dot and a space:
More information on mixed assembly error
You may encounter an error when running some commands that are built using older .NET versions. Interestingly, you may see this while running your script in the PowerShell ISE, but not necessarily in the shell.
A few steps are required to solve this issue:
For the 32-bit ISE, this is the default path:
%windir%\sysWOW64\WindowsPowerShell\v1.0\PowerShell_ISE.exe
For the 64-bit ISE, this is the default path:
%windir%\system32\WindowsPowerShell\v1.0\PowerShell_ISE.exe
Go to the PowerShell ISE Install folder.Create an empty file called powershell_ise.exe.config.Add the following snippet to the content and save the file:Creating a SQL Server instance object
Most of what you will need to do in SQL Server will require a connection to an instance.
Getting ready
Open up your PowerShell console, the PowerShell ISE, or your favorite PowerShell editor.
