Tag Archives: SQLBits

Announcing my SQLBits Pre-conference Session

panda

High Availability Lab on your Laptop!

Having been a speaker at SQLBits since 2010, I am excited to announce that I will be finally delivering a preconference session at this year’s event in Telford and it is only fitting that my session returns to subject matter that is quite close to my heart.

In 1998 I was made responsible for a SQL Server 6.5 Failover Cluster (running on Windows NT 4 Enterprise) and quickly learned the value of having test environments for Availability solutions. The only problem was that Virtualization was in its infancy, physical hardware for non-production loads was restrictively expensive for Enterprises, and not many people understood the technology! If you wanted to test out High Availability concepts and functionality, you were generally forced to do so in Production – And a lot of us got burnt!

Today we have numerous rich Virtualization Platforms, hardware is cheap and Evaluation Software is free (for time-limited evaluation), but despite all these things, I still see most companies failing to provide Highly Available Virtualized test platforms for their DBA’s and Administrators.

creeper

Don’t wait until your environment is hit by the Creeper!

In this session, I will show you how to Build Your Own SQL Server High Availability Test Lab (on your Laptop and beyond!) so that you no longer need to put your production environment at risk, and are empowered to play with varying scenarios to your hearts content from the comfort of your own armchair. Once we have our own test platform, we will then focus on SQL Server High Availability (up to and including SQL Server 2016, Windows 2016 and Azure) and take a comprehensive look from the ground up covering simple and complex topics in an easy to understand format!

After attending this training day you will have comprehensively learnt the fundamentals of implementing and managing SQL Server High Availability solutions and will understand how you can deploy these on your laptop, home network, and work test environments.

If this all sounds like your cup-of-tea, then check out the rest of my abstract below:


Session:
Build Your Own SQL Server High Availability Test Lab (on your Laptop and beyond!)

Speaker Bio:
Mark Broadbent is a Microsoft Certified Master in SQL Server, Microsoft Data Platform MVP, Chapter Leader, Virtual Chapter Leader, SQLSaturday Cambridge founder (the UKs first SQLSaturday), SharePoint Saturday Cambridge founder, SQL Nexus board member and was awarded Microsoft’s Community Contributor award in 2011 and the PASS Outstanding Volunteer award in 2012. Mark is a regular speaker at both International and National events and is an expert in HADR solutions, SQL Server migration, and SQL Server Concurrency control.

Abstract:
• Are you struggling to understand SQL Server High Availability?
• Would you like to build your own test environment?
• Would you like to practice fail-overs, adding or removing Availability Group Replicas, Cluster Storage Migrations or play with Hybrid deployments -all from the comfort of your own arm-chair?

If any of these are true then have no fear, for in this demo-rich pre-conference I will explain how you can build your own “High Availability” environment in the safety of your own laptop *1, home network or work test network.

We will start from nothing and configure your lab from the ground up until we have a fully functional working Virtualized Domain, Availability Group Replicas and even a multi-instance SQL Server Failover Cluster and then start playing with them to destruction.

By the end of the day you will not only learn the fundamentals of SQL Server High Availability, but will also understand more advanced topics such as:
• Deployment and builds
• Cluster troubleshooting.
• Hybrid deployments and Interoperability.
• Availability Group Monitoring and Performance.
• Virtualization for High Availability.
• New features in Windows 2016/ SQL 2016

*1Please note that deployment on a single host is for testing purposes only (in case that wasn’t obvious!)

Take aways:
Attendees to this session will receive access to a set of scripts, notes and a pre-built Hyper-V time-limited virtual lab for your own testing and enjoyment.

Audience:
Attendees will not be expected to have prior knowledge of High Availability, Virtualization, and Operating System configuration and Networking, but a basic understanding of these concepts will lessen the learning curve when we come to implement those things.

When the SQLBits preconference sessions are made public I will add an extra link for booking!

Big up to Karla!

I'm sad

I’m sad

Well the day I’ve been dreading for the last few weeks has finally arrived, and it is with great sadness and joy to read Karla’s announcement that she will be leaving her current role in the PASS family. Sadness because Karla has not only become a great friend and ally over the years (and I will deeply miss her in doing what she does), but joyous because I know that she is moving on to the next successful phase of her life story -one in which she can rightly reap the rewards of her brilliance.

In the beginning

I don’t know how it happened, but I somehow managed to miss Karla at the PASS Summit in 2010 during my first ever visit to Seattle. Stranger still was that I did not bump into her during my (first “International”) speaking gig at SQLRally Orlando in 2011 either. So my first time meeting Karla in person was at SQLBits in 2011, which was apparently not too long after she had joined PASS proper in her Community Evangelist role (although she did it so well, you would have thought she had been doing it for 20 years).

I’ve told her this before, but the minute I’d met her I knew that she was someone who I wanted to be friends with. I could tell she was a DO-ER. A no-nonsense, no bullsh*t professional. A straight talker. But most of all, she was very approachable, warm and had a wicked sense of humour and infectious laugh. There are very few people on this planet (when I see them) that suddenly everything seems good with the world, but Karla is most definitely one of them.

Thanks boss!

Over the years since our first meeting we have worked together professionally for many years. Karla was instrumental in helping me launch the UK’s first SQLSaturday (SQLSaturday Cambridge) in 2012 and I can say with hand on heart, that without her support in doing so, it could not have happened. I also went on to serve my 2-year term as UK Regional Mentor amongst other things. I would jokingly call her (and regard her) as “my boss”, but one who did not pay me! Being close to Karla was reward enough, for the time that I spent with her discussing or participating in community activities controlled by her, were usually very successful and happy occasions.

From our professional relationship, we have grown a friendship and I know that there is a huge number of you who will also echo my feelings. She is never scared to tell me that I am talking nonsense when she disagrees with anything I may say, but can do so without being condescending. She also has broad shoulders and can listen to my criticism (or moaning) without taking things personally, and people like her are rare indeed. She has helped support me through the good times and the bad times in equal measure, and for that I feel incredibly privelaged to have known her.

It’s not the end

Now I know what you are thinking. This post is reading a little bit like an obitury. The reason for this is simple. When Karla leaves her current role at PASS we are all losing someone special, and someone who “has our back”. It is natural for me to but feel sad at this loss, but I am also happy and encouraged that she is returning to a technical role with the possibility that she will still be part of our community, and we all will benefit from that. Either way, I will not stop having a private moan to Karla about anything that is annoying me no matter where she goes (you cannot escape me Karla!).

Karla has been pivotal in helping me grow, develop and succeed during her tenure at PASS and is leaving some very big boots to fill. I hope you (like me) will wish her the best of luck with the future and thank her for everything she has done for us and SQLSaturdays, PASS and the SQL Community at large.

Karla you’re the best!

Speaking at SQLBits XIV (after all!)

I don’t think it is a particularly big secret that I am a fan of SQLBits (more posts can be found here). I remember being at the PASS Summit in 2010 and enthusiastically talking about the SQLBits conference to those that cared to listen and was rather taken aback by the lack of awareness outside of Europe during my time there, so I like to think that my enthusiastic ramblings over the years have contributed in some very small way to promoting the conference to a wider global audience. SQLBits gave me my first speaking break, which allowed me soon after to speak at SQLRally in Orlando, so I am always grateful to them for offering me that opportunity.
Ye Olde Cluster Curiosity Shoppe

I was not going to speak this year (having also regrettably been forced to withdraw last year at the last minute for reasons outside my control) for a multitude of reasons, but nether-the-less knew the event would rock regardless of my presence (some would say more so!). As time went by, it became increasingly likely that I would attend on Saturday in any capacity, so I am now very pleased to announce that not only will I be attending, I will also be speaking again!

My session this year is one of my current favorites which I hope to polish in time for the big day. I’m presenting Ye Olde Cluster Curiosity Shoppe in which I will discuss a miscellany of top tips, warnings and advice from nearly 20 years of SQL Server Failover Clustering. This is not one for the faint of heart and you really should avoid coming to the session 🙂 …in the words of Tubbs and Edward

“This is a local shop for local people. There’s nothing for you here!”

Twelve is a Magic Number

coltsfoot_rock_small

A massive congratulations to the SQLBits team for announcing what will become the 12th iteration of the UKs finest and largest SQL Server conference. Putting on events at this scale by such a small core team demands huge respect and I sincerely thank them for doing so. I have personally learnt a ton of information and grown as a person and as a SQL Professional because of their efforts and I know many others in the industry feel the same way too.

The location this year is Telford, and I must confess to not knowing a great deal about this Town in the West Midlands. After a close inspection of Wikipedia, it tells me that Telford is a new town formed in the 1960s and ’70s from a collection of neighbouring smaller towns and close by is the famous landmark known as Ironbridge Gorge which was the first Iron Bridge of its kind in the World! I was already familiar with this due to my Mother raving to me many years ago about a Sweet shop that can be found there where you could buy lots of Olde World favourites such as Poor Bens, Coltsfoot Rock and Lemon Sherberts 🙂

As always, I look forward to see what the organisers have planned for us all, and encourage you to check out the event as the details start trickling through and perhaps consider submitting a session? I know you won’t regret it and hope to see you there!

If you need any further encouragement (really!?), I recommend that you check out my following SQLBits related posts:

T-SQL Tuesday #41 – I ♥ SQLBits
SQLBits 8 – finis!
Standing upon the shoulders of Giants
SQLBits 7 the forgotten photos part 1
SQLBits 7 the forgotten photos part 2
SQLRally and beyond
Speaker Training day

T-SQL Tuesday #41 – I ♥ SQLBits

TSQL2sDay150x150It’s been a long time since I last wrote a T-SQL Tuesday post but the combination of a great topic along with a spare 5 minutes of downtime has resulted in my contribution to the mix this month.

This time around T-SQL Tuesday is hosted by Bob Pusateri (blog|twitter) and his chosen topic is “how did you come to love presenting?”. So here goes…

In 2010 I had traveled to my very first conference as a presenter. So there I was, stood on the stage in front of a motley collection of SQL professionals, realizing the enormity of what was about to happen.

For many years I had been one of the guys in the crowd of a sea of attendees who had come to listen to awesome presentations from awesome professionals. From time to time I would wonder what it must be like talking in front of all those people -and more importantly whether I could be capable of such a feat. Did I have anything to say? Yes absolutely, but these guys were (of course) in a different league from me, and a league in which I could never hope to emulate. Ever. Then a funny thing happened one day… I decided to submit a presentation to SQLBits 7 really out of curiosity more than anything.

Oh Flip!

Oh Flip!

Months later, oh joy of joys… I got selected!

Finally when the euphoria faded, the reality set in. OH MY GOD… I’VE BEEN SELECTED!!! WHAT AM I GOING TO DO!!! This presentation was going to be my first ever presentation to my first ever audience anywhere ever and I knew it wasn’t going to be a walk in the park. There was also an extra element of excitement/ fear in that SQLBits always recorded their presentations, hosted them publicly for free on the internet, and (if that wasn’t enough) used picture in picture recordings of the presenter in sync with the slidedeck.

Yes people, even back then SQLBits was a very slick conference, and if you are going to fail at presenting, it is perhaps not the place to start :).

And let’s not forget the leap of faith that the SQLBits committee had shown by selecting me for this event. Whilst I was not entirely unknown from a social media perspective (I had been a active blogger and twitter user for several years by then) the fact remains that as a presenter I was (shall we say) a little inexperienced!

So moving back to stage where I was delivering my first ever session…

I remember looking at the audience quite early on around 5 minutes into the presentation and I clearly remember thinking I AM ACTUALLY DOING THIS!!! After composing myself, I continued the session and think it went quite well. Not only was it one of the most frightening, exhilarating, scary and rewarding experiences that I have ever had, the months and months of preparation leading up to the event improved my technical skills exponentially from where they had been before.

Now with every presentation I deliver, I always learn hugely from each experience.

After SQLBits 7 I decided that I wanted more and have gone on to present at every SQLBits since (to date). I have also delivered sessions to many major SQL conferences and community events around the world, seen places I would have never seen, have met people I would have never met and embraced the SQL Community in ways that I had never realized possible.

So to the SQLBits Committee who made it all this possible and started my enormously rewarding journey, I would like to say a special thank you!

SQLBits I think I ♥ you!

Baby baby baby, where did our love/ data go?

Possibly one of the very first things we learn as fledgling DBA’s is that transactions are used to provide “all or nothing” operations. If you ever go for a SQL Server job interview, you are almost guaranteed to be asked exactly what constitutes the ACID properties (*groan*). The A in ACID is of course Atomicity and basically represents the fact that a given set of operations within a transaction either succeeds as an atomic unit OR it doesn’t.

What I am about to discuss is probably not the biggest secret to many of the “more informed” of you, and over the years many fantastic speakers (such as Kalen Delaney blog|twitter) have revealed the truth about SQL Server’s transactional behavior.

I first presented this subject (and this behavior) at SQLBits 9 (Liverpool) in 2011, and have subsequently presented the subject a few other times elsewhere (most recently at SQLSaturday #162 Cambridge), and each time never usually fails to spark the alarm of at least one person in the audience.

When I was a relatively new DBA (and I do have to go back a considerable time) I struggled for a very long time to understand exactly what was the point of SQL Server’s SET XACT_ABORT ON statement…

…I mean, as I understood it, if transactions failed (and therefore aborted) everything was rolled back anyway wasn’t it? So what would be the point of setting XACT_ABORT ON then? This statement just didn’t make any sense to me.

Then one day an incident happened at a company I was working for at the time, in which a massive amount of data had gone missing whilst it was being processed by an in-house application. The application would collect the data from a third party vendor, shred it and do funky things to it, before finally placing the data into a SQL database. Upon committing this data, the application would send an acknowledgement message to the third party vendor to provide a confirmation the data had been successfully received and could be removed from their queue.

When the missing data was detected by the application developers, the predictable finger pointing ensued. They blamed us for OUR crummy database and obviously misconfiguration server, and we blamed them for THEIR atrociously written application that was somehow (incorrectly) deleting data.

A typical transactional scenario

We have an orders table in which we wish to remove a record that needs to be fulfilled and insert the (fulfilled) record into the fulfilled table. If the insert fails, it is important that the delete is rolled back and if the delete fails it is important that the insert is rolled back.

The code for this is as follows:

DECLARE @orderid INT
SELECT @orderid = MAX(id) FROM orders
BEGIN TRAN
   DELETE FROM orders WHERE id = @orderid
   INSERT INTO orders_fulfilled VALUES(@orderid, 4, @@SPID)
COMMIT

So in the code snippet above you can see that we are deleting the top orderid from the orders table and inserting it into the orders_fulfilled table. If we execute this code, it does exactly what it says on the tin – deletes the record from orders table and adds it into orders_fulfilled table all within a transactional context. Pretty basic huh?

Now if we rerun the code above, simply changing line 5 as below:

INSERT INTO orders_fulfilled VALUES(@orderid, 5, @@SPID)

The following error occurs…

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the CHECK constraint “status”. The conflict occurred in database “READPAST & Furious”, table “dbo.orders_fulfilled”, column ‘status’.
The statement has been terminated.

So the error message itself is remarkably explanatory. It has told us that our insert statement failed due to the presence of a CHECK constraint on the orders_fulfilled table. Upon checking for open transactions, we note that there are none. Great, this must mean of course that our transaction will have automatically rolled back and undone our delete -right? WRONG!

Upon further inspection we note that not only did the insert not complete and that the transaction is no longer open, we observe that the delete did actually occur! The result if you were not expecting it is lost data.

Going back to our in-house application, the developers had introduced some code which was shredding vast volumes of data and performing a similar transactional delete and insert, but had not accounted for the fact that the constraint might be broken by the vendor data OR what would happen if it was. Because the application was acknowledging the receipt and commit of this data to the third party, not only was the data failing to insert into the database, but was also being removed from the vendor’s transmission queue.

Why is this happening?

This is not a bug in SQL Server, far from it. In fact this behavior is by design and occurs only when certain errors are raised within transactions. As you have seen, constraint errors are one such situation and the others that you may run into are (as far as I know still not documented in one single place).

I believe the thinking behind this behavior is to avoid unnecessarily undoing work performed by all the successful statements, which could come in useful in certain expensive transactional loading operations. Besides…if you didn’t want this to happen, then you would have implemented an alternative right? 🙂

What is the solution?

Well I’ve already mentioned the XACT_ABORT ON statement, and now it suddenly makes sense doesn’t it? Since we have shown that transactions are not truly “all or nothing” operations by default, we need a way to direct SQL Server to make them atomic. XACT_ABORT ON informs SQL Server that it absolutely *must* rollback any transactions if there are ANY errors during their lifetime or commit everything.

There is a second and more common way of making transactions behave as expected and this is by using error handling. SQL Server 2005 introduced the BEGIN TRY and BEGIN CATCH syntax which hugely simplified our transaction management. Within our catch-block we can simply perform any clean up actions that we want and ROLLBACK our transaction should we so desire.

So what happens if we use both methods together?

There is a particular problem to be aware of when using error handling in conjunction with XACT_ABORT ON and this is really due to the implications of setting this behavior on. By doing so, as we have already discussed, you have told SQL Server to COMMIT everything or roll everything back. When using error handling you are enabling functionality to take custom actions within your transactions. One such action could be a rollback to a savepoint, or perhaps to insert logging information to another table.

Let’s see what happens with the following code snippet:

SET XACT_ABORT ON
DECLARE @orderid INT
SELECT @orderid = MAX(id) FROM orders
BEGIN TRY
   BEGIN TRAN
      SAVE TRAN savetohere
      DELETE FROM orders WHERE id = @orderid
      INSERT INTO orders_fulfilled VALUES(@orderid,5,@@SPID)
   COMMIT
END TRY
BEGIN CATCH
   ROLLBACK TRAN savetohere
END CATCH

The following error occurs…

Msg 3931, Level 16, State 1, Line 15
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

The error we are experiencing here is known as an “uncommitable transaction” and occurs because XACT_ABORT is explicit in requiring that the whole transaction is rolled back on error. Performing ANY operation that causes log writes (such as an insert, delete, update or even rollbacks to savepoints) will result in error. It is not obvious from the error above, but rollback is automatic and no transactions are left open when this condition is hit.

So should we ever use XACT_ABORT ON then?

There are a couple of situations that using XACT_ABORT ON is the right or necessary thing to do (with or without additional error handling). One such situation is when you have unskilled (or lesser skilled) labour executing and promoting SQL scripts into your environments. You (or your developers) want to ensure that the scripts either work OR they do not. If you use distributed transactions then you will be required to use XACT_ABORT ON.

In the scenario that you do use both techniques, it is important that within your catch-block you detect any uncommitable transactions before attempting to perform any logging operations. This is performed by detecting the transaction state and the XACT_STATE() function enables us to programatically do this.

For instance:

IF XACT_STATE() <> 0 ROLLBACK TRAN --if there is an open transaction, roll it back!
--if tran state is -1, then tran uncommittable, if 0 then no tran open, else if 1 tran is committable

Final Thoughts

Understanding SQL Server transaction management is far more difficult than you may realise and it is important that you know the implications of how your developers implement them. In the scenario presented here, we have demonstrated that if they/ you get things wrong then your database may be losing data without you even realizing it. The use of good T-SQL error handling and transaction state detection can be the difference and very simple to implement in the right hands.

Whilst it is common in organizations for Developers and DBA’s to point fingers at each other when problems are experienced in your environment, it is important to understand that this usually happens due to poor communications between the two parties and most importantly because you both care. It is YOUR job as a DBA not only to protect your data but also ensure that you educate Developers where possible. Likewise it is the Developer’s job to ensure performant and accurate application code and educate DBAs. Understanding each other’s worlds better and communicating more efficiently can avoid some of the largest (and sometimes more costly) problems with very little effort.

SQLRally and beyond

I am almost ashamed to say that I have let over a month pass since my last post and truth be told I have come close a few times without never actually pressing that submit button.

I shan’t bore you with my excuses or reasons for my inactivity but what matters is I’m back!

I’ve been seeing an increasing amount of noise about SQLRally Dallas and I felt that I wanted to share my own personal experience with you about this rather unique event (in Orlando) -for it quite literary changed my life.

Six years or so ago I joined a company with big hopes and promises and after starting a small family (and growing it further after joining) I was looking for a period of stability and a platform to learn, improve and share my skills. In short, I knew that come what may, as good or bad as things might get, I was there for the long haul.

Not long after joining, my skills and talent for other technologies were recognised and when the need for a specialized “crack” team supporting a critical troublesome in-house application, I was identified as their man. This position (I was told) would be permanent and was absolutely vital to the running of the company operation. There was one small problem -I didn’t want it and regretfully declined. My reasons were many but I had joined the company as a SQL Server SME and saw the very broad set of skills required to support one single application platform as a regression in my ever growing expertise in SQL Server (and increasing knowledge in Oracle) and did my best to explain this.

Unfortunately for me, the Technical Director at the time (let’s give him a fictitious name of Dennis) told me in no uncertain terms that this would be the last time I ever refused him. Three months into my new job, everything had turned sour overnight simply due to me being good at what I do.

Years would pass and with each new year would bring a new job offer somewhere else for an ever increasing Salary, but I would always find a reason not to take it -probably the thought of repeating the same mistakes again was always the biggest reason to avoid saying yes.

Then something happened.

Although I have been helping in forums and the like on and off for a long period of time which you can read a little more about in this post (Standing upon the shoulders of Giants) I always felt that I had more to give, more to share, more to say and more to learn. I decided to submit my first ever public presentation to SQLBits 7 and surprisingly managed to secure a speaking slot! As daunting as the whole thing was, I think I managed to pull it off -and if nothing else I know that I learned LOADS during my weeks and weeks of hard graft of preparation putting it all together.

About a month or so later I took a week off work and self-financed a trip to the SQLPASS 2010 Summit (my very first time) and met some absolutely amazingly talented people which is probably another story for another time.

Whilst in Seattle I decided that should the opportunity arise, I wanted to attempt a presentation in America and would submit and hope for the best. SQLRally Orlando was announced and my submissions went in. Although I didn’t make the first cut, my submission received the joint highest votes for the runners up and quite incredibly I was eventually selected as a wildcard! Totally brilliant and I was thrilled!

This time (unlike Seattle) I thought I would approach Dennis and see if my company would like to contribute in any way since there would be obvious technical benefits to the organization. The response did not surprise me. He said that my speaking and attending sessions at SQLRally (or anywhere else) had absolutely no benefit to the company at all. I have never forgotten the impact that sentence made upon me. The situation was made even more ironic when a few days later I was assigned to an important scalability project (for someone had personally requested that I should be the resource).

…… The title of my presentation : “Orders of magnitude-Scaling your SQL Server Data“.

Like the SQLPASS Summit, I fully financed my trip to SQLRally and used a week of my holiday entitlement and came to a decision. I realised that however much I tried to improve myself socially and technically (and help others), Dennis would never support my efforts in any way. A few days later I received a permanent job offer from another firm offering a substantial salary increase (almost double) and I ……….turned it down!

SQLRally helped me realize that the only person holding me back was myself and It was time for me make the jump. I quit and left for Orlando to give my presentation. On my return, I secured work and now when I need to finance any speaking or training events, the only person I need to convince is myself.

But what of SQLRally? I loved giving my presentation, it was very hard work preparing for it but I learned more than I can put into words at hopefully managed to communicate some of that knowledge across. From a non-speaking perspective, if you have never been to a SQL Server event and can get to Dallas then you are going to LOVE it. SQLRally is smaller than the Summit and full of lots of first timers and many regulars who will embrace you and make you feel completely at home.

Attending SQLRally could be your first steps towards something amazing. I hope you take them, and if you do, I look forward to meeting you someday soon and hopefully share a nice cold beer.


You can read more about my exploits at SQLRally here and here.

What has happened to me since my return?

Going forward I am currently :-

Dennis, I think you made a mistake with me but I know you would never be able to admit it. I sincerely thank you for every single day I spent praying for deliverance. You made me realize that if I wanted something strong enough I could go out there and get it. Through your neglect, you encouraged me to meet people LIKE me. You have given me hope and for that, I will never forget you.