Microsoft SQL Server 2008 Administration with Windows PowerShell (Wrox Programmer to Programmer)

580 Pages·2009·8.7 MB·English

by  Ananthakumar Muthusamy

spine=1.152"
Wrox Programmer to ProgrammerTM Wrox Programmer to ProgrammerTM
Microsoft®
SQL Server 2008 Administration MAK
®
Yan Pan
with Windows PowerShell™
Secure, reliable, and scalable, SQL Server 2008 delivers a dynamic, smart, and wSM
productive data platform for all your data-related needs and offers many new i
iQc
features that will change how you administer a database server. Among these t r
new features is the ability to use Windows PowerShell 2.0 scripts to automate and h o
Ls
manage various aspects of the Windows environment. W o
f
Written by well-known and highly respected SQL Server experts, this nuts-and- St
i ®
bolts guide brings together the best of both worlds—database and system n
administration—to help you manage, automate, and control your environment. de
You’ll discover how to construct effective and practical solutions that can improve or
SQL Server administration and monitoring while saving you countless hours of wv
manual effort and ensuring more consistent results.
se
Packed with clear, well-constructed examples throughout, this tutorial-based
resource covers the fundamentals of Windows PowerShell so you can get started Pr
writing scripts in Windows PowerShell to perform SQL Server 2008 administration o ®
tasks immediately. Wrox Programmer to ProgrammerTM w 2
e
What you will learn from this book
0
r
● How Windows Management Instrumentation Providers for SQL Server enable Professional S
Windows 0
you to manage SQL Server services and network connectivity PowerShell™ h
Programming:
● How to manage SQL Server objects, including the new policy objects using the Snap-ins, Cmdlets, Hosts, and Providers e8 Microsoft®
new SQL Server 2008 support for Windows PowerShell A rul Kumaravel, Jon White, Naixin Li, Scott Happell, Guohui Xie, Krishna C. Vutukuri l SQL Server 2008
l
● Ways to use SQL Server Management Objects to create database and Updates, source code, and Wrox technical support at www.wrox.com ™A
®
database objects, and backup and restore databases
d
● Techniques for building an SQL Server inventory over an existing or new Enhance Your Knowledge
environment Advance Your Career m
● Tips for installing, monitoring, collecting performance data, database scripting, Administration
i
and more, using Windows PowerShell
n
● Various programming features such as inputs, outputs, debugging, functions, and more.
i
Who this book is for s
This book is for SQL Server database administrators, developers, or system administrators who are looking to manage SQL Server t
2008 using Windows PowerShell 2.0. No previous Windows PowerShell experience is assumed. r with Windows PowerShell
™
a
Wrox guides are crafted to make learning programming languages and technologies easier than you think. Written by programmers
t
for programmers, they provide a structured, tutorial format that will guide you through all the techniques involved.
i
o
www.wrox.com
n
Recommended Database Management ISBN: 978-0-470-47728-1
Computer Book Ananthakumar Muthusamy, Yan Pan
Categories General
$59.99 USA
$71.99 CAN Updates, source code, and Wrox technical support at www.wrox.com
spine=1.152"

Microsoft®
SQL Server 2008 Administration
®
Programmer to Programmer™
with Windows PowerShell™
Get more out of
Professional SQL Server 2008 Administration
ISBN 978-0-470-24796-9
This book is intended for developers, DBAs, and casual users
who hope to administer or may already be administering a SQL
Server 2008 system and its business intelligence features,
WROX.com
such as Integration Services. This book is a professional book,
meaning the authors assume that you know the basics about
how to query an SQL Server and have some rudimentary
concepts of SQL Server already. For example, this book does
not show you how to create a database or walk you through the
installation of SQL Server using the wizard. Instead, the author of
the installation chapter may provide insight into how to use some
of the more advanced concepts of the installation. Although this
book does not cover how to query an SQL Server database, it
does cover how to tune the queries you’ve already written.
Interact Chapters on Demand
Wrox Programmer to ProgrammerTM Microsoft SQL Server 2008 Administration
Take an active role online by participating in Purchase individual book chapters in pdf with Windows PowerShell
ISBN 978-0-470-47728-1
our P2P forums format Professional
Windows If you’re an SQL Server database administrator, this book will
PowerShell™ make your life easier. Windows PowerShell is an administrative
Programming:
Snap-ins, Cmdlets, Hosts, and Providers scripting tool that allows you to automate many tasks you’re
Wrox Online Library Join the Community
probably currently doing by hand. This nuts-and-bolts guide
Arul Kumaravel, Jon White, Naixin Li, Scott Happell, Guohui Xie, Krishna C. Vutukuri
Updates, source code, and Wrox technical support at www.wrox.com shows you how to create Windows PowerShell scripts to
Hundreds of our books are available online Sign up for our free monthly newsletter at administer almost every aspect of SQL Server.
through Books24x7.com newsletter.wrox.com
Professional Windows PowerShell Programming:
Snapins, Cmdlets, Hosts and Providers
Wrox Blox Browse ISBN 978-0-470-17393-0
Enhance Your Knowledge Providing you with the programmer’s approach to understanding
Windows PowerShell, this book introduces the concepts,
Download short informational pieces and Ready for more Wrox? We have books and
Advance Your Career components, and development techniques for building software
code to keep you up to date and out of e-books available on .NET, SQL Server, Java, packages that leverage the power of PowerShell.
trouble! XML, Visual Basic, C#/ C++, and much more!

Beginning Microsoft SQL Server 2008 Administration
ISBN 978-0-470-44091-9
This book teaches you how to develop the skills required to successfully administer an SQL Server 2008 database; no prior
experience is required. The material covers system installation and configuration/architecting, implementing and monitoring
security controls, configuring and managing network communications, automating administration tasks, disaster prevention
and recovery, performance monitoring, optimizing and ensuring high availability, as well as major SQL Server 2008 components
Contact Us. including Integration Services, Reporting Services, Analysis Services, and Service Broker.
We always like to get feedback from our readers. Have a book idea?
Need community support? Let us know by e-mailing [e-mail]
®
Professional SQL Server 2008 Administration with
™
Windows PowerShell
Introduction ..............................................................xxi
Chapter1:WhatIsWindowsPowerShell?................................... 1
Chapter2:WindowsPowerShellCommandTypes,Snap-ins,andcmdlets ... 15
Chapter3:WindowsPowerShellProgramming,Scripting,ErrorHandling,
andDebugging................................................. 43
Chapter4:WindowsPowerShellFunctions,Parameters,Sourcing,
Scopes,andUserProfiles ...................................... 77
Chapter5:WorkingwiththeFileSystem,Registry,andVariables........... 93
Chapter6:WorkingwithEventLogs ......................................123
Chapter7:WorkingwithWindowsServicesandProcesses ................141
Chapter8:WorkingwithWMI ............................................159
Chapter9:WMIProviderforConfigurationManagement...................197
Chapter10:WMIProviderforServerEvents...............................219
Chapter11:WindowsPowerShellinSQLServer2008Environment,
SQLServerPowerShellProvider ..............................241
Chapter12:ManagingPoliciesthroughSQLSERVER:\SQLPolicy..........259
Chapter13:WindowsPowerShellandSMO ...............................283
Chapter14:BuildingSQLServerStandardsandPowerShellCoding
Standards ...................................................317
Chapter15:BuildingSQLServerInventory ................................333
Chapter16:SQLServerInstallation.......................................377
Chapter17:CollectingSQLServerPerformanceandHostPerformance
Data ........................................................401
Chapter18:MonitoringSQLServer .......................................419
Chapter19:MonitoringDiskSpaceUsage,DatabaseFiles,andBackups ..451
Chapter20:DefiningPolicies.............................................475
Chapter21:GeneratingDatabaseScripts.................................487
AppendixA:cmdlets .....................................................507
Index ....................................................................531

Professional
SQL Server® 2008 Administration with
™
Windows PowerShell

Professional
SQL Server® 2008 Administration with
™
Windows PowerShell
Muthusamy Anantha Kumar
Yan Pan
WileyPublishing,Inc.
Professional SQL Server® 2008 Administration with
Windows PowerShell™
Publishedby
WileyPublishing,Inc.
10475CrosspointBoulevard
Indianapolis,IN46256
www.wiley.com
Copyright© 2009byWileyPublishing,Inc.,Indianapolis,Indiana
PublishedbyWileyPublishing,Inc.,Indianapolis,Indiana
PublishedsimultaneouslyinCanada
ISBN:978-0-470-47728-1
ManufacturedintheUnitedStatesofAmerica
10987654321
LibraryofCongressCataloging-in-PublicationData
Kumar,MuthusamyAnantha,1974-
MicrosoftSQLserver2008administrationwithWindowsPowershell/MuthusamyAnanthaKumar,YanPan.
p.cm.
Includesindex.
ISBN978-0-470-47728-1(paper/website)
1.SQLserver.2.WindowsPowerShell(Computerprogramlanguage)I.Pan,Yan,1976-II.Title.
QA76.73.W56K962009
005.2’82--dc22
2009013304
Nopartofthispublicationmaybereproduced,storedinaretrievalsystemortransmittedinanyformorbyany
means,electronic,mechanical,photocopying,recording,scanningorotherwise,exceptaspermittedunderSections
107or108ofthe1976UnitedStatesCopyrightAct,withouteitherthepriorwrittenpermissionofthePublisher,or
authorizationthroughpaymentoftheappropriateper-copyfeetotheCopyrightClearanceCenter,222Rosewood
Drive,Danvers,MA01923,[phone],fax[phone].RequeststothePublisherforpermissionshouldbe
addressedtothePermissionsDepartment,JohnWiley&Sons,Inc.,111RiverStreet,Hoboken,NJ07030,(201)
748-6011,fax[phone],oronlineatwww.wiley.com/go/permissions.
LimitofLiability/DisclaimerofWarranty:Thepublisherandtheauthormakenorepresentationsorwarrantieswith
respecttotheaccuracyorcompletenessofthecontentsofthisworkandspecificallydisclaimallwarranties,including
withoutlimitationwarrantiesoffitnessforaparticularpurpose.Nowarrantymaybecreatedorextendedbysalesor
promotionalmaterials.Theadviceandstrategiescontainedhereinmaynotbesuitableforeverysituation.Thiswork
issoldwiththeunderstandingthatthepublisherisnotengagedinrenderinglegal,accounting,orotherprofessional
services.Ifprofessionalassistanceisrequired,theservicesofacompetentprofessionalpersonshouldbesought.
Neitherthepublishernortheauthorshallbeliablefordamagesarisingherefrom.Thefactthatanorganizationor
Websiteisreferredtointhisworkasacitationand/orapotentialsourceoffurtherinformationdoesnotmeanthat
theauthororthepublisherendorsestheinformationtheorganizationorWebsitemayprovideorrecommendations
itmaymake.Further,readersshouldbeawarethatInternetWebsiteslistedinthisworkmayhavechangedor
disappearedbetweenwhenthisworkwaswrittenandwhenitisread.
ForgeneralinformationonourotherproductsandservicespleasecontactourCustomerCareDepartmentwithinthe
UnitedStatesat(877)762-2974,outsidetheUnitedStatesat(317)572-3993orfax(317)572-4002.
Trademarks:Wiley,theWileylogo,Wrox,theWroxlogo,WroxProgrammertoProgrammer,andrelatedtrade
dressaretrademarksorregisteredtrademarksofJohnWiley&Sons,Inc.and/oritsaffiliates,intheUnitedStates
andothercountries,andmaynotbeusedwithoutwrittenpermission.SQLServerandWindowsPowerShellare
trademarksorregisteredtrademarksofMicrosoftCorporationintheUnitedStatesand/orothercountries.All
othertrademarksarethepropertyoftheirrespectiveowners.WileyPublishing,Inc.,isnotassociatedwithany
productorvendormentionedinthisbook.
Wileyalsopublishesitsbooksinavarietyofelectronicformats.Somecontentthatappearsinprintmaynotbe
availableinelectronicbooks.
About the Authors
MuthusamyAnanthaKumar,akaMAK(MCTS,MCITP:DatabaseAdministrator2008),iscurrentlythe
SeniorSQLServerDatabaseEngineerforaleadingglobalfinancialservicesfirmonWallStreet.Hehas
publishedmanyonlinearticlesasacontributingcolumnistforDatabaseJournal.com.Healsopub-
lishedonlinearticlesforSQL-Server-Performance.comandSQLServercentral.com.Hisarticlescanbe
foundatwww.mssqlengineering.com.HealsoteachesSQLServerDatabaseAdministrationonlineatthe
UniversityofWestFlorida.
MAKhasbeenworkingininformationtechnologyformorethan10years.Hehasworkedinthetechnol-
ogysideofthefinance,dotcom,B2B,andsoftwareindustries.HispastclientsincludeFortJames,Boston
Financial,PCConnection,PurchaseSoft,MerrillLynch,andJefferies.MAKholdsanM.B.A.degreeand
variouscertificationsinthecomputerfield.
HealsoteachesMicrosoftSQLServerandhastaughtmanySQLServerdevelopersandDBAs.Hehas
master’sdegreeinBusinessAdministration.
[e-mail].
YanPan(MCITP,MCDBA,OCA)isaSeniorDBAforaleadingglobalfinancialservicesfirm,where
herdailydutiesincludeadministeringhundredsofSQLServerandOracleserversofeverypossible
version,workingwithbusinessunits,andtroubleshootingdatabaseperformanceproblems.Sheisalsoa
contributingcolumnistforDatabaseJournal.com.Herarticlescanbefoundatwww.yanpansql.com.
Previously,YanworkedasaSQLServerDBAanda.NETdeveloperatDataBasedAds,Inc.,anddevel-
opedtwo.NETWebapplications.Beforethat,sheworkedatAT&TResearchLabs.ShedesignedOLAP
cubeswithSQLServerAnalysisServicesanddevelopedPivotTablereportsforregionalmanagers.She
hasmaster’sdegreesinComputerScienceandPhysics.
YanlivesinChicago,[e-mail].