the week is over and there were a few things to take care of.
First at all: thank you to all the people who always have a good advice or idea on Twitter. It´s unbelievable!
So, this week was all in the sign of SQLNexus. Those of you who still don’t know it: sqlnexus.codeplex.com It really can help you with the analysis of SQL server performance data and also sql traces. It does not make sense to tell you every little detail, because it is all mentioned on the codeplex site and on other sql server sides (e.g. mssqltips.com) . I gave a colleague of mine the possibility to start a data collection with sqldiag. Afterwards I just loaded the info into Nexus and bingo. Here was already the problem: an easy blocking issue. Ok, that´s not that difficult and maybe you can track down that easily without getting a whole bunch of data. But when this blocking situation occurred I was already sleeping while my colleague was working on the other side of the Atlantic ocean.
Next thing was a simple create logins script which ran in the dev environment but not in the test environment. So what happened? From SQL2k5 upward there is an interesting settings for SQL logins: enforce password policy & enforce password expiration. BUT:where do I find the setting to define the password policy? Well, it´s the policy from the host system itself. If you´re running W2K3 or W2K8 (not SQL2k5 on W2K) then you may have the affect that you cannot create a sql login due to the policy setting. In the dev environment for example there was no password complexity set and a password length of 7 characters. But in test environment the complexity criteria is needed and length must be at least 8 characters. To get more information just take a look here. This FAQ by Laurentiu Cristofor is all you need.
So, what to say? I´ve learned that sqlnexus can be very useful and it´s easy to handle. Next thing is that dev, test and live environment should always have the same security settings, so there will be no surprise.