cluster

Managing Microsoft Message Queuing in a cluster remotely

Ever had the problem when you’ve only got remote access to your cluster nodes and you need to modify a clustered MSMQ instance, but the tools just won’t connect to the correct instance, even when you’ve tried the set computername command prompt trick, you can’t use mstsc /console or you’re stuck on Windows 2008R2 and you don’t have the manage option on right click in Failover Manager.

Make your life so much easier by getting a nice simple free tool from Microsoft MMCV –

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Syncing SQL Server databases ready for DB Mirroring

Whilst Database mirroring is now deprecated with the release of SQL Server 2012 (http://msdn.microsoft.com/en-us/library/ms143729%28v=sql.110%29.aspx) it’s still something I’m going to be using heavily with 2008 and 2008R2 as it’s a simple way of implementing failover between discrete clusters or instances. They’ll move to Availability groups eventually, but not in the short term as there’s no direct way to do this without building a new Windows cluster that contains the Fallover Clusters

However, with a database with frequent transaction log backups (and if it’s important enough to mirror, it’s almost certainly important enough to deserver frequent log backups) it can take a bit of work to get everything synced up again. Or catching back up if you’ve had to break the mirror for any reason.

The first script is to sync up from scratch. It connects back to the primary server to get the back information. It assumes that:

  • You’re running it as an account that exists on both instances with sysadmin role granted
  • You don’t already have a linked server with the name of the primary instance on the secondary
  • The file structure is identical on the 2 instance
  • All backup files (log and db) are available in their main location (ie; not taken to tape and deleted)

declare @last_lsn numeric(25,0), @first_lsn numeric(25,0), @@tmp_lsn numeric(25,0) declare @dbname varchar(50), @restorefile varchar(255), @oserver nvarchar(50), @lserver nvarchar(50)

--Set server name twice. First is used for building db links, 2nd is used in the actual query to hide special characters set @oserver='server1\instance' set @lserver='[server1\instance]' set @dbname='bigdb1'

EXEC master.dbo.sp_addlinkedserver @oserver, @srvproduct=@oserver, @provider=N'SQLNCLI', @datasrc=@oserver EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@oserver,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

exec(N'select c.physical_device_name, a.last_lsn, a.first_lsn into ##mirror from '+@lserver+'.msdb.dbo.backupset a, '+@lserver+'.msdb.dbo.backupmediaset b, '+@lserver+'.msdb.dbo.backupmediafamily c where a.database_name='''+@dbname+''' and a.media_set_id=b.media_set_id and b.media_set_id=c.media_set_id and a.last_lsn > (select max(first_lsn) from '+@lserver+'.msdb.dbo.backupset where database_name='''+@dbname+''' and type=''D'') order by last_lsn asc') declare restore_cursor cursor for select physical_device_name, last_lsn, first_lsn from ##mirror order by last_lsn asc open restore_cursor fetch next from restore_cursor into @restorefile, @last_lsn ,@first_lsn while @@FETCH_STATUS = 0 begin --comment out the exec line and uncomment the print line if you want to check the restore commands first

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

List of SQL Server instances and version on Cluster Nodes

With a large windows cluster with many installed SQL Server instances it can get tricky to quickly check which instances are on which nodes, and a what patch level they’re running at. It’s also a good idea to check what revision level the instances think they’re on after a patching session.

In the grand tradition of lazyefficient DBAs I don’t want to be manually checking each node and instance for this information. So I use the following Powershell script:

Import-Module FailoverClusters
$Cluster=$args[0]
$clustnodes = Get-ClusterNode -Cluster $Cluster

foreach($node in $clustnodes){
echo "$node`n------------------"
	$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $node)
	$regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL")
	foreach($val in ($regkey.GetValueNames() | sort)){
		$regKey2= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\$val\\MSSQLServer\\CurrentVersion" )
		echo "$val `t`t $($regkey2.GetValue("CurrentVersion"))"
	}
echo "`n`n"
}

This can then be run as:

.\clust-version.ps1 my-clust

The returned results are in plain text, and need to be pairedup. But unless you’re a *LOT* of instances that’s the easy bit.

sql-01
------------------
INSTANCE1		10.50.1600.1
INSTAMCE2		10.0.2531.0
INSTANCE3		10.0.2531.0
INSTANCE4		10.50.1600.1

sql-02
------------------
INSTANCE1		10.50.1600.1
INSTAMCE2		10.0.2531.0
INSTANCE3		10.0.2531.0
INSTANCE4		10.50.1600.1

sql-03
------------------
INSTANCE1		10.50.1600.1
INSTAMCE2		10.0.2531.0
INSTANCE3		10.0.2531.0
INSTANCE4		10.50.1600.1
Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Matching windows cluster node ID to physical server

So you’ve built your Windows 2008 Cluster, and you did it sensibly with good logical names like

  • cluster-node-01
  • cluster-node-02
  • cluster-node-03

It’s been running quite happily, you’ve extended it and upgraded it. But now you’ve got a problem, a resource won’t start up when it’s moved to a new node. So you’ve followed the instructions to get the last 5 minutes of Cluster logs by running:

C:\>cluster log /gen /span:5

And you’ve found that the error is something to do with:

00000790.0000545c::2012/01/09-11:16:15.778 ERR   [RCM]s_RcmRpcGetResourceState: ERROR_CLUSTER_GROUP_MOVING(5908)' because of ''Cluster Disk 3' is owned by node 4, not 6.'

But wait a second, what’s this node 4 and 6 business? And which node is the one causing the problem?

The simplest way to find out which node is which is to simply drop to a cmd prompt on one of the nodes and:

C:\>cluster node /status
Listing status for all available nodes:

Node           Node ID Status
-------------- ------- ---------------------
cluster-node-04      1 Up
cluster-node-02      4 Up
cluster-node-01      5 Up
cluster-node-03      6 Up

C:\>

And there you have a nice little lookup table for you.

But why aren’t they aren’t the in nice logical order you installed them in? Well, remember that time you had to evict cluster-node-01 because of the main board fault and rebuilt it on new hardware? Or when you upgraded the hardware by adding in a new ‘better’ node and then removed/replaced the nodes 1 by 1.

Every time the Cluster sees a ‘new’ node it assigns it a shiny new Node number. So when you first built the cluster it was a nice correlation. But the good news is that as it’s going to change fairly infrequently it’s pretty easy to keep a track of it.

 

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike