Windows Server 2008 R2 Archives - Page 5 of 9

May 10, 2011

Patching Clustered SQL Server Instances with SQL Server 2008 and SQL Server 2008 R2

One of the things you will eventually have to do to an instance of SQL Server is apply one or more of the following: a community update (CU), service pack (SP), or hotfix. I recently wrote a whitepaper entitled “Applying Updates to a Clustered Instance of SQL Server 2008 or SQL Server 2008 R2” which can be downloaded from this link. It has all the background information you need, and includes how to do things like check for reboots, script thge installs, etc. The scripts for the whitepaper can be downloaded here.

That whitepaper describes the process on how to approach patching clustered instances of SQL Server and patching, but in its examples, covers two more difficult examples because things can be complex and those are not described anywhere else (hence me wanting to write the paper). Read the whitepaper for the background information, but I thought in this blog post I would present the most simple case: one instance on a two-node cluster. All changes will be denoted in red italics.

In this example, to start, the instance is currently owned by Node A and is at SQL Server 2008 SP1 and you are looking to update the instance to SQL Server 2008 Sp2. Both nodes can potentially own the instance at this point.

	Node A			Node B
	Owner	Possible Owner	Version	Owner	Possible Owner	Version
Instance	Yes	Yes	SP1	No	Yes	SP1

Table 1. Initial cluster state

To start the process, you will first have to remove the node which does not own the instance as a possible owner of the network name resource for the instance. This process and why you need to do it is described in the whitepaper.

	Node A			Node B
	Owner	Possible Owner	Version	Owner	Possible Owner	Version
Instance	Yes	Yes	SP1	No	No	SP1

Table 2. Cluster state after removing Node B as a possible owner

Install SP2 on the node which is not currently a possible owner of the instance. Once that it is done, it will look like Table 3.

	Node A			Node B
	Owner	Possible Owner	Version	Owner	Possible Owner	Version
Instance	Yes	Yes	SP1	No	No	SP2

Table 3. Cluster state after patching the instance on Node B

Add Node B back as a possible owner of the network name resource. The cluster will now look like Table 4.

	Node A			Node B
	Owner	Possible Owner	Version	Owner	Possible Owner	Version
Instance	Yes	Yes	SP1	No	Yes	SP2

Table 4. Cluster state after adding Node B back as a possible owner

You now need to fail the instance over to Node B to start the upgrade of the instance itself as shown in Table 5. This will be the first time you would have to schedule an outage. You should do this in a controlled fashion as to minimize the impact to end users and applications.

	Node A			Node B
	Owner	Possible Owner	Version	Owner	Possible Owner	Version
Instance	No	Yes	SP1	Yes	Yes	SP2

Table 5. Failing the instance over to Node B

The instance itself and the databases that were online will now be upgraded. The time it takes to do this will be fairly short. Technically you could allow connections after the failover, but you may want to wait until you are able to complete the patching of Node A and test full failover. That is up to you.

As with Node B, remove Node A as a possible owner of the instance’s network name as shown in Table 6.

	Node A			Node B
	Owner	Possible Owner	Version	Owner	Possible Owner	Version
Instance	No	No	SP1	Yes	Yes	SP2

Table 6. Removing Node A as a possible owner of the instance

Install SP2 on Node A as shown in Table 7.

	Node A			Node B
	Owner	Possible Owner	Version	Owner	Possible Owner	Version
Instance	No	No	SP2	Yes	Yes	SP2

Table 7. Installing SP2 on Node A

Once Node A has been patched, add it back as a possible owner as shown in Table 8.

	Node A			Node B
	Owner	Possible Owner	Version	Owner	Possible Owner	Version
Instance	No	Yes	SP2	Yes	Yes	SP2

Table 8. Adding Node A back as a possible owner of the instance

Finally, you should fail the instance back to Node A as shown in Table 8 not because you want it to “live” there (your instance should be able to run anywhere in the cluster assuming you did capacity planning correctly), but because you want to ensure that there will be no problems after the installations are done and that the instance can run on all nodes. If you have allowed connections into the instance already, you will have to schedule an outage or do it during an already scheduled outage.

	Node A			Node B
	Owner	Possible Owner	Version	Owner	Possible Owner	Version
Instance	Yes	Yes	SP2	No	Yes	SP2

Table 9. Final state

April 18, 2011

SQLU Post #1: What’s In A Name? (Part One)

I’m proud to be participating in SQL University’s High Availability and Disaster Recovery Week this week (April 18, 2011) along with Robert Davis (blog | Twitter) and Geoff Hiten (blog | Twitter). This is the first of my posts.

One of the biggest causes of downtime in my experience is how an application or user connection deals with a name (or IP address) change after a server switch. Many applications are not very tolerant of a server or name change, and that is not the fault of the backend: it’s a lack of planning in the application to be able to cope with changes on it. The backend and its administrators can only compensate for so much before it is the application’s problem. This is why application developers cannot assume availability happens by magic server dust. It’s their problem, too. If the applicaiton handles a server switch poorly, it will extend downtime.

This first of two posts will discuss how to properly name clustered instances of SQL Server. The next post will cover some of the other situations such as database mirroring and log shipping where server names may not be the same.

As a quick recap, one of the biggest benefits of a clustered SQL Server instance is that the instance keeps the same name (and IP address) no matter what node it is currently residing on. The application will have to account for the failover, which is a stop and start of SQL Server, with things like retry logic or coding the application to be cluster-aware. However, the name will always remain the same. This is good news.

However, what I see all the time with a lot of customers is that they get confused as to what names to put where and where they actually map to. This post will demystify that.

When you have a clustered instance of SQL Server, there are a lot of names in play. You have:

The name of each node, each of which is unique in the domain.
The name of the Windows failover cluster which is also unique in the domain.
The name of the SQL Server failover clustering instance (FCI) which is unique in the domain. This is true whether installing a default or a named instance. This is what applications will use to connect to.
If installing a named instance, you will also have the named part of the instance which is unique in a given Windows failover cluster, but not necessarily in the domain. Along with the FCI name, this will be used by applications if a named instance is installed.
You also need to be concerned with the value for the Instance ID, which I will get to in a little bit.

Confused yet? I hope not. Even if you are, here’s a simple example:
You are configuring a two node cluster for SQL Server. The node names are DENNIS, TOMMY, and JY and are in the STYX domain. When you configure the Windows failover cluster, you give it a name of ROBOTO. When you configure the first instance, you configure it as a default instance with a name of EQUINOX. You then configure two more instances, CRYSTALBALL and GRANDILLUSION. This all seems straightforward, but getting there is part of the issue.

Assuming you’re going to use the interface for SQL Server Setup, the screen below from SQL Server 2008 R2 is the one that will get you into trouble if you do not enter things correctly. The screens are similar in both SQL Server 2008 RTM and Denali. While not totally the same in SQL Server 2005, the concepts are the same.
Figure 1. Instance Configuration dialog in SQL Server 2008 R2 Setup

I’m going to break down each value relating to names that can be entered.

SQL Server Network Name
SQL Server Network Name is exactly what it sounds like: it’s what Windows cares about and is used in a variety of places as shown below. The value for Network Name can be changed after installing a clustered instance of SQL Server by modifying it in the Windows failover cluster. This may cause a brief outage since it touches so many things and clients would need to have DNS updated accordingly. Depending on how long the time to live is for a DNS entry, it could be quite some time if left to automatically refresh.

1. The value is used as the basis of the corresponding network name in the Windows failover cluster.
Network Name via PowerShell Figure 2. Network Name for the default instance EQUINOX in PowerShell

Network Name at the Group Level in Failover Cluster Manager Figure 3. Network Name for the default instance EQUINOX at the Group Level in Failover Cluster Manager

Figure 4. Network Name details for the default instance EQUINOX in Failover Cluster Manager

2. The network name for the clustered instance in the Windows failover cluster is used for the virtual computer object (VCO) corresponding to the clustered SQL Server in Active Directory.
Figure 5. The EQUINOX VCO in Active Directory

3. The network name for the clustered instance in the Windows failover cluster is used in DNS.

Figure 6. EQUINOX in DNS

Named Instance
Instance behavior is a bit different on a cluster. When you connect to a clustered instance of SQL Server, you do not connect to a node name nor do you connect to the Windows failover cluster name. You connect to the name you give it for the Network Name and if using a named instance, the named instance. For example, the EQUINOX instance is a default instance so applications and end users would use EQUINOX. It looks like Figure 7 in Failover Cluster Manager. Note that there is nothing next to the SQL Server or SQL Server Agent resources denoting what kind of instance it is; this is indicative of a default instance.

Figure 7. Default clustered instance in Failover Cluster Manager

To further see this is a default instance, look at the Properties tab of the SQL Server resource as shown in Figure 8. You will see the VirtualServerName (virtual server is older naming convention for the combination of clustered resources that have a name, IP, and disk resource that was retired when Microsoft shipped the Virtual Server product) value which is the same as the network namer and an InstanceName value of MSSQLSERVER. The SQL Server Agent resource will also show something similar, as seen in Figure 9.

Figure 8. Properties for the SQL Server resource for a default instance

Figure 9. Properties for the SQL Server Agent resource for a default instance

Things are a bit different for a named instance. In Figure 10, you will see the list of resources for the instance CRYSTALBALL. Note the (BALL) next to both the SQL Server and SQL Server Agent resources.

Figure 10. Named clustered instance in Failover Cluster Manager

Similar to a default instance, if you look at the properties for the SQL Server and SQL Server Agent resources (as shown here in Figures 11 and 12), you will see the VirtualServerName maps to the network name as expected and the value entered for Named Instance is reflected in the InstanceName value.

Figure 11. Properties for the SQL Server resource for a named instance

Figure 12. Properties for the SQL Server Agent resource for a named instance

Assuming you will be installing a named instance of SQL Server in your cluster, the named portion must be unique for the particular Windows failover cluster you are installing the instance into. You can reuse the named portion in another Windows failover cluster.

Warning: Be careful – if you screw up the named instance name, you cannot change it post-install as you can the network name. To correct the problem would be an uninstall and reinstall, which is clearly something you do not want to do.

Where I find customers get confused is that they will enter the same name for the Network Name as they will for for Named Instance. For example, EQUINOXEQUINOX. Don’t do that. Both parts should be unique, such as CRYSTALBALL. Why is this important? Technically you can use the CRYSTAL value for connecting in some places which may make it seem like a default instance. However, when you want to go configure things like replication, you need to know the proper full named instance. This happened to me recently when working with a customer. I was configuring replication on their clustered instance and they didn’t tell me it was a named instance and replication was giving me errors. I went into Failover Cluster Manager and determined the right name for the instance. You can easily figure this out yourself by using the sample screens above.

Instance ID
Instance ID is not a new, but yet a new, concept. Prior to SQL Server 2008, SQL Server when installing multiple instances enumerated the directories used for the dedicated binaries by number. Clearly that was not the most descriptive thing to do since you could not necessarily tell which binaries you were looking at. In SQL Server 2008 and later, this value can be set by you during Setup. It cannot be changed after, so you definitely have to get this right the first time. If you are installing a default instance, the default value for Instance ID is MSSQLSERVER and if you are installing a named instance, it is the name you specified for the named instance (for example, THEATER for the instance shown above).

However, even that on a cluster is not necessarily the best thing to do since you generally don’t access an instance via MSSQLSERVER or BALL, and the names are different than the nodes (for example, you wouldn’t connect using DENNISBALL). So what I recommend is that you use an Instance ID that is reflective of the name you are actually going to use, such as CRYSTAL_BALL. Figure 13 shows what things look like under the Program FilesMicrosoft SQL Server folder with properly named Instance IDs for clustered instances.

Figure 13. Instance IDs for multiple clustered instances

Summary
Names matter when you configure your clustered instance. Knowing the difference between the different configurable values versus the ones in the operating system will help you get things deployed properly the first time.

February 7, 2011

Scripting the Addition and Removal of Cluster Nodes as Possible Owners of a Resource

Currently I’m writing a whitepaper on how to best approach installing updates on a multiple instance failover cluster. I’m using Windows Server 2008 R2 as the underlying operating system. As part of my testing, I’m automating a bunch of things via a combination of PowerShell scripts and scripts to run the SP install without having to do anything in the GUI. The scripts will be available as part of the whitepaper.

As some of you may know, part of the process for installing a SP or CU on a clustered instance of SQL Server is removing some nodes as possible owners. This is done on the Network Name resource. You have three ways of accomplishing this task: GUI (Failover Cluster Manager), cluster.exe (deprecated in Windows Server 2008 R2, but it’s still there for now), and in PowerShell. Because cluster.exe is going away, I’m using all PowerShell. Doing things in the GUI is more time consuming, and as I said, I want to automate as much as possible.

I wrote about this in my book Pro SQL Server 2008 Failover Clustering in Chapter 8. However, I did show some wrong screen shots for PowerShell – I showed removing possible owners of the SQL Server resource with PowerShell. That is wrong. I showed it correctly with cluster.exe. So that’s one errata for the book. I was also using the beta of Windows Server 2008 R2. Now that it’s released, I can update you here with the new stuff.

My cluster has three nodes: DENNIS, TOMMY, and JY. I’m currently in the midst of updating the EQUINOX instance from SQL Server 2008 SP1 to SQL Server 2008 SP2. I can remove TOMMY and JY from the network name resource’s possible owners with no problem using PowerShell. Set-ClusterOwnerNode has an -owners option, and my understanding is that it was always implicit. It is … but only sometimes. Let me show you.

Below is adding the nodes working as expected using cluster.exe (which I do not want to use; this syntax currently works on Windows Server 2003, Windows Server 2008, and Windows Server 2008 R2).

Adding the nodes back using cluster.exe

Let’s see what happens in PowerShell.

First, here is the removal of the other nodes as a possible owner so I can do the patching on those nodes. Note that I do not use the -owners clause. I apologize if the formatting of the following pictures looks a little weird on your screen.

Removing nodes to patch

Things are working as expected. So far, so good. Now, let’s try to add the nodes removed back as possible owners.

First, set the nodes to all three not using the -owners clause.

Adding the nodes back not using -owners with error

Clearly, that didn’t work. Now let’s try it with the -owners clause.

Adding the nodes back successfully with -owners

Yay! All is right with the world again.

Now, what happens if you use quotes around each node?

Putting quotes around each node

As expected, this works just like it would if the nodes were not encased in quotes.

This whole -owners inconsistency is not a hindrance, and clearly you can get everything to work just fine.

However if you just use the list all encased in quotes, you will encounter this:

Error when specifying all nodes encased in quotes

What happened? Well, it not only didn’t add or remove the two nodes I wanted, but it removed ALL of the nodes as possible owners because -owners parses everything encases in quotes and expects it to be a node name. There is no node named DENNIS,TOMMY,JY so it removes all nodes as possible owners. It seems to treat it like a NULL or something like that.

This is behavior I hope is changed in a future version of Windows so that it will leave the current configuration alone or only allow removal/addition of nodes which currently do not own the resource. That way the resource, and possibly the resource group if there are dependencies (such as SQL Server being dependent on the Network Name), won’t accidentally go offline.

So what are the lessons to be learned?

Be careful if using PowerShell to add or remove nodes and using quotes around the nodes. Doing that wrong may have unintended consequences.
Adding and removing nodes using PowerShell is nearly opposite of what you would think. Since it is a single command, you specify all nodes you want to own that resource. With cluster.exe you had separate switches for adding and removing, so it was a bit more intuitive.

October 18, 2010

Adding .NET Framework to Windows Server 2008 R2 Part One – Command Line and PowerShell

Something which I don’t think I covered before but if I have, it bears repeating. I’m updating the chapter I wrote for the SQL Server 2008 upgrade guide for SQL Server 2008 R2, and finally got around to documenting how to properly install .NET Framework 3.51 on Windows Server 2008 R2 so you can install SQL Server 2008 or SQL Server 2008 R2. This blog post will cover how to install it using a command prompt and Part Two will cover the GUI, and you’ll see why the command line are PowerShell ways are better and less confusing. These instructions will also help you create deployment scripts, which will help you automate the entire process.

You should not need to reboot to install .NET 3.51 under normal conditions.

Note: The instructions assume the user executing have the right permissions to install features.

Why are things different on Windows Server 2008 R2? If you try to run the standalone installer package that ships with SQL Server 2008 or SQL Server 2008 R2, you will see this message:

Error when trying to install .NET using the package

It’s not the best written message as the tool it tells you to use is wrong, but that’s OK. Don’t worry about that. You’ll see what I mean in Part Two.

Normal Command Prompt
The first method I’m going to show you on how to install .NET Framework 3.51 is using the dism command, which can be done in any Command Prompt window. The command you want to enter is:

dism.exe /online /enable-feature /featurename:NetFx3

Below is a sample execution:

Installing .NET 3.51 using dism

If you’re wondering why I got the reboot prompt, it’s probably because I had just done the PowerShell way (see below) and didn’t reboot yet.

PowerShell
If you haven’t hear me say it before, I’ll say it again: learn PowerShell. It’s the way of the future for command line-based administration and tools. You’ll be using two cmdlets: Get-WindowsFeature and Add-WindowsFeature.

First, you will execute the command:
Get-WindowsFeature NET*

This will show you the status of whether or note what you want is installed. Note that in the sample execution below, there is no X next to any of the .NET-related features. Now, also note that there are multiple options for .NET features. For SQL Server, you only need the .NET Framework. As you will see in Part Two, if you try to install everything, you’ll get things you may not want nor should you install in most SQL Server installs (especially on a cluster).

Once you verify that .NET 3.51 has not been configured, you will execute this command:
Add-WindowsFeature Net-Framework-Core
Note that the Net-Framework-Core is the name Windows uses internally; don’t try to use the friendly name (the Display Name column) from the output of Get-WindowsFeature. Use the value from the Name column.

Finally, re-run Get-WindowsFeature NET* and you will see everything is configured.

Below is a sample execution:

Installing .NET 3.51 Using PowerShell

September 14, 2010

Schedule and Other Updates

Sorry it’s been so long – I have had a crazy busy summer! Just a quick note here to update you on a few things.

Schedule Updates

I’ll be speaking at the New York City SQL Server User Group next Thursday, 9/23. Check out my Schedule for details.
I’m doing a high availability webcast next month. Details are being locked down.
I’ll be speaking at PASS in November.
I have a few abstracts for submission to the NYC SQL Saturday in November. Once I know what is accepted, I’ll post more details.

Other Updates

I was renewed as a Cluster MVP! I’m very psyched and I thank those who made that decision.
I’ve got quite a few papers in the works. As soon as they’re published, it’ll be posted here.
I’ve got some new training in the works as well … again, details to follow!

It’s going to be an exciting, but busy end of the year. Hope to see you at one of the events …

« First ‹ Prev 2 3 456 7 8 Next ›Last »

Categories