COVID-19 Response at Fishpond.com.au

Read what we're doing...

Professional Microsoft SQL Server 2014 Administration
By

Rating

Product Description
Product Details

Table of Contents

INTRODUCTION xxxvii CHAPTER 1: SQL SERVER 2014 ARCHITECTURE 1 SQL Server 2014 Ecosystem 1 New Important Features in 2014 2 Production DBA 2 Development DBA 3 Business Intelligence DBA and Developer 4 SQL Server Architecture 5 Database Files and Transaction Log 5 SQL Server Native Client 6 Standard System Databases 7 Schemas 9 Synonyms 10 Dynamic Management Objects 10 SQL Server 2014 Data Types 12 Editions of SQL Server 18 Edition Overview 18 Licensing 21 Summary 22 CHAPTER 2: INSTALLING SQL SERVER 2014 BEST PRACTICES 23 Planning the System 24 Hardware Options 24 Software and Install Options 29 Installing SQL Server 32 New Installs 32 Side-by-Side Installs 32 Upgrades 33 Attended Installations 33 Unattended Installs 37 Installing Analysis Services 43 Multidimensional and Data Mining Mode (UDM Mode) 44 Tabular Mode 46 Installing PowerPivot for SharePoint 47 Burning in the System 48 Post-Install Configuration 49 Configuring SQL Server Settings for Performance 49 tempdb 51 Configuring SQL Server Settings for Security 52 SQL Server Configuration Manager 54 Back It Up 54 Uninstalling SQL Server 54 Uninstalling Reporting Services 55 Uninstalling Analysis Services 55 Uninstalling the SQL Server Database Engine 55 Troubleshooting a Failed Install 55 Summary 56 CHAPTER 3: UPGRADING SQL SERVER 2014 BEST PRACTICES 57 Why Upgrade to SQL Server 2014? 57 Risk Mitigation-the Microsoft Contribution 58 Independent Software Vendors and SQL Community Contributions 59 Upgrading to SQL Server 2014 59 In-Place Upgrading 60 Side-by-Side Upgrade 62 In-Place Upgrade versus Side-by-Side Upgrade Considerations 62 Pre-Upgrade Steps and Tools 63 Pre-Upgrade Steps 63 Pre-Upgrade Tools 64 Backward Compatibility 72 Unsupported and Discontinued Features in SQL Server 2014 72 SQL Server 2014 Deprecated Database Features 74 Other SQL Server 2014 Changes Affecting Behavior 74 SQL Server Component Considerations 75 Upgrading Full-Text Catalog 75 Upgrading Reporting Services 75 Upgrading to 64-Bit 76 Post-Upgrade Checks 76 Summary 77 CHAPTER 4: MANAGING AND TROUBLESHOOTING THE DATABASE ENGINE 79 Configuration and Administration Tools 80 SQL Server Configuration Manager 80 Startup Parameters 82 Startup Stored Procedures 84 Partially Contained Databases 86 Troubleshooting Tools 87 Dedicated Administrator Connection 87 Rebuilding the System Databases 89 SQL Server Management Studio 90 Reports 90 Configuring SQL Server in SQL Server Management Studio 93 Filtering Objects 97 Error Logs 97 Activity Monitor 98 Monitoring Processes in T-SQL 104 sp_who and sp_who2 104 sys.dm_exec_connections 105 sys.dm_exec_sql_text 105 Multiserver Management 106 Central Management Servers and Server Groups 106 Trace Flags 107 Getting Help from Support 109 SQLDumper.exe 109 SQLDiag.exe 110 Summary 112 CHAPTER 5: AUTOMATING SQL SERVER 113 Maintenance Plans 114 Maintenance Plan Wizard 114 Maintenance Plan Designer 119 Automating SQL Server with SQL Server Agent 122 Jobs 123 Schedules 128 Operators 129 Alerts 133 SQL Server Agent Security 138 Service Account 138 Access to SQL Agent 138 SQL Server Agent Proxies 139 Configuring SQL Server Agent 143 General Properties 143 Advanced Properties 144 Alert System Properties 145 Job System Properties 146 Connection Properties 147 History Properties 147 Multiserver Administration 147 Using Token Replacement 148 Event Forwarding 150 Using WMI 151 Multiserver Administration-Using Master and Target Servers 152 Summary 154 CHAPTER 6: SERVICE BROKER IN SQL SERVER 2014 155 Asynchronous Messaging 155 SQL Service Broker Overview 157 SQL Server Service Broker versus Other Message Queues 158 Configuring SQL Server Service Broker 159 Setting Broker State 159 Message Types 161 Contracts 162 Queues 163 Services 165 Routes 166 Priorities 167 Conversation Groups 168 Using SQL Server Service Broker 169 Sending Messages 169 Receiving Messages 172 Sending Messages between Instances 174 External Activation 176 Log User Example 177 Summary 184 CHAPTER 7: SQL SERVER CLR INTEGRATION 185 Introduction to CLR 185 SQL Server as a .NET Runtime Host 187 Application Domains 187 T-SQL versus CLR 187 Enabling CLR Integration 188 Creating CLR Assemblies 189 The Non-Visual Studio Way 189 Using Microsoft SQL Server Data Tools 191 CLR Integration Security 193 Performance Monitoring 194 Windows System Monitor 194 Extended Events 196 Dynamic Management Views (DMVs) 197 CLR Integration Design Goals 197 Summary 198 CHAPTER 8: SECURING THE DATABASE INSTANCE 199 Authentication Types 200 SQL Authentication 200 Windows Authentication 201 SQL versus Windows Authentication 202 Logins and Users 202 Authorizing Securables 202 Server Securables 203 Database Securables 209 Permission Chains 210 Cross-Database Permission Chains 211 Row-Level Security 213 Summary 214 CHAPTER 9: IN-MEMORY OLTP 215 Using and Implementing In-Memory OLTP 215 Enabling In-Memory OLTP 216 In-Memory OLTP Structures 218 Records 218 Indexes 219 CPU Considerations 224 Virtualization Considerations 224 Memory Considerations 225 Managing Memory with Resource Governor 227 Creating Natively Compiled Stored Procedures 230 Overview of the Analyze, Migrate, and Report (ARM) Tool 233 Summary 246 CHAPTER 10: CONFIGURING THE SERVER FOR OPTIMAL PERFORMANCE 247 What Every DBA Needs to Know About Performance 248 The Performance Tuning Cycle 248 Configuration 250 Power Configuration 250 Instant Database File Initialization 252 Trace Flags 256 Defining Good Performance 256 Focus on What's Most Important 257 What the Developer DBA Needs to Know About Performance 258 Users 258 SQL Statements 259 Data Usage Patterns 259 Database Schema 259 What the Production DBA Needs to Know About Performance 260 Optimizing the Server's Hardware 261 Hardware Management 262 CPU 263 x64 263 Cache 264 Hyper-Threading 264 Multicore Terminology 265 Memory 266 Physical Memory 266 Physical Address Space 267 Virtual Memory Manager 267 The Page File 267 Page Faults 268 I/O 269 Network 270 Magnetic Disks 271 Solid State/Flash Drives 274 Storage Considerations 274 Summary 278 CHAPTER 11: OPTIMIZING SQL SERVER 2014 281 Application Optimization 281 Defining a Workload 282 The Silent Killer: I/O Problems 282 SQL Server I/O Process Model 282 Database File Placement 283 tempdb Considerations 284 SQL Server Internals and File Allocations 288 Table and Index Partitioning 290 Why Consider Partitioning? 290 Creating a Partition Function 292 Creating Filegroups 294 Creating a Partition Scheme 295 Data Compression 296 Row Compression 296 Page Compression 299 Estimating Space Savings 300 Monitoring Data Compression 302 Data Compression Considerations 303 Understanding SQL Server and CPUs 304 NUMA and Hot Add CPUs 305 Cache Coherency 306 Affinity Mask 307 Max Degree of Parallelism (MAXDOP) 307 Cost Threshold for Parallelism 308 Memory Considerations and Enhancements 310 Buffer Pool Extensions 310 Tuning SQL Server Memory 312 Data Locality 314 Max Server Memory 315 Resource Governor 316 The Basic Elements of Resource Governor 317 Using Resource Governor from SQL Server 2014 Management Studio 321 Monitoring Resource Governor 322 Summary 323 CHAPTER 12: MONITORING YOUR SQL SERVER 325 The Goal of Monitoring 326 Determining Your Monitoring Objectives 326 Establishing a Baseline 327 Comparing Current Metrics to the Baseline 327 Choosing the Appropriate Monitoring Tools 328 Performance Monitor 329 CPU Resource Counters 331 Disk Activity 332 Memory Usage 338 Performance Monitoring Tools 341 Monitoring Events 343 The Default Trace 345 system_health Session 346 SQL Trace 347 Event Notifications 351 SQL Server Extended Events 354 Monitoring with Dynamic Management Views and Functions 375 What's Going on Inside SQL Server? 377 Viewing the Locking Information 380 Viewing Blocking Information 380 Index Usage in a Database 381 Indexes Not Used in a Database 382 View Queries Waiting for Memory Grants 383 Connected User Information 384 Filegroup Free Space 384 Query Plan and Query Text for Currently Running Queries 385 Memory Usage 385 Buffer Pool Memory Usage 385 Monitoring Logs 386 Monitoring the SQL Server Error Log 386 Monitoring the Windows Event Logs 387 SQL Server Standard Reports 387 System Center Advisor 387 Summary 390 CHAPTER 13: PERFORMANCE TUNING T-SQL 391 Overview of Query Processing 392 Generating Execution Plans 394 Statistics 394 SQL 2014 Query Optimization Improvements-The New Cardinality Estimator 395 Using the New Cardinality Estimator 395 Identifying SQL Query Performance Tuning Issues 396 Monitoring Query Performance 396 What to Do When You Find a Slow-Performing Query 397 Generating the Query Plans 398 Reading the Query Plan 400 Data Access Operators in Query Plans 403 Table Scan 404 Clustered Index Scan 406 Nonclustered Index Scans 408 Connecting Access Operators 411 Join Operators 412 Nested Loop or Loop Join 412 Hash Join 413 Merge Join 415 Data Modification Query Plan 417 Query Processing on Partitioned Tables and Indexes 419 Partition-Aware Operations 419 Parallel Query Execution Strategy for Partitioned Objects 421 Analyzing Query Performance in a Production Environment 421 System Dynamic Management Views (DMVs) 422 Putting It All Together 423 Summary 424 CHAPTER 14: INDEXING YOUR DATABASE 425 What's New for Indexes in SQL Server 2014 426 About Indexes and Partitioned Tables 430 Understanding Indexes 430 Creating Indexes 435 Using Partitioned Tables and Indexes 436 Index Maintenance 437 Monitoring Index Fragmentation 438 Cleaning Up Indexes 439 Improving Query Performance with Indexes 440 Database Tuning Advisor 445 Summary 448 CHAPTER 15: REPLICATION 449 Replication Overview 450 Replication Components 450 Replication Types 452 Replication Models 454 Single Publisher, One or More Subscribers 454 Multiple Publishers, Single Subscriber 454 Multiple Publishers Also Subscribing 456 Updating Subscriber 457 Peer-to-Peer 458 Implementing Replication 460 Setting Up Snapshot Replication 460 Setting Up Distribution 460 Implementing Snapshot Replication 464 Implementing Transactional and Merge Replication 479 Peer-to-Peer Replication 480 Setting Up Peer-to-Peer Replication 480 Configuring Peer-to-Peer Replication 481 Scripting Replication 484 Monitoring Replication 485 Replication Monitor 485 Performance Monitor 487 Replication DMVs 488 sp_replcounters 489 Summary 489 CHAPTER 16: CLUSTERING SQL SERVER 2014 491 Clustering and Your Organization 492 What Clustering Can Do 492 What Clustering Cannot Do 493 Choosing SQL Server 2014 Clustering for the Right Reasons 494 Alternatives to Clustering 494 Clustering: The Big Picture 497 How Clustering Works 497 Clustering Options 502 Upgrading SQL Server Clustering 504 Don't Upgrade 504 Upgrading Your SQL Server 2014 Cluster In-Place 505 Rebuilding Your Cluster 505 Back-Out Plan 507 Which Upgrade Option Is Best? 507 Getting Prepared for Clustering 507 Preparing the Infrastructure 508 Preparing the Hardware 509 Clustering Windows Server 2012 R2 511 Before Installing Windows 2012 R2 Clustering 511 Installing Windows Server 2012 R2 Failover Clustering 512 Preparing Windows Server 2012 R2 for Clustering 516 Clustering Microsoft Distributed Transaction Coordinator 517 Clustering SQL Server 2014 518 Step-by-Step Instructions to Cluster SQL Server 519 Installing the Service Pack and Cumulative Updates 526 Test, Test, and Test Again 527 Managing and Monitoring the Cluster 529 Troubleshooting Cluster Problems 530 How to Approach Windows Failover Clustering Troubleshooting 530 Doing It Right the First Time 531 Gathering Information 531 Resolving Problems 532 Working with Microsoft 532 Summary 533 CHAPTER 17: BACKUP AND RECOVERY 535 Backup and Restore Enhancements 536 SQL Server Backup to URL 536 SQL Server Managed Backup to Windows Azure 536 Encryption for Backups 536 Overview of Backup and Restore 537 How Backup Works 537 Copying Databases 539 Backup Compression 553 Comparing Recovery Models 554 Choosing a Model 556 Switching Recovery Models 557 Backing Up History Tables 558 Permissions Required for Backup and Restore 559 Backing Up System Databases 559 Full-Text Backup 561 Verifying the Backup Images 561 How Restore Works 562 Preparing for Recovery 563 Recoverability Requirements 564 Data Usage Patterns 565 Maintenance Time Window 565 Other High-Availability Solutions 566 Developing and Executing a Backup Plan 567 Using SQL Server Management Studio 568 Database Maintenance Plans 572 Using Transact-SQL Backup Commands 576 Managing Backups 578 Backup and Restore Performance 578 Performing Recovery 579 Restore Process 579 SQL Server Management Studio Restore 583 T-SQL Restore Command 588 Restoring System Databases 588 Archiving Data 590 SQL Server Table Partitioning 590 Partitioned View 591 Summary 592 CHAPTER 18: SQL SERVER 2014 LOG SHIPPING 593 Log-Shipping Deployment Scenarios 593 Log Shipping to Create a Warm Standby Server 594 Log Shipping as a Disaster-Recovery Solution 595 Log Shipping as a Report Database Solution 596 Log-Shipping Architecture 597 Primary Server 598 Secondary Server 598 Monitor Server 598 Log-Shipping Process 599 System Requirements 599 Network 600 Identical Capacity Servers 600 Storage 600 Software 600 Deploying Log Shipping 601 Initial Configuration 601 Deploying with Management Studio 602 Deploying with T-SQL Commands 610 Monitoring and Troubleshooting 611 Monitoring with SQL 2014 Management Studio 612 Monitoring with Stored Procedures 613 Troubleshooting Approach 613 Managing Changing Roles 614 Synchronizing Dependencies 614 Switching Roles from the Primary to Secondary Servers 617 Switching Between Primary and Secondary Servers 619 Redirecting Clients to Connect to the Secondary Server 620 Database Backup Plan 621 Integrating Log Shipping with Other High-Availability Solutions 622 SQL Server 2014 Data Mirroring 622 Windows Failover Clustering 622 SQL Server 2014 Replication 623 Removing Log Shipping 624 Removing Log Shipping with Management Studio 624 Removing Log Shipping with T-SQL Commands 624 Log-Shipping Performance 625 Upgrading to SQL Server 2014 Log Shipping 626 Minimum Downtime Approach 626 With Downtime Approach 626 Deploy Log-Shipping Approach 627 Summary 627 CHAPTER 19: DATABASE MIRRORING 629 Overview of Database Mirroring 630 Operating Modes of Database Mirroring 631 Database Mirroring In Action 634 Preparing the Endpoints 635 Preparing the Database for Mirroring 640 Initial Synchronization between Principal and Mirror 641 Establishing the Mirroring Session 642 High-Safety Operating Mode Without Automatic Failover 643 High-Safety Operating Mode with Automatic Failover 643 High-Performance Operating Mode 645 Database Mirroring and SQL Server 2014 Editions 646 Database Mirroring Role Change 646 Automatic Failover 647 Manual Failover 650 Forced Failover 651 Monitoring Using Database Mirroring Monitor 652 Setting Thresholds on Counters and Sending Alerts 656 Preparing the Mirror Server for Failover 658 Hardware, Software, and Server Configuration 658 Database Availability During Planned Downtime 660 SQL Job Configuration on the Mirror 661 Client Redirection to the Mirror 662 Database Mirroring and Other High-Availability Solutions 663 Database Mirroring versus Clustering 663 Database Mirroring versus Transactional Replication 664 Database Mirroring versus Log Shipping 664 Database Mirroring versus Availability Groups 664 Database Snapshots 665 Summary 666 CHAPTER 20: INTEGRATION SERVICES ADMINISTRATION AND PERFORMANCE TUNING 667 A Tour of Integration Services 668 Integration Services Uses 668 The Main Parts of Integration Services 669 Project Management and Change Control 671 Administration of the Integration Services Service 671 An Overview of the Integration Services Service 672 Configuration 672 Event Logs 675 Monitoring Activity 676 Administration of Integration Services Packages in Package Deployment Model 677 Using Management Studio for Package Management 677 Deployment 679 Administration of Integration Services Packages in Project Deployment Model 683 Configuring the SSIS Catalog 683 Deploying Packages 685 Configuring Packages 687 Execution and Scheduling 690 Running Packages in SQL Server Data Tools 691 Running Packages with the SQL Server Import and Export Wizard 691 Running Packages with DTExec 691 Running Packages with DTExecUI (Package Deployment Model) 692 Running Packages with the Execute Package Tool (Project Deployment Model) 693 Scheduling Execution with SQL Server Agent 694 Running Packages with T-SQL 696 Applying Security to Integration Services 696 An Overview of Integration Services Security 696 Securing Packages in Package Deployment Model 697 Summary 699 CHAPTER 21: ANALYSIS SERVICES ADMINISTRATION AND PERFORMANCE TUNING 701 Tour of Analysis Services 702 MOLAP Components 703 Tabular Model Components 704 Analysis Services Architectural Components 704 Administering Analysis Services Server 705 Server Properties 706 Required Services 707 Analysis Services Scripting Language 708 Administering Analysis Services Databases 709 Deploying Analysis Services Databases 710 Processing Analysis Services Objects 712 Backing Up and Restoring Analysis Services Databases 717 Synchronizing Analysis Services Databases 721 Analysis Services Performance Monitoring and Tuning 722 Monitoring Analysis Services Events 722 Using Flight Recorder for After-the-Fact Analysis 723 Summary 723 CHAPTER 22: SQL SERVER REPORTING SERVICES ADMINISTRATION 725 SQL Server Reporting Services Configuration Manager 726 The Service Account 728 The Web Service URL 729 Reporting Services Databases 731 The Report Manager URL 733 E-mail Settings 734 Execution Account 734 Encryption Keys 736 Scale-Out Deployment 737 The Report Execution Log 739 Report Manager 740 Managing Report Manager 740 Managing Reports 747 Summary 761 CHAPTER 23: SQL SERVER 2014 SHAREPOINT 2013 INTEGRATION 763 Components of Integration 763 PowerPivot 764 Reporting Services 766 Power View 768 Data Refresh 771 Using Data Connections in Excel 771 PerformancePoint Data Connections 776 Visio Services Data Refresh 778 PowerPivot Data Refresh 780 Summary 788 CHAPTER 24: SQL DATABASE ADMINISTRATION AND CONFIGURATION 789 Getting to Know Windows Azure SQL Database 789 SQL Database Architecture 790 Client Layer 791 Services Layer 791 Platform Layer 791 Infrastructure Layer 791 Understanding the Differences 791 Configuring SQL Database 792 Server and Database Provisioning 792 Throttling and Load Balancing 800 Configuring SQL Database Firewalls 801 Connecting to SQL Database 802 Administering SQL Database 804 Creating Logins and Users 805 Assigning Access Rights 807 Working with SQL Database 807 Backups with SQL Database 807 Object Explorer for SQL Database 809 What's Missing in SQL Database 810 Summary 811 CHAPTER 25: ALWAYSON AVAILABILITY GROUPS 813 Architecture 814 Availability Group Replicas and Roles 815 Availability Modes 815 Types of Failover Supported 816 Allowing Read-Only Access to Secondary Replicas 817 Availability Group Example 818 Configure a New Availability Group 819 Configure an Existing Availability Group 829 Availability Group Failover Operation 833 Suspend an Availability Database 834 Resume an Availability Database 835 Client Application Connections 836 Active Secondary for Secondary Read-Only 837 Read-Only Access Behavior 837 Secondary Replica Client Connectivity 838 Performance 839 Backup on the Secondary Replica 841 Evaluate Backup Replicas Metadata 842 AlwaysOn Group Dashboard 843 Monitoring and Troubleshooting 845 Summary 847 INDEX 849

About the Author

Adam Jorgensen is the President of Pragmatic Works and the Executive Vice President of PASS with more than 15 years experience. Bradley Ball is a MCITP 2005, 2008, and 2012 MCSE DBA with more than a decade of experience who is currently the Data Platform Management Lead with Pragmatic Works specializing in Data Platform solutions. Steven Wort has been working with SQL Server since 1993, who joined Microsoft in 2000, and now works as an Architect in the CRM Service Engineering team. Ross LoForte is a Technology Architect at the Microsoft Technology Center Chicago focused on Microsoft SQL Server solutions, with more than 20 years of business development, project management, and SQL experience. Brian Knight is the founder of Pragmatic Works and co-founder of SQLServerCentral.com and JumpStartTV.com Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.

Reviews

If you want a good, wide ranging, general SQL Server 2014 administration book, I can certainly recommend this book (I Programmer, October 2014)

Ask a Question About this Product More...
Write your question below:
Look for similar items by category
How Fishpond Works
Fishpond works with suppliers all over the world to bring you a huge selection of products, really great prices, and delivery included on over 25 million products that we sell. We do our best every day to make Fishpond an awesome place for customers to shop and get what they want — all at the best prices online.
Webmasters, Bloggers & Website Owners
You can earn a 8% commission by selling Professional Microsoft SQL Server 2014 Administration on your website. It's easy to get started - we will give you example code. After you're set-up, your website can earn you money while you work, play or even sleep! You should start right now!
Authors / Publishers
Are you the Author or Publisher of a book? Or the manufacturer of one of the millions of products that we sell. You can improve sales and grow your revenue by submitting additional information on this title. The better the information we have about a product, the more we will sell!
Item ships from and is sold by Fishpond World Ltd.
Back to top