Monthly Archives: February 2012

Encrypting SSMS connections using certificates

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

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

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)

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

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

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

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

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

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