Author Archives: brentm

Encrypting SSMS connections using certificates

Posted on 2012/02/25 by brentm

We had a requirement for PCI compliance that all admin connections to SQL Server must be encrypted. I took this a little bit further to ensure that this requirement was easily enforced using Login triggers. In our environment, we have … Continue reading →

Posted in Encryption | Comments Off on Encrypting SSMS connections using certificates

SQL Server cluster setup failure due to resource offline

Posted on 2012/02/25 by brentm

When running SQL Server 2008 setup and during the installation a dialog box appears to say that installation failed because it couldn’t bring a cluster resource online then do not despair. This failure is quite common apparently and there is … Continue reading →

Posted in Clustering | Comments Off on SQL Server cluster setup failure due to resource offline

Adding DTS support to SSMS

Posted on 2012/02/25 by brentm

I have done this on my recent rebuild of my laptop at work so know that this does work. SQL 2000 tools are no longer required. The following add-ons are required to provide DTS support in SSMS 2008… 1. SQL … Continue reading →

Posted in Tips | Comments Off on Adding DTS support to SSMS

Rebuilding system databases on SQL 2005 (or adding additional components)

Posted on 2012/02/25 by brentm

SQL server is corrupt – master is damaged. You didn’t do the original install and you have to supply the media for the repair. In the situation I’m thinking of, SQL Server was installed as part of the server build … Continue reading →

Posted in Tips | Comments Off on Rebuilding system databases on SQL 2005 (or adding additional components)

SQL Server Edition Upgrade

Posted on 2012/02/25 by brentm

Edition upgrades on SQL2005 are a completely different kettle of fish compared to SQL 2000. Whereas in 2000, you pop the cd in the drive and it recognises that you can upgrade the edition and it is all over in … Continue reading →

Posted in Upgrade | Comments Off on SQL Server Edition Upgrade

SQL Server Memory

Posted on 2012/02/25 by brentm

On a 32-bit system there is a maximum of 4GB of virtual address space (VAS). This is made up of: 2GB for the kernel and 2GB user mode for the Buffer Pool minus the Mem-To-Leave amount. Not all the memory … Continue reading →

Posted in Memory | Comments Off on SQL Server Memory

Cleaning up old single use plans

Posted on 2012/02/25 by brentm

After identifying the queries for which we want to create a plan guide, we could just leave it there and the old plans will age out naturally or we could clean them out ourselves. The command to clean out the … Continue reading →

Posted in Plan Guides | Comments Off on Cleaning up old single use plans

Creating a Plan Guide

Posted on 2012/02/25 by brentm

One day while looking at the cache usage on a particular server, I was surprised at the size of the procedure cache. Looking closely, there were a large number of single use plans in the cache. I looked at the … Continue reading →

Posted in Plan Guides | Comments Off on Creating a Plan Guide

Capturing Timeouts

Posted on 2012/02/25 by brentm

The only way to capture timeouts is via SQL Trace – you need to know what was running/executing at the time of the timeout and SQL Trace is the tool to capture this. Do not confuse SQL Trace with SQL … Continue reading →

Posted in Performance | Comments Off on Capturing Timeouts

Adding a location for your saved SQL scripts into SSMS

Posted on 2012/01/31 by brentm

I was searching for this on the internet because the registry keys for the default save location can’t be modified – well actually they can, but they just keep getting over-written. So, I found this which is a neat way … Continue reading →

Posted in Tips | Comments Off on Adding a location for your saved SQL scripts into SSMS