Professional Microsoft SQL Server 2008 Administration (Wrox Programmer to Programmer)

916 Pages·2008·18.8 MB·English

by  Brian Knight

spine=1.728"
Wrox Programmer to ProgrammerTM Wrox Programmer to ProgrammerTM
Professional
Knight, Patel, Snyder,
Microsoft® LoForte, Wort
SQL Server 2008 Administration
®
SQL Server 2008 represents a significant jump forward in scalability Professional
and performance. Database administrators — and developers as well —
will need to master new methods for effectively managing increasingly
ASM
large and complex SQL Server environments.
i
This author team of SQL Server experts arms you with unique tips, d c
tricks, and workarounds, including managing and monitoring SQL Qr
o
Server, automating administration, clustering, performance tuning, m
s
handling backup and recovery, and more. You’ll learn the various tools
o
that are available to you with the 2008 release, as well as new features Lf
such as Performance Studio, Change Data Capture, Policy-based i t
Management, and Query Governor. With a solid understanding of SQL n ®

Server 2008, you’ll be able to confidently handle even the most difficult
i
SQL Server admin issues. S
s
What you will learn from this book
t
● Best practices for SQL Server 2008 installation e
r Professional
● Tips for troubleshooting and securing the database engine
a
● How to automate SQL Server
r
● Ways to index your database t
● Methods for monitoring your SQL Server instance v
i
● How to performance tune your SQL Server instance o
Microsoft
e ®
Who this book is for
n
This book is for experienced database administrators who are SQL Server 2008
interested in learning best pratices for administering SQL Server 2008. r
®
Enhance Your Knowledge ®
Advance Your Career
2
Administration
Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, 0
developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They
provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.
0 subtitle
8
www.wrox.com
Brian Knight, Ketan Patel, Wayne Snyder, Ross LoForte, Steven Wort
Recommended Database Management ISBN: 978-0-470-24796-9
Computer Book
Categories General
$49.99 USA
$54.99 CAN Updates, source code, and Wrox technical support at www.wrox.com
spine=1.728"
Professional
™
Programmer to Programmer
Microsoft®
SQL Server 2008 Administration
®
Get more out of
Professional Microsoft SQL Server 2008 Integration
Services
978-0-470-24795-2
WROX.com This book shows developers how to master the 2008 release of SSIS, covering topics
including data warehousing with SSIS, new methods of managing the SSIS platform,
and improved techniques for ETL operations.
Professional SQL Server 2008 Reporting Services
978-0-470-24201-8
This book teaches solutions architects, designers, and developers how to use
Microsoft’s reporting platform to create reporting and business intelligence solutions.
Professional Microsoft SQL Server 2008 Analysis Services
978-0-470-24798-3
Interact Chapters on Demand
Professional Microsoft SQL Server 2008 Analysis Services shows readers how to
build data warehouses and multidimensional databases, query databases, and
Take an active role online by participating in Purchase individual book chapters in pdf use Analysis Services and other components of SQL Server to provide end-to-end
solutions.
our P2P forums format
Professional Microsoft SQL Server 2008 Programming
978-0-470-25702-9
Wrox Online Library Join the Community
This updated new edition of Wrox’s best-selling SQL Server book has been expanded
to include coverage of SQL Server 2008’s new datatypes, new indexing structures,
Hundreds of our books are available online Sign up for our free monthly newsletter at manageability features, and advanced time-zone handling.
through Books24x7.com newsletter.wrox.com Professional Microsoft SQL Server 2008 Administration
Enhance Your Knowledge
978-0-470-24796-9
A how-to guide for experienced database administrators, this book is loaded with
Advance Your Career
Wrox Blox Browse unique tips, tricks, and workarounds for handling the most difficult SQL Server
administration issues. The authors discuss data capture, performance studio, Query
Governor, and new techniques for monitoring and policy management.
Download short informational pieces and Ready for more Wrox? We have books and
Beginning Microsoft SQL Server 2008 Programming
code to keep you up to date and out of e-books available on .NET, SQL Server, Java,
978-0-470-25701-2
trouble! XML, Visual Basic, C#/ C++, and much more!
This comprehensive introduction to SQL Server covers the fundamentals and moves on to discuss how to create and change tables, manage
keys, write scripts, work with stored procedures, and much more.
Beginning T-SQL with Microsoft SQL Server 2005 and 2008
978-0-470-25703-6
Beginning T-SQL with Microsoft SQL Server 2005 and 2008 provides a comprehensive introduction to the T-SQL programming language, with
concrete examples showing how T-SQL works with both SQL Server 2005 and SQL Server 2008.
Contact Us.
Beginning Database Design Solutions
978-0-470-38549-4
Beginning Database Design Solutions introduces IT professionals—both DBAs and database developers—to database design. It explains what
We always like to get feedback from our readers. Have a book idea?
databases are, their goals, and why proper design is necessary to achieve those goals. It tells how to decide what should be in a database to
Need community support? Let us know by e-mailing [email protected] meet the application’s requirements. It tells how to structure the database so it gives good performance while minimizing the chance for error.
Knight ffirs.tex V1-10/01/2008 4:16am Pagei
® ®
Professional Microsoft SQL Server 2008 Administration
Introduction .................................................................. xxix
Chapter1:SQLServer2008Architecture ...................................... 1
Chapter2:SQLServer2008InstallationBestPractices ...................... 35
Chapter3:UpgradingSQLServer2008BestPractices ....................... 63
Chapter4:ManagingandTroubleshootingtheDatabaseEngine ............. 97
Chapter5:AutomatingSQLServer........................................... 129
Chapter6:IntegrationServicesAdministrationandPerformanceTuning... 175
Chapter7:AnalysisServicesAdministrationandPerformanceTuning ..... 207
Chapter8:AdministeringtheDevelopmentFeatures ....................... 237
Chapter9:SecuringtheDatabaseEngine ................................... 329
Chapter10:ChangeManagement........................................... 383
Chapter11:ConfiguringtheServerforOptimalPerformance ............... 413
Chapter12:OptimizingSQLServer2008.................................... 455
Chapter13:MonitoringYourSQLServer..................................... 497
Chapter14:PerformanceTuningT-SQL...................................... 555
Chapter15:IndexingYourDatabase ........................................ 605
Chapter16:Replication...................................................... 653
Chapter17:DatabaseMirroring ............................................. 683
Chapter18:BackupandRecovery........................................... 731
Chapter19:SQLServer2008LogShipping.................................. 779
Chapter20:ClusteringSQLServer2008 .................................... 807
Index ......................................................................... 839
Knight ffirs.tex V1-10/01/2008 4:16am Pageii
Knight ffirs.tex V1-10/01/2008 4:16am Pageiii
Professional
Microsoft® SQL Server® 2008
Administration
Knight ffirs.tex V1-10/01/2008 4:16am Pageiv
Knight ffirs.tex V1-10/01/2008 4:16am Pagev
Professional
Microsoft® SQL Server® 2008
Administration
Brian Knight
Ketan Patel
Wayne Snyder
Ross LoForte
Steven Wort
WileyPublishing,Inc.
Knight ffirs.tex V1-10/01/2008 4:16am Pagevi
® ®
Professional Microsoft SQL Server 2008 Administration
Publishedby
WileyPublishing,Inc.
10475CrosspointBoulevard
Indianapolis,IN46256
www.wiley.com
Copyright©2009byWileyPublishing,Inc.,Indianapolis,Indiana
PublishedsimultaneouslyinCanada
ISBN:978-0-470-24796-9
ManufacturedintheUnitedStatesofAmerica
10987654321
LibraryofCongressCataloging-in-PublicationData
MicrosoftSQLserver2008administration/BrianKnight...[etal.].
p.cm.
Includesindex.
ISBN978-0-470-24796-9(paper/website)
1. SQLserver. 2. Databasemanagement. I. Knight,Brian.
QA76.9.D3M573662008
005.75’85—dc22
2008037353
Nopartofthispublicationmaybereproduced,storedinaretrievalsystemortransmittedinanyformorbyany
means,electronic,mechanical,photocopying,recording,scanningorotherwise,exceptaspermittedunderSections
107or108ofthe1976UnitedStatesCopyrightAct,withouteitherthepriorwrittenpermissionofthePublisher,or
authorizationthroughpaymentoftheappropriateper-copyfeetotheCopyrightClearanceCenter,222Rosewood
Drive,Danvers,MA01923,[phone],fax[phone].RequeststothePublisherforpermissionshouldbe
addressedtotheLegalDepartment,WileyPublishing,Inc.,10475CrosspointBlvd.,Indianapolis,IN46256,(317)
572-3447,fax[phone],oronlineatwww.wiley.com/go/permissions.
LimitofLiability/DisclaimerofWarranty:Thepublisherandtheauthormakenorepresentationsorwarranties
withrespecttotheaccuracyorcompletenessofthecontentsofthisworkandspecificallydisclaimallwarranties,
includingwithoutlimitationwarrantiesoffitnessforaparticularpurpose.Nowarrantymaybecreatedorextended
bysalesorpromotionalmaterials.Theadviceandstrategiescontainedhereinmaynotbesuitableforevery
situation.Thisworkissoldwiththeunderstandingthatthepublisherisnotengagedinrenderinglegal,accounting,
orotherprofessionalservices.Ifprofessionalassistanceisrequired,theservicesofacompetentprofessionalperson
shouldbesought.Neitherthepublishernortheauthorshallbeliablefordamagesarisingherefrom.Thefactthatan
organizationorWebsiteisreferredtointhisworkasacitationand/orapotentialsourceoffurtherinformation
doesnotmeanthattheauthororthepublisherendorsestheinformationtheorganizationorWebsitemayprovide
orrecommendationsitmaymake.Further,readersshouldbeawarethatInternetWebsiteslistedinthisworkmay
havechangedordisappearedbetweenwhenthisworkwaswrittenandwhenitisread.
ForgeneralinformationonourotherproductsandservicespleasecontactourCustomerCareDepartmentwithinthe
UnitedStatesat(800)762-2974,outsidetheUnitedStatesat(317)572-3993orfax(317)572-4002.
Trademarks:Wiley,theWileylogo,Wrox,theWroxlogo,WroxProgrammertoProgrammer,andrelatedtradedress
aretrademarksorregisteredtrademarksofJohnWiley&Sons,Inc.and/oritsaffiliates,intheUnitedStatesandother
countries,andmaynotbeusedwithoutwrittenpermission.MicrosoftandSharePointareregisteredtrademarksof
MicrosoftCorporationintheUnitedStatesand/orothercountries.Allothertrademarksarethepropertyoftheir
respectiveowners.WileyPublishing,Inc.,isnotassociatedwithanyproductorvendormentionedinthisbook.
Wileyalsopublishesitsbooksinavarietyofelectronicformats.Somecontentthatappearsinprintmaynotbe
availableinelectronicbooks.
Knight fabout.tex V1-10/01/2008 4:21am Pagevii
About the Authors
BrianKnight,SQLServerMVP,MCSE,MCDBA,istheco-founderofSQLServerCentral.comandJump-
startTV.com.BrianisaprincipalconsultantandownerofPragmaticWorks.HerunsthelocalSQL
Serverusersgroup(JSSUG)inJacksonville,Florida,andwasontheBoardofDirectorsoftheProfes-
sionalAssociationforSQLServer(PASS).BrianisacontributingcolumnistforSQLServerStandard,
maintainsaregularcolumnforthedatabasewebsiteSQLServerCentral.com,anddoesregularwebcasts
atJumpstartTV.HeistheauthorofnineSQLServerbooks.Brianisaspeakeratnumerousconfer-
ences,includingPASS,SQLConnections,andTechEd,andmanycodecamps.Hisblogcanbefoundat
www.pragmaticworks.com.Brianspendsweekendspracticingtobeanamateursurgeonandproceeds
fromthisbookwillhelppayforthemobileCATscanforhisvan.
KetanPatel,B.E.,electronicsengineering,MCSE,MCDBA,isaseniordevelopmentmanagerforthe
BusinessIntelligenceCenterofExcellencegroupatMicrosoft.HehasworkedwithSQLServerandother
Microsofttechnologiesfornearlynineyears.KetanhasalsospokenatTechEd.Hespendshissparetime
withhisfamilyandfriends,playingcricketandwatchingNFL.
WayneSnyderisrecognizedworldwideasaSQLServerexpertandMicrosoftMostValuedProfessional
(MVP),withover25yearsofexperienceinprojectmanagement,databaseadministration,software
design,performancemeasurement,andcapacityplanning.Heisapopularconsultant,trainer,writer,
andspeaker,andproducesaseriesofWeb-basedseminarsonSQLServer2005.Waynehasedited
manySQLServerbooks,hasSQLTrainingCDswithLearnkey,ispresidentofPASS,theProfessional
AssociationforSQLServer(www.sqlpass.org),playskeyboardforacoverbandnamedSoundBar-
rier(www.soundbarrierband.com),andisamanagingconsultantforMariner,aBusinessIntelligence
Company(www.mariner-usa.com).
RossLoForteisatechnicalarchitectattheMicrosoftTechnologyCenterChicago,focusedonMicrosoft
SQLServersolutions.Rosshasmorethan16yearsofexperienceinbusinessdevelopment,projectman-
agement,anddesigningSQLarchitecturesolutions.Forthepasteightyears,Rosshasbeenworkingwith
theMicrosoftTechnologyCenters,andhasledarchitecturedesignandproof-of-conceptengagements
forMicrosoft’slargestandmoststrategiccustomerstodesignenterprise,mission-criticalSQLServer
solutions.RossisaSQLServerinstructoratDePaulUniversityinChicago,andregularlypresentsat
TechEd,SQLPASS,Gartner,TDWI,andotherconferences.Apublishedauthor,hehasbeenactivewith
theProfessionalAssociationforSQLServer,theChicagoSQLServerUsersGroup,andtheSQLServer
communityformanyyears.
StevenWorthasbeenworkingwithSQLServerforthepast14years.Hespentmuchofthattimeworking
asafreelanceapplicationdeveloperanddatabasearchitectbuildingVBandWebapplicationsonSQL
ServerformanyofLondon’slargestfinancialinstitutions.HemovedtotheUnitedStatesnineyearsago,
joiningMicrosoftoversevenyearsago.AfterthreeyearsworkinginPSSontheSIEteam,hemovedover
totheSQLServerProductgroup,spendingayearworkingonSQLServerscalability,followedbyayear
workingwiththeSQLPlaybackteam.TwoyearsagoStevenmovedtotheWindowsdivision,wherehe
startedworkontheSQLServersideoftheWatsonsystem.Whenheisnotinvolvedwithtechnology,
StevencanbefounddoingsomethingactivesomewhereoutdoorsinthePacificNorthwest.
Geoff Hiten, a Microsoft MVP, is a senior SQL Server consultant based in Atlanta. Geoff began
workingwithSQLServerin1992withversion4.2andhasusedeveryversionsince.Hespecializesin
high-availabilityandhigh-performanceSQLsystems.Recentprojectsinclude:systemupgrades,SQL
Serverplatformmigrations,performancetuning,customreportingsolutions,anddatabasestrategy
implementations.GeoffiscurrentlyontheleadershipteamoftheAtlantaareaMicrosoftDatabaseForum
Knight fabout.tex V1-10/01/2008 4:21am Pageviii
About the Authors
Usersgroup(AtlantaMDF)andcanbefoundlurkinginthehallsatPASS(ProfessionalAssociation
forSQLServer)CommunitySummitevents.Inaddition,GeoffhasbeenaSubjectMatterExpertfor
MicrosoftTrainingCurriculummaterialsandhasauthoredarticles,whitepapers,andpodcastsonSQL
Server.
K.BrianKelley(MCSE,Security+,CISA)isasystemsandsecurityarchitectforAgFirstFarmCredit
Bank.AtAgFirstheprovidesinfrastructureandsecurityguidancewithrespecttoWindows-basedtech-
nologiesincludingActiveDirectory,InternetInformationServer,andMicrosoftSQLServer.Brian,author
ofStarttoFinishGuidetoSQLServerPerformanceMonitoringandcontributingauthorforHowtoCheatat
SecuringSQLServer2005,isaregularcolumnistandbloggeratSQLServerCentral.comfocusingprimarily
onSQLServersecurity.HeisalsoafrequentcontributorforSQLServerStandardMagazine.Brianisa
memberoftheProfessionalAssociationofSQLServer(PASS)andtheInformationSystemsAuditand
ControlAssociation(ISACA).HeisalsoactiveintheMidlandsPASSchapter,anofficialPASSchapter
forSouthCarolina.
viii