SQL Server
-
Advantages of using CMS
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…
-
How to move Cluster MSDTC Drive
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…
-
How to move Cluster Quorum Drive
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…
-
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…
-
Why My Policy will not Evaluate System DBs
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…
-
How to move the TempDB Database
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…
-
How to move the MSDB database
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. Next we need to tell SQL where…
-
How to move the Model Database
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. Next we need to tell SQL where…
-
How to move the Master database
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…
-
How to add your CMS server to a group
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…
-
syspolicy_purge_history Job Fails
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. First to check to see if PBM is enabled and exit if it is not. Run the msdb.dbo.sp_syspolicy_purge_history system stored procedure to clear the history Use…
-
T-SQL Tuesday Mirroring and SSWUG
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…