ADExplorer

Need to find out who is in a global group?  Where I work, they don’t install management tools on our computers if you’re not in the security or server group.  Sysinternals to the rescue.

http://technet.microsoft.com/en-us/sysinternals/bb963907.aspx

We keep our users and group in a container named _users. I just navigate to that node and search the container for whatever group I’m looking for.  The group has a members property you can double-click to view.

SQL Browser

Did you know you can start the SQL Browser service from the command line and watch as the UDP requests on 1434 fly in?

Your location of the file may vary, but you just use the -c switch like so:

c:\program files\microsoft sql server\90\shared>sqlbrowser.exe -c

Cluster Diagnostics

Installing SQL 2008 on my existing 2005 cluster seemed to have broken my clustered 2005 SSIS service.  It stopped starting successfully around the time of the install and the instance started using the SSIS 10 service – although I didn’t know this.

I found out by using a tool called clusterdiag.exe from microsoft.

Instruction on usage and obtainage here:  http://technet.microsoft.com/en-us/library/aa996161%28EXCHG.65%29.aspx#OpeningTheClusterLog

I tried to start the SSIS service and then fired up this program.  I opened the cluster folder in the WINDOWS directory on my server directly by opening \\server01\c$\windows\cluster in offline mode.  There was an error creating some sort of tree view, but everything seemed to work well enough.

I opened the Test and Cluster logs folder and found cluster.log within it.  After a double-click I saw a color coded display of the log.  I found the error messages and googled the error codes.  The service did not exist.  That led me to find that it had been replaced by the 2008/10 install.  I reran the 2005 install and ended up with 2 SSIS versions.

After a thorough read of the clusterdiag.exe page I found that you can highlight an error code and go to TOOLS > FIND WIN32 ERROR. You get a handy definition of the error, which my brain can understand.

I believe my install went wrong when I upgraded a report server from 2005 to 2008 and must have also upgraded the shared components.

Some Transaction Log Smarts

Paul Randal writes about and demonstrates the workings of the transaction log.  

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

Some reasons why your transaction log will not or is not shrinking.

http://msdn.microsoft.com/en-us/library/ms345414.aspx

A query to see why your log cannot be reused and is stopping the function of your database

SELECT name,log_reuse_wait_desc
FROM MASTER.sys.databases

The meanings of log_reuse_wait_desc are shown in the msdn article linked to above.

And finally, some steps for what to do when your log is full.

http://msdn.microsoft.com/en-us/library/ms175495%28SQL.90%29.aspx

Making backups for non-production usage

Last night while dealing with a full transaction log, it was requested that I restore a database.  I generated the restore statement to a certain  point in time and reviewed the script.  The backup file was being retrieved from a non-standard location and when I went there, the backup file was not present!  I tracked down a job on the dev server which was backing up this DB, restoring it to dev and then deleting the file!  The result of this is that we effectively had no recovery available for the database since that backup was deleted.  How did this happen and how can we avoid this?

It’s a frequent task in SQL to create a back for a developer to use in staging, development or on their desktop.  If they can do with last nights data, you can just give them a copy of the previous day’s backup.  If they need something current, you’ll need to make another backup.  You’ll probably run a statement like this:

BACKUP MYDATABASE TO DISK = 'E:\BACKUPS\MYBACKUP.BAK'

This file has now become a vital part of your recovery for this database.   To recover anything done past this point using transaction log backups made in the future, you’ll need this file.  So you can’t just drag this file off to another server or share.

If you want to move the file around or delete it, use this command: 

BACKUP MYDATABASE TO DISK = '\\SERVER_1\SHARE$\MYBACKUP.BAK' WITH COPY_ONLY

You can back up straight to a network share on another machine and save some time copying files around.  This doesn’t disturb your chain of backups and log backups and lets you do as you please with the file. 

More information on log sequence numbers and how they work can be found here http://msdn.microsoft.com/en-us/library/ms190729.aspx .

Renewing SSL Certificates for SQL Reporting

Once a year, our certificates expire for ssl on reporting services.  These can be replaced with zero down time in reporting services.
1.    Every year, the certificate for reporting services must be renewed.
2.    Obtain certificate .pfx file and password from web infrastructure team.
3.    Connect remotely to the reporting server.
4.    Open Control Panel > Administrative Tools > Public Key Management
5.    Open Certificates (Local Computer) > Personal
6.    All Tasks > Import
7.    Let the wizard determine the store.
8.    Open IIS > Web Sites > Default WebSite > Directory Security Tab > Server Certificate
9.    Replace the current certificate
10.    Pick new cert from the store
11.    View the website and click the lock to the right of the url bar in IE.  Click the ‘view certificate’ button and ensure the date has been changed to the new certificate effective/expiry dates.

As you can see, we didn’t restart the website and you’re using the new cert.

The many caveats of installing a cluster

There are quite a few things to remember when you are installing a clustered instance of SQL server.

For 2005, you need to remember a few things

  • Global groups for your accounts.
  • Service accounts to go in those groups.
  • Rights to add those accounts to those groups during the install.
  • Set up MSDTC as a clustered resource.
  • Get your IP address from the network admin and ensure your SQL virtual name is not used.
  • Get your drives set up properly and make sure to test failover before you begin.
  • Make sure you are logged out of node 2 and have the task scheduler service running.

So I thought I had everything gathered up and ready to go tonight.  Nope.

Near the end of the install I see this:

remote setup failed

After a moments google I find this:  http://support.microsoft.com/kb/910851 , which gets me to the task scheduler log on the remote machine.

Unable to load task.
The specific error is:
0x80070002: The system cannot find the file specified.

I found a suggestion from a MS MVP saying that you need to make sure your install account has access to the install files. I figured that had to be it – so I moved them to the c:\temp directory and voila, a good install.

Evidently, whatever account the install was running under couldn’t access my desktop – but I did enter MY domain account to start the account, so maybe it switched to the SQL service account to do the install? Doesn’t make sense to me.

The lesson I learned to day is to never install from a network share or a user directory – put the install files on a drive which has an admin share and this shouldn’t happen.

Currently Reading

The Practice of System and Network Administration

Still on chapter one, but I know I’m going to like it.

1.40 My Dishwasher Leaves Spots on My Glasses
• Spots are usually the result of not using hot enough water rather than
finding a special soap or even using a special cycle on the machine.
• Check for problems with the hot water going to your dishwasher.
• Have the temperature of your hot water adjusted.
• Before starting the dishwasher, run the water in the adjacent sink until
it’s hot.

Not SA related, but gives me some valuable info for a problem I am having.