Microsoft SQL Server 2012 Administration

Real-World Skills for MCSA Certification and Beyond (Exams 70-461, 70-462, and 70-463)

Häftad, Engelska, 2013

659 kr

Beställningsvara. Skickas inom 7-10 vardagar

Fri frakt för medlemmar vid köp för minst 249 kr.

Implement, maintain, and repair SQL Server 2012 databasesAs the most significant update since 2008, Microsoft SQL Server 2012 boasts updates and new features that are critical to understand. Whether you manage and administer SQL Server 2012 or are planning to get your MCSA: SQL Server 2012 certification, this book is the perfect supplement to your learning and preparation. From understanding SQL Server's roles to implementing business intelligence and reporting, this practical book explores tasks and scenarios that a working SQL Server DBA faces regularly and shows you step by step how to handle them. Includes practice exams and coverage of exam objectives for those seeking MSCA: SQL Server 2012 certificationExplores the skills you'll need on the job as a SQL Server 2012 DBADiscusses designing and implementing database solutionsWalks you through administrating, maintaining, and securing SQL Server 2012Addresses implementing high availability and data distributionIncludes bonus videos where the author walks you through some of the more difficult tasks expected of a DBAFeaturing hands-on exercises and real-world scenarios, this resource guides you through the essentials of implementing, maintaining, and repairing SQL Server 2012 databases.

Produktinformation

Utgivningsdatum2013-07-05
Mått188 x 234 x 46 mm
Vikt1 347 g
FormatHäftad
SpråkEngelska
Antal sidor936
FörlagJohn Wiley & Sons Inc
ISBN9781118487167

Tillhör följande kategorier

Databaser inom Data och it

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