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:
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:
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;
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.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.