SQL Server Club

This week: sneak peek of PASS Summit 2010 in 24 free live webcasts

Join an exceptional lineup of SQL Server and BI experts from around the world as they present a sneak peek of PASS Summit 2010 in 24 free live webcasts September 15-16.

Find out more here:

http://www.sqlpass.org/24hours/fall2010/

Installing SQL Server 2008? Save yourself time and download .Net Framework 3.5 **SP1** first

When installing SQL Server 2008 remember that you'll need to install .Net Framework 3.5, Service Pack 1 as a pre-requisite.

Tip : if you've got more than one server that needs a SQL Server 2008 installation or upgrade,: download the distributable version of the package.

This can then be copied over the network around your servers or burned to CD/DVD, saving valuable time downloading it all again for each installation.

Essential if you're stuck in a cold machine room all day. Just go to Microsoft's site and search for .net framework 3.5 sp1 - then look for the Full Package download link.

UK: #SQLBits 7 announced, University of #York, 30th September - 2nd October 2010 #SQLServer : http://sqlbits.com/default.aspx

UK: #SQLBits 7 announced : University of #York, 30th September - 2nd October 2010 #SQLServer : http://sqlbits.com/default.aspx

TypePad Conversations » Answer this question!

Getting Intelligent with SQLBits VI

If you’re in or around London, UK on Friday 16th April 2010, SQLBits VI is well worth a visit. And this SQLBits is a bit of a special one, intentionally arranged in conjunction with Microsoft’s launch of SQL Server 2008 R2 the previous day.

Much of the day is dedicated to Business Intelligence and has some top notch guest speakers - Simon Sabin, world renowned database author Mark Whitehorn, MDX and OLAP expert Chris Webb, Keith Burns and Thomas Kejser from Microsoft, and Tony Rogerson (MVP) to name but a few. 

If you want to check out the sessions, click here. 

For those wanting to make a weekend of it and include some sightseeing, SQLBits has arranged a special promotional offer with the Park Plaza Hotel. This brand new hotel has only just opened and is a five minute walk away over the River Thames via Westminster Bridge.

Plus, the conference venue is also very close to two great Fuller’s pubs, The Sanctuary House Hotel in Tothill Street and The Red Lion in Parliament Street, a stone’s throw from Downing Street. 

And if that’s not enough, there’s a prize draw for tickets to the PASS European Summit, and the PASS Summit, worth $1,995.

All in all, it promises to be a great conference in an easy to reach location. Certainly if it’s as good as SQLBits V, and you’re interested in SQL Server, you should try to be there. SQL Server Club will as usual be Tweeting events as they happen.

SQLBits VI will be held on 16th April 2010. The venue is the Church House Conference Centre, London, opposite Westminster Abbey, a two minute walk from the Houses of Parliament and Big Ben. Registration and entry is free, and available now at www.sqlbits.com.

All Purists Must Die

How I love purists.

Especially Object Oriented, agile-assisted, latest fad, scrummed up, backlog driven, customer focussed, buttock-clenching purists.

Just like their counterparts during the Spanish Inquisition they like to go around telling everyone what to do and think. Then do the exact opposite themselves whilst vilifying any non-believers as evil-doers. Hypocrits!

Why am I having this rant? Simple. I was coding a small app earlier today and needed to change the Connection Timeout period of my Connection object. All well and good, a simple connDB.COnnectionTimeout=5 should do it. Or so I thought.

WRONG! How very, very wrong.

The ConnectionTimeout property is read only. Fair enough, some things should be read only. So which connection object property DO I set when I want to change the connection timeout period? Answer: I don’t.

I was expecting something like:

set connDB.ConnectionTimeout=5

or even:

connDB.SetConnectionTimeout(5)

But no. Here’s the solution:

sConnString = “Provider=sqloledb;integrated security=SSPI;data source=” & sNewServerName & “;Connect Timeout=5″
connDB.ConnectionString=sConnString

Where as I’d have expected something truely OO such as:

with connDB
.Provider=sqloledb
.IntegratedSecurity=SSPI
.data source=sNewServerName
.ConnectTimeout=5
end with

Instead, I end up with hacking around strings with delimited values.

Ex-<pletive deleted>-traordinary.

So. One of the simplest and most common things you can ever wish to do as a developer, ie: connect to a database to execute SQL cannot be done in true, 100% O-O fashion. If I want to do it I have to launch myself back into the 1970s land of semi-colon delimited strings, unfashionable beards, real ale and loon pants.

With all the non-OO possibilities for mis-spelling and non-binding that entails. I’m just mildly surprised I’ve not been forced to specify it all in XML.
I love backwards compatibility as much as anyone else. But it makes you wonder if the .net wonder-boys at Microsoft get bored half-way through and think, “Sod it, we’ll leave that bit as it is, I feel like a beer.”

Well, thank YOU !
Me?
I feel tainted.
Contaminated even.
I’m off to scrub my hands repeatedly and sanitise my keyboard.
I feel light-headed, nauseous and may even vomit.
I think I need to lay down.
In a dark room.

Now where did I put my C++ book and that list of heretical non-believers?
I need a fix….
</RANTDBA>

Butt Clung, Databases and Real World Testing

I was happily minding my own business whilst responding to a blog about SQL Server Integration Services last week, when I was suddenly confronted by the following request to verify my response:




Now, I have nothing against Re-captcha. In fact, I think it’s a fabulous idea. We use it here at SQL Server Club to prevent the feeble minded from easily using our site to spam others, and I’d actively recommend it to anyone who seems to be getting 1 million hits on their “contact us” page each month, unless of course you work for Amazon.

For those of you not familiar with it, Re-Captcha is a very simple solution to a very annoying problem. First, it takes words electronically scanned in from old documents and converted into gifs by university research departments. It then displays these as words for verification on the screen and asks the user to type in the words displayed. This stops web sites getting spammed. It is easy and unobtrusive for the user to use and best of all provides a free services to archivists the world over who need to transcribe ancient documents but do not have the manpower to do it this side of hell freezing over. Better still, it displays each word several dozen times and takes a statistical average of what each user types, which eliminates any random input. I’ve seen it in loads of places and it works just fine.

But seeing Butt clung in front of my eyes stopped me in my tracks.

It’s the mental equivalent of steadily cruising along in a manual car at 70mph, and accidentally changing from fourth to first, instead of fifth. That lurching, unexpected, head about to go through the screen feeling.

It reminded me of a recent conversation I had with a DBA about licence keys numbers. He works for a major online electrical goods shop which is very successful. On a regular basis, vouchers will get printed out with a discount code on them and popped into deliveries over a certain value, offering the customer a substantial reduction off certain goods next time they buy.

All well and good, except the discount codes use five groups, each of four random letters. Similar to a Microsoft product license key.

Which is fine most of the time as what gets generated is complete, random gibberish. But occasionally, just occasionally, a sequence of letters appear which have more than a passing resemblance to good old-fashioned Anglo Saxon curse words.  

Whilst I, and most of you reading this might find this mildly amusing in a slightly infantile way, someone’s 70 year old grandmother might be more than just mortified to receive this in the post with her hair curlers. As a result, all of the discount codes have to be progmatically checked against a table of known swear words before each batch is sent off to the printers.

My DBA friend has spent sleepless nights trying to eliminate banned words from his discount codes. He’s wondering if he should extend the list to include Spanish, German and Italian curses as they’ve recently expanded into these markets. I’ve suggested that once he’s done, he publishes the list as a CSV file so that other DBAs can benefit from his efforts.

What does all this prove?

Well, that no matter how hard you test for these things, some odd permutation of states will never get tested for and, what’s more, can never be predicted. Whilst we as coders, not matter what the language, can always have a fair crack at stopping the howlers getting through, there really is no substitute for getting your code out there in the real world and letting your customers find the bugs that years of scripted testing will never find….

Why You Should Remove a Database Mirror When You Are Done With It

I ran into an interesting problem a few days ago. One of our call centre databases had a huge transaction log, without any obvious reason. We’d only noticed it was down to 100Mb free on the transaction log drive.

Odd thing was, the database was being backed up every night and should have been truncated, but for some reason, the log file had grown to over 300Gb in size. Which is pretty enormous.

After a little investigation, I noticed that the database had been previously mirrored to the backup server. However, due to budgetary constraints, the previous DBA had broken the mirror and re-used the server to host a new system.

Unfortunately in his haste, he’d broken but not removed the mirror from the principal server.

As a result, the database log file had grown and grown. This is because when a database mirror is broken but not removed, the transactions that get applied to the principal database get put into the transaction log, but never get cleared out.  

This way, when the connection to the mirror database/server is re-established, the “missed” transactions can be applied to the mirror to bring it back into the same state as the principal. Makes total sense.

In our case, we weren’t going to re-establish the mirror, but we wanted to clear down that huge log before we ran out of disk space. This was a simple two step process.

First of all, we removed the mirror from the database with a simple ALTER DATABASE command:

ALTER DATABASE CallCentre SET PARTNER OFF

A quick refresh of the database list in Management Studio confirmed that the mirror had been removed.

Now all we needed to do was shrink the log file to reclaim some of the disk space:

DBCC SHRINKFILE (’CallCentre_log1′)

Finally we took a full backup and presto! The log file shrank back to a far more decent 500Mb. 299.5GB of disk space free and one happier DBA.

Notes From a Yawning Masochist on Building a Lawn Mower After a Night Without Sleep…

Sleep deprivation. Everyone reacts to it in their own way, whether enforced or voluntary, and I found this out personally the other day. In the process, I discovered how you can rejoice in the experience of seeing words in front of your eyes which normally make sense but now seem completely meaningless - like instructions on how to self-assemble a small lawn mower…

The scenario was this: we had to make a new data centre go live, which involved being awake from 8pm in the evening until 8am the next day, keeping an eye on a bunch of new database servers, failing over our web sites to the new data centre and fixing any problems that occurred as a result.

Our bosses were good, I got paid double, they laid on pizza and plenty of other goodies, and we even had the option to take a free afternoon off to catch up on some sleep. Unfortunately, I just can’t sleep during the day.

As a result from when I woke at 5am on Tuesday 1st October, until 11pm on Wednesday 2nd October, I got absolutely no sleep - that’s a 42 hour stretch without a break.

For those of you who’re curious and have never done an overnight work session, here’s some of what I experienced from the notes I took on the night:

Tuesday 20:00

In to the office building early to re-check everything I double checked the previous day.

Everything must go to plan tonight, there’s no room for mistakes, so I’m in early, just in case we have some last minute hitch.

I’m feeling sharp, tuned in to what we’re doing, calm but cautious and raring to go.

22:00

Straight in to a meeting with all of my data centre colleagues who’ve just arrived, a couple of managers and our test team.

Everyone’s jovial and jokey, slightly nervous about the night ahead, but confident we can do it.

We perform some more preliminary checks, go through the plan and make sure there’s no late-hitting problems we need to know about.

Wednesday 00:00

I’m sitting on a stool in our chilly, noisy, air-con’d server room. We’ve prep’d the new data centre as far as we can in advance, and have switched the web servers over. Everything’s been checked and seems operational.

No obvious sleep deprivation effects yet, but hey, it’s only midnight.

01:00

Feeling a little tired, but all of the fluorescents are on in the server room and there’s mentally demanding work to do that must be done right, so we’re all focused.

There’s also dozen other guys here, network engineers, systems guys and another DBA to talk techie to and keep me from drowsing off.

As a result, I’ve successfully stayed away from the urge to pop outside to the hallway for a strong coffee. At night I find the caffeine which I enjoy in the day just makes me very irritable and snappy, it just doesn’t cut it after a while. You end up with all the irritability and none of the ability to think well. Plus your colleagues don’t want to talk to you.

02:00

We’re at a lull in proceedings while we wait for Australia to do some work before we switch data centers.

Our resident hardware engineer has rigged a PC up to an XVGA overhead projector in the adjacent project room and is playing that 90’s classic Doom on a 30ftx40ft wall, which gives us all a laugh. Feeling quite relaxed, less sharp than I should, but watching this is at least keeping me awake and mentally active.

03:00

I’m really longing for high sugar, high caffeine food. But I know from previous experience that this really screws up your blood sugar levels and affects drowsiness and concentration, so I’m fighting my brain’s compulsion to throw a Snickers and a black coffee down my throat.

But it’s only a matter of time before I give in. I know that the more tired I get, the more susceptible I’m going to become to suggestion and peer pressure, as well as what my own brain’s craving. I’m just hoping someone else cracks first. I know around 03:30 my body temperature will dip and my blood hormones will be battling to get me to sleep.

04:00

The big dip has hit me, hard and heavy.

My ability to construct moderately complex sentences has started to fall apart.

Not big time, I just can’t think of the right word, or jumble up a couple of words in a sentence. It’s a bit like after your fourth drink of the night, but without the mellowness.

Thankfully we’ve got the most difficult part of our work finished, but it’s still a tight schedule as we’ve hit a few minor problems.

05:00

My typing skills are now much less than accurate. They’re gradually getting worse and will disappear altogether son. Ooops, another typo, I meant soon.

It seems that my seemingly normal ability to hold all four octets of a simple TCP/IP address have suddenly evaporated, like water through a sieve the numbers drip, coming out garbled, encrypted and completely unlike the original address. I literally feel like my mind’s sliding away under the server room doors and down between the joins in the floor tiles.

06:00

Coordination starts to go. I’m bumping into door handles and door frames - something which I don’t usually do.

Clumsiness starts to set in. I just dropped my USB stick on the floor and must have taken a minute to retrieve it, kept dropping the damned thing.

My brain is now on auto pilot, so I do and re-do simple things, forget what I need to do even though I’ve just read it from a sheet. Even mild distractions become major barriers to concentration now, and my thinking is labored, slow and treacle-like.

07:00

A successful data centre switch. Hurray, we all celebrate, in a muted kind of way. Well maybe more tomorrow. I’m going to head home now and will be taking today off work.

I know it will do more harm than good trying to reason without sleep in an analytical job, so I’m very wisely not going to be sitting at my desk today.

My manager’s in work for a while longer, but management doesn’t always involve logic, so he shouldn’t be too much of a danger to shipping!

08:00

I probably shouldn’t be doing this, but I have to get home.

Mr Brain is totally devoid of thoughts, and my ability to drive is most definitely impaired. I’m not on the other side of the road, but as my attention span lapses get longer and longer, it’s getting to the point where it’s dangerous. Very dangerous. If I got pulled now, I could probably be successfully accused of not being in a fit state to drive, despite my bloodstream being totally alcohol and drug free.

My capacity to judge distance and speed is impaired, I’m thinking I should slow right down, follow someone else at a good distance, be very cautious and avoid driving next time if at all possible.

I thankfully get home safely this time, but I won’t be doing that again in a hurry. I grab a coffee, sit on the sofa and tune in to some daytime TV with a couple of random cats who wonder what’s going on.

Epilogue

I’m writing this five nights later and I still don’t feel quite right. There’s a kind of vague fuzziness around the edge of my brain which I can’t explain, and I’m not quite 100% back to normal despite three nights of proper sleep. I’ve suffered:

- Loss of logical reasoning. 
- Inability to think straight.
- Much, much slower thinking.
- Slower physical movement, but especially my reaction times.
- A breakdown in normal coordination.

In summary, when you do this kind of work in an IT job, you have to watch out for these things, as they all affect you ability to do your job, or your (and others’) personal safety.

From personal experience, I think it’s like having a drunk in charge, just without the alcohol. Sleep deprivation is dangerous, both inside and outside the office, and I think employers really need to look hard and long at how to make sure their staff operate and get home safely if they have to do this sort of thing. If you ever have to do this, these things are worth noting and preparing yourself for - even if it means your company has to get someone to drive you home.

If you’re interested in reading more about this subject, a starting point might be Wikipedia, at:

http://en.wikipedia.org/wiki/Sleep_deprivation
http://en.wikipedia.org/wiki/Sleep
http://archives.cnn.com/2000/TECH/computing/06/12/sleep.deprived.idg/index.html

Bye Bye Bill

It’s so easy to knock Microsoft. Everybody does it, ourselves included, and with good reason. And it’s even easier to knock Bill Gates. So just for a change, on Bill’s last day at Microsoft, today Friday 27th June 2008, we thought we’d write something positive and congratulatory about him.

Born 28th October 1955, 52 year old Bill has done battle with some of the best people in the IT business over the years. He’s been criticised, attacked, and ridiculed for the way he does business, his aggressive management style and his clothing.

But with an estimated net worth of $58 billion, Bill’s main raison d’etre is now to help put the world to rights. He may never achieve perfection of his goals, but he has already given away a staggering 29 billion dollars to charity and there is even more to come.

Just think about that for one moment: 29,000,000,000 dollars. 

Look at each zero and ponder it.

He has moved a vast tract of his personal Microsoft stock into the Bill & Melinda Gates Foundation, the largest transparently operated charity in the world. The charity must give away all of the money it has received from Gates and his friend and business associate, Warren Buffett within 50 years of their deaths, to distribute it for the greater good of humankind.

Most of us in the developed world are fortunate enough not to need Bill’s help. But we hope his generosity and philanthropy can make a real difference to the lives of people less fortunate than us. Combining Gates’ mind with that of his long term friend Warren Buffett should make a lot of peoples’ quality of life much better.

So, if you’re reading this, congratulations William Henry Gates III, and good luck - we hope your intentions bear great fruit.

You may not be perfect, but just like the Rockefellers and Carnegies before you, may your massive generosity solve some of the most difficult problems that face the world today.

And try to find the time to have a beer or two after work tonight before you set off on your next crusade - you surely deserve it!

Microsoft Office Spy: Clippy Gets Detective Work

Clippy says “I’ve noticed that you’re stressed out by the Microsoft Office Assistant. Would you like me to:

- Administer a measured dose of Temazepam
- Administer a measured dose of Morphine
- Automatically report this to your manager
- Book you in for a health check up
- Change the Office Assistant to a mini Steve Ballmer?”


So, Microsoft has applied for a patent on what is affectionately being dubbed “MS Office Spy”.

If you’ve not already heard about this, the idea is that a hidden module in MS Office will monitor your breathing, heart rate, blood sugar and stress hormone levels whilst you’re at work.

The very thought of this has probably already kicked off a flood of stress hormones into your bloodstream. All very well intentioned, although the civil liberties aspects will almost undoubtedly prevent it ever getting implemented.


But what if it is put into practice? Well, there will of course be a number of unpredicted technological spin offs. What author William Gibson famously described as “The street finding its own use for things”…

The first beta, sorry, CTP releases will no doubt be buggy. Ex-colleagues will be flagged as being in ill health and recommended a week off with full sick pay. Which will be automatically credited to their bank account without informing payroll, even though the person in question left three years ago.

By v1.0, spouses will be simultaneously informed by text message and email that their beloved is dead, and the funeral parlour automatically contacted with height and weight measurements, when in reality the “deceased” has just not properly undocked themselves from their ActiveSync metabolism monitor.

Version 2.0 will be more reliable but riddled with security holes and buffer overflow issues. Mischievous colleagues will hack your machine and rack up your supposed heart rate until you keel over. Or set it so low that you’re clinically dead. And it will no doubt insecurely post all of this data to a web service which also has a security problem, so that anyone who cares to can see the health records for an entire nation, without too much effort.

But what I like most is the idea that you’ll be able to submit your rankings online to a site that gathers your individual data, and ranks it. Then, at the end of the year there will be awards for the most sleep deprived developer, the longest marketing migraine, the highest admin anxiety levels, and the most days worked past midnight in a year by a business consultant.

Perhaps Jolt Cola could sponsor it, the perfect caffeine-fuelled corporate sponsorship deal….