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

The industry's most complete, useful, and up-to-date guide to SQL Server 2014. 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 examples you'll need to install, monitor, maintain, and optimize the most complex database environments. The provided examples and sample code provide plenty of hands-on opportunities to learn more about SQL Server and create your own viable solutions. Four leading SQL Server experts present deep practical insights for administering SQL Server, analyzing and optimizing queries, implementing data warehouses, ensuring high availability, tuning performance, and much more. You will benefit from their behind-the-scenes look into SQL Server, showing what goes on behind the various wizards and GUI-based tools. You'll learn how to use the underlying SQL commands to fully unlock the power and capabilities of SQL Server. Writing for all intermediate-to-advanced-level SQL Server professionals, the authors draw on immense production experience with SQL Server. Throughout, they focus on successfully applying SQL Server 2014's most powerful capabilities and its newest tools and features. Detailed information on how to...* Understand SQL Server 2014's new features and each edition's capabilities and licensing * Install, upgrade to, and configure SQL Server 2014 for better performance and easier management * Streamline and automate key administration tasks with Smart Admin * Leverage powerful new backup/restore options: flexible backup to URL, Managed Backup to Windows Azure, and encrypted backups * Strengthen security with new features for enforcing "least privilege" * Improve performance with updateable columnstore indexes, Delayed Durability, and other enhancements * Execute queries and business logic more efficiently with memoryoptimized tables, buffer pool extension, and natively-compiled stored procedures * Control workloads and Disk I/O with the Resource Governor * Deploy AlwaysOn Availability Groups and Failover Cluster Instances to achieve enterprise-class availability and disaster recovery * Apply new Business Intelligence improvements in Master Data Services, data quality, and Parallel Data WarehouseIntroduction 1 Who This Book Is For 2 What This Book Covers 2 Conventions Used in This Book 4 Good Luck! 5 Part I Welcome to Microsoft SQL Server 1 SQL Server 2014 Overview 9 SQL Server Components and Features 9 The SQL Server Database Engine 10 SQL Server 2014 Administration and Management Tools 12 Replication 15 Merge Replication 16 SQL Server AlwaysOn Features 17 SQL Server Service Broker 18 Full-Text and Semantic Search 18 SQL Server Integration Services (SSIS) 20 SQL Server Analysis Services (SSAS) 21 SQL Server Reporting Services (SSRS) 23 Master Data Services 23 Data Quality Services 24 SQL Server 2014 Editions 24 SQL Server 2014 Standard Edition 25 SQL Server 2014 Enterprise Edition 26 Differences Between the Enterprise and Standard Editions of SQL Server 26 Other SQL Server 2014 Editions 28 SQL Server Licensing 30 Web Edition Licensing 31 Developer Edition Licensing 32 Express Edition Licensing 32 Choosing a Licensing Model 32 Mixing Licensing Models 32 Licensing SQL Server of High Availability 32 Licensing SQL Server in a Virtual Environment 34 Summary 35 2 What's New in SQL Server 2014 37 New SQL Server 2014 Features 37 Memory-Optimized Tables/In-Memory OLTP 38 New Cardinality Estimation Logic 38 Delayed Durability for Transactions 38 Buffer Pool Extension 38 SQL Server Data Tools for Business Intelligence 39 SQL Server 2014 Enhancements 39 Resource Governor Enhancements 39 Security Enhancements 39 Backup and Restore Enhancements 40 Indexing Enhancements 40 Monitoring Enhancements 41 SQL Server AlwaysOn and Availability Groups Enhancements 42 New Transact-SQL Enhancements 42 Deprecated and Discontinued Features 42 Summary 45 Part II SQL Server Tools and Utilities 3 SQL Server Management Studio 49 What's New in SSMS 50 The Integrated Environment 50 Window Management 50 Integrated Help 53 Administration Tools 56 Registered Servers 56 Object Explorer 58 Activity Monitor 60 Log File Viewer 62 SQL Server Utility 64 Development Tools 69 The Query Editor 69 Managing Projects in SSMS 77 Integrating SSMS with Source Control 78 Using SSMS Templates 80 Using SSMS Snippets 84 T-SQL Debugging 85 Multiserver Queries 86 Summary 87 4 SQL Server Command-Line Utilities 89 What's New in SQL Server Command-Line Utilities 90 The sqlcmd Command-Line Utility 91 Executing the sqlcmd Utility 93 Using Scripting Variables with sqlcmd 95 The dta Command-Line Utility 96 The tablediff Command-Line Utility 99 The bcp Command-Line Utility 102 The sqldiag Command-Line Utility 103 The sqlservr Command-Line Utility 105 The sqlLocalDB Command-Line Utility 106 Summary 108 5 SQL Server Profiler 111 What's New with SQL Server Profiler 111 SQL Server Profiler Architecture 112 Creating Traces 113 Events 115 Data Columns 117 Filters 120 Executing Traces and Working with Trace Output 122 Saving and Exporting Traces 123 Saving Trace Output to a File 123 Saving Trace Output to a Table 124 Saving the Profiler GUI Output 124 Importing Trace Files 125 Importing a Trace File into a Trace Table 125 Analyzing Trace Output with the Database Engine Tuning Advisor 128 Replaying Trace Data 128 Defining Server-Side Traces 131 Monitoring Running Traces 141 Stopping Server-Side Traces 143 Profiler Usage Scenarios 145 Analyzing Slow Stored Procedures or Queries 145 Deadlocks 146 Identifying Ad Hoc Queries 148 Identifying Performance Bottlenecks 148 Monitoring Auto-Update Statistics 150 Monitoring Application Progress 151 Summary 153 6 SQL Distributed Replay 155 What's New for Distributed Replay 155 Overview of Distributed Replay 155 Distributed Replay Components 156 Distributed Replay Administrative Tool 157 Distributed Replay Controller 157 Distributed Replay Clients 158 Target Server 158 Configuring Distributed Replay 158 Controller Configuration File 159 Client Configuration File 159 Preprocess Configuration File 160 Replay Configuration File 161 Replay the Trace Data 163 Configure Permissions and Security 163 Capture the Workload 165 Preprocess the Trace File 166 Apply the Workload 167 Summary 169 Part III SQL Server Administration 7 SQL Server System and Database Administration 173 What's New in SQL Server System and Database Administration 173 System Administrator Responsibilities 174 System Databases 174 The master Database 175 The resource Database 176 The model Database 176 The msdb Database 176 The distribution Database 176 The tempdb Database 177 Maintaining System Databases 177 System Tables 178 System Views 179 Compatibility Views 180 Catalog Views 182 Information Schema Views 184 Dynamic Management Views 186 System Stored Procedures 189 Useful System Stored Procedures 189 Summary 191 8 Installing SQL Server 2014 193 What's New in Installing SQL Server 2014 193 Installation Requirements 193 Hardware Requirements 194 Software Requirements 195 Installation Walkthrough 198 Install Screens, Step-by-Step 198 Installing SQL Server Documentation 217 Installing SQL Server Using a Configuration File 219 Running an Automated or Manual Install 224 Installing SQL Server Using Sysprep 226 Preparing a SQL Server Sysprep Image 226 Completing a SQL Server Sysprep Image 229 Modifying a SQL Server Sysprep Image 231 Common Uses of SQL Server Sysprep Images 232 Installing Service Packs and Cumulative Updates 233 Applying a Service Pack or Cumulative Update During a New Installation 233 Summary 236 9 Upgrading to SQL Server 2014 237 What's New in Upgrading SQL Server 237 The SQL Server 2014 Upgrade Matrix 237 Identifying Products and Features to be Upgraded 240 Using the SQL Server Upgrade Advisor (UA) 241 Getting Started with the UA 241 The Analysis Wizard 243 The Report Viewer 249 Destination: SQL Server 2014 250 Side-by-Side Upgrades 251 Upgrading In-Place 259 Upgrading the Database Engine 260 Installing Product Updates (Slipstreaming) During Upgrades 263 Upgrading Using a Configuration File 264 Upgrading from Pre-SQL Server 2005 Versions 266 Upgrading Other SQL Server Components 266 Upgrading Analysis Services 266 Upgrading SQL Server Analysis Services 266 Upgrading Reporting Services 266 Upgrading SSIS Packages 269 Migrating DTS Packages 271 Summary 271 10 Client Installation and Configuration 273 What's New in Client Installation and Configuration 273 Client/Server Networking Considerations 274 Server Network Protocols 275 The Server Endpoint Layer 277 The Role of SQL Browser 280 Client Installation 281 Installing the Client Tools 281 Installing SNAC 282 Client Configuration 284 Client Configuration Using SSCM 284 Connection Encryption 287 Client Data Access Technologies 289 Provider Choices 290 Connecting Using the Various Providers and Drivers 291 General Networking Considerations and Troubleshooting 296 Summary 299 11 Database Backup and Restore 301 What's New in Database Backup and Restore 301 Developing a Backup and Restore Plan 302 Types of Backups 303 Full Database Backups 304 Differential Database Backups 304 Partial Backups 305 Differential Partial Backups 305 File and Filegroup Backups 305 Copy-Only Backups 306 Transaction Log Backups 306 Recovery Models 306 Full Recovery 307 Bulk-Logged Recovery 308 Simple Recovery 309 Backup Devices 310 Disk Devices 310 Tape Devices 310 Network Shares 311 Media Sets and Families 311 Creating Backup Devices 311 Backing Up a Database 312 Creating Database Backups with SSMS 312 Creating Database Backups with T-SQL 315 Backing Up the Transaction Log 318 Creating Transaction Log Backups with SSMS 318 Creating Transaction Log Backups with T-SQL 319 Backup Scenarios 320 Full Database Backups Only 320 Full Database Backups with Transaction Log Backups 321 Differential Backups 322 Partial Backups 323 File/Filegroup Backups 325 Mirrored Backups 326 Copy-Only Backups 326 Compressed Backups 327 Encrypted Backups 328 System Database Backups 329 Restoring Databases and Transaction Logs 330 Restores with T-SQL 330 Restoring by Using SSMS 334 Restore Information 339 Restore Scenarios 342 Restoring to a Different Database 342 Restoring a Snapshot 344 Restoring a Transaction Log 344 Restoring to the Point of Failure 345 Restoring to a Point in Time 347 Online Restores 349 Restoring the System Databases 349 Additional Backup Considerations 351 Frequency of Backups 352 Using a Standby Server 352 Snapshot Backups 353 Considerations for Very Large Databases 354 Maintenance Plans 354 Summary 355 12 Database Mail 357 What's New in Database Mail 357 Setting Up Database Mail 358 Creating Mail Profiles and Accounts 359 Using T-SQL to Update and Delete Mail Objects 362 Setting System-Wide Mail Settings 363 Testing Your Setup 364 Sending and Receiving with Database Mail 364 The Service Broker Architecture 364 Sending Email 365 Receiving Email 371 Using SQL Server Agent Mail 371 Job Mail Notifications 371 Creating an Operator 371 Enabling SQL Agent Mail 371 Creating the Job 372 Testing the Job-Completion Notification 373 Alert Mail Notifications 373 Creating an Alert 373 Testing the Alert Notification 374 Related Views and Procedures 375 Viewing the Mail Configuration Objects 375 Viewing Mail Message Data 376 Summary 377 13 SQL Server Agent 379 What's New in Scheduling and Notification 380 Configuring the SQL Server Agent 380 Configuring SQL Server Agent Properties 380 Configuring the SQL Server Agent Startup Account 382 Configuring Email Notification 384 SQL Server Agent Proxy Account 385 Viewing the SQL Server Agent Error Log 387 SQL Server Agent Security 388 Managing Operators 389 Managing Jobs 391 Defining Job Properties 391 Defining Job Steps 392 Defining Multiple Job Steps 394 Defining Job Schedules 395 Defining Job Notifications 397 Viewing Job History 398 Managing Alerts 399 Defining Alert Properties 399 Defining Alert Responses 402 Scripting Jobs and Alerts 404 Multiserver Job Management 405 Creating a Master Server 406 Enlisting Target Servers 407 Creating Multiserver Jobs 407 Event Forwarding 407 Summary 408 14 SQL Server Policy-Based Management 409 What's New in Policy-Based Management 409 Introduction to Policy-Based Management 410 Policy-Based Management Concepts 411 Facets 411 Conditions 414 Policies 415 Categories 415 Targets 415 Execution Modes 415 Central Management Servers 416 Implementing Policy-Based Management 418 Creating a Condition Based on a Facet 418 Creating a Policy 420 Creating a Category 422 Evaluating Policies 424 Importing and Exporting Policies 425 Sample Templates and Real-World Examples 426 Sample Policy Templates 426 Evaluating Recovery Models 427 Ensuring Object Naming Conventions 427 Checking Best Practices Compliance 427 Policy-Based Management Best Practices 427 Summary 428 15 Security and User Administration 429 What's New in Security and User Administration 429 An Overview of SQL Server Security 430 Authentication Methods 433 Windows Authentication Mode 433 Mixed Authentication Mode 433 Setting the Authentication Mode 433 Managing Principals 434 Logins 434 SQL Server Security: Users 437 The dbo User 438 The guest User 439 The INFORMATION_SCHEMA User 439 The sys User 439 User/Schema Separation 440 Roles 441 Fixed Server Roles 442 Fixed Database Roles 443 The public Role 445 User-Defined Database Roles 446 User-Defined Server Roles 448 Application Roles 448 Managing Securables 449 Managing Permissions 450 Managing SQL Server Logins 452 Using SSMS to Manage Logins 452 Using T-SQL to Manage Logins 456 Managing SQL Server Users 457 Using SSMS to Manage Users 458 Using T-SQL to Manage Users 460 Managing Database Roles 461 Using SSMS to Manage Database Roles 461 Using T-SQL to Manage Database Roles 462 Managing Server Roles 462 Using SSMS to Manage Server Roles 463 Using T-SQL to Manage Server Roles 463 Managing SQL Server Permissions 464 Using SSMS to Manage Permissions 464 Using SSMS to Manage Permissions at the Server Level 465 Using SSMS to Manage Permissions at the Database Level 467 Using SSMS to Manage Permissions at the Object Level 470 Using T-SQL to Manage Permissions 472 The Execution Context 473 Explicit Context Switching 473 Implicit Context Switching 474 Summary 475 16 Data Encryption 477 What's New in Data Encryption 478 An Overview of Data Encryption 478 SQL Server Key Management 480 Extensible Key Management 482 Column-Level Encryption 483 Encrypting Columns Using a Passphrase 484 Encrypting Columns Using a Certificate 486 Transparent Data Encryption 490 Implementing Transparent Data Encryption 491 Managing TDE in SSMS 493 Backing Up TDE Certificates and Keys 495 The Limitations of TDE 496 Column-Level Encryption Versus Transparent Data Encryption 496 Summary 498 17 Managing Linked Servers 499 What's New in Managing Linked Servers 500 Linked Servers 500 Distributed Queries 501 Distributed Transactions 502 Adding, Dropping, and Configuring Linked Servers 503 sp_addlinkedserver 503 sp_linkedservers 510 sp_dropserver 512 sp_serveroption 512 Mapping Local Logins to Logins on Linked Servers 513 sp_addlinkedsrvlogin 514 sp_droplinkedsrvlogin 515 sp_helplinkedsrvlogin 516 Obtaining General Information About Linked Servers 517 Executing a Stored Procedure via a Linked Server 518 Setting Up Linked Servers Using SQL Server Management Studio 519 Summary 523 18 SQL Server Configuration Options 525 What's New in Configuring, Tuning, and Optimizing SQL Server Options 525 SQL Server Instance Architecture 526 Configuration Options 527 Fixing an Incorrect Option Setting 535 Setting Configuration Options with SSMS 535 Obsolete Configuration Options 535 Configuration Options and Performance 536 access check cache bucket count 536 access check cache quota 536 ad hoc distributed queries 537 affinity I/O mask 537 affinity mask 539 Agent XP 540 backup checksum default 541 backup compression default 541 blocked process threshold 542 c2 audit mode 542 clr enabled 543 common criteria compliance enabled 543 contained database authentication 543 cost threshold for parallelism 544 cross db ownership chaining 545 cursor threshold 545 Database Mail XPs 546 default full-text language 546 default language 548 default trace enabled 550 disallow results from triggers 551 EKM provider enabled 551 filestream_access_level 551 fill factor 552 index create memory 552 in-doubt xact resolution 553 lightweight pooling 553 locks 554 max degree of parallelism 554 max server memory and min server memory 554 max text repl size 556 max worker threads 557 media retention 558 min memory per query 558 nested triggers 559 network packet size 559 Ole Automation Procedures 560 optimize for ad hoc workloads 560 PH_timeout 561 priority boost 561 query governor cost limit 562 query wait 562 recovery interval 563 remote access 564 remote admin connections 564 remote login timeout 564 remote proc trans 565 remote query timeout 565 scan for startup procs 565 show advanced options 566 user connections 566 user options 567 XP-Related Configuration Options 568 Summary 569 19 Working with and Deploying to Azure SQL Database 571 Setting Up Subscriptions, Servers, and Databases 571 Setting Up Your Windows Azure Subscription 572 Creating a Logical Database Server 574 Managing Your Server 576 Configuring Your Firewall 577 Using SQL Server Management Studio 578 Using Management Portal 579 Working with Databases 580 Understanding SQL Database Service Tiers 580 Managing Databases Using T-SQL 584 Migrating Data into SQL Database 586 Copying Databases 587 Exporting Databases 588 Backing Up and Restoring Databases 590 Using SQL Database Backup, Replication, and Recovery 590 Using Database Copies for Backup and Restore 592 Using BACPAC Files for Backup and Restore 593 Managing Logins, Users, and Roles 595 Understanding Roles 595 Managing Logins and Users 596 Considerations for SQL Database Client Applications 598 Connectivity Limitations 598 Connection String Differences 599 Understanding SQL Database Billing 599 Baseline Billing 599 Tracking Your Usage 601 Understanding SQL Database Limitations 603 Unsupported and Partially Supported Functionality 603 References 606 Summary 606 Part IV Database Administration 20 Creating and Managing Databases 609 What's New in Creating and Managing Databases 610 Data Storage in SQL Server 610 Database Files 611 Primary Files 612 Secondary Files 612 Using Filegroups 613 Using Partitions 616 Transaction Log Files 616 Creating Databases 617 Using SSMS to Create a Database 618 Using T-SQL to Create Databases 621 Setting Database Options 622 The Database Options 623 Using T-SQL to Set Database Options 625 Retrieving Option Information 626 Managing Databases 629 Managing File Growth 629 Expanding Databases 630 Shrinking Databases 631 Moving Databases 636 Restoring a Database to a New Location 636 Using ALTER DATABASE 636 Detaching and Attaching Databases 637 Contained Databases 639 Creating a Contained Database 640 Connecting to a Contained Database 642 Summary 643 21 Creating and Managing Tables 645 What's New in SQL Server 2014 645 Creating Tables 646 Using Object Explorer to Create Tables 646 Using Database Diagrams to Create Tables 647 Using T-SQL to Create Tables 648 Defining Columns 650 Data Types 651 Column Properties 657 Column Sets 663 Working with Sparse Columns 664 Sparse Columns: Good or Bad? 667 Defining Sparse Columns in SSMS 667 Defining Table Location 668 Defining Table Constraints 670 Modifying Tables 672 Using T-SQL to Modify Tables 672 Using Object Explorer and the Table Designer to Modify Tables 675 Using Database Diagrams to Modify Tables 678 Dropping Tables 680 Using Partitioned Tables 681 Creating a Partition Function 682 Creating a Partition Scheme 684 Creating a Partitioned Table 686 Adding and Dropping Table Partitions 689 Switching Table Partitions 693 Using FILESTREAM Storage 697 Enabling FILESTREAM Storage 698 Setting Up a Database for FILESTREAM Storage 701 Using FILESTREAM Storage for Data Columns 702 Using FileTables 705 FileTable Prerequisites 705 Creating FileTables 707 Copying Files to the FileTable 707 Creating Temporary Tables 709 Summary 710 22 Creating and Managing Indexes 711 What's New in Creating and Managing Indexes 711 Types of Indexes 712 Clustered Indexes 712 Nonclustered Indexes 714 Creating Indexes 716 Creating Indexes with T-SQL 716 Creating Indexes with SSMS 720 Managing Indexes 722 Managing Indexes with T-SQL 723 Managing Indexes with SSMS 726 Dropping Indexes 727 Online Indexing Operations 727 Indexes on Views 729 Summary 730 23 Implementing Data Integrity 731 What's New in Data Integrity 731 Types of Data Integrity 732 Domain Integrity 732 Entity Integrity 732 Referential Integrity 732 Enforcing Data Integrity 732 Implementing Declarative Data Integrity 732 Implementing Procedural Data Integrity 733 Using Constraints 733 The PRIMARY KEY Constraint 733 The UNIQUE Constraint 735 The FOREIGN KEY Referential Integrity Constraint 736 The CHECK Constraint 740 Creating Constraints 742 Managing Constraints 747 Rules 750 Defaults 751 Declarative Defaults 751 Bound Defaults 753 When a Default Is Applied 754 Restrictions on Defaults 755 Summary 756 24 Creating and Managing Views 757 What's New in Creating and Managing Views 757 Definition of Views 757 Using Views 758 Simplifying Data Manipulation 759 Focusing on Specific Data 760 Abstracting Data 761 Controlling Access to Data 762 Creating Views 764 Creating Views Using T-SQL 765 ENCRYPTION 767 Creating Views Using the View Designer 769 Managing Views 772 Altering Views with T-SQL 772 Dropping Views with T-SQL 773 Managing Views with SSMS 773 Data Modifications and Views 773 Partitioned Views 774 Modifying Data Through a Partitioned View 778 Distributed Partitioned Views 779 Indexed Views 780 Creating Indexed Views 781 Indexed Views and Performance 783 To Expand or Not to Expand 786 Summary 787 25 Creating and Managing Stored Procedures 789 What's New in Creating and Managing Stored Procedures 789 Advantages of Stored Procedures 789 Creating Stored Procedures 791 Creating Procedures in SSMS 792 Executing Stored Procedures 799 Executing Procedures in SSMS 800 Execution Context and the EXECUTE AS Clause 802 Using the WITH RESULT SETS Clause 804 Deferred Name Resolution 807 Identifying the Objects Referenced Within Stored Procedures 809 Viewing Stored Procedures 811 Modifying Stored Procedures 814 Viewing and Modifying Stored Procedures with SSMS 815 Using Input Parameters 816 Setting Default Values for Parameters 817 Passing Object Names as Parameters 820 Using Wildcards in Parameters 822 Using Table-Valued Parameters 823 Using Output Parameters 825 Returning Procedure Status 826 Debugging Stored Procedures Using SQL Server Management Studio 827 Startup Procedures 830 Natively Compiled Stored Procedures 834 T-SQL Stored Procedure Coding Guidelines 838 Summary 839 26 Creating and Managing User-Defined Functions 841 Why Use User-Defined Functions? 841 Types of User-Defined Functions 844 Scalar Functions 844 Table-Valued Functions 847 Creating and Managing User-Defined Functions 849 Creating User-Defined Functions 849 Viewing and Modifying User-Defined Functions 860 Managing User-Defined Function Permissions 868 Rewriting Stored Procedures as Functions 869 Summary 871 27 Creating and Managing Triggers 873 What's New in Creating and Managing Triggers 874 Using DML Triggers 874 Creating DML Triggers 875 Using AFTER Triggers 877 Using inserted and deleted Tables 881 INSTEAD OF Triggers 885 Using DDL Triggers 893 Creating DDL Triggers 897 Managing DDL Triggers 901 Using Nested Triggers 903 Using Recursive Triggers 903 Summary 905 28 Transaction Management and the Transaction Log 907 What's New in Transaction Management 907 What Is a Transaction? 907 How SQL Server Manages Transactions 908 Defining Transactions 909 AutoCommit Transactions 909 Explicit User-Defined Transactions 910 Implicit Transactions 916 Implicit Transactions Versus Explicit Transactions 918 Transactions and T-SQL Batches 919 Transactions and Stored Procedures 921 Transactions and Triggers 926 Triggers and Transaction Nesting 927 Triggers and Multistatement Transactions 930 Using Savepoints in Triggers 931 Transactions and Locking 933 READ_COMMITTED_SNAPSHOT Isolation 934 Coding Effective Transactions 934 Transaction Logging and the Recovery Process 935 The Checkpoint Process 939 Automatic Checkpoints 941 Indirect Checkpoints 942 Manual Checkpoints 944 The Recovery Process 945 Managing the Transaction Log 947 Long-Running Transactions 952 Distributed Transactions 954 Summary 955 29 Database Snapshots 957 What's New with Database Snapshots 958 What Are Database Snapshots? 958 Limitations and Restrictions of Database Snapshots 962 Copy-on-Write Technology 964 When to Use Database Snapshots 965 Reverting to a Snapshot for Recovery Purposes 965 Safeguarding a Database Prior to Making Mass Changes 966 Providing a Testing (or Quality Assurance) Starting Point (Baseline) 967 Providing a Point-in-Time Reporting Database 967 Providing a Highly Available and Offloaded Reporting Database from a Database Mirror 968 Setup and Breakdown of a Database Snapshot 970 Creating a Database Snapshot 970 Removing a Database Snapshot 974 Reverting to a Database Snapshot for Recovery 975 Reverting a Source Database from a Database Snapshot 975 Database Snapshots Maintenance and Security Considerations 977 Security for Database Snapshots 977 Snapshot Sparse File Size Management 977 Number of Database Snapshots per Source Database 977 Summary 978 30 Database Maintenance 979 What's New in Database Maintenance 980 The Maintenance Plan Wizard 980 Backing Up Databases 983 Checking Database Integrity 987 Shrinking Databases 988 Maintaining Indexes and Statistics 990 Scheduling a Maintenance Plan 993 Managing Maintenance Plans Without the Wizard 997 Executing a Maintenance Plan 1001 Maintenance Without a Maintenance Plan 1002 Database Maintenance Policies 1003 Summary 1003 Part V SQL Server Performance and Optimization 31 Understanding SQL Server Data Structures 1007 What's New for Data Structures 1007 Understanding Data Structures 1008 Database Files and Filegroups 1008 Primary Data File 1010 Secondary Data Files 1010 The Log File 1011 File Management 1011 Using Filegroups 1012 FILESTREAM Filegroups 1015 Database Pages 1017 Page Types 1017 Data Pages 1018 Row-Overflow Pages 1024 LOB Data Pages 1025 Index Pages 1028 Space Allocation Structures 1029 Extents 1029 Global and Shared Global Allocation Map Pages 1030 Page Free Space Pages 1031 Index Allocation Map Pages 1031 Differential Changed Map Pages 1032 Bulk Changed Map Pages 1032 Data Compression 1033 Row-Level Compression 1033 Page-Level Compression 1035 The CI Record 1038 Implementing Page Compression 1038 Evaluating Page Compression 1039 Managing Data Compression with SSMS 1042 Understanding Table Structures 1043 Heap Tables 1045 Clustered Tables 1047 Understanding Index Structures 1048 Clustered Indexes 1049 Nonclustered Indexes 1052 Columnstore Indexes 1057 Data Modification and Performance 1062 Inserting Data 1062 Deleting Rows 1065 Updating Rows 1066 Summary 1068 32 Indexes and Performance 1069 What's New for Indexes and Performance 1069 Index Utilization 1070 Index Selection 1072 Evaluating Index Usefulness 1073 Index Statistics 1076 The Statistics Histogram 1078 How the Statistics Histogram Is Used 1080 Index Densities 1081 Estimating Rows Using Index Statistics 1082 Generating and Maintaining Index and Column Statistics 1085 SQL Server Index Maintenance 1093 Setting the Fill Factor 1103 Reapplying the Fill Factor 1105 Disabling Indexes 1106 Managing Indexes with SSMS 1107 Index Design Guidelines 1108 Clustered Index Indications 1109 Nonclustered Index Indications 1111 Index Covering 1112 Included Columns 1114 Wide Indexes Versus Multiple Indexes 1115 Indexed Views 1116 Indexes on Computed Columns 1117 Filtered Indexes and Statistics 1119 Creating and Using Filtered Indexes 1120 Creating and Using Filtered Statistics 1122 Choosing Indexes: Query versus Update Performance 1124 Identifying Missing Indexes 1125 The Database Engine Tuning Advisor 1125 Missing Index Dynamic Management Objects 1126 Missing Index Feature Versus Database Engine Tuning Advisor 1128 Identifying Unused Indexes 1129 Summary 1131 33 In-Memory Optimization and the Buffer Pool Extension 1133 Overview of In-Memory OLTP 1134 In-Memory OLTP Concepts and Terminology 1136 In-Memory Optimization Requirements 1137 Limitations of In-Memory OLTP 1137 Using In-Memory OLTP 1138 Enabling a Database for In-Memory OLTP 1138 Creating Memory-Optimized Tables 1140 Memory-Optimized Tables Row Structure 1142 Indexes on Memory-Optimized Tables 1143 Garbage Collection 1151 Maintaining Statistics on Memory-Optimized Tables 1153 Memory-Optimized Index Design Guidelines 1154 Using Memory-Optimized Tables 1156 Interpreted T-SQL Support for In-Memory OLTP 1156 Native Compilation 1157 Natively Compiled Stored Procedures 1159 Memory-Optimized Table Variables 1162 Transactions and Memory-Optimized Tables 1162 Monitoring Transactions on Memory-Optimized Tables 1170 Logging, Checkpoint, and Recovery for In-Memory OLTP 1170 Transaction Logging 1171 Checkpoint 1171 Recovery 1174 Managing Memory for In-Memory OLTP 1175 Monitoring Memory Usage 1176 Managing Memory with the Resource Governor 1177 Backup and Recovery of Memory-Optimized Databases 1178 Migrating to In-Memory OLTP 1179 Using the AMR Tool 1180 Using the Table Memory Optimization Advisor to Migrate Disk-Based Tables 1181 Dynamic Management Views for In-Memory OLTP 1183 The Buffer Pool Extension 1185 Summary 1186 34 Understanding Query Optimization 1187 What's New in Query Optimization 1188 What Is the Query Optimizer? 1188 Query Compilation and Optimization 1189 Compiling DML Statements 1189 Optimization Steps 1190 Query Analysis 1191 Identifying Search Arguments 1191 Identifying OR Clauses 1191 Identifying Join Clauses 1192 Row Estimation and Index Selection 1193 Evaluating SARG and Join Selectivity 1193 Estimating Access Path Cost 1199 Using Multiple Indexes 1206 Optimizing with Indexed Views 1213 Optimizing with Filtered Indexes 1216 Evaluating Cardinality Estimates 1218 Join Selection 1219 Join Processing Strategies 1219 Determining the Optimal Join Order 1224 Subquery Processing 1226 Execution Plan Selection 1228 Query Plan Caching 1231 Query Plan Reuse 1231 Query Plan Aging 1234 Recompiling Query Plans 1234 Monitoring the Plan Cache 1235 Other Query Processing Strategies 1243 Predicate Transitivity 1244 GROUP BY Optimization 1244 Queries with DISTINCT 1245 Queries with UNION 1245

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

Tytuł: Microsoft SQL Server 2014 Unleashed
Autor: Alex Silverstein, Ray Rankins, Chris Gallelli
Producent: Sams Publishing
ISBN: 9780672337291
Rok produkcji: 2015
Ilość stron: 1992
Oprawa: Miękka
Waga: 2.53 kg

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