Ryan Adams Blog

SQL, Active Directory, Scripting

I will be presenting two sessions at Dallas TechFest!  I am honored and excited to be speaking.  Dallas TechFest is a premiere technology conference that includes many technology areas like .Net, Java, Windows Phone, and of course SQL Server.  This year the conference has grown and expanded from one day to two days, and is being held at the University of Texas at Dallas on August 12th and 13th.

If you are looking to cram your head full technology greatness from some of the best speakers around, then this is the conference for you.  The conference does have a cost, but it’s only a $100 for two days of networking and the best in technology information.  That is a great value for two days of training, but let’s make it even better by giving you half off the price by using the code “TwoDays”.  That is two days of training and lunch for only $50!  Ooooh, I forgot to mention that lunch is included?….Your welcome.

Check out the Dallas TechFest website, and you can also Register Here.  Make sure to check out my sessions; here is what I will be presenting:

Manage your shop with CMS and Policy Based Management

Mirroring: The Bare Necessities

See you there!

 

There are two drawbacks to how CMS works.  The first one I have already written about, so I won’t cover that in this post.  You can read about it in the following post:

How to add your CMS server to a group

The second one can be a pain in large companies with multiple environments, but we will be able to see that there is some security merit behind this restriction.

All registered server connections in the CMS can only use Windows authentication.  This means you can only add servers to your CMS that are in the same domain or a trusted domain.  You will not be able to add standalone servers that are not in a domain.  We generally see this in a DMZ scenario where the SQL Server hosts data for a public facing web server.  Along those lines, we typically see that test and development environments are hosted in separate untrusted domains.  You will not be able to add these servers either.  In these scenarios, you will need a separate CMS in each untrusted domain.

The inability to use SQL authentication can seem like quite a setback in large companies with multiple environments.  Although this restriction can be a hindrance in many environments, you have to think about the security risks if it were allowed.  Allowing SQL authentication would mean having connections with stored usernames and passwords.  Anyone allowed to connect to the CMS could use that connection to access a server with another set of credentials.  This would make it impossible to have user and group based access controls to your data.  You would also not be able to audit or track access to your server and identify an offending user.

The most obvious advantage of a CMS is that it holds an inventory of all the SQL servers in our environment.  We can now connect to a single place and get a quick view of every server in our environment without having to remember each server and instance name.

We also have the flexibility to arrange our server connections in folders.  Doing so allows us to get an even better picture of our environment at a glance.  We can arrange our connections in any manner that we choose.  This is entirely dependent on your business model so that you can match your folder structure to your business support model.  Common examples are to model your folder structure in a logical manner by server function, geographic location, SQL Server version, or any combination thereof.

Let’s say our company has their support model broken up into regions, so if an administrator supports the West region he can easily discover and manage the servers he supports.  Our business model also dictates that every production environment should have both a test and development environment.  That encompasses our business model, but as the administrator you also want to separate and identify different versions of SQL Server.  This helps avoid running a query that may be supported on one version but not another.  Here is what that implementation might look like:

CMS Design Example

Beyond housing a server inventory and being able to arrange it according to our business needs, a CMS gives us the power to run a query against multiple servers.  This is the most powerful feature of CMS and the biggest reason to utilize it.  If your manager comes up to your desk and says that the company has acquired a budget to upgrade your SQL Servers and he has to submit the request within two hours, how long would it take you to find and evaluate the version of every SQL Server in your environment?  What if a new cumulative update patch comes out that fixes a security issue and you need to know which servers need the patch?  Using our example, we can find out the version of all SQL Servers in the West region by right clicking the West folder and selecting “New Query”.  In the new query window we type in “SELECT @@VERSION” and this is what we get.

Result of CMS Multi-query

That was fast and easy, and we can instantly report to our manager that we have 3 instances of SQL Server running SQL 2008 RTM SP2.  We can also see that one of them is running 64bit and the other two are running 32bit.

There are several other things to make a note of here.  First is that there is an added column returned named “Server Name”.  CMS adds that column to every multi-server query so you know which result came from which server.  You will also notice in the lower right corner that the name of the group (West) we queried is displayed to show what the query was executed against in our CMS.

The last thing to note is what you will see in the query window prior to executing your query.  The status bar at the bottom will be a pink color as opposed to the usual yellow color.  This lets you know that the query window is attached to multiple connections.  You will also notice that it says “Connected. (3/3)”.  This lets you know how many connections any queries in this window will be executed against.

CMS Connected Servers

The last advantage of CMS comes when using it conjunction with Policy Based Management.  It allows you to evaluate policies against multiple SQL instances simultaneously, much like using the multi-server query feature.  It’s as simple as right clicking on a server connection or folder and selecting “Evaluate Policies”.  PBM and CMS complement each other very well.

I recently had the need to move the MSDTC for a cluster to a new SAN drive.  It’s a quite simple thing to do, but you can’t redirect the MSDTC resource to a new drive.  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.  Right click the drive to which you plan to move the MSDTC, go to more options, and select Move this resource to another service or application.  You want to select your MSDTC group.

At this point everything is now setup and ready for the move.  Unfortunately you can’t simply change the MSDTC resource object to point to a new drive.  You have to delete the resource and create a new one.  In Cluster Manager, expand your MSDTC group, right click the MSDTC resource and select Delete.  In the actions pane on the right, click Add Resource and under More Resources select Add Distributed Transaction Coordinator.  Do not bring the resource online yet as we need to define its dependencies.  Double click the resource and go to the dependencies tab.  Here you need to make it dependent on both the network name and the new cluster disk you just added.  Click OK and bring the resource online.  You have now successfully moved your cluster MSDTC to another drive, but let’s verify it.  Open Windows explorer and view the new drive.  You should now see a folder called MSDTC, which verifies the move.

The last thing you want to do is some clean up.  Go back into Cluster Manager and select the Storage node in the left hand pane.  Under your MSDTC group, right click the old MSDTC disk and select Remove.  This will remove the old storage drive from the cluster group and put it back into the available storage group.

If you want to reuse the old drive letter (Typically M:), 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 MSDTC group.

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

How to move cluster Quorum drive

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

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