Book description
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 Warehouse
Table of contents
- About This eBook
- Title Page
- Copyright Page
- Contents at a Glance
- Table of Contents
- About the Authors
- Dedications
- Acknowledgments
- We Want to Hear from You!
- Reader Services
- Introduction
-
Part I: Welcome to Microsoft SQL Server
-
Chapter 1. SQL Server 2014 Overview
-
SQL Server Components and Features
- The SQL Server Database Engine
- SQL Server 2014 Administration and Management Tools
- Replication
- Merge Replication
- SQL Server AlwaysOn Features
- SQL Server Service Broker
- Full-Text and Semantic Search
- SQL Server Integration Services (SSIS)
- SQL Server Analysis Services (SSAS)
- SQL Server Reporting Services (SSRS)
- Master Data Services
- Data Quality Services
- SQL Server 2014 Editions
- SQL Server Licensing
- Summary
-
SQL Server Components and Features
- Chapter 2. What’s New in SQL Server 2014
-
Chapter 1. SQL Server 2014 Overview
-
Part II: SQL Server Tools and Utilities
- Chapter 3. SQL Server Management Studio
- Chapter 4. SQL Server Command-Line Utilities
- Chapter 5. SQL Server Profiler
- Chapter 6. SQL Distributed Replay
-
Part III: SQL Server Administration
- Chapter 7. SQL Server System and Database Administration
- Chapter 8. Installing SQL Server 2014
- Chapter 9. Upgrading to SQL Server 2014
- Chapter 10. Client Installation and Configuration
- Chapter 11. Database Backup and Restore
- Chapter 12. Database Mail
- Chapter 13. SQL Server Agent
- Chapter 14. SQL Server Policy-Based Management
-
Chapter 15. Security and User Administration
- What’s New in Security and User Administration
- An Overview of SQL Server Security
- Authentication Methods
- Managing Principals
- Managing Securables
- Managing Permissions
- Managing SQL Server Logins
- Managing SQL Server Users
- Managing Database Roles
- Managing Server Roles
- Managing SQL Server Permissions
- The Execution Context
- Summary
- Chapter 16. Data Encryption
-
Chapter 17. Managing Linked Servers
- What’s New in Managing Linked Servers
- Linked Servers
- Adding, Dropping, and Configuring Linked Servers
- Mapping Local Logins to Logins on Linked Servers
- Obtaining General Information About Linked Servers
- Executing a Stored Procedure via a Linked Server
- Setting Up Linked Servers Using SQL Server Management Studio
- Summary
-
Chapter 18. SQL Server Configuration Options
- What’s New in Configuring, Tuning, and Optimizing SQL Server Options
- SQL Server Instance Architecture
- Configuration Options
- Fixing an Incorrect Option Setting
- Setting Configuration Options with SSMS
- Obsolete Configuration Options
-
Configuration Options and Performance
- access check cache bucket count
- access check cache quota
- ad hoc distributed queries
- affinity I/O mask
- affinity mask
- Agent XP
- backup checksum default
- backup compression default
- blocked process threshold
- c2 audit mode
- clr enabled
- common criteria compliance enabled
- contained database authentication
- cost threshold for parallelism
- cross db ownership chaining
- cursor threshold
- Database Mail XPs
- default full-text language
- default language
- default trace enabled
- disallow results from triggers
- EKM provider enabled
- filestream_access_level
- fill factor
- index create memory
- in-doubt xact resolution
- lightweight pooling
- locks
- max degree of parallelism
- max server memory and min server memory
- max text repl size
- max worker threads
- media retention
- min memory per query
- nested triggers
- network packet size
- Ole Automation Procedures
- optimize for ad hoc workloads
- PH_timeout
- priority boost
- query governor cost limit
- query wait
- recovery interval
- remote access
- remote admin connections
- remote login timeout
- remote proc trans
- remote query timeout
- scan for startup procs
- show advanced options
- user connections
- user options
- XP-Related Configuration Options
- Summary
- Chapter 19. Working with and Deploying to Azure SQL Database
-
Part IV: Database Administration
- Chapter 20. Creating and Managing Databases
- Chapter 21. Creating and Managing Tables
- Chapter 22. Creating and Managing Indexes
- Chapter 23. Implementing Data Integrity
- Chapter 24. Creating and Managing Views
-
Chapter 25. Creating and Managing Stored Procedures
- What’s New in Creating and Managing Stored Procedures
- Advantages of Stored Procedures
- Creating Stored Procedures
- Executing Stored Procedures
- Deferred Name Resolution
- Viewing Stored Procedures
- Modifying Stored Procedures
- Using Input Parameters
- Using Output Parameters
- Returning Procedure Status
- Debugging Stored Procedures Using SQL Server Management Studio
- Startup Procedures
- Natively Compiled Stored Procedures
- T-SQL Stored Procedure Coding Guidelines
- Summary
- Chapter 26. Creating and Managing User-Defined Functions
- Chapter 27. Creating and Managing Triggers
-
Chapter 28. Transaction Management and the Transaction Log
- What’s New in Transaction Management
- What Is a Transaction?
- How SQL Server Manages Transactions
- Defining Transactions
- Transactions and T-SQL Batches
- Transactions and Stored Procedures
- Transactions and Triggers
- Transactions and Locking
- Coding Effective Transactions
- Transaction Logging and the Recovery Process
- Long-Running Transactions
- Distributed Transactions
- Summary
-
Chapter 29. Database Snapshots
- What’s New with Database Snapshots
- What Are Database Snapshots?
- Limitations and Restrictions of Database Snapshots
- Copy-on-Write Technology
- When to Use Database Snapshots
- Setup and Breakdown of a Database Snapshot
- Reverting to a Database Snapshot for Recovery
- Database Snapshots Maintenance and Security Considerations
- Summary
- Chapter 30. Database Maintenance
-
Part V: SQL Server Performance and Optimization
- Chapter 31. Understanding SQL Server Data Structures
-
Chapter 32. Indexes and Performance
- What’s New for Indexes and Performance
- Index Utilization
- Index Selection
- Evaluating Index Usefulness
- Index Statistics
- SQL Server Index Maintenance
- Index Design Guidelines
- Indexed Views
- Indexes on Computed Columns
- Filtered Indexes and Statistics
- Choosing Indexes: Query versus Update Performance
- Identifying Missing Indexes
- Identifying Unused Indexes
- Summary
-
Chapter 33. In-Memory Optimization and the Buffer Pool Extension
- Overview of In-Memory OLTP
- In-Memory Optimization Requirements
- Limitations of In-Memory OLTP
- Using In-Memory OLTP
- Using Memory-Optimized Tables
- Logging, Checkpoint, and Recovery for In-Memory OLTP
- Managing Memory for In-Memory OLTP
- Backup and Recovery of Memory-Optimized Databases
- Migrating to In-Memory OLTP
- Dynamic Management Views for In-Memory OLTP
- The Buffer Pool Extension
- Summary
-
Chapter 34. Understanding Query Optimization
- What’s New in Query Optimization
- What Is the Query Optimizer?
- Query Compilation and Optimization
- Query Analysis
- Row Estimation and Index Selection
- Join Selection
- Execution Plan Selection
- Query Plan Caching
- Other Query Processing Strategies
- Parallel Query Processing
- Common Query Optimization Problems
- Summary
- Chapter 35. Managing the Query Optimizer
- Chapter 36. Query Analysis
-
Chapter 37. Locking and Performance
- What’s New in Locking and Performance
- The Need for Locking
- Transaction Isolation Levels in SQL Server
- The Lock Manager
- Monitoring Lock Activity in SQL Server
- SQL Server Lock Types
- SQL Server Lock Granularity
- Lock Compatibility
- Locking Contention and Deadlocks
- Table Hints for Locking
- Optimistic Locking
- Summary
- Chapter 38. Database Design and Performance
- Chapter 39. Monitoring SQL Server Performance
- Chapter 40. SQL Server Database Engine Tuning Advisor
- Chapter 41. Managing Workloads with the Resource Governor
-
Part VI: SQL Server High Availability
- Chapter 42. SQL Server High Availability Fundamentals
-
Chapter 43. Data Replication
- What’s New in Data Replication
- What Is Replication?
- The Publisher, Distributor, and Subscriber Magazine Metaphor
-
Replication Scenarios
- The Central Publisher Replication Model
- The Central Publisher with Remote Distributor Replication Model
- The Publishing Subscriber Replication Model
- The Central Subscriber Replication Model
- The Multiple Publishers with Multiple Subscribers Replication Model
- The Updating Subscribers Replication Model
- The Peer-to-Peer Replication Model
- Subscriptions
- Replication Agents
- Planning for SQL Server Data Replication
- SQL Server Replication Types
- Basing the Replication Design on User Requirements
- Setting Up Replication
- Scripting Replication
-
Monitoring Replication
- Replication Monitoring SQL Statements
- Monitoring Replication within SQL Server Management Studio
- Troubleshooting Replication Failures
- Peer-to-Peer Replication
- The Performance Monitor
- Replication in Heterogeneous Environments
- Backup and Recovery in a Replication Configuration
- Some Thoughts on Performance
- Log Shipping
- Change Data Capture
- The Change Data Capture Tables
- Enabling CDC for a Database
- Enabling CDC for a Table
- Querying the CDC Tables
- CDC and DDL Changes to Source Tables
- CDC and AlwaysOn Availability Groups
- Change Tracking
- Summary
- Chapter 44. SQL Server Failover Clustering
- Chapter 45. SQL Server AlwaysOn and Availability Groups
-
Part VII: SQL Server Business Intelligence Features
-
Chapter 46. SQL Server 2014 Analysis Services
- What’s New in SSAS
- Understanding SSAS and OLAP
- Understanding the SSAS Environment Wizards
- An Analytics Design Methodology
-
An OLAP Requirements Example: CompSales International
- CompSales International Requirements
- OLAP Cube Creation
- Using SQL Server Visual Studio BI
- Creating an OLAP Database
- Defining Dimensions and Hierarchies
- Creating the Other Dimensions
- Creating the Cube
- Building and Deploying the Cube
- Populating the Cube with Data
- Aggregating Data Within the Cube
- Browsing Data in the Cube
- Delivering Data to Users
- ADO MD
- Query Analysis and Optimization
- Generating a Relational Database
- Summary
- Chapter 47. SQL Server Integration Services
-
Chapter 48. SQL Server 2014 Reporting Services
- What’s New in SSRS 2014
- Reporting Services Architecture
- Installing and Configuring SSRS
-
Developing Reports
- Tools of the Trade
- Report Basics
- Overview of the Report Development Process
- Data Planning and Preparation
- Using Shared Data Sources
- Using Datasets
- Using Shared Datasets
- Developing Reports Using SSDT
- Working with the Tablix
- Understanding Expressions
- Report Design Fundamentals
- Designing Reports Using Report Builder
- Report Builder and Report Model Security
- Enabling Report Builder
- Management and Security
- Performance and Monitoring
- Summary
- Chapter 49. Data Quality Services
- Chapter 50. Master Data Services
- Chapter 51. Parallel Data Warehouse
-
Chapter 46. SQL Server 2014 Analysis Services
- Index
- Code Snippets
Product information
- Title: Microsoft SQL Server 2014 Unleashed
- Author(s):
- Release date: June 2015
- Publisher(s): Sams
- ISBN: 9780134084473
You might also like
book
Professional Microsoft SQL Server 2014 Administration
Learn to take advantage of the opportunities offered by SQL Server 2014 Microsoft's SQL Server 2014 …
book
Microsoft SQL Server 2012 Internals
Dive deep inside the architecture of SQL Server 2012 Explore the core engine of Microsoft SQL …
book
Microsoft SQL Server 2012 Bible
Harness the powerful new SQL Server 2012 Microsoft SQL Server 2012 is the most significant update …
video
SQL Server 2016 Fundamentals for the Accidental DBA LiveLessons
6+ Hours of Video Instruction Help non-DBAs learn the critical skills they need to manage SQL …