Opis: Microsoft SQL Server 2012 Unleashed - Alex Silverstein, Ray Rankins, Chris Gallelli

Buy the print version of'Microsoft SQL Server 2012 Unleashed and get the eBook version for free! eBook version includes chapters 44-60 not included in the print. See inside the book for access code and details. ' With up-to-the-minute content, this is the industry's most complete, useful guide to SQL Server 2012. ' You'll find start-to-finish coverage of SQL Server's core database server and management capabilities: all the real-world information, tips, guidelines, and samples you'll need to create and manage complex database solutions. The additional online chapters add extensive coverage of SQL Server Integration Services, Reporting Services, Analysis Services, T-SQL programming, .NET Framework integration, and much more. ' Authored by four expert SQL Server administrators, designers, developers, architects, and consultants, this book reflects immense experience with SQL Server in production environments. Intended for intermediate-to-advanced-level SQL Server professionals, it focuses on the product's most complex and powerful capabilities, and its newest tools and features. * Understand SQL Server 2012's newest features, licensing changes, and capabilities of each edition * Manage SQL Server 2012 more effectively with SQL Server Management Studio, the SQLCMD command-line query tool, and Powershell * Use Policy-Based Management to centrally configure and operate SQL Server * Utilize the new Extended Events trace capabilities within SSMS * Maximize performance by optimizing design, queries, analysis, and workload management * Implement new best practices for SQL Server high availability * Deploy AlwaysOn Availability Groups and Failover Cluster Instances to achieve enterprise-class availability and disaster recovery * Leverage new business intelligence improvements, including Master Data Services, Data Quality Services and Parallel Data Warehouse * Deliver better full-text search with SQL Server 2012's new Semantic Search * Improve reporting with new SQL Server 2012 Reporting Services features Download the following from informit.com/title/9780672336928: Sample databases and code examples ' 'Introduction 1 Who This Book Is For 2 What This Book Covers 2 Conventions Used in This Book 4 Good Luck! 6 Part I Welcome to Microsoft SQL Server 1 SQL Server 2012 Overview 9 SQL Server Components and Features 9 The SQL Server Database Engine 10 SQL Server 2012 Administration and Management Tools 12 Replication 16 Merge Replication 17 Database Mirroring 17 SQL Server AlwaysOn Features 18 SQL Server Service Broker 19 Full-Text and Semantic Search 20 SQL Server Integration Services (SSIS) 21 SQL Server Analysis Services (SSAS) 22 SQL Server Reporting Services (SSRS) 24 Master Data Services 25 Data Quality Services 26 SQL Server 2012 Editions 26 SQL Server 2012 Standard Edition 26 SQL Server 2012 Enterprise Edition 27 Differences Between the Enterprise and Standard Editions of SQL Server 28 Other SQL Server 2012 Editions 29 SQL Server Licensing Models 32 Web Edition Licensing 33 Developer Edition Licensing 33 Express Edition Licensing 33 Choosing a Licensing Model 33 Mixing Licensing Models 34 Passive Server/Failover Licensing 34 Virtual Server Licensing 34 Summary 35 2 What's New in SQL Server 2012 37 New SQL Server 2012 Features 37 New and Improved Storage Features 38 New Transact-SQL Constructs 39 New Scalability and Performance Features 40 New Security Features 40 New Availability Features 40 Statistical Semantic Search 41 Data Quality Services 41 SQL Server 2012 Enhancements 42 SQL Server Management Studio Enhancements 42 Resource Governor Enhancements 43 Spatial Data Enhancements 43 Integration Services Enhancements 43 Service Broker Enhancements 44 Full-Text Search Enhancements 44 Analysis Services Enhancements 45 Reporting Services Enhancements 45 Master Data Services Enhancements 46 Deprecated Features 46 Summary 47 3 Examples of SQL Server Implementations 49 Application Terms 50 OLTP Application Examples 51 OLTP ERP Example 51 OLTP Shopping Cart Example 53 DSS Application Examples 55 DSS Example One 55 DSS Example Two 56 DSS Example Three 57 Summary 59 Part II SQL Server Tools and Utilities 4 SQL Server Management Studio 63 What's New in SSMS 63 The Integrated Environment 64 Window Management 64 Integrated Help 67 Administration Tools 71 Registered Servers 71 Object Explorer 72 Activity Monitor 75 Log File Viewer 77 SQL Server Utility 78 Development Tools 84 The Query Editor 84 Managing Projects in SSMS 92 Integrating SSMS with Source Control 93 Using SSMS Templates 95 Using SSMS Snippets 99 T-SQL Debugging 100 Multiserver Queries 102 Summary 102 5 SQL Server Command-Line Utilities 105 What's New in SQL Server Command-Line Utilities 106 The sqlcmd Command-Line Utility 107 Executing the sqlcmd Utility 108 Using Scripting Variables with sqlcmd 111 The dta Command-Line Utility 111 The tablediff Command-Line Utility 114 The bcp Command-Line Utility 117 The sqldiag Command-Line Utility 118 The sqlservr Command-Line Utility 120 The SqlLocalDB Command-Line Utility 121 Summary 123 6 SQL Server Profiler 125 What's New with SQL Server Profiler 125 SQL Server Profiler Architecture 126 Creating Traces 127 Events 129 Data Columns 131 Filters 134 Executing Traces and Working with Trace Output 136 Saving and Exporting Traces 137 Saving Trace Output to a File 137 Saving Trace Output to a Table 137 Saving the Profiler GUI Output 138 Importing Trace Files 138 Importing a Trace File into a Trace Table 139 Analyzing Trace Output with the Database Engine Tuning Advisor 142 Replaying Trace Data 142 Defining Server-Side Traces 144 Monitoring Running Traces 155 Stopping Server-Side Traces 156 Profiler Usage Scenarios 158 Analyzing Slow Stored Procedures or Queries 158 Deadlocks 159 Identifying Ad Hoc Queries 161 Identifying Performance Bottlenecks 162 Monitoring Auto-Update Statistics 164 Monitoring Application Progress 164 Summary 166 Part III SQL Server Administration 7 SQL Server System and Database Administration 169 What's New in SQL Server System and Database Administration 169 System Administrator Responsibilities 170 System Databases 171 The master Database 171 The resource Database 172 The model Database 172 The msdb Database 172 The distribution Database 172 The tempdb Database 173 Maintaining System Databases 173 System Tables 174 System Views 175 Compatibility Views 176 Catalog Views 178 Information Schema Views 180 Dynamic Management Views 182 System Stored Procedures 185 Useful System Stored Procedures 186 Summary 187 8 Installing SQL Server 2012 189 What's New in Installing SQL Server 2012 189 Installation Requirements 190 Hardware Requirements 190 Software Requirements 191 Installation Walkthrough 195 Install Screens, Step-by-Step 196 Installing SQL Server Documentation 216 Installing SQL Server Using a Configuration File 218 Running an Automated or Manual Install 224 Installing Service Packs and Cumulative Updates 225 Applying a Service Pack or Cumulative Update During a New Installation 225 Applying a Service Pack or Cumulative Update to an Existing Installation 227 Installing a Service Pack from the Command Line 231 Summary 232 9 Upgrading to SQL Server 2012 233 What's New in Upgrading SQL Server 233 SQL Server 2012 233 The SQL Server 2012 Upgrade Matrix 234 Identifying Products and Features to be Upgraded 236 Using the SQL Server Upgrade Advisor (UA) 237 Getting Started with the UA 237 The Analysis Wizard 239 The Report Viewer 244 Destination: SQL Server 2012 245 Side-by-Side Upgrade 246 Upgrading In-Place 254 Upgrading the Database Engine 256 Installing Product Updates (Slipstreaming) During Upgrades 258 Upgrading Using a Configuration File 260 Upgrading from Pre-SQL Server 2005 Versions 261 Upgrading Other SQL Server Components 261 Upgrading Analysis Services 262 Upgrading SQL Server Analysis Services 262 Upgrading Reporting Services 262 Performing an In-Place Upgrade of Reporting Services 262 Migrating to Reporting Services 2012 264 Upgrading SSIS Packages 265 Migrating DTS Packages 266 Summary 267 10 Client Installation and Configuration 269 What's New in Client Installation and Configuration 269 Client/Server Networking Considerations 270 Server Network Protocols 271 The Server Endpoint Layer 273 The Role of SQL Browser 276 Client Installation 277 Installing the Client Tools 277 Installing SNAC 278 Client Configuration 280 Client Configuration Using SSCM 280 Connection Encryption 283 Client Data Access Technologies 285 Provider Choices 286 Connecting Using the Various Providers and Drivers 287 General Networking Considerations and Troubleshooting 293 Summary 295 11 Database Backup and Restore 297 What's New in Database Backup and Restore 297 Developing a Backup and Restore Plan 298 Types of Backups 299 Full Database Backups 300 Differential Database Backups 300 Partial Backups 301 Differential Partial Backups 301 File and Filegroup Backups 301 Copy-Only Backups 302 Transaction Log Backups 302 Recovery Models 302 Full Recovery 303 Bulk-Logged Recovery 304 Simple Recovery 305 Backup Devices 306 Disk Devices 306 Tape Devices 307 Network Shares 307 Media Sets and Families 307 Creating Backup Devices 308 Backing Up a Database 308 Creating Database Backups with SSMS 308 Creating Database Backups with T-SQL 311 Backing Up the Transaction Log 314 Creating Transaction Log Backups with SSMS 314 Creating Transaction Log Backups with T-SQL 315 Backup Scenarios 316 Full Database Backups Only 316 Full Database Backups with Transaction Log Backups 317 Differential Backups 318 Partial Backups 319 File/Filegroup Backups 321 Mirrored Backups 322 Copy-Only Backups 323 Compressed Backups 323 System Database Backups 324 Restoring Databases and Transaction Logs 324 Restores with T-SQL 325 Restoring by Using SSMS 329 Restore Information 333 Restore Scenarios 337 Restoring to a Different Database 337 Restoring a Snapshot 338 Restoring a Transaction Log 338 Restoring to the Point of Failure 339 Restoring to a Point in Time 342 Online Restores 342 Restoring the System Databases 344 Additional Backup Considerations 346 Frequency of Backups 346 Using a Standby Server 347 Snapshot Backups 348 Considerations for Very Large Databases 348 Maintenance Plans 348 Summary 349 12 Database Mail 351 What's New in Database Mail 351 Setting Up Database Mail 352 Creating Mail Profiles and Accounts 353 Using T-SQL to Update and Delete Mail Objects 356 Setting System-Wide Mail Settings 357 Testing Your Setup 357 Sending and Receiving with Database Mail 358 The Service Broker Architecture 358 Sending Email 358 Receiving Email 364 Using SQL Server Agent Mail 364 Job Mail Notifications 365 Creating an Operator 365 Enabling SQL Agent Mail 365 Creating the Job 365 Testing the Job-Completion Notification 366 Alert Mail Notifications 367 Creating an Alert 367 Testing the Alert Notification 368 Related Views and Procedures 368 Viewing the Mail Configuration Objects 368 Viewing Mail Message Data 369 Summary 371 13 SQL Server Agent 373 What's New in Scheduling and Notification 374 Configuring the SQL Server Agent 374 Configuring SQL Server Agent Properties 374 Configuring the SQL Server Agent Startup Account 376 Configuring Email Notification 378 SQL Server Agent Proxy Account 380 Viewing the SQL Server Agent Error Log 381 SQL Server Agent Security 382 Managing Operators 383 Managing Jobs 385 Defining Job Properties 385 Defining Job Steps 386 Defining Multiple Jobs Steps 389 Defining Job Schedules 390 Defining Job Notifications 391 Viewing Job History 392 Managing Alerts 393 Defining Alert Properties 393 Defining Alert Responses 397 Scripting Jobs and Alerts 399 Multiserver Job Management 400 Creating a Master Server 401 Enlisting Target Servers 401 Creating Multiserver Jobs 402 Event Forwarding 402 Summary 403 14 Administering SQL Server 2012 with PowerShell 405 What's New with PowerShell 405 Overview of PowerShell 406 Start Using PowerShell Now 407 Common Terminology 407 Object-Based Functionality 408 SQL Server Management Objects 408 WMI 409 Installing PowerShell 409 PowerShell Console 409 Scriptable and Interactive 410 Default Security 411 Execution Policy 411 Profiles 411 Built-In Help Features 411 PowerShell Scripting Basics 414 A Few Basic Cmdlets 414 Creating a PowerShell Script 415 Adding Comments 415 Variables 416 Escaping Characters 417 Special Variable $_ 418 Joining Variables and Strings 418 Passing Arguments 419 Using Param 419 Arrays 420 Operators 421 Conditional Statements 421 Functions 422 Looping Statements 423 Filtering Cmdlets 424 Formatting Cmdlets 425 Dealing with CSV Files 426 Dealing with Dates and Times 427 -WhatIf/-Confirm Parameters 428 PowerShell in SQL Server 2012 428 Adding PowerShell Support 428 Accessing SQL Server PowerShell 429 SQL Server PowerShell 430 SQL Provider 431 SQL Cmdlets 432 SQL Server Agent Support 432 Step-by-Step Examples 432 General Tasks 433 Scheduling Scripts 433 Common OS-Related Tasks 435 SQL Server-Specific Tasks 437 Using the Provider 438 Creating a Database Table 438 Performing a Database Backup 440 Checking Server Settings 441 Checking the Database Usage 442 Getting Table Properties 443 Cmdlet Example: Invoke-SqlCmd 443 Cmdlet Example: Invoke-PolicyEvaluation 444 Joining Columns 444 Retrieving an Entry 445 Summary 445 15 SQL Server Policy-Based Management 447 What's New in Policy-Based Management 447 Introduction to Policy-Based Management 448 Policy-Based Management Concepts 449 Facets 449 Conditions 452 Policies 453 Categories 453 Targets 453 Execution Modes 453 Central Management Servers 454 Implementing Policy-Based Management 456 Creating a Condition Based on a Facet 456 Creating a Category 460 Evaluating Policies 462 Importing and Exporting Policies 462 Sample Templates and Real-World Examples 464 Sample Policy Templates 464 Evaluating Recovery Models 464 Implementing Surface Area Configuration Checks 464 Ensuring Object Naming Conventions 465 Checking Best Practices Compliance 465 Policy-Based Management Best Practices 465 Summary 466 Part IV SQL Server Security Administration 16 Security and Compliance 469 Exposure and Risk 470 Across the Life Cycle 471 The Security Big Picture 472 Identity Access Management Components 474 Data Security Compliance and SQL Server 476 SQL Server Auditing 478 Setting Up Auditing via T-SQL 484 SQL Injection Is Easy to Do 485 Summary 487 17 Security and User Administration 489 What's New in Security and User Administration 489 An Overview of SQL Server Security 490 Authentication Methods 493 Windows Authentication Mode 493 Mixed Authentication Mode 493 Setting the Authentication Mode 493 Managing Principals 494 Logins 494 SQL Server Security: Users 497 The dbo User 498 The guest User 498 The INFORMATION_SCHEMA User 499 The sys User 499 User/Schema Separation 500 Roles 501 Fixed Server Roles 501 Fixed Database Roles 502 The public Role 505 User-Defined Database Roles 506 User-Defined Server Roles 507 Application Roles 508 Managing Securables 508 Managing Permissions 509 Managing SQL Server Logins 511 Using SSMS to Manage Logins 512 Using T-SQL to Manage Logins 516 Managing SQL Server Users 517 Using SSMS to Manage Users 517 Using T-SQL to Manage Users 519 Managing Database Roles 520 Using SSMS to Manage Database Roles 520 Using T-SQL to Manage Database Roles 521 Managing Server Roles 522 Using SSMS to Manage Server Roles 522 Using T-SQL to Manage Server Roles 522 Managing SQL Server Permissions 524 Using SSMS to Manage Permissions 524 Using SSMS to Manage Permissions at the Server Level 524 Using SSMS to Manage Permissions at the Database Level 527 Using SSMS to Manage Permissions at the Object Level 529 Using T-SQL to Manage Permissions 531 The Execution Context 533 Explicit Context Switching 533 Implicit Context Switching 534 Summary 535 18 Data Encryption 537 What's New in Data Encryption 538 An Overview of Data Security 538 An Overview of Data Encryption 539 SQL Server Key Management 541 Extensible Key Management 543 Column-Level Encryption 544 Encrypting Columns Using a Passphrase 545 Encrypting Columns Using a Certificate 547 Transparent Data Encryption 551 Implementing Transparent Data Encryption 552 Managing TDE in SSMS 554 Backing Up TDE Certificates and Keys 556 The Limitations of TDE 557 Column-Level Encryption Versus Transparent Data Encryption 557 Summary 558 Part V Database Administration 19 Creating and Managing Databases 563 What's New in Creating and Managing Databases 564 Data Storage in SQL Server 564 Database Files 565 Primary Files 566 Secondary Files 566 Using Filegroups 567 Using Partitions 570 Transaction Log Files 570 Creating Databases 572 Using SSMS to Create a Database 572 Using T-SQL to Create Databases 576 Setting Database Options 577 The Database Options 577 Using T-SQL to Set Database Options 580 Retrieving Option Information 581 Managing Databases 583 Managing File Growth 583 Expanding Databases 584 Shrinking Databases 585 Moving Databases 589 Restoring a Database to a New Location 590 Using ALTER DATABASE 590 Detaching and Attaching Databases 591 Contained Databases 594 Creating a Contained Database 594 Connecting to a Contained Database 597 Summary 598 20 Creating and Managing Tables 599 What's New in SQL Server 2012 599 Creating Tables 600 Using Object Explorer to Create Tables 600 Using Database Diagrams to Create Tables 601 Using T-SQL to Create Tables 602 Defining Columns 605 Data Types 605 Column Properties 612 Column Sets 618 Working with Sparse Columns 619 Sparse Columns: Good or Bad? 623 Defining Sparse Columns in SSMS 623 Defining Table Location 623 Defining Table Constraints 625 Modifying Tables 627 Using T-SQL to Modify Tables 627 Using Object Explorer and the Table Designer to Modify Tables 630 Using Database Diagrams to Modify Tables 633 Dropping Tables 635 Using Partitioned Tables 636 Creating a Partition Function 637 Creating a Partition Scheme 640 Creating a Partitioned Table 641 Adding and Dropping Table Partitions 644 Switching Table Partitions 648 Using FILESTREAM Storage 652 Enabling FILESTREAM Storage 653 Setting Up a Database for FILESTREAM Storage 656 Using FILESTREAM Storage for Data Columns 657 Using FileTables 660 FileTable Prerequisites 660 Creating FileTables 661 Copying Files to the FileTable 662 Creating Temporary Tables 663 Summary 664 21 Creating and Managing Indexes 665 What's New in Creating and Managing Indexes 665 Types of Indexes 666 Clustered Indexes 666 Nonclustered Indexes 668 Creating Indexes 669 Creating Indexes with T-SQL 670 Creating Indexes with SSMS 673 Managing Indexes 676 Managing Indexes with T-SQL 677 Managing Indexes with SSMS 680 Dropping Indexes 681 Online Indexing Operations 681 Indexes on Views 683 Summary 684 22 Implementing Data Integrity 685 What's New in Data Integrity 685 Types of Data Integrity 686 Domain Integrity 686 Entity Integrity 686 Referential Integrity 686 Enforcing Data Integrity 686 Implementing Declarative Data Integrity 686 Implementing Procedural Data Integrity 687 Using Constraints 687 The PRIMARY KEY Constraint 687 The UNIQUE Constraint 689 The FOREIGN KEY Referential Integrity Constraint 690 The CHECK Constraint 694 Creating Constraints 695 Managing Constraints 700 Rules 704 Defaults 705 Declarative Defaults 705 Bound Defaults 707 When a Default Is Applied 707 Restrictions on Defaults 709 Summary 710 23 Creating and Managing Views 711 What's New in Creating and Managing Views 711 Definition of Views 711 Using Views 712 Simplifying Data Manipulation 713 Focusing on Specific Data 714 Abstracting Data 715 Controlling Access to Data 716 Creating Views 718 Creating Views Using T-SQL 719 ENCRYPTION 721 Creating Views Using the View Designer 723 Managing Views 725 Altering Views with T-SQL 726 Dropping Views with T-SQL 726 Managing Views with SSMS 727 Data Modifications and Views 727 Partitioned Views 728 Modifying Data Through a Partitioned View 732 Distributed Partitioned Views 732 Indexed Views 734 Creating Indexed Views 734 Indexed Views and Performance 737 To Expand or Not to Expand 740 Summary 741 24 Creating and Managing Stored Procedures 743 What's New in Creating and Managing Stored Procedures 743 Advantages of Stored Procedures 743 Creating Stored Procedures 745 Creating Procedures in SSMS 746 Executing Stored Procedures 753 Executing Procedures in SSMS 754 Execution Context and the EXECUTE AS Clause 755 Using the WITH RESULT SETS Clause 758 Deferred Name Resolution 761 Identifying Objects Referenced in Stored Procedures 763 Viewing Stored Procedures 765 Modifying Stored Procedures 769 Viewing and Modifying Stored Procedures with SSMS 769 Using Input Parameters 771 Setting Default Values for Parameters 772 Passing Object Names as Parameters 775 Using Wildcards in Parameters 777 Using Table-Valued Parameters 778 Using Output Parameters 780 Returning Procedure Status 781 Debugging Stored Procedures Using SQL Server Management Studio 783 Using System Stored Procedures 785 Startup Procedures 789 Summary 793 25 Creating and Managing User-Defined Functions 795 Why Use User-Defined Functions? 795 Types of User-Defined Functions 798 Scalar Functions 798 Table-Valued Functions 801 Creating and Managing User-Defined Functions 803 Creating User-Defined Functions 803 Viewing and Modifying User-Defined Functions 814 Managing User-Defined Function Permissions 823 Rewriting Stored Procedures as Functions 824 Creating and Using CLR Functions 825 Adding CLR Functions to a Database 825 Deciding Between Using T-SQL or CLR Functions 827 Summary 828 26 Creating and Managing Triggers 829 What's New in Creating and Managing Triggers 830 Using DML Triggers 830 Creating DML Triggers 831 Using AFTER Triggers 833 Using inserted and deleted Tables 837 INSTEAD OF Triggers 841 Using DDL Triggers 849 Creating DDL Triggers 853 Managing DDL Triggers 857 Using CLR Triggers 859 Using Nested Triggers 862 Using Recursive Triggers 862 Summary 863 27 Transaction Management and the Transaction Log 865 What's New in Transaction Management 865 What Is a Transaction? 865 How SQL Server Manages Transactions 866 Defining Transactions 867 AutoCommit Transactions 867 Explicit User-Defined Transactions 868 Implicit Transactions 874 Implicit Transactions Versus Explicit Transactions 877 Transactions and Batches 877 Transactions and Stored Procedures 879 Transactions and Triggers 884 Triggers and Transaction Nesting 885 Triggers and Multistatement Transactions 888 Using Savepoints in Triggers 889 Transactions and Locking 890 READ_COMMITTED_SNAPSHOT Isolation 892 Coding Effective Transactions 892 Transaction Logging and the Recovery Process 893 The Checkpoint Process 894 Automatic Checkpoints 896 Indirect Checkpoints 897 Manual Checkpoints 899 The Recovery Process 900 Managing the Transaction Log 902 Long-Running Transactions 906 Distributed Transactions 909 Summary 910 28 Database Snapshots 911 What's New with Database Snapshots 912 What Are Database Snapshots? 912 Limitations and Restrictions of Database Snapshots 917 Copy-on-Write Technology 918 When to Use Database Snapshots 919 Reverting to a Snapshot for Recovery Purposes 919 Safeguarding a Database Prior to Making Mass Changes 921 Providing a Testing (or Quality Assurance) Starting Point (Baseline) 921 Providing a Point-in-Time Reporting Database 922 Providing a Highly Available and Offloaded Reporting Database from a Database Mirror 923 Setup and Breakdown of a Database Snapshot 924 Creating a Database Snapshot 925 Removing a Database Snapshot 930 Reverting to a Database Snapshot for Recovery 930 Reverting a Source Database from a Database Snapshot 930 Using Database Snapshots with Testing and QA 931 Database Snapshots Maintenance and Security Considerations 932 Security for Database Snapshots 932 Snapshot Sparse File Size Management 932 Number of Database Snapshots per Source Database 933 Summary 933 29 Database Maintenance 935 What's New in Database Maintenance 936 The Maintenance Plan Wizard 936 Backing Up Databases 937 Checking Database Integrity 941 Shrinking Databases 942 Maintaining Indexes and Statistics 943 Scheduling a Maintenance Plan 947 Managing Maintenance Plans Without the Wizard 950 Executing a Maintenance Plan 954 Maintenance Without a Maintenance Plan 955 Database Maintenance Policies 956 Summary 956 Part VI SQL Server Performance and Optimization 30 Data Structures, Indexes, and Performance 959 What's New for Data Structures, Indexes, and Performance 960 Understanding Data Structures 960 Database Files and Filegroups 961 Primary Data File 962 Secondary Data Files 963 The Log File 963 File Management 963 Using Filegroups 965 FILESTREAM Filegroups 967 Database Pages 969 Page Types 970 Data Pages 971 Row-Overflow Pages 977 LOB Data Pages 978 Index Pages 980 Space Allocation Structures 981 Extents 981 Global and Shared Global Allocation Map Pages 982 Page Free Space Pages 983 Index Allocation Map Pages 983 Differential Changed Map Pages 984 Bulk Changed Map Pages 984 Data Compression 985 Row-Level Compression 985 Page-Level Compression 988 The CI Record 990 Implementing Page Compression 990 Evaluating Page Compression 991 Managing Data Compression with SSMS 994 Understanding Table Structures 995 Heap Tables 997 Clustered Tables 999 Understanding Index Structures 1000 Clustered Indexes 1001 Nonclustered Indexes 1004 Columnstore Indexes 1008 Data Modification and Performance 1013 Inserting Data 1013 Deleting Rows 1016 Updating Rows 1017 Index Utilization 1018 Index Selection 1020 Evaluating Index Usefulness 1021 Index Statistics 1024 The Statistics Histogram 1026 How the Statistics Histogram Is Used 1028 Index Densities 1029 Estimating Rows Using Index Statistics 1030 Generating and Maintaining Index and Column Statistics 1031 SQL Server Index Maintenance 1040 Setting the Fill Factor 1050 Reapplying the Fill Factor 1052 Disabling Indexes 1052 Managing Indexes with SSMS 1054 Index Design Guidelines 1054 Clustered Index Indications 1055 Nonclustered Index Indications 1057 Index Covering 1059 Included Columns 1061 Wide Indexes Versus Multiple Indexes 1062 Indexed Views 1062 Indexes on Computed Columns 1064 Filtered Indexes and Statistics 1065 Creating and Using Filtered Indexes 1067 Creating and Using Filtered Statistics 1069 Choosing Indexes: Query Versus Update Performance 1070 Identifying Missing Indexes 1071 The Database Engine Tuning Advisor 1072 Missing Index Dynamic Management Objects 1072 Missing Index Feature Versus Database Engine Tuning Advisor 1074 Identifying Unused Indexes 1075 Summary 1078 31 Understanding Query Optimization 1079 What's New in Query Optimization 1080 What Is the Query Optimizer? 1080 Query Compilation and Optimization 1081 Compiling DML Statements 1081 Optimization Steps 1082 Query Analysis 1083 Identifying Search Arguments 1083 Identifying OR Clauses 1083 Identifying Join Clauses 1084 Row Estimation and Index Selection 1085 Evaluating SARG and Join Selectivity 1085 Estimating Access Path Cost 1090 Using Multiple Indexes 1097 Optimizing with Indexed Views 1104 Optimizing with Filtered Indexes 1107 Join Selection 1109 Join Processing Strategies 1109 Determining the Optimal Join Order 1114 Subquery Processing 1115 Execution Plan Selection 1118 Query Plan Caching 1121 Query Plan Reuse 1121 Query Plan Aging 1123 Recompiling Query Plans 1124 Monitoring the Plan Cache 1125 Other Query Processing Strategies 1133 Predicate Transitivity 1133 Group by Optimization 1134 Queries with DISTINCT 1134 Queries with UNION 1135 Queries Using Columnstore Indexes 1136 Parallel Query Processing 1137 Parallel Query Configuration Options 1138 Identifying Parallel Queries 1139 Common Query Optimization Problems 1140 Out-of-Date or Insufficient Statistics 1140 Poor Index Design 1141 Search Argument Problems 1141 Large Complex Queries 1143 Triggers 1143 Managing the Optimizer 1144 Optimizer Hints 1145 Forced Parameterization 1151 Using the USE PLAN Query Hint 1153 Using Plan Guides 1155 Limiting Query Plan Execution with the Query Governor 1163 Summary 1166 32 Query Analysis 1167 What's New in Query Analysis 1168 Query Analysis in SSMS 1168 Execution Plan ToolTips 1169 Logical and Physical Operator Icons 1173 Analyzing Stored Procedures 1181 Saving and Viewing Graphical Execution Plans 1181 Displaying Execution Plan XML 1183 Missing Index Hints 1184 SSMS Client Statistics 1188 Using the SET SHOWPLAN Options 1190 SHOWPLAN_TEXT 1190 SHOWPLAN_ALL 1192 SHOWPLAN_XML 1193 Using sys.dm_exec_query_plan 1194 Query Statistics 1196 STATISTICS IO 1196 STATISTICS TIME 1199 Using datediff() to Measure Runtime 1202 STATISTICS PROFILE 1203 STATISTICS XML 1203 Query Analysis with SQL Server Profiler 1204 Summary 1206 33 Locking and Performance 1207 What's New in Locking and Performance 1207 The Need for Locking 1207 Transaction Isolation Levels in SQL Server 1208 Read Uncommitted Isolation 1209 Read Committed Isolation 1210 Read Committed Snapshot Isolation 1211 Repeatable Read Isolation 1212 Serializable Read Isolation 1213 Snapshot Isolation 1213 The Lock Manager 1216 Monitoring Lock Activity in SQL Server 1217 Querying the sys.dm_tran_locks View 1217 Viewing Locking Activity with SQL Server Profiler 1221 Monitoring Locks with Performance Monitor 1223 SQL Server Lock Types 1225 Shared Locks 1226 Update Locks 1227 Exclusive Locks 1228 Intent Locks 1228 Schema Locks 1229 Bulk Update Locks 1230 SQL Server Lock Granularity 1230 Serialization and Key-Range Locking 1232 Using Application Locks 1236 Index Locking 1239 Row-Level Versus Page-Level Locking 1240 Lock Escalation 1241 Lock Compatibility 1243 Locking Contention and Deadlocks 1243 Identifying Locking Contention 1244 Setting the Lock Timeout Interval 1247 Minimizing Locking Contention 1248 Deadlocks 1249 Table Hints for Locking 1263 Transaction Isolation-Level Hints 1264 Lock Granularity Hints 1266 Lock Type Hints 1266 Optimistic Locking 1266 Optimistic Locking Using the rowversion Data Type 1267 Optimistic Locking with Snapshot Isolation 1269 Summary 1272 34 Database Design and Performance 1273 What's New in Database Design and Performance 1273 Basic Tenets of Designing for Performance 1274 Logical Database Design Issues 1275 Normalization Conditions 1275 Normalization Forms 1275 Benefits of Normalization 1277 Drawbacks of Normalization 1277 Denormalizing a Database 1277 Denormalization Guidelines 1278 Essential Denormalization Techniques 1279 Database Filegroups and Performance 1285 RAID Technology 1287 RAID Level 0 1287 RAID Level 1 1289 RAID Level 10 1290 RAID Level 5 1290 SQL Server and SAN Technology 1292 What Is a SAN? 1292 SAN Considerations for SQL Server 1293 Summary 1294 35 Monitoring SQL Server Performance 1295 What's New in Monitoring SQL Server Performance 1296 Performance Monitoring Tools 1297 The Data Collector and the MDW 1297 SQL Server Utility 1319 SQL Server Extended Events 1323 Windows Performance Monitor 1359 A Performance Monitoring Approach 1371 Monitoring the Network Interface 1372 Monitoring the Processors 1374 Monitoring Memory 1379 Monitoring the Disk System 1382 Monitoring SQL Server's Disk Activity 1384 Monitoring Other SQL Server Performance Items 1385 Summary 1386 36 SQL Server Database Engine Tuning Advisor 1387 What's New in SQL Server Database Engine Tuning Advisor 1387 SQL Server Instance Architecture 1388 Database Engine Tuning Advisor 1389 The Database Engine Tuning Advisor GUI 1390 The Database Engine Tuning Advisor Command Line 1397 Summary 1405 37 Managing Workloads with the Resource Governor 1407 What's New for Resource Governor 1408 Overview of Resource Governor 1408 Resource Governor Components 1410 Classification 1410 Resource Pools 1410 Workload Groups 1412 Configuring Resource Governor 1413 Enabling Resource Governor 1413 Defining Resource Pools 1415 Defining Workload Groups 1417 Creating a Classifier Function 1421 Monitoring Resource Usage 1424 Modifying Your Resource Governor Configuration 1428 Deleting Workload Groups 1429 Deleting Resource Pools 1430 Modifying a Classifier Function 1431 Summary 1431 38 A Performance and Tuning Methodology 1433 The Full Architectural Landscape 1434 Primary Performance and Tuning Handles 1435 A Performance and Tuning Methodology 1436 Designing In Performance and Tuning from the Start 1437 Code and Test 1440 Performance and Tuning for an Existing Implementation 1442 Performance and Tuning Design Guidelines 1448 Hardware and Operating System Guidelines 1448 SQL Server Instance Guidelines 1450 Database-Level Guidelines 1451 Table Design Guidelines 1451 Indexing Guidelines 1453 View Design Guidelines 1455 Transact-SQL Guidelines 1455 Application Design Guidelines 1459 Distributed Data Guidelines 1460 High-Availability Guidelines 1460 Tools of the Performance and Tuning Trade 1461 Microsoft Out-of-the-Box 1461 Third-Party Performance and Tuning Tools 1462 Summary 1464 Part VII SQL Server High Availability 39 SQL Server High Availability Fundamentals 1467 What's New in High Availability 1468 What Is High Availability? 1469 The Fundamentals of HA 1471 Hardware Factors 1471 Backup Considerations 1471 Operating System Upgrades 1472 Vendor Agreements Followed 1472 Training Kept Up-to-Date 1472 Quality Assurance Done Well 1472 Standards/Procedures Followed 1472 Server Instance Isolation 1472 Building Solutions with One or More HA Options 1474 Failover Cluster Services (FCS) 1475 SQL Clustering 1476 AlwaysOn Failover Clustering Instance (FCI) 1478 AlwaysOn Availability Groups 1479 Data Replication 1480 Change Data Capture 1482 Log Shipping 1482 Database Mirroring 1484 Combining Failover with Scale-Out Options 1485 Other HA Techniques That Yield Great Results 1486 High Availability from the Windows Server Family Side 1489 Microsoft Virtual Machines and Hyper-V 1489 Summary 1490 40 Data Replication 1493 What's New in Data Replication 1494 What Is Replication? 1495 The Publisher, Distributor, and Subscriber Magazine Metaphor 1497 Publications and Articles 1498 Filtering Articles 1498 Replication Scenarios 1503 The Central Publisher Replication Model 1503 The Central Publisher with Remote Distributor Replication Model 1504 The Publishing Subscriber Replication Model 1506 The Central Subscriber Replication Model 1507 The Multiple Publishers with Multiple Subscribers Replication Model 1507 The Updating Subscribers Replication Model 1509 The Peer-to-Peer Replication Model 1511 Subscriptions 1511 Anonymous Subscriptions (Pull Subscriptions) 1513 The Distribution Database 1513 Replication Agents 1515 The Snapshot Agent 1516 The Log Reader Agent 1518 The Distribution Agent 1519 The Merge Agent 1520 Other Specialized Agents 1520 Planning for SQL Server Data Replication 1521 Autonomy, Timing, and Latency of Data 1522 Methods of Data Distribution 1522 SQL Server Replication Types 1523 Snapshot Replication 1523 Transactional Replication 1524 Merge Replication 1525 Basing the Replication Design on User Requirements 1526 Data Characteristics 1528 Setting Up Replication 1529 Creating a Distributor and Enabling Publishing 1529 Creating a Publication 1535 Horizontal and Vertical Filtering 1542 Creating Subscriptions 1544 Scripting Replication 1550 Monitoring Replication 1555 Replication Monitoring SQL Statements 1555 Monitoring Replication within SQL Server Management Studio 1557 Troubleshooting Replication Failures 1559 Peer-to-Peer Replication 1560 The Performance Monitor 1566 Replication in Heterogeneous Environments 1566 Backup and Recovery in a Replication Configuration 1567 Some Thoughts on Performance 1568 Log Shipping 1569 Data Replication and Database Mirroring or AlwaysOn Features for Fault Tolerance and High Availability 1569 Change Data Capture 1570 The Change Data Capture Tables 1571 Enabling CDC for a Database 1572 Enabling CDC for a Table 1573 Querying the CDC Tables 1575 CDC and DDL Changes to Source Tables 1581 CDC and AlwaysOn Availability Groups 1582 Change Tracking 1582 Implementing Change Tracking 1583 Identifying Tracked Changes 1585 Identifying Changed Columns 1589 Change Tracking Overhead 1590 Summary 1591 41 Database Mirroring 1593 What's New in Database Mirroring 1594 What Is Database Mirroring? 1594 Copy-on-Write Technology 1596 When to Use Database Mirroring 1597 Roles of the Database Mirroring Configuration 1597 Playing Roles and Switching Roles 1598 Database Mirroring Operating Modes 1598 Setting Up and Configuring Database Mirroring 1599 Getting Ready to Mirror a Database 1600 Creating the Endpoints 1603 Granting Permissions 1605 Creating the Database on the Mirror Server 1605 Identifying the Other Endpoints for Database Mirroring 1607 Configuring Database Mirroring by Using the Wizard 1609 Monitoring a Mirrored Database Environment 1616 Removing Mirroring 1619 Testing Failover from the Principal to the Mirror 1621 Client Setup and Configuration for Database Mirroring 1623 Using Replication and Database Mirroring Together 1624 Using Database Snapshots from a Mirror for Reporting 1625 Summary 1627 42 SQL Server Failover Clustering 1629 What's New in SQL Server Failover Clustering 1630 How Microsoft SQL Server Failover Clustering Works 1630 Understanding WSFC 1632 Extending WSFC with NLB 1636 How WSFC Sets the Stage for SQL Server Clustering 1637 Installing SQL Server Failover Clustering 1638 Configuring SQL Server Database Disks 1639 Installing Network Interfaces 1641 Installing WSFC 1641 Cluster Events 1642 Installing SQL Server within WSFC 1642 Fail Over to Another Node 1655 The Client Connection Impact of a Failover 1655 Potential Problems to Watch Out for with SQL Server Failover Clustering 1658 Summary 1659 43 SQL Server AlwaysOn and Availability Groups 1661 SQL Server AlwaysOn and Availability Groups 1661 Windows Failover Cluster Services 1662 AlwaysOn Failover Clustering Instances 1662 AlwaysOn Availability Groups 1663 Combining Failover with Scale-Out Options 1666 Building a Multinode AlwaysOn Configuration 1666 Adding Replicas 1681 Dashboard and Monitoring 1681 Summary 1683 Part VIII SQL Server Application Development 44 What's New for Transact-SQL in SQL Server 2012 1687 THROW Statement 1687 The SEQUENCE Object 1693 SEQUENCE Objects Versus Identity Columns 1701 New Conversion Functions 1702 The PARSE Function 1702 The TRY_PARSE Function 1703 The TRY_CONVERT Function 1704 New Logical Functions 1705 The CHOOSE Function 1705 The IIF Function 1706 New String Functions 1706 CONCAT 1706 FORMAT 1708 New date and time Functions 1714 Some Recently Added Date and Time Features You May Have Missed 1717 Date and Time Conversions 1720 SQL Server 2012 Enhancements to Windowing Functions 1722 The OVER Clause 1722 Ranking Functions 1723 Calculating Aggregates with the OVER Clause 1729 SQL Server 2012 Enhancements to Windowing Functions 1730 Ad Hoc Query Paging 1744 New Features and Enhancements to Spatial Data 1747 The Spatial Data Types 1747 Spatial Instance Types 1748 Other SQL Server 2012 Enhancements 1749 Representing Spatial Data in SQL Server 2012 1750 Working with Geometry Data 1751 Working with Geography Data 1754 Spatial Data Support in SSMS 1756 Spatial Data Types: Where to Go from Here? 1759 Summary 1759 45 Transact-SQL Programming Guidelines, Tips, and Tricks 1761 General T-SQL Coding Recommendations 1762 Provide Explicit Column Lists 1762 Qualify Object Names with a Schema Name 1764 Avoid SQL Injection Attacks When Using Dynamic SQL 1768 Comment Your T-SQL Code 1778 General T-SQL Performance Recommendations 1779 UNION Versus UNION ALL Performance 1779 Use IF EXISTS Instead of SELECT COUNT(*) 1779 Avoid Unnecessary ORDER BY or DISTINCT Clauses 1779 Temp Tables Versus Table Variables Versus Common Table Expressions 1780 Avoid Unnecessary Function Executions 1781 Cursors and Performance 1782 Variable Assignment in UPDATE Statements 1784 In Case You Missed It: Recently Added T-SQL Features 1788 TOP Enhancements 1788 The OUTPUT Clause 1793 MERGE Statement 1798 MERGE Statement Best Practices and Guidelines 1804 Insert over DML 1805 Common Table Expressions 1807 Recursive Queries with CTEs 1809 Using the Hierarchyid Data Type 1818 Creating a Hierarchy 1818 Populating the Hierarchy 1819 Querying the Hierarchy 1821 Modifying the Hierarchy 1825 Using Row Numbers for Paging Results 1830 De-Duping Data with Ranking Functions 1834 PIVOT and UNPIVOT 1836 The APPLY Operator 1841 CROSS APPLY 1841 OUTER APPLY 1843 The TABLESAMPLE Clause 1843 Variable Assignment in DECLARE Statements 1847 Compound Assignment Operators 1848 GROUP BY Clause Enhancements 1848 GROUPING SETS 1849 The grouping_id() Function 1852 General T-SQL Tips and Tricks 1854 Date Calculations 1854 Using CONTEXT_INFO 1857 Working with Outer Joins 1859 Generating T-SQL Statements with T-SQL 1869 Summary 1869 46 Advanced Stored Procedure Programming and Optimization 1871 T-SQL Stored Procedure Coding Guidelines 1871 Calling Stored Procedures from Transactions 1874 Handling Errors in Stored Procedures 1877 Using Cursors in Stored Procedures 1881 Using CURSOR Variables in Stored Procedures 1886 Nested Stored Procedures 1892 Recursive Stored Procedures 1893 Using Temporary Tables in Stored Procedures 1897 Temporary Table Performance Tips 1899 Using the table Data Type 1901 Stored Procedure Performance 1903 Query Plan Caching 1904 The SQL Server Plan Cache 1904 Shared Query Plans 1905 Automatic Query Plan Recompilation 1905 Forcing Recompilation of Query Plans 1908 Using Dynamic SQL in Stored Procedures 1912 Using sp_executesql 1915 Installing and Using .NET CLR Stored Procedures 1918 Adding CLR Stored Procedures to a Database 1919 T-SQL or CLR Stored Procedures? 1920 Using xp_cmdshell 1921 Summary 1922 47 SQL Server and the .NET Framework 1925 What's New in SQL Server 2012 and the .NET Framework 1925 Getting Comfortable with ADO.NET 4.5 and SQL Server 2012 1926 Essential ADO.NET Development Techniques 1926 Developing with LINQ to SQL 1933 Getting Started with LINQ to SQL 1933 Going Deeper 1935 Uncovering LINQ to SQL with LINQPad 1938 Introducing the Entity Framework 1942 Getting Started 1943 Data Operations with EF Entities 1946 Using WCF Data Services 1951 Getting Set Up 1951 Essentials 1951 Building Your Data Service 1951 CRUD Operations 1958 Summary 1963 48 SQLCLR: Developing SQL Server Objects in .NET 1965 What's New for SQLCLR in SQL Server 2012 1965 Developing Custom Managed Database Objects 1968 An Introduction to Custom Managed Database Objects 1968 Managed Object Permissions 1969 Developing Managed Objects with Visual Studio 2012 1971 Developing Managed Stored Procedures 1972 Developing Managed User-Defined Functions (UDFs) 1977 Developing Managed User-Defined Types (UDTs) 1987 Developing Managed User-Defined Aggregates (UDAs) 1995 Developing Managed Triggers 1998 Using Transactions 2004 Using the Related System Catalogs 2006 Summary 2007 49 Using XML in SQL Server 2012 2009 What's New in Using XML in SQL Server 2012 2009 Understanding XML 2011 Relational Data as XML: The FOR XML Modes 2011 RAW Mode 2012 AUTO Mode 2018 EXPLICIT Mode 2022 PATH Mode 2026 FOR XML and the xml Data Type 2029 XML as Relational Data: Using OPENXML 2031 Using the xml Data Type 2035 Defining and Using xml Columns 2036 Using XML Schema Collections 2039 The Built-in xml Data Type Methods 2045 Indexing and Full-Text Indexing of xml Columns 2063 Indexing xml Columns 2064 Full-Text Indexing xml Columns 2077 Summary 2078 50 SQL Server Service Broker 2079 What's New in Service Broker 2079 Understanding Distributed Messaging 2080 The Basics of Service Broker 2080 Example System Design 2084 Understanding Service Broker Constructs 2085 Defining Messages and Choosing a Message Type 2085 Setting Up Contracts for Communication 2090 Creating Queues for Message Storage 2091 Defining Services to Send and Receive Messages 2093 Planning Conversations Between Services 2094 Service Broker Routing and Security 2105 Using Certificates for Conversation Encryption 2105 A Final Note on the Sample System 2113 Troubleshooting SSB Applications with ssbdiagnose.exe 2113 AlwaysOn Availability Group Support 2114 Related System Catalogs 2115 Summary 2117 51 SQL Server Full-Text Search 2119 What's New in SQL Server 2012 Full-Text Search 2120 Installing SQL FTS 2121 Upgrade Options in SQL Server 2012 2123 How SQL Server FTS Works 2124 Indexing 2125 Searching 2131 Implementing SQL Server 2012 Full-Text Catalogs 2131 Setting Up a Full-Text Index 2134 Using T-SQL Commands to Build Full-Text Indexes 2134 Using the Full-Text Indexing Wizard to Build Full-Text Indexes and Catalogs 2152 Full-Text Searches 2155 CONTAINS and CONTAINSTABLE 2155 FREETEXT and FREETEXTTABLE 2162 Semantic Search 2164 Full-Text Search Maintenance 2166 Full-Text Search Performance 2167 Full-Text Search Troubleshooting 2167 Summary 2169 52 Working with SQL Azure 2171 Setting Up Subscriptions, Servers, and Databases 2171 Setting Up Your Windows Azure Subscription 2172 Creating a Logical Server 2174 Managing Your Server 2176 Configuring Your Firewall 2176 Using SQL Server Management Studio 2177 Using Management Portal 2179 Working with Databases 2179 Understanding SQL Database Editions 2179 Managing Databases Using T-SQL 2183 Migrating Data into SQL Database 2184 Copying Databases 2186 Backing Up and Restoring Databases 2187 Using Database Copies for Backup and Restore 2187 Using BACPAC Files for Backup and Restore 2187 Managing Logins, Users, and Roles 2189 Understanding Roles 2190 Managing Logins and Users 2190 Using SQL Database with ADO.NET 2192 Connecting to SQL Database Using a Custom Windows Application 2192 Connectivity Limitations 2196 Understanding SQL Database Billing 2203 Baseline Billing 2203 Tracking Your Usage 2204 Understanding SQL Database Limitations 2206 Unsupported and Partially Supported Functionality 2206 References 2209 Summary 2209 Part IX SQL Server Business Intelligence Features 53 SQL Server 2012 Analysis Services 2213 What's New in SSAS 2213 Understanding SSAS and OLAP 2215 Understanding the SSAS Environment Wizards 2217 OLAP Versus OLTP 2221 An Analytics Design Methodology 2223 An Analytics Mini-Methodology 2223 An OLAP Requirements Example: CompSales International 2225 CompSales International Requirements 2225 OLAP Cube Creation 2227 Using SQL Server SSDT 2228 Creating an OLAP Database 2229 Defining Dimensions and Hierarchies 2235 Creating the Other Dimensions 2244 Creating the Cube 2246 Building and Deploying the Cube 2249 Populating the Cube with Data 2251 Aggregating Data Within the Cube 2252 Browsing Data in the Cube 2257 Delivering Data to Users 2261 ADO MD 2263 Query Analysis and Optimization 2264 Generating a Relational Database 2266 Limitations of a Relational Database 2267 Cube Perspectives 2267 Data Mining 2268 Security and Roles 2278 Tabular Models and SSAS 2279 OLAP Performance 2280 Summary 2281 54 SQL Server Integration Services 2283 What's New with SSIS 2284 SSIS Basics 2285 SSIS Architecture and Concepts 2290 SSIS Tools and Utilities 2293 A Data Transformation Requirement 2298 Running the SSIS Import and Export Wizard 2300 The SSIS Designer 2309 The Package Execution Utility 2318 The dtexec Utility 2320 Running Packages 2321 Running Package Examples 2325 The dtutil Utility 2326 dtutil Examples 2328 Change Data Capture and SSIS 2333 Using bcp 2333 Fundamentals of Exporting and Importing Data 2335 File Data Types 2338 Format Files 2339 Using Views 2348 Logged and Nonlogged Operations 2348 Batches 2349 Parallel Loading 2349 Supplying Hints to bcp 2350 Summary 2352 55 SQL Server 2012 Reporting Services 2353 What's New in SSRS 2012 2353 Discontinued Functionality and Breaking Changes 2354 Reporting Services Architecture 2355 HTTP Architecture 2358 Installing and Configuring SSRS 2358 The Installation Sequence 2359 SSRS Configuration Using RSCM 2362 Developing Reports 2367 Tools of the Trade 2367 Report Basics 2368 Overview of the Report Development Process 2369 Data Planning and Preparation 2369 Using Shared Data Sources 2370 Using Datasets 2370 Using Shared Datasets 2371 Developing Reports Using SSDT 2371 Working with the Tablix 2379 Understanding Expressions 2380 Report Design Fundamentals 2383 Designing Reports Using Report Builder 2394 Report Builder and Report Model Security 2409 Enabling Report Builder 2410 Management and Security 2410 Securing Reports 2410 Subscriptions 2412 Report Execution Options 2414 Performance and Monitoring 2416 SSRS Trace Log 2416 Execution Log 2416 Windows Event Log 2417 Performance Counters 2417 Summary 2417 56 Master Data Services and Data Quality Services 2419 Master Data Services 2419 Data Quality Services 2420 What's New in MDS and DQS 2420 Master Data Management 2421 Master Data Services Terms and Concepts 2423 Master Data Services 2426 Data Quality Services 2446 Summary 2460 57 Parallel Data Warehouse 2463 What's New in Parallel Data Warehouse 2463 Understanding MPP and PDW 2464 MPP Architecture 2465 The PDW 2466 Data on a PDW 2468 PDW and Big Data (Hadoop) 2471 xVelocity Columnstore Indexes 2471 Columnstore Indexes 2472 Summary 2477 Part X Bonus Chapters 58 Managing Linked Servers 2481 What's New in Managing Linked Servers 2482 Linked Servers 2483 Distributed Queries 2483 Distributed Transactions 2484 Adding, Dropping, and Configuring Linked Servers 2485 sp_addlinkedserver 2485 sp_linkedservers 2492 sp_dropserver 2494 sp_serveroption 2494 Mapping Local Logins to Logins on Linked Servers 2495 sp_addlinkedsrvlogin 2496 sp_droplinkedsrvlogin 2497 sp_helplinkedsrvlogin 2498 Obtaining General Information About Linked Servers 2499 Executing a Stored Procedure via a Linked Server 2500 Setting Up Linked Servers Using SQL Server Management Studio 2501 Summary 2505 59 SQL Server Disaster Recovery Planning 2507 What's New in SQL Server Disaster Recovery Planning 2508 How to Approach Disaster Recovery 2508 Disaster Recovery Patterns 2510 Recovery Objectives 2514 A Data-Centric Approach to Disaster Recovery 2516 Microsoft SQL Server Options for Disaster Recovery 2516 Data Replication 2517 Log Shipping 2519 Database Mirroring and Snapshots 2519 Change Data Capture 2521 AlwaysOn Availability Groups 2521 The Overall Disaster Recovery Process 2522 The Focus of Disaster Recovery 2523 Planning and Executing a Disaster Recovery 2530 Have You Detached a Database Recently? 2530 Third-Party Disaster Recovery Alternatives 2530 Summary 2531 60 SQL Server Configuration Options 2533 What's New in Configuring, Tuning, and Optimizing SQL Server Options 2533 SQL Server Instance Architecture 2534 Configuration Options 2535 Fixing an Incorrect Option Setting 2542 Setting Configuration Options with SSMS 2543 Obsolete Configuration Options 2543 Configuration Options and Performance 2544 access check cache bucket count 2544 access check cache quota 2544 ad hoc distributed queries 2545 affinity I/O mask 2545 affinity mask 2547 Agent XP 2548 backup compression default 2549 blocked process threshold 2550 c2 audit mode 2550 clr enabled 2550 common criteria compliance enabled 2551 contained database authentication 2551 cost threshold for parallelism 2552 cross db ownership chaining 2553 cursor threshold 2553 Database Mail XPs 2554 default full-text language 2554 default language 2556 default trace enabled 2558 disallow results from triggers 2558 EKM provider enabled 2559 filestream_access_level 2559 fill factor 2559 index create memory 2560 in-doubt xact resolution 2560 lightweight pooling 2561 locks 2561 max degree of parallelism 2562 max server memory and min server memory 2562 max text repl size 2564 max worker threads 2565 media retention 2566 min memory per query 2566 nested triggers 2567 network packet size 2567 Ole Automation Procedures 2568 optimize for ad hoc workloads 2568 PH_timeout 2569 priority boost 2569 query governor cost limit 2570 query wait 2571 recovery interval 2571 remote access 2572 remote admin connections 2572 remote login timeout 2573 remote proc trans 2573 remote query timeout 2573 scan for startup procs 2575 show advanced options 2575 user connections 2575 user options 2577 XP-Related Configuration Options 2577 Summary 2578 Index 2579

Szczegóły: Microsoft SQL Server 2012 Unleashed - Alex Silverstein, Ray Rankins, Chris Gallelli

Tytuł: Microsoft SQL Server 2012 Unleashed
Autor: Alex Silverstein, Ray Rankins, Chris Gallelli
Producent: Sams Publishing
ISBN: 9780672336928
Rok produkcji: 2013
Ilość stron: 1872
Waga: 2.43 kg

Recenzje: Microsoft SQL Server 2012 Unleashed - Alex Silverstein, Ray Rankins, Chris Gallelli