capacityserver2008

TableofcontentsIntroduction.
3NewfeaturesofSQLServer2008x643InstallingandsettingupWindowsServer2003R2Enterprisex64andSQLServer2008x64inthisenvironment4Definingourenvironment4InstallingWindowsServer2003R2Enterprisex646Configuringthedrives9InstallingSQLServer2008x6411Nextsteps:BestpracticesforadministeringandmonitoringSQLServer2008x6416AdministeringSQLServer2008x64inthisenvironment.
.
.
.
.
.
.
16AUTOGROWTH17AUTOCLOSE.
18Onetempdbdatafilepercore18Processorandmemoryparameters19NotesonthenewSQLServer2008x64compressioncapabilities19MonitoringSQLServer2008x64inthisenvironment.
20CountersavailablefromPerformanceMonitor20Dynamicmanagementviewsandfunctions.
21PerformanceDataCollectorandManagementDataWarehouse23Summingup27AppendixA.
Methodology:InstallingWindowsServer2003R2Enterprisex6429Configuringdatadrives.
30Maximizethroughputfornetworkapplications.
31AppendixB.
Methodology:InstallingSQLServer2008x6432UsingtheALTERDATABASEcommandtomodifythelocationofuserdatabaseortempdbfilesandlogsafteraninstallation.
34SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge29503IntroductionTheDellPowerEdge2950isanexcellentserverfordeployingSQLServer2008x64,thelatestreleaseofMicrosoft'shighlyavailableandhigh-performingdatamanagementplatform.
ThisDeploymentGuidereviewstheapproachthatPrincipledTechnologies(PT)andDellrecommendforimplementingatestedandvalidatedsolutionforSQLServer2008x64ontheDellPowerEdge29509thgenerationserverandMicrosoftWindowsServer2003EnterpriseR2x64.
PThasperformedhands-ontestingandresearchanddrawnonreal-worldexperiencestodocumentbestpracticesandhelpsystemsadministratorsanddatabaseadministratorssimplifyoperationsandtakeadvantageofnewfeaturesinSQLServer2008x64.
NewfeaturesofSQLServer2008x64SQLServer2008x64introducesawidevarietyofnewfeatures.
WhiletherearefartoomanytodiscussinthisGuide,thefollowinglistmentionssomeofthemoresignificantones:Policy-basedmanagement.
SQLServer2008x64letsyouautomaticallyenforceandmonitorpoliciesfordatabaseoperations.
Youcanalsopushthesepoliciesouttoyourservers.
ResourceMonitor.
TheResourceMonitorallowsyoutomonitorwhichusersarerunningwhichqueriesandhowlongthosequeriesrun.
Thisabilityhelpsyoutobettertuneyourserverforoptimalperformance.
ResourceGovernor.
TheResourceGovernorletsyoulimittheresourceconsumptionofincomingrequestsbyclassifyingincomingconnectionsasspecificworkloadtypesanddefininglimitsforthosetypes.
Youcanalsoredefinethelimitsinrealtime.
Backupcompression.
SQLServer2008x64canautomaticallycompressdatabasebackups,afeaturepreviouslyonlyavailableviathepurchaseofthird-partysoftwareproducts.
Performancedatacollection.
SQLServer2008x64letsyoustoreperformancedatainacentralizeddatabase.
Italsoprovidesenhancedtoolsforanalyzingandreportingonsuchperformancedata.
DellPowerEdge2950SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge29504FILESTREAMdata.
ThenewSQLServer2008x64FILESTREAMdatatypeallowsyoutostorelargebinarydata,suchasdocumentsorimages,directlyinanNTFSfilesystem.
Thedataobjectremainspartofthedatabaseandmaintainstransactionalconsistency.
ApplicationscanaccessthedataviaanNTFSstreamingAPI.
"Hotadd"CPUs.
OnsupportedhardwareplatformsyoucannowaddoneormoreCPUswhileSQLServer2008x64isrunning.
AswithSQLServer2005,youcanalsohotaddmemory.
InstallingandsettingupWindowsServer2003R2Enterprisex64andSQLServer2008x64inthisenvironmentInthissection,wefocusoninstallingandsettingupWindowsServer2003R2Enterprisex64andSQLServer2008x64ontheDellPowerEdge2950server.
Webeginbydefiningtheenvironmentweusedforoursamplesetupandthenaddressconfiguringtheserver'sdrives.
DefiningourenvironmentBESTPRACTICE:Usethelatesttestedandvalidatedsoftware,firmware,anddriverversionsforNICs,storagearrays,andothercomponents.
YoucanfindthesesoftwarecomponentsintheSolutionsDeliverableList(SDL)atwww.
dell.
com/sql.
Figure1presentsthesetupweusedinourhands-ontestingandresearchforthisGuide.
TheheartofthesetupwasaDellPowerEdge2950withthefollowingcomponents:twoIntelXeonProcessorE5335(Clovertown)processors,eachrunningat2.
0GHz16GBofPC2-5300FRAMthree73.
0GB,15,000rpm,SeagateCheetahST373455SSSASharddisksDellPERC5/iintegratedRAIDcontrollertwoBroadcomBCM5708CNetXtremeIIGigEintegratedNICsWeconnectedtheservertoanexternalActiveDirectorydomaincontrollerviaagigabitswitch.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge29505BESTPRACTICE:DeploySQLServer2008x64inanActiveDirectorydomainanduseWindowsauthentication.
DoingsoallowsyoutotakeadvantageoftheintegratedsecurityandcentralizedmanagementfeaturesofActiveDirectory.
WeinstalledWindowsServer2003R2Enterprisex64usingtheDellOpenManageServerAssistant8.
10.
0fromtheDellPowerEdgeInstallationandServerManagementBootableCDversion5.
2.
ThenweappliedServicePack2beforeinstallingSQLServer2008x64.
TheversionofSQLServerweusedwastheNovemberCommunityTechnologyPreview(CTP),whichidentifieditselfas:MicrosoftSQLServercodename"Katmai"(CTP)-10.
0.
1075.
23(X64)Nov8200714:13:37.
ThiswasthelatestversionofSQLServer2008x64availableasofthestartofourworkonthisGuide.
ActiveDirectoryserverDellPowerEdge2950serverGigabitswitchFigure1.
Thesetupweusedinourhands-ontestingandresearchforthisDeploymentGuide.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge29506InstallingWindowsServer2003R2Enterprisex64Thissectionprovidesanoverviewoftheoperatingsysteminstallationprocess.
Weincludeapproximatewaittimesforeachstep.
NOTE:Planonatleast90minutesforinstallingWindowsServer2003R2Enterprisex64ontheDellPowerEdge2950server.
Eachstepbelowtakesatleastaminute.
Weputtheapproximateamountoftimeeachsteptakesinparenthesesattheendofthedescriptionofthatstep.
Thosetimesbelowexcludedataentrytime.
Thetimetoinstallupdates,whichwas10minutesinoursetup,willincreaseovertimeasMicrosoftreleasesadditionalOSupdates.
1.
InserttheDellPowerEdgeInstallationandServerManagementbootableCDversion5.
2,andrebootthesystem.
Note:Duringthisprocess,thesoftwarewillaskifyouwanttoinsertareplicationfloppy;wedidnot.
(5minutes,duringwhichthesystembootstwice.
)2.
OntheWelcometotheDellOpenManageServerAssistantscreen,clickCickHereforServerSetup.
Setthedateandtime.
SelectMicrosoftWindowsServer2003ServicePack1/ServicePack2x64Edition.
SkipRAIDconfiguration.
Setthebootpartitionsizetothemaximumavailable–69335MBandleavetheFilesystematNTFS.
EntertheIPaddressesandselectthecorrectsubnettype.
Enteryourconfigurationinformation.
(6minutes)Figure2.
DellOpenManageServeraskingfortheWindowsinstallationCD.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295073.
Whenprompted,inserttheMicrosoftWindowsServer2003CD.
(5minutes)Figure3.
OperatingSystemFilescopied,readytostarttheinstall.
4.
Whenprompted,removetheCDfromthesystem.
ClickFinish.
Theinstallationbegins.
(27minutes,duringwhichthesystemreboots4times.
)5.
Whenthesystemcomesavailable,loginasAdministrator,andinsertthesecondWindowsserver2003CD.
InsertitandclickOK.
(2minutes)6.
InstallSP2.
WedidthisfromaCD,acceptingthelicenseagreementandthedefaultoptions.
(3minutes)SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge29508Figure4.
InstallingServicePack2.
7.
Rebootsystem(5minutes)8.
RunWindowsupdate.
Weinstalledallessentialupdates.
WhentheinstallationsoftwarepromptedustoinstallInternetExplorer7,wedeclined.
(Note:Thetimewillvary;inourcase,itwas10minutes.
)Figure5.
Windowsupdatesexaminingtheserver.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge29509BESTPRACTICE:ConfigurealldatabaseserverswithstaticIPaddresses.
ThispracticeassuresthatSQLServer2008x64resourceswillremainavailableevenintheeventofaDHCPserverfailure.
ItalsoincreasesthestabilityofyournetworkingandDNSenvironments.
BESTPRACTICE:UseCNAMErecordstoassignaliasestoyourdatabaseservers.
Thispracticeletsyouisolateyourusersandapplicationsfromchangesintheunderlyingserverinfrastructure.
Suchisolationcanmakedeploymentsandmigrationssimplerandmorerobust.
ConfiguringthedrivesThenextstepistoconfiguretheremainingdrivesinyourserver.
FormatallSQLServervolumes,includingthoseyouusetoholdyourdata,logs,andtemporarydatabase(tempdb)asNTFSfilesystem.
Microsoftrecommendstheallocationunitsizeforthesevolumesbe64KB.
Avoidvalueslessthan8KB,astheycanincreasetheriskoftornpages(i.
e.
,pageswhosecontentsaresplitacrossdiskallocationunits).
AppendixApresentsdetaileddriveconfigurationsteps.
NOTE:Ideally,theoperatingsystemwouldalsobeonitsowndrive.
Inthisthree-drivecase,wemusteitherhavetwooftheSQLServercomponentsshareadriveorputacomponentonthedrivewiththeoperatingsystem.
Wechosetoputthelogfilesonthedrivewiththeoperatingsystem.
Theoperatingsysteminstallationsetstheallocationunitsizeto4KB,insteadofthe64KBMicrosoftrecommends.
WhileitispossibletousetherecoveryconsoletoformattheOSdrivetoa64KBallocationunitsize,wedonotrecommendthispracticeduetothepotentialnegativeeffectontheperformanceofOSfunctions.
Donotusecompresseddrives.
WindowsServer2003EnterpriseR2x64doesnotoffertheoptiontocompressdriveswhentheallocationunitsizeis64KB.
Moreover,SQLServer2008x64willcreateonlyread-onlydatabasesoncompresseddrives,whichmakessuchdrivesusefulonlyforhistoricaldatabases.
Fortunately,SQLServerincludesbuilt-incompression,whichwediscussbelow.
UseSQLServer'sbuilt-incompressionwhenyourblendofperformanceandcapacityneedssuggestthatitmakessensetodoso.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295010Forthediscussionbelow,wewillviewSQLServer2008x64asusingthreespecificstorageareas:thetemporarydatabasefile(tempdb),thelogfiles,andthedatafilesfordatabasesyoucreate.
Wemakethisdivisionbasedonthedifferentcharacteristicsofthesetypesoffiles.
Theactualsituationismorecomplicated,ofcourse:thetempdbisoneofseveralsystemdatabases,albeitthemostvolatileone,andthereisatleastonelogfileforeverydatabase.
OnepartoftheinstallationprocessforSQLServer2008x64iscreatingthetempdbsystemdatabase,whichalldatabasesontheservershare.
SQLServerusesthestorageofthetempdbdatabaseforsuchpurposesassorting,buildingaggregatesforGROUPBYorORDERBYclauses,composingthetemporarytablesresultingfromJOINstatements,queryingusingDISTINCT,andstoringcursors.
Eachdatabasehasoneormoretransactionlogfiles.
AtransactionlogfilestoresthedetailsforeachupdatetoaSQLServerdatabaseandthedetailsofthetransactionsthatperformedeachupdate.
Thisinformationiscriticalforrestoringdatabaseconsistencyintheeventofafailure.
Thedatafilescontainthedataandobjectsforeachdatabase.
Theircontentsincludedatabaseobjects,suchastables,indexes,andstoredprocedures,thatyoucandefine.
BESTPRACTICE:Separatethetempdbandtransactionlogfilesontotheirowndisksonseparatediskgroupswhenpossible.
DoingsocanhelpyieldbetterI/Operformancebyensuringthatthesefilesdonotsharethesamephysicaldisks.
Ofcourse,insomecircumstances,suchasconsolidationoraserverwithfewdisks(asinthisGuide),suchtotalisolationisnotpossible.
Likewise,whenpossible,createfull-textcatalogsontheirownphysicaldrive(s).
Forconsolidation,whenpossiblegroupfileswithsimilarI/Ocharacteristics.
Forexample,consolidatealllogs.
BecauseheterogeneousworkloadsmayhaveverydifferentandevencompetingI/Ocharacteristics,combiningheterogeneousworkloadscanhurtoverallperformance.
Toachieveoptimalperformancethatscaleswithheavyworkloads,MicrosoftrecommendsthatthenumberofdatafilesforaSQLServer2008x64databaserangefrom0.
5to1foreachCPUcoreSQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295011ontheserver.
Youdo,however,payacostforhavingmultiplefilesinafilegroup.
Ifthesefilessharespindles,theservercanexperiencecontentionwhenmultipledatabaseprocessesattempttoaccessthemsimultaneously.
Therefore,youmustbalancethepossibilityofcontentionformultiplefilesonaspindleagainstthedemandfordatatosatisfytheprocessorcores.
Inthiscase,considerbreakingthedatafilesintoanumberoffilesequaltohalfthenumberofcores.
Inthecaseofthetempdb,useonedatafilepercore.
NOTE:YoucanaddormodifydatafilesandfilegroupsintheSQLServerManagementStudiobyright-clickingonthedatabaseinquestion,selectingProperties,andthenclickingFilesorFilegroups.
InstallingSQLServer2008x64Asweexplainedabove,weranourActiveDirectoryonaseparateserver.
WehighlyrecommendagainstdeployingSQLServeronanyActiveDirectorycontroller.
ActiveDirectoryprocesseswilladdoverheadthatcouldadverselyaffecttheperformanceofSQLServer.
Youshould,however,deploySQLServeronamemberserverinanActiveDirectorydomain.
DonotmaketheSQLServerservicedomainaccountsmembersoftheDomainAdministratorsgroup.
Infact,grantonlythenecessaryrightsonthelocalservertotheSQLServerserviceaccountaspartofyourpre-installationchecklist.
TheSQLServerinstallationsoftwarewillcreatethelocalgroupsitneedsforitssecuritypurposes.
FormoreinformationontheWindowsDomainaccountsyouneedforSQLServerserviceaccounts,pleaseseeBooksOnlineatmsdn2.
microsoft.
com/en-us/library/ms143504(SQL.
100).
aspx#Review_NT_rights.
ThissectionprovidesanoverviewoftheSQLServer2008x64installationprocess.
Weincludeapproximatewaittimesforeachstep.
AppendixBprovidesfull,detailedinstallationinstructions.
AppendixBalsoincludesinstructionsforcreatingauserdatabaseandinstructionsforusingtheALTERDATABASEcommandtomoveanexistingdatabase.
NOTES:WeinstalledSQLServer2008usinganISOimageweburnedtoaDVD.
Microsoft'sdownloadpagefortheNovemberCTPofSQLServer2008offerstwooptions:aself-extractingexecutable,whichenablesyoutoinstallwithoutaDVDburner,andtheDVDISOimage.
MicrosoftdidnotSQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295012recommendonemethodovertheother.
WeusedtheDVDapproachbecausewehavefoundittobeconvenientwheninstallingmultipletimesonmultiplesystems.
Ifyouusetheself-extractingexecutable,yourtimesmightvary.
Planonatleast30minutesforinstallingSQLServer2008x64ontheDellPowerEdge2950server.
Eachstepbelowtakesatleastaminute.
Weputestimatedtimesinparenthesesattheendofeachstep.
Thosetimesexcludedataentrytime.
1.
InserttheSQLServer2008x64DVDintotheDVDdrive.
ClickServercomponents,Tools,BooksOnline,andSamples.
2.
Acceptthelicenseagreementfor.
Net2.
0andinstallit.
(6minutes)Figure6.
Installing.
NETFramework2.
0.
3.
Acceptthelicenseagreement;andinstalltheprerequisites.
(1minute)SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295013Figure7.
InstallingSQLServer2008x64prerequisites.
4.
ClickNewInstallationtolaunchtheInstallingSQLServer2008Wizard.
Thesystemconfigurationcheckcompletesalmostinstantly.
Selectthefeaturesyouneedtoinstall.
Forabasicinitialinstallation,installtheDatabaseEngineandClientTools,aswedid.
Youcaninstalladditionalcomponentslaterasnecessary.
Figure8.
SelectingSQLServer2008x64features.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge2950145.
Onthefollowingscreens,entertheaccountinformationanddatafilelocations.
Install.
Attheconfirmationscreen,clickNext.
(11minutes)Figure9.
Settingfilelocations.
6.
AttheCompletescreen,notetheSummarylogfilelocation,andclickClosetocompletetheinstall.
BecausewewereinstallingaCommunityTechnologyPreview(CTP)releaseofSQLServer2008x64,apreliminaryversionMicrosoftreleasestothetechnicalcommunity,therewerenoupdatestodownload.
WhenyouinstallthereleaseversionofSQLServer2008x64,aftertheinstallationprocesscompletesyoushouldchecktheMicrosoftDownloadsCenter(http://www.
microsoft.
com/downloads)forthelatestSQLServerpatches,updates,andservicepacks.
BESTPRACTICE:AfteryoucompletetheSQLServerinstallation,givetheSQLServerserviceaccounttherighttopreventtheoperatingsystemfrompagingitsmemorytodisk.
SQLServerwilldynamicallyallocateanddeallocatememorytorelievememorypressureandswapping.
Anotherprocess,however,canalwaysrequestasubstantialamountofmemoryandthuscausetheOStoswapsomeofSQLServer'smemorytodiskbeforeSQLServerhasthechancetoreact.
Thissettingstopsthatproblemfromoccurring.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295015WindowsServer2003EnterpriseR2x64providesasettingthatallowsSQLServertoretainitsdatainphysicalmemoryinsteadofswappingthedatatovirtualmemory,thepagefile,ordisk.
Toenablethissetting,givetheaccountrunningtheSQLServer2008x64servicetherightto"Lockpagesinmemory.
"Todoso,followthesesteps:1.
ClickStart.
2.
GotoAdministrativeTools|LocalSecurityPolicy.
3.
ClickLocalPoliciesClickUserRightsAssignment.
4.
ScrolldowntoLockpagesinmemory,anddouble-clickit.
ClickAddUserorGroup,andentertheSQLServerserviceaccountname.
Figure10.
SelectingtheLockpagesinmemorysetting.
BESTPRACTICE:Whenpossible,leavetheMinimumservermemoryandtheMaximumservermemoryattheirdefaultsof0and2147483647,respectively.
ThispracticeallowsSQLServer2008x64touseasmuchmemoryasthesystemmakesavailable.
Ifyoumustchangethesesettings,makesurethatthesumofthemaximummemorysettingsacrossallprocessesislessthantheamountofphysicalRAMavailable.
BESTPRACTICE:EnableInstantFileInitialization.
ThedefaultbehavioristoinitializethestoragewithzeroswheneverSQLServercreatesadatafileorallocatesanSQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295016extenttogrowadatafile.
(SeethediscussionofAUTOGROWTHbelow.
)Zero-fillingstoragecanbeverytime-consuming.
WhenyouenableInstantFileInitialization,thesystemwillnotinitializestorageitallocates.
Instead,thestoragewillremainuninitializeduntilSQLServerwritesdatatoit.
Microsoft'sin-housetestingshowsaradicalperformanceimprovementwhenusinginstantdatafileinitialization.
Toenableinstantfileinitialization,youmustgivetheSE_MANAGE_VOLUME_NAMEpermissiontotheWindowsaccountunderwhichyouruntheSQLServer2008x64service.
Todoso,allowtheaccountto"Performvolumemaintenancetasks"withthefollowingsteps:1.
ClickStart.
2.
GotoAdministrativeTools|LocalSecurityPolicy.
3.
ClickUserRightsassignment.
4.
ScrolldowntoPerformvolumemaintenancetasks,anddouble-clickit.
5.
ClickAddUserorGroup,andentertheaccountname.
Nextsteps:BestpracticesforadministeringandmonitoringSQLServer2008x64Theoptimalperformanceofyourdatabaseserverdependsonmorethanasuccessfulinstallation.
TherunparametersforSQLServer2008x64andthedatabaselayoutrequiremuchcarefulthought.
Inthissection,wepresentafewbestpracticesthatwillimproveperformanceandreliability.
AdetaileddiscussionofsuchpracticesmayfollowinalaterGuide.
AdministeringSQLServer2008x64inthisenvironmentInthissection,wediscussthefollowingtipsingreaterdetail:SelectyourAUTOGROWTHsizesappropriately:smallenoughtominimizetheeffectonperformance,butlargeenoughtominimizefragmentation.
DonotuseAUTOGROWTHasasubstituteforsizingyourdatafilesappropriately.
LeaveAUTOCLOSEatitsdefaultvalueofFalse.
Createonetempdbdatafilepercore.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295017Leaveprocessorandmemoryparametersattheirdefaultsunlessyouhaveaspecificreasonforchangingthem.
AUTOGROWTHTothegreatestdegreereasonablypossible,sizealldata,tempdb,andlogfilesappropriately.
Ideally,planfilegrowthandmanuallyexpandfilesduringperiodswhentheserverisrelativelyidle.
DonotdependonAUTOGROWTHtosizefilesforyou.
UsingAUTOGROWTHcancreatefilefragmentationandaffectsystemperformance.
(Fileexpansionalwaysaffectsperformance.
)WhileinstantfileinitializationcanhelpreducethenegativeperformanceeffectoftheAUTOGROWTHfeature,itdoesnoteliminatetheproblem.
Inaddition,instantfileinitializationwillnothelpreducefilefragmentationatall.
UseAUTOGROWTHonlyasasafetynettopreventdatafilesfromfillingupandforcingthedatabasetoaread-onlystate.
TheAUTOGROWTHincrementshouldbebothsmallenoughtolimittheperformanceeffectofunplannedfilegrowthandlargeenoughtopreventexcessivefilefragmentation.
Whileitisimpossibletorecommendaspecificsizethatwillworkwithallapplications,wecanrecommendthatyousettheincrementusingfixedsizes,eitherinmegabytesorgigabytes,ratherthanpercentages.
Asafilegrows,agivenpercentageofitssizealsogrowsinabsoluteterms,sousingpercentageincrementscouldleadtouncontrolledfilegrowthandanunacceptableimpactonperformance.
Logfilesraiseanadditionalissue.
Withinaphysicallogfile,therearemultiplevirtuallogfiles.
Avirtuallogcannotspanfileextents.
Thus,ifyousettheAUTOGROWTHincrementat5MB,youenduplimitingthemaximumsizeofavirtuallogto5MB,whichcanmakecertainlargetransactionsimpossibletocomplete.
EvenwhenyouhavesettheAUTOGROWTHincrementappropriately,manuallygrowingthefilesduringperiodsoflowactivityisstillpreferable.
NOTE:YoucanmodifyAUTOGROWTHsettingsintheSQLServerManagementStudiobyright-clickingonthedatabaseinquestionandselectingProperties.
ClickFiles,andscrolltotheAUTOGROWTHcolumn.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295018AUTOCLOSELeavetheAUTOCLOSEoptionatitsdefaultvalueofFalse.
Foradatabaseinfrequentuse,AUTOCLOSEcancausemanyunnecessaryopensandcloses,whichcandegradeperformance.
NOTE:YoucanmodifyAUTOCLOSEsettingsintheSQLServerManagementStudiobyright-clickingonthedatabaseinquestionandselectingProperties.
ClickOptions,andscrolltotheAUTOCLOSErow.
OnetempdbdatafilepercoreWhenyouinstallSQLServer2008x64,theinstallationprocesscreatesasingletempdbprimarydatafile.
Runningwithasingletempdbdatafile,however,cancreateunacceptablelatchcontentionandI/Operformancebottlenecks.
Tomitigatetheseproblems,allocateonetempdbdatafileperprocessorcore.
(Thisisdifferentthanthepracticeforuser-defineddatabasefiles,wherewerecommend0.
5to1datafilepercore.
)Tocheckthelevelofcontentionyouareexperiencinginthisarea,followthesesteps:1.
StarttheReliabilityandPerformanceMonitor.
2.
ClickPerformanceMonitor,thenright-clicktherightgraphpane,andselectAddcounters.
3.
ScrolldownuntilyoufindSQLServer:Latches.
Thereareanumberoflatch-relatedcountersfromwhichtochoose.
Bydefault,AUTOGROWTHissettoTRUEfortempdbfiles.
Aswithdatafiles,however,expandingtempdbtoofrequentlycancauseperformancetodegrade.
Therefore,weagainrecommendallocatingthetempdbfilesenoughinitialspacetoaccommodatetheexpectedworkload.
Asasafetynet,setthefilegrowthincrementlargeenoughtominimizetempdbexpansions.
Bydefault,theFILEGROWTHincrementis10percentforthetempdbfile,withanunrestrictedgrowthincrementof10percent.
Althoughwerecommendafixedincrementfordatafiles,Microsoftrecommendsthe10percentincrementfortempdb.
Tobetterutilizetheallocationmechanism,makealltempdbdatafilesequalinsize.
BecauseSQLServerusesaproportionalfillalgorithm,differentlysizedfilesdonotdistributetheI/Oloadevenly.
NOTE:YoucanaddormodifytempdbdatafilesandAUTOGROWTHoptionsintheSQLServerManagementSQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295019Studiobyright-clickingonthetempdbdatabase,selectingProperties,andthenclickingFiles.
ProcessorandmemoryparametersLeavetheprocessorandmemoryparametersforSQLServerattheirdefaultsettings.
ThedefaultsettingsrunSQLServeratastandardpriority,whichletsitusealltheprocessorsintheserverandalsomakesavailableasmuchRAMasSQLServerneeds.
Ifyouhavespecialneeds,youhavetheoptionofchangingthesesettings.
Thedefaults,however,workbestformostinstallations.
Similarly,leavethenetworkpacketsizeatthedefaultvalueof4096bytes.
Insomecases,suchasbulkcopyoperationsorlargeimagefiles,increasingthepacketsizecanimproveefficiency.
Youshouldnot,however,changethepacketsizeunlessyouaresurethatdoingsowillimproveefficiency.
Finally,wheneverpossiblerunSQLServer2008x64ondedicatedservers.
Withtheexceptionofnecessaryutilities,runnoapplicationsonyourserverotherthanSQLServer.
OtherapplicationswillhurtperformancebycompetingwithSQLServerforresources.
Also,eachadditionalapplicationmakesithardertotunetheserverforoptimaldatabaseperformanceortotroubleshootproblems.
NotesonthenewSQLServer2008x64compressioncapabilitiesAsweexplainedearlier,forSQLServer2008x64installationsneverusethediskcompressionfeatureofWindowsServer.
Infact,SQLServer2008x64willletyoucreateread-onlydatabasesoncompresseddrives.
(Thatabilitycan,ofcourse,proveusefulforhistoricaldatabases).
Moreover,WindowsServer2003graysouttheoptiontocompressadrivewhentheallocationunitsizeissetto64KB,whichisthesizewerecommendabove.
Thatsaid,SQLServerincludesitsownbuilt-incompressionoptionsandtherearecircumstanceswhereusingitmakesverygoodsense.
SQLServer2008x64actuallyprovidestwocompressionfeatures:backupcompressionanddatacompression.
Backupcompressionusesstandardfilecompressiontechniques.
Earlybenchmarkinghasdemonstratedcompressionratiosinthe5:1range,though,asalways,thecompressionlevelyouachievewilldependonyourSQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295020dataset.
Usecompressedbackupsiftheymakesenseforyouroperation.
InSQLServer2008x64,theStorageEnginetransparentlymanagescompressionattherowlevelandthepagelevel.
(Becausepagescontainrows,page-levelcompressionimpliesrow-levelcompression.
)Youcanenablecompressiononatableorindex,orevenapartofatableorindex.
Aswithanycompression,thecompressionratioandtheCPUoverheadwilldependonthecharacteristicsofyourparticulardata.
CAUTION:Donotcompresslogfiles.
Doingsocannegativelyaffectbothperformanceandreliability.
MonitoringSQLServer2008x64inthisenvironmentOncetheserverisupandrunning,you'llwanttomonitoritsperformancesoyoucanbettertuneitforyourspecificworkload.
Inthissection,wediscusssometraditionalperformancemonitoringoptionsandSQLServer2008x64'snewPerformanceDataCollector.
ManystatisticsareavailablefrombothWindowsandSQLServer.
Werestrictourdiscussiontoafewthatwehavefoundparticularlyuseful.
(MoredetaileddiscussionsofmonitoringmaybethetopicofafutureGuide.
)WebeginwithcountersfromtheOS.
CountersavailablefromPerformanceMonitorToseetheperformancestatisticsavailableattheoperatingsystemlevelinWindowsServer2003,followthesesteps:1.
ClickStart2.
GotoAdministrativeTools|Performance.
3.
Right-clicktherightpaneofPerformanceMonitor,andselectAddcounters.
4.
SelecttherelevantPerformanceObjectfromthedropdownlist.
5.
Scrolldowntotherelevantstatistic.
6.
Selectfromthelist,clickAdd,andthenclickClose.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295021CounterStatisticExplanationPhysicalDisk%idletimeIndicatorofI/Operformance;ideally>20%Processor%processortimeIdeally95%Figure11.
DatarangeswerecommendforselectPerformanceMonitorcounters.
DynamicmanagementviewsandfunctionsDynamicmanagementviewsandfunctions(DMV/DMFs)exposethecurrentstateoftheSQLServersystem.
YoucanusethestatisticsyoucollectfromaDMV/DMFquerytoanalyzeserverhealthanddiagnosepotentialproblems.
Someofthesestatisticsapplytotheentireserver,whileothersapplytospecificdatabases.
SQLServer2008x64offersover100DMV/DMFs.
SomeofthemoreinterestingDMV/DMFsincludethefollowing:sys.
dm_db_index_physical_stats.
Thisfunctionreturnsphysicalcharacteristicsofindexes,e.
g.
,fragmentationpercentage,pagecounts,androwcounts.
sys.
dm_db_index_usage_stats.
Thisviewreturnsinformationondifferenttypesofindexoperations(scan,seek,etc.
),suchaswhenthesystemlastperformedthemandhowmanytimesitdidso.
sys.
dm_os_performance_counters.
ThisviewreturnsthecurrentvalueofsomeSQLServerperformancecounterstheservermaintains.
sys.
dm_io_virtual_file_stats.
ThisfunctionreturnstheI/Ostatisticsfordataandlogfiles.
sys.
dm_resource_governor_resource_pools.
ThisnewSQLServer2008x64viewreturnsinformationaboutthecurrentresourcepoolstate,theconfigurationofresourcepools,andotherresourcepoolstatistics.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295022Figure12.
Sampleresultsfromthefollowingquery:sys.
dm_os_performance_counters.
ToqueryaDMV,usethefollowingsteps:1.
OpenSQLServerManagementStudio.
2.
Entertheappropriateservername,entercredentialsasnecessary,andclickConnect.
3.
ExpandtheDatabasesfolderintheObjectExplorer.
4.
Right-clickadatabasename,andselectNewQuery.
5.
QuerythedesiredDynamicManagementViewbyrunningthefollowingstatements:SELECT*FROMForaDMVexample:SELECT*FROMsys.
dm_os_performance_countersForaDMFexample:SELECT*FROMsys.
dm_io_virtual_file_stats(NULL,NULL)FormoreinformationonDynamicManagementViews,includingqueryoptionsandrequiredparameters,seeMicrosoftBooksOnline:http://msdn2.
microsoft.
com/en-us/library/ms188754(SQL.
100).
aspxBESTPRACTICE:Inallperformancemonitoring,capturebaselinedataandsetuparegularmonitoringplan.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295023PerformanceDataCollectorandManagementDataWarehouseSQLServer2008x64introducesthePerformanceDataCollector,whosegoalistomaintainperformance-relateddatainaformatthathelpsadministratorsmoreeasilytunetheirdatabases.
ThePerformanceDataCollectorbuildsonSQLServer2008x64'sDataCollectorArchitecture.
(Seemsdn2.
microsoft.
com/en-us/library/bb677355(SQL.
100).
aspxforadiscussionoftheDataCollectorArchitecture.
)ThePerformanceDataCollectorispartofabroadersetofcapabilitiesthatMicrosoftreferstoasthePerformanceStudio.
ThePerformanceStudiowasnotpartoftheNovemberCTP,thoughitissupposedtobepartoftheupcomingSQLServer2008ReadinessKit.
ThePerformanceStudioallowsyoutocollectperformancedatafrommultipledatabasesandstorethatdatainacentralrepository.
YoucanthencomparecurrentandpastSQLServerperformance.
ThePerformanceDataCollectoristheframeworkforcollectingdiagnosticandperformancedata.
ThePerformanceDataCollectorstoresthedataitgathersinaspecialdatabaseMicrosoftcallstheManagementDataWarehouse.
ThePerformanceDataCollectorusesthreeSystemDataCollectionsets,whichtheSQLServer2008x64documentationdefinesasfollows:DiskUsage.
Collectsdataaboutdiskandlogusageforallthedatabasesontheserver.
ServerActivity.
CollectsresourceusagestatisticsandperformancedatafromtheserverandSQLServer.
QueryStatistics.
Collectsquerystatistics,individualquerytext,queryplans,andspecificqueries.
Formoredetailsaboutthespecificdataineachset,seehttp://technet.
microsoft.
com/en-us/library/bb964725(SQL.
100).
aspxOncethePerformanceDataCollectorisrunning,youcaneitherwaitforthecollectionscheduletocyclecompletelyorupdatethedataimmediately.
Onceyouhaveyourdata,youcanviewahistoricalreportoryoucancreateacustomreportusinganyofthe20fieldsavailableforeachcollection.
Weexplainbelowhowtoupdateimmediatelyandhowtodisplayasamplereport.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295024NOTE:YoumuststarttheSQLAgentservicebeforeyouconfigurethePerformanceDataCollector.
Inaddition,youshouldsettheSQLAgentservicetostartautomaticallysofuturedatacollectionjobswillrun.
ToconfigurethePerformanceDataCollector,followthesesteps:1.
OpenMicrosoftSQLServerManagementStudio.
2.
Connecttotheserver.
3.
InObjectExplorer,expandManagement.
4.
UnderManagement,right-clickDataCollection,andselectConfigureManagementDataWarehouse.
Figure13.
ConfiguringtheManagementDataWarehouse.
5.
OntheWelcometotheConfigureManagementDataWarehouseWizardscreen,clickNext.
6.
OntheConfigureManagementDataStoragescreen,selectthelocalinstanceofSQLServer.
7.
ClickNew,namethenewdatabasewhereyou'llbestoringtheperformancedata,andclickOK.
8.
Selectafilesystemlocation,whichSQLServerusestocachetheperformancedata.
Thisminimizestheimpactofthedatacollectiononthestatistics.
9.
ClickNext.
10.
OntheMapLoginsandUsersscreen,selecttheappropriatesecurity,andclickNext.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge29502511.
OntheCompletetheWizardscreen,clickFinish.
Figure14.
Managementdatawarehouseconfigurationcompleted.
ThePerformanceDataCollectorisnowrunning.
Youmayconfigureeachofthecollectionsetsindividually.
Toconfigureacollectionset,followthesesteps:1.
OpentheMicrosoftSQLServerManagementStudio.
2.
Connecttotheserver.
3.
IntheObjectExplorer,expandManagement.
4.
UnderManagement,expandclickDataCollection.
5.
Right-clickthecollectoryouwanttochange,andselectProperties.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295026Figure15.
DiskUsageproperties.
Fromthepropertiesscreen,youcansettheschedule,periodofdataretentionandothercharacteristicsofthecollection.
Ifyouwanttoseeareportbeforeanentirereportingcyclehasfinished,youhavetheoptionofuploadingthestatisticsearlybyfollowingthesesteps:1.
OpentheMicrosoftSQLServerManagementStudio.
2.
Connecttotheserver.
3.
IntheObjectExplorer,expandManagement.
4.
UnderManagement,expandclickDataCollection.
5.
Right-clickthecollectoryouwant,andselectCollectanduploadnow.
Toviewareport,followthestepsabove,butselectReports,andselectthereportyouwant.
Thereportshavehotlinkswhichallowyoutodrilldownintothedetailsofinformationofinterest.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295027Figure16.
Samplemanagementdatareport.
SummingupWindowsServer2003Enterprisex64andSQLServer2008x64introducemanynewfeaturesandenhancements.
AsthisGuidehasdocumented,theprocessofdeployingtheseproductsonaDellPowerEdgeServerisrelativelystraightforward;youcanperformabasicinstallationinacoupleofhours.
Spendingsomeup-fronttimeplanningcanhelpyouavoidpotentialproblemsdowntheroad.
Forexample,thinkingthroughthewayyouwilllayoutdatafilescansavesignificanteffortlater.
Tokeepyourdatabaseserverrunningoptimally,youmustmonitorandrespondtothechangingdemandsoftheworkload.
WindowsServer2003andSQLServer2008x64providearichsetoftoolstohelpyoudoso.
WhileathoroughtreatmentofmonitoringaSQLServer2008x64databaseisbeyondthescopeofthisGuide,weintroducedtwotoolsyouwillfindparticularlyusefulforthatpurpose:theWindowsServer2003PerformanceMonitorandtheSQLServer2008PerformanceDataCollector.
Wehavealsogivenguidelinesforsomeofthecountersthatcanhelpyoulocateperformancebottlenecks.
Thebestpracticeswedescribehereshouldhelpyougetthebestperformanceandreliabilityfromyourdatabaseserver.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295028Tolearnmore,pleaseexplorethefollowingresources:www.
dell.
com/sqlwww.
microsoft.
com/windowsserver2003/default.
mspxwww.
microsoft.
com/sql/2008/default.
mspxSQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295029AppendixA.
Methodology:InstallingWindowsServer2003R2Enterprisex64InstallMicrosoftWindowsServer2003R2Enterprisex64byfollowingthesesteps.
Weprovideapproximatetimesforeachgroupofstepsinthefirststepofthatgroup.
1.
InserttheDellPowerEdgeInstallationandServerManagementbootableCDversion5.
2,andrebootthesystem.
Note:Duringthisprocess,thesoftwarewillaskifyouwanttoinsertareplicationfloppy;wedidnot.
(5minutes,duringwhichthesystemrebootstwice.
)2.
OntheWelcometotheDellOpenManageServerAssistantscreen,clickClickHereforServerSetup.
3.
SetthedateandtimeontheSetDateandTimescreen.
ClickContinue.
4.
OntheSelectanOperatingSystemtoInstallscreen,selectMicrosoftWindowsServer2003ServicePack1/ServicePack2x64Edition.
Asnotedabove,weareinstallingWindowsServer2003R2Enterprisex64–5.
2.
3790ServicePack2Build3790.
Thefollowingnoteappearsonthescreen:ForMicrosoft(R)WindowsServer(R)2003versions,theR2editionisalsosupported.
5.
LeaveCreateUtilityPartitionatitsdefaultvalueofYes,andclickContinue.
6.
OnthescreenConfigureorSkipRAID,selectSkipRAIDconfiguration,andclickContinue.
7.
OnthescreenConfigurethePhysicalDiskforMicrosoftWindowsServer2003ServicePack1/ServicePack2x64Edition,changethebootpartitionsizetothemaximumavailable–69335MB.
LeavetheFilesystematNTFS.
8.
OnthescreenConfirmtoDeletePartitions,clickContinue.
9.
OnthescreenNetworkAdapter(s)configuration,selectSpecifyanIPAddress.
10.
EntertheIPaddressesandselectthecorrectsubnettype.
11.
OnthescreenEntertheConfigurationInformationforMicrosoftWindowsServer2003ServicePack1/ServicePack2x64Edition,enteryourinformation.
Becareful,afterselectingJoinDomain,toenterthedomainnameandpasswordexactly.
Otherwise,youwillgeterrorslaterintheinstallprocess.
12.
OntheOperatingSystemInstallationSummaryscreen,clickContinue.
(6minutes)SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge29503013.
Whenprompted,inserttheMicrosoftWindowsServer2003CD,sothatDellOpenManageServerAssistantcancopytheoperatingsystemfiles.
(5minutes)14.
Whenprompted,removetheCDfromthesystem,andclickFinish.
Theinstallationbegins.
(27minutes,duringwhichthesystemrebootsfourtimes.
)15.
Whenthesystemcomesavailable,loginasAdministrator.
Note:theAdministratorpasswordisstillblankatthispoint.
Whenyoulogin,WindowsasksforthesecondWindowsserver2003CD.
Insertit,andclickOK.
(2minutes)16.
InstallSP2.
WedidthisfromaCD,acceptingthelicenseagreementandthedefaultoptions.
(3minutes)17.
Rebootsystem.
(4:35)18.
InstallWindowsupdate.
Weinstalledallessentialupdates.
However,whenpromptedtoinstallInternetExplorer7,wedeclined.
(Note:Thetimewillvary;inourcase,itwas10minutes.
)19.
LoginasAdministrator.
Thepasswordisblankatthispoint.
SetthepasswordfortheAdministratoraccounttoastrongpassword.
ConfiguringdatadrivesConfigureeachofthetwodata(non-operatingsystem)drivesusingthefollowingprocess.
Thesedirectionsassumethatnoonehaspreviouslyformattedthedisks.
1.
ClickStart.
2.
ExpandAdministrativeTools,andclickComputerManagement.
3.
Intheleftpanel,expandStorage,andselectDiskManagement.
4.
Right-clicktheunformatteddisk,andselectNewPartition.
5.
AcceptthedefaultofPrimaryPartition6.
AcceptthedefaultPartitionsizeinMB.
Thisshouldbetheentiredisk.
7.
Acceptthedefaultdriveletter.
8.
OntheFormatPartitionscreen,formatwiththeFilesystemsettoNTFSandtheAllocationunitsixesetto64K.
FillinginaVolumelabelisoptional.
9.
OntheCompletingtheNewPartitionWizardscreen,clickFinish.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295031MaximizethroughputfornetworkapplicationsWeonlyneededtodothefollowingstepsforthefirstofthenetworkconnections.
However,wedidverifythatthesettingappliedtothesecondnetworkconnectionaswell:1.
ClickStart.
2.
ExpandControlpanel,andclickNetworkConnections.
3.
SelectLocalAreaConnection.
4.
SelectFileandPrinterSharingforMicrosoftNetworksandclickProperties.
5.
OntheServerPropertiestab,selectMaximizethroughputfornetworkapplications.
6.
ClickOK.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295032AppendixB.
Methodology:InstallingSQLServer2008x64NOTES:BecausethisscenariodidnotincludeupgradingfromapreviousversionofSQLServer,wedidnotruntheUpgradeAdvisor.
WeinstalledSQLServer2008usinganISOimageweburnedtoaDVD.
Microsoft'sdownloadpagefortheNovemberCTPofSQLServer2008offerstwooptions:aself-extractingexecutable,whichenablesyoutoinstallwithoutaDVDburner,andtheDVDISOimage.
Microsoftdidnotrecommendonemethodovertheother.
WeusedtheDVDapproachbecausewehavefoundittobeconvenientwheninstallingmultipletimesonmultiplesystems.
Ifyouusetheself-extractingexecutable,yourtimesmightvary.
InstallMicrosoftSQLServer2008x64byfollowingthesesteps.
Weprovideapproximatetimesforeachgroupofstepsinthefirststepofthatgroup.
Planon30minutestocompletetheinstallation.
1.
InserttheSQLServer2008x64DVDintoDVDdrive.
2.
AttheSQLServer2008x64Startscreen,underInstall,clickServercomponents,Tools,BooksOnline,andSamples.
Thewindowsdisappearsbriefly(12minutes)3.
OntheWelcometoMicrosoft.
NETFramework2.
0x64Setupscreen,clickNext.
4.
AccepttheEndUserLicenseAgreementandclickNext.
(56minutes)5.
OntheSetupCompletescreenfor.
NET,clickFinish.
6.
OntheAcceptLicenseTermsscreen,checkIacceptthelicenseterms,andclickNext.
7.
OntheInstallationPrerequisitesscreen,clickInstalltoinstalltherequiredprerequisites.
(lessthan1minute)8.
OntheInstallationCenterscreen,clickNewInstallationtolaunchtheInstallingSQLServer2008Wizard.
Thesystemconfigurationcheckrunsautomaticallyandshouldcompletealmostinstantly.
9.
ClickNexttocontinue.
10.
OntheFeatureSelectionscreen,selectonlythosecomponentsyouneedforthisparticularinstallationofSQLServer2008x64.
ThispracticereducesoverheadonyourDellPowerEdge2950.
IfyouplanonrunningonlytheSQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295033databaseengine,thenselectjustDatabaseEngineServicesandClientTools.
Theclienttoolsarehelpfulforadministeringthisserverlocally.
11.
ClickNexttocontinue.
12.
OntheInstanceConfigurationscreen,eitheraccepttheDefaultinstanceorspecifyanamedinstance.
Youcanhaveonlyonedefaultinstanceonaserver.
13.
ClickNexttocontinue.
14.
OntheServiceaccountstaboftheServerconfigurationscreen,entertheaccountnameandpasswordyouwanttouse.
Youmustqualifytheaccountnamewiththedomainname:/.
IfyouwanttouseasingleaccountforallSQLservices,entertheaccountandpasswordintheUsethesameaccountforallsection,andclickApplytoall.
15.
OntheCollationtaboftheServerconfigurationscreen,acceptthedefaultsunlessyouhaveaspecificrequirementforothercollations,andclickNexttocontinue.
16.
OntheAccountProvisioningtaboftheDatabaseEngineConfigurationscreen,selectwhetheryouwanttorunSQLServer2008x64inmixedmodeorWindowsAuthenticationonly.
WerecommendleveragingActiveDirectorysecurityandchoosingWindowsAuthentication.
YoumustspecifyatleastoneSQLServeradministrator.
ToaddtheaccountyouareloggedintoWindowsServerasanadministrator,clickAddCurrentUser.
IfyourunSQLServer2008x64inmixedmode,youmustenterthepasswordforthe"sa"accounthere.
17.
OntheDataDirectoriestaboftheDatabaseEngineConfigurationscreen,entertheappropriatepathnames.
SeetheConfiguringthedrivessectionforhelpinselectingphysicaldirectoryandfilelocations.
Ifatallpossible,separatetheuserdatabasedirectory,userdatabaselogdirectory,andtempdbdirectory.
18.
ClickNexttocontinue.
NOTE:Tochangethephysicallocationoftheuserdatabasefiles,userdatabaselogfiles,ortempdbdirectoryfilesaftertheinitialinstallation,usetheALTERDATABASEcommandfromwithintheMicrosoftSQLServerManagementStudio.
(Laterinthisappendix,weprovideinstructionsfordoingso.
)19.
OntheErrorandUsageReportingscreen,SQLServerhasselectedthefollowingbydefault:SenderrorreportstoMicrosoftoryourcorporatereportserver,andSendSQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295034anonymousfeatureusagedatatoMicrosoft.
DeselecttheseifyoudonotwishtoparticipateintheMicrosoftdatacollectionprogram.
20.
ClickNexttocontinue.
21.
TheReadytoinstallscreenoffersonelastchancetoreviewyourchoices.
Afterdoingso,clickInstall.
(11minutes)22.
Attheconfirmationscreen,clickNext.
23.
AttheCompletescreen,notetheSummarylogfilelocation,andclickClosetocompletetheinstallation.
24.
AftertheSQLServer2008x64installationprocesscompletes,checktheMicrosoftDownloadsCenter(http://www.
microsoft.
com/downloads)forthelatestSQLServerupdates,patches,andservicepacks.
25.
IfyouneedtocreateaUserdatabase,followthestepsbelow.
Note:ManyapplicationsthatuseSQLServerasabackenddatabaseautomaticallycreatetheirownuserdatabasesaspartoftheinstallationprogram.
a.
OpenSQLServerManagementStudio.
b.
Entertheappropriateservername,entercredentialsasnecessary,andclickConnect.
c.
Right-clickDatabasesinObjectExplorer,andselectNewDatabase.
d.
Enterthedatabasename.
e.
Leavetheowneras.
f.
ScrolltotherightoftheDatabaseFileswindowandentertheappropriatepathsforthedatabasefileandlogfile.
ThedefaultlocationsforthesepathsarethosethatyouspecifiedintheDataDirectoriestaboftheDatabaseEngineConfigurationscreenduringtheinstallation.
g.
ClickOKtocreatethedatabase.
UsingtheALTERDATABASEcommandtomodifythelocationofuserdatabaseortempdbfilesandlogsafteraninstallationInthisexample,wecalledtheuserdatabaseDelldb;namedthedatafileDelldb.
mdf;andcalledthelogfileDelldb_log.
ldf.
Besureyouhaveexclusiveaccesstoanydatabaseyouplantomove.
1.
OpenSQLServerManagementStudio.
2.
Entertheappropriateservername,entercredentialsasnecessary,andclickConnect.
3.
ExpandtheDatabasesviewintheObjectExplorer.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge2950354.
Right-clickthedatabasenamewhosefilelocationyouwishtochange,andselectProperties.
5.
UnderSelectapage,selectFilesandtakenoteofthelogicalname,path,andfilenameoftheitemsyouplantomove.
6.
ClickOKtoclosethePropertiesdialog.
Note:SystemdatabasessuchastempdbarevisibleintheSystemDatabasesviewundertheDatabasesview.
7.
Right-clickthedatabasenamewhosefilelocationyouwishtochange,andselectTasks|TakeOffline.
Thisstepisunnecessarywhenmovingtempdbfilesortempdblogs.
8.
WhentheTakeOfflinecommandcompletes,clickClosetoclosethedialogbox.
9.
UseWindowsExplorertomovethefileorfilestothenewlocation.
Note:SQLServerautomaticallyre-createsthetempdbfilesandtempdblogsinthenewlocationaftertheservicerestarts.
10.
UnderDatabases|Systemdatabases,right-clickMaster,andselectNewQuery.
11.
Foreachfileyoumoved,runthefollowingstatement:ALTERDATABASEdatabase_nameMODIFYFILE(NAME=logical_name,FILENAME='new_path\os_file_name')Forexample:ALTERDATABASEDelldbMODIFYFILE(NAME=Delldb,FILENAME='c:\newdbfolder\Delldb.
mdf')12.
Right-clickthedatabasenamewhosefilelocationyouchanged,andselectTasks|BringOnline.
Whenmovingtempdbfilesorlogs,youmuststopandrestartSQLServeratthispoint.
13.
WhentheBringOnlinecommandcompletes,clickClosetoclosethedialogbox.
14.
Youcanverifythefilechangebytypingthefollowingstatementsinaquerywindow:Usedatabase_namesp_helpfileForexample:UseDellDBsp_helpfileBESTPRACTICE:AfteryouverifythatSQLServerhassuccessfullyrecreatedthetempdbdatafilesandtempdblogfiles,deletetheoldfilesfromthefilesystem.
Thispracticewillfreediskspaceandavoidpotentialconfusion.
SQLServer2008x64onWindowsServer2003R2Enterprisex64onDellPowerEdge295036AboutPrincipledTechnologiesWeprovideindustry-leadingtechnologyassessmentandfact-basedmarketingservices.
Webringtoeveryassignmentextensiveexperiencewithandexpertiseinallaspectsoftechnologytestingandanalysis,fromresearchintonewtechnologies,tothedevelopmentofnewmethodologies,totestingwithexistingandnewtools.
Whentheassessmentiscomplete,weknowhowtopresenttheresultstoabroadrangeoftargetaudiences.
Weprovideourclientswiththematerialstheyneed,frommarket-focuseddatatouseintheirowncollateraltocustomsalesaids,suchastestreports,performanceassessments,andwhitepapers.
Everydocumentreflectstheresultsofourtrustedindependentanalysis.
Weprovidecustomizedservicesthatfocusonourclients'individualrequirements.
Whetherthetechnologyinvolveshardware,software,Websites,orservices,weoffertheexperience,expertise,andtoolstohelpyouassesshowitwillfareagainstitscompetition,itsperformance,whetherit'sreadytogotomarket,anditsqualityandreliability.
Ourfounders,MarkL.
VanNameandBillCatchings,haveworkedtogetherintechnologyassessmentforover20years.
Asjournaliststheypublishedoverathousandarticlesonawidearrayoftechnologysubjects.
TheycreatedandledtheZiff-DavisBenchmarkOperation,whichdevelopedsuchindustry-standardbenchmarksasZiffDavisMedia'sWinstoneandWebBench.
TheyfoundedandledeTestingLabs,andaftertheacquisitionofthatcompanybyLionbridgeTechnologiesweretheheadandCTO,respectively,ofVeriTest.
PrincipledTechnologies,Inc.
1007SlaterRoad,Suite250Durham,NC,27703www.
principledtechnologies.
comPrincipledTechnologiesisaregisteredtrademarkofPrincipledTechnologies,Inc.
AllotherproductnamesarethetrademarksoftheirrespectiveownersDisclaimerofWarranties;LimitationofLiability:PRINCIPLEDTECHNOLOGIES,INC.
HASMADEREASONABLEEFFORTSTOENSURETHEACCURACYANDVALIDITYOFITSTESTING,HOWEVER,PRINCIPLEDTECHNOLOGIES,INC.
SPECIFICALLYDISCLAIMSANYWARRANTY,EXPRESSEDORIMPLIED,RELATINGTOTHETESTRESULTSANDANALYSIS,THEIRACCURACY,COMPLETENESSORQUALITY,INCLUDINGANYIMPLIEDWARRANTYOFFITNESSFORANYPARTICULARPURPOSE.
ALLPERSONSORENTITIESRELYINGONTHERESULTSOFANYTESTINGDOSOATTHEIROWNRISK,ANDAGREETHATPRINCIPLEDTECHNOLOGIES,INC.
,ITSEMPLOYEESANDITSSUBCONTRACTORSSHALLHAVENOLIABILITYWHATSOEVERFROMANYCLAIMOFLOSSORDAMAGEONACCOUNTOFANYALLEGEDERRORORDEFECTINANYTESTINGPROCEDUREORRESULT.
INNOEVENTSHALLPRINCIPLEDTECHNOLOGIES,INC.
BELIABLEFORINDIRECT,SPECIAL,INCIDENTAL,ORCONSEQUENTIALDAMAGESINCONNECTIONWITHITSTESTING,EVENIFADVISEDOFTHEPOSSIBILITYOFSUCHDAMAGES.
INNOEVENTSHALLPRINCIPLEDTECHNOLOGIES,INC.
'SLIABILITY,INCLUDINGFORDIRECTDAMAGES,EXCEEDTHEAMOUNTSPAIDINCONNECTIONWITHPRINCIPLEDTECHNOLOGIES,INC.
'STESTING.
CUSTOMER'SSOLEANDEXCLUSIVEREMEDIESAREASSETFORTHHEREIN.