Whitepapers

Using Replication for High Availability and Disaster Recovery

Nicholas Dritsas — Tue, 22 Sep 2009 16:29:00 GMT

Writers: Manaphan Huntrakoon, Nicholas Dritsas

Technical Reviewers: Burzin Patel, Sanjay MishraPublished: August 2009 Summary: An essential part of IT mission is maintaining high availability and disaster recovery capability. This technical case study shows how a company can use SQL Server 2008 database replication to eliminate single points of failure in data centers, and to enable fast recovery from a possible disaster at its primary data center. These strategies and solutions will be of interest to database administrators, senior IT managers, project leads, and architects.Introduction: An international company has deployed a number of SQL Server instances spread out over different locations in Asia, the United States and Europe. The main problem they wanted to resolve was the high availability of data critical to their main application. In the event this data ever became unavailable, the financial repercussions would have been substantial. While the company had many ways and methods to alleviate this problem, they were unsure of which SQL Server high availability/disaster recovery technology would address their problem plus provide a strategic platform for the future. They engaged with Microsoft to help in the design of an architecture best fit for their needs, budget, expectations and skill set. In this document, we will be going through the steps and processes the project team went through to evaluate and decide on the best choices and options.
For more details, please look in this white paper: Using Replication for High Availability and Disaster Recovery in SQL Server 2008.

Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services

denny.lee — Sat, 19 Sep 2009 23:12:00 GMT

How Microsoft adCenter takes advantage of groundbreaking performance and scalability enhancements and innovative design tools available with Microsoft SQL Server 2008 Analysis Services

For more information, please refer to the whitepaper Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services.

Executive Summary

As an Internet platform for online advertising campaigns on Microsoft® Bing™, Microsoft adCenter offers customers a wide range of intelligence and analytics tools to target, reach, and engage the right audience with the right ad at the right time. A complex, multi-faceted Online Analytical Processing (OLAP) environment based on SQL Server® 2008 Analysis Services drives the adCenter intelligence solutions. This large-scale environment includes numerous data marts that consolidate information from various sources through SQL Server 2008 Integration Services. The adCenter data marts, based on SQL Server 2008 Enterprise Edition, maintain up to two terabytes of data in relational databases, equivalent to one month of Web activities at a current accumulation rate of approximately 60 to 70 gigabytes per day. The OLAP cubes built on top of these relational stores hold 13 months of data and are up to three terabytes in size.

Prior to SQL Server 2008, adCenter relied on Microsoft SQL Server 2005 as the technological foundation of its intelligence and analytics infrastructure. Among other benefits, SQL Server 2005 enabled adCenter to take full advantage of the 64-bit platform, the Unified Dimensional Model (UDM), and Multidimensional Expressions (MDX). However, it was difficult to sustain processing and query execution performance levels during peak usage as scalability became a limiting factor. At 500 users per cube, adCenter began to notice performance issues, challenging a continually growing user base. By upgrading to SQL Server 2008, adCenter was able to break through existing limitations without the need to replace server hardware or redesign infrastructure. The performance and scalability improvements are vital for keeping adCenter agile, competitive, and ready for further growth.

In comparison to its predecessor, SQL Server 2008 offers better hardware utilization, optimized cube processing, and substantially faster MDX query execution, and it promotes efficient UDM designs and code quality in analytics solutions. These are key factors that enable adCenter to increase its return on investment (ROI) in server hardware by allowing more users per server, while at the same time improving user experience and service level agreements (SLAs). For the most critical data marts, adCenter SLAs now state that 80 percent of the queries must return results in 3 seconds or less. Moreover, adCenter gained new options to track service level compliance and establish baselines for Analysis Services capacity planning. By exploiting the new monitoring capabilities in addition to Analysis Services traces, such as Management Data Warehouse (MDW) and new performance counters for Analysis Services-related system resources, adCenter can locate root causes of performance issues faster than with traces alone. This ultimately increases the productivity of OLAP developers when troubleshooting performance issues and optimizing analytics solutions.

The upgrade to SQL Server 2008 also allows adCenter to prepare for future OLAP infrastructure optimizations and relational data-warehousing technologies, such as SQL Server code-named Project "Madison." The adCenter Data Mart team has developed concrete plans to assess a load-balanced scale-out data warehouse appliance for the largest data marts and data warehouses. adCenter also plans to increase the size of the relational data stores to a massive volume of more than 150 terabytes as soon as Madison becomes available. This will allow adCenter to maintain between five and six months of Web activity data online, assuming an increase in data collection rates of one terabyte per day. For adCenter, the upgrade to SQL Server 2008 represents an important step toward the future.

The purpose of this technical white paper is to share Microsoft knowledge, experiences, and recommendations related to the architecture and design of SQL Server 2008 Analysis Services for high performance and scalability in a large-scale OLAP environment. This paper is not intended to serve as a procedural guide. Although many organizations have similar requirements, each enterprise environment also has unique needs, making it necessary to adapt the information discussed in this paper.

This white paper assumes that readers are IT professionals and technical decision makers, already familiar with Windows Server®, Active Directory®, and SQL Server. Specifically, knowledge about SQL Server 2008 Analysis Services, Integration Services, and Business Intelligence Development Studio is helpful. Detailed product information is available in the SQL Server 2008 TechCenter at http://technet.microsoft.com/en-us/sqlserver/default.aspx.

Note: For security reasons, the sample names of forests, domains, servers, databases, and other internal resources used in this paper do not represent real resource names used within Microsoft and are for illustration purposes only.

For more information, please refer to the whitepaper Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services.

Configure a Sharepoint Server Farm for minimal downtime during software updates

Lindsey.allen — Fri, 18 Sep 2009 21:32:00 GMT

This article describes how to update Office SharePoint Server farms with minimal downtime. By following the procedures in this article, you can update servers to add the latest service pack or cumulative update without incurring significant downtime for users. This article describes a step-by-step process for Office SharePoint Server farms that incorporate SQL Server database mirroring.

It often takes several teams or roles in an organization to configure, upgrade, and maintain a server farm environment. To implement this solution, you must confer with network engineers, SQL Server database administrators, and all affected SharePoint farm administrators.

This article is written for IT professionals who maintain server farms that are running Microsoft Office SharePoint Server 2007 with Service Pack 2 (SP2) .

The complete step by step instruction can be found here: http://technet.microsoft.com/en-us/library/ee514459.aspx

A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network

Lindsey.allen — Fri, 14 Aug 2009 00:06:00 GMT

Writer: Thomas H. Grohser (=tg=)Contributor: Lindsey AllenTechnical Reviewers: Sanjay Mishra, Lubor Kollar, Stuart Ozer, Thomas Kejser, Juergen Thomas, James Podgorski, Burzin Patel

Summary: Database sizes increase constantly, as do requirements for access and availability. At the same time, it is more important than ever to have a fast and reliable backup and recovery plan in place. This document discusses the challenges of designing a robust backup and restore solutions for very large databases (VLDBs). Using a real-world example, it demonstrates how to make the best use of the backup and restore features of SQL Server 2008 to help create a fast and reliable backup and restore plan for VLDBs over the network.

Backing up a 2-terabyte database onto a local hard drive and then restoring it is fast, but this simple process does not provide adequate protection against worst-case scenario. On the other hand, backing up the database over the network to another location provides protection against our worst-case scenario, but with a typical 1-gigabit-per-second (Gbps) connection, performance suffers. When we looked at this situation, as a baseline test, we backed up the 2-terabyte database over a 1-Gbps network link to another data center 10 miles away. This took more than 24 hours, which was far from acceptable. We needed to design a solution that would remove the bottleneck, enabling us to complete the backup within that time-to-restore window specified in our SLA.

Assembling all the small pieces of the backup puzzle and running endless tests, we were able to reduce the backup of the 2-terabyte database to 36 minutes. The solution, which we termed “multistreamed backups over the network”, used eight 1-Gbps network connections. Jumbo frames were configured for each network card, and each network card was then aggregated to layer 2 backup switches to one 10GE (10Gbit Ethernet) line that ran to the second site. Backup now took 2 hours and 15 minutes. Enabling SQL Server 2008 backup compression finally brought the time down to 36 minutes.

For details please refer to the whitepaper:

http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Technical Case Study-Backup VLDB Over Network_Final.docx

High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study

SanjayMishra — Tue, 04 Aug 2009 20:59:00 GMT

Writer: David P. Smith (ServiceU Corporation)

Contributors: Ron Talmage (Solid Quality Mentors); Sanjay Mishra, Prem Mehra

Technical Reviewers: James Podgorski, Mike Weiner, Peter Carlin

Published: August 2009

ServiceU Corporation, based in Memphis, Tennessee, is a leading provider of online and on-demand event management software. Our software services are used by churches, schools, universities, theaters, and businesses to manage events such as concerts and conferences as well as online payments. We have customers in all 50 states of the United States and in 15 countries worldwide.

Our software services are built and deployed using the Microsoft® Application Platform, including the Microsoft .NET connection software, the Microsoft SQL Server® 2008 database software, and the Windows Server® 2008 operating system. The Microsoft Application Platform helps us provide a seamless user experience and maximum availability of our applications to users. The applications use both the Software as a Service (SaaS) model and the Microsoft Software + Services architecture.

From a security standpoint, we maintain Payment Card Industry (PCI) Level 1 compliance to protect credit card holder and Automated Clearing House (ACH) information. (Details of our PCI Compliance are not covered in this case study.)

Achieving maximum availability and near immediate recovery from a disaster is essential for maintaining our revenue stream. We have worked hard to eliminate all single points of failure in our architecture, and we have developed procedures for patching servers, upgrading software, and implementing application changes that preserve high availability. Based on these efforts, we have achieved 99.99 percent uptime, including both planned and unplanned downtime.

This case study examines the decisions that we made and the procedures we employed to maintain maximum availability with minimal downtime. This information will be of interest to senior IT managers, project leads, architects, and database administrators (DBAs).

For more information, please refer to the whitepaper High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study.

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.