Inside SQLSaturday: Niko Neugebauer & SQLSaturday Lisbon

The beautiful Sintra

The beautiful Sintra

Welcome to the second thrilling ;) instalment of Inside SQLSaturday, and today I am joined by Portugal’s very own Niko Neugebauer (web|twitter).

Niko is the event lead to SQLSaturday Lisbon which holds the honour of being the first SQLSaturday *ever* to be held outside of the United States and the launch pad for so many other great events to follow around the world. Along with his team they run a selection of Community events and innovative initiatives.

Q: Hello Niko! Thank you for agreeing to talk to me for my ongoing Inside SQLSaturday Series, I know you are really busy right now! It feels like an eternity since we have spoken properly, so tell me what have you been up to over the last 6 months?

A: After moving from my previous position as PASS Community Evangelist I am now working together with my colleagues Tillmann Eitelberg (web|twitter) & Oliver Engels (web|twitter) at OH22 Information Services and we are working on a range of different and exciting projects.
From the community perspective, we are running new events in the Northern Portugal – Oporto with a lot of success. The last meeting we held had 35 attendees. Of course we are still running regular SQLPort meetings every month in Lisbon.

Q: With the start of SQLSaturday Lisbon just around the corner (pre cons are due this Thursday and Friday, the community day is this Saturday), I think it should not be forgotten that this event is famous for being the first ever SQLSaturday outside of the US. That must make you and your team very proud, tell me how all this came about?

A: I honestly do not feel proud, I feel more like I was truly lucky.
The experience was simply amazing and it was somewhere around April 2010 when I decided to bring a good SQL event to my favourite place in the Universe – Lisbon.
With this goal I went to the PASS Summit and after all the conversations that I have held with SQLSaturday creator Andy Warren (web|twitter), in just 3 days he promised to give me an opportunity to organize one.
I told him that I will do everything possible (and overcome the impossible) to make it work and as remarkable as possible -apparently I was convincing enough. :)
The rest is just history – we got support from our local Microsoft office and the next April (2011) we held our first event. PASS also sent us an internationally recognised speaker (Scott Stauffer), and with support of Microsoft Portugal we managed to get Ramesh Meyappan as well. We had an amazing event (from a newbie point of view) and on the Friday (yes we were asked to do it during the week in order to lower the risk!) we had around 109 people.

Q: Did you feel any resistance to holding the event and if so from where and how did you overcome those challenges?

A: Absolutely. First of all – people are afraid of changes, any changes.
I was aware that this idea was coming from some crazy guy whom nobody knew and it must have appeared as a maniac idea.
I just ignored those who expected failure, I explained to those who had doubts and recruited support of those who believed in what we were doing.

The most important thing when organizing a community event is to have a community PASSION. Passion will help you – it will lead, it will guide, it will open doors, it will find a way. The first key to any success is having the faith – you gotta believe in what you are doing. You gotta do it for the much higher purposes that you as an individual can strive for.

The second most important thing is the plan. And not just 1 plan, but multiple layers of plans.
Plan A -> Plan A.A -> Plan A.B -> …
Plan B -> Plan B.A -> …
Plan for success, prepare for the failure. Plan for everyone to support you. Plan for a lack of support. Prepare for everything. Be ready to face problems, your faith in your mission will give you the necessary courage.
Do not be afraid of being abstracted and rejected. Once you get on the wave of success, everyone will want to join because of opportunities of being associated with that success.
Use this desire of success and plan for it. Plant seeds that will grow if not this year, than maybe the next one.

Q: I think out of any event I have been to, your team has to be the closest, most single minded entity of them all. Can you tell me about your team, the individuals within it, and why they are so special?

A: I can’t say anything about other events because I have never been a part of any other team, but my team is what makes things work. Having people such as Paulo Matos & Paulo Borges who are core members is what makes it what it is.
I can trust and entrust tasks knowing that some things will be done much better, compared to any of my personal efforts.
Paulo (Borges) is one heck of a creative guy who helps to invent stuff. He can take a hit from any side and keep on carrying on like no other person.

Paulo Matos is the like no one else in this galaxy when it comes to financial or governmental or bureaucracy task. He just gets things done. He runs workshops and I just check on the progress with him from time to time to see if I can be of any help. Apart from that, it is just him running the show. Both Paulos are helping to execute the Saturday event and we naturally split the tasks according to the capabilities of everyone.

In other different areas we have Pedro Simões running our partnerships and sponsorship relations and he is doing one amazing job. He just thinks from another perspective – and it is no surprise that one of the biggest IT firms in Portugal is totally dependent on him. If we had to replace him we would need a whole team of people with deep knowledge of multiple disciplines for that. :p

Vitor Pombeiro is our arrival & departure master managing the schedule to decide who is collecting and dropping off speakers and this year we also have have Murilo Miranda running the speaker dinner. So far, I praise his efforts. He is on time and delivers updates regularly – I am happy as anyone in my position can be.

Finally we have one more important addition to the team in André Batisita – his availability and desire to help motivate each one of us and get some of the “smaller” tasks done, have already made a very BIG difference in our organisation.

Thanks to him and his resources we have every piece of content printed and prepared on Sunday – 6 days before the event!

Yeah, I wish everyone had such a great team and if you ever see our event as successful, it is because of them.

Q: Leading up to your events you appear to get quite stressed and concerned -or perhaps more accurately described as very attentive to detail! Normally you are outwardly the most relaxed person I know, so explain to me exactly how you and your team are feeling in those final few hours before your event is due to start?

A: It is because we are trying to achieve perfection, but knowing that it is impossible, but we still try. I am a strong believer in improvement and I don’t want to fail. I do fail a lot and I am trying to improve. I want to see things running the way we envision them and I do get disappointed when it is not happening, but it is a process and I am keep on learning & improving.
This year we are trying to lower stress by preparing everything in advance, like any printed materials are already done and packed.
We have well defined plans and we shall try to get them right. :)

Those final hours, oh those final hours …
Its all about anxiety, we are working around 9 months on our event, so you know – when you arrive to the final moments of those thoughts, conversations and dreams …
I feel happy arriving there, I feel exhausted, I feel like I am on the top of the mountain and like riding with speed of the wind. I am anxious waiting to see it happen and I am willing to see the results of our sleepless days and nights.

stainglassQ: SQLSaturday Lisbon holds a very special place in my heart because it was the first ever SQLSaturday I spoke at despite having spoken at numerous events around the world. I believe this was the second event that you had ran. For those of us who did not manage to go to the very first event, could you describe that event for us and how it all went?

A: The first event was a simple 1-track event where everyone were on the verge of having heart attack. Unknown and inexperienced guys running one of the biggest community events – insane!!!
In my books, through all the nerves, ups & downs – it was still amazing. I remember so many little insignificant details, such as the place where I was sitting, words I was saying, etc…

I remember all of our speakers giving the best they could and the crowd were a little unsure of how to interpret what we were doing (what? a community event? what the heck is that?).

We came to the final part of the event feeling happy & exhausted. Feeling complete. Feeling like we did something that we have planed and desired. Looking for more. It was like an entry (into the history books), but we felt that we could do so much more, so much better.

Q: Having a mix of English language and Portuguese language speakers on the agenda, it must be very difficult to decide the balance of sessions. I have heard of other European events having these discussions and wonder how much consideration goes into choosing a session based on the language of delivery? What are the challenges you face?

A: This year our event will be 100% done in English, especially since we have attendees signed up from 12 different countries. We aim to do our best to complete our vision of what we are trying to achieve, we select sessions which are running into the direction that we are moving in.
Some choices are tough and sometimes we get a lot of critics, but that is a part of life.

I think that the biggest challenge is to stay true to your mission and to your values. Those things come into question regularly by others, and by ourselves as well.
Staying the course can be tough and taking unpopular decisions is also difficult, but necessary.

Q: As a speaker I find it really interesting delivering sessions to a different cultural audience. It is harder to judge what is funny and exactly what they expect from you. For the benefit of English Speaking presenters at this year’s event, what tips would you give them for a successful session?

A: Be yourself. If you are true to who you are, people will feel it and will trust you, even if they don’t completely get it. Don’t try to be funny, just try to be objective and friendly. Make people learn together with you. Learn from them.

Q: I think more than any other event, the hospitality and welcome you guys show to travelling speakers is second to none and a benchmark for all SQLSaturdays and IT events around the world. Tell me, is this an intentional thing or just a cultural Portuguese trait?

A: You are doing amazing job yourself as well, mate!
I have just 1 measure of success – Treat others like you wish to be treated yourself.
Our speakers are crossing thousands of miles to get to our event, and the least we can do is to warmly greet them and to take the best care possible.
I see every speaker as a personal friend, so I feel like I should try to take care of each one of them.

Q: I suppose I really should mention the pre-conference sessions you are running this year. You have managed to land a fantastic selection of big name speakers including my good friend Edwin Sarmiento. Can you tell me about these guys, their sessions and why people really should attend them?

A: We have 5 amazing pre-conference sessions this year:
Edwin is doing High Availability, Tim Mitchell is talking about SSIS, Milos Radivojevic is doing a great stuff for developers (note that he is a developer for Bwin.Party, one of the most-talked companies in SQL Server space at the moment), Paul Turley is doing great precon for BI on Friday and Brent Ozar will take it on the next level with Virtualization Storage and Hardware for SQL Server. All DBA’s are very much invited. For just 120€. :)

We have the following sessions:

Thursday 10th April

Tim Mitchell – Real World SSIS: Survival Guide (web|twitter).

Milos Radivojevic – SQL Server for Application Developers (web|twitter).

Edwin Sarmiento – High Availability & Disaster Recovery Deep Dive (web|twitter).

Friday 11th April

Paul Turley – Complete BI Solution with Office & SQL Server (web|twitter).

Brent Ozar – Virtualization, Storage, and Hardware for SQL Server (web|twitter).

For more detail, please visit here.

Q: You can refuse to answer this question if you like (bearing in mind I might steal your idea ;)), but if you could pick anyone at all to give a pre-conference session at SQLPort, who would you choose and why?

A: Well for a start I’d pick all those guys who are doing them this year!
We hand-picked them from a good number of submissions.

Q: Before I visited your event for the first time, I had never been to Portugal before. Whilst I know that many British tourists (sadly) bypass mainland destinations and head to The Algarve, this had never really appealed to me. I think it is fair to describe Lisbon as a very beautiful and historic City, could you explain for those fortunate enough to visit, what exactly it has to offer them and why they should visit?

A: I guess it all depends on one’s priorities. Lisbon has it all – from historic Roman ruins to beaches for swimming and surfing, but the Algarve attracts those who are looking for a different type of entertainment. I am not a big fan of that type of culture. Catching a tram on the streets of Lisbon, getting a nice food with red wine, listening to Fado (traditional Portuguese music) are some of the things that help me to find my true self.
Some great Britons came to Portugal and found a peace of mind and heart. For example, Lord Byron described Sintra (a place near Lisbon) as a “glorious Eden”.

Q: Last year on the Sunday after the event you took the speakers on a rather fabulous trip to a Castle (Sintra) on a very high hillside. Have you anything similar surprises planned this year?

A: Yes. :) ;) ;)

Q: At SQLSaturday Cambridge and many other events around the world, Portuguese speakers are becoming an integral part of those events. Once, when I asked you to remind me who a particular Portuguese speaker was, I remember you telling me that “(he) was only the best SQL trainer in the whole of Portugal!”. Could you explain why your country has so much technical talent?

A: Like any other nation we have some great people, but there are a couple of things that might be helping develop talent. We are a small country (just over 10 Million) and are accepting of change. In the tech world we are very open to new technologies and a lot of pioneer programs are actually being run in Portugal without a big fanfare. We (SQLPort) try to help people to transform and to achieve their dreams.
But there is one big flaw that my nation has – a lack of marketing knowledge & exposure.
I don’t think that some of the most capable people I know are publicising themselves in any way. So it stays a kind of a hidden secret, waiting to be discovered.

What's for dinner!

What’s for dinner!

Q: I’ve had discussions with you and your team before regarding the location of Portugal with respect to the rest of Europe (Portugal runs along the Western most tip of the European continent, bordering Spain). Do you think that this location makes it harder sometimes to “sell” Portugal as an international IT powerhouse and could this ever be achievable in this slowly changing world without borders?

A: Absolutely. Not many people are travelling through Lisbon and this “hurts” any potential new ideas and business developments. We are getting some of the best professionals in the world, such as yourself, willing to come over and to share knowledge, so I guess it is a question of time before the results will appear.

Q: From discussions I’ve had with a few sponsors I think some of them mistakenly overlook your event for the reasons just mentioned in my previous question and yet they forget the reach of the Portuguese IT Community in and around Europe and the UK. Would you agree with this statement, and could you say why a company really should consider sponsoring SQLSaturday Lisbon?

A: I believe so. I have argued much with international sponsors before, but I understand that everything has their own priorities.
I can guarantee 1 thing – we try harder, we bust ourselves further than we have ever thought we could. I see SQLSaturday Portugal as truly international event – speakers from 17 different countries, attendees from 12 different countries, countless MVP’s & MCM’s.
It is a perfect opportunity to get to know the local community (which is very strong), but also get great talks & insights from some of the most forward thinking specialists on this planet.

Q: Niko you have the ability to speak at least 4 to 5 languages fluently (which by the way makes me incredibly jealous). I remember you telling me in the past that the way you learn these languages is not by using the traditional approach, could you explain your special technique again for the benefit of people like me?

A: I try to learn any language in the same way I believe any child would learn it – hearing and repeating. Making mistakes and not fearing sounding profoundly funny. Always improving.
I connect objects that I see with the sound of the words that I hear, I don’t map it to any other language (which is the normal way a language is taught).
I don’t think this method makes you good at translation but once you switch the language – it will flow, because you will feel it.

Q: I have often observed that multi-lingual individuals are usually very intelligent people, and I wonder if their ability to speak multiple languages is simply a result of their intelligence or something more. What I really mean by this is that I wonder if you can solve problems thinking in one language that you would struggle to do so in say you native tongue?

A: I honestly don’t know how true it is, mate.
It sounds very beautiful, but I never thought about it.
Listening, trying and repeating are the keys for any success.

Q: And if you had to recommend that a person learns only two languages fluently, which would you choose and why?

A: English – is the most important language right now.
Chinese – this is the most important language for the future.

Q: For his year’s event do you have any surprises in store that you can tell me about?

A: Be social my friend, be social! :)

Q: Now before I go, I really have to ask you, which Football team is the best in Lisbon? Sporting or Benfica?

A: My personal preferences are unimportant but for what matters I love the color blue… 

In my extended family we also enjoy green colours, but that is as far as I am ready to go in the Portuguese championship, but for international games – I can proudly wear any color of any Portuguese teams.

As always Niko, it has been an absolute pleasure to talk to you. I am sure your event is going to be another great success and showcase for the SQLSaturday Community. I’m really looking forward to it and seeing what you and your team have in store for us all!


lisbonexpSQLSaturday Lisbon is due to commence this week, starting with the Pre-conference sessions this Thursday 10th and Friday 11th April and you can book one of these fabulous sessions by visiting here.

The Community day is being held on the Saturday 12th April and if you are lucky you can grab a registration by visiting here.

And if you see me, make sure you say hello!

Posted in Community, learning, personaldevelopment, publicspeaking, sqlsaturday, SQLServerPedia Syndication | Leave a comment

Inside SQLSaturday: Jonathan Allen & SQLSaturday Exeter

In the first of a new and ongoing series of Inside SQLSaturday posts, which I hope will give you an insight into the phenomena known as SQLSaturday, I would first like to introduce you to Jonathan Allen (blog|twitter) who currently runs the SQL South West User Group, which is a PASS Chapter and also the parent organisation behind SQLSaturday Exeter. Last year Jonathan led his first successful SQLSaturday Exeter and is currently preparing for the second on Saturday 22nd March (the pre-conference is on Friday 21st March). At the end of this year he also completed his two year term as a UK PASS Regional Mentor (working alongside myself), but many of you will probably know him better under his alias of @FatherJack on Twitter. He also is a frequent blogger on Redgate’s Simple Talk platform.

father-jack-awardQ: Hi Jonathan, thank you very much for agreeing to the interview and answer my questions. I’ve been wanting to interview you for a while now and I’m very much looking forward to your replies. Before we get started though, I have to ask about your Twitter handle and the inspiration behind it?

A: Fatherjack? that comes from way back. When I’m writing code it’s generally easy to tell how it’s going based on the language emanating from my desk so I got the nick name of Fatherjack in the office during a time when it wasnt going so well. My colleagues felt I was very very similar to Father Jack Hackett from the Channel 4 Father Ted comedy series. I had to create an user name on a website and it was the first thing that came to mind, gradually it got used on more and more sites and now it has reached the point that people often think I am actually called Jack or that I am in some way ordained!

Q: If I am not mistaken, your Twitter Avatar is probably one of the longest running unchanged pictures; where does the Mug come from and have you ever flirted with the idea of using a photo of yourself?

A: The mug was bought in the Brighton Lanes while I was attending SQLBits there. The photo was taken when I was testing the camera on a new phone at my desk and then needing to put an avatar on Twitter soon after. Again, its been around so long people know me by it. Would I change it? No probably not now I’m happy being a mug!

Q: Tell me about your SQL User Group and how it all got started?

A: SQL South West had its first meeting in May 2011 but it all started at SQLBits in York in 2010. I was speaking to Tony Rogerson (twitter|blog) in the community area and asked where my nearest User Group was. Turns out it was too far away to attend regularly and Tony simply said ‘start your own then’. It took all the time from Oct 10 to May 11 to get things organised (location, attendees, speaker etc). We’ve met pretty much every month since then.

Q: I spent an enjoyable 10 years growing up in the South West. For those who are not too familar with the region, can you say why it is so special and why they should pay it a visit?

A: The South West (specifically Cornwall, Devon and Somerset) are a wonderful place to be, there is plenty of space to get away from it all – we have Bodmin Moor, Dartmoor and Exmoor close by as well as all the beaches and coastline. The pace of life is relaxed and it all makes for a pretty great experience whether you live here or just come and visit us.

Q: Building upon the success of your User Group, last year you hosted your first SQLSaturday in Exeter. How do you think it all went and what lessons did you learn that are proving useful this time around?

A: I think we did OK, certainly based on the feedback from the delegates that came along. We had a short time from launch to the event day and had Christmas and New Year in the middle so it was pretty frantic. The bulk of the preparation work was done by me and Annette (my wife/ @mrs_fatherjack) but with lots of help on the day from a lot of very generous people. This year we launched a lot sooner and we have recruited a team who are doing a spectacular job to take a lot of pressure off us. we have a few deadlines and ‘pressure points’ but we look in pretty good shape at the moment.

Q: One thing I admired about your event last year was the involvement of your User Group members. What tips can you give to SQLSaturday events trying to foster local involvement and contribution to the running of a SQLSaturday?

A: I guess I am benefiting from the excellent people that come along to SQL South West meetings, they are very generous and willing to help us with the event and their eager assistance is invaluable to the smooth running of the day. From the outset of thinking about last years SQLSaturday we wanted to make sure that as much as possible the event would showcase the South West – hence our pasty and pint refreshments and using local suppliers for as much as possible. This ethic flowed into asking the group for their assistance on the day and they were superb in the work they gave us, we cant thank them enough.

Dartmoor-Tor-smallQ: SQLSaturday Exeter is probably hosting the largest number of pre-conference sessions I think I have ever seen at a SQLSaturday. Could you share with us the motivations behind this and also explain to the audience why they should attend one?

A: 9 training day sessions, yes its probably the biggest change people will see between this year and last. It is certainly the biggest single cause of ‘should we , shouldnt we’ conversations while we are preparing. Essentially we had some outstanding submissions for training day sessions and when we found a second location that could support our event we thought we would try something new: a SQLSaturday training day with 9 sessions, spread over two locations. We have chartered a bus to make sure everyone gets where they need to be in comfort and on time. It should be quite a sight going through Exeter with FusionIO logos all over it! Another strong motivation is to keep trying something new, we have a 2 hour session on the Saturday, to provide a chance to get a more in-depth session on a topic and we are also mixing the event party with the speaker meal to see if we can bring everyone together and start socialising as early as possible.

Q: You have some great pre-conference Speakers, but could you pick any personal favourites and tell us why?

A: That, sir, is a nasty question! It’s like asking someone which is their favourite child! However, a special thanks needs to go to all the speakers that are travelling thousands of miles between them to be with us like Stacia from the USA, Mladen from Slovenia and Hugo and Andre who are both from Holland. Having said that, some of the UK based speakers are travelling a long way too and we are just as thankful to them.

I guess you will press me for a firm answer though so, if was able to attend any of the sessions then I think I would probably choose André Kamman’s session on SQL Server 2014. It looks like there will be masses of content that I can take back to work and implement or use as points to argue for an upgrade project to move our servers off the older versions that we run. Take a look here.

Q: Have you any advise for someone attending the pre-conference or your event on Saturday?

A: Come along with the aim of having a good time, we are a small event so you will meet some great people in a friendly atmosphere. you will no doubt see some excellent sessions on both days and there is a strong chance that you will meet some people that will more than likely become a good friend and part of your ongoing career. I know that I have made many strong friendships with people that I have met at community events that I am sure will last longer than my career. Catching up with these people at events becomes a key part of attending SQLBits or SQLSaturday or even the PASS Summit.

Q: Your event is just weeks away now, how are your finding the traditional mayhem to the run up of it?

A: Yeah, it’s pretty frantic currently. We have a lot to do but we seem to have it all in hand at the moment! It’s been a lot less frantic this year as we have a bigger team and more time in project.

Q: You are returning to last years venue and I am guessing that this has made many of the arrangements much easier this time around. Do you want to say anything about Jury’s Inn?

A: Yes, this was something we decided almost before last years event was over was that we would return to the same location. We think it suited our needs very well, was well placed for transport connections and we worked well with the conference team. The Jurys Inn staff were incredibly flexible dealing with our requests and very responsive to things happening on the day. Not only were the conference facilities very good but the accommodation was very comfortable and I am sure that the bar area will be as popular in the evenings this year as it was last.

surfsupQ: One really nice touch at the end of your event last year was the serving of Cornish Pasties to the attendees. Rumour has it that you have surprises in store this year too, are you able to share any with us?

A: The Pasty and Pint will be with us again but rather than on the Saturday when everyone is heading home it is going to be on the Friday night. Anyone attending the Friday or Saturday training is welcome to come along from 19:30 on the Friday and join in our party. We are having a beach/surf themed event so anyone in the right sort of clothing is in line for a prize. We have hired a surfing simulator and there will be prizes for best surfer of the night as well as the best (or worst!) ‘exit’ from the surfboard. Not only will delegates be there but the evening will have speakers from both days and sponsor representatives too so you can all talk and get to know each other.

Q: I also love the fact that you have managed to engage with local sponsors and contributors. How hard (or easy) was it convince them that they should get involved?

A: The ‘local’ part of the event was each to say but not so simple to put in place. People are not so close together in Devon and Cornwall as they are in other big cities so finding SQL Server users is always hard work, whether it is for our User Group or for the conference. We had great support from Nexus OS last year and we are hoping to have some other local companies involved this year. One key tenet of last years conference was that not only were we putting on a SQL Server conference but we were also showcasing how good things are in the South West. We have excellent services and resources down here and they perhaps dont get the publicity that they could or should. Hopefully SQLSaturday Exeter helps address this a little.

Q: There is a great selection of speakers on the Saturday event. How hard was it to select your sessions and could you give an insight into how your team did this?

A: Selecting session for SQLSaturday is one of the hardest tasks in the whole project. Another aim for SQLSaturday Exeter is to give new speakers a chance to have a session in front of a big audience and show what they can do. We tried to balance the session content across 4 major topic areas – Performance, DBA, Dev and BI but also to bring in new speakers as well as having some of the regulars in place too. If I was giving advice to speakers on getting submissions accepted it would be to focus a lot on the abstract that you send to the event organisers. Sometimes ambiguity or poor content here means that sessions get dismissed.

Q: You have a few first time speakers at the event. What advise what you give to them?

A: Come along and have a good time. There is noone here that is hoping you will fail or have problems. If something goes wrong then the room is full of people ready for you to recover, continue and succeed. I had a nightmare session last year: someone in the front row poured a hot cup of tea in their lap, my PowerPoint crashed and the internet connection dipped out right at the time that I needed it but we all came out of the session alive and hopefully a few people picked up a tip or two along the way. Take a look at one of last years first timers experience here : “Community Interview – What’s it like to speak at a SQLSaturday?

Q: If you manage to break away from behind the scenes and make it to any sessions, which ones do you think you will try to attend?

A: I’m hoping to get to see some of the 2014 content for the reasons I mentioned before and then I’d like to try something different and maybe get to a BI session. its something I have only ever seen from afar and I see it as a gap in my SQL Server knowledge.

Thanks Jonathan for taking the time to answer my questions, especially during this busy period. I look forward to attending again soon and enjoying the fun and SQL learning!

Great, it was a pleasure, looking forward to seeing you and a whole lot of new friends in a week and a bit!

You can find more information about SQLSaturday #269 Exeter Pre-conference sessions by visiting here and the Community day by visiting here. And don’t forget to bring your Hawaiian shirt!

Posted in Community, learning, personaldevelopment, publicspeaking, sqlsaturday, SQLServerPedia Syndication | Leave a comment

ROLLBACK damn you!

angrymanA good question was asked on #SQLHELP the other day regarding the use of XACT_ABORT ON - “If you don’t have a BEGIN TRAN COMMIT TRAN in a stored procedure, will SET XACT_ABORT ON do anything?”.

My immediate reply to this was “Yes it makes the transaction atomic. See my blog post Baby baby baby, where did our love/ data go?

In response to this, the follow-up question was raised “wouldn’t executing the stored procedure by itself be atomic?”.

So I said “not quite. An implicit or explicit transaction is not atomic by default in SQL Server depending upon errors” then “and a proc doesn’t equate to a transaction. Check out my READPAST & Furious presentation at #SQLPASS or #SQLBITS for demos”.

You really have to love Twitter sometimes for the 140 Character limit, and it really can cause a certain level of ambiguity in answers that can sometimes cause confusion or vagueness to replies, and I felt that it is probably worth elaborating further in a post where the only restriction is that of the editor (Moi) getting tired of typing!

Fellow Concurrency Junkie Roji Thomas (Twitter|Blog) later sent me a tweet which read “When there is no explicit transactions, are you suggesting that XACT_ABORT has an effect on single statement implicit transactions?”. The answer is of course no, but I knew exactly what he was getting at and he was in effect quite rightly suggesting that there was an element of ambiguity in my reply.

So at this stage it might be a good idea for me to describe what XACT_ABORT does. If you want the official blurb on this directive you can look in Books Online, but I’ll boil it down in a nutshell. Put simply, setting XACT_ABORT ON has the effect on ensuring that any open transaction will be treated as purely atomic (all statements will complete successfully otherwise the transaction will completely rollback). By default XACT_ABORT is OFF, and more importantly there exist certain errors that will not cause a transaction to fail (even though that single statement might have). Examples of these are constraint failures and lock timeouts which have been discussed in detail elsewhere in this blog.

Setting XACT_ABORT ON is one way then to ensure that your transactions become all or nothing operations (yes, just like you thought they were already!) and there are certain considerations to bear in mind when using it. Again, rather than me regurgitate them, check out my other blog posts on this subject and my READPAST & Furious presentation last recorded at SQLPASS Summit 2012 which describe its usage fully.

So going back to Roji’s comment, what he was getting at was that under the default behaviour of SQL Server, essentially the following set statements are essentially identical in possible behaviour and outcomes:

--transaction 1
INSERT INTO dbo.mytable VALUES(1)

--transaction 2
INSERT INTO dbo.mytable VALUES(1)

--transaction 3
INSERT INTO dbo.mytable VALUES(1)

--transaction 4
INSERT INTO dbo.mytable VALUES(1)

There are two important points of note. The first is that one of the reasons that these sets of statements are pretty much identical in behaviour is because we only have a single DML operation occurring in each. If we are not explicitly defining the transaction then the single DML operation would itself by default imply and begin and commit (or rollback) as a single unit of work (or transaction). The second point of note is that because we are running our connection using default SQL settings, IMPLICIT TRANSACTIONS is off (more on this in a second). So the point that Roji was raising, was that under these conditions, the setting of XACT_ABORT is irrelevant and I fully agree with this assertion.

This story becomes slightly more complicated when we start talking about implicit transactions. If we SET IMPLICIT_TRANSACTIONS ON, then the behaviour of transaction 1 changes slightly. Again, you can refer to the SQL DBA’s favourite resource (Book’s Online) for more information on this command, but essentially when implicit transactions are set to ON, it causes SQL Server to imply open ended transactions. In other words, now when a transaction has been implied, you will explicitly have to commit it (Oracle style). So in the case of transaction 1, there is the possibility that your T-SQL batch may contain further DML operations (which would all be automatically enlisted into this open transaction). In this scenario setting XACT_ABORT ON would have an effect and distinct difference to the same batch with SET IMPLICIT TRANSACTION OFF (the default).

For instance:

--transaction 5
INSERT INTO dbo.mytable VALUES(1)
INSERT INTO dbo.mytable VALUES(5)

--transaction 6
INSERT INTO dbo.mytable VALUES(1)
INSERT INTO dbo.mytable VALUES(5)

In the example above transaction 5 is not quite the same as transaction 6. Transaction 5 could encounter a constraint violation by one of its statements, but still commit the other, whereas transaction 6 would automatically rollback in that situation.

In summary, you should always be careful when changing the default behaviours of SQL Server, and more importantly even when you haven’t, make sure you understand that the default behaviour matches that which was expected and required.

The setting of XACT_ABORT is irrelevant for single DML statements in isolation since they are ALWAYS atomic regardless, however the complications (and considerations) arise when a transaction spans multiple DML statements and could result in different behaviour to that which you were expecting. Setting IMPLICIT_TRANSACTIONS to ON will result in the duration of a transaction to last until it is explicitly committed and therefore the scope of your transaction might not be so obvious to you simply by looking at the code.

Good luck!

Posted in Concurrency, sql, SQLServerPedia Syndication | Leave a comment