March 2007 - Posts
I am reviewing my interview questions to try and make
them more exhaustive.
I intially started off interviewing with the view that the interviewee should
be able to stand up on there own. I'm not going to give them anything. If they
can't think of the right answer then mustn't know it. I never wanted to give a
interviewee the answer as I felt that defeated the objective.
This I have realised is a bit drastic and you have to find a middle ground.
Obviously the more non-leading questions you can give and get the answers you
want the better. but where would you start.
For example would you ask "How do you manage replication?" or would you ask
"What is RMO/Remplication Monitor etc?" An other example "How do you improve the
performance of a query?" or "What is an index?"
I do find the open questions lead on to a few or more more detailed
questions. If the interviewee hasn't made that connection and you have to make
the step to the more leading questions then you know the level they are at.
The one thing I learnt when I was at the MVP summit a
few weeks ago was that hotfixes are always cumulative, in other words you get
all the other hotfixes that have been made prior to your hot fix.
The reason for this is that there is one SQL Server code branch that is used
for releasing code. The code changes for the hotfix are applied to the branch
and the hotfix package is then built.
You may have noticed that the GDR (General Distribution Release in between a
hotfix and an SP) for the Maintenance plan cleanup bug made changes to all
sorts of areas including RS, AS not just the tools. This is because the GDR is
just a hotfix that is made public.
Tony has blogged about the time it takes to apply the latest SQL Hotfix SQL
2005 SP2 hot fix from windows update takes over 10 minutes to run! This
is probably because the cut off for SP2 was quite a while ago and so hotfixes
have been piling up so the hot fix actuall contains lots of previous
Be warned. Make sure you have a well structured testing process for applying
hotfixes as well as service packs
We are holding the next UK SQL User group meeting in Central
London on the 26th April from 6pm - 9pm (later if you fancy a drink)
The eveneing will have a SQL event and BI event running side by side with 8 presenters and loads of giveaways
including t-shirts, books and USB keys.
If you want to learn more about Performance point or SSIS in the real world
register for the BI event
http://sqlserverfaq.com/?eid=95&UK SQL Usergroup meeting - Performance Point, SSIS and more cool BI stuff
Or for sessions on managing change and using SMO from TSQL, Arrays in TSQL
and other great SQL topics register for the SQL Event.
http://sqlserverfaq.com/?eid=94&Managing Change, Agile Development and SQL Server Tips and
We are restricted to only 30 people per event so make sure you register
Thank you to those people that informed me that my
taskpad report was incorrectly reporting the backups times against the wrong
type of backup.
I have now fixed the report and have also included the backup types that
are supported by SQL 2005.
You can downlaod the new version of the taskpad custom report
You will need SQL Server 2005 SP2 to use the custom report.
If you are using Event Notifications or capturing events
in Profiler you undoubtedly have noticed that MS have used the same columns (xml
elements) for storing different data for each event. For example there is an
element called "integer data". You may ask what is stored in here cos it ain't
obvious from the name of the element.
Help is at hand but it's not obvious. I spent a while digging through BOL and
found the pages you need.
The top level page is called "SQL Server Event Class Reference" and can
be found with thi BOL
This page provides a link to all the pages that detail what all the elements
mean for each event class.
In addition you can use the index and go to the pages directly. The name of
the page is "<event name> event class" i.e.
"Exception event class" with a BOL link of
Microsoft are in the process of retiring GotDotNet and
replacing it with CodePlex.
CodePlex is a shared source website which allows you to upload projects and
give access to other people to view/maintain.
The great feature is that it is using Team Foundation Server. This means that
any one using Visual Studio with a Team suite client license can have full
access to the Team foundation server. This is all done over HTTP.
A big step has been made by the SQL Server team in putting the samples on
CodePlex (Thanks Ed) Whilst you can't have access to modify the source you can
access all the component parts of the samples. This means you don't have to
download all the samples to get the sample you want, and even better you don't
have to download a complete sample you can access individual files, i.e.
scripts, read me files, word documents etc.
So go and have a look http://www.codeplex.com/SqlServerSamples
This site also acts as a starting point for looking for other SQL Samples
provided by the community.
Paul Randal recently informed me of a new white
paper on online index operations. The whitepaper can be found here
Online Index White Paper.
This is one of many whitepapers that have been published lately by Bob
SMO is a great feature of SQL Server 2005. It is a rich
API for managing SQL Server and as with its predecssor DMO the SQL Server tools
are built on top of it.
As SMO is a .Net managed library many people want to reference SMO in a SQL
CLR function or stored procedure. However SMO is one library that cannot be
hosted within SQL Server (See Bob's post for more details http://www.sqlskills.com/blogs/bobb/2007/03/13/TwoThingsYouCantDoInSQLCLR.aspx)
If you want to use SMO functionality there is a way you can achieve this from
within TSQL and thats with the use of Service Broker.
Service Broker allows us to submit a request to a service in TSQL and waits
for a response, an external service is then able to recieve the request, process
it and reply with the response. The TSQL receives the response and can do what
it needs to do with it. Whilst Service Broker is an asynchronous process
you can make an end to end solution that is synchronous by having the calling
process wait for a response.
The beauty of this is that your external service can be anything from a VB6
application to a .Net application, it just needs to be able to read a message
from a queue and send a message back. Both of which are just TSQL
I will shortly be posting a demo of this in action.
Due to a regression bug introduced in SP2 some of
the maintenance plan tasks that can operate on multiple databases may not perform
If a maintenance plan component uses TSQL of this nature
To handle errors in the process the batch is split into multiple commands
(split by the GO keyword) so that if one fails the others can still be run.
Because a new the server connection is being made for each statement the
context of the connection is always the database specified in the connection
string. Rather than the one specified in the previous USE statement.
I have found a partial workaround for the executesql task (the
maintenance plan one), which is to open the maintenance plan in BI Studio and
change the properties of the connection to not reset the connection.
However this does not work for the Database Integrity component.
I therefore suggest anyone that has upgraded to SP2 and is using the Database
Integrity component to change and generate the SQL that would have been used and
use either the normal SSIS ExecuteSQL Task or the Maintenance ExecuteSQL Task
with the above work around.