reading

Cover
Title
Copyright
Dedication
About the Authors
About the Contributors
About the Technical Editors
Credits
Acknowledgments
Introduction
Part I: Laying the Foundations
1. Chapter 1: The World of SQL Server
  1. SQL Server History
  2. SQL Server in the Database Market
  3. SQL Server Components
  4. Editions of SQL Server 2012
  5. Notable SQL Server 2012 Enhancements
  6. Summary
2. Chapter 2: Data Architecture
  1. Information Architecture Principle
  2. Database Objectives
  3. Smart Database Design
  4. Summary
3. Chapter 3: Installing SQL Server
  1. Preparing the Server
  2. Selecting the Edition
  3. The Installation Process
  4. Summary
4. Chapter 4: Client Connectivity
  1. Enabling Server Connectivity
  2. SQL Server Native Client Features
  3. Summary
5. Chapter 5: SQL Server Management and Development Tools
  1. Organizing the Interface
  2. Registered Servers
  3. Object Explorer
  4. Using the Query Editor
  5. Using the Solution Explorer
  6. Getting a Jumpstart on Code with Templates and Code Snippets
  7. Summary
Part II: Building Databases and Working with Data
1. Chapter 6: Introducing Basic Query Flow
  1. Understanding Query Flow
  2. FROM Clause Data Sources
  3. WHERE Conditions
  4. (0 row(s) affected)Columns, Stars, Aliases, and Expressions
  5. Ordering the Result Set
  6. Select Distinct
  7. TOP ()
  8. Summary
2. Chapter 7: Relational Database Design and Creating the Physical Database Schema
  1. Database Basics
  2. Data Design Patterns
  3. Normal Forms
  4. Strategy Considerations
  5. Summary
3. Chapter 8: Data Types, Expressions, and Scalar Functions
  1. Data Types
  2. Building Expressions
  3. Scalar Functions
  4. String Functions
  5. Soundex Functions
  6. Data-Type Conversion Functions
  7. Server Environment Information
  8. Summary
4. Chapter 9: Merging Data with Joins, Subqueries, and CTEs
  1. Using Joins
  2. Set Difference Queries
  3. Using Unions
  4. Subqueries
  5. Summary
5. Chapter 10: Aggregating, Windowing, and Ranking Data
  1. Aggregating Data
  2. Grouping Within a Result Set
  3. Windowing and Ranking
  4. Ranking Functions
  5. Summary
6. Chapter 11: Projecting Data Through Views
  1. Why Use Views?
  2. The Basic View
  3. A Broader Point of View
  4. Locking Down the View
  5. Using SQL Synonyms
  6. Summary
7. Chapter 12: Modifying Data In SQL Server
  1. Inserting Data
  2. Updating Data
  3. Deleting Data
  4. Merging Data
  5. Returning Modified Data
  6. Summary
Part III: Advanced T-SQL Data Types and Querying Techniques
1. Chapter 13: Working with Hierarchies
  1. HierarchyID
  2. HierarchyID Methods
  3. Indexing Strategies
  4. Hierarchical Data Alternatives
  5. Summary
2. Chapter 14: Using XML Data
  1. The XML Data Type
  2. XML Data Type Methods
  3. FOR XML
  4. XQuery and FLWOR Operations
  5. Summary
3. Chapter 15: Executing Distributed Queries
  1. Distributed Query Overview
  2. Developing Distributed Queries
  3. Performance Consideration
  4. Summary
Part IV: Programming with T-SQL
1. Chapter 16: Programming with T-SQL
  1. Transact-SQL Fundamentals
  2. Working with Variables
  3. Procedural Flow
  4. Examining SQL Server with Code
  5. Temporary Tables and Table Variables
  6. What's New in T-SQL for 2012
  7. Error Handling
  8. Bulk Operations
  9. Summary
2. Chapter 17: Developing Stored Procedures
  1. Managing Stored Procedures
  2. Passing Data to Stored Procedures
  3. Returning Data from Stored Procedures
  4. Summary
3. Chapter 18: Building User-Defined Functions
  1. Scalar Functions
  2. Inline Table-Valued Functions
  3. Multistatement Table-Valued Functions
  4. Best Practices with User-Defined Functions
  5. Summary
Part V: Enterprise Data Management
1. Chapter 19: Configuring SQL Server
  1. Setting the Options
  2. Configuration Options
  3. Summary
2. Chapter 20: Policy Based Management
  1. Defining Policies
  2. Evaluating Policies
  3. Summary
3. Chapter 21: Backup and Recovery Planning
  1. Recovery Concepts
  2. Recovery Models
  3. Backing Up the Database
  4. Working with the Transaction Log
  5. Recovery Operations
  6. System Databases Recovery
  7. Performing a Complete Recovery
  8. Summary
4. Chapter 22: Maintaining the Database
  1. DBCC Commands
  2. Managing Database Maintenance
  3. Summary
5. Chapter 23: Transferring Databases
  1. Copy Database Wizard
  2. Working with SQL Script
  3. Detaching and Attaching
  4. Import and Export Wizard
  5. Data-Tier Application (DAC)
  6. Summary
6. Chapter 24: Database Snapshots
  1. How Do Database Snapshots Work?
  2. Using Database Snapshots
  3. Summary
7. Chapter 25: Asynchronous Messaging with Service Broker
  1. Configuring a Message Queue
  2. Working with Dialogs
  3. What's New in Service Broker for SQL Server 2012?
  4. Monitoring and Troubleshooting Service Broker
  5. Summary
8. Chapter 26: Log Shipping
  1. Availability Testing
  2. Warm Standby Availability
  3. Defining Log Shipping
  4. Checking Log Shipping Configuration
  5. Monitoring Log Shipping
  6. Modifying or Removing Log Shipping
  7. Switching Roles
  8. Summary
9. Chapter 27: Database Mirroring
  1. Database Mirroring Overview
  2. Defining and Configuring Database Mirroring
  3. Checking Database Mirroring Configuration
  4. Monitoring Database Mirroring
  5. Pausing or Removing Database Mirroring
  6. Role Switching
  7. High Availability/AlwaysOn
  8. Summary
10. Chapter 28: Replicating Data
  1. Moving Data Between Servers
  2. Replication Concepts
  3. Configuring Replication
  4. Summary
11. Chapter 29: Clustering
  1. What Does Clustering Do?
  2. Configuring Clustering
  3. Summary
12. Chapter 30: Configuring and Managing SQL Server with PowerShell
  1. Why Use PowerShell?
  2. Basic PowerShell
  3. SQL Server PowerShell Extensions
  4. Communicating with SQL Server via SMO
  5. Scripting SQL Server Tasks
  6. Summary
13. Chapter 31: Managing Data in Windows Azure SQL Database
  1. Overview of Azure SQL Database
  2. Managing Windows Azure SQL Database
  3. High Availability and Scalability
  4. Migrating Data to SQL Database
  5. Summary
Part VI: Securing Your SQL Server
1. Chapter 32: Authentication Types in SQL Server
  1. Windows Authentication
  2. SQL Authentication
  3. Differences Between SQL and Windows Authentication
  4. Kerberos and Windows Authentication Delegation
  5. Summary
2. Chapter 33: Authorizing Securables
  1. Permission Chains
  2. Object Ownership
  3. Securables Permissions
  4. Object Security
  5. A Sample Security Model
  6. Views and Security
  7. Summary
3. Chapter 34: Data Encryption
  1. Introducing Data Encryption
  2. Summary
4. Chapter 35: Row-Level Security
  1. The Security Table
  2. Assigning Permissions
  3. Checking Permissions
  4. Summary
Part VII: Monitoring and Auditing
1. Chapter 36: Creating Triggers
  1. Trigger Basics
  2. Working with the Transaction
  3. Multiple-Trigger Interaction
  4. Transaction-Aggregation Handling
  5. DDL Triggers
  6. Managing DDL Triggers
  7. Developing DDL Triggers
  8. Summary
2. Chapter 37: Performance Monitor and PAL
  1. Using PerfMon
  2. Summary
3. Chapter 38: Using Profiler and SQL Trace
  1. Features of SQL Server Profiler
  2. Running Profiler
  3. Using SQL Trace
  4. Summary
4. Chapter 39: Wait States
  1. The SQL Server OS
  2. Examining Wait Statistics
  3. Common Red-Flag Wait Types
  4. Other Ways to Gather Wait data
  5. Summary
5. Chapter 40: Extended Events
  1. The Extended Events Object Model
  2. The system_health Session
  3. The Extended Events Profiler
  4. Summary
6. Chapter 41: Data Change Tracking and Capture
  1. Configuring Change Tracking
  2. Querying Change Tracking
  3. Removing Change Tracking
  4. Change Data Capture
  5. New in SQL Server 2012
  6. Enabling CDC
  7. Working with Change Data Capture
  8. Removing Change Data Capture
  9. Summary
7. Chapter 42: SQL Audit
  1. SQL Audit Technology Overview
  2. Creating an Audit
  3. Server Audit Specifications
  4. Database Audit Specifications
  5. Viewing the Audit Trail
  6. Summary
8. Chapter 43: Management Data Warehouse
  1. Using the Management Data Warehouse
  2. Configuring MDW
  3. Setting Up Data Collection
  4. Viewing MDW Reports
  5. Creating Custom Data Collector Sets
  6. Summary
Part VIII: Performance Tuning and Optimization
1. Chapter 44: Interpreting Query Execution Plans
  1. Viewing Query Execution Plans
  2. Understanding Execution Plan Operators
  3. Summary
2. Chapter 45: Indexing Strategies
  1. Zen and the Art of Indexing
  2. Indexing Basics
  3. The Path of the Query
  4. A Comprehensive Indexing Strategy
  5. Specialty Indexes
  6. Summary
3. Chapter 46: Maximizing Query Plan Reuse
  1. Query Compiling
  2. Query Recompiles
  3. Summary
4. Chapter 47: Managing Transactions, Locking, and Blocking
  1. The ACID Properties
  2. Programming Transactions
  3. Default Locking and Blocking Behavior
  4. Monitoring Locking and Blocking
  5. Dealing with Deadlocks
  6. Understanding SQL Server Locking
  7. Transaction Isolation Levels
  8. Application Locks
  9. Application Locking Design
  10. Transaction-Log Architecture
  11. Transaction Performance Strategies
  12. Summary
5. Chapter 48: Data Compression
  1. Understanding Data Compression
  2. Applying Data Compression
  3. Summary
6. Chapter 49: Partitioning
  1. Partitioning Strategies
  2. Partitioned Views
  3. Partitioned Tables and Indexes
  4. Summary
7. Chapter 50: Resource Governor
  1. Exploring the Fundamentals of the Resource Governor
  2. Performance Monitoring of Resource Governor
  3. Views and Limitations
  4. Summary
Part IX: Business Intelligence
1. Chapter 51: Business Intelligence Database Design
  1. Data Warehousing
  2. Designing a Data Warehouse Using a Star Schema
  3. Designing Your Data Warehouse Using a Snowflake Schema
  4. Ensuring Consistency within a Data Warehouse
  5. Loading Data
  6. Summary
2. Chapter 52: Building, Deploying, and Managing ETL Workflows in Integration Services
  1. Exploring the SSIS Environment in Brief
  2. Exploring the SSIS Environment in More Detail
  3. Deploying and Executing Projects and Packages
  4. Summary
3. Chapter 53: Building Multidimensional Cubes in Analysis Services with MDX
  1. Analysis Services Quick Start
  2. Analysis Services Architecture
  3. Building a Database
  4. Dimensions
  5. Cubes
  6. Data Storage
  7. Cube Processing
  8. Summary
4. Chapter 54: Configuring and Administering Analysis Services
  1. Installing Analysis Services
  2. Configuring Basic Analysis Services Settings
  3. Advanced SSAS Deployments
  4. Reviewing Query Performance with SQL Profiler
  5. Summary
5. Chapter 55: Authoring Reports in Reporting Services
  1. Report Authoring Environments
  2. The Basic Elements of a Report
  3. Building a Report with the Report Wizard
  4. Authoring a Report from Scratch
  5. Exploring the Report Designer
  6. Using Reporting Services Features to Visualize Your Data
  7. Designing the Report Layout
  8. Building Reports with Report Builder
  9. Summary
6. Chapter 56: Configuring and Administering Reporting Services
  1. Installing Reporting Services
  2. Deploying Reporting Services Reports
  3. Managing Security with Reporting Services
  4. Disaster Recovery
  5. Summary
7. Chapter 57: Data Mining with Analysis Services
  1. The Data Mining Process
  2. Modeling with Analysis Services
  3. Algorithms
  4. Cube Integration
  5. Summary
8. Chapter 58: Creating and Deploying BI Semantic Models
  1. What Is a BI Semantic Model?
  2. The Development Environment
  3. Creating BI Semantic Models Using PowerPivot
  4. Extending a BI Semantic Model with PowerPivot
  5. Deploying BI Semantic Models to SharePoint
  6. Managing Automatic Data Refresh of PowerPivot Workbooks in SharePoint 2010
  7. Creating BI Semantic Models Using SQL Server Data Tools
  8. Extending a BI Semantic Model with SQL Server Data Tools
  9. Deploying BI Semantic Models to an Analysis Services Instance
  10. Summary
9. Chapter 59: Creating and Deploying Power View Reports
  1. Power View Requirements
  2. Creating and Deploying Reports with Power View
  3. Deploying Power View Reports
  4. Summary
10. End User License Agreement

List of Illustrations

Chapter 1: The World of SQL Server
1. Figure 1.1 Example of MDX query in Analysis Services.
2. Figure 1.2 SQL Server Management Studio Query Interface.
Chapter 2: Data Architecture
1. Figure 2.1 Data store types and their typical relationships
2. Figure 2.2 Smart Database Design is the premise that an elegant physical schema makes the data intuitively obvious and enables writing great set-based queries that respond well to indexing. This in turn creates short, tight transactions, which improves concurrency and scalability, while reducing the aggregate workload of the database. This flow from layer to layer becomes a methodology for designing and optimizing databases.
Chapter 3: Installing SQL Server
1. Figure 3.1 SQL Server 2012 Installation Center.
2. Figure 3.2 Installation section.
3. Figure 3.3 SQL Server 2012 Setup Support Rules results.
4. Figure 3.4 Product updates.
5. Figure 3.5 SQL Server 2012 Setup Support Rules results.
6. Figure 3.6 Product Key screen.
7. Figure 3.7 Setup Role screen with SQL Server feature selection options.
8. Figure 3.8 Feature selection list.
9. Figure 3.9 Instance configuration.
10. Figure 3.10 Disk Requirements.
11. Figure 3.11 Service Accounts.
12. Figure 3.12 Database Engine Configuration.
13. Figure 3.13 Ready to Install the configuration summary.
14. Figure 3.14 Installation results.
15. Figure 3.15 PowerShell command line.
16. Figure 3.16 Default minimum and maximum memory settings.
17. Figure 3.17 SQL Server Configuration Manager.
Chapter 4: Client Connectivity
1. Figure 4.1 The SQL Server Configuration Manager establishes the connectivity protocols used by SQL Server to communicate with clients.
2. Figure 4.2 The SQL Server Configuration Manager view for SQL Native Client Configuration Client Protocols.
Chapter 5: SQL Server Management and Development Tools
1. Figure 5.1 Moving a floating Window in Management Studio presents several drop points. The shaded area indicates where the dropped window will be placed.
2. Figure 5.2 Although Management Studio can be configured with multiple windows, this is a common configuration for doing development work: Object Explorer Details for searches and Query Editor for script-style coding in the tabbed view, with a little Object Explorer on the side.
3. Figure 5.3 Registered Servers is the tool used to manage multiple servers. Here, we see the context menu showing how to manage service control remotely on your servers.
4. Figure 5.4 Multi-server results in Management Studio.
5. Figure 5.5 Object Explorer's tree structure invites you to explore the various components of SQL Server management and development.
6. Figure 5.6 Using the Table Designer tool, you can create tables or edit their designs.
7. Figure 5.7 The AdventureWorks database relationships viewed with the Database Diagram tool.
8. Figure 5.8 Object Explorer's Query Designer.
9. Figure 5.9 The server or database standard reports are a great way to quickly investigate your SQL Server.
10. Figure 5.10 You can view and set Advanced and ASNI query options using the Query Options dialog. This view shows the Advanced Query Options.
11. Figure 5.11 Query Editor's capability to graphically display the execution plan of a query is perhaps its most useful feature.
Chapter 6: Introducing Basic Query Flow
1. Figure 6.1 Use the Query Designer to graphically create queries.
2. Figure 6.2 A simplified view of the logical flow of the query showing how data moves through the major clauses of the SQL select command.
3. Figure 6.3 The physical execution plan is different from the syntactical order, or logical understanding, of the query.
4. Figure 6.4 Within Management Studio's Query Designer, you can define the sort order and sort type in the column pane. The TOP() predicate is set for the Query Designer inside the query's Properties page.
Chapter 7: Relational Database Design and Creating the Physical Database Schema
1. Figure 7.1 You can identify entities along a continuum, from overly generalized with a single table, to overly specific with too many tables.
2. Figure 7.2 A one-to-many relationship consists of a primary entity and a secondary entity. The secondary entity's foreign key points to the primary entity's primary key. In this case, the Sales.SalesOrderDetail's SalesOrderID is the foreign key that relates to Sales.SalesOrderHeader's primary key.
3. Figure 7.3 A simple method for diagramming logical schemas.
4. Figure 7.4 The one-to-many relationship relates zero to many tuples (rows) in the secondary entity to a single tuple in the primary entity.
5. Figure 7.5 This one-to-one relationship partitions contact data, segmenting additional employee information into a separate entity.
6. Figure 7.6 The many-to-many logical model shows multiple tuples on both ends of the relationship.
7. Figure 7.7 The many-to-many implementation adds an associative table to create artificial one-to-many relationships for both tables.
8. Figure 7.8 In the associative entity (SpecialOfferProduct), each special offer can be represented multiple times, which creates an artificial one-specialoffer-to-many-product relationship. Likewise, each product can be listed multiple times in the associative entity, creating a one-product-to-many-special relationship.
9. Figure 7.9 The supertype/subtype pattern uses an optional one-to-one relationship that relates a primary key to a primary key.
10. Figure 7.10 The domain integrity lookup pattern uses a foreign key to ensure that only valid data is entered into the attribute.
11. Figure 7.11 The reflexive, or recursive, relationship is a one-to-many relationship between two tuples of the same entity.
12. Figure 7.12 The conceptual diagram of a many-to-many recursive relationship shows multiple cardinality at each end of the relationship.
13. Figure 7.13 The physical implementation of the many-to-many reflexive relationship must include an associative entity to resolve the many-to-many relationship, just like the many-to-many two-entity relationship.
14. Figure 7.14 The entity-values pairs pattern is a simple design with only four tables: class/type, attribute/column, object/item, and value. The value table stores every value for every attribute for every item — one long list.
15. Figure 7.15 Visualizing the database as three layers can be useful when designing the conceptual diagram and coding the SQL DLL implementation.
Chapter 9: Merging Data with Joins, Subqueries, and CTEs
1. Figure 9.1 A join merges rows from one data set with rows from another data set, creating a new set of rows that includes columns from both. The code, 101, is common to Smith and order number 1 and merges the two original rows into a single result row.
2. Figure 9.2 Relational joins are based on the overlap, or common intersection, of two data sets.
3. Figure 9.3 The inner join includes only those rows from each side of the join that are contained within the intersection of the two data sources.
4. Figure 9.4 An outer join includes not only rows from the two data sources with a match, but also unmatched rows from outside the intersection.
5. Figure 9.5 The full outer join returns all the data from both data sets, matching the rows where it can and presenting NULL when it cannot.
6. Figure 9.6 The database diagram of the Employee table shows the self-referencing foreign key relationship.
7. Figure 9.7 A graphical representation of a cross join is simply two tables without a join condition.
8. Figure 9.8 The set difference query finds data outside the intersection of the two data sets.
9. Figure 9.9 A union vertically appends the result of one SELECT statement to the result of another SELECT statement.
Chapter 11: Projecting Data Through Views
1. Figure 11.1 Creating a view in Management Studio's Query Designer.
2. Figure 11.2 When the query that references a view is submitted to SQL Server, the query parser picks the query apart and replaces the name of the view with the view's SELECT statement.
3. Figure 11.3 The dependency chain for nested views is easily seen in the Object Dependencies dialog. Here, the vEmployeeListDBA includes the nested view vEmployeeList, which in turn is based on the Employee table, and so on.
Chapter 15: Executing Distributed Queries
1. Figure 15.1 New Linked Server Dialog.
2. Figure 15.2 Creating a Named Range.
3. Figure 15.3 Distributed Transaction Coordinator.
Chapter 16: Programming with T-SQL
1. Figure 16.1 A SQL Server RAISERROR error in the Windows Application event log. Notice that the server and database name are embedded in the error data.
2. Figure 16.2 Viewing an error in the SQL Server log using Management Studio.
Chapter 17: Developing Stored Procedures
1. Figure 17.1 Possible ways for stored procedures to pass data.
Chapter 18: Building User-Defined Functions
1. Figure 18.1 Management Studio's Object Explorer lists all the user-defined functions within a database, organized by table-valued and scalar-valued functions.
Chapter 19: Configuring SQL Server
1. Figure 19.1 The General tab of Management Studio's Server Properties dialog.
2. Figure 19.2 Use Management Studio's Database Properties Options tab to configure the most common database properties.
3. Figure 19.3 The ANSI settings that SQL Server uses to run your queries.
4. Figure 19.4 The Surface Area Configuration facet exposes properties that enable you to allow or disallow features.
5. Figure 19.5 Add startup parameters to the SQL Server service to change its behavior.
6. Figure 19.6 Memory tab of Management Studio's SQL Server Properties dialog.
7. Figure 19.7 The Processors tab shows the processors available on the system and enables you to set how SQL Server uses them.
8. Figure 19.8 Security tab of Management Studio's SQL Server Properties dialog.
9. Figure 19.9 Connections tab of Management Studio's SQL Server Properties dialog.
10. Figure 19.10 Advanced tab of Management Studio's SQL Server Properties dialog.
11. Figure 19.11 Enabling FILESTREAM feature using SQL Server Configuration Manager.
12. Figure 19.12 The Database Settings tab of Management Studio's Server Properties.
Chapter 20: Policy Based Management
1. Figure 20.1 You can also evaluate policies directly against an object, Under the Management node PBM's policies, conditions, and facets in Object Explorer.
2. Figure 20.2 The Facet Properties' General page lists of all the facet properties and their descriptions. In this case, it's showing the properties for the Database facet.
3. Figure 20.3 The View Facets dialog, opened from any object's context menu, presents a browsable UI of every facet and property that can apply to that type of object, and even can export a new policy to match the current object's settings.
4. Figure 20.4 This condition includes an expression that tests the Database Facet's @AutoShrink. The condition evaluates as True if @AutoShrink = False.
5. Figure 20.5 Use the Advanced Edit dialog to create each side of the expression. In this case it shows the left side of the AutoShrink expression.
6. Figure 20.6 Viewing a Policy. This policy enforces the AutoShrink condition for every database on demand.
7. Figure 20.7 All the policies that can be run on-demand for an object can be selected and evaluated using the Evaluate Polices dialog.
8. Figure 20.8 Here the Aesop database passes the No AutoShrinkage policy and declares to be in good health.
Chapter 21: Backup and Recovery Planning
1. Figure 21.1 A typical recovery plan using the simple recovery model includes only full and differential backups.
2. Figure 21.2 A sample recovery plan using the full recovery model, using full, differential, and transaction-log backups.
3. Figure 21.3 The General page of the Back Up Database form.
4. Figure 21.4 The Options page of the Back Up Database form.
5. Figure 21.5 The inactive transactions are all those prior to the oldest active transaction.
6. Figure 21.6 Only the correct sequence of restoring from multiple backup files is possible from Management Studio's Restore Database form.
7. Figure 21.7 The Timeline.
8. Figure 21.8 The Options page of the Restore Database form.
9. Figure 21.9 The Restore Page interface lets you check and see which database pages are marked as suspect.
Chapter 22: Maintaining the Database
1. Figure 22.1 Create a maintenance plan by adding some tasks and scheduling a time to perform the tasks.
2. Figure 22.2 Use the Toolbox to add new tasks to your maintenance plan.
3. Figure 22.3 Define the task specifics using the Properties window.
4. Figure 22.4 In addition to the Properties window, you can use the task-related dialog box for configuration.
5. Figure 22.5 View T-SQL button enables you to see the Transact-SQL command.
6. Figure 22.6 Define a schedule that meets the task requirements; use off-hours scheduling for tasks that require many resources.
7. Figure 22.7 Add new connections as needed to perform maintenance tasks.
8. Figure 22.8 Add new connections as needed to perform maintenance tasks.
Chapter 23: Transferring Databases
1. Figure 23.1 Select your preferred choice of transfer.
2. Figure 23.2 Select the databases you want to copy or move.
3. Figure 23.3 Management Studio can generate scripts for any objects within the database.
4. Figure 23.4 Select your options for how your script should be saved or published.
5. Figure 23.5 Advanced Scripting Options.
6. Figure 23.6 Results of the script generation process.
7. Figure 23.7 The Detach Database feature removes the database from SQL Server's list of databases and frees the files for copying.
8. Figure 23.8 The database may be reattached by means of Management Studio's Attach Database tool.
9. Figure 23.9 Configuring the destination table and column mappings.
10. Figure 23.10 Indicating to run the package immediately and saving the package with encryption.
Chapter 24: Database Snapshots
1. Figure 24.1 Database snapshot using copy-on-first-write Technology.
2. Figure 24.2 Database file information.
3. Figure 24.3 Viewing database snapshots in Object Explorer.
4. Figure 24.4 Viewing the size of the database snapshot data file.
5. Figure 24.5 Users querying the database snapshot, accessing the updated pages from the database snapshot, and unchanged pages from the source database.
Chapter 25: Asynchronous Messaging with Service Broker
1. Figure 25.1 Asynchronous Shipping Notification with Service Broker.
2. Figure 25.2 After the objects are created, they can be seen in Object Explorer.
3. Figure 25.3 Query results of the TargetQueue table.
Chapter 26: Log Shipping
1. Figure 26.1 Typical log shipping configuration.
2. Figure 26.2 Enabling the primary database for log shipping.
3. Figure 26.3 Configuring transaction log backup settings for log shipping.
4. Figure 26.4 Configuring the secondary server database for log shipping.
5. Figure 26.5 Configuring the copy job on the secondary server.
6. Figure 26.6 Configuring the restore transaction log job on the secondary server.
7. Figure 26.7 Configuring the monitor server for log shipping.
8. Figure 26.8 Finished configuring log shipping.
9. Figure 26.9 Successful completion of Log Shipping Configuration.
10. Figure 26.10 Executing the Transaction Log Shipping Status report on monitor server.
11. Figure 26.11 Removing a secondary server from the log shipping configuration.
12. Figure 26.12 Deleting the secondary server from the log shipping configuration.
13. Figure 26.13 Checking if you want to completely remove log shipping.
14. Figure 26.14 Completely removed log shipping configuration.
Chapter 27: Database Mirroring
1. Figure 27.1 Synchronous database mirroring mode.
2. Figure 27.2 Asynchronous database mirroring mode.
3. Figure 27.3 Selecting the witness server to save the security configuration.
4. Figure 27.4 Configuring the Principal Server.
5. Figure 27.5 Configuring the Witness Server.
6. Figure 27.6 Configuring endpoints.
7. Figure 27.7 Start database mirroring.
8. Figure 27.8 Database mirroring started.
9. Figure 27.9 Status of principal database.
10. Figure 27.10 Monitoring the database mirroring session.
11. Figure 27.11 Database Mirroring History.
12. Figure 27.12 Setting warning thresholds.
13. Figure 27.13 Database mirroring warnings.
14. Figure 27.14 Overview of a WSFC cluster with an AlwaysOn Availability Group.
15. Figure 27.15 Failover Cluster Validation Wizard displays if any issues exist with creating your cluster.
16. Figure 27.16 Configuring the cluster name and IP address.
17. Figure 27.17 Summary screen of a successfully created cluster.
18. Figure 27.18 Enabling AlwaysOn Availability Groups.
19. Figure 27.19 Selecting databases to be in the availability group.
20. Figure 27.20 Specifying backup preferences for your replicas.
21. Figure 27.21 Configuring the availability group listener.
22. Figure 27.22 Setting up initial synchronization of databases.
23. Figure 27.23 Scripting Out the Availability Group Setup.
24. Figure 27.24 Fully configured AlwaysOn Availability Group.
25. Figure 27.25 Availability group details page.
Chapter 28: Replicating Data
1. Figure 28.1 The Agent Security dialog.
2. Figure 28.2 The Snapshot Agent Security dialog.
Chapter 29: Clustering
1. Figure 29.1 Select Role Services screen.
2. Figure 29.2 Failover Cluster Manager option.
3. Figure 29.3 Select Servers or a Cluster screen.
4. Figure 29.4 Validation progress window.
5. Figure 29.5 Access Point for Administering the Cluster screen.
6. Figure 29.6 Newly created cluster details.
7. Figure 29.7 Cluster disks with friendly names.
8. Figure 29.8 Setup Support Rules screen.
9. Figure 29.9 Cluster Disk Selection screen.
10. Figure 29.10 Database Engine Configuration Screen.
11. Figure 29.11 SQL Server clustered resources.
12. Figure 29.12 MSDTC Dependencies tab.
13. Figure 29.13 MSDTC mapped as the service syntax and result.
14. Figure 29.14 MSDTC mapping verification and result.
15. Figure 29.15 Manual failover options.
16. Figure 29.16 Confirm action pop-up window.
Chapter 30: Configuring and Managing SQL Server with PowerShell
1. Figure 30.1 Navigating the SQL Server file system.
2. Figure 30.2 Database objects.
3. Figure 30.3 SMO scripting objects.
Chapter 31: Managing Data in Windows Azure SQL Database
1. Figure 31.1 SQL Server Management Studio Connected to SQL Database.
2. Figure 31.2 SQL Database Manager.
3. Figure 31.3 Select the database objects to script.
4. Figure 31.4 Set Scripting Options.
5. Figure 31.5 Advanced Scripting Options.
6. Figure 31.6 Save or Publish Scripts.
7. Figure 31.7 Script execution errors.
8. Figure 31.8 Creating an Integration Services Project.
9. Figure 31.9 SSIS Package Control Flow Surface.
10. Figure 31.10 Verifying Empty Contact and Employee Tables.
11. Figure 31.11 Creating a New Connection.
12. Figure 31.12 Defining a New Connection in the Connection Manager for SQL Database.
13. Figure 31.13 Configured Execute SQL Task.
14. Figure 31.14 Source to Destination Mapping.
15. Figure 31.15 Completed Data Flow.
16. Figure 31.16 Completed Control Flow.
17. Figure 31.17 Successfully executed SSIS Package.
18. Figure 31.18 Viewing the Migrated Data.
19. Figure 31.19 Using bcp to export data from the on-premises Contact table.
20. Figure 31.20 Using bcp to import data into SQL Database.
Chapter 32: Authentication Types in SQL Server
1. Figure 32.1 SQL Server authentication in properties.
2. Figure 32.2 SQL Server LoginMode registry key.
Chapter 33: Authorizing Securables
1. Figure 33.1 The Securables page is used to grant specific permission to individual objects.
Chapter 36: Creating Triggers
1. Figure 36.1 Object Explorer lists all triggers for any table and may be used to modify the trigger using the context menu.
2. Figure 36.2 The Nested Triggers configuration option enables a DML statement within a trigger to fire additional triggers.
3. Figure 36.3 A recursive trigger is a self-referencing trigger — one that executes a DML statement that causes itself to be fired again.
Chapter 37: Performance Monitor and PAL
1. Figure 37.1 System Monitor is useful for watching the overall activity within SQL Server.
2. Figure 37.2 The Data Collector Set is configured to write server performance counter data.
3. Figure 37.3 Unformatted Performance Counter Output.
4. Figure 37.4 Formatted performance counter results.
Chapter 38: Using Profiler and SQL Trace
1. Figure 38.1 This SQL Server Profiler uses the T-SQL Duration template and can write information to a file.
2. Figure 38.2 The Trace Properties Events Selection page enables you to select the events tracked by Profiler.
3. Figure 38.3 Moving columns into GROUP BY status.
4. Figure 38.4 SQL Server Profiler can integrate Performance Monitor data and move through the events in sync.
Chapter 39: Wait States
1. Figure 39.1 Querying sys.dm_exec_requests for any request that has a status of running, suspended, or runnable. The query returns the current wait type of the request as well as the last wait type, and the total elapsed time for the request.
2. Figure 39.2 Returns all data from the sys.dm_wait_stats DMV ordered by the highest waits first. This DMV returns the number of waits by wait type, total elapsed time, highest wait time, and the total signal wait time for that wait.
3. Figure 39.3 This query returns all requests in the “waiting list” that are user processes. This query returns any session that may be blocking, the duration of the wait, and the reason the request is waiting.
Chapter 40: Extended Events
1. Figure 40.1 This figure lists all objects in the Extended Events object model.
2. Figure 40.2 You can view the new Extended Events folder in Object Explorer. Users can now create and modify Event Sessions graphically using SQL Server Management Studio.
3. Figure 40.3 You can use the Extended Events user interface in SQL Server 2012 to create new Event Sessions as well as modify existing Event Sessions.
Chapter 41: Data Change Tracking and Capture
1. Figure 41.1 The Database Properties' Change Tracking page displays the current settings and may be used to enable or disable Change Tracking.
2. Figure 41.2 Use the Table Properties dialog to view the table's Change Tracking settings.
Chapter 42: SQL Audit
1. Figure 42.1 Use the Create Audit dialog to define SLQ Server Audit objects, which collect events defined by the Server Audit Specification or the Database Audit Specification.
2. Figure 42.2 Creating a new Server Audit Specification using Management Studio.
3. Figure 42.3 This Database Audit Specification records every select statement executed by the dbo user in the AdventureWorks2012 database and passes the audit data to the SQL Server 2012 Bible SQL Audit bucket.
4. Figure 42.4 Viewing the audit history using Management Studio's Log File Viewer. Here, select statements issued in AdventureWorks2012 are audited on a per-table basis.
Chapter 43: Management Data Warehouse
1. Figure 43.1 Configure Management Data Warehouse context menu.
2. Figure 43.2 Configure Management Data Warehouse Wizard.
3. Figure 43.3 Select Configuration Task screen.
4. Figure 43.4 Configure Management Data Warehouse Storage screen.
5. Figure 43.5 Map Logins and Users screen.
6. Figure 43.6 Complete the Wizard screen.
7. Figure 43.7 Configuration success.
8. Figure 43.8 Set Up Data Collection task option.
9. Figure 43.9 Storage configuration screen.
10. Figure 43.10 Configuration summary.
11. Figure 43.11 Data Collection Sets.
12. Figure 43.12 The Disk Usage Summary report.
13. Figure 43.13 The Server Activity History report.
14. Figure 43.14 The Query Statistics report.
15. Figure 43.15 Custom Data Collector.
Chapter 44: Interpreting Query Execution Plans
1. Figure 44.1 Execution plans show the operators SQL Server uses to satisfy a query.
2. Figure 44.2 Using DMVs, you can view the SQL code and the query execution plan in the procedure cache. Clicking the XML in the right-most column would open another tab with the graphical view of the selected query execution plan.
Chapter 45: Indexing Strategies
1. Figure 45.1 This figure illustrates a simplified view of a clustered index with an identity column as the clustered index key. The first name is the data column.
2. Figure 45.2 This simplified illustration of a nonclustered index has a B-tree index with a first name as the key column. The nonclustered index includes pointers to the clustered index key column.
3. Figure 45.3 Use the output from DBCC SHOW_STATISTICS to determine the last time the statistics were updated and the sampling rate.
4. Figure 45.4 An index seek operation navigates the B-tree index, selects a beginning row, and then scans all the required rows.
5. Figure 45.5 The clustered index scan sequentially reads all the rows from the clustered index.
6. Figure 45.6 A clustered index seek navigates the B-tree index and locates the row efficiently.
7. Figure 45.7 The clustered index seek's predicate has a start and an end, which indicates the range of rows searched for using the B-tree index.
8. Figure 45.8 An index seek operation has the option of seeking to find the first row, and then sequentially scanning on a block of data.
9. Figure 45.9 Query Path 4 (filter by nonkey column) passes every row from an index scan to a filter operation to manually select the rows.
10. Figure 45.10 The nonclustered index does not contain one or more columns that the query requests. To solve the query, SQL Server must perform a bookmark lookup (the dashed line) from the nonclustered index to the base table, which is a clustered index in this example. This illustration shows a single row. In reality it's often hundreds or thousands of rows scattered throughout the clustered index.
11. Figure 45.11 The query execution plan shows the bookmark lookup as an Index seek being joined with a key lookup.
12. Figure 45.12 With the StartDate column included in the index, the queries are solved with an index seek — a perfect covering index.
13. Figure 45.13 The index seek operator can have a seek predicate, which uses the B-tree; and a predicate, which functions as a nonindexed filter.
14. Figure 45.14 Filtering by two indexes adds a merge join into the mix.
15. Figure 45.15 Filtering two criteria using a composite index performs like greased lighting.
16. Figure 45.16 Filtering by the second key column of an index forces an index scan.
Chapter 46: Maximizing Query Plan Reuse
1. Figure 46.1 Viewing the Plan Cache is easy with a few DMVs. You can view the query execution plan by clicking the XML in the query_plan column.
Chapter 47: Managing Transactions, Locking, and Blocking
1. Figure 47.1 Opening multiple Query Editor windows and sending the second tab into a New Vertical Tab Group (using the tab's context menu) is the best way to experiment with transactions.
2. Figure 47.2 Management Studio's All Blocking Transactions Report is a quick way to view key transaction locking and blocking information.
3. Figure 47.3 Activity Monitor displays information about the current locks and any blocking going on. In this instance, spid 54 is blocked by spid 51, which is blocked by spid 52.
4. Figure 47.4 SQL Server Profiler can monitor and display the blocked and blocking code in XML.
5. Figure 47.5 Creating a deadlock situation in Management Studio using two connections tiled vertically.
6. Figure 47.6 SQL Server Profiler can monitor deadlocks using the Locks: Deadlock Graph event and can display the resource conflict that caused the deadlock.
7. Figure 47.7 A dirty read occurs when transaction 2 can see transaction 1's uncommitted changes.
8. Figure 47.8 A nonrepeatable read side effect occurs when transaction 2 selects the same data twice and sees different values.
9. Figure 47.9 When the rows returned by a select are altered by another transaction, the phenomenon is called a phantom row.
10. Figure 47.10 The SQL DML commands are performed in memory as part of a transaction.
11. Figure 47.11 The commit transaction command launches another insert into the transaction log.
12. Figure 47.12 As one of the last steps, the data modification is written to the data file.
Chapter 48: Data Compression
1. Figure 48.1 The sample data before page compression is enabled.
2. Figure 48.2 Prefix compression identifies the best prefix for each column and then stores the prefix character count in each row instead of the prefix characters.
3. Figure 48.3 The storage engine compresses and decompresses data as it's written to and read from the buffer.
4. Figure 48.4 The Data Compression Wizard estimates the compression ratio and applies the selected type of data compression.
Chapter 49: Partitioning
1. Figure 49.1 The partition table's query plan, when run without a WHERE clause restriction, includes all the partition tables as a standard union query.
2. Figure 49.2 When a query with a WHERE clause restriction that includes the partition key retrieves data through the partition view, SQL Server's query processor accesses only the required tables.
3. Figure 49.3 The partition scheme uses the partition function to place the data in separate filegroups.
Chapter 50: Resource Governor
1. Figure 50.1 The Resource Governor Properties dialog box enables you to set the new resource pool's values.
2. Figure 50.2 Performance Monitor enables you to know how the resource pools function.
Chapter 51: Business Intelligence Database Design
1. Figure 51.1 The diagram view of the AdventureWorks data warehouse database shows one fact table and several dimensions resembling a star.
2. Figure 51.2 Snowflake Dimension.
3. Figure 51.3 Simple Dimension Load from SSIS.
4. Figure 51.4 Fact load using SSIS.
5. Figure 51.5 When a new row is inserted into a type 2 dimension, the bit flag is set to on, and the start and end date are set.
6. Figure 51.6 When a row is inserted and a version of that row exists, the bit flag for the existing row is set to off, and the end date is set to the date and time of insertion of the new row.
Chapter 52: Building, Deploying, and Managing ETL Workflows in Integration Services
1. Figure 52.1 The source file. Notice the absence of a ProductID.
2. Figure 52.2 Target table in Adventureworks (two views, one including the foreign key reference to Product).
3. Figure 52.3 A new project and package.
4. Figure 52.4 The Toolbox button and the Variables button.
5. Figure 52.5 The Control Flow and Data Flow Toolboxes, side-by-side.
6. Figure 52.6 Flat File Connection Manager Editor.
7. Figure 52.7 An OLEDB Connection Manager Editor.
8. Figure 52.8 The Destination Mappings tab.
9. Figure 52.9 The Precedence Constraint Editor.
10. Figure 52.10 The Data Flow from Product.ProductReview.
11. Figure 52.11 The Resolve References editor.
12. Figure 52.12 The Project Parameters Editor.
13. Figure 52.13 The ever-helpful Expression Builder.
14. Figure 52.14 Deploying using the Integration Services Deployment Wizard.
15. Figure 52.15 SSMS folder with deployed project.
Chapter 53: Building Multidimensional Cubes in Analysis Services with MDX
1. Figure 53.1 Project Properties.
2. Figure 53.2 Choosing impersonation method for processing.
3. Figure 53.3 Editing relationships within your cube.
4. Figure 53.4 A completed DSV in Analysis Services.
5. Figure 53.5 Example dimension design screen.
6. Figure 53.6 Sample attribute relationships in a dimension.
7. Figure 53.7 A self-referential table.
8. Figure 53.8 Analysis Services cube designer in solution explorer.
9. Figure 53.9 Analysis Services dimension usage tab.
10. Figure 53.10 Cube calculations tab.
11. Figure 53.11 Advanced view for designing aggregations in your cube.
12. Figure 53.12 Creating and setting perspectives in your cube.
13. Figure 53.13 Setting processing options for your cube.
Chapter 54: Configuring and Administering Analysis Services
1. Figure 54.1 The Setup Role dialog.
2. Figure 54.2 The Analysis Services Configuration dialog.
3. Figure 54.3 The SQL Server Configuration Manager showing Analysis Services running.
4. Figure 54.4 Object Explorer showing different Analysis Services instances.
5. Figure 54.5 The Connect to Server dialog.
6. Figure 54.6 Analysis Server Properties.
7. Figure 54.7 Trace Properties dialog Events Selection.
8. Figure 54.8 Trace Results.
Chapter 55: Authoring Reports in Reporting Services
1. Figure 55.1 Query result for reseller contact info.
2. Figure 55.2 The Reseller Contact Report, Preview mode.
3. Figure 55.3 The Reseller Contact report in Design view.
4. Figure 55.4 A matrix report with toggling enabled.
5. Figure 55.5 The Report Parameter Properties window.
6. Figure 55.6 The Available Values tab configuration.
7. Figure 55.7 Parameterizing a dataset built off of a cube.
8. Figure 55.8 The Grouping Properties dialog box.
9. Figure 55.9 Adding fields to values in a Databar.
10. Figure 55.10 Configuring an Indicator.
11. Figure 55.11 Using Sparklines to compare sales trends for products over time.
12. Figure 55.12 The Report Builder Wizard.
13. Figure 55.13 A report created in Report Builder.
14. Figure 55.14 Using the Report Gallery.
Chapter 56: Configuring and Administering Reporting Services
1. Figure 56.1 When installing Reporting Services during a SQL Server 2012 installation, you can install the necessary components for both native mode and SharePoint integrated mode. This figure shows installation of both modes.
2. Figure 56.2 Mapping the Reporting Services Service Application inside SharePoint.
3. Figure 56.3 Use the settings on the property page of a Reporting Services project to configure the deployment options.
4. Figure 56.4 Accessing the properties of a report inside Report Manager.
5. Figure 56.5 Updating the reference for a dataset is done from the Properties page.
6. Figure 56.6 Upload a report.
7. Figure 56.7 Find the Report.
8. Figure 56.8 Enter any relevant descriptions in the properties tab.
9. Figure 56.9 Create a report subscription to be delivered to a windows file share by configuring these options.
10. Figure 56.10 Backup and restore the Reporting Services encryption key on the Encryption Keys tab of Reporting Services Configuration Manager.
11. Figure 56.11 Key Management is located under Service Applications in integrated mode.
Chapter 57: Data Mining with Analysis Services
1. Figure 57.1 Adding a new mining model to your SSAS cube.
2. Figure 57.2 Verifying accuracy with the accuracy chart tool.
3. Figure 57.3 This is a great example of the decision tree being implemented.
4. Figure 57.4 This is a good example of the cluster profile viewer.
5. Figure 57.5 This is a good example of the Neural Network Viewer.
6. Figure 57.6 Naive Bayes dependency viewer is shown here.
Chapter 58: Creating and Deploying BI Semantic Models
1. Figure 58.1 The three layers of the BI Semantic Model.
2. Figure 58.2 PowerPivot add-in for Excel 2010.
3. Figure 58.3 SQL Server Data Tools.
4. Figure 58.4 External data database source options.
5. Figure 58.5 Select Tables and View screen.
6. Figure 58.6 Diagram view.
7. Figure 58.7 Data sliced by products.
8. Figure 58.8 Filtered list of products using slicers.
9. Figure 58.9 Reseller Sales Gross Profit.
10. Figure 58.10 Key Peformance Indicator (KPI) window.
11. Figure 58.11 Excel PivotTable with KPI status.
12. Figure 58.12 Product tables' relationships in the Diagram View.
13. Figure 58.13 Create Hierarchy icon.
14. Figure 58.14 Product Drilldown hierarchy.
15. Figure 58.15 Product Drilldown hierarchy.
16. Figure 58.16 Office Ribbon options' menu and the Switch to Advanced Mode option.
17. Figure 58.17 SalesOrderData Perspective.
18. Figure 58.18 Table Import Wizard dialog window.
19. Figure 58.19 Role Manager Window.
Chapter 59: Creating and Deploying Power View Reports
1. Figure 59.1 Launching Power View from PowerPivot workbook in PowerPivot Gallery.
2. Figure 59.2 New Document drop-down menu options.
3. Figure 59.3 Data Source Properties screen.
4. Figure 59.4 PowerPivot Workbook BI Semantic Model Connection properties.
5. Figure 59.5 Analysis Services BI Semantic Model Connection file properties.
6. Figure 59.6 Library view options of the PowerPivot Gallery.
7. Figure 59.7 Create Power View Report option.
8. Figure 59.8 Column Chart.
9. Figure 59.9 Image slicers with Column Chart.
10. Figure 59.10 Vertical multiples.
11. Figure 59.11 Horizontal multiples.
12. Figure 59.12 Bubble chart.
13. Figure 59.13 Two-column Power View Card.
14. Figure 59.14 Product Catalog using a Card.
15. Figure 59.15 Interactive Power View Report exported to Office PowerPoint.