Professional Microsoft SQL Server 2012 Administration E-Book

Adam Jorgensen

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1499

Veröffentlichungsjahr: 2012

Beschreibung

An essential how-to guide for experienced DBAs on the most significant product release since 2005!

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

• Serves as a must-have how-to guide on the new features of the 2012 release of Microsoft SQL Server
• Walks you through ways to manage and monitor SQL Server and automate administration
• Guides you through SQL Azure for cloud computing, data replication, and data warehouse performance improvements
• Addresses client-connectivity enhancements, new features that ensure high availability of mission-critical applications, and tighter integration with Excel and SharePoint for data analysis and reporting
• Highlights major changes to the suite of SQL Server BI tools, including integration, reporting, and analysis services
• Includes real-world case examples and best practices from Microsoft insiders

If you're a database administrator eager to get started with the newest version of SQL Server, then this book is essential reading.

Leseprobe

CONTENTS

Chapter 1: SQL Server 2012 Architecture

SQL Server 2012 Ecosystem

New Important Features in 2012

SQL Server Architecture

Editions of SQL Server

Summary

Chapter 2: Installing SQL Server 2012 Best Practices

Planning the System

Installing SQL Server

Installing Analysis Services

Installing PowerPivot for SharePoint

Burning in the System

Post-Install Configuration

Uninstalling SQL Server

Troubleshooting a Failed Install

Summary

Chapter 3: Upgrading SQL Server 2012 Best Practices

Why Upgrade to SQL Server 2012?

Upgrading to SQL Server 2012

Pre-Upgrade Steps and Tools

Backward Compatibility

SQL Server Component Considerations

Post-Upgrade Checks

Summary

Chapter 4: Managing and Troubleshooting the Database Engine

Configuration and Administration Tools

Troubleshooting Tools

Management Studio

Monitoring Processes in T-SQL

Multiserver Management

Trace Flags

Getting Help from Support

Summary

Chapter 5: Automating SQL Server

Maintenance Plans

Automating SQL Server with SQL Server Agent

SQL Server Agent Security

Configuring SQL Server Agent

Database Mail

Multiserver Administration

Summary

Chapter 6: Service Broker in SQL Server 2012

Asynchronous Messaging

Configuring SQL Server Service Broker

Using SQL Server Service Broker

Summary

Chapter 7: SQL Server CLR Integration

Introduction to the CLR

Creating CLR Assemblies

Securing CLR

Performance Monitoring

Summary

Chapter 8: Securing the Database Instance

Authentication Types

Authorizing Securables

Row Level Security

Summary

Chapter 9: Change Management

Creating Solutions and Projects

Policy-Based Management

DDL Trigger Syntax

Trigger Views

Scripting Overview

Creating Change Scripts

Data-Tier Applications

Summary

Chapter 10: Configuring the Server for Optimal Performance

What Every DBA Needs to Know About Performance

What the Developer DBA Needs to Know About Performance

What the Production DBA Needs to Know About Performance

CPU

Memory

I/O

Summary

Chapter 11: Optimizing SQL Server 2012

Application Optimization

The Silent Killer: I/O Problems

Table and Index Partitioning

Data Compression

CPU Considerations

Memory Considerations and Enhancements

Resource Governor

Summary

Chapter 12: Monitoring Your SQL Server

The Goal of Monitoring

Choosing the Appropriate Monitoring Tools

Performance Monitor

Monitoring Events

Monitoring with Dynamic Management Views and Functions

Monitoring Logs

Management Data Warehouse

SQL Server Standard Reports

System Center Management Pack

SQL Server Best Practice Analyzer

System Center Advisor

Summary

Chapter 13: Performance Tuning T-SQL

Physical Query Processing Part One: Compilation and Recompilation

Physical Query Processing Part Two: Execution

Summary

Chapter 14: Indexing Your Database

Noteworthy Index-Related Features in SQL Server

Partitioned Tables and Indexes

Index Maintenance

Improving Query Performance with Indexes

Database Tuning Advisor

Too Many Indexes?

Summary

Chapter 15: Replication

Replication Overview

Replication Models

Implementing Replication

Peer-to-Peer Replication

Scripting Replication

Monitoring Replication

Summary

Chapter 16: Clustering SQL Server 2012

Clustering and Your Organization

Clustering: The Big Picture

Upgrading SQL Server Clustering

Getting Prepared for Clustering

Clustering Windows Server 2008

Clustering Microsoft Distributed Transaction Coordinator

Clustering SQL Server 2012

Managing and Monitoring the Cluster

Troubleshooting Cluster Problems

Summary

Chapter 17: Backup and Recovery

Types of Failure

Making Plans

Overview of Backup and Restore

Preparing for Recovery

Developing and Executing a Backup Plan

Managing Backups

Backup and Restore Performance

Performing Recovery

Archiving Data

Summary

Chapter 18: SQL Server 2012 LOG Shipping

Log Shipping Deployment Scenarios

Log-Shipping Architecture

Log Shipping Process

System Requirements

Deploying Log Shipping

Monitoring and Troubleshooting

Managing Changing Roles

Database Backup Plan

Integrating Log Shipping with Other High-Availability Solutions

Removing Log Shipping

Log-Shipping Performance

Upgrading to SQL Server 2012 Log Shipping

Summary

Chapter 19: Database Mirroring

Overview of Database Mirroring

Operating Modes of Database Mirroring

Database Mirroring In Action

Database Mirroring and SQL Server 2012 Editions

Database Mirroring Catalog Views

Database Mirroring Role Change

Database Availability Scenarios

Monitoring Database Mirroring

Troubleshooting Database Mirroring

Preparing the Mirror Server for Failover

Mirroring Multiple Databases

Database Mirroring and Other High-Availability Solutions

Mirroring Event Listener Setup

Database Snapshots

Summary

Chapter 20: Integration Services Administration and Performance Tuning

A Tour of Integration Services

Administration of the Integration Services Service

Administration of Integration Services Packages in Package Deployment Model

Administration of Integration Services Packages in Project Deployment Model

Execution and Scheduling

Applying Security to Integration Services

Summary

Chapter 21: Analysis Services Administration and Performance Tuning

Tour of Analysis Services

Administering Analysis Services Server

Administering Analysis Services Databases

Analysis Services Performance Monitoring and Tuning

Management of Analysis Services MOLAP Model Storage

Applying Security to Analysis Services in the MOLAP Model

Summary

Chapter 22: SQL Server Reporting Services Administration

SQL Server Reporting Services Configuration Manager

Report Server Properties

The Report Execution Log

Report Builder

Report Manager

Summary

Chapter 23: SQL Server 2012 Sharepoint 2010 Integration

Components of Integration

Data Refresh

Chapter 24: SQL Azure Administration and Configuration

Introduction to SQL Azure

SQL Azure Architecture

Configuring SQL Azure

Administering SQL Azure

Working with SQL Azure

What’s Missing in SQL Azure

Summary

Chapter 25: Alwayson Availability Groups

Architecture

Availability Group Example

Active Secondary for Secondary Read-Only

Backup on the Secondary Replica

AlwaysOn Group Dashboard

Monitoring and Troubleshooting

Summary

Introduction

Advertisement

Chapter 1

SQL Server 2012 Architecture

WHAT’S IN THIS CHAPTER

New Important Features in SQL Server 2012How New Features Relate to Data Professionals Based on Their RoleSQL Server Architecture OverviewEditions of SQL Server and How They Affect the Data Professional

SQL Server 2012 offers a fresh look at how organizations and their developers, information workers, and executives use and integrate data. A tremendous number of new features and improvements focus on extending SQL Server more into SharePoint, improving self-service options, and increasing data visualization, development, monitoring and exploration capabilities. This chapter is not a deep dive into the architecture but provides enough information to give you an understanding of how SQL Server operates.

SQL SERVER 2012 ECOSYSTEM

This thing called SQL Server has become quite large over the past few releases. This first section provides a review of the overall SQL Server ecosystem, which is now referred to as less of a product and more of an ecosystem, because there are so many interactions with other products and features that drive increased performance, scale, and usability. Following are three major areas of focus for the release of SQL Server 2012:

Performance: Features such as improved core support, columnstore indexes, compression enhancements, and Always On make this the most powerful, available release of SQL Server.Self Service: With new data exploration tools such as Power View, improvements in SQL Azure Business Intelligence (BI,), data quality and master data offerings, and PowerPivot for SharePoint enable users to be closer to the data at all times and to seek and deliver intelligence more rapidly than ever.Integration and collaboration: New integrations for reporting services, PowerPivot, and claims authentication in SharePoint 2010 provide a strong foundation for the significant focus on self-service in this release. The new BI semantic model approach extends into the cloud as well with reporting services now in SQL Azure and more features promised to come.

NEW IMPORTANT FEATURES IN 2012

There are a number of new things that you will be excited about, depending on your role and how you use SQL Server. This section touches on the features you should be checking out and getting your hands on. Many of these features are quick to get up and running, which is exciting for those readers who want to begin delivering impact right away.

Production DBA

Production DBAs are a company’s insurance policy that the production database won’t go down. If the database does go down, the company cashes in its insurance policy in exchange for a recovered database. The Production DBA also ensures that the server performs optimally and promotes database changes from development to quality assurance (QA) to production. New features include the following:

AlwaysOn: Availability functionality including availability groups and the ability to file over databases in groups that mimic applications. This includes new readable secondary servers, a big enhancement.FileTable: Additional file-based data storageExtended Events: A new functionality built into SQL Server 2012 that provides lightweight and extensive tracing capabilityImproved functionality and stability in SQL Server Management Studio (now in Visual Studio 2010 shell)Distributed replay capabilitiesImproved debugging functionality including expression support and breakpoint validation.Columnstore indexes for optimizing large data volumesImproved statistics algorithm for very large databasesImproved compression and partitioning capabilities

Development DBA

Since the release of SQL Server 2000, there has been a trend away from full-time Production DBAs, and the role has merged with that of the Development DBA. The trend may have slowed, though, with laws such as Sarbanes-Oxley, which require a separation of power between the person developing the change and the person implementing the change. In a large organization, a Production DBA may fall into the operations department, which consists of the network of administrators and Windows-support administrators. In other instances, a Production DBA may be placed in a development group. This removes the separation of power that is sometimes needed for regulatory reasons.

Development DBAs play a traditional role in an organization. They wear more of a developer’s hat and are the development staff’s database experts and representatives. This administrator ensures that all stored procedures are optimally written and that the database is modeled correctly, both physically and logically. The development DBA also may be the person who writes the migration processes to upgrade the database from one release to the next. The Development DBA typically does not receive calls at 2:00 A.M like the Production DBA might for failed backups or similar problems. Things development DBAs should be excited about in this new release include the following:

New TSQL and spatial functionalitySQL Server data tools: A new TSQL development environment integrated with Visual StudioNew DAX expression language that provides Excel-like usability with the power of multidimensional capabilitiesNew tabular model for Analysis Services: Provides in-memory OLAP capabilities in a quick time to value format

The Development DBA typically reports to the development group and receives requests from a business analyst or another developer. In a traditional sense, Development DBAs should never have modification access to a production database. They should, however, have read-only access to the production database to debug in a time of escalation.

Business Intelligence DBA and Developer

The Business Intelligence (BI) DBA is a new role that has evolved due to the increased capabilities of SQL Server. In SQL Server 2012, BI grew to be an incredibly important feature set that many businesses could not live without. The BI DBA or developer is an expert at these features. This release is a treasure trove of new BI functionality including new enhancements to Reporting Services Integration, data exploration tools such as Power View, and a dramatic set of enhancements that make PowerPivot easier and more accessible than ever. Additionally, the new Tabular model in SSAS delivers the ability to create new PowerPivot-like “in memory” BI projects to SharePoint for mass user consumption.

Development BI DBAs specialize in the best practices, optimization, and use of the BI toolset. In a small organization, a Development BI DBA may create your SSIS packages to perform Extract Transform and Load (ETL) processes or reports for users. In a large organization, developers create the SSIS packages and SSRS reports. The Development BI DBA is consulted regarding the physical implementation of the SSIS packages and Analysis Services (SSAS) cubes. Development BI DBAs may be responsible for the following types of functions: