• No results found

Beginning SQL Server Administration. Apress. Rob Walters Grant Fritchey

N/A
N/A
Protected

Academic year: 2021

Share "Beginning SQL Server Administration. Apress. Rob Walters Grant Fritchey"

Copied!
13
0
0

Loading.... (view fulltext now)

Full text

(1)

Beginning

SQL

Server

2012 Administration

Rob

Walters

Grant

Fritchey

(2)

Contents

at

a

Glance

About the Authors xv

About the Technical Reviewer xvi

Acknowledgments

xvii

Introduction xviii

Chapter

1: The DatabaseAdministration Profession 1

Chapter

2:

Planning

and

Installing

SQL Server2012 13

Chapter

3: What's in the Toolbox? 43

Chapter

4: SQL Server

Development

77

Chapter5:

Creating

Tables and Other

Objects

105

Chapter

6:Transact-SQL , 125

Chapter

7: SQLServer Internals 147

Chapter 8: Database Backup

Strategies

157

Chapter

9: Database Restore

Strategies

185

Chapter

10: Common Database Maintenance Tasks 213

Chapter

11: SQL Server

Security

249

Chapter

12:

Auditing, Encryption,

and

Compliance

271

Chapter

13: Performance

Tuning

and

Optimization

299

Chapter

14: Fault Tolerance 339

Chapter

15: Cloud

Computing

for the DBA 365

Chapter

16:SQLServer Private Cloud 387

Chapter 17: Where toGo Next? .401

Index 407

(3)

Contents

About the Authors xv

About theTechnical Reviewer xvi

Acknowledgments

xvii

Introduction xviii

Chapter

1: The Database Administration Profession 1

Why

Do WeNeeda DBA? Access Runs Great! 1

What

Exactly

Doesa DBADo

Anyway?

2

Database Maintenance 2

SecurityandRegulatory Compliance 3

DisasterRecovery 3

Design and Performance Improvement 3

Documentation 4

Salary

Information 4

Wordsfrom 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

(4)

mCONTENTS

Chapter

2:

Planning

and

Installing

SQL Server 2012 ..13

Understanding

the Editions 14

Demystifying32-Bitand 64-Bit Architectures 14

Server Editions 14

SQLServerTerminology 16

Planning

Your Installation 17

Assessingthe Environment 17

ChoosingtheRightEdition 18

ValidatingHardware andSoftwareRequirements 19

Installing

SQL Server Evaluation Edition 19

Step1: DownloadingSQLServerEvaluation Edition 19

Step2: Understandingthe SQL Server Installation Center 20

Step3: InstallinganInstanceofSQL Server 28

Upgrading

SQLServer 37

Summary

40

IChapter

3: What's in the Toolbox? 43

ConnectingtoSQLServer 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

(5)

iCONTENTS

Command-Line Tools 70

SQLCMD 71

PowerShell ProviderforSQL Server 73

Summary 74

Chapter4: SQL Server

Development

77

Touring

SSMS

Through

the Eyes of a Developer 77

IntelliSense 77

Query Designer 78

Templates 81

DebugginginT-SQL 84

Data-Tier

Applications

85 Defining a Data-TierApplication 85

DeployingaData-TierApplication 89

RegisteringanExisting Databaseas aData-TierApplication 90

MonitoringaData-TierApplication Usingthe SQL ServerUtility 90

SQL Server

Developer

Tools 95

Workingwith SSDT in Connected Mode 96

Workingwith SSDT in OfflineMode 99

ApplicationVersioning 102

SQL Server LocalDB 102

Summary

103

SChapter

5:

Creating

Tables and Other

Objects

105

Navigating

the

Object

ExplorerTree 105

Scripting

theActions of an SSMS

Dialog

Box 106

Transact-SQL

(T-SQL)

Primer 109

(6)

m CONTENTS

Creating

Tables 110

CreatingTables from the Table Designer 111

IssuingtheCREATE TABLE Statement 112

Altering

Tables 114

Adding

Constraints 114

NULL Constraints 114

CHECKConstraints 114

Primary KeysandUniqueConstraints 115

Foreign KeyConstraints 116

Dropping

Tables 117

Creating

Indexes 119

Summary

123

Chapter

6: Transact-SQL 125

The VetClinic

Sample

Database Revisited 125

Data

Types

126

Unicodevs.ANSI 127

Livingwith NULL 128

Data

Manipulation

Language

128

SELECT 128 INSERT 133 UPDATE 135 DELETE 135 Transactions 136 ExecutingTransactions 136 IsolatingTransactions 136 Deadlocks 137 Stored Procedures 139

CreatingStored ProceduresUsing Templates 140

(7)

mCONTENTS

ModifyingStoredProcedures 142

SystemStoredProcedures 142

Functions 143

Creating aFunction 143

Invoking aFunction 143

Triggers

144

Summary I45

Chapter

7: SQL Server Internals 147

Databases I47 MasterDatabase 147 TempdbDatabase 147 Model Database 148 MSDB Database 148 Resource Database 149

Repairing

Corrupt

System

Databases 149

Writing

Data to Disk 149

SQLServerServices 150

Single-User

Mode 152

PlacinganAlready-Started Database intoSingle-UserMode 153

Starting SQL ServerinSingle-User Mode 153

Summary

156

Chapter

8: Database

Backup Strategies

157

Defining

the

Types

of

Backups

157

FullBackups 158

DifferentialBackups 163

FileandFilegroupBackups 164

Backing Up

the

Logs

166

(8)

i§CONTENTS

Log BackupUsingManagementStudio "168

Log Backup UsingT-SQL 169

Backing Up aCopyoftheDatabase 169

Automating

the

Backup

Process 169

Encrypting Backup

Files for

Security

179

Compressing

Backups

to Save

Space

180

Designing

a Backup Plan 182

Summary

184

Chapter

9: Database Restore

Strategies

185

Restoring

a Databasefrom the

Management

Studio GUI 186

Specifying

Restore

Options

fromthe GUI 192

Restoring

a Database

Using

T-SQL 194

Executing aSimple Restore 194

CreatingaCopyofa Database 195

RetrievingLogicaland Physical File Names 196

DisplayingGeneral InformationAboutaBackup 198

Cleaning Up 200

Restoring

Differential Backups 200

Restoring Log Backups

201

Restoring

File Backups 205

Testing Backups

207

Developing

a Full DisasterRecovery Plan 207

GatheringInformationAbout the Business 208

Establishingthe Disaster Plan 209

Testingthe Disaster Plan 210

Summary

211

(9)

CONTENTS

Chapter

10: Common Database Maintenance Tasks 213

Backing Up

and

Restoring

Databases 213

Moving Data 214 MovingaDatabase Usingthe "Detachand Attach" Method 214

Movinga Database UsingtheCopyDatabase Wizard 216

Moving DataUsingtheImportand ExportWizard 220

MovingDataUsingtheBulkCopy Program (BCP) 227

Checking

Database

Integrity

229

Maintaining

Indexes 230

Detecting Fragmentation 231

DefragmentingIndexes 233

Distribution Statistics 234 UnderstandingDistribution Statistics 234

UpdatingStatistics 237

Creating

Maintenance Plans 237

Scheduling

Maintenance Tasks 244

Creating Log

Files 245

Summary

247

Chapter

11: SQLServer

Security

249

Terminology

249

Authentication 249

Authentication Mode 250

Authorization 250

ServerInstancevs.the Database 250

SQL Server Instance

Security

252 CreatingaSQLServerLogin 252

Server Roles 253

(10)

sCONTENTS

Endpoints 256

User-DefinedServer Roles 258

Database

Security

259

Database Users 259

Schemas 259

Fixed DatabaseRoles 262

DatabasePermissions 263

Flexible DatabaseRoles 265

SecurityFunctions 266

Contained Databases 267

Summary

269

Chapter 12:

Auditing, Encryption,

and

Compliance

271

Auditing

in SQLServer 272

Auditing Objects 273

ServerAuditObject 274

ServerAuditSpecificationObject 277 DatabaseAuditSpecification Object 280 User-Defined Audit Event 283

Encryption

284

EncryptionPrimer 285 Password-BasedEncryption 285 Certificate-BasedEncryption 288

TransparentDataEncryption 290

ValidatingServerConfiguration 291

The Need foraPolicy 291

CreatePolicyonaLocal Server 291

(11)

bCONTENTS

EvaluatethePolicy 294

Usingthe CentralManagementServer 295

Summary

296

Chapter

13: Performance Tuning and

Optimization

299

Measuring

SQL Server Performance 299

Understanding Performance Counters 300

Performance Monitor 302

Dynamic Management Objects 305

DataCollector 307

Setting Upthe Data Collector 308

Viewingthe Data Collector Data 311

Tuning

Queries 319

Understanding Execution Plans 319

Gathering QueryInformation with Extended Events 326

UsingtheDatabaseEngine TuningAdvisor 331

Managing

Resources 333

LimitingResourceUse 334

LeveragingDataCompression 336

Summary

336

liChapter 14: FaultTolerance 339

Defining

aService LevelAgreement

(SLA)

339

The"Nines" 340

Other Metrics 340

High Availability

Features in SQLServer 341

Backup andRestore 341

LogShipping 341

Replication 348

(12)

CONTENTS

AlwaysOnFailoverClustering 354

AlwaysOn AvailabilityGroups 355

Configuring

an

AlwaysOn

Availability

Group

355

CreatingaWindows Server Failover Cluster 356

Configuringthe SQL ServerInstance 358

CreatinganAvailability Group 359

Summary 363

Chapter

15:Cloud Computingfor the DBA 365

Cloud Service Models 366 Infrastructureas aService 366 Softwareas aService 366 PlatformasaService 366 Windows Azure 367 Roles 368 Storage 369 AppFabric 369

Case

Study.

OCCMundial.com 369

Case

Study:

3M 370

SQLAzure 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

386

(13)

CONTENTS

•s

Chapter

16: SQL Server Private Cloud 387

Virtualization 389

Resource

Pooling

389

Discovering

SQL ServerSprawl 389

Upgrading

SQL Server 395

Elasticity

398

Self-Service 399

Control and Customization 399

Implementation Options 399

Summary

400

Chapter 17: Where to Go Next? 401 VisitSQLServer Books Online 401

Keep Up

with Public SQL Server Websites 401

Attend aConference 402

Finda Local Users

Group

402 Create aLab

System

and Practice 403 Createavirtual machine usingVirtual PC 2007 403

Get Certified! 405

Summary

405

References

Related documents

In an effort to refine the University’s brand and develop a process and organizational structure to more effectively and coherently market the University, FGCU engaged

 Introduction to Data Storage with SQL Server  Managing Storage for System Databases  Managing Storage for User Databases  Moving Database Files..  Configuring the

• Security Features within Databases • Applications Systems and Databases • Common Database Issues... The

The successful qualification of the modernized floater includes buoyancy tests with different masses, a long duration buoyancy test, a recovery hoisting test and the

It is o�ten used a�ter ground movements like Negativa It is o�ten used a�ter ground movements like Negativa as above, to get up from the ground and back into the as above, to get

Description: 'Cloud Database and DBaaS Market by Databases (SQL, Nosql), Software (Database Application Builder, Data Scaling and Replication, Back-Up and Recovery,

 Configuring Server memory settings  Configuring Database Settings  Tempdb configuration.  Best Practices on configuration tempdb &

In Object Explorer, you connect to an instance of the SQL Server 2005 database engine, expand that instance, expand Databases and the relevant application database, navigate to