Microsoft SQL Server 2012 Administration

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