Table of Contents
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
