Reducing Planned Downtime - SQL Server 2008

As mentioned at the beginning of this chapter, we are actually trying to minimize two general flavors of downtime: planned and unplanned. Combinations of the previous technologies can be used to address, and possibly eliminate, unplanned downtime. However, it has generally been accepted that some amount of planned downtime is required. However, increasingly, we are dealing with organizations that have zero tolerance for any type of downtime. Financial organizations are following the sun when it comes to data entry of things like new policies. Medical providers are increasingly leveraging SQL Server for their most mission-critical emergency room systems. This 24*7 business demand is eliminating the typical nightly cycle.

The SQL Server product team is helping us address these demands by allowing many operations to be performed while our databases are online and accessible by end users. These include adding a CPU or memory to a running system, as well as performing online index operations and partitioning tables and indexes.

Adding a CPU to a Running System

The ability to dynamically add CPUs (hot-add) to a SQL Server instance, without restarting, is a new feature available in SQL Server 2008. The following are the requirements for this feature:

  • The underlying server hardware must support the ability to dynamically add a CPU without restarting, unless using virtualization or hardware partitioning.
  • The operating system must be Windows Server 2008 Enterprise Edition or Windows Server 2008 Datacenter Edition. The operating system must also be a 64-bit edition.
  • SQL Server 2008 Enterprise Edition must be used.

SQL Server will not automatically recognize or use a newly added CPU(s). You must use the RECONFIGURE command to enable the new CPU.

Adding Memory to a Running System

Similar to dynamically adding a CPU, you can hot-add memory to a SQL Server and use it, without needing to restart the SQL Server instance. This feature has the following requirements:

  • It is available only in the 64-bit SQL Server Enterprise Edition, or the 32-bit version with Address Windowing Extensions enabled.
  • The operating system must be Windows Server 2003 or 2008 Enterprise or Datacenter Edition.
  • The underlying physical hardware must support the ability to dynamically add memory, unless using virtualization or hardware partitioning.

Performing Online Index Operations

SQL Server provides the ability to create, rebuild, and drop indexes online. The idea of online index operations is that users of the database are not locked from the table or index undergoing the operations. This is very different functionality than we were used to with online index operations in editions prior to SQL Server 2005, which required exclusive locks while the operation was being performed, thus ensuring that users could not change or access the data in any way.

The following is an example using the online option:

USE AdventureWorks;
ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
ON Sales.SalesOrderDetail

Keep in mind that online index operations make extensive use of tempdb, and therefore you should ensure that tempdb is configured to Microsoft’s recommended best practices.

Partitioning Tables and Indexes

Table and index partitioning provide the ability to create, modify, and delete subsets of data without affecting the integrity of the entire data collection. Partitioning is often thought of as a way to increase performance and scalability when working with large amounts of data. Although performance is typically increased, partitioning was originally designed to improve manageability of large tables and indexes.

With data partitioned into many smaller subsets, operations such as index rebuilds and individual partition backups execute much more quickly than they would against a nonpartitioned table or index. In fact, the combination of index partitioning and online index operations provides a very powerful solution for performing maintenance operations without any planned downtime.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics