Microsoft SQL Server 2012 Administration

Tom Carpenter, MCSA: SQL Server 2012, is founder and senior consultant for SysEdCo, a training and consulting firm located in central Ohio. He is the author of several books on technical topics including networking infrastructure, database management, and VoIP. Having worked as a systems analyst in a large corporation with more than 20,000 network nodes, Tom brings a wealth of real-world experience to his books and presentations.

Introduction xxix Part I Introducing SQL Server 2012 1

Chapter 1 Understanding SQL Server’s Role 3

What Is Information Technology? 4

The Importance of IT 4

The Components of IT 6

Introduction to Databases 7

Types of Databases 7

Weighing the Benefits of Using a Local or Server-Based Database 11

Important Database Terms 16

Database Servers and Applications 18

Database Application Types 18

SQL Server’s Role 20

New Features Introduced in SQL Server 2012 21

Features Introduced in SQL Server 2008 25

Features Introduced in SQL Server 2005 29

Core Features of SQL Server 38

SQL Server Roles 39

Summary 43

Chapter Essentials 43

Chapter 2 Installing SQL Server 2012 45

Installation Planning 46

SQL Server 2012 Architecture 46

Installation Planning Process 56

Managing Permissions 60

Installing a Default Instance 62

SQL Server Installation Center Tasks 63

Installation 64

Installing Named Instances 73

Installing to a Cluster 76

Installing Extra Features 77

Upgrading from Previous Versions 77

Handling Upgrades 78

Understanding Migrations 79

Validating an Installation 79

Removing an Installation 80

Summary 80

Chapter Essentials 80

Chapter 3 Working with the Administration Tools 83

SQL Server Configuration Manager 84

Overview of the SSCM 85

Performing Common SSCM Tasks 86

SQL Server Management Studio 92

Overview of the SSMS 92

Performing Common SSMS Tasks 94

SQL Server Data Tools 102

Overview of SSDT 102

Performing Common SSDT Tasks 104

SQL Server Profiler 113

Books Online 116

Windows Server Administration for the DBA 118

User Account Management 118

File System Management 119

Network Configuration Administration 120

Summary 127

Chapter Essentials 127

Chapter 4 SQL Server Command-Line Administration 129

Introducing the Command Prompt 130

General Command-Prompt Options 132

Font Settings 133

Screen Layout 134

Color Choices 135

General Commands 135

Directory and Folder Navigation 136

Directory and Folder Listing 137

Screen Management 141

Displaying Information 142

Redirecting Output 146

Administrative Commands 148

Batch Files 155

Using Logic (IF and GOTO) 155

Passing Data 156

Including Comments 157

Mastering SQLCMD 161

Introducing Windows PowerShell 164

Using SQL Server PowerShell Extensions 165

Summary 169

Chapter Essentials 169

Chapter 5 Querying SQL Server 171

Understanding the SQL Language 172

Queries 172

Short Learning Curve 173

Varied Query Modes 173

Standardization 175

Added Logical Capabilities of T-SQL 176

SQL Statement Types 176

Data Manipulation Language 176

Data Definition Language 177

Data Control Language 177

SQL Syntactical Elements 177

Keywords 177

Comments 178

Batch Directives 178

SQL Statements 180

Clauses 180

Identifiers 180

Variables 181

Data Types 182

System Functions 182

Operators and Expressions 182

Statement Terminator 184

Coding Recommendations 184

Capitalize Keywords 184

Use Standard SQL 185

Do Not Use Keywords as Identifiers 185

Using SELECT Statements 185

Basic SELECT Statements 186

Filtered SELECT Statements 191

Sorted SELECT Statements 196

Handling NULL Data 197

Converting Data in Result Sets 199

Identifying Collation Details 201

Using Data from Multiple Tables 201

Advanced Query Techniques 204

Subqueries 204

Aggregate Queries 206

Using DDL Statements 207

Creating Objects 207

Altering Objects 209

Deleting Objects 210

Using DCL Statements 212

Granting Access 212

Denying Access 213

Revoking Permissions 213

Modifying Data 214

The INSERT Statement 214

The UPDATE Statement 215

The DELETE Statement 216

Tuning and Optimizing Queries 216

Transaction Processing 216

Error Handling 218

Summary 220

Chapter Essentials 220

Part Il Designing Database Solutions 223

Chapter 6 Database Concepts and Terminology 225

Relational Database Theory 226

Data 226

Information 227

Tables 227

Table Components 228

Relationship Types 232

Database Design Processes 236

Systems Development Life Cycle 237

Database Life Cycle 238

Business, Users, Model 239

Project Management for the DBA 243

The Define Phase 244

The Design Phase 246

The Deliver Phase 246

The Determine Phase 247

Summary 247

Chapter Essentials 248

Chapter 7 ERD and Capacity Planning 249

Planning a Database 250

User Surveys 250

Evaluating Business Processes 252

Developing Use Cases 254

Understanding Entity Relationship Diagramming 255

Building an ERD 257

Creating an ERD in Visio 258

Creating an ERD in OSA 264

Capacity Planning 271

Summary 276

Chapter Essentials 277

Chapter 8 Normalization and Other Design Issues 279

Designing for Normalization 280

Normal Forms 282

Normalizing a Database 291

Denormalizing a Database 294

Designing for Performance 295

Designing for Availability 295

Designing for Security 297

Summary 297

Chapter Essentials 297

Part Ill Implementing Database Solutions 299

Chapter 9 Creating SQL Server Databases 301

SQL Server Databases 302

System Databases 303

User Databases 308

Database Storage 308

Database Data Files 308

Database Filegroups 309

Transaction Logs 311

Database Options and Properties 312

Autogrowth 312

Recovery Model 313

Compatibility Level 316

Auto Shrink 316

Restrict Access 317

More Database Properties 318

Creating Databases in the GUI 321

Creating Databases with T-SQL 323

Creating Databases with PowerShell 326

Attaching and Detaching Databases 328

Database Snapshots 331

Creating Snapshots 332

Reverting to Snapshots 334

Summary 335

Chapter Essentials 335

Chapter 10 Creating Tables 337

Data Types 338

Data Type Categories 340

Collations 344

Configuring Server Instance Collations 345

Configuring Database Collations 346

Configuring Column Collations 346

Table Creation Process 349

Creating Tables with the Table Designer 349

Creating Tables with T-SQL 357

Data Partitioning 358

Vertical and Horizontal Partitioning 359

Data Partitioning with Functions and Schemes 360

Summary 361

Chapter Essentials 362

Chapter 11 Indexes and Views 363

Understanding Indexes 364

Indexes Defined 365

Index Types 369

Creating Basic Indexes 373

Creating a Clustered Index 374

Creating a Nonclustered Index 376

Creating Advanced Indexes 379

Creating a Covering Index 380

Creating a Filtered Index 381

Managing Indexes 383

Dropping an Index 384

Disabling and Enabling Indexes 384

Understanding Index Fragmentation 387

Understanding Views 389

Creating Views 390

Summary 392

Chapter Essentials 393

Chapter 12 Triggers and Stored Procedures 395

Triggers Defined 396

Types of Triggers 397

Recursive and Nested Triggers 398

Using Triggers 399

Creating Triggers 401

Understanding Stored Procedures 406

Creating Stored Procedures 407

How Triggers Differ from Stored Procedures 409

Summary 409

Chapter Essentials 409

Chapter 13 Implementing Advanced Features 411

Understanding and Installing Analysis Services 413

Analysis Services Tools 413

Analysis Services Optional Components 415

Installing and Configuring Analysis Services 416

Understanding Integration Services 419

Installing and Configuring Integration Services 419

Creating a Basic Integration Services Package 421

Troubleshooting and Debugging an SSIS Package 427

Scheduling Your Package to Run Automatically 429

Security Protection Levels 431

Understanding and Installing Reporting Services 432

Implementing Database Mail 436

Configuring Full-Text Indexing 439

Implementing Transparent Data Encryption 441

TDE Architecture 441

TDE Implementation Process 442

Data Compression 443

Summary 444

Chapter Essentials 444

Part IV Administering and Maintaining SQL Server 2012 447

Chapter 14 Creating Jobs, Operators, and Alerts 449

Standardize, Automate, and Update 450

Understanding SQL Server Jobs 452

Job Steps 452

Job Configuration Properties 454

Typical Jobs 462

Creating T-SQL Jobs 465

Creating SSIS Jobs 470

Creating Windows Command Jobs 476

Creating and Using Operators 480

Creating and Using Alerts 482

Using WSUS for SQL Server 2012 487

Summary 488

Chapter Essentials 488

Chapter 15 Performance Monitoring and Tuning 491

Performance Tuning Principles 492

Why Performance Tuning Matters 492

Common Performance Tuning Myths 494

Performance and Troubleshooting Tools 496

Blocks, Locks, and Deadlocks 497

Understanding Locks 498

Lock Types 498

Granularity of Locks 498

Lock Isolation Levels 499

Blocks and Deadlocks 499

SQL Server Profiler 503

Database Engine Tuning Advisor 506

Creating a DTA Workload File 506

Analyzing Your Workload File 507

Applying DTA Recommendations 509

Performance Monitoring with System Monitor 511

Installing the System Monitor 511

Viewing Live Performance Data 512

Logging Counters in Windows Server 2003 514

Data Collection in Windows Server 2008 515

Using the Resource Governor 519

Performance Studio 520

Advanced Monitoring Tools 523

Dynamic Management Views 523

DBCC 525

Resource Monitor 527

Summary 529

Chapter Essentials 530

Chapter 16 Policy-Based Management 533

Policy-Based Management 534

PBM Components 536

Creating Conditions 550

Creating Policies 553

Evaluating Policies 558

Centralized Server Management 558

Major Benefits and Requirements 558

Creating a Central Management Server 559

Registering Subscriber Servers 559

Standardizing with PBM and CMS 563

Standardizing 563

Automating 564

Updating 566

Summary 567

Chapter Essentials 567

Chapter 17 Backup and Restoration 569

Backing Up a Database 570

Creating a Backup Plan 571

Choosing a Recovery Model 573

Using the Different Backup Types 578

Working with Backup Devices and Files 584

Compressing Backups 587

Performing File and Filegroup Backups 588

Backing Up System Databases 588

Restoring a Database 589

Choosing a Restore Method 589

Restoring to a Point in Time 590

Restoring to the Point of Failure 591

Restoring System Databases 592

Backing Up the Environment 593

Built-in Backup Tools 594

Imaging and Third-Party Tools 595

Summary 596

Chapter Essentials 596

Part V SQL Server Security 597

Chapter 18 Security Threats and Principles 599

Security Defined 600

How to Classify Data for Security Purposes 603

Security in Theory 603

Security in the Real World 605

Security Threats 605

Threats, Vulnerabilities, and Exploits Defined 606

Attack Point 1: Windows Server 608

Attack Point 2: SQL Server 613

Attack Point 3: The Network 614

Attack Point 4: The Client 618

Cracking Examples 621

Security Principles 633

Start with Good Design 634

Trust, but Monitor 635

Defense-in-Depth 637

Least Privilege 638

Summary 638

Chapter Essentials 639

Chapter 19 Authentication and Encryption 641

Understanding Authentication 642

Credentials 644

Common Authentication Methods 646

Regulatory Compliance 648

SQL Server Authentication Methods 652

Logins, Users, and Roles 653

Configuring the Authentication Mode 653

Creating and Managing Principals and Roles 655

Creating Database Users 668

Understanding Encryption 669

SQL Server Encryption Solutions 669

Implementing Application-Level Encryption 671

Implementing Transparent Encryption 673

Summary 674

Chapter Essentials 675

Chapter 20 Security Best Practices 677

Establishing Baselines 678

Working with Security Templates 679

Analyzing a Server’s Security Settings 686

Using the Security Configuration Wizard 688

Implementing Least Privilege 691

Permissions and Authorization 692

Ownership Chains 698

Credentials 699

Auditing SQL Server Activity 701

Using Audits 701

Notifications 705

DDL Triggers 706

Logon Triggers 706

Configuring the Surface Area 707

SP_Configure for Surface Area Management 707

Policy-Based Management Surface Area Options 709

Understanding Common Criteria and C2 712

GRC 712

C2 Compliance 713

Common Criteria 716

Summary 717

Chapter Essentials 718

Part VI Implementing High Availability and Data Distribution 719

Chapter 21 AlwaysOn and High Availability 721

Introducing AlwaysOn Technology 722

Understanding High Availability 722

AlwaysOn Defined 725

Mirroring and AlwaysOn 727

Replacing Traditional Mirroring 727

Using Traditional Mirroring 728

Failover Solutions 728

Synchronous-Commit 728

Asynchronous-Commit 729

The Failover Process 729

Selecting Hardware for AlwaysOn 730

Highly Available Servers 730

Highly Available Storage 731

Highly Available Networks 731

Summary 732

Chapter Essentials 732

Chapter 22 SQL Server Failover Clustering 733

Understanding Windows Failover Clustering Service 734

Implementing a Windows Cluster 735

Failover Clustering Components 735

The Failover Clustering Installation Process 738

Installing SQL Server 2012 to a Cluster 747

Monitoring and Managing a SQL Server Cluster 748

Service Packs and Update Management 748

Failover Management 749

Cluster Troubleshooting 750

Summary 750

Chapter Essentials 750

Chapter 23 Database Mirroring and Snapshots 753

RAID-based Data Redundancy 754

Using Database Mirroring 757

Database Mirroring Components 758

Understanding Mirror Operating Modes 759

Planning for Role Switching 759

Implementing Database Mirroring 760

Understanding Log Shipping 764

Inside the Log-Shipping Process 764

Configuring Log Shipping and Monitoring 766

Implementing Database Snapshots 768

Database Snapshot Functionality 768

Implementing a Snapshot 770

Querying a Snapshot 770

Reverting to a Snapshot 770

Summary 771

Chapter Essentials 771

Chapter 24 Implementing Replication 773

SQL Server Replication 774

SQL Server Replication Roles and Concepts 775

Replication Types 775

Replication Models 778

New Features in SQL Server 2012 778

Configuring a Publisher and Distributor 779

Configuring a Subscriber 787

Monitoring Replication 790

Replication Performance 790

Replication Recovery 791

Importing and Exporting Data 791

Using BCP 792

Bulk Insert Commands 793

Using SQL Server Integration Services 795

Summary 797

Chapter Essentials 798

Part VII Implementing Business Intelligence and Reporting 799

Chapter 25 Data Warehousing 801

Understanding Data Warehouses 802

Defining Terminology 803

Defining Usage 805

Implementing Fact Tables 806

Understanding and Planning for Fact Tables 806

Creating Fact Tables 807

Implementing Dimensions 808

Understanding and Planning for Dimensions 808

Creating Dimensions 810

Summary 811

Chapter Essentials 811

Chapter 26 SQL Server Integration Services 813

Integration Issues 814

Existing Systems 814

Existing Data 815

Co-existence 816

Installing SSIS 816

Providing Prerequisites 816

Installing the SSIS Components 817

Development vs. Production Servers 817

Configuring SSIS for Operations 818

Configuring SSIS Security Settings 819

SSIS Service 819

SSIS Roles 820

Access Control 821

Digital Signatures 821

Deploying Packages 823

Working with the SSIS Catalog 823

Using the Deployment Utility 824

Deploying to SQL Server or Files 825

Using DTUTIL 826

SSIS Auditing and Event Handling 826

Auditing Packages 826

Using Log Providers 827

Using Event Handlers 828

Extracting, Transforming, and Loading Data 828

Connection Managers 829

Data Flow Design 831

Understanding Data Load Options 833

Using Script Tasks 835

Summary 836

Chapter Essentials 836

Chapter 27 Data Quality Solutions 837

Understanding Data Quality Concerns 838

Data Quality Problems 838

Data Quality Dimensions 839

Data Quality Processes 839

Installing Data Quality Services 840

Prerequisites 840

Performing the Installation 841

Data Governance 842

Using Master Data Services 843

Installing and Implementing MDS 843

Creating MDS Objects 844

Cleaning Data 845

Profiling Systems 845

Knowledge Base Management 846

Creating the Project 847

Summary 848

Chapter Essentials 848

Appendices 849

Appendix A Microsoft’s Certification Program 851

How Do You Become Certified on SQL Server 2012? 852

Tips for Taking a Microsoft Exam 853

Exam Registration 853

Certification Objectives Map 854

Appendix B About the Additional Study Tools 859

Additional Study Tools 860

Videos 860

Sybex Test Engine 860

Electronic Flashcards 860

Glossary of Terms 860

Adobe Reader 861

System Requirements 861

Using the Study Tools 861

Troubleshooting 861

Customer Care 862

Index 863