Microsoft® SQL Server® 2012: Pocket Consultant

Microsoft® SQL Server® 2012: Pocket Consultant

by William R. Stanek
Released February 2012
Publisher(s): Microsoft Press
ISBN: 9780735670273

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Portable and precise, this pocket-sized guide delivers ready answers for the day-to-day administration of SQL Server. Zero in on core support and maintenance tasks using quick-reference tables, instructions, and lists. You’ll get the focused information you need to save time and get the job done—whether at your desk or in the field!

Get fast facts to:

  • Manage SQL Server services and clients

  • Configure, manage, and tune servers

  • Implement policy-based management

  • Administer security and access

  • Monitor server activity and tune performance

  • Automate maintenance

  • Perform backups and recovery

    •

    Table of contents

    1. Dedication
    2. Introduction
      1. Who Is This Book For?
      2. How Is This Book Organized?
      3. What Is SQL Server 2012?
      4. System Requirements
      5. Conventions Used in This Book
      6. Other Resources
      7. Support and Feedback
        1. Errata
        2. We Want to Hear from You
        3. Stay in Touch
    3. I. Microsoft SQL Server 2012 Essentials
      1. 1. Managing Your SQL Servers
        1. Using SQL Server Management Studio
          1. Getting Started with SQL Server Management Studio
          2. Connecting to a Specific Server Instance
          3. Connecting to a Specific Database
        2. Managing SQL Server Groups
          1. Introducing SQL Server Groups and the Registered Servers View
          2. Creating a Server Group
          3. Deleting a Server Group
          4. Editing and Moving Server Groups
          5. Adding SQL Servers to a Group
        3. Managing Servers
          1. Registering a Connected Server
          2. Registering a New Server in the Registered Servers View
          3. Registering Previously Registered SQL Server 2000 Servers
          4. Updating Registration for Local Servers
          5. Copying Server Groups and Registration Details from One Computer to Another
          6. Editing Registration Properties
          7. Connecting to a Server
          8. Disconnecting from a Server
          9. Moving a Server to a New Group
          10. Deleting a Server Registration
        4. Using Windows PowerShell for SQL Server Management
        5. Starting, Stopping, and Configuring SQL Server Agent
        6. Starting, Stopping, and Configuring MSDTC
        7. Managing SQL Server Startup
          1. Enabling or Preventing Automatic SQL Server Startup
          2. Setting Database Engine Startup Parameters
            1. Adding Startup Parameters
            2. Removing Startup Parameters
            3. Common Startup Parameters
          3. Managing Services from the Command Line
          4. Managing the SQL Server Command-Line Executable
        8. Managing Server Activity
          1. Examining Process Information
          2. Tracking Resource Waits and Blocks
          3. Troubleshooting Deadlocks and Blocking Connections
          4. Tracking Command Execution in SQL Server
          5. Killing Server Processes
      2. 2. Managing SQL Server Services and Clients
        1. Managing SQL Server Component Feature Access
        2. Managing the Network and SQL Server Native Client Configuration
          1. Managing the Connections Configuration
          2. Specifying the Shared Memory Network Configuration
          3. Specifying the Named Pipes Network Configuration
          4. Specifying the TCP/IP Network Configuration
            1. Disabling, Enabling, and Configuring TCP/IP
            2. Using Static TCP/IP Network Configurations
            3. Using Dynamic TCP/IP Network Configurations
          5. Configuring Security for Native Client Configurations
          6. Configuring the Native Client Protocol Order
          7. Configuring the Shared Memory Native Client Configuration
          8. Configuring the Named Pipes Native Client Configuration
          9. Configuring the TCP/IP Native Client Configuration
        3. Understanding the Services Configuration
        4. Configuring SQL Server Services
          1. Managing Service State and Start Mode
          2. Setting the Startup Service Account
          3. Setting Up File Streaming
            1. Working with File-Stream Data
            2. Enabling and Configuring File Streaming
          4. Configuring Service Dump Directories, Error Reporting, and Customer Feedback Reporting
    4. II. Microsoft SQL Server 2012 Management and Security
      1. 3. Implementing Policy-Based Management
        1. Introducing Policy-Based Management
        2. Working with Policy-Based Management
        3. Configuring Central Management Servers
          1. Registering Central Management Servers
          2. Registering Subordinate Servers and Groups
          3. Moving Subordinate Servers and Server Groups
          4. Deleting Subordinate Servers and Server Groups
          5. Executing Statements Against Multiple Servers
        4. Managing Policies Throughout the Enterprise
          1. Importing and Exporting Policies
          2. Configuring and Managing Policy Facets
          3. Creating and Managing Policy Conditions
            1. Defining Conditions Using Properties and Standard Expressions
            2. Creating and Modifying Conditions
            3. Defining Complex Expressions
          4. Creating and Managing Policies
          5. Managing Policy Categories and Mandating Policies
          6. Evaluating Policies
          7. Troubleshooting Policies
      2. 4. Configuring and Tuning Your SQL Servers
        1. Accessing SQL Server Configuration Data
        2. Techniques for Managing SQL Server Configuration Options
          1. Setting Configuration Options
          2. Working with SET Options
          3. Working with Server Options
          4. Working with Database Options
          5. Managing Database Compatibility
        3. Configuring SQL Server with Stored Procedures
          1. Using SQL Server Management Studio for Queries
          2. Executing Queries and Changing Settings
          3. Checking and Setting Configuration Parameters
          4. Changing Settings with ALTER DATABASE
      3. 5. Tuning and Linking Your SQL Servers
        1. SQL Server Management Studio Essentials
          1. Managing the Configuration with SQL Server Management Studio
          2. Determining System and Server Information
          3. Configuring Utility Control Points
            1. Creating a Control Point
            2. Enrolling an Instance
            3. Deploying Data-Tier Applications
            4. Performing Utility Administration
        2. Working with Linked Servers and Distributed Data
          1. Using Distributed Queries
          2. Using Distributed Transactions
          3. Running the Distributed Transaction Coordinator Service
        3. Configuring Authentication and Auditing
          1. Setting the Authentication Mode
          2. Setting the Auditing Level
          3. Enabling or Disabling C2 Audit Logging
          4. Enabling or Disabling Common Criteria Compliance
        4. Tuning Memory Usage
          1. Working with Dynamically Configured Memory
          2. Using Fixed Memory
          3. Enabling AWE Memory Support
          4. Optimizing Memory for Indexing
          5. Allocating Memory for Queries
        5. Configuring Processors and Parallel Processing
          1. Optimizing CPU Usage
          2. Setting Parallel Processing
        6. Configuring Threading, Priority, and Fibers
        7. Configuring User and Remote Connections
          1. Setting Maximum User Connections
          2. Setting Default Connection Options
          3. Configuring Remote Server Connections
        8. Managing Server Settings
          1. Enabling or Disabling Contained Database Support
          2. Enabling or Disabling File Streaming Support
          3. Setting the Default Language for SQL Server
          4. Allowing and Disallowing Nested Triggers
          5. Controlling Query Execution
          6. Configuring Year 2000 Support
        9. Managing Database Settings
          1. Setting the Index Fill
          2. Configuring Backup and Restore Time-Out Options
          3. Configuring Backup and Restore Retention Options
          4. Flushing the Cache with Checkpoints
          5. Compressing the Backup Media
        10. Managing Linked Servers
          1. Adding Linked Servers
          2. Configuring Security for Linked Servers
          3. Setting Server Options for Remote and Linked Servers
          4. Deleting Linked Servers
        11. Troubleshooting Configuration Problems
          1. Recovering from a Bad Configuration
          2. Changing Collation and Rebuilding the master Database
      4. 6. Database Administration Essentials
        1. Database Files and Logs
        2. Database Administration Basics
          1. Viewing Database Information in SQL Server Management Studio
          2. Viewing Database Information Using T-SQL
          3. Checking System and Sample Databases
          4. Examining Database Objects
        3. Creating Databases
          1. Creating Databases in SQL Server Management Studio
          2. Creating Databases Using T-SQL
        4. Altering Databases and Their Options
          1. Setting Database Options in SQL Server Management Studio
          2. Modifying Databases Using ALTER DATABASE
          3. Configuring Automatic Options
          4. Controlling ANSI Compliance at the Database Level
          5. Configuring Parameterization
          6. Configuring Cursor Options
          7. Controlling User Access and Database State
          8. Setting Online, Offline, or Emergency Mode
          9. Managing Cross-Database Chaining and External Access Options
          10. Configuring Recovery, Logging, and Disk I/O Error Checking Options
          11. Viewing, Changing, and Overriding Database Options
        5. Managing Database and Log Size
          1. Configuring SQL Server to Manage File Size Automatically
          2. Expanding Databases and Logs Manually
          3. Compressing and Shrinking a Database Manually
        6. Manipulating Databases
          1. Renaming a Database
          2. Dropping and Deleting a Database
          3. Attaching and Detaching Databases
            1. Detaching a Database
            2. Attaching a Database with Multiple Files
            3. Attaching a Database with Only Data Files
        7. Tips and Techniques
          1. Copying and Moving Databases
          2. Moving Databases
          3. Moving and Resizing tempdb
          4. Creating Secondary Data and Log Files
          5. Preventing Transaction Log Errors
          6. Preventing a Filegroup Is Full Error
          7. Creating a New Database Template
          8. Configuring Database Encryption
      5. 7. Implementing SQL Server 2012 Security
        1. Overview of SQL Server 2012 Security
          1. Working with Security Principals and Securables
          2. Understanding Permissions of Securables
          3. Examining Permissions Granted to Securables
            1. Examining Built-in Permissions
            2. Examining Effective Permissions
        2. SQL Server 2012 Authentication Modes
          1. Windows Authentication
          2. Mixed Security and SQL Server Logins
          3. Authentication at the Database Level
        3. Special-Purpose Logins and Users
          1. Working with the Administrators Group
          2. Working with the Administrator User Account
          3. Working with the sa Login
          4. Working with the NT SERVICE and SYSTEM Logins
          5. Working with the Guest User
          6. Working with the dbo User
          7. Working with the sys and INFORMATION_SCHEMA Users
        4. Permissions
          1. Object Permissions
          2. Statement Permissions
          3. Implicit Permissions
        5. Roles
          1. Server Roles
          2. Database Roles
        6. Managing Server Logins
          1. Viewing and Editing Existing Logins
          2. Creating Logins
          3. Editing Logins with T-SQL
          4. Granting or Denying Server Access
          5. Enabling, Disabling, and Unlocking Logins
          6. Removing Logins
          7. Changing Passwords
        7. Configuring Server Roles
          1. Assigning Roles by Login
          2. Assigning Roles to Multiple Logins
          3. Revoking Access Rights and Roles by Server Login
        8. Controlling Database Access and Administration
          1. Viewing and Editing Existing Database Users
          2. Creating Database Users
          3. Editing Users with T-SQL
          4. Removing Users
          5. Assigning Access and Roles by Login
          6. Assigning Roles for Multiple Logins
          7. Creating Standard Database Roles
          8. Creating Application Database Roles
          9. Removing Role Memberships for Database Users
          10. Deleting User-Defined Roles
          11. T-SQL Commands for Managing Access and Roles
        9. Managing Database Permissions
          1. Assigning Database Permissions for Statements
          2. Object Permissions by Login
          3. Object Permissions for Multiple Logins
    5. III. Microsoft SQL Server 2012 Data Management
      1. 8. Manipulating Schemas, Tables, and Views
        1. Working with Schemas
          1. Creating Schemas
          2. Modifying Schemas
          3. Moving Objects to a New Schema
          4. Dropping Schemas
        2. Getting Started with Tables
        3. Table Essentials
          1. Understanding Data Pages
          2. Understanding Extents
          3. Understanding Table Partitions
        4. Working with Tables
          1. Creating Tables
          2. Modifying Existing Tables
          3. Viewing Table Row and Size Information
          4. Displaying Table Properties and Permissions
          5. Displaying Current Values in Tables
          6. Copying Tables
          7. Renaming and Deleting Tables
          8. Adding and Removing Columns in a Table
            1. Adding Columns
            2. Modifying Columns
            3. Removing Columns
          9. Scripting Tables
        5. Managing Table Values
          1. Using Native Data Types
          2. Using Fixed-Length, Variable-Length, and Max-Length Fields
          3. Using User-Defined Data Types
            1. Creating User-Defined Data Types
            2. Managing User-Defined Data Types
          4. Allowing and Disallowing Nulls
          5. Using Default Values
          6. Using Sparse Columns
          7. Using Identities and GUIDs
          8. Using User-Defined Table Types
            1. Understanding User-Defined Table Types
            2. Creating User-Defined Table Types
            3. Managing User-Defined Table Types
        6. Using Views
          1. Working with Views
          2. Creating Views
          3. Modifying Views
          4. Using Updatable Views
          5. Managing Views
      2. 9. Using Indexes, Constraints, and Partitions
        1. Creating and Managing Indexes
          1. Understanding Traditional Indexes
          2. Using Clustered Indexes
          3. Using Nonclustered Indexes
          4. Using XML Indexes
          5. Using Filtered Indexes
          6. Determining Which Columns Should Be Indexed
          7. Indexing Computed Columns and Views
          8. Viewing Index Properties
          9. Creating Indexes
          10. Managing Indexes
          11. Using the Database Engine Tuning Advisor
        2. Working with Columnstore Indexes
          1. Using Columnstore Indexes
          2. Viewing Columnstore Index Properties
          3. Creating and Managing Columnstore Indexes
        3. Column Constraints and Rules
          1. Using Constraints
            1. Setting Unique Constraints
            2. Designating Primary Key Constraints
            3. Using Foreign Key Constraints
            4. Using Check Constraints
            5. Using Not Null Constraints
          2. Using Rules
        4. Creating Partitioned Tables and Indexes
          1. Creating Partition Functions
          2. Creating Partition Schemes
          3. Creating Partitions
          4. Viewing and Managing Partitions
        5. Compressing Tables, Indexes, and Partitions
          1. Using Row and Page Compression
          2. Setting or Changing Compression Settings
    6. IV. Microsoft SQL Server 2012 Optimization, Maintenance, and Recovery
      1. 10. Automating and Maintaining SQL Server 2012
        1. Database Automation and Maintenance
        2. Using Database Mail
          1. Performing the Initial Database Mail Configuration
          2. Managing Database Mail Profiles and Accounts
          3. Viewing or Changing Database Mail System Parameters
        3. Using SQL Server Agent
          1. Accessing Alerts, Operators, and Jobs
          2. Configuring the SQL Server Agent Service
          3. Setting the SQL Server Agent Mail Profile
          4. Using SQL Server Agent to Restart Services Automatically
        4. Managing Alerts
          1. Using Default Alerts
          2. Creating Error Message Alerts
          3. Handling Alert Responses
          4. Deleting, Enabling, and Disabling Alerts
        5. Managing Operators
          1. Registering Operators
          2. Deleting and Disabling Notification for Operators
          3. Configuring a Fail-Safe Operator
        6. Scheduling Jobs
          1. Creating Jobs
          2. Assigning or Changing Job Definitions
          3. Setting Steps to Execute
          4. Configuring Job Schedules
          5. Handling Job Alerts
          6. Handling Notification Messages
          7. Managing Existing Jobs
          8. Managing Job Categories
            1. Working with Job Categories
            2. Creating Job Categories
            3. Updating Job Categories
        7. Automating Routine Server-to-Server Administration Tasks
          1. Copying User Accounts, Tables, Views, and Other Objects from One Database to Another
          2. Copying Alerts, Operators, and Scheduled Jobs from One Server to Another
        8. Multiserver Administration
          1. Event Forwarding
          2. Multiserver Job Scheduling
            1. Multiserver Scheduling Requirements
            2. Configuring Master Servers
            3. Configuring Target Servers
        9. Database Maintenance
          1. Database Maintenance Checklists
            1. Daily
            2. Weekly
            3. Monthly
            4. As Needed
          2. Using Maintenance Plans
            1. Creating Maintenance Plans
            2. Checking Maintenance Reports and History
            3. Viewing, Editing, Running, and Deleting Maintenance Plans
          3. Checking and Maintaining Database Integrity
            1. Using DBCC CHECKDB
            2. Using DBCC CHECKTABLE
            3. Using DBCC CHECKALLOC
            4. Using DBCC CHECKCATALOG
            5. Using DBCC CHECKIDENT
            6. Using DBCC CHECKFILEGROUP
            7. Using DBCC UPDATEUSAGE
      2. 11. SQL Server 2012 Backup and Recovery
        1. Creating a Backup and Recovery Plan
          1. Initial Backup and Recovery Planning
          2. Planning for Mirroring and Mirrored Database Backups
          3. Planning for Backups of Replicated Databases
          4. Planning for Backups of Very Large Databases
          5. Planning for Backup Compression
        2. Selecting Backup Devices and Media
        3. Using Backup Strategies
        4. Creating a Backup Device
        5. Performing Backups
          1. Creating Backups in SQL Server Management Studio
            1. Creating a New Backup Set
            2. Adding to an Existing Backup Set
          2. Using Striped Backups with Multiple Devices
            1. Creating a New Media Set
            2. Adding to an Existing Media Set
          3. Using T-SQL Backup
          4. Performing Transaction Log Backups
            1. Options and Commands That Invalidate Log Sequences
            2. Log Truncation Options
        6. Restoring a Database
          1. Database Corruption and Problem Resolution
          2. Restoring a Database from a Normal Backup
          3. Restoring Files and Filegroups
          4. Restoring a Database to a Different Location
          5. Recovering Missing Data
          6. Creating Standby Servers
            1. Creating a Cold Standby
            2. Creating a Warm Standby
          7. Using T-SQL Restore Commands
        7. Restoring the master Database
      3. 12. SQL Server 2012 Profiling and Monitoring
        1. Monitoring Server Performance and Activity
          1. Reasons to Monitor SQL Server
          2. Getting Ready to Monitor
          3. Monitoring Tools and Resources
        2. Working with Replication Monitor
          1. Starting and Using Replication Monitor
          2. Adding Publishers and Publisher Groups
        3. Working with the Event Logs
          1. Examining the Application Log
          2. Examining the SQL Server Event Logs
          3. Examining the SQL Server Agent Event Logs
        4. Monitoring SQL Server Performance
          1. Choosing Counters to Monitor
          2. Performance Logging
            1. Creating and Managing Data Collector Sets
            2. Collecting Performance Counter Data
            3. Collecting Performance Trace Data
            4. Collecting Configuration Data
          3. Viewing Data Collector Reports
          4. Configuring Performance Counter Alerts
        5. Configuring a Management Data Warehouse
          1. Understanding Management Data Warehouses
          2. Creating the Management Data Warehouse
          3. Setting Up Data Collection
          4. Managing Collection and Generating Reports
        6. Solving Performance Problems with Profiler
          1. Using Profiler
          2. Creating New Traces
          3. Working with Traces
          4. Saving a Trace
          5. Replaying a Trace
            1. Requirements for Replaying Traces
            2. Replaying Traces on a Different Server
            3. Replaying and Analyzing a Trace
    7. A. About the Author
      1. About the Technical Reviewer
    8. Index
    9. About the Author
    10. Copyright

    Product information

    • Title: Microsoft® SQL Server® 2012: Pocket Consultant
    • Author(s): William R. Stanek
    • Release date: February 2012
    • Publisher(s): Microsoft Press
    • ISBN: 9780735670273