Ryan Adams Blog

SQL, Active Directory, Scripting

I recently had the need to move the quorum for a cluster to a new SAN drive.  It’s a quite simple thing to do, but some of the options and locations of the cluster resources itself are a little hidden in Windows 2008.  Let’s just walk through the basic steps.

The first thing you need to do is get the new drive added to the cluster and put into the proper group.  If the drive is being presented from a SAN and you have multiple paths to that SAN, then you will need to setup whatever multi-pathing software you have.  If you’re not sure, then an easy test is to open Window Disk Management and see how many new drives are there.  If there are more drives there than you are supposed to have, then you need to setup your multi-pathing.  Once you get that setup and reboot, you should now see the correct number of drives.  If you are on a cluster, you need to do this on each node in the cluster.

Now that your drives are correctly presented to the OS, it’s time to bring them online and format them.  You can do this from the node in the cluster that your services are currently running on and you only have to do this on that one node.  Right click the drive in Windows Disk Management and select online.  Now that the drive is online you need to right click it again and select initialize.  Finally you need to format it by right clicking the drive and selecting New Simple Volume.  Walk through the wizard giving it a drive letter and formatting it with a label.  Don’t worry about what drive letter you assign, as we can change that later.

You are now ready to add the drive to the cluster.  Open the Cluster Manager MMC, expand the cluster, and select storage in the left hand pane.  In the right hand pane you will see all of your cluster groups with their assigned storage below the group.  Select Add Disk in the actions pane and select the disk to add.  You will now see a group called Available Storage where you will find your new drive.

At this point everything is now setup and ready for the move.  In the left hand pane, click the cluster name at the top of the list.  In the actions pane go to More Options and select Configure Cluster Quorum Settings.  Follow through the wizard and the only thing you need to change is on the Configure Storage Witness page.  On this page, simply select the check next to the new storage disk.  You have now successfully moved your cluster quorum to another drive, but let’s verify it.  Open Windows explorer and view the new drive.  You should now see a folder called Cluster, which verifies the move.  Just to be clear, what you have really done is not move the quorum but created a new one.

If you want to reuse the old drive letter (Typically Q:), then right click the old drive under the Available Storage group and select Change Drive Letter.  Now you can right click the old drive and select delete, which will remove it from the Available Storage group in Cluster Manager.  In order to assign your new drive to the previously used drive letter, simply right click it in Cluster Manager and select Change Drive Letter.  The old drive letter will now be available, so simply select it.  After changing the drive letter you now need to restart the cluster service or run the following command from an elevated command prompt to move it to another node.

cluster group “cluster group” /move:nodename

More Information – If you are moving your cluster quorum drive you might want to check out the following posts:

How to move cluster MSDTC

How to move the Master database

How to move the Model database

How to move the MSDB database

How to move the TempDB database

SQLAgent Error 435

Do you get error 435 in the Application event log and your SQLAgent fails to start?  The reason you’re getting this error is that the SQLAgent error log and working directory file locations are not available.

I recently ran into this after moving the user and system databases to a different SAN drive.  Even though you tell SQL where you moved MSDB, you still have not told it the new location for the error log and working directory.  SQL stores these locations in the registry and you will have to update it.  Here are the locations for the keys you need to update.

*** NOTE *** If you are on a cluster, make sure you update the registry on every node in the cluster.

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERSQLServerAgentErrorLogFile

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERSQLServerAgentWorkingDirectory

More Information – If you have this problem then you might want to check out the following posts:

How to move the Master database

How to move the Model database

How to move the MSDB database

How to move the TempDB database

How to move cluster Quorum drive

How to move cluster MSDTC drive

Do you have a policy that is supposed to include “Every Database” as part of its condition, but for some reason it never includes the system databases?  The “Every” condition is a built-in condition that cannot be changed and does not include system databases.

If you want to include the system databases in your policy you need to create a new condition that includes both user and system databases.  Once you have created the condition you will want to go back to any policies that need this change and alter the target condition to point to your newly created condition.  Here is what that condition might look like:

User and System Database Condition

 

TIP:

Normally you can click the hyperlink in your “Against Targets” condition to open and edit the condition.  If you click the hyperlink and the condition does not open, then you know it is a default system condition.  Go ahead and click on the “Every” database condition and you will see that a whole lot of nothing happens.

Note that TempDB is recreated every time SQL starts.  Why is this important?  It means we don’t have to move the physical file on the file system.

First we need to know the name and file location of the TempDB database files.  When you change the location, make sure to keep the same name.  Technically we don’t need the current physical path, but it’s just a good idea to have it documented in case things go badly.

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO

Next we need to tell SQL where we plan to move the files.  Make sure to change the “FILENAME” path and ensure the “NAME” is the same as what you got from the above query.

USE master;
GO
ALTER DATABASE TempDB
MODIFY FILE (NAME = Tempdev, FILENAME = ‘Y:MSSQL10.MSSQLSERVERMSSQLDATAtempdb.mdf’);
GO
ALTER DATABASE TempDB
MODIFY FILE (NAME = Templog, FILENAME = ‘Z:MSSQL10.MSSQLSERVERMSSQLDATAtemplog.ldf’);
GO

Now all we need to do is stop and restart SQL server.  Once SQL is back up you might want to run the first query again just to make sure everything went as planned and the location has been updated properly.  Also, don’t forget to clean up after yourself and delete the old database files.

More Information – If you are moving your TempDB database you might want to check out the following posts:

How to move the Master database

How to move the Model database

How to move the MSDB database

SQLAgent Error 435

How to move cluster Quorum drive

How to move cluster MSDTC drive

First we need to know the name and file location of the MSDB database files.  When you change the location, make sure to keep the same name.  Technically we don’t need the current physical path, but it’s just a good idea to have it documented in case things go badly.

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N’msdb’);
GO

Next we need to tell SQL where we plan to move the files.  Make sure to change the “FILENAME” path and ensure the  “NAME” is the same as what you got from the above query.

USE master;
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData, FILENAME = ‘Y:MSSQL10.MSSQLSERVERMSSQLDATAMSDBData.mdf’);
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBLog, FILENAME = ‘Z:MSSQL10.MSSQLSERVERMSSQLDATAMSDBLog.ldf’);
GO

We need to shut down SQL.  Once SQL is stopped, copy the physical MDF and LDF to the new file system location.

Now we are ready to restart SQL server.  Once SQL is back up you might want to run the first query again just to make sure everything went as planned and the location has been updated properly.  Also, don’t forget to clean up after yourself and delete the old database files.

More Information – If you are moving your MSDB database you might want to check out the following posts:

How to move the Master database

How to move the Model database

How to move the TempDB database

SQLAgent Error 435

How to move cluster Quorum drive

How to move cluster MSDTC drive

First we need to know the name and file location of the Model database files.  When you change the location, make sure to keep the same name.  Technically we don’t need the current physical path, but it’s just a good idea to have it documented in case things go badly.

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N’model’);
GO

Next we need to tell SQL where we plan to move the files.  Make sure to change the ”FILENAME” path and ensure the “NAME” is the same as what you got from the above query.

USE master;
GO
ALTER DATABASE model
MODIFY FILE (NAME = modeldev, FILENAME = ‘Y:MSSQL10.MSSQLSERVERMSSQLDATAmodel.mdf’);
GO
ALTER DATABASE model
MODIFY FILE (NAME = modellog, FILENAME = ‘Z:MSSQL10.MSSQLSERVERMSSQLDATAmodellog.ldf’);
GO

We need to shut down SQL.  Once SQL is stopped, copy the physical MDF and LDF to the new file system location.

Now we are ready to restart SQL server.  Once SQL is back up you might want to run the first query again just to make sure everything went as planned and the location has been updated properly.  Also, don’t forget to clean up after yourself and delete the old database files.

More Information – If you are moving your Model database you might want to check out the following posts:

How to move the Master database

How to move the MSDB database

How to move the TempDB database

SQLAgent Error 435

How to move cluster Quorum drive

How to move cluster MSDTC drive

The catch to moving the Master database is that you must also move the Resource database.  Microsoft states that the Resource database must reside in the same location as the Master database.  You can see their instructions HERE on how to move the Master database.

However, on my cluster I did not find the Resource database in the same location as Master.  In fact I didn’t find it there on any of the instances I have running on my laptop either.  I actually found it in the local disk of each node in the cluster.  Here is where I found mine, and if you don’t find yours there, then just do a search in all drives with Windows Explorer.

D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn

Now that we have the file location for the Resource database files we need to verify the file location of the master database as well.  Here is the query we can use to get that:

SELECT physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N’master’);
GO

First we will focus on moving the Master database, so we need to tell SQL where we plan to move the master database files.  SQL stores the location of the Master database in the registry, but it’s best to use SQL Server Configuration Manager to make the change.  Open Configuration Manager and select the “SQL Server Services” node on the left.  Next we want to right click and select properties on the SQL Server Service for the instance we are changing.  In the properties dialog box we want to select the Advanced tab and take a look at the Startup Parameters option.

SQL Startup Parameters

By default there are 3 startup parameters. “-d” specifies the location of the Master database data file.  “-l” specifies the location of the Master database log file.  “-e” specifies the location of the SQL Server error log file.  Here is what it looks like on my laptop for the default instance.

-dC:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLogERRORLOG;-lC:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAmastlog.ldf

Go ahead and change the location of the Master database data and log file locations and click OK.  You can also change the error log location at the same time if you need to.

We need to shut down SQL.  Once SQL is stopped, copy the physical MDF and LDF to the new file system location.  Remember when I said my resource database was in a local drive on my cluster nodes, even though the Microsoft article said it HAD to be in the same location as Master?  If that is the case for you, then you can leave it alone and just go ahead and restart SQL Server.  However, if your Resource database was indeed in the same place as Master then read on to see how to get that moved.

When we start SQL back up we now have to start it in recovery mode to change the location of the Resource database.  We can do that by running a command prompt as an Administrator and running the following command.  Type this command exactly as you see here.

NET START MSSQLSERVER /f /T3608

Next we need to tell SQL where we plan to move the Resource database files.  Make sure to change the “FILENAME” path but leave the ”NAME” the same as what you see below.

USE master;
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME = data, FILENAME = ‘Y:MSSQL10.MSSQLSERVERMSSQLDATAmssqlsystemresource.mdf’);
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME = log, FILENAME = ‘Z:MSSQL10.MSSQLSERVERMSSQLDATAmssqlsystemresource.ldf’);
GO

Don’t shut down SQL yet.  Go ahead and copy the physical MDF and LDF to the new file system location, and then run the following command.  The system Resource database must be put back into a read only mode.

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

Now we can shut down and restart SQL server.  Once SQL is back up you might want to run the first query again just to make sure everything went as planned and the location has been updated properly.  Also, don’t forget to clean up after yourself and delete the old database files.

More Information – If you are moving your Master database you might want to check out the following posts:

How to move the Model database

How to move the MSDB database

How to move the TempDB database

SQLAgent Error 435

How to move cluster Quorum drive

How to move cluster MSDTC drive

You cannot add a connection to your CMS server, on your CMS server.  Well that is what I thought until SQLSaturday in Houston, where John Sterrett (Blog|Twitter) pointed out that you can use an IP address.  I think Microsoft prevents this primarily because you already have a connection defined for it simply by virtue of it being your CMS.  However, you may have the need to include it in a folder for the purpose of multi-query or PBM policy evaluations.  I decided to do a little more research on the matter to better understand the internals.  First let’s walk through the issue, then figure out how Microsoft is preventing it, and finally look at our workaround options.

The Issue

In the example below you can see that the server named “File2” is our CMS server at the root of the tree, and you can right click on it to perform several CMS actions.

 CMS Registered Servers

If you double click it, a connection to File2 and only to File2 will be automatically opened in your object explorer window.  However, if you right click and select Object Explorer it will open connections to every server in your CMS in the object explorer window.  The same will occur against all servers in your CMS if you select “New Query” or “Evaluate Policies”.  If you want to perform any of these actions against the CMS server alone then you will notice an option in the right click context menu called “Central Management Server Actions”.  Choosing options from there will execute against the CMS alone and not every server connection defined within your CMS.

If your CMS (File2 in our example) is also a production SQL Server in the East region, we might need to add it to that folder.  If we attempt to do that, here is what we get:

 CMS Registration Error

How it is prevented

When you use the GUI to add a server to CMS, it passes the parameters that you define to following stored procedure:

msdb.dbo.sp_sysmanagement_add_shared_registered_server

If you were to look at that stored procedure, you would see several checks on the server name that is passed to it.  It is designed to disallow you adding a server with the same name as the CMS.  It also will not let you add a server named “.”, “local”, or “localhost”.  The check it uses to ensure you do not add a server connection with the same name as the CMS is to compare the name you passed to @@servername.  The @@servername system variable holds the NetBIOS name of the machine, and in our case that is File2.

If you are curious, here is the part of the above stored procedure that performs the server name check.

    IF (@server_name IS NULL) 
    BEGIN 
        RAISERROR(14618, -1, 1, '@server_name'
        RETURN(1)    
    END 

    set @server_name = LTRIM(@server_name) 
    set @server_name = RTRIM(@server_name) 

    — Disallow relative names 
    IF (‘.’ = @server_name) OR 
        (1 = CHARINDEX(N’.’, @server_name)) OR 
        (1 = CHARINDEX(N’LOCALHOST’, UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS))) OR 
        (UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS) = ‘LOCALHOST’) OR 
        (UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS) = ‘(LOCAL)’) 
    BEGIN 
        RAISERROR (35011, -1, -1) 
        RETURN (1) 
    END 

    IF (UPPER(@@SERVERNAME collate SQL_Latin1_General_CP1_CS_AS) = UPPER(@server_name collate SQL_Latin1_General_CP1_CS_AS)) 
    BEGIN 
        RAISERROR (35012, -1, -1) 
        RETURN (1) 
    END 

Workaround Options

What does all that mean and how can you circumvent it?  It’s quite simple.  We just need to add the connection with another name that will resolve to our server other than the NetBIOS name.  The easiest method is to add it using the server’s IP address (local loopback address can also be used).  Of course that does not look very pretty in our CMS console, but we can alias it when creating the connection.  Here is how we do that:

 New Server Registration

We have two other options for alternate names.  As you can see in the above screen shot, we are in a domain named “Camelot”.  This means we can use the FQDN of file2.camelot.com.  The last option we have is to create an alias or CNAME record in DNS.  Here is what it would look like if we added it twice in the East Production group using the IP address for one connection and the FQDN for the other:

 CMS Registered Servers 2

The syspolicy_purge_history SQLAgent job is used by Policy Based Management in SQL server to clear out the policy evaluation history kept by PBM.

The job consists of the following three steps.

  1. First to check to see if PBM is enabled and exit if it is not.
  2. Run the msdb.dbo.sp_syspolicy_purge_history system stored procedure to clear the history
  3. Use PowerShell to clear the system health phantom records

If you’re not a PBM user you might not have any idea what this job does (until now).  This job might be failing on you and the error is seen in step 3.  If this describes you then you are also probably running your SQL instance on a cluster, and that’s why we see this job fail.

The job is created by default during the SQL server install and SQL uses the system name in step 3.  It’s not a problem on a standalone SQL server, but it’s a problem on a cluster.  When you address a SQL instance running on a cluster you don’t connect using the names of the physical cluster nodes.  You have to use the virtual cluster network name given to that SQL instance on the cluster.

The fix is very simple.  You just need to open the job and edit step 3 to replace the system name with the cluster virtual name.  You don’t even need to know PowerShell, so don’t panic if you don’t.  Simply replace the server name with the virtual cluster network name for you SQL instance.  Here’s an example of the PowerShell line.

(Get-Item SQLSERVER:SQLPolicyMyServerNameDEFAULT).EraseSystemHealthPhantomRecords()

T-SQL Tuesday

This month’s T-SQL Tuesday is hosted by Allen Kinsel (Blog|Twitter) and covers “disasters and recovery”.  My favorite DR solution is mirroring.  Mirroring is easy to setup and fast, if implemented properly.  It’s a database level solution that allows you to keep a remote copy of your database in sync with your principal database.  Everything is kept in sync, transaction by transaction, in near real time.

Here are some tips and tricks for your mirroring environment:

  • If you use mirroring in SQL 2005, I highly suggest you upgrade to 2008.  I have seen several cases where OLTP systems had to use asynchronous mirroring due to load with SQL 2005, but the added log stream compression in 2008 allowed the use of synchronous mode.
  • Did you use the GUI to configure your mirroring solution?  Then you really need to look into the T-SQL way.  There are several options the GUI doesn’t show you that you need to know about like dedicating mirror traffic to a specific NIC Card.
  • Have you checked to make sure everything on your principal has also been configured on your mirror for when a failover occurs?  You need to make sure things like the database owner, CLR, linked servers, and Logins are all setup and ready to go on the mirror server.
  • Are you using a witness for automatic failover?  You should double check that your application has the partner setting in its connection string or it will not be able to find the mirror.

Last month I flew out to Tucson, AZ to film a few training sessions for SSWUG.ORG.  One of my sessions was on mirroring as part of their SQL Disaster Recovery Expo.  The timing for this event and this month’s T-SQL Tuesday topic is perfect.  The expo starts this Friday June 17th, 2011.  If you are planning a DR solution I highly suggest you check out their expo.

The expo is delivered online and split into two tracks.  The premium track is $39 for SSWUG members and $49 for non-members.  That’s really cheap for an entire day of training from industry leading SQL experts.

They also have a “Best of SSWUG” track that is absolutely free and features content from experts like Kevin Kline (Blog|Twitter).

If you’re considering mirroring in your environment and want to learn more tips and tricks like the ones above, then make sure you register for this expo and watch my session.  I will also be online in the chat and available to answer any questions you may have.

In my session you learn how mirroring can fit into an HA/DR solution, and we also discuss the other options that are available.  You can’t choose the right solution if you don’t know what the other options are, because there is no one size fits all.  In the session I also walk you through how to configure mirroring both through the GUI and T-SQL.  You will not want to miss seeing the differences.  We even take a brief look at how to monitor your mirroring configuration after you get it up and rolling.

I hope to see you there!

REGISTER <<<< REGISTER NOW!