Have you ever spent days pounding your head against a wall investigating an issue that has no logical explanation? Then next time this happens, check for a password embedded in whatever it is you are trying to do.
I just got done troubleshooting a SQL Server Agent job that suddenly stopped working. I talked to our networking and system admin folks, but they couldn't find anything that changed. I validated that the account Agent was running under had the appropriate access rights. Then - on a whim - I decided to look closer at the body of the job. One of the steps had a user name and an encrypted password. As it ends up, that password was changed the day the job started failing.
The moral of this story is - if you do need to embed passwords into jobs or applications - make sure that you document where that password is embedded so that - when someone does need to change it - it can be changed in every place where it is used.
Have you had a chance to check out the resource governor in SQL Server 2008 yet? If not, you should take a look.
The resource governor allows you to categorize a connection (at the time of login) into a workload group. That group exists as part of a resource pool. At the pool level, you can set minimums and maximums for processor and memory utilization. There are even more settings that you can adjust at the individual group level - including group priority.
To me, this has many different possibilities. First, if you have service level agreements with your customers, you can set up workload pools that will help you enforce those agreements. Second, if you have tasks that should run at different priorities, you can categorize those tasks into different groups within your resource pools. Third, if you want to do departmental chargebacks based on usage, you can use the pools to monitor that usage.
These are just a few things that you can do with the resource governor. I'll be posting more about this and other SQL Server 2008 features in the weeks and months to come as we lead up to Katmai's RTM.
Chuck Heinzelman