Whitepapers

SQL Server 2008 Failover Clustering

Mike Weiner — Wed, 08 Jul 2009 22:49:00 GMT

Writers: Mike Weiner, Paul Burpo, Max Verun, Joseph Sack, Justin Erickson

Contributors: Sanjay Mishra, Jason Wu, Uttam Parui

Technical Reviewers: Prem Mehra, James Podgorski, David Whitney, Richard Tkachuk, Sethu Kalavakur, Cindy Gross, Neal Graves, Farzan Ratistari, Ayad Shammout (Caregroup Healthcare Systems), David P. Smith (ServiceU Corporation)

Published: June 2009

Applies to: SQL Server 2008

Summary: This white paper complements the existing documentation on planning, implementation, and administration of a SQL Server 2008 failover cluster, which can be found in Microsoft SQL Server 2008 Books Online. There are links to relevant existing content throughout the paper, which is intended primarily for a technical audience. This white paper covers failover cluster architecture and concepts for Windows Server (2003 and 2008) and SQL Server 2008; installation of a SQL Server 2008 failover cluster; upgrades and updates to SQL Server 2008 failover clustering; and maintenance and administration of SQL Server 2008.

For further information please download the SQL Server 2008 Failover Clustering whitepaper.

Configure disaster recovery across SharePoint farms by using SQL Server log shipping

SanjayMishra — Fri, 05 Jun 2009 22:19:00 GMT

Contributors / Technical Reviewers: Doron Bar-Caspi, Lindsay Allen, Sanjay Mishra, Burzin Patel, Bill Baer, Cory Burns, Steve Peschka, JP Poissant

Published: May 2009

This article describes how to use Microsoft SQL Server 2005 or Microsoft SQL Server 2008 log shipping to create a disaster recovery farm in a geographically distributed data center for Microsoft Office SharePoint Server 2007 with Service Pack 2 (SP2). By using this configuration, you can provide a disaster recovery site that provides current search results when a failover occurs. The article assumes that you are familiar with the concepts and terms presented in Plan for availability (Office SharePoint Server).

It often takes many teams or roles in an organization to create and configure a secondary data center and farm. In order to configure and test the secondary environment, you need to confer with the administrators of the authentication providers, the SQL Server database administrators, and all affected SharePoint farm administrators. This article is intended primarily for SharePoint farm administrators to help them do the following:

Understand the requirements for creating log-shipped disaster recovery farms
Set up trial log-shipped environments
Communicate with the SQL Server database administrators who will configure log shipping for the production environments.

For more information, please refer to the whitepaper Configure disaster recovery across SharePoint farms by using SQL Server log shipping.

Data Compression: Strategy, Capacity Planning and Best Practices

SanjayMishra — Fri, 29 May 2009 16:39:00 GMT

Writer: Sanjay Mishra

Contributors: Marcel van der Holst, Peter Carlin, Sunil Agarwal

Technical Reviewers: Stuart Ozer, Lindsey Allen, Juergen Thomas, Thomas Kejser, Burzin Patel, Prem Mehra, Joseph Sack, Jimmy May, Cameron Gardiner, Mike Ruthruff, Glenn Berry (SQL Server MVP), Paul S Randal (SQLskills.com), David P Smith (ServiceU Corporation)

Published: May 2009

The data compression feature in the Microsoft® SQL Server® 2008 database software can help reduce the size of the database as well as improve the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress. This white paper provides guidance on the following:

How to decide which tables and indexes to compress
How to estimate the resources required to compress a table
How to reclaim space released by data compression
The performance impacts of data compression on typical workloads

For more information, please refer to the whitepaper Data Compression: Strategy, Capacity Planning and Best Practices.

Improving Microsoft CRM Performance and Securing Data with SQL Server 2008

Peter — Thu, 28 May 2009 18:22:00 GMT

Writers: Microsoft Corporation

Contributors: Amir Jafri, Jim Toland, Peter Scharlock

Technical Reviewers: Srini Acharya, Sunil Agarwal, Peter Carlin, Roger Gilchrist

Published: January 2009

Applies To: SQL Server 2008, MS CRM 4.0

Summary: Microsoft SQL Server 2008 contains many new features that can enhance the performance and security of a MS CRM 4.0 deployment. This paper describes a project, including findings and specific implementation guidence, which the MS CRM Engineering for Enterprise team in conjunction with SQLCAT recently completed.

This project evaluated how to maximize the performance and security of MS CRM 4.0 utilizing the following SQL Server 2008 features: Compression, Filtered Indexes, Sparse Columns, Transparent Data Encryption, and Data Encryptions.

For more information, please refer to the Improving Microsoft CRM Performance and Securing Data with SQL Server 2008 Whitepaper.

SQL Server 2008 with Siebel, PeopleSoft and JD Edwards Applications

wandahe — Tue, 19 May 2009 19:40:00 GMT

Published: March, 2009

Applies to: SQL Server 2008

Introduction: This white paper describes why Microsoft® SQL Server® is the optimal data platform to use with JD Edwards, PeopleSoft, and Siebel applications that run on the Microsoft® platform. The paper describes the new and enhanced features of SQL Server 2008 that enable Oracle application customers to reduce cost, optimize performance, improve availability and reliability, and simplify data management and security. It is assumed that the reader has a working knowledge of these Oracle applications and Microsoft SQL Server concepts and features.

For more information, please refer to the SQL Server 2008 with Siebel, PeopleSoft and JD Edwards Applications Whitepaper

SQL Server 2008 with Siebel CRM Applications

wandahe — Tue, 19 May 2009 19:32:00 GMT

Writers: Microsoft Corporation

Technical Reviewers: Anu Chawla, Wanda He, George Heynen, Peter Samson

Published: April 2009

Applies To: SQL Server 2008

Summary: Microsoft SQL Server 2008 offers best-of breed performance for Siebel. This paper describes the capabilities of SQL Server 2008, how to maximize database performance for Siebel, and how to resolve common issues encountered by customers.

For more information, please refer to the SQL Server 2008 with Siebel CRM Applications Whitepaper

Disk Partition Alignment Best Practices for SQL Server

denny.lee — Tue, 12 May 2009 03:44:00 GMT

SQL Server Techical Article

Published: May 2009
Authors: Jimmy May (MS IT Assessment, Consulting, & Engineering (ACE) | bio), Denny Lee
Contributors: Mike Ruthruff, Robert Smith, Bruce Worthington, Jeff Goldner, Mark Licata, Deborah Jones, Michael Thomassy, Michael Epprecht, Frank McBath, Joseph Sack, Matt Landers, Jason McKittrick, Linchi Shea, Juergen Thomas, Emily Wilson, John Otto, Brent Dowling
Technical Reviewers: Mike Ruthruff, Robert Smith, Bruce Worthington, Emily Wilson, Lindsey Allen, Stuart Ozer, Thomas Kejser, Kun Cheng, Nicholas Dritsas, Paul Mestemaker, Alexei Khalyako, Mike Anderson, Bong Kang

Disk partition alignment is a powerful tool for improving SQL Server performance. Configuring optimal disk performance is often viewed as much art as science. A best practice that is essential yet often overlooked is disk partition alignment. Windows Server 2008 attempts to align new partitions out-of-the-box, yet disk partition alignment remains a relevant technology for partitions created on prior versions of Windows.

This paper documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation; and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of preexisting partitions.

The following topics are also included: background information, implementation, vendor considerations, two essential correlations, valid starting partition offsets, and the simple protocol to align partitions, define file allocation unit size, and assign drive letters. It includes results from tests that show how partition alignment can affect performance for SQL Server 2008.

For more information, please refer to the Disk Partition Alignment Best Practices for SQL Server whitepaper.

You can also download a Microsoft Word version of this article.

BizTalk Server 2009 Hyper-V Guide

Lindsey.allen — Sat, 02 May 2009 03:23:10 GMT

The purpose of this guide is to provide practical guidance for using Microsoft BizTalk Server 2009 with Microsoft Windows Server 2008 Hyper-V. The emphasis is on BizTalk Server, but the performance evaluation methods and performance testing scenarios are useful for analyzing the performance of virtualized server applications in general. This guidance will be of interest to both the IT Pro and Developer communities.

To download a copy of this guide, go to http://go.microsoft.com/fwlink/?LinkId=149267.

Troubleshooting Performance Problems in SQL Server 2008

Cheng_kun — Tue, 14 Apr 2009 04:21:00 GMT

Writers: Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng, Burzin Patel

Technical Reviewers: Jerome Halmans, Fabricio Voznika, George Reynya

Published: March 2009

Applies to: SQL Server 2008

Summary: Sometimes a poorly designed database or a system that is improperly configured for the workload can cause the slowdowns in SQL Server. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective action. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, Performance Monitor, dynamic management views, and SQL Server Extended Events (Extended Events) and the data collector, which are new in SQL Server 2008.

For more information, please refer to http://msdn.microsoft.com/en-us/library/dd672789.aspx

Service Broker: Performance and Scalability Techniques

MichaelThomassy — Tue, 24 Mar 2009 15:16:00 GMT

Writer: Michael Thomassy

Contributors: Sanjay Mishra

Technical Reviewers: Lindsey Allen, Lubor Kollar, Mark Souza, Thomas Kejser, Burzin Patel

Published: March 2009

Service Broker provides support for building asynchronous messaging and queuing applications with the SQL Server Database Engine. This paper describes a large scale customer scenario and the techniques employed in scaling Service Broker to process tens of thousands of messages per second on one server.

Microsoft SQL Server Service Broker provides native support to the SQL Server Database Engine for asynchronous, transactional messaging. Finding Service Broker bottlenecks requires a similar approach to tuning any high-end OLTP database systems. This paper will describe the performance and scalability techniques applied to a real-world workload to increase overall system throughput. After you understand the workload and Service Broker system tables that are used, you’ll be able to find and remove the bottlenecks to scale Service Broker applications.

For more information, please refer to the Service Broker: Performance and Scalability Techniques whitepaper.

Enterprise Policy Management Framework with SQL Server 2008

SanjayMishra — Wed, 25 Feb 2009 18:37:00 GMT

Writers: Tom Davidson, Lara Rubbelke, Dmitri Tchikatilov

Contributor: Sanjay Mishra

Technical Reviewers: Lindsey Allen, Hongfei Guo, Prem Mehra, Joseph Sack, Jimmy May, Glenn Berry (SQL Server MVP), Michael Thomassy

The Enterprise Policy Management (EPM) Framework leverages and extends the new Microsoft SQL Server 2008 Policy-Based Management feature across an entire SQL Server enterprise, including down-level instances of SQL Server such as SQL Server 2000 and SQL Server 2005. In addition, the EPM Framework can be used to:

Automate the evaluation of policies against a defined set of SQL Server instances, including SQL Server 2000 and SQL Server 2005.
Centralize the policy evaluation history to a single source for enterprise policy reporting.
Define best practices for implementing policy evaluation in extremely large enterprise environments.

This document provides best practice guidance on the use of the EPM Framework as a means of collecting and reporting on policy compliance across an entire SQL Server enterprise.

For more information, please refer to the Enterprise Policy Management Framework with SQL Server 2008 whitepaper.

Implementing a SQL Server Fast Track Data Warehouse

SanjayMishra — Tue, 24 Feb 2009 03:55:00 GMT

Published: February 2009
Writers: Stuart Frost, Dave Salch, Paul ***, Karl Schendel, Mark Theissen, Jesse Fountain, Stuart Ozer, Sonya Marshall, John Hoang, Murshed Zaman, Rick Brown, Glenn Yoshimura, Allan Haywood, Dave Christensen, Scotty Moran

The purpose of this document is to define a repeatable architectural approach for implementing a scalable model for an symmetric multiprocessor (SMP)-based Microsoft SQL Server 2008 data warehouse. The end result of this process represents the recommended minimal SQL Server configuration, inclusive of all the software and hardware, required to achieve and maintain a baseline level of “out of box” scalable performance when deploying SQL Server data warehousing (SSDW) sequential data access workload scenarios versus traditional random I/O methods.

The target audience for this document consists of IT planners, architects, DBAs, CIOs, CTOs, and business intelligence (BI) users with an interest in options for their BI applications and in the factors that affect those options.

For more information, please refer to the Implementing a SQL Server Fast Track Data Warehouse whitepaper.

The Analysis Services 2008 Performance Guide

tkejser — Sun, 15 Feb 2009 16:49:00 GMT

SQL Server Techical Article

Published: October 2008
Writer: Richrd Tkachuk and Thomas Kejser
Technical Reviewers: T.K. Anand, Marius Dumitru, Greg Galloway, Siva Harinath, Denny Lee, Edward Melomed, Akshai Mirchandani, Mosha Pasumansky, Carl Rabeler, Elizabeth Vitt, Sedat Yogurtcuoglu, Anne Zorner

This white paper describes how application developers can apply query and processing performance-tuning techniques to their SQL Server 2008 Analysis Services OLAP solutions.

For more information, please refer to the Analysis Services 2008 Performance Guide whitepaper.

The Data Loading Performance Guide 2008

tkejser — Sun, 15 Feb 2009 16:38:00 GMT

SQL Server Technical Article

Published: January 2009
Writers: Thomas Kejser, Peter Carlin and Stuart Ozer
Contributors: Sunil Agarwal, Ted Lee, David Schwartz, Chris Less, Lindsey Allen, Hermann Daeubler, Juergen Thomas, Sanjay Mishra, Denny Lee, Peter Carlin, Lubor Kollar, David Schwartz, Ted Lee, Henk van der Valk (Unisys), Alexei Khalyako, and Marcel van der Holst,

This document described techniques for bulk loading large data sets into SQL Server. It covers both the available techniques as well as methodologies to performance tune and optimize the bulk loading process

For more information, please refer to the The Data Loading Performance Guide whitepaper.

Tuning the Performance of Change Data Capture in SQL Server 2008

SanjayMishra — Mon, 24 Nov 2008 19:50:00 GMT

SQL Server Best Practices Article

Published: November 2008
Writer: Steffen Krause
Contributors: Sanjay Mishra, Gopal Ashok, Greg Yvkoff, Rui Wang
Technical Reviewers: Burzin Patel, Denny Lee, Glenn Berry (SQL Server MVP), Joseph Sack, Lindsey Allen, Michael Redman, Mike Ruthruff, Paul S. Randal (SQLskills.com)

Change data capture is a new feature in SQL Server 2008 that provides an easy way to capture changes to data in a set of database tables so these changes can be transferred to a second system like a data warehouse. This document provides guidance on how to configure change data capture parameters to maximize data capture performance while minimizing the performance impact on the production workload. The scope of this document is limited to the capture of change data and the cleanup process. Querying the changed data is out of scope for this white paper.

For more information, please refer to the Tuning the Performance of Change Data Capture in SQL Server 2008 whitepaper.