An Introduction to Dynamic Management Views and Functions in SQL Server 2005

One my most favorite features of SQL Server 2005 is the introduction of Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs). They allow the DBA to take a peek into the internal workings of SQL Server at both the server and database levels. Using DMVs and DMFs, the DBA can better monitor the health of SQL Server, better tune SQL Server, and troubleshoot many kinds of problems. In many cases, they replace the need to use system tables and other obscure methods to find out what is happening inside SQL Server. And in many other cases, they provide new insights into SQL Server internals that have never been available before to DBAs.

While DMVs and DMFs provide a wealth of information, the information they provide is often esoteric or difficult to understand. Because of this, there is a fairly steep learning curve when using them. In addition, you must use Transact-SQL to SELECT the results you want, so you must have a basic understanding of how to write SELECT statements in order to take full advantage of them.

The purpose of this article is to introduce you to them at a high level; in later articles, I will drill down into how specific DMVs and DMFs can be used to help you performance tune your servers and databases.

The Basics

There are two different kinds of DMVs and DMFs:

• Server-scoped: These look at the state of an entire SQL Server instance.
• Database-scoped: These look at the state of a specific database.

All DMVs and DMFs exist in the master database and belong to the sys schema. They also follow the naming convention of dm_*, such as:

sys.dm_db_index_usage_stats

DMVs can be referred to in Transact-SQL using the DMVs two-part, three-part, or four-part name. DMFs have to be referred to using either their two-part or three-part names in code. Neither DMVs nor DMFs can be referred to with their one-part name. The two-part name example above is the most common way to refer to DMVs and DMFs.

To access a DMV or DMF, the user must have SELECT permission on the specific DMV or DMF they want to access and must have either VIEW SERVER STATE or VIEW DATABASE STATE permission, depending on if the DMV or DMF is server- or database-based. By default, members of the sysadmin group have these permissions.

To view the data provided by a DMV or DMF, you use the SELECT statement. Here's a simple example:

SELECT * FROM sys.dm_db_index_usage_stats

This produces results like the following:

Figure: This output from the sys.dm_db_index_usage_stats DMV has been greatly abbreviated to fit the available space.

As you can see just from this one example, results from a DMV or DMF can be very detailed, but also very difficult to interpret without a little, or a lot of help. To be able to fully understand the capabilities of DMVs and DMFs, you will need to read up on each one and experiment with them to see if the information they provide is beneficial to you. In some cases, you may have to write some very complex Transact-SQL to produce the results you need. Books Online has some examples of Transact-SQL code you can use for various DMVs and DMFs.

Classes of DMVs and DMFs

Because there are so many DMVs and DMFs available, Microsoft has grouped them into categories. They include:

• Common Language Runtime Related Dynamic Management Views
• I/O Related Dynamic Management Views and Functions
• Database Mirroring Related Dynamic Management Views
• Query Notifications Related Dynamic Management Views
• Database Related Dynamic Management Views
• Replication Related Dynamic Management Views
• Execution Related Dynamic Management Views and Functions
• Service Broker Related Dynamic Management Views
• Full-Text Search Related Dynamic Management Views
• SQL Server Operating System Related Dynamic Management Views
• Index Related Dynamic Management Views and Functions
• Transaction Related Dynamic Management Views and Functions

Each of the above categories has many different DMVs and DMFs. While we don't have time in this article to look at them all, let's take a quick look at those that fall in the Index Related Dynamic Management Views and Functions category, one of the most useful categories of DMVs and DMFs used in performance tuning. They include:

• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_missing_index_columns
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups

While the name of the DMV or DMF is somewhat useful when figuring out what information they provide, you will find yourself looking these up in Books Online, or just experimenting by trail and error, to find out what information they can provide you.

Summary

DMVs and DMFs, along with data provided by Management Studio, System Monitor, and Profiler, provide DBAs with a wealth of information they can use to troubleshoot and correct SQL Server performance problems, in addition to many other SQL Server-related problems. If you have not yet done so, take some time and look up the more interesting DMVs and DMFs in Books Online and give them a try.

Caching in ASP.NET (Part I)

The ability to store data in the main memory and then allow for retrieval of the same as and when they are requested is one of the major factors that can yield high performance and scalable applications. This results in a gain in the application’s overall throughput by serving the subsequent requests for data from the Cache rather than recreating them or retrieving them from the database or any other storage device that works much slower compared to the main memory. This is one of the most striking features in Microsoft’s ASP.NET compared to its earlier counterparts and has been enhanced further with the introduction of the new release of ASP.NET (ASP.NET 2.0). This is the first in the series of articles on Caching in ASP.NET and discusses Caching, its types, benefits and the new features of Caching in ASP.NET 2.0.

Caching types, Cache Dependencies and Cache Expirations

ASP.NET features several different types of caching namely Page Output Caching, Page Fragment Caching and Data Caching. In Page Output Caching, the entire page is cached in memory so all the subsequent requests for the same page are addressed from the cache itself. In Page Fragment Caching, a specific a portion of the page is cached and not the entire page. Page Output or Fragment Caching can be enabled or disabled at the Page, Application or even the Machine levels.
Data Caching allows us to cache frequently used data and then retrieve the same data from the cache as and when it is needed. We can also set dependencies so that the data in the cache gets refreshed whenever there is a change in the external data store. The external data store can be a file or even a database. Accordingly, there are two types to dependencies, namely, file based and Sql Server based. There are also differing cache expiration policies.

New Caching features in ASP.NET 2.0

ASP.NET 2.0 has introduced a lot of new features in Caching. This new version of ASP.NET includes an excellent feature called automatic database server cache invalidation which is in turn based on database triggered cache invalidation technique. This powerful and easy-to-use feature enables us to output cache database-driven page and partial page content within a site and then leave it to ASP.NET invalidate these cache entries automatically and refresh the content whenever the there are any changes in the database. This ensures that the data in the Cache is in sync with that of the database. Note that the Cache API in ASP.NET 1.x does not allow you to invalidate an item in the cache when the data in the database changes. It is possible to invalidate cached data based on pre-defined conditions that relate to any change in the data in an external data store on which the dependency has been set. The data gets deleted from the Cache with any change in the data in the external data storage. However, the cached data cannot be refreshed directly.

ASP.NET 2.0 also features numerous enhancements to the Cache API. The CacheDependency class of ASP.NET 1.x was sealed to prevent further inheritance. Thus preventing extension of this class to provide custom cache dependency features. ASP.NET 2.0 Cache API provides the ability to create custom cache dependency classes that are inherited from the CacheDependency class. I would discuss all these issues in details in the subsequent articles in this series of articles on Caching in ASP.NET applications.

Page Caching in ASP.NET

There are two types of Page caching, namely, Page Output Caching and Page Fragment Caching. Page Fragment Caching is also known Partial Page Caching.

Page Output Caching

According to MSDN, Page Output Caching is "the simplest form of caching, output caching simply keeps a copy of the HTML that was sent in response to a request in memory. Subsequent requests are then sent the cached output until the cache expires, resulting in potentially very large performance gains (depending on how much effort was required to create the original page output—sending cached output is always very fast and fairly constant)".

The ASP.NET Cache Engine is responsible for providing support for Caching in ASP.NET. But, how does it work? The output of the ASP.NET web pages is cached such that all subsequent requests for the same page are served from the cache instead of any other persistent storage medium. When we say that the output of a web page is cached, we imply that the web page output is stored in the cache memory. Whenever a new page request is made, the ASP.NET Cache Engine is activated. It checks whether there is a corresponding cache entry for this page. If one is found, it is known as a Cache hit, else, we say that a cache miss has occurred. If there is a cache hit, i.e., if the ASP.NET Cache engine finds a corresponding cache entry for this page, the page is rendered from the cache, otherwise, the page being requested is rendered dynamically. Page Output caching can be specified in either of the following two ways:

Declarative Approach
Programmatic Approach

Declarative Approach

The declarative specification here implies that Page Output Caching is specified at the design time by adding the OutputCache directive in the web page source.

The following is the complete syntax of page output caching directive in ASP.NET.

<%@ OutputCache Duration="no of seconds"
Location="Any | Client | Downstream | Server | None"
VaryByControl="control"
VaryByCustom="browser |customstring"
VaryByHeader="headers"
VaryByParam="parameter" %>

We can specify output caching in an aspx page at design time using the OutputCache directive as shown below.

<%@OutputCache Duration="25" VaryByParam="none" %>

The VaryByHeader and VaryByCustom parameters of the OutputCache directive are used to enable customization of the page's look or content based on the client that is accessing them. The cache duration is a mandatory parameter and specifies how long (in seconds) the web page remains in the cache memory. The VaryByParam parameter is optional and is used to cache different views of the page, i.e., whether the cached page would have different versions based on a specific parameter. A value of * in the same parameter indicates that the page would be cached based on all the Get/Post parameters. We can also specify one or more Get/Post parameter(s). The VaryByParam parameter is particularly useful in situations where we require caching a page based on some pre-defined criteria. As an example, we might require to cache a specific page based on the PatientID. The OutputCache directive needs to be specified as shown below.

<%@OutputCache Duration="10" VaryByParam="PatientID" %>

The VaryByParam parameter can also have multiple parameters as shown in the example below.

<%@OutputCache Duration="20" VaryByParam="PatientID;ProviderID" %>

The location parameter in the OutputCache directive is used to specify the cache location, i.e., from where the client received the cached page. This should be one of the possible four OutputCacheLocation enumeration values, namely, Any, Client, Downstream, None, Server. An example is given below.

<%@OutputCache Duration="10" VaryByParam="*" Location = "Any"%>

Programmatic Approach

Page Output Caching can also be specified programmatically using the Response.Cache.SetCacheability method. The parameters to this method can be “NoCache”, “Private”, “Public” or “Server”. Refer to the code example below that illustrates how to set a page’s cache ability programmatically:

Response.Cache.SetCacheability (HttpCacheability.Server);

It is also possible to set the OutputCache on all the pages in an ASP.NET application programmatically in the Global.asax page. Refer to the code snippet that follows:--

void Application_BeginRequest(object sender, EventArgs e)
{
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.Server);
}

The source code given in the above code snippet sets the cache ability programmatically in the BeginRequest event of the application's global class.

Page Fragment Caching

Also known as Partial Page Caching, this is a feature that allows specific portions of the web page to be cached rather than caching the entire web page and is useful in situations where a particular web page can contain both static and dynamic content. MSDN says, “Often, caching an entire page is not feasible, because certain parts of the page are customized for the user. However, there may be other parts of the page that are common to the entire application. These are perfect candidates for caching, using fragment caching and user controls“. The term “fragment” implies one or more user controls in the web page. Note that each of these user controls can have different cache durations. The following statement depicts how this can be accomplished.

<%@ OutputCache Duration="10"
VaryByControl="PatientID” VaryByParam="*"%>

New Page Caching Features in ASP.NET 2.0

With ASP.NET 2.0, you do no longer need to split your pages into multiple .ascx user control files for implementing Page Fragment Caching or Partial Page Caching. You are free to do the same using Output Cache Substitution – a concept that provides you the facility to cache the output of a web page with some dynamic portions inside in page. Note that this is in sharp contrast to ASP.NET 1.x when we needed to implement the same as, in the partial page caching; “the overall page is dynamic, with cached regions in the middle”.

The following code snippet illustrates how we can implement output cache substitution using the control.

Implement the method myMethod in such a way that it contains some code that would display static data. Now, the entire page would be cached except the contents of the control. Note that you can also achieve this programmatically using the Response.WriteSubstitution method.

Conclusion

Caching is a great tool that can be used to boost the performance web applications. This article has discussed the ways of storing data in the cache using ASP.NET with code examples wherever applicable. The next part in this series discusses storing data in the cache, i.e., Data Caching in ASP.NET.

Should You Upgrade Your Current SQL Server Cluster to a SQL Server 2005 Cluster?

If your organization is like many organizations, it may have some older version SQL Server clusters in production. If so, at some point you will have to make a choice about how to upgrade them to SQL Server 2005. Your available options include:

• Don't upgrade.
• Perform an in-place SQL Server 2005 upgrade.
• Rebuild your cluster from scratch, and then install SQL Server 2005 clustering.

Let's look at all of these options.

Don't Upgrade

This is an easy decision. It's simple and doesn't cost anything. Just because a new version of SQL Server comes out doesn't automatically mean you have to upgrade. If your current SQL Server cluster is running fine, why change it? You are just asking for potential new problems where you have none now, and you will have new costs.

On the other hand, if your current SQL Server cluster is not fine, then you have the perfect reason to upgrade. SQL Server 2005 offers many new benefits and they may solve the problems your current cluster is experiencing. But don't automatically assume this is the case. Before you upgrade, do the research to determine if the new features of SQL Server 2005 will actually resolve your problems. If not, then sticking with what you know may be the best choice.

Upgrading Your SQL Server 2005 Cluster In-Place

If you decide to upgrade, your next step is to decide whether you want to upgrade in-place, or start over from scratch with a fresh install. In this section, we take a look at how you upgrade in-place.

Before we begin talking about how to upgrade a current cluster to a SQL Server 2005 cluster, we first need to discuss what operating system you are currently running. If you are on Windows Server 2003 with the latest service pack, then you are in good shape and upgrading to SQL Server 2005 in-place should not be a problem. But if you are not running Windows 2003 Server, then you should seriously consider rebuilding your server nodes so that they are running at the latest operating system level.

The logic behind this is that if you are going to all the trouble to upgrade to SQL Server 2005, you should be running on the latest operating system platform, otherwise, you are not taking advantage of the latest technology and the benefits they bring to high availability. So if you are still running Windows Server 2000 (or earlier), I strongly recommend that you don't upgrade in-place. And don't even think about upgrading the operating system in-place, then upgrading to SQL Server 2005 in-place. You are just asking for trouble.

You can upgrade from a SQL Server 7.0 or SQL Server 2000 cluster directly to SQL Server 2005. If you are running SQL Server 6.5, you are out of luck.

Now, assuming you are running Windows Server 2003, let's look at the major steps to performing an upgrade in-place from your current version of SQL Server to SQL Server 2005.

• Insure that your current SQL Server cluster is running 100% correctly. If there are any problems with the current cluster, do not perform an in-place upgrade. It will most likely fail.
• Run the free Microsoft ClusPrep utility. It will help you determine if you can perform a successful upgrade. Download this tool from Microsoft's Web site.
• Run the SQL Server 2005 Upgrade Advisor to identify any potential issues that should be corrected before the upgrade begins.
• Assuming all of the above are successful, you can then perform the upgrade by running the SQL Server 2005 Installation Wizard and following its instructions. The Installation Wizard will recognize your current SQL Server cluster installation and will guide you through the upgrade.
• Once the upgrade is complete, you will want to test the upgrade extensively before releasing it to production.

If you do decide to upgrade in-place, be sure you reserve plenty of downtime to complete the upgrade and for testing, along with including extra time to deal with any unforeseen problems.

Rebuilding Your Cluster From Scratch

Instead of upgrading in-place, it is often a good idea to rebuild your cluster from scratch. This is especially true if any one of the following conditions exist:

• You need to upgrade your current hardware (because it is either old or underpowered).
• You need to upgrade the operating system.
• The current cluster installation is unstable.
• You have a disdain for upgrading software in-place, and prefer a fresh install.

If you do decide to upgrade from scratch, you have to decide whether you will be installing onto new hardware or will be using your old hardware.

If you install on new hardware, you have the convenience of building the cluster, and testing it, at your own pace, while the current cluster is still in production. This helps to ensure that you have done an outstanding job building the cluster, and at the same time, it helps relieve some of the stress that you will experience if you have to reuse your old hardware and then rebuild the cluster during a brief and intense rebuild.

If you don't have the luxury of new hardware, and have to use your old hardware, you will have to identify a good time so that your system can be down while the rebuild occurs. This could range from a 4- to a 12-hour period, depending on your particular circumstances. Besides the time your cluster will be down, there is also the added risk of unexpected problems. For example, you might make an installation mistake halfway through the upgrade and have to start over. Because of the uncertainty involved, you should first estimate how much time you think the upgrade will take under good circumstances, and then double this time as the size of your requested window of downtime. This way, your users will be prepared for the worst.

Whether you upgrade using new hardware or old hardware, there are two additional issues you will have to consider:

• Will you reuse your current virtual server name and IP address, or select new ones?
• How will you move your data from the old cluster to the new cluster?

Let's look at each of these issues, one at a time.

The clients that access your current SQL Server cluster do so using the cluster's virtual name and IP address. If you want the clients to continue using the same virtual name and IP address to access your cluster, then you will need to reuse the old virtual name and IP address in the new cluster. This is the most common approach because it is generally easier to reuse a single virtual and IP address than reconfiguring dozens, if not hundreds, of clients who access the cluster.

If you are upgrading using old hardware, reusing the former virtual name and IP address is not an issue because the old cluster is brought down, then the new one back up, so there is never a case where the virtual name and IP address could be on two clusters at the same time (which won't work).

But if you upgrade by using new hardware, you will need to assign a virtual name and IP address for testing, but you won't be able to use the old ones because they are currently in use. In this case, you will need to use a temporary virtual name and IP address for testing, and when you are ready for the actual changeover from the old to the new cluster, you will need to follow these general steps:

• Secure your data.
• Remove SQL Server clustering from the old cluster, or turn off the old cluster.
• On the new cluster, remove SQL Server 2005 clustering, and then reinstall it using the virtual name and IP address of the old cluster.
• Restore the data.

Uninstalling SQL Server 2005 clustering, and then reinstalling it with the old virtual name and IP address is a pain, but doesn't take a long time. Besides, this is the only way to change the virtual name or IP address of a SQL Server 2005 cluster install.

Now, how do you move the data from the old cluster to the new? This depends somewhat on whether or not you are using old hardware or new hardware.

If you use old hardware, all you really have to do is to back up the system and user databases, and then detach the user databases. Rebuild the cluster without deleting the backups or detached databases. When the cluster is rebuilt, restore the system databases and reattach the detached databases. This of course assumes that the databases will remain in their current location. If you need to move the databases, then you need to follow the next option.

If you are moving to new hardware, or will be moving the location of the databases on old hardware, you would first do full backups of the system and user databases, and then detach the user databases. Next, move these to the new server, or new location. Then when the cluster is rebuilt, restore the system databases and reattach the user databases.

Because of space limitations, the above steps don't include all the complex details, such as what happens if the drive letter changes, and so on. The key to success is to plan all of these steps, and if possible, perform a trial run before you do an actual cutover.

Backout Plan

No matter how you decide to upgrade to SQL Server 2005 clustering, you need to have a backout plan. Essentially, a backout plan is what you do if your upgrade fails. I can't tell you exactly what to do for a backout plan because I don't know your particular circumstances. But I do know you need a backout plan if the upgrade fails. So as you are planning your upgrade, consider how the plan could fail, and come up with options to get you back in business should things not go well. Your job could depend on how good your backout plan is.

Which Upgrade Option Is Best?

Speaking from personal experience, I always prefer to upgrade by rebuilding my clusters from scratch on new hardware. This is the easiest, fastest, least risky and least stressful way. Unfortunately, you may not have this option for whatever reasons management gives you. In this case, you will have to work with what you have been given. The key to a successful upgrade is lots of detailed planning and testing, and of course, having a great backout plan.

Caching in ASP.NET (Part II)

Caching is an essential feature of ASP.NET that reduces latency and the network traffic by storing frequently used data and pages in the Cache for quick retrieval later; hence greatly boosting the application’s performance. We have had a detailed look at the concepts of Caching in the first part of this series of articles on Caching in ASP.NET. This article presents Data Caching, a feature of ASP.NET that allows you to store your data in the cache memory for faster retrieval. It also discusses the Cache API and the Cache expiration strategies. As in the earlier articles in this series, it also throws light on the related newly added features in ASP.NET 2.0 Caching.

Data Caching using the Cache API

Data Caching is a feature that enables us to store frequently used data in the Cache. The Cache API was introduced in ASP.NET 1.x and was quite exhaustive and powerful. However, it did not allow you to invalidate an item in the Cache based on a change of data in a Sql Server database table. With ASP.NET 2.0, the Cache API provides you a database triggered Cache invalidation technique that enables you to invalidate the data in the Cache based on any change of data in the Sql Server database table. Besides this, you can also create custom cache dependencies.

The Cache class contains a numerous properties and methods. Of these, the Add, Insert, Remove methods and the Count property are the most frequently used. The Add/Insert method of the Cache class is used to add/insert an item into the cache. The Remove method removes a specified item from the cache. The Count property returns the current number of objects in the Cache.

The Cache property of the Page.HttpContext class can be used to store and retrieve data in the cache. The following code snippet illustrates the simplest way of storage and retrieval of data to and from the cache using the Cache class.

//Storing data
Cache ["key"] = objValue;
//Retrieving the data
object obj = Cache ["key"];

We can also check for the existence of the data in the cache prior to retrieving the same. This is shown in the code snippet below:

object obj = null;

if(Cache["key"] != null)
obj = Cache["key"];

The following code snippet illustrates how we can make use of the Cache API to store and retrieve data from the Cache. The method GetCountryList checks to see if the data (list of countries) exists in the cache. If so, it is retrieved from the cache; else, the GetCountryListFromDatabase method fills the DataSet from the database and then populates the Cache.

public DataSet GetCountryList()
{
string key = "Country";
DataSet ds = Cache[key] as DataSet;

if (ds == null)
{
ds = GetCountryListFromDatabase ();
Cache.Insert(cacheKey, ds, null, NoAbsoluteExpiration,
TimeSpan.FromHours(5),CacheItemPriority.High, null);
}

else
{
return ds;
}

} //End of method GetCountryList

public DataSet GetCountryListFromDatabase ()
{
// Necessary code to retrieve the list of countries from the database and
// store the same in a DataSet instance, which in turn is returned to the
// GetCountryList method.
}

Cache Dependency and Cache Expiration Strategies

Cache dependency implies a logical dependency between the data in the Cache and physical data storage. Whenever the data in the physical storage changes, the dependency is broken, the cache is invalidated and the cached item is removed. This physical storage can be an XML file, a database, a flat file, etc.

The following is the complete syntax of using the CacheDependency class.

CacheDependency cacheDependency = new CacheDependency(fileObj, dateTimeObj);
Cache.Insert(key, value, cacheDependency);

As an example, you can set Cache dependency to an external Xml file as shown below:--

Cache.Insert("Country", objDataSet, new CacheDependency(Server.MapPath("Country.xml")));

You can also set a Cache dependency to a file in a remote system as shown below:--

CacheDependency objCacheDependency = new
CacheDependency(@"\\192.168.0.9\joydipk\Test.txt");

Similarly, you can also set Cache dependency on an entire folder. When the contents of the folder changes it will call an event handler for which you need to write the necessary code. In the earlier version of ASP.NET, the CacheDependency class was sealed. Hence you could not subclass this class to create your own Custom Cache dependencies. Things have now changed with ASP.NET 2.0 and the class is no longer sealed so you can create Custom Cache Dependencies by extending this class.

Cache Expiration relates to the removal of data in the cache after the durability of the cached item in the cache has expired. Cache expirations strategies can be Time Based, File Based or Key Based. The following sections discuss each of these strategies in detail with code examples.

Time Based Expiration

This is implemented by specifying a specific duration for which a cached item should remain in the cache. When the time elapses, the item is removed from the cache and subsequent requests to retrieve the item returns a null. This is used to specify a specific period of time for which the page would remain in the cache. The following statement specifies such expiration for a page in the code behind of a file using C# using the Cache API.

Response.Cache.SetExpires(DateTime.Now.AddSeconds(45));

This can also be accomplished by specifying the following in the page output cache directive as shown here.

<%@OutputCache Duration="45"
VaryByParam="None" %>

Time based expiration strategies can in turn be of the following two types:

Absolute
Sliding

In Absolute Expiration, the cache expires at a fixed time/date. For Sliding Expiration the cache duration is increased by the specified sliding expiration value each time the page is requested. Thus a heavily requested page will remain cached, whereas a less requested page will not be cached. Sliding Expiration is therefore useful in ensuring that the valuable resources allocated to the cache are allocated to the most heavily requested pages/items.

The following is an example of Absolute Expiration:

Cache.Insert("Country", ds, null, DateTime.Now.AddMinutes(1), Cache.NoSlidingExpiration);

The cache is set to expire exactly two minutes after the user has retrieved the data.

The following is an example of Sliding Expiration:

Cache.Insert("Country", ds, null, Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(1));

Note that you cannot use both Absolute and Sliding expirations at the same time.

File Based Expiration

This is implemented by using a file as a cache dependency. Whenever the contents of the dependent file are changed, the cached is invalidated. As shown in the code snippet below.

Cache.Insert("Country", obj, new CacheDependancy(Server.MapPath("CountryList.xml")));
Cache.Insert("Country", xmldocumentObject,cacheDependencyObj);

Key Based Expiration

The third type of dependency is the key dependency. This implies that you can map a cache entry to an existing dependency. Now, when the depended-upon entry changes or expires, the dependent entry will also be expired. You can have an array of keys that can be specified as a single CacheDependency. Refer to the code snippet below:

string[] keys = new string[] {"key"};
CacheDependency cacheDependencyObj = new CacheDependency(null,keys);
Cache.Insert("Country", xmldocumentObject,cacheDependencyObj);

Enterprise

Library Caching Application Block

The Patterns and Practices Group of Microsoft’ came up with the reusable, configurable and extensible Enterprise Library Caching Block that enables you to incorporate local cache capabilities in your applications. Microsoft recommends usage of this block when the volume of relatively static data transport is high in your application and performance is a major concern.

The Enterprise Library Caching Application Block facilitates storage of cached data in the memory, isolated storage (i.e., an XML file) or in a database. The Enterprise Library Caching Application Block encapsulates efficient, consistent caching strategies and, hence, simplifies implementation of caching strategies in enterprise application development.

The salient features of the Caching Application Block include:

Support for efficient caching strategies in web applications
Configurable
Thread Safety

Conclusion

Caching is a great tool that can be used to boost the performance of your web applications. Caching in ASP.NET is an effective and powerful way of boosting application performance while minimizing the usage of precious server resources. However, choosing the appropriate type of caching and caching the right type of data goes a long way in designing and developing high performing applications. The fourth and final article in this series will discuss the best practices for Caching in ASP.NET and demonstrates a sample application that will present all the concepts that we have learnt so far in this series.

Preparing the SQL Server 2005 Clustering Infrastructure

Before you even begin building a SQL Server 2005 cluster, you must ensure that your network infrastructure is in place. Here's a checklist of everything that is required before you begin installing a SQL Server 2005 cluster. In many cases, these items are the responsibility of others on your IT staff. But it is your responsibility to ensure that all of these are in place before you begin building your SQL Server 2005 cluster.

• Your network must have at least one Active Directory server, and ideally two for redundancy.
• Your network must have at least one DNS server, and ideally two for redundancy.
• Your network must have available switch ports for the public network cards used by the nodes of the cluster. Be sure they are manually set to match the manually set network card settings used in the nodes of the cluster. In addition, all the nodes of a cluster must be on the same subnet.
• You will need to secure IP addresses for all the public network cards.
• You must decide how you will configure the private heartbeat network. Will you use a direct network card to network card connection, or use a hub or switch?
• You will need to secure IP addresses for the private network cards. Generally, use a private network subnet such as 10.0.0.0 – 10.255.255.255, 172.16.0.0 – 172.31.255.255, 192.168.0.0 – 192.168.255.255. Remember, this is a private network only seen by the nodes of the cluster.
• Ensure that you have proper electrical power for the new cluster servers and shared array (assuming they are being newly added to your data center.)
• Ensure that there is battery backup power available for all the nodes in your cluster and your shared array.
• If you don't already have one, create a SQL Server service account to be used by the SQL Server services running on the cluster. This must be a domain account, with the password set to never expire.
• If you don't already have one, create a cluster service account to be used by the Windows Clustering service. This must be a domain account, with the password set to never expire.
• Create three global groups, one for the SQL Server Service, the SQL Server Agent Service, and the SQL Text Service. You will need these when you install SQL Server 2005 on a cluster.
• You will need to determine a name for your virtual cluster (Clustering Services) and secure a virtual IP address for it.
• You will need to determine a name for your virtual SQL Server 2005 cluster and secure a virtual IP address for it.
• If you are using a Smart UPS for any node of the cluster, remove it before installing Cluster Services then re-add it.
• If your server nodes have AMP/ACPI power saving features, turn them off. This includes network cards, drives, etc. Their activation can cause a failover.

I have included this list here so you understand that these are the steps you need to take before actually beginning a cluster install.

Preparing the Hardware

Based on my experience building clusters, the hardware presents the thorniest problems, often taking the most time to research and configure. Part of the reason for this is that there are many hardware options, some of which work, and others that don't.

Unfortunately, there is no complete resource you can use to help you sort through this. Each vendor offers different hardware, and the available hardware is always changing, along with new and updated hardware drivers, making this entire subject a moving target with no easy answers. In spite of all this, here is what you need to know to get started on selecting the proper hardware for your SQL Server 2005 cluster.

Finding Your Way Through the Hardware Jungle

Essentially, here's the hardware you need for a SQL Server cluster. To keep things simple, we will only be referring to a 2-node active/passive cluster, although these same recommendations apply to multi-node clusters. The following are my personal minimum recommendations. If you check out Microsoft's minimum hardware requirements for a SQL Server 2005 cluster, they will be somewhat less. Also, I highly suggest that each node in your cluster be identical. This can save lots of installation and administrative headaches.

Server Nodes

• Dual CPUs, 2 GHz or higher, 2MB L2 Cache (32-bit or 64-bit)
• 1GB or more RAM
• Local mirrored SCSI drive (C:), 9GB or larger
• SCSI DVD player
• SCSI connection for local SCSI drive and DVD player
• SCSI or Fiber connection to shared array or SAN
• Redundant power supplies
• Private network card
• Public network card
• Mouse, keyboard, and monitor (can be shared)

Shared Array

• SCSI-attached RAID 5 or RAID 10 array with appropriate high-speed SCSI connection. With Microsoft Clustering, SCSI is only supported if you have a 2-node cluster. If you want to cluster more than two nodes, you must use a fiber-attached disk array or SAN.

Or

• Fiber-attached RAID 5 or RAID 10 array with appropriate high-speed connection.

Or

• Fiber-attached SAN storage array with appropriate high-speed connection (a fiber switch).

Because hardware varies so much, we won't spend much time on hardware specifics. If you are new to clustering, I would suggest you contact your hardware vendor for specific hardware recommendations. Keep in mind that you will be running SQL Server 2005 on this cluster, so ensure that whatever hardware you select meets the needs of your predicted production load.

If It's Not on the Hardware Compatibility List, Don't Even Think About It

Whether you select your own hardware, or get recommendations from a vendor, it is highly critical that the hardware selected is listed in the Cluster Solutions section of the Microsoft Hardware Compatibility List (HCL), which can be found at http://www.windowsservercatalog.com/.

As you probably already know, Microsoft lists all of the hardware in the HCL that is certified to run their products. If you are not building a cluster, you can pick and choose almost any combination of certified hardware from multiple vendors and know that it will work with Windows 2003 Server.

This is not the case with clustering. If you look at the Cluster Solutions in the HCL, you will notice that entire systems, not individual components, have to be certified. In other words, you can't just pick and choose individually certified components and know that they will work. Instead, you must select from approved cluster systems, which include the nodes and the shared array. In some ways, this reduces the variety of hardware you can choose from. On the other hand, by only selecting approved cluster systems, you can be assured the hardware will work in your cluster. And assuming you need another reason to only select an approved cluster system, Microsoft will not support a cluster that does not run on an approved system.

In most cases, you will find your preferred hardware as an approved system. But, as you can imagine, the HCL is always a little behind, and newly released systems may not be on the list yet. So what do you do if the system you want is not currently on the HCL? Do you select an older, but tested and approved system, or do you take a risk and purchase a system that has not yet been tested and officially approved? This is a tough call. But what I have done in the past when confronted by this situation is to require the vendor to certify, on their own, that the hardware will become certified by Microsoft at some time in the future, and if the hardware is not approved (as promised) that the vendor has to correct the problem by replacing unapproved hardware with approved hardware at their cost. I have done this several times and it has worked out fine so far.

Preparing the Hardware

As a DBA, you may or may not be the one who installs the hardware. In any case, here are the general steps most people follow when building cluster hardware:

• Install and configure the hardware for each node in the cluster as if they will be running as stand-alone servers. This includes installing the latest approved drivers.
• Once the hardware is installed, install the operating system and latest service pack, along with any additional required drivers.
• Connect the node to the public network. To make things easy, name the network used for public connections as "network."
• Install the private heartbeat network. To make things easy, name the private heartbeat network "private."
• Install and configure the shared array or SAN.
• Install and configure the SCSI or fiber cards in each of the nodes and install the latest drivers.

Now, one at a time, connect each node to the shared array or SAN following the instructions for your specific hardware. It is critical that you do this one node at a time. By this, I mean that only one node at a time should be physically on and connected to the shared array or SAN and configured. Once that node is configured, turn it off, and then turn the next node on and configure it, and so on, one node at a time. If you do not following this procedure, you risk corrupting the disk configuration on your nodes, requiring you to start over again.

After connecting each node to the shared array or SAN, you will need to use Disk Administrator to configure and format the drives on the shared array. You will need at least two logical drives on the shared array. One will be for storing your SQL Server databases, and the other one will be for the Quorum drive. The data drive must be big enough to store all the required data, and the Quorum drive must be at least 500 MB (which is the smallest size that an NTFS volume can efficiently operate. When configuring the shared drives using Disk Administrator, it is required that each node of the cluster use the same drive letter when referring to the drives on the shared array or SAN. For example, you might want to assign your data drive as drive "F:" on all the nodes, and assign the Quorum drive letter "Q:" on all the nodes.

Once all of the hardware and software is configured, it is critical that it be functioning properly. This means that you need to test, test, and test again the hardware, ensuring that there are no problems before you begin installing clustering services. While you may be able to do some diagnostic hardware testing before you install the operating system, you will have to wait until after installing the operating system before you can fully test the hardware.

Once all of the hardware has been configured, and tested, you are ready to install Windows 2003 Clustering. I will cover this topic in a future article.

An Introduction to SQL Server 2005 Clustering Basics

Clustering is a complex technology with lots of messy details. To make it easier to understand, let's take a look at the big picture of how clustering works. In this article we take a look at:

• Active vs. Passive Nodes
• Shared Disk Array
• The Quorum
• Public and Private Networks
• The Virtual Server
• How a Failover Works

Active vs. Passive Nodes

Although a SQL Server 2005 cluster can support up to eight nodes, clustering actually only occurs between two nodes at a time. This is because a single SQL Server 2005 instance can only run on a single node at a time, and should a failover occur, the failed instance can only fail over to another individual node. This adds up to two nodes. Clusters of three or more nodes are only used where you need to cluster multiple instances of SQL Server 2005.

In a two-node SQL Server 2005 cluster, one of the physical server nodes is referred to as the active node, and the other one is referred to as the passive node. It doesn't matter which physical servers in a cluster is designated as the active or the passive, but it is easier, from an administrative point of view, to go ahead and assign one node as the active and the other as the passive. This way, you won't get confused about which physical server is performing which role at the current time.

When we refer to an active node, we mean that this particular node is currently running an active instance of SQL Server 2005 and that it is accessing the instance's databases, which are located on a shared data array.

When we refer to a passive node, we mean that this particular node is not currently in production and it is not accessing the instance's databases. When the passive node is not in production, it is in a state of readiness, so that if the active node fails, and a failover occurs, it can automatically go into production and begin accessing the instance's databases located on the shared disk array. In this case, the passive mode then becomes the active node, and the formerly active node now becomes the passive node (or failed node should a failure occur that prevents it from operating).

Shared Disk Array

So what is a shared disk array? Unlike non-clustered SQL Server 2005 instances, which usually store their databases on locally attached disk storage, clustered SQL Server 2005 instances store data on a shared disk array. By shared, we mean that both nodes of the cluster are physically connected to the disk array, but that only the active node can access the instance's databases. There is never a case where both nodes of a cluster are accessing an instance's databases at the same time. This is to ensure the integrity of the databases.

Generally speaking, a shared disk array is a SCSI- or fiber-connected RAID 5 or RAID 10 disk array housed in a stand-alone unit, or it might be a SAN. This shared array must have at least two logical partitions. One partition is used for storing the clustered instance's SQL Server databases, and the other is used for the quorum.

The Quorum

When both nodes of a cluster are up and running, participating in their relevant roles (active and passive) they communicate with each other over the network. For example, if you change a configuration setting on the active node, this configuration change is automatically sent to the passive node and the same change made. This generally occurs very quickly, and ensures that both nodes are synchronized.

But, as you might imagine, it is possible that you could make a change on the active node, but before the change is sent over the network and the same change made on the passive node (which will become the active node after the failover), that the active node fails, and the change never gets to the passive node. Depending on the nature of the change, this could cause problems, even causing both nodes of the cluster to fail.

To prevent this from happening, a SQL Server 2005 cluster uses what is called a quorum, which is stored on the quorum drive of the shared array. A quorum is essentially a log file, similar in concept to database logs. Its purpose is to record any change made on the active node, and should any change recorded here not get to the passive node because the active node has failed and cannot send the change to the passive node over the network, then the passive node, when it becomes the active node, can read the quorum file and find out what the change was, and then make the change before it becomes the new active node.

In order for this to work, the quorum file must reside on what is called the quorum drive. A quorum drive is a logical drive on the shared array devoted to the function of storing the quorum.

Public and Private Networks

Each node of a cluster must have at least two network cards. One network card will be connected to the public network, and the other to a private network.

The public network is the network that the SQL Server 2005 clients are attached, and this is how they communicate to a clustered SQL Server 2005 instance.

The private network is used solely for communications between the nodes of the cluster. It is used mainly for what is called the heartbeat signal. In a cluster, the active node puts out a heartbeat signal, which tells the other nodes in the cluster that it is working. Should the heartbeat signal stop then a passive node in the cluster becomes aware that the active node has failed, and that it should at this time initiate a failover so that it can become the active node and take control over the SQL Server 2005 instance.

The Virtual Server

One of the biggest mysteries of clustering is how do clients know when and how to switch communicating from a failed cluster node to the now new active node? And the answer may be a surprise. They don't. That's right; SQL Server 2005 clients don't need to know anything about specific nodes of a cluster (such as the NETBIOS name or IP address of individual cluster nodes). This is because each clustered SQL Server 2005 instance is given a virtual name and IP address, which clients use to connect to the cluster. In other words, clients don't connect to a node's specific name or IP address, but instead connect to a virtual name and IP address that stays the same no matter what node in a cluster is active.

When you create a cluster, one of the steps is to create a virtual cluster name and IP address. This name and IP address is used by the active node to communicate with clients. Should a failover occur, then the new active node uses this same virtual name and IP address to communicate with clients. This way, clients only need to know the virtual name or IP address of the clustered instance of SQL Server, and a failover between nodes doesn't change this. At worst, when a failover occurs, there may be an interruption of service from the client to the clustered SQL Server 2005 instance, but once the failover has occurred, the client can once again reconnect to the instance using the same virtual name or IP address.

How a Cluster Failover Works (Putting the Pieces Together)

While there can be many different causes of a failover, let's look at the case where the power stops for the active node of a cluster and the passive node has to take over. This will provide a general overview of how a failover occurs.

Let's assume that a single SQL Server 2005 instance is running on the active node of a cluster, and that a passive node is ready to take over when needed. At this time, the active node is communicating with both the database and the quorum on the shared array. Because only a single node at a time can be communicating with the shared array, the passive node is not communicating with the database or the quorum. In addition, the active node is sending out heartbeat signals over the private network, and the passive node is monitoring them to see if they stop. Clients are also interacting with the active node via the virtual name and IP address, running production transactions.

Now, for whatever reason, the active node stops working because it no longer is receiving any electricity. The passive node, which is monitoring the heartbeats from the active node, now notices that it is not receiving the heartbeat signal. After a predetermined delay, the passive node assumes that the active node has failed and it initiates a failover. As part of the failover process, the passive node (now the active node) takes over control of the shared array and reads the quorum, looking for any unsynchronized configuration changes. It also takes over control of the virtual server name and IP address. In addition, as the node takes over the databases, it has to do a SQL Server startup, using the databases, just as if it is starting from a shutdown, going through a database recovery. The time this takes depends on many factors, including the speed of the system and the number of transactions that might have to be rolled forward or back during the database recovery process. Once the recovery process is complete, the new active nodes announces itself on the network with the virtual name and IP address, which allows the clients to reconnect and begin using the SQL Server 2005 instance with minimal interruption.

Summary

That's the big picture of how SQL Server 2005 clustering works. If you are new to SQL Server clustering, it is important that you understand these basic concepts before you begin to drill down into the detail. In later articles, I will discuss, in great detail, how to plan, build, and administer a SQL Server 2005 cluster.

How to Cluster Windows Server 2003

Before you can install SQL Server 2005 clustering, you must first install Windows Server 2003 clustering services. Once it is successfully installed and tested, then you can install SQL Server 2005 clustering. In this article, we take a step-by-step approach to installing and configuring Windows 2003 clustering. In a later article, we will learn how to install SQL Server 2005 clustering.

Before Installing Windows 2003 Clustering

Before you install Windows 2003 clustering, we need to perform a series of important preparation steps. This is especially important if you didn't build the cluster nodes, as you want to ensure everything is working correctly before you begin the actual cluster installation. Once they are complete, then you can install Windows 2003 clustering. Here are the steps you must take:

• Double check to ensure that all the nodes are working properly and are configured identically (hardware, software, drivers, etc.).
• Check to see that each node can see the data and Quorum drives on the shared array or SAN. Remember, only one node can be on at a time until Windows 2003 clustering is installed.
• Verify that none of the nodes has been configured as a Domain Controller.
• Check to verify that all drives are NTFS and are not compressed.
• Ensure that the public and private networks are properly installed and configured.
• Ping each node in the public and private networks to ensure that you have good network connections. Also ping the Domain Controller and DNS server to verify that they are available.
• Verify that you have disabled NetBIOS for all private network cards.
• Verify that there are no network shares on any of the shared drives.
• If you intend to use SQL Server encryption, install the server certificate with the fully qualified DNS name of the virtual server on all nodes in the cluster.
• Check all of the error logs to ensure there are no nasty surprises. If there are, resolve them before proceeding with the cluster installation.
• Add the SQL Server and Clustering service accounts to the Local Administrators group of all the nodes in the cluster.
• Check to verify that no antivirus software has been installed on the nodes. Antivirus software can reduce the availability of clusters and must not be installed on them. If you want to check for possible viruses on a cluster, you can always install the software on a non-node and then run scans on the cluster nodes remotely.
• Check to verify that the Windows Cryptographic Service Provider is enabled on each of the nodes.
• Check to verify that the Windows Task Scheduler service is running on each of the nodes.
• If you intend to run SQL Server 2005 Reporting Services, you must then install IIS 6.0 and ASP .NET 2.0 on each node of the cluster.

These are a lot of things you must check, but each of these is important. If skipped, any one of these steps could prevent your cluster from installing or working properly.

How to Install Windows Server 2003 Clustering

Now that all of your physical nodes and shared array or SAN is ready, you are now ready to install Windows 2003 clustering. In this section, we take a look at the process, from beginning to end.

To begin, you must start the Microsoft Windows 2003 Clustering Wizard from one of the nodes. While it doesn't make any difference to the software which physical node is used to begin the installation, I generally select one of the physical nodes to be my primary (active) node, and start working there. This way, I won't potentially get confused when installing the software.

If you are using a SCSI shared array, and for many SAN shared arrays, you will want to make sure that the second physical node of your cluster is turned off when you install cluster services on the first physical node. This is because Windows 2003 doesn't know how to deal with a shared disk until cluster services is installed. Once you have installed cluster services on the first physical node, you can turn on the second physical node, boot it, and then proceed with installing cluster services on the second node.

Installing the First Cluster Node

To begin your installation of SQL Server 2003 Clustering, open Cluster Administrator. If this is the first cluster, then you will be presented with the following window.

From the Action drop-down box, select Create New Cluster and click OK. This brings up the New Server Cluster Wizard, as show below.

Click Next to begin the wizard.

The next steps seem easy because of the nature of the wizard, but if you choose the wrong options, they can have negative consequences down the line. Because of this, it is important that you carefully think through each of your responses. Ideally, you will already have made these choices during your planning stage.

The first choice you must make is the domain the cluster will be in. If you have a single domain, this is an easy choice. If you have more than one domain, select the domain that all of your cluster nodes reside in.

The second choice is the name you will assign the virtual cluster. This is the name of the virtual cluster, not the name of the virtual SQL Server. About the only time you will use this name is when you connect to the cluster with Cluster Administrator. SQL Server 2005 clients will not connect to the cluster using this virtual name.

Once you enter the information, click Next to proceed.

Now, we have to tell the wizard the physical name of the node we want to install clustering on. Assuming that you are running the Cluster Wizard on the primary node of your cluster, then the computer name you see in the above screen will be the name of the physical node you are installing on. If you are installing from one node, but want to install clustering on a different node, you can, but it just gets confusing if you do. It is much easier to install on the same node.

Notice the Advanced button in the screen shot above. If you click on it, you will see the following.

Advanced Configuration Options allow you to choose from between a Typical and an Advanced configuration. In almost all cases, the Typical configuration will work fine, and that is the option we use during this example. The Advanced configuration option is only needed for complex SAN configurations, and is beyond the scope of this article.

So click Cancel to return to the wizard, enter the correct physical node, if need be, and click Next.

This next step is very important. What the Cluster Wizard does is to verify that everything is in place before it begins the actual installation of the cluster service on the node. As you can see above, the wizard goes through many steps, and if you did all of your preparation correctly, when the testing is done, you will see a green bar under Tasks completed, and you will be ready to proceed. But if you have not done all the preliminary steps properly, you may see yellow or red icons next to one or more of the many tested steps, and a green or red bar under Tasks completed.

Ideally, you will want to see results similar to the figure above, with a green bar and no yellow icons next to the test steps. In some cases, you may see yellow warning icons next to one or more of the test steps, but still see a green bar at the bottom. While the green bar does indicate that you can proceed, it does not mean the cluster will be completed successfully or will be configured like you want it to be completed. If you see any yellow warning icons, you can drill down into them and see exactly what the warning is. Read each warning very carefully. If the warning is something unimportant to you, it can be ignored. But in most cases, the yellow warnings need to be addressed. This may mean you will have to abort the cluster service installation at this time to fix the problem. Then you can try to install it again.

If you get any red warning icons next to any of the test steps, then you will also get a red bar at the bottom, which means that you have a major problem that needs to be corrected before you can proceed. Drill down to see the message and act accordingly. Most likely, you will have to abort the installation, fix the issue, and then try installation again.

Assuming that the installation is green and you are ready to proceed, click Next.

The next step is to enter the IP address of our virtual cluster. This is the IP address for the cluster, not the virtual SQL Server. The IP address must be on the same subnet as all of the nodes in the cluster. Click Next.

Next you enter the name of the domain account you want to use as the cluster service account. You will also enter the account's password and the name of the domain where the account was created. This account should have already been created in your domain and added to all of the cluster nodes in the Local Administrators Group. Click Next.

The next Cluster Wizard step is the Proposed Cluster Configuration. But before you click Next, be sure to click on the Quorum button and check which drive the Cluster Wizard has selected for the Quorum. In this case Drive Q has been chosen, which is correct. Most of the time, the Cluster Wizard will select the correct drive for the Quorum, but not always. This is why it is important to check to see if the correct drive was chosen. Because I named my Quorum drive "Q," it is very easy for me to determine that the correct drive was chosen by the Cluster Administrator. That is why I earlier suggested that you name the Quorum drive "Q."

Assuming everything is OK, click OK to accept the Quorum drive, and then click Next. At this time, the Cluster Wizard will reanalyze the cluster, again looking for any potential problems. If none is found, click Next, and then click Finish to complete the installation of SQL Server 2003 clustering on the first node.

Installing the Second Node of Your Cluster

Once you have installed the first node of your cluster, it is time to install the second node. Like the first node, the second node is installed from Cluster Administrator. Because the cluster already exists, we are just adding the second node to the currently existing cluster. You can install the second node from either the first node or the second node. Personally, I do it from the second node so that I don't get confused.

To install the second node, turn it on (it should have been off while you installed the first node) and bring up Cluster Administrator. You will get the same window as you saw when you installed the first node. From here, select Add Nodes to Cluster. This brings up the Add Nodes Wizard, which is very similar to the previous New Server Cluster Wizard we just ran, except it has fewer options.

As the wizard proceeds, you will enter the name of the physical node to add to the current cluster, after which a series of tests will be automatically run to verify that the node is ready to be clustered. As before, if you run into any problems—yellow or red warnings—you should correct them first before continuing. Once all problems have been corrected, you are then asked to enter the password for the cluster service account (to prove that you have permission to add a node to the cluster) and the node is added to the cluster.

Verifying the Nodes With Cluster Administrator

Once you have successfully installed the two nodes of your cluster, it is a good idea to view the nodes from Cluster Administrator. When you bring up Cluster Administrator for the first time after creating a cluster, you may have to tell it to Open a Connection to Cluster, and type in the name of the virtual cluster you just created. Once you have done this, the next time you open Cluster Administrator it will automatically open this cluster for you by default.

After opening up Cluster Administrator, what you see will be very similar to the figure below.

Notice that two resource groups have been created for you: Cluster Group and Group 0. The Cluster Group includes three cluster resources: the Cluster IP Address, the Cluster Name, and the Quorum drive. These were all automatically created for you by the Cluster Wizard. We will talk more about Group 0 a little later.

When you look next to each cluster resource, the State for each resource should be Online. If not, then your cluster may have a problem that needs to be fixed. As a quick troubleshooting technique, if any of the resources are not Online, right-click on the resource and choose Bring Online. In some cases, this will bring the resource online and you will not experience any more problems. But if this does not work, then you need to begin troubleshooting your cluster.

Also, next to each resource is listed the Owner of the resource. All the resources in a resource group will always have the same owner. Essentially, the owner is the physical node where the cluster resources are currently running. In the example above, the physical node they are running on is SQL2005A, which is the first node in my two-node cluster. If a failover occurs, then all of the resources in the resource group will change to the other node in your cluster.

How to Configure Windows Server 2003 for Clustering

Before you install SQL Server clustering, there is one small step you need to perform, and that is to prepare a resource group for the SQL Server resources that will be created when SQL Server is installed.

Most likely, when you created the cluster, as above, you will see a resource group named Group 0. This resource group was created when the cluster was created, and it most likely includes the shared resource for your SQL Server databases to use. See below.

In my example, Disk F, the shared array for SQL Server, is in Group 0. If you like, you can leave the resource group with this name, but it is not very informative. I suggest that you rename Group 0 to SQL Server Group. You can do this by right-clicking on Group 0 and selecting Rename.

In some cases, the Cluster Wizard may put the SQL Server shared disk array in the Cluster Group resource group and not create a Group 0. If this is the case, then you will need to create a new resource group and then move the SQL Server shared disk array from the Cluster Group to the newly created SQL Server resource group.

Here's how you create a new resource group using Cluster Administrator:

• Start Cluster Administrator.
• From the File menu, select New, then select Group. This starts the New Group Wizard.
• For the Name of the group, enter "SQL Server Group." Optionally, you can also enter a description of this group. Click Next.
• Now, you must select which nodes of your cluster will be running SQL Server. This of course will be all of your nodes. The nodes are listed on the left side of the wizard. CTRL-click each of the nodes on the left and then select Add. This will move the selected nodes from the left side of the wizard to the right side. Click Finish.

The new SQL Server Group resource group has now been created.

Now that the group has been created, it must be brought online. Here's how.

• From Cluster Administrator, right-click on the SQL Server resource group (it will have a red dot next to it) and select Bring Online.
• The red dot next to the resource group name goes away, and the SQL Server Group resource group is now online and ready for use.

Now, your next step is to move any disk resources from the Cluster Group (except the Quorum drive) to the SQL Server Group. This is a simple matter of dragging and dropping the disk resources from the Cluster Group to the SQL Server Group. Once you have done this, you are ready for the next step.

Test, Test, and Test Again

Once you have installed Windows 2003 clustering on your nodes, you need to thoroughly test the installation before beginning the SQL Server 2005 cluster install. If you don't, and problems arise later with Windows 2003 clustering, you may have to remove SQL Server 2005 clustering to fix it, so you might as well identify any potential problems and resolve them now.

Below are a series of tests you can perform to verify that your Windows 2003 cluster is working properly. After you perform each test, verify if you get the expected results (a successful failover). Also be sure to check the Windows event log files for any possible problems. If you find a problem during one test, resolve it before proceeding to the next test. Once you have performed all of these tests successfully, then you are ready to continue with the cluster installation.

Preparing for the Tests

Before you begin testing, identify a workstation that has Cluster Administrator on it, and use this copy of Cluster Administrator for interacting with your cluster during testing. You will get a better test using a remote copy of Cluster Administrator than trying to use a copy running on one of the cluster nodes.

Move Groups Between Nodes

The easiest test to perform is to use Cluster Administrator to manually move the Cluster Group and SQL Server resource groups from the active node to a passive node, and then back again. To do this, right-click on the Cluster Group and then select Move Group.

Once the group has been successfully moved from the active node to a passive node, then use the same procedure above to move the group back to the original node. The moves should be fairly quick and uneventful. Use Cluster Administrator to watch the failover and failback, and check the Event Logs for possible problems. After moving the groups, all of the resources in each group should be in the online state. If not, you have a problem that needs to be identified and corrected.

Manually Initiate a Failover in Cluster Administrator

This test is also performed from Cluster Administrator. Select any of the resources found in the Cluster Group resource group (not the cluster group itself), right-click on it, and select Initiate Failure. Because the cluster service always tries to recover up to three times from a failure, if it can, you will have to select this option four times before a test failover is initiated. Watch the failover from Cluster Administrator. After the failover, then failback using the same procedure as described above, again watching the activity from Cluster Administrator. Check the Event Logs for possible problems. After this test, all of the resources in each group should be in the online state. If not, you have a problem that needs to be identified and corrected.

Manually Failover Nodes by Turning Them Off

This time, we will only use Cluster Administrator to watch the failover activity, not to initiate it. First, turn off the active node by turning it off hard. Once this happens, watch the failover in Cluster Administrator. Once the failover occurs, turn the former active node on and wait until it fully boots. Then turn off the now current active node by turning it off hard. And again, watch the failover in Cluster Administrator. After the failover occurs, bring the off node back on. Check the Event Logs for possible problems. After this test, all of the resources in each group should be in the online state. If not, you have a problem that needs to be identified and corrected.

Manually Failover Nodes by Breaking the Public Network Connections

In this test, we will see what happens if network connectivity fails. First, both nodes being tested should be on. Second, unplug the public network connection from the active node. This will cause a failover to a passive node, which you can watch in Cluster Administrator. Third, plug the public network connection back into the server. Fourth, unplug the public network connection from the now active node. This will cause a failover to the current passive node, which you can watch in Cluster Administrator. Once the testing is complete, plug the network connection back into the server. Check the Event Logs for possible problems. After this test, all of the resources in each group should be in the online state. If not, you have a problem that needs to be identified and corrected.

Manually Failover Nodes by Breaking the Shared Array Connection

This test is always exciting as it is the test that is most apt to identify potential problems. First, from the active node, remove the shared array connection. This will cause a failover that you can watch in Cluster Administrator. Now reconnect the broken connection. Second, from the now active node, remove the shared array connection. Watch the failover in Cluster Administrator. When done, reconnect the broken connection. Check the Event Logs for possible problems. After this test, all of the resources in each group should be in the online state. If not, you have a problem that needs to be identified and corrected.

As I mentioned before, if any particular test produces unexpected problems, such as failover not working or errors are found in the Event Logs, identify and resolve them now before proceeding with the next test. Once you have resolved any problems, be sure to repeat the test that originally indicated the problem in order to verify that it has been fixed.

Now that you have completed the Windows 2003 cluster installation and have tested it, you are ready to install and configure the Microsoft Distributed Transaction Coordinator.

Configuring the Microsoft Distributed Transaction Coordinator

While not required, it is recommended that you install the Microsoft Distributed Transaction Coordinator (MS DTC) on each of the cluster nodes before installing SQL Server 2005 clustering. This is because SQL Server 2005 requires this service in order to perform some functions, including running distributed queries, two-phase commit transactions, and some aspects of replication. MS DTC must be installed after installing Windows 2003 clustering, but before installing SQL Server 2005 clustering.

Installing MS DTC Using Cluster Administrator

While MS DTC can be set up for clustering from the command line, it is much easier to use Cluster Administrator, as described below. This is because this procedure automatically configures MS DTC on all of the cluster nodes at the same time. Take your time to ensure that you do it right the first time.

• Start Cluster Administrator.
• Right-click on the Cluster Group resource group, select New, then Resource. This starts the new Resource Wizard.
• In the first screen of the Resource Wizard, enter the name of the resource you are creating, which would be "MSDTC Resource." If you like, you can also enter an optional description of this resource. Under Resource Type, select Distributed Transaction Coordinator. Under Group, Cluster Group should already be displayed. Click Next.
• In the Possible Owners dialog box, you will see that all of the nodes of the cluster are listed under Possible Owners. This is correct and should not be changed. Click Next.
• In the Dependencies dialog box, press and hold the CTRL key on the Quorum disk resource and the Cluster Name, then click Add. Then click finish.

At this time, the MSDTC Resource is created.

Now that the resource has been created, it must be brought online. Here's how.

• From Cluster Administrator, right-click on the MSDTC Resource (it will have a red dot next to it) and select Bring Online.

The red dot next to the resource name goes away, and the MSDTC Resource is now online and ready for use. If the new resource won't come online, delete it and try again.

Ready to Install SQL Server 2005

Finally, you are ready to install SQL Server 2005 clustering. This topic will be covered in my next article.

Using Service Broker to Communicate With Other Database Servers

In this article, we are going to discuss the Service Broker, another new feature in SQL Server 2005. In simple terms, Service Broker is a distributed messaging framework, or MSMQ (Microsoft Message Queue), in SQL Server 2005. Using this new feature, you will be able to add reliable, scalable, asynchronous functionality to your database applications.

This article will focus on the benefits of this new feature and basic configuration for applications.

Benefits of Message-Based Application

Before detailing the features and functionalities of Service Broker, let us examine the benefits of message-based applications.

Message based applications are very much needed when several services are involved. For example, in an online trading application, before confirming the order of a client, you need to perform several processes like checking stock availability, shipping availability, delivery date, etc. These processes will need time. If you are validating all the processes before confirming the order, your client may have to wait for some time at his browser. Instead, you can take in ordering details from the client and process all the necessary processes by putting the information in messaging. After getting all the information from all the services, you can compile them into an e-mail and send it to the client. In this way, your client does not have to wait for a long time at your Web site, which will make it more user-friendly.

Message based applications can process several services at once. For example, shipping service and stock availability service can run parallel. By doing so, it will minimize the time required to process the entire order.

Microsoft Messaging Queue (MSMQ) is a framework that supports this kind of messaging. You can write an application in VB, C#, or C++ to implement MSMQ applications. However, a basic issue with traditional messaging applications is when one process fails you have to rollback all the other processes manually, which has to be handled carefully. Not an easy task.

SQL Server 2005 has come up with this new feature to get rid of traditional messaging issues.

Install

Service Broker needs minimum configuration, and its features are available in all SQL Server editions. In SQL Server Express edition, you get the client features of Service Broker.

There are a few concepts or objects that we have to understand in Service Broker: Queues, Messages, Dialogs, Contracts, Services, and Conversation Groups.

Queues

Well, queues are nothing but queues. Like traditional queues, this is a first-in-first-out (FIFO) storage system. Queues provide a link between the message sender and the message receiver. The message sender will send the message to the queue while the message receiver will read the message. Because of the queue technology, the sender does not have to wait for the receiver to finish processing the message before sending the next one. The sender can send any number of messages and the receiver can process them at a different rate or even a different time thus enabling equal resource distribution.

In Service Broker, queues use hidden tables. Because messages are saved in tables, they will not be removed even after the server restarts. Also, you can backup your messages by simply making a backup of the database, as those queues are part of the database. Another important feature is that you cannot execute INSERT, DELETE, or UPDATE statements against these queues. SELECT and RECEVE (which I will discuss later in this article) are the only operations that you can perform on these queues.

Messages

A message is placed in queues. The data type of a message is varbinary(max), which is a new data type in SQL Server 2005. This new data type gives you the option of saving messages of up to 2 GB capacity. You will not able to view the message by simply invoking a SELECT statement. Messages are always received in the order in which they are sent and are guaranteed to arrive, as they cannot be lost during transmission or to any other cause. This is a major advantage over MSMQ as many developers/users complain about the sequence of message sending and receiving in MSMQ. If the message is not sent for some reason, the service will try to resend it again later, which will reduce your need to monitor the messages.

When defining a message, you can define what type of message it should contain. This provides added security to Service Broker messages. The validation types are EMPTY, WELL_FORMED_XML and VALID_XML_WITH_SCHEMA_COLLECTION. With EMPTY, you are not imposing any validation on the message. WELL_FORMED_XML means that message should have valid XML format. VALID_XML_WITH_SCHEMA_COLLECTION is the same as the class XmlSchemaCollection. XmlSchemaCollection is a cache or library where XML-Data Reduced (XDR) and XML Schema Definition (XSD) language schemas can be stored and validated. XmlSchemaCollection improves performance by caching schemas in memory instead of accessing them from a file or URL.

Dialogs

You are familiar with Queues and Messages from other messaging applications. A Dialog is a fairly new concept in SQL Server 2005 Service Broker. Some experts are calling this a Conversation as well. Whatever the name, it forms the core of Service Broker. A Dialog is the mechanism by which you are able to put your messages in queues. In a Dialog, messages are ordered and delivered in the order that they were sent. Under normal circumstances, a dialog is created for a particular task and deleted when it is completed. A Dialog occurs between two end points. End points are used to communicate with Service Broker on different SQL Server instances. End points allow Service Broker to communicate by using HTTP, TCP, or SOAP protocols. These end points are not configured by default. So you need to configure them if you plan to communicate over different SQL Server instances. Service Broker often uses port 4022 for broker-to-broker communication. You can change this if you want to, when creating end points.

Contracts

Contracts define who can send what types of messages from the initiator or receiver. You can specify multiple message types in a contract and specify who (either initiator or receiver) can send it.

Services

Services read messages from a queue and process them. A Service can be a SQL Server stored procedure or a different program that is linked with a Dialog.

Conversation Groups

As some messages are related to others, Conversation groups are used to keep them together. If one message process fails, the service will automatically roll back the related message processes.

Service Broker Architecture

Here are all the concepts and objects of Service Broker together in pictorial format:

Source: MSDN

Applications exchange messages as part of a dialog. When SQL Server receives a message for a dialog, it places the message in the queue from the service for the dialog. The application or stored procedure receives the message from the queue and processes the message as necessary. As part of the processing, the application may send messages to the other participant in the dialog.

Practice

Now it is time to get your hands dirty. First, let's define some simple business logic to demonstrate Service Broker. Let's assume that we are doing a design for online purchasing. Our requirement is such that whenever a user places an order the details are sent to the inventory system. From the inventory system, the service will read a queue and update necessary tables. (For simplicity, I assume the trading system and inventory system to be in one database. In the real world, the two systems will be on two different servers and you will have to create end points to facilitate communication.)

First we create a new database called SSSB.

USE [master]
GO
/****** Object: Database [SSSB] Script Date: 02/19/2007 22:55:11 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SSSB')
DROP DATABASE [SSSB]

CREATE DATABASE [SSSB]

Then we enable the Service Broker for this database.

ALTER DATABASE [SSSB] SET ENABLE_BROKER

Now we create an order table that will be in the inventory system.

CREATE TABLE [dbo].[tblOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ItemCode] [varchar](15) NULL,
[Qty] [int] NULL,
[OrderDate] [smalldatetime] NULL
CONSTRAINT [PK_tblOrders] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)

We have to create Service Broker objects. First we need to create a MESSAGE TYPE and a CONTRACT.

CREATE MESSAGE TYPE SendStockDetails
VALIDATION = WELL_FORMED_XML;

The SendStockDetails Message Type will send the information about the entered stock. This will have WELL_FORMED_XML as the messages are supposed to be sent in XML format.

CREATE CONTRACT [MainContract]
(
SendStockDetails SENT BY INITIATOR
);

CONTRACT specifies in what direction these message types are used.

The next step is to create a Queue (queSendStockSend) to send the stock details.

CREATE QUEUE queSendStockSend WITH STATUS= ON, ACTIVATION ( PROCEDURE_NAME = usp_updatetocks,
MAX_QUEUE_READERS = 5, EXECUTE AS 'dbo' ) ;

The queue above has two important parameters. One is the procedure name. Whenever a message is sent to the queue, the given stored procedure (usp_updatetocks) will be activated. This sp should be created before the CREATE QUEUE statement. However, for the sake of presentation, sp will be discussed later.

The other parameter, MAX_QUEUE_READERS, specifies the maximum number of instances of the activation stored procedure that the queue starts at the same time. The value of max_readers must be a number between 0 and 32,767.

We create the SERVICE by combining the QUEUE and CONTRACT.

CREATE SERVICE svrStockUpd ON QUEUE queSendStockSend ([MainContract])

Now we have to write a sp to input data into the queue. This sp will have two parameters: itemcode and qty. inside the sp, XMLmessage will be formatted and sent to the queue.

CREATE PROCEDURE [dbo].[usp_StockInfo]
@ItemCode [varchar](15),
@Qty [int]
AS
BEGIN
DECLARE @OrdDate AS SMALLDATETIME
SET @Orddate = GETDATE() -- We assume current date and time as the order date
DECLARE @Message XML
CREATE TABLE #XMLMessage
(
[ItemCode] VARCHAR(15),
[Qty] INT,
[OrderDate] SMALLDATETIME,
)

INSERT INTO #XMLMessage
(
[ItemCode],
[Qty],
[OrderDate]
)
VALUES (
@ItemCode,
@Qty,
@ORddate
)

SELECT @Message = ( SELECT * FROM #XMLMessage
FOR XML PATH('Order'),
TYPE
) ;
-- Above will fomulate valid XML message
DECLARE @Handle UNIQUEIDENTIFIER ;

-- Dialog Conversation starts here

BEGIN DIALOG CONVERSATION @Handle FROM SERVICE svrStockUpd TO
SERVICE 'svrStockUpd' ON CONTRACT [MainContract] WITH ENCRYPTION = OFF ;

SEND ON CONVERSATION @Handle MESSAGE TYPE SendStockDetails (@Message) ;
END
GO

You can observe that the service is used for DIALOG CONVERSATION.

The message will be in the following XML format.

2001
60
2007-02-21T00:32:00

First let's input some date by using usp_stockinfo.

[usp_StockInfo] 'A-200',12

You will notice that you will be returned immediately after the above executes.

If you execute SELECT * FROM dbo.queSendStockSend you can view the message data. However, you will see that message body is encrypted. (Download the code in the example above.)

Next we should create a sp to read the queue. In practice, this should be created before the queue is created, as you need to specify the activation stored procedure while creating the queue.

CREATE PROCEDURE usp_updatetocks
AS
BEGIN

SET NOCOUNT ON ;
DECLARE @Handle UNIQUEIDENTIFIER ;
DECLARE @MessageType SYSNAME ;
DECLARE @Message XML
DECLARE @OrdDate SMALLDATETIME
DECLARE @Qty INT
DECLARE @ItemCode VARCHAR(15) ;

RECEIVE TOP ( 1 )
@Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body FROM dbo.queSendStockSend;

IF ( @Handle IS NOT NULL
AND @Message IS NOT NULL
)
BEGIN
SELECT @OrdDate = CAST(CAST(@Message.query('/Order/OrdDate/text()') AS NVARCHAR(MAX)) AS SMALLDATETIME)
SELECT @Qty = CAST(CAST(@Message.query('/Order/Qty/text()') AS NVARCHAR(MAX)) AS INT)
SELECT @ItemCode = CAST(CAST(@Message.query('/Order/ItemCode/text()') AS NVARCHAR(MAX)) AS VARCHAR(15))

INSERT INTO dbo.tblOrders
(
ItemCode,
Qty,
OrderDate
)
VALUES (
@ItemCode,
@Qty,
@OrdDate
) ;
END
END

To read the queue, we have used the RECEIVE command. With the RECEIVE command you are deleting the queue record after receiving it. If you use SELECT, it will not be removed from the queue. As the message is in a binary format, we need to convert it using the CAST function. We will convert them to three separate variables and insert them into the table.

After this, if you run select * from tblOrders, you can see that data has been logged in the tblorder table by Service Broker.

Using Service Broker in SQL Server 2005

A typical example that you can provide for the Service Broker is Database Mail in SQL Server 2005. Whenever a mail request (a Message in the context of Service Broker) is placed on a queue, an external application, Databasemail90.exe activates by means of Service Broker. The main advantage of Database Mail is that the mail process is done by an external application, which reduces SQL Server overhead. This was achieved because of the Service Broker architecture.

Here are some other places where you can use Service Broker to your advantage.

• Asynchronous triggers
• Reliable query processing
• Reliable data collection
• Distributed server-side processing for client applications
• Data consolidation for client applications
• Large-scale batch processing

You can find full details in "Typical Uses of Service Broker."

Conclusion

Service Broker of SQL Server 2005 is not simply a replacement for MSMQ because more features have been added that will benefit system developers. I tried to keep this introductory example simple. The next article on Service Broker with cover advanced features such as security and endpoints.

Your feedback on this article is most welcome at dineshasanka@gmail.com.