Beginning
SQL
Server
2012 Administration
Rob
Walters
Grant
Fritchey
Contents
at
a
Glance
About the Authors xv
About the Technical Reviewer xvi
Acknowledgments
xviiIntroduction xviii
Chapter
1: The DatabaseAdministration Profession 1Chapter
2:Planning
andInstalling
SQL Server2012 13Chapter
3: What's in the Toolbox? 43Chapter
4: SQL ServerDevelopment
77Chapter5:
Creating
Tables and OtherObjects
105Chapter
6:Transact-SQL , 125Chapter
7: SQLServer Internals 147Chapter 8: Database Backup
Strategies
157Chapter
9: Database RestoreStrategies
185Chapter
10: Common Database Maintenance Tasks 213Chapter
11: SQL ServerSecurity
249Chapter
12:Auditing, Encryption,
andCompliance
271Chapter
13: PerformanceTuning
andOptimization
299Chapter
14: Fault Tolerance 339Chapter
15: CloudComputing
for the DBA 365Chapter
16:SQLServer Private Cloud 387Chapter 17: Where toGo Next? .401
Index 407
Contents
About the Authors xv
About theTechnical Reviewer xvi
Acknowledgments
xviiIntroduction xviii
Chapter
1: The Database Administration Profession 1Why
Do WeNeeda DBA? Access Runs Great! 1What
Exactly
Doesa DBADoAnyway?
2Database Maintenance 2
SecurityandRegulatory Compliance 3
DisasterRecovery 3
Design and Performance Improvement 3
Documentation 4
Salary
Information 4Wordsfrom Real-World DBAs 4 GrantFritchey, Principal DBA 5 RomanRehak,Principal Database Architect 7
CharliePyne, Senior DBA 8
EdClapper,Database ProductArchitectManager 9 Michael Esposito,Senior Technical Consultant and DBA 11
mCONTENTS
Chapter
2:Planning
andInstalling
SQL Server 2012 ..13Understanding
the Editions 14Demystifying32-Bitand 64-Bit Architectures 14
Server Editions 14
SQLServerTerminology 16
Planning
Your Installation 17Assessingthe Environment 17
ChoosingtheRightEdition 18
ValidatingHardware andSoftwareRequirements 19
Installing
SQL Server Evaluation Edition 19Step1: DownloadingSQLServerEvaluation Edition 19
Step2: Understandingthe SQL Server Installation Center 20
Step3: InstallinganInstanceofSQL Server 28
Upgrading
SQLServer 37Summary
40IChapter
3: What's in the Toolbox? 43ConnectingtoSQLServer 43
IssuingQueriesUsing SSMS 46
Managing MultipleServers 53
MonitoringServerActivity 55
Other Tools from the Start Menu 61
AnalysisServicesFolder 63
ConfigurationTools Folder 63 DocumentationandCommunityFolder 68
IntegrationServices Folder 68
Performance Tools Folder 69
ImportandExportData Wizard 70
SQLServer DataTools 70
iCONTENTS
Command-Line Tools 70
SQLCMD 71
PowerShell ProviderforSQL Server 73
Summary 74
Chapter4: SQL Server
Development
77Touring
SSMSThrough
the Eyes of a Developer 77IntelliSense 77
Query Designer 78
Templates 81
DebugginginT-SQL 84
Data-Tier
Applications
85 Defining a Data-TierApplication 85DeployingaData-TierApplication 89
RegisteringanExisting Databaseas aData-TierApplication 90
MonitoringaData-TierApplication Usingthe SQL ServerUtility 90
SQL Server
Developer
Tools 95Workingwith SSDT in Connected Mode 96
Workingwith SSDT in OfflineMode 99
ApplicationVersioning 102
SQL Server LocalDB 102
Summary
103SChapter
5:Creating
Tables and OtherObjects
105Navigating
theObject
ExplorerTree 105Scripting
theActions of an SSMSDialog
Box 106Transact-SQL
(T-SQL)
Primer 109m CONTENTS
Creating
Tables 110CreatingTables from the Table Designer 111
IssuingtheCREATE TABLE Statement 112
Altering
Tables 114Adding
Constraints 114NULL Constraints 114
CHECKConstraints 114
Primary KeysandUniqueConstraints 115
Foreign KeyConstraints 116
Dropping
Tables 117Creating
Indexes 119Summary
123Chapter
6: Transact-SQL 125The VetClinic
Sample
Database Revisited 125Data
Types
126Unicodevs.ANSI 127
Livingwith NULL 128
Data
Manipulation
Language
128SELECT 128 INSERT 133 UPDATE 135 DELETE 135 Transactions 136 ExecutingTransactions 136 IsolatingTransactions 136 Deadlocks 137 Stored Procedures 139
CreatingStored ProceduresUsing Templates 140
mCONTENTS
ModifyingStoredProcedures 142
SystemStoredProcedures 142
Functions 143
Creating aFunction 143
Invoking aFunction 143
Triggers
144Summary I45
Chapter
7: SQL Server Internals 147Databases I47 MasterDatabase 147 TempdbDatabase 147 Model Database 148 MSDB Database 148 Resource Database 149
Repairing
CorruptSystem
Databases 149Writing
Data to Disk 149SQLServerServices 150
Single-User
Mode 152PlacinganAlready-Started Database intoSingle-UserMode 153
Starting SQL ServerinSingle-User Mode 153
Summary
156Chapter
8: DatabaseBackup Strategies
157Defining
theTypes
ofBackups
157FullBackups 158
DifferentialBackups 163
FileandFilegroupBackups 164
Backing Up
theLogs
166i§CONTENTS
Log BackupUsingManagementStudio "168
Log Backup UsingT-SQL 169
Backing Up aCopyoftheDatabase 169
Automating
theBackup
Process 169Encrypting Backup
Files forSecurity
179Compressing
Backups
to SaveSpace
180Designing
a Backup Plan 182Summary
184Chapter
9: Database RestoreStrategies
185Restoring
a Databasefrom theManagement
Studio GUI 186Specifying
RestoreOptions
fromthe GUI 192Restoring
a DatabaseUsing
T-SQL 194Executing aSimple Restore 194
CreatingaCopyofa Database 195
RetrievingLogicaland Physical File Names 196
DisplayingGeneral InformationAboutaBackup 198
Cleaning Up 200
Restoring
Differential Backups 200Restoring Log Backups
201Restoring
File Backups 205Testing Backups
207Developing
a Full DisasterRecovery Plan 207GatheringInformationAbout the Business 208
Establishingthe Disaster Plan 209
Testingthe Disaster Plan 210
Summary
211CONTENTS
Chapter
10: Common Database Maintenance Tasks 213Backing Up
andRestoring
Databases 213Moving Data 214 MovingaDatabase Usingthe "Detachand Attach" Method 214
Movinga Database UsingtheCopyDatabase Wizard 216
Moving DataUsingtheImportand ExportWizard 220
MovingDataUsingtheBulkCopy Program (BCP) 227
Checking
DatabaseIntegrity
229Maintaining
Indexes 230Detecting Fragmentation 231
DefragmentingIndexes 233
Distribution Statistics 234 UnderstandingDistribution Statistics 234
UpdatingStatistics 237
Creating
Maintenance Plans 237Scheduling
Maintenance Tasks 244Creating Log
Files 245Summary
247Chapter
11: SQLServerSecurity
249Terminology
249Authentication 249
Authentication Mode 250
Authorization 250
ServerInstancevs.the Database 250
SQL Server Instance
Security
252 CreatingaSQLServerLogin 252Server Roles 253
sCONTENTS
Endpoints 256
User-DefinedServer Roles 258
Database
Security
259Database Users 259
Schemas 259
Fixed DatabaseRoles 262
DatabasePermissions 263
Flexible DatabaseRoles 265
SecurityFunctions 266
Contained Databases 267
Summary
269Chapter 12:
Auditing, Encryption,
andCompliance
271Auditing
in SQLServer 272Auditing Objects 273
ServerAuditObject 274
ServerAuditSpecificationObject 277 DatabaseAuditSpecification Object 280 User-Defined Audit Event 283
Encryption
284EncryptionPrimer 285 Password-BasedEncryption 285 Certificate-BasedEncryption 288
TransparentDataEncryption 290
ValidatingServerConfiguration 291
The Need foraPolicy 291
CreatePolicyonaLocal Server 291
bCONTENTS
EvaluatethePolicy 294
Usingthe CentralManagementServer 295
Summary
296Chapter
13: Performance Tuning andOptimization
299Measuring
SQL Server Performance 299Understanding Performance Counters 300
Performance Monitor 302
Dynamic Management Objects 305
DataCollector 307
Setting Upthe Data Collector 308
Viewingthe Data Collector Data 311
Tuning
Queries 319Understanding Execution Plans 319
Gathering QueryInformation with Extended Events 326
UsingtheDatabaseEngine TuningAdvisor 331
Managing
Resources 333LimitingResourceUse 334
LeveragingDataCompression 336
Summary
336liChapter 14: FaultTolerance 339
Defining
aService LevelAgreement(SLA)
339The"Nines" 340
Other Metrics 340
High Availability
Features in SQLServer 341Backup andRestore 341
LogShipping 341
Replication 348
CONTENTS
AlwaysOnFailoverClustering 354
AlwaysOn AvailabilityGroups 355
Configuring
anAlwaysOn
Availability
Group
355CreatingaWindows Server Failover Cluster 356
Configuringthe SQL ServerInstance 358
CreatinganAvailability Group 359
Summary 363
Chapter
15:Cloud Computingfor the DBA 365Cloud Service Models 366 Infrastructureas aService 366 Softwareas aService 366 PlatformasaService 366 Windows Azure 367 Roles 368 Storage 369 AppFabric 369
Case
Study.
OCCMundial.com 369Case
Study:
3M 370SQLAzure 372
UnderstandingSQL Azure 372
GettingStarted 373
ManagingaSQLAzure Database 378
MigratingData into SQLAzure 380
UnderstandingSQL Azurespecificviews 384
Backing UpandRestoringDatawith SQL Azure 385
StoringMoreThan Relational Data 385
Summary
386CONTENTS
•s
Chapter
16: SQL Server Private Cloud 387Virtualization 389
Resource
Pooling
389Discovering
SQL ServerSprawl 389Upgrading
SQL Server 395Elasticity
398Self-Service 399
Control and Customization 399
Implementation Options 399
Summary
400Chapter 17: Where to Go Next? 401 VisitSQLServer Books Online 401
Keep Up
with Public SQL Server Websites 401Attend aConference 402
Finda Local Users
Group
402 Create aLabSystem
and Practice 403 Createavirtual machine usingVirtual PC 2007 403Get Certified! 405