Contents

Cover

Contents

Title page

Dedication

Acknowledgments

About the Author

Table of Exercises

Introduction

Who Should Read This Book
What Is Covered in This Book
How to Contact the Author

Part I: Introducing SQL Server 2012

Chapter 1: Understanding SQL Server’s Role
1. What Is Information Technology?
2. Introduction to Databases
3. Database Servers and Applications
4. SQL Server’s Role
5. Summary
6. Chapter Essentials
Chapter 2: Installing SQL Server 2012
1. Installation Planning
2. Installing a Default Instance
3. Installing Named Instances
4. Installing to a Cluster
5. Installing Extra Features
6. Upgrading from Previous Versions
7. Validating an Installation
8. Removing an Installation
9. Summary
10. Chapter Essentials
Chapter 3: Working with the Administration Tools
1. SQL Server Configuration Manager
2. SQL Server Management Studio
3. SQL Server Data Tools
4. SQL Server Profiler
5. Books Online
6. Windows Server Administration for the DBA
7. Summary
8. Chapter Essentials
Chapter 4: SQL Server Command-Line Administration
1. Introducing the Command Prompt
2. General Commands
3. Batch Files
4. Mastering SQLCMD
5. Introducing Windows PowerShell
6. Using SQL Server PowerShell Extensions
7. Summary
8. Chapter Essentials
Chapter 5: Querying SQL Server
1. Understanding the SQL Language
2. SQL Statement Types
3. SQL Syntactical Elements
4. Coding Recommendations
5. Using SELECT Statements
6. Advanced Query Techniques
7. Using DDL Statements
8. Using DCL Statements
9. Modifying Data
10. Tuning and Optimizing Queries
11. Summary
12. Chapter Essentials

Part II: Designing Database Solutions

Chapter 6: Database Concepts and Terminology
1. Relational Database Theory
2. Database Design Processes
3. Project Management for the DBA
4. Summary
5. Chapter Essentials
Chapter 7: ERD and Capacity Planning
1. Planning a Database
2. Understanding Entity Relationship Diagramming
3. Building an ERD
4. Capacity Planning
5. Summary
6. Chapter Essentials
Chapter 8: Normalization and Other Design Issues
1. Designing for Normalization
2. Designing for Performance
3. Designing for Availability
4. Designing for Security
5. Summary
6. Chapter Essentials

Part III: Implementing Database Solutions

Chapter 9: Creating SQL Server Databases
1. SQL Server Databases
2. Database Storage
3. Database Options and Properties
4. Creating Databases in the GUI
5. Creating Databases with T-SQL
6. Creating Databases with PowerShell
7. Attaching and Detaching Databases
8. Database Snapshots
9. Summary
10. Chapter Essentials
Chapter 10: Creating Tables
1. Data Types
2. Collations
3. Table Creation Process
4. Data Partitioning
5. Summary
6. Chapter Essentials
Chapter 11: Indexes and Views
1. Understanding Indexes
2. Creating Basic Indexes
3. Creating Advanced Indexes
4. Managing Indexes
5. Understanding Views
6. Creating Views
7. Summary
8. Chapter Essentials
Chapter 12: Triggers and Stored Procedures
1. Triggers Defined
2. Using Triggers
3. Creating Triggers
4. Understanding Stored Procedures
5. Creating Stored Procedures
6. Summary
7. Chapter Essentials
Chapter 13: Implementing Advanced Features
1. Understanding and Installing Analysis Services
2. Understanding Integration Services
3. Understanding and Installing Reporting Services
4. Implementing Database Mail
5. Configuring Full-Text Indexing
6. Implementing Transparent Data Encryption
7. Data Compression
8. Summary
9. Chapter Essentials

Part IV: Administering and Maintaining SQL Server 2012

Chapter 14: Creating Jobs, Operators, and Alerts
1. Standardize, Automate, and Update
2. Understanding SQL Server Jobs
3. Creating T-SQL Jobs
4. Creating SSIS Jobs
5. Creating Windows Command Jobs
6. Creating and Using Operators
7. Creating and Using Alerts
8. Using WSUS for SQL Server 2012
9. Summary
10. Chapter Essentials
Chapter 15: Performance Monitoring and Tuning
1. Performance Tuning Principles
2. Performance and Troubleshooting Tools
3. Blocks, Locks, and Deadlocks
4. SQL Server Profiler
5. Database Engine Tuning Advisor
6. Performance Monitoring with System Monitor
7. Using the Resource Governor
8. Performance Studio
9. Advanced Monitoring Tools
10. Summary
11. Chapter Essentials
Chapter 16: Policy-Based Management
1. Policy-Based Management
2. Centralized Server Management
3. Standardizing with PBM and CMS
4. Summary
5. Chapter Essentials
Chapter 17: Backup and Restoration
1. Backing Up a Database
2. Backing Up System Databases
3. Restoring a Database
4. Backing Up the Environment
5. Summary
6. Chapter Essentials

Part V: SQL Server Security

Chapter 18: Security Threats and Principles
1. Security Defined
2. Security Threats
3. Security Principles
4. Summary
5. Chapter Essentials
Chapter 19: Authentication and Encryption
1. Understanding Authentication
2. SQL Server Authentication Methods
3. Logins, Users, and Roles
4. Understanding Encryption
5. Summary
6. Chapter Essentials
Chapter 20: Security Best Practices
1. Establishing Baselines
2. Implementing Least Privilege
3. Auditing SQL Server Activity
4. Configuring the Surface Area
5. Understanding Common Criteria and C2
6. Summary
7. Chapter Essentials

Part VI: Implementing High Availability and Data Distribution

Chapter 21: AlwaysOn and High Availability
1. Introducing AlwaysOn Technology
2. Mirroring and AlwaysOn
3. Failover Solutions
4. Selecting Hardware for AlwaysOn
5. Summary
6. Chapter Essentials
Chapter 22: SQL Server Failover Clustering
1. Understanding Windows Failover Clustering Service
2. Implementing a Windows Cluster
3. Installing SQL Server 2012 to a Cluster
4. Monitoring and Managing a SQL Server Cluster
5. Summary
6. Chapter Essentials
Chapter 23: Database Mirroring and Snapshots
1. RAID-based Data Redundancy
2. Using Database Mirroring
3. Understanding Log Shipping
4. Implementing Database Snapshots
5. Summary
6. Chapter Essentials
Chapter 24: Implementing Replication
1. SQL Server Replication
2. Importing and Exporting Data
3. Summary
4. Chapter Essentials

Part VII: Implementing Business Intelligence and Reporting

Chapter 25: Data Warehousing
1. Understanding Data Warehouses
2. Implementing Fact Tables
3. Implementing Dimensions
4. Summary
5. Chapter Essentials
Chapter 26: SQL Server Integration Services
1. Integration Issues
2. Installing SSIS
3. Configuring SSIS Security Settings
4. Deploying Packages
5. SSIS Auditing and Event Handling
6. Extracting, Transforming, and Loading Data
7. Summary
8. Chapter Essentials
Chapter 27: Data Quality Solutions
1. Understanding Data Quality Concerns
2. Installing Data Quality Services
3. Using Master Data Services
4. Cleaning Data
5. Summary
6. Chapter Essentials

Appendices

Appendix A: Microsoft’s Certification Program
1. How Do You Become Certified on SQL Server 2012?
2. Tips for Taking a Microsoft Exam
3. Certification Objectives Map
Appendix B: About the Additional Study Tools
1. Additional Study Tools
2. System Requirements
3. Using the Study Tools
4. Troubleshooting

End User License Agreement

List of Illustrations

Chapter 1: Understanding SQL Server’s Role

FIGURE 1.1 The core components of IT
FIGURE 1.2 A table of flat-file databases
FIGURE 1.3 The Sales and Items tables interact in a relational structure.
FIGURE 1.4 Comparing Microsoft Access and SQL Server database security
FIGURE 1.5 A simple implementation of client-server technology with a client accessing a single server directly
FIGURE 1.6 An n-tier application using a SharePoint server to access a backend database server
FIGURE 1.7 An example of multiserver queries with the user querying one server that queries three other servers in turn
FIGURE 1.8 Errors listed in the Error List dialog
FIGURE 1.9 The SQL Server 2000 Enterprise Manager used in earlier versions of SQL Server
FIGURE 1.10 The Query Analyzer from SQL Server 2000
FIGURE 1.11 The SQL Server 2005 Database Mail Configuration Wizard
FIGURE 1.12 SQL Server Integration Services showing a sample project
FIGURE 1.13 The Surface Area Configuration for Features dialog box

Chapter 2: Installing SQL Server 2012

FIGURE 2.1 SQL Server logical architecture
FIGURE 2.2 The SQL Server services are displayed.
FIGURE 2.3 SQL Server Installation Center
FIGURE 2.4 The Setup Support Rules screen
FIGURE 2.5 The Install Setup Files screen
FIGURE 2.6 Potential installation issues
FIGURE 2.7 The Feature Selection screen
FIGURE 2.8 The Server Configuration screen
FIGURE 2.9 The Database Engine Configuration screen
FIGURE 2.10 Installation summary
FIGURE 2.11 The installation is complete.
FIGURE 2.12 SSMS connecting to the installed instance

Chapter 3: Working with the Administration Tools

FIGURE 3.1 The SQL Server Configuration Manager interface
FIGURE 3.2 Creating an alias
FIGURE 3.3 The SSMS interface
FIGURE 3.4 The SSMS Options dialog
FIGURE 3.5 SSMS start-up mode with a query window
FIGURE 3.6 The SSDT interface
FIGURE 3.7 Creating a new project and solution
FIGURE 3.8 Adding a project to an existing solution
FIGURE 3.9 Solution Explorer with multiple projects
FIGURE 3.10 Viewing the package created by the wizard
FIGURE 3.11 Debugging the package
FIGURE 3.12 Viewing the CSV data
FIGURE 3.13 The SQL Server Profiler default interface
FIGURE 3.14 Basic Profiler trace settings
FIGURE 3.15 Event selection in a Profiler trace
FIGURE 3.16 Event filtering in a Profiler trace
FIGURE 3.17 Capturing SSMS start-up events
FIGURE 3.18 Books Online content in the Microsoft Help viewer
FIGURE 3.19 Working with permissions
FIGURE 3.20 The TCP/IP configuration
FIGURE 3.21 Basic IPConfig output
FIGURE 3.22 Detailed IPConfig output
FIGURE 3.23 Verifying DNS with NSLOOKUP
FIGURE 3.24 PathPING in action

Chapter 4: SQL Server Command-Line Administration

FIGURE 4.1 The Windows command prompt
FIGURE 4.2 The command prompt’s general options
FIGURE 4.3 Command-prompt font settings
FIGURE 4.4 Command-prompt screen layout
FIGURE 4.5 The command prompt’s Colors tab
FIGURE 4.6 CD command help
FIGURE 4.7 Directory navigation commands
FIGURE 4.8 Using the TREE command
FIGURE 4.9 Listing files with the TREE command
FIGURE 4.10 DIR command options
FIGURE 4.11 ECHO commands
FIGURE 4.12 Piping output from one command into another
FIGURE 4.13 SQL Server service list at the command prompt
FIGURE 4.14 Automatically responding to a NET STOP prompt
FIGURE 4.15 Analyzing fragmentation with Contig
FIGURE 4.16 Finding PIDs for active connections
FIGURE 4.17 Matching PIDs with process names
FIGURE 4.18 ROBOCOPY output and log
FIGURE 4.19 SQLCMD switches
FIGURE 4.20 SQLCMD in interactive mode
FIGURE 4.21 Viewing the SQLCMD output log
FIGURE 4.22 The Windows PowerShell interface
FIGURE 4.23 Navigating SQL Server in PowerShell
FIGURE 4.24 Executing queries with Invoke-SQLCMD
FIGURE 4.25 Running DBCC CHECKTABLE with Invoke-SQLCMD

Chapter 5: Querying SQL Server

FIGURE 5.1 SSMS as a color-coded editor
FIGURE 5.2 Processing a BETWEEN clause
FIGURE 5.3 The processing of an IN clause
FIGURE 5.4 The sorted results with ORDER BY
FIGURE 5.5 NULLIF result set
FIGURE 5.6 Better NULLIF results using COALESCE
FIGURE 5.7 Using the CONVERT and CAST functions
FIGURE 5.8 Complex INNER JOIN results
FIGURE 5.9 OUTER JOIN result examples
FIGURE 5.10 Results before, during, and after transaction processing and rollback
FIGURE 5.11 Error handling with transaction processing

Chapter 6: Database Concepts and Terminology

FIGURE 6.1 Simple data entry form
FIGURE 6.2 Device Status sample table
FIGURE 6.3 SSMS table designer
FIGURE 6.4 One-to-one diagram
FIGURE 6.5 One-to-many diagram
FIGURE 6.6 Querying data in a one-to-many scenario
FIGURE 6.7 Many-to-many relationship

Chapter 7: ERD and Capacity Planning

FIGURE 7.1 ERD for the Book Collection Database
FIGURE 7.2 Many-to-many relationship in an ERD
FIGURE 7.3 All entities created without relationships
FIGURE 7.4 The Book Collection ERD in Visio
FIGURE 7.5 Open System Architect
FIGURE 7.6 The Books entity shown in OSA
FIGURE 7.7 All entities created in OSA
FIGURE 7.8 Using the DataSizer tool

Chapter 9: Creating SQL Server Databases

FIGURE 9.1 The default data store including the system databases
FIGURE 9.2 Using the tempdb database in code
FIGURE 9.3 Viewing the size of the tempdb database and log file
FIGURE 9.4 Using more than one file in a single filegroup
FIGURE 9.5 The New Database window used to create databases from the GUI
FIGURE 9.6 The Script button’s options used to generate T-SQL scripts
FIGURE 9.7 The automatic script generated from the Script button
FIGURE 9.8 Running the CreateDB.ps1 script
FIGURE 9.9 The AWSS snapshot displayed after creating it with the T-SQL code

Chapter 10: Creating Tables

FIGURE 10.1 Using the Table Designer to create tables
FIGURE 10.2 Displaying the Properties window in the Table Designer view
FIGURE 10.3 The partitioning object hierarchy

Chapter 11: Indexes and Views

FIGURE 11.1 The SQL Server B-tree index structure
FIGURE 11.2 The B-tree index structure for a clustered index on the Customers table
FIGURE 11.3 Creating the Customers table for the Books database
FIGURE 11.4 Verifying the primary key for the clustered index
FIGURE 11.5 Disabling an index within SSMS
FIGURE 11.6 Enabling an index with the Rebuild option
FIGURE 11.7 Viewing the IsDisabled property of the ix_City index in both the disabled and enabled states
FIGURE 11.8 Viewing the fragmentation level on an index

Chapter 12: Triggers and Stored Procedures

FIGURE 12.1 Disabling recursive and nested triggers on the Books database

Chapter 13: Implementing Advanced Features

FIGURE 13.1 OLAP cubes represented as views of data
FIGURE 13.2 SSDT used to create data cubes
FIGURE 13.3 Creating an SSIS package in SSDT

Chapter 14: Creating Jobs, Operators, and Alerts

FIGURE 14.1 Creating a new job in SSMS with the GUI interface
FIGURE 14.2 The job steps page in the New Job dialog
FIGURE 14.3 Creating a new step in a job
FIGURE 14.4 A job step of type SSIS changes the interface.
FIGURE 14.5 Configuring advanced settings for a job step
FIGURE 14.6 The Schedules page with a CPU idle schedule
FIGURE 14.7 Configuring notifications for a job
FIGURE 14.8 Manually running the Backup and Snapshot for the AdventureWorks job
FIGURE 14.9 Viewing job activity in the Job Activity Monitor
FIGURE 14.10 Viewing the history of the Generate System Information Log job
FIGURE 14.11 Configuring an operator for notification within a SQL Server Agent job

Chapter 15: Performance Monitoring and Tuning

FIGURE 15.1 Viewing the blocking processes in sys.dm_exec_requests
FIGURE 15.2 Viewing the code executed by a connection or process in the Activity Monitor
FIGURE 15.3 Viewing the default System Diagnostics data collector set
FIGURE 15.4 Viewing a report in the Reliability and Performance Monitor
FIGURE 15.5 Configuring the stop condition for a data collector set
FIGURE 15.6 Viewing the properties for the Query Statistics system data collector set
FIGURE 15.7 Querying the sys.dm_exec_connections DMV
FIGURE 15.8 Running DBCC SHOWCONTIG against the Production.Product table
FIGURE 15.9 Viewing the Resource Monitor
FIGURE 15.10 Viewing the Resource Monitor report

Chapter 16: Policy-Based Management

FIGURE 16.1 PBM components represented hierarchically
FIGURE 16.2 Viewing the list of available facets in Object Explorer
FIGURE 16.3 The General page of the Create New Condition dialog box
FIGURE 16.4 The Description page of the Create New Condition dialog box
FIGURE 16.5 The General page of the Create New Policy dialog
FIGURE 16.6 The Description page of the Create New Policy dialog
FIGURE 16.7 Viewing the applicable target types by facet
FIGURE 16.8 Viewing the history of an evaluation showing noncompliant targets
FIGURE 16.9 Viewing On Change: Log Only entries in the Event Viewer application log
FIGURE 16.10 Viewing the registered servers in the Registered Servers window of SSMS
FIGURE 16.11 Viewing the options available when right-clicking a server group
FIGURE 16.12 Viewing the polSurfaceAreaLog entry in the application log

Chapter 17: Backup and Restoration

FIGURE 17.1 Querying the database recovery models of all databases
FIGURE 17.2 Viewing the approximate consumed space in a database
FIGURE 17.3 Forcing a database overwrite during a restoration

Chapter 18: Security Threats and Principles

FIGURE 18.1 Viewing the Secunia.com SQL Server vulnerability report
FIGURE 18.2 Using Win Sniffer to capture passwords
FIGURE 18.3 Sniffing a user’s email password from a wireless network connection
FIGURE 18.4 Cracking passwords with L0phtCrack

Chapter 19: Authentication and Encryption

FIGURE 19.1 Viewing LoginMode in REGEDIT
FIGURE 19.2 Viewing the sys.syslogins table that contains the logins within the SQL Server
FIGURE 19.3 Viewing the local password policies
FIGURE 19.4 Managing server role membership within user properties
FIGURE 19.5 Viewing encrypted data without decrypting it
FIGURE 19.6 Viewing encrypted data with the DecryptByKey function

Chapter 20: Security Best Practices

FIGURE 20.1 The SCM Showing the Member Server Security Compliance baseline
FIGURE 20.2 The Security Templates snap-in showing expanded categories
FIGURE 20.3 Using the log file to view the audit logs within SSMS viewer
FIGURE 20.4 Evaluating the surface area policy on a SQL Server 2012 default installation
FIGURE 20.5 Viewing the details of the evaluation

Chapter 21: AlwaysOn and High Availability

FIGURE 21.1 The WEI on Windows 8
FIGURE 22.1 Viewing the warnings in a Validate A Configuration Wizard report
FIGURE 22.2 Viewing the warning details in the report

Chapter 23: Database Mirroring and Snapshots

FIGURE 23.1 RAID levels 0, 1, and 5
FIGURE 23.2 The Mirroring page in the Database Properties dialog
FIGURE 23.3 The Transaction Log Shipping page in the Database Properties dialog

Chapter 24: Implementing Replication

FIGURE 24.1 The Distributor Properties dialog box
FIGURE 24.2 The Publisher Properties dialog box used to select replication databases

Chapter 26: SQL Server Integration Services

FIGURE 26.1 Choosing the default environment for Visual Studio when used with SSDT
FIGURE 26.2 The SSIS 11.0 Properties dialog
FIGURE 26.3 Integration Services Dashboard report
FIGURE 26.4 The SQL Server OLEDB Connection Manager
FIGURE 26.5 The Excel Connection Manager
FIGURE 26.6 The Data Flow tab of an SSIS package
FIGURE 26.7 The error event handler arrow
FIGURE 26.8 The Event Handler tab
FIGURE 26.9 Supported common and other transforms in SSIS packages
FIGURE 26.10 The SQL Server Destination Editor dialog
FIGURE 26.11 The Excel Destination Editor dialog
FIGURE 26.12 The Select Script Component Type dialog shown when you drag a script component to the Data Flow designer

List of Tables

Chapter 2: Installing SQL Server 2012

TABLE 2.1 Hardware Requirements

Chapter 4: SQL Server Command-Line Administration

TABLE 4.1 Command Prompt Color Codes
TABLE 4.2 The Command-Line Switches

Chapter 5: Querying SQL Server

TABLE 5.1 Operator Precedence
TABLE 5.2 String Definition Examples
TABLE 5.3 Sample Table for the NULLIF Function

Chapter 6: Database Concepts and Terminology

TABLE 6.1 Data in context

Chapter 7: ERD and Capacity Planning

TABLE 7.1 Book collection database information
TABLE 7.2 Entity parameters
TABLE 7.3 Entities

Chapter 8: Normalization and Other Design Issues

TABLE 8.1 The Books table without normalization
TABLE 8.2 Duplicate records problem solved
TABLE 8.3 The Authors table
TABLE 8.4 AuthorsBooks linking table
TABLE 8.5 The AuthorTopics table
TABLE 8.6 Books table in 2NF, but not 3NF
TABLE 8.7 Recommended Books table structure
TABLE 8.8 3NF table without Boyce-Codd (BCNF)
TABLE 8.9 BCNF table without 4NF
TABLE 8.10 Columns assigned to entities
TABLE 8.11 Table descriptions in 1NF
TABLE 8.12 The final table descriptions in 3NF

Chapter 9: Creating SQL Server Databases

TABLE 9.1 Database properties defined

Chapter 10: Creating Tables

TABLE 10.1 Numeric data types
TABLE 10.2 Date and time data types
TABLE 10.3 Character data types
TABLE 10.4 Binary data types
TABLE 10.5 Special data types
TABLE 10.6 Column specifications

Chapter 11: Indexes and Views

TABLE 11.1 Sample customer data table

Chapter 15: Performance Monitoring and Tuning

TABLE 15.1 Common performance tuning myths
TABLE 15.2 DBCC commands with examples

Chapter 17: Backup and Restoration

TABLE 17.1 Backup device rotation structure

Chapter 19: Authentication and Encryption

TABLE 19.1 Password policy descriptions and recommendations

Chapter 20: Security Best Practices

TABLE 20.1 Securables and scope level

Chapter 21: AlwaysOn and High Availability

TABLE 21.1 Availability group failover modes and operational modes

Chapter 22: SQL Server Failover Clustering

TABLE 22.1 Failover Clustering quorum modes and functionality
TABLE 22.2 Validate A Configuration Wizard tests and actions

Chapter 24: Implementing Replication

TABLE 24.1 Replication types and their applications

Chapter 25: Data Warehousing

TABLE 25.1 SSAS 2012 aggregate functions
TABLE 25.2 A Type 2 SCD solution

Appendix A: Microsoft’s Certification Program

TABLE A.1 Exam 70-461, Querying Microsoft SQL Server 2012 objectives map
TABLE A.2 Exam 70-462, Administering Microsoft SQL Server 2012 database
TABLE A.3 Exam 70-463, Implementing a Data Warehouse with Microsoft SQL Server 2012

Table of Exercises

Exercise 2.1	Installing a Named Instance
Exercise 3.1	Starting and Stopping Services
Exercise 3.2	Configuring Service Properties
Exercise 3.3	Configuring Protocols
Exercise 3.4	Performing the Initial SSMS Configuration
Exercise 3.5	Opening a New Query Window and Viewing the Error List
Exercise 3.6	Viewing Predesigned Reports
Exercise 3.7	Generating Administrative Task Scripts
Exercise 3.8	Creating a New Project and a New Solution
Exercise 3.9	Creating a New Project in an Existing Solution
Exercise 3.10	Deleting a Project from a Solution
Exercise 3.11	Using the Import and Export Wizard
Exercise 3.12	Configuring IP Settings in the GUI
Exercise 3.13	Opening the Windows Command Prompt
Exercise 3.14	Configuring the Windows Firewall
Exercise 4.1	Preparing Your System for the Batch File
Exercise 4.2	Creating the Batch File
Exercise 4.3	Running the Batch File
Exercise 7.1	Preparing the Visio Environment for Entity Relationship Diagramming
Exercise 7.2	Creating the Visio Entity Relationship Diagram
Exercise 7.3	Creating Relationships Between Entities
Exercise 7.4	Creating an ERD File
Exercise 7.5	Creating the Entities
Exercise 7.6	Creating Relationships
Exercise 7.7	Estimating the Size of a Clustered Index
Exercise 7.8	Estimating the Size of a Clustered Index Table
Exercise 9.1	Creating Multiple Filegroups
Exercise 9.2	Creating the Books Database in the GUI
Exercise 9.3	Detaching the Books Database in the GUI
Exercise 9.4	Attaching the Books Database in the GUI
Exercise 10.1	Assigning Collations at the Column Level
Exercise 10.2	Launching the Table Designer
Exercise 10.3	Creating Columns
Exercise 10.4	Selecting a Primary Key
Exercise 10.5	Specifying Table Properties and Saving the Table
Exercise 10.6	Scripting the Table for Documentation
Exercise 11.1	Setting the Primary Key
Exercise 11.2	Creating the LastName Nonclustered Index
Exercise 11.3	Creating the City Nonclustered Index
Exercise 11.4	Creating the Covering Index
Exercise 11.5	Creating the Filtered Index
Exercise 11.6	Creating a View
Exercise 12.1	Creating a DML Trigger
Exercise 12.2	Creating a DDL Trigger
Exercise 13.1	Installing Analysis Services
Exercise 13.2	Installing Integration Services
Exercise 13.3	Creating a Basic Integration Services Package
Exercise 13.4	Troubleshooting an SSIS Package with Debug
Exercise 13.5	Saving and Scheduling Packages
Exercise 13.6	Installing and Configuring Reporting Services
Exercise 13.7	Configuring Database Mail
Exercise 13.8	Creating a Full-Text Index
Exercise 14.1	Creating a T-SQL Job
Exercise 14.2	Creating the SSIS Export Package
Exercise 14.3	Creating an SSIS Job
Exercise 14.4	Creating a Batch File for Information Gathering
Exercise 14.5	Creating a Windows Command Job
Exercise 14.6	Creating an Operator
Exercise 14.7	Creating a SQL Server Alert in SSMS
Exercise 14.8	Creating a Free Drive Space Alert
Exercise 15.1	Generating a Deadlock Scenario
Exercise 15.2	Creating a Trace with SQL Server Profiler
Exercise 15.3	Creating a DTA Workload File in SQL Server Profiler
Exercise 15.4	Analyzing the Workload File with the DTA Tool
Exercise 15.5	Applying the Saved DTA Recommendations
Exercise 15.6	Installing the System Monitor in a Custom MMC
Exercise 15.7	Viewing Live Performance Data on Windows Server 2003
Exercise 15.8	Viewing Live Performance Data on Windows Server 2008 R2
Exercise 15.9	Creating a Performance Counter Log in Windows Server 2003
Exercise 15.10	Creating a Data Collector Set in Windows Server 2008 R2
Exercise 15.11	Adding SQL Server Counters to a Data Collector Set
Exercise 15.12	Creating the MDW for Performance Studio
Exercise 15.13	Setting Up Data Collection Options
Exercise 15.14	Viewing Performance Studio Reports
Exercise 15.15	Launching the Resource Monitor Directly
Exercise 16.1	Determining Read-Only Properties of Facets
Exercise 16.2	Importing the Microsoft Sample Policies
Exercise 16.3	Creating Custom Categories for Policies
Exercise 16.4	Subscribing to a Category
Exercise 16.5	Creating the conDatabaseSize Condition
Exercise 16.6	Creating the conPasswordRules Condition
Exercise 16.7	Creating the conSurfaceArea Condition
Exercise 16.8	Creating the polDatabaseSizeScheduled Policy
Exercise 16.9	Creating the polPasswordRulesPrevent Policy
Exercise 16.10	Creating the polSurfaceAreaLog Policy
Exercise 16.11	Creating a CMS in SSMS
Exercise 16.12	Creating Server Groups in the CMS
Exercise 16.13	Registering a Server
Exercise 16.14	Creating an Automated Event Log Monitoring Solution
Exercise 17.1	Setting the Recovery Model in SSMS
Exercise 17.2	Creating a Full Backup of the Database
Exercise 17.3	Creating a Backup Device That Points to a File
Exercise 17.4	Restoring to a Point in Time
Exercise 17.5	Backing Up the Tail Log After a Database File Storage Failure
Exercise 17.6	Starting the SQL Server Database Engine in Single-User Mode
Exercise 17.7	Installing Windows Server Backup
Exercise 18.1	Using the MBSA Utility from Microsoft
Exercise 18.2	Creating Strong Password Policies in Windows Domains
Exercise 19.1	Configuring the Authentication Mode in SSMS
Exercise 19.2	Creating a SQL Login
Exercise 19.3	Viewing Local Password Policies
Exercise 19.4	Creating Windows Logins
Exercise 19.5	Creating a Database Role with SSMS
Exercise 19.6	Creating a Database User
Exercise 20.1	Installing Microsoft SCM
Exercise 20.2	Creating a Custom Security Template
Exercise 20.3	Analyzing Security with Templates
Exercise 20.4	Creating a Baseline from Current Settings with SCW
Exercise 20.5	Managing Permissions in SQL Server Management Studio
Exercise 20.6	Enabling a SQL Server Audit
Exercise 20.7	Using sp_configure to Configure the Surface Area
Exercise 20.8	Configuring the Surface Area with PBM
Exercise 20.9	Enabling the C2 Audit Trace
Exercise 22.1	Installing Windows Failover Clustering
Exercise 22.2	Running the Validate A Configuration Wizard
Exercise 22.3	Creating a Failover Cluster
Exercise 23.1	Creating the Mirroring Endpoints
Exercise 23.2	Implementing Log Shipping
Exercise 23.3	Generating a Log Shipping Report
Exercise 24.1	Configuring the Publisher and Distributor
Exercise 24.2	Creating a Publication with a Single Table to Replicate the Production.Product Table
Exercise 24.3	Creating a Subscription
Exercise 24.4	Exporting Data with the Import/Export Wizard
Exercise 26.1	Signing a Package in SSDT
Exercise 26.2	Creating a Deployment Utility
Exercise 26.3	Deploying with the Package Installation Wizard
Exercise 27.1	Creating a Data Quality Project

Senior Acquisitions Editor: Jeff Kellum

Development Editor: Jim Compton

Technical Editors: Mitchell Sellers and Denny Cherry

Production Editor: Liz Britten

Copy Editor: Kim Wimpsett

Editorial Manager: Pete Gaughan

Production Manager: Tim Tate

Vice President and Executive Group Publisher: Richard Swadley

Vice President and Publisher: Neil Edde

Media Project Manager 1: Laura Moss-Hollister

Media Associate Producer: Marilyn Hummel

Media Quality Assurance: Shawn Patrick

Book Designers: Judy Fung and Bill Gibson

Proofreader: Sarah Kaikini, WordOne New York

Indexer: Ted Laux

Project Coordinator, Cover: Katherine Crocker

Cover Designer: Ryan Sneed

Cover Image: iStockphoto.com / Sami Suni

Published simultaneously in Canada

ISBN: 978-1-118-48716-7

ISBN: 978-1-118-65473-6 (ebk.)

ISBN: 978-1-118-65490-3 (ebk.)

ISBN: 978-1-118-65468-2 (ebk.)

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at (877) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2013933939

TRADEMARKS: Wiley, the Wiley logo, and the Sybex logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and SQL Server are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

10 9 8 7 6 5 4 3 2 1

Dear Reader,

Thank you for choosing Microsoft SQL Server 2012 Administration. This book is part of a family of premium-quality Sybex books, all of which are written by outstanding authors who combine practical experience with a gift for teaching.

Sybex was founded in 1976. More than 30 years later, we’re still committed to producing consistently exceptional books. With each of our titles, we’re working hard to set a new standard for the industry. From the paper we print on, to the authors we work with, our goal is to bring you the best books available.

I hope you see all that reflected in these pages. I’d be very interested to hear your comments and get your feedback on how we’re doing. Feel free to let me know what you think about this or any other Sybex book by sending me an email at nedde@wiley.com. If you think you’ve found a technical error in this book, please visit http://sybex.custhelp.com. Customer feedback is critical to our efforts at Sybex.

Best regards,

Neil Edde

Vice President and Publisher

Sybex, an Imprint of Wiley

Table of Exercises

Microsoft^® SQL
Server^® 2012
Administration

Real World Skills for MCSA Certification and Beyond

Acknowledgments

About the Author