Details

Introduction xxix <p>Part I Introducing SQL Server 2012 1</p> <p>Chapter 1 Understanding SQL Server’s Role 3</p> <p>What Is Information Technology? 4</p> <p>The Importance of IT 4</p> <p>The Components of IT 6</p> <p>Introduction to Databases 7</p> <p>Types of Databases 7</p> <p>Weighing the Benefits of Using a Local or Server-Based Database 11</p> <p>Important Database Terms 16</p> <p>Database Servers and Applications 18</p> <p>Database Application Types 18</p> <p>SQL Server’s Role 20</p> <p>New Features Introduced in SQL Server 2012 21</p> <p>Features Introduced in SQL Server 2008 25</p> <p>Features Introduced in SQL Server 2005 29</p> <p>Core Features of SQL Server 38</p> <p>SQL Server Roles 39</p> <p>Summary 43</p> <p>Chapter Essentials 43</p> <p>Chapter 2 Installing SQL Server 2012 45</p> <p>Installation Planning 46</p> <p>SQL Server 2012 Architecture 46</p> <p>Installation Planning Process 56</p> <p>Managing Permissions 60</p> <p>Installing a Default Instance 62</p> <p>SQL Server Installation Center Tasks 63</p> <p>Installation 64</p> <p>Installing Named Instances 73</p> <p>Installing to a Cluster 76</p> <p>Installing Extra Features 77</p> <p>Upgrading from Previous Versions 77</p> <p>Handling Upgrades 78</p> <p>Understanding Migrations 79</p> <p>Validating an Installation 79</p> <p>Removing an Installation 80</p> <p>Summary 80</p> <p>Chapter Essentials 80</p> <p>Chapter 3 Working with the Administration Tools 83</p> <p>SQL Server Configuration Manager 84</p> <p>Overview of the SSCM 85</p> <p>Performing Common SSCM Tasks 86</p> <p>SQL Server Management Studio 92</p> <p>Overview of the SSMS 92</p> <p>Performing Common SSMS Tasks 94</p> <p>SQL Server Data Tools 102</p> <p>Overview of SSDT 102</p> <p>Performing Common SSDT Tasks 104</p> <p>SQL Server Profiler 113</p> <p>Books Online 116</p> <p>Windows Server Administration for the DBA 118</p> <p>User Account Management 118</p> <p>File System Management 119</p> <p>Network Configuration Administration 120</p> <p>Summary 127</p> <p>Chapter Essentials 127</p> <p>Chapter 4 SQL Server Command-Line Administration 129</p> <p>Introducing the Command Prompt 130</p> <p>General Command-Prompt Options 132</p> <p>Font Settings 133</p> <p>Screen Layout 134</p> <p>Color Choices 135</p> <p>General Commands 135</p> <p>Directory and Folder Navigation 136</p> <p>Directory and Folder Listing 137</p> <p>Screen Management 141</p> <p>Displaying Information 142</p> <p>Redirecting Output 146</p> <p>Administrative Commands 148</p> <p>Batch Files 155</p> <p>Using Logic (IF and GOTO) 155</p> <p>Passing Data 156</p> <p>Including Comments 157</p> <p>Mastering SQLCMD 161</p> <p>Introducing Windows PowerShell 164</p> <p>Using SQL Server PowerShell Extensions 165</p> <p>Summary 169</p> <p>Chapter Essentials 169</p> <p>Chapter 5 Querying SQL Server 171</p> <p>Understanding the SQL Language 172</p> <p>Queries 172</p> <p>Short Learning Curve 173</p> <p>Varied Query Modes 173</p> <p>Standardization 175</p> <p>Added Logical Capabilities of T-SQL 176</p> <p>SQL Statement Types 176</p> <p>Data Manipulation Language 176</p> <p>Data Definition Language 177</p> <p>Data Control Language 177</p> <p>SQL Syntactical Elements 177</p> <p>Keywords 177</p> <p>Comments 178</p> <p>Batch Directives 178</p> <p>SQL Statements 180</p> <p>Clauses 180</p> <p>Identifiers 180</p> <p>Variables 181</p> <p>Data Types 182</p> <p>System Functions 182</p> <p>Operators and Expressions 182</p> <p>Statement Terminator 184</p> <p>Coding Recommendations 184</p> <p>Capitalize Keywords 184</p> <p>Use Standard SQL 185</p> <p>Do Not Use Keywords as Identifiers 185</p> <p>Using SELECT Statements 185</p> <p>Basic SELECT Statements 186</p> <p>Filtered SELECT Statements 191</p> <p>Sorted SELECT Statements 196</p> <p>Handling NULL Data 197</p> <p>Converting Data in Result Sets 199</p> <p>Identifying Collation Details 201</p> <p>Using Data from Multiple Tables 201</p> <p>Advanced Query Techniques 204</p> <p>Subqueries 204</p> <p>Aggregate Queries 206</p> <p>Using DDL Statements 207</p> <p>Creating Objects 207</p> <p>Altering Objects 209</p> <p>Deleting Objects 210</p> <p>Using DCL Statements 212</p> <p>Granting Access 212</p> <p>Denying Access 213</p> <p>Revoking Permissions 213</p> <p>Modifying Data 214</p> <p>The INSERT Statement 214</p> <p>The UPDATE Statement 215</p> <p>The DELETE Statement 216</p> <p>Tuning and Optimizing Queries 216</p> <p>Transaction Processing 216</p> <p>Error Handling 218</p> <p>Summary 220</p> <p>Chapter Essentials 220</p> <p>Part Il Designing Database Solutions 223</p> <p>Chapter 6 Database Concepts and Terminology 225</p> <p>Relational Database Theory 226</p> <p>Data 226</p> <p>Information 227</p> <p>Tables 227</p> <p>Table Components 228</p> <p>Relationship Types 232</p> <p>Database Design Processes 236</p> <p>Systems Development Life Cycle 237</p> <p>Database Life Cycle 238</p> <p>Business, Users, Model 239</p> <p>Project Management for the DBA 243</p> <p>The Define Phase 244</p> <p>The Design Phase 246</p> <p>The Deliver Phase 246</p> <p>The Determine Phase 247</p> <p>Summary 247</p> <p>Chapter Essentials 248</p> <p>Chapter 7 ERD and Capacity Planning 249</p> <p>Planning a Database 250</p> <p>User Surveys 250</p> <p>Evaluating Business Processes 252</p> <p>Developing Use Cases 254</p> <p>Understanding Entity Relationship Diagramming 255</p> <p>Building an ERD 257</p> <p>Creating an ERD in Visio 258</p> <p>Creating an ERD in OSA 264</p> <p>Capacity Planning 271</p> <p>Summary 276</p> <p>Chapter Essentials 277</p> <p>Chapter 8 Normalization and Other Design Issues 279</p> <p>Designing for Normalization 280</p> <p>Normal Forms 282</p> <p>Normalizing a Database 291</p> <p>Denormalizing a Database 294</p> <p>Designing for Performance 295</p> <p>Designing for Availability 295</p> <p>Designing for Security 297</p> <p>Summary 297</p> <p>Chapter Essentials 297</p> <p>Part Ill Implementing Database Solutions 299</p> <p>Chapter 9 Creating SQL Server Databases 301</p> <p>SQL Server Databases 302</p> <p>System Databases 303</p> <p>User Databases 308</p> <p>Database Storage 308</p> <p>Database Data Files 308</p> <p>Database Filegroups 309</p> <p>Transaction Logs 311</p> <p>Database Options and Properties 312</p> <p>Autogrowth 312</p> <p>Recovery Model 313</p> <p>Compatibility Level 316</p> <p>Auto Shrink 316</p> <p>Restrict Access 317</p> <p>More Database Properties 318</p> <p>Creating Databases in the GUI 321</p> <p>Creating Databases with T-SQL 323</p> <p>Creating Databases with PowerShell 326</p> <p>Attaching and Detaching Databases 328</p> <p>Database Snapshots 331</p> <p>Creating Snapshots 332</p> <p>Reverting to Snapshots 334</p> <p>Summary 335</p> <p>Chapter Essentials 335</p> <p>Chapter 10 Creating Tables 337</p> <p>Data Types 338</p> <p>Data Type Categories 340</p> <p>Collations 344</p> <p>Configuring Server Instance Collations 345</p> <p>Configuring Database Collations 346</p> <p>Configuring Column Collations 346</p> <p>Table Creation Process 349</p> <p>Creating Tables with the Table Designer 349</p> <p>Creating Tables with T-SQL 357</p> <p>Data Partitioning 358</p> <p>Vertical and Horizontal Partitioning 359</p> <p>Data Partitioning with Functions and Schemes 360</p> <p>Summary 361</p> <p>Chapter Essentials 362</p> <p>Chapter 11 Indexes and Views 363</p> <p>Understanding Indexes 364</p> <p>Indexes Defined 365</p> <p>Index Types 369</p> <p>Creating Basic Indexes 373</p> <p>Creating a Clustered Index 374</p> <p>Creating a Nonclustered Index 376</p> <p>Creating Advanced Indexes 379</p> <p>Creating a Covering Index 380</p> <p>Creating a Filtered Index 381</p> <p>Managing Indexes 383</p> <p>Dropping an Index 384</p> <p>Disabling and Enabling Indexes 384</p> <p>Understanding Index Fragmentation 387</p> <p>Understanding Views 389</p> <p>Creating Views 390</p> <p>Summary 392</p> <p>Chapter Essentials 393</p> <p>Chapter 12 Triggers and Stored Procedures 395</p> <p>Triggers Defined 396</p> <p>Types of Triggers 397</p> <p>Recursive and Nested Triggers 398</p> <p>Using Triggers 399</p> <p>Creating Triggers 401</p> <p>Understanding Stored Procedures 406</p> <p>Creating Stored Procedures 407</p> <p>How Triggers Differ from Stored Procedures 409</p> <p>Summary 409</p> <p>Chapter Essentials 409</p> <p>Chapter 13 Implementing Advanced Features 411</p> <p>Understanding and Installing Analysis Services 413</p> <p>Analysis Services Tools 413</p> <p>Analysis Services Optional Components 415</p> <p>Installing and Configuring Analysis Services 416</p> <p>Understanding Integration Services 419</p> <p>Installing and Configuring Integration Services 419</p> <p>Creating a Basic Integration Services Package 421</p> <p>Troubleshooting and Debugging an SSIS Package 427</p> <p>Scheduling Your Package to Run Automatically 429</p> <p>Security Protection Levels 431</p> <p>Understanding and Installing Reporting Services 432</p> <p>Implementing Database Mail 436</p> <p>Configuring Full-Text Indexing 439</p> <p>Implementing Transparent Data Encryption 441</p> <p>TDE Architecture 441</p> <p>TDE Implementation Process 442</p> <p>Data Compression 443</p> <p>Summary 444</p> <p>Chapter Essentials 444</p> <p>Part IV Administering and Maintaining SQL Server 2012 447</p> <p>Chapter 14 Creating Jobs, Operators, and Alerts 449</p> <p>Standardize, Automate, and Update 450</p> <p>Understanding SQL Server Jobs 452</p> <p>Job Steps 452</p> <p>Job Configuration Properties 454</p> <p>Typical Jobs 462</p> <p>Creating T-SQL Jobs 465</p> <p>Creating SSIS Jobs 470</p> <p>Creating Windows Command Jobs 476</p> <p>Creating and Using Operators 480</p> <p>Creating and Using Alerts 482</p> <p>Using WSUS for SQL Server 2012 487</p> <p>Summary 488</p> <p>Chapter Essentials 488</p> <p>Chapter 15 Performance Monitoring and Tuning 491</p> <p>Performance Tuning Principles 492</p> <p>Why Performance Tuning Matters 492</p> <p>Common Performance Tuning Myths 494</p> <p>Performance and Troubleshooting Tools 496</p> <p>Blocks, Locks, and Deadlocks 497</p> <p>Understanding Locks 498</p> <p>Lock Types 498</p> <p>Granularity of Locks 498</p> <p>Lock Isolation Levels 499</p> <p>Blocks and Deadlocks 499</p> <p>SQL Server Profiler 503</p> <p>Database Engine Tuning Advisor 506</p> <p>Creating a DTA Workload File 506</p> <p>Analyzing Your Workload File 507</p> <p>Applying DTA Recommendations 509</p> <p>Performance Monitoring with System Monitor 511</p> <p>Installing the System Monitor 511</p> <p>Viewing Live Performance Data 512</p> <p>Logging Counters in Windows Server 2003 514</p> <p>Data Collection in Windows Server 2008 515</p> <p>Using the Resource Governor 519</p> <p>Performance Studio 520</p> <p>Advanced Monitoring Tools 523</p> <p>Dynamic Management Views 523</p> <p>DBCC 525</p> <p>Resource Monitor 527</p> <p>Summary 529</p> <p>Chapter Essentials 530</p> <p>Chapter 16 Policy-Based Management 533</p> <p>Policy-Based Management 534</p> <p>PBM Components 536</p> <p>Creating Conditions 550</p> <p>Creating Policies 553</p> <p>Evaluating Policies 558</p> <p>Centralized Server Management 558</p> <p>Major Benefits and Requirements 558</p> <p>Creating a Central Management Server 559</p> <p>Registering Subscriber Servers 559</p> <p>Standardizing with PBM and CMS 563</p> <p>Standardizing 563</p> <p>Automating 564</p> <p>Updating 566</p> <p>Summary 567</p> <p>Chapter Essentials 567</p> <p>Chapter 17 Backup and Restoration 569</p> <p>Backing Up a Database 570</p> <p>Creating a Backup Plan 571</p> <p>Choosing a Recovery Model 573</p> <p>Using the Different Backup Types 578</p> <p>Working with Backup Devices and Files 584</p> <p>Compressing Backups 587</p> <p>Performing File and Filegroup Backups 588</p> <p>Backing Up System Databases 588</p> <p>Restoring a Database 589</p> <p>Choosing a Restore Method 589</p> <p>Restoring to a Point in Time 590</p> <p>Restoring to the Point of Failure 591</p> <p>Restoring System Databases 592</p> <p>Backing Up the Environment 593</p> <p>Built-in Backup Tools 594</p> <p>Imaging and Third-Party Tools 595</p> <p>Summary 596</p> <p>Chapter Essentials 596</p> <p>Part V SQL Server Security 597</p> <p>Chapter 18 Security Threats and Principles 599</p> <p>Security Defined 600</p> <p>How to Classify Data for Security Purposes 603</p> <p>Security in Theory 603</p> <p>Security in the Real World 605</p> <p>Security Threats 605</p> <p>Threats, Vulnerabilities, and Exploits Defined 606</p> <p>Attack Point 1: Windows Server 608</p> <p>Attack Point 2: SQL Server 613</p> <p>Attack Point 3: The Network 614</p> <p>Attack Point 4: The Client 618</p> <p>Cracking Examples 621</p> <p>Security Principles 633</p> <p>Start with Good Design 634</p> <p>Trust, but Monitor 635</p> <p>Defense-in-Depth 637</p> <p>Least Privilege 638</p> <p>Summary 638</p> <p>Chapter Essentials 639</p> <p>Chapter 19 Authentication and Encryption 641</p> <p>Understanding Authentication 642</p> <p>Credentials 644</p> <p>Common Authentication Methods 646</p> <p>Regulatory Compliance 648</p> <p>SQL Server Authentication Methods 652</p> <p>Logins, Users, and Roles 653</p> <p>Configuring the Authentication Mode 653</p> <p>Creating and Managing Principals and Roles 655</p> <p>Creating Database Users 668</p> <p>Understanding Encryption 669</p> <p>SQL Server Encryption Solutions 669</p> <p>Implementing Application-Level Encryption 671</p> <p>Implementing Transparent Encryption 673</p> <p>Summary 674</p> <p>Chapter Essentials 675</p> <p>Chapter 20 Security Best Practices 677</p> <p>Establishing Baselines 678</p> <p>Working with Security Templates 679</p> <p>Analyzing a Server’s Security Settings 686</p> <p>Using the Security Configuration Wizard 688</p> <p>Implementing Least Privilege 691</p> <p>Permissions and Authorization 692</p> <p>Ownership Chains 698</p> <p>Credentials 699</p> <p>Auditing SQL Server Activity 701</p> <p>Using Audits 701</p> <p>Notifications 705</p> <p>DDL Triggers 706</p> <p>Logon Triggers 706</p> <p>Configuring the Surface Area 707</p> <p>SP_Configure for Surface Area Management 707</p> <p>Policy-Based Management Surface Area Options 709</p> <p>Understanding Common Criteria and C2 712</p> <p>GRC 712</p> <p>C2 Compliance 713</p> <p>Common Criteria 716</p> <p>Summary 717</p> <p>Chapter Essentials 718</p> <p>Part VI Implementing High Availability and Data Distribution 719</p> <p>Chapter 21 AlwaysOn and High Availability 721</p> <p>Introducing AlwaysOn Technology 722</p> <p>Understanding High Availability 722</p> <p>AlwaysOn Defined 725</p> <p>Mirroring and AlwaysOn 727</p> <p>Replacing Traditional Mirroring 727</p> <p>Using Traditional Mirroring 728</p> <p>Failover Solutions 728</p> <p>Synchronous-Commit 728</p> <p>Asynchronous-Commit 729</p> <p>The Failover Process 729</p> <p>Selecting Hardware for AlwaysOn 730</p> <p>Highly Available Servers 730</p> <p>Highly Available Storage 731</p> <p>Highly Available Networks 731</p> <p>Summary 732</p> <p>Chapter Essentials 732</p> <p>Chapter 22 SQL Server Failover Clustering 733</p> <p>Understanding Windows Failover Clustering Service 734</p> <p>Implementing a Windows Cluster 735</p> <p>Failover Clustering Components 735</p> <p>The Failover Clustering Installation Process 738</p> <p>Installing SQL Server 2012 to a Cluster 747</p> <p>Monitoring and Managing a SQL Server Cluster 748</p> <p>Service Packs and Update Management 748</p> <p>Failover Management 749</p> <p>Cluster Troubleshooting 750</p> <p>Summary 750</p> <p>Chapter Essentials 750</p> <p>Chapter 23 Database Mirroring and Snapshots 753</p> <p>RAID-based Data Redundancy 754</p> <p>Using Database Mirroring 757</p> <p>Database Mirroring Components 758</p> <p>Understanding Mirror Operating Modes 759</p> <p>Planning for Role Switching 759</p> <p>Implementing Database Mirroring 760</p> <p>Understanding Log Shipping 764</p> <p>Inside the Log-Shipping Process 764</p> <p>Configuring Log Shipping and Monitoring 766</p> <p>Implementing Database Snapshots 768</p> <p>Database Snapshot Functionality 768</p> <p>Implementing a Snapshot 770</p> <p>Querying a Snapshot 770</p> <p>Reverting to a Snapshot 770</p> <p>Summary 771</p> <p>Chapter Essentials 771</p> <p>Chapter 24 Implementing Replication 773</p> <p>SQL Server Replication 774</p> <p>SQL Server Replication Roles and Concepts 775</p> <p>Replication Types 775</p> <p>Replication Models 778</p> <p>New Features in SQL Server 2012 778</p> <p>Configuring a Publisher and Distributor 779</p> <p>Configuring a Subscriber 787</p> <p>Monitoring Replication 790</p> <p>Replication Performance 790</p> <p>Replication Recovery 791</p> <p>Importing and Exporting Data 791</p> <p>Using BCP 792</p> <p>Bulk Insert Commands 793</p> <p>Using SQL Server Integration Services 795</p> <p>Summary 797</p> <p>Chapter Essentials 798</p> <p>Part VII Implementing Business Intelligence and Reporting 799</p> <p>Chapter 25 Data Warehousing 801</p> <p>Understanding Data Warehouses 802</p> <p>Defining Terminology 803</p> <p>Defining Usage 805</p> <p>Implementing Fact Tables 806</p> <p>Understanding and Planning for Fact Tables 806</p> <p>Creating Fact Tables 807</p> <p>Implementing Dimensions 808</p> <p>Understanding and Planning for Dimensions 808</p> <p>Creating Dimensions 810</p> <p>Summary 811</p> <p>Chapter Essentials 811</p> <p>Chapter 26 SQL Server Integration Services 813</p> <p>Integration Issues 814</p> <p>Existing Systems 814</p> <p>Existing Data 815</p> <p>Co-existence 816</p> <p>Installing SSIS 816</p> <p>Providing Prerequisites 816</p> <p>Installing the SSIS Components 817</p> <p>Development vs. Production Servers 817</p> <p>Configuring SSIS for Operations 818</p> <p>Configuring SSIS Security Settings 819</p> <p>SSIS Service 819</p> <p>SSIS Roles 820</p> <p>Access Control 821</p> <p>Digital Signatures 821</p> <p>Deploying Packages 823</p> <p>Working with the SSIS Catalog 823</p> <p>Using the Deployment Utility 824</p> <p>Deploying to SQL Server or Files 825</p> <p>Using DTUTIL 826</p> <p>SSIS Auditing and Event Handling 826</p> <p>Auditing Packages 826</p> <p>Using Log Providers 827</p> <p>Using Event Handlers 828</p> <p>Extracting, Transforming, and Loading Data 828</p> <p>Connection Managers 829</p> <p>Data Flow Design 831</p> <p>Understanding Data Load Options 833</p> <p>Using Script Tasks 835</p> <p>Summary 836</p> <p>Chapter Essentials 836</p> <p>Chapter 27 Data Quality Solutions 837</p> <p>Understanding Data Quality Concerns 838</p> <p>Data Quality Problems 838</p> <p>Data Quality Dimensions 839</p> <p>Data Quality Processes 839</p> <p>Installing Data Quality Services 840</p> <p>Prerequisites 840</p> <p>Performing the Installation 841</p> <p>Data Governance 842</p> <p>Using Master Data Services 843</p> <p>Installing and Implementing MDS 843</p> <p>Creating MDS Objects 844</p> <p>Cleaning Data 845</p> <p>Profiling Systems 845</p> <p>Knowledge Base Management 846</p> <p>Creating the Project 847</p> <p>Summary 848</p> <p>Chapter Essentials 848</p> <p>Appendices 849</p> <p>Appendix A Microsoft’s Certification Program 851</p> <p>How Do You Become Certified on SQL Server 2012? 852</p> <p>Tips for Taking a Microsoft Exam 853</p> <p>Exam Registration 853</p> <p>Certification Objectives Map 854</p> <p>Appendix B About the Additional Study Tools 859</p> <p>Additional Study Tools 860</p> <p>Videos 860</p> <p>Sybex Test Engine 860</p> <p>Electronic Flashcards 860</p> <p>Glossary of Terms 860</p> <p>Adobe Reader 861</p> <p>System Requirements 861</p> <p>Using the Study Tools 861</p> <p>Troubleshooting 861</p> <p>Customer Care 862</p> <p>Index 863</p>

<p>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.</p>

<p><b>Take Your Career to the Next Level with This Practical Guide</b></p> <p>SQL Server expert Tom Carpenter teaches you skills to help you excel in your job as a SQL Server 2012 database administrator. From designing and implementing database solutions with SQL Server 2012 to querying, repairing data, maintaining high availability, and managing always-on technology, he explores the real-world tasks and scenarios you'll face as a SQL Server database administrator and shows you how to handle them. This book also serves as an ideal resource for those preparing for their MCSA: SQL Server 2012 certification.</p> <ul> <li>Install and configure SQL Server 2012</li> <li>Design, implement, and effectively manage database solutions</li> <li>Learn basic and advanced querying techniques</li> <li>Understand triggers, stored procedures, and indexing</li> <li>Standardize, automate, and update jobs, operators, and alerts</li> <li>Monitor and manage a SQL Server cluster and learn mirroring</li> <li>Initiate backup and restore, and learn security best practices</li> <li>Plan for high availability, data warehousing, and business intelligence</li> <li>Discover how to clean data and improve data quality</li> </ul> <p>ACCESS practice exams, electronic flashcards, and author videos of critical tasks at www.sybex.com/go/sqlserver12admin</p> <p>An ideal supplement to help you prepare for:</p> <p>Exam 70-461: Querying Microsoft SQL Server 2012</p> <p>Exam 70-462: Administering a Microsoft SQL Server 2012 Database</p> <p>Exam 70-463: Implementing Data Warehouses with Microsoft SQL Server 2012</p>

US