Blog

Why a Boot Camp?

Why a Boot Camp?

Boot Camp LogoAs a some of you may already know, I am now offering SQL Server boot camps with the first one being this July in Washington DC. Over the last couple of weeks since I announced this I’ve had a few questions as to why I am offering a boot camp and more importantly, how is it different from what anyone else offers?

It was a great question, and really got me thinking as to how this would be different and worth going to instead of a “Big Box” boot camp. Well, let’s go over a few things that will hopefully help clear those questions up some:

Why are you now offering training instead of letting all the others just do it?

I have invested a lot, I mean thousands, of hours and dollars on training over the years. I bought books, went to boot camps, took online courses, pretty much, you name it I may have tried it. I did learn from them, but it was all sort of dis-jointed. What I discovered was a lot of training opportunities would advertise focusing on the fact that they would teach you what you need to get through the Microsoft’s SQL Server certification exams, to then help you do “your job”, whatever that happens to be. This is fine, and I used to think the certifications were very important. After years of being in the field and managing teams, I learned that there are a lot of people with certifications that can’t really do the basics that the job demands. For me, this led to a lot of frustration. Frustration in the inability to find people that could do the job required, and also in why this was more common than I ever thought it could be.

When I decided to create a boot camp I wanted to do something different. I didn’t want to make it about the certification, or the test. I wanted it to be about being able to do the job to the best of your ability. This is why I am taking what would normally be covered in a 6 to 9 day course and condensing it into just four days of training. I am not making it an easy, get away from work training session. This is being designed to push you to your limits and make sure you know what you need to know to do the job. It is no easy task to condense that much and make it all work, but by trimming out a lot of the excess and focusing on what is really required to do the job I know I can make the attendee a stronger candidate at their current, or any future job. I am even going further to develop some other retreat type of training events to allow people to really focus on the learning, while having a good relaxing time so the learning sinks in deeper and stick stronger.

 

I want to share my passion for SQL Server

Over the last 18+ years I have done a variety of jobs and tasks involving SQL Server in one way or another. That 18+ years ago is when I actually made a conscious decision to eventually become a DBA. Last year I was asked to report some training courses for LearnNowonline.com. This introduced me to the art of building training and courses, which I’ve done in the past in some way or another, but now for broader audience. After receiving great responses from the people who took the online courses I created, along with getting great responses from my presentations at SQL conferences and various user groups, I felt it was the time to venture into the training of SQL Server users. I spent years working my way from being an end user of data through the development of systems that use data, business intelligence done upon that data, and finally as a database administrator. This is giving me a unique view of how systems and data work along with the skills to communicate between various users of that data. Be it an end-user, report writer, developer, or even management. I have found I can bridge that gap to get common understanding. Using this ability I am creating the boot camps to be flexible enough to adapt to who is attending to learn how SQL Server works.

 

I want to do something different when it comes to a boot camp

There’s a lot of things I have on my personal checklist that I wanted to make sure were included in any training I offered.

  • I want the attendee to not just have a printout of the presentation slides like many training courses offer. I wanted them to have an actual handbook that covered everything done in the class as well as some extra exercises and information. It should be a true reference book and encourage the attendee to continue their education on their own. That is why each attendee with get a physical copy of the handbook I am creating, as well as a digital copy.
  • I want to provide a copy of the software, SQL Server, to the attendee to they continue their studies on their own and explore the tools. Right now I will be providing a copy of the evaluation edition of SQL Server 2014, good for 6 months. I am working to find a way that I can provide a fully licensed version of Developer edition for the attendees.
  • I want to provide the free scripts and tools that I have found useful over the years. There are a lot of tools and scripts out there, and I have created many of my own over the years and will include those that I have found helpful in dealing with being a SQL Server user.
  •   I want to train in environments that the attendee can do hands on training as well. I have partnered with a company that will give me a complete virtual lab to work in. Attendees can bring laptops, or use the systems provided to perform hands on learning. Time is being allotted in the 4 day intensive boot camp to allow everyone to get hands on experience. I have not experienced a lot of training that offers this depth on in lab on hands training. I know it makes a difference versus just hearing and seeing the work being done. The venues chosen also offer us the option to truly focus by providing breakfast, hot catered lunches, snacks and beverages all day long.

The first boot camp is coming up this July in Washington DC. You can get more information here.

As an added bonus, since the venue I have partnered with provides a virtual environment, there are thousands of locations around the country in which I can offer a more local boot camp if you are unable to travel to Washington DC. If you are interested in having a local boot camp, contact us and we will see what can be done. If you are interested in having a private boot camp or other training for your company, contact us and we can discuss the options we have available.

 

What the Hekaton ?!?!

I was playing around some with SQL Server 2014 CTP2 this past weekend, just before the real release today, and noticed something odd when I was playing with the new in memory OLTP feature, named Hekaton.

While I was at SQL Saturday #262 in Boston things worked fine. Same goes for my being at the hotel and even at Boston Logan airport while I waited for my multi-hour delayed flight home.

It was only once I got on the plane that I found Hekaton wasn’t working correctly anymore.

I went nuts trying to figure it out what was going on and get it working. We started to land, so I quit trying until I got home. Then everything worked fine again, in fact it was working really well. I even got better performance from Hekaton than I did before. I dug in a little deeper and found out it was my internet connection making a difference. I just got a new insanely fast router (NetGear NightHawk AC1900) at home and coupled with my business grade high speed internet connection Hekaton was working really fast!

It was the only difference between everywhere that I had it working, and the plane, where I had one of the ones still in existence without internet connectivity.

Now, as a side note:  I was shocked and mildly amused when I heard about this a few weeks ago:

Did you know you can get Oracle to run in a Microsoft Azure environment.

No, it’s true! Here’s the link to verify that as of 3/12/14 you can do that! http://www.windowsazure.com/en-us/campaigns/oracle/

Considering the long time rivalry between Oracle and SQL Server I didn’t expect this at all. Then I started to put it all together.

Hekaton is using an Azure based Oracle instance to store the SQL OLTP data in ‘memory’!

It makes perfect sense! Oracle has always been said to be faster and better than SQL Server. Just ask any management level person working in the government sector or at a large business.

This has to be why it won’t work for all applications immediately and some ‘recode’ is required to make ‘applications more tolerant’ of the data in memory.

Sneaky, sneaky Microsoft. Slowly letting the enemy infiltrate the ranks to get better performance and boost market share with new ‘Hekaton’ features.

index

Ok, I know this is a touchy subject to think about today. After all, it is April Fool’s day.

But it is the official release date for SQL Server 2014 and this just seems to be too big of a coincidence.

Chew on that for a bit.

 

When Data Should Walk Out The Door

When Data Should Walk Out The Door

Data Walking

What would you do?

What would your reaction be if, one day, all that old data you’ve been holding onto for years suddenly got up and walked out?

I’m not talking about unsecured data being hacked, personnel issues, malware in your network, or even HVAC systems with too much access to the network.

I’m talking data that suddenly, somehow, becomes self aware and rather than sitting around rotting away while you do nothing with it, decides to leave.

I recently read an article about toasters that act this way.

Yes, toasters.

Those devices that produce either charred lumps of what is presumed to have been bread or lukewarm bread that soft butter easily tears apart. Basically, a series of toasters were networked together if one was not being used, it would complain and want to go somewhere else. You can read the article here.

Now, I’m not suggesting you try to make you database and data self aware, though that would be very interesting; but I am suggesting you take a serious look at what data you are holding onto that you really don’t need.

If you are holding onto years of old data, and customers, clients or users only ever look at the last 12 months, do you still really need it all?
Maybe you need it for compliance with some regulation. We all know there are a ton of those out there, but does that regulation state it must be sitting in your live production database and immediately available? Odds are no, it doesn’t. It probably states that the older data has to be accessible in a reasonable amount of time. What’s that mean? Well, it depends on how “reasonable amount of time” is defined. If you have a couple of days you can probably restore a backup with the data from a tape that was sent offsite. This means you can get a good backup specifically with the old data, then purge it from your production system. Then you can sit back and marvel at the lack of expense required to gain a fair amount of performance & storage because you have much smaller data sets to work with.

An alternative is to archive old data out to a data warehouse environment, if you really need it. Even then, look at the warehouse and figure out just how much of the data you have there that is really required. The odds are that you have a lot more data than you will ever find useful. Just saying ‘It’s a warehouse, it’s designed for holding all the old stuff’ isn’t a really valid justification for holding onto worthless, old data. Cleaning up the warehouse leads to more important data being able to be stored, and retrieved more efficiently.

If you insist on keeping the data around in your primary database and you are lucky enough to be using Enterprise edition of SQL Server, you could look into file groups and partitioning your tables. This way you can direct your more recent, active data onto faster hardware and focused indexes to help with performance.

If you are not running Enterprise edition, you can apply for the TV show Hoarders, here.

And Then There Were Two

And Then There Were Two

And then there were Two. Years that is.

Two years ago WaterOx Consulting, Inc. was founded and has been growing ever since.WaterOx Logo

The concept of WaterOx Consulting, Inc. started many years ago, but legally on paper, it was started 2 years ago yesterday. As anyone running a business would attest to, the time flew by regardless of the level of fun. There are days I would celebrate a new client or a presentation well done. Other times I would sit worrying about how the mortgage would be paid as well as feeding the family. Even today, I am working on some new presentations and thinking on how to drum up some new business and I almost blew by completely missing this milestone in any company’s life.

A lot has happened and I want to take a little time to reflect back on a few things just to remind myself of what has been accomplished in this short of a time frame.

Company:

  • Picked up 9 new clients.
  • Grown through networking, without advertising campaigns.
  • Clients range in size from a small boutique chocolate shop in NY State, to a large data warehousing and analysis company in the DC area to a few large online retailers.
  • Developed Egret 1.0, a SQL monitoring tool I use as a consultant that works with multiple locations and clients and centralizes the data for my analysis.
  • Started developing Egret 2.0, a cloud based version of the Egret monitoring system potentially available for public use.
  • Successfully registered the trademark for Concierge DBA® – the long term DBA services WaterOx Consulting provides.
  • Built our website (so far) with blog, photos, and videos pertaining to SQL Server and actually had people read things!
  • Created & recorded a successful full length SQL DBA training course for LearnNowOnline, as well as a series of courses for Windows 8 / Server 2012 Administration.
  • Accepted into the Google Helpouts pilot program and now offer services online through the program.

Community:

  • Presented at my first User Group (NOVASQL) last year.
  • Started the PASSDC User Group with Matt Velic (b|t) and Paul Rizza (b|t) in April of 2014 and have grown to over 120 members.
  • Presented at my first SQL Saturday in Rochester, (which is coming up soon, but I can’t be there this year).
  • Presented at 6 more SQL Saturdays over the past year and attended over 13 in total.
  • Presented webinars on SQL topics for LearnNowOnline community members as well as PASS User Groups.
  • Presented my ‘Optimizing Protected Indexes’ session at the Information Security Summit in Ohio.
  • Co-ordinated the last 2 SQL Saturdays in Washington DC.
  • Cruised 3 times on SQLCruise (1 Alaska, 2 Caribbean).
  • Attended PASS Summit in 2013 for the first time.
  • Was a finalist in 2012 for Red Gate Software’s Exceptional DBA award.
  • Ran for the PASS Board of Directors and actually made the final slate! I had to withdraw due to some issues that came up with clients, but I will most likely run again.

Not to shabby. I am still striving to find more clients and more ways to give back to the community. This year I am looking forward to trying to get Egret 2.0 out for use. I am also excited to be working towards offering a couple of multi-day training courses in D.C. and other areas, including a week of private SQL training in 2015 in the Bahamas. Keep tuned for those exciting announcements & offers!

All in all the first couple of years, like any new venture, have not been easy but things have worked out pretty well so far.

This was never intended as a get rich quick scheme (trust me, it would be nice, but it isn’t making me rich at all!) but a way to be able to provide outstanding service and help to SQL users and the community.

On to the next 2 years, and more.

2013 infographic – a year in review

Infographics! it’s a trend right?

OK, so visualizing data is always fun (if you are in our line of work anyhow). There are a ton of amazing infographics out there & we figured we would try our hand at making a very small, crappy infographic of our own to highlight what WaterOx Consulting accomplished this past year. At an initial glance it doesn’t seem like much, but trust me there was a lot that happened this year to keep everything moving forward.

There are also lots of exciting new things coming own the pipeline for us in 2014 that we are very excited about and will share them as they come to fruition!

Enjoy!
2013 infographic

Counting Work Days

holidays aren't work days

Don’t forget that most holiday’s aren’t work days.

Recently while working with a client I was faced with some seriously convoluted code to count the number of days between a couple of dates.

Usually this is not a big issue, a simple datediff() works, but this also had to take into account customized options for their application.

Options such as excluding the whole weekend, just Sunday or including weekends as work days. Oh, and there are company defined holidays to add to the mix.

Can’t forget them.

The code being reviewed was performing all sorts of look ups and calculations to figure out how to remove the appropriate days from the count of days between the dates.

I pondered over this for a while and after finding a great response on stackoverflow from Aaron Bertrand (T|B) I was able to come up with the following solution to help with performance and to help simplify the coding.

I was going to just take a table and populate it with all the days that could be in the ranges being looked at; in this case the year 2000 through 2030.

Once the table was populated, I would then go back and based on what criteria are required, mark certain dates as non-workdays.

It sounded simple, and it turns out it kind of was.

The client already had a holidays table that the client would input all the company holidays into.

The table is very simple and here’s the code to make a similar table:

There was also a value in the applications configuration to either include weekends (0 – zero),  include Sunday only (1) or exclude Saturday and Sunday (2).

For this I added a look up to the configuration table, but if you are always excluding or including certain days you could just as easily hard code the condition into the procedure I created as noted later.

To begin though I needed a table to hold all the dates. I opted to name it Calendar and here is the code that created it:

There are only 2 fields that I really needed in the calendar table. The date and a flag to indicate if the day is a ‘workday’

Once the table was created, it was time to populate it.

There are many ways to do this, but I wanted some extra flexibility if the table had to be reloaded or changed to include more dates, so I created the following stored procedure.

Lets go through this procedure real quick:

First it truncates the Calendar table so we can start with a clean slate.

Then a simple insert is performed.

The breakdown of the insert section is as follows:

This is just a simple insert statement to begin with, then it gets fun:

By using the cross join to the sys.all_objects table and calculating the row_number() for the number of days between the start and end date a sequential list of numbers from 1 to the number of days is created. The “as x ( n )” portion returns the value on each row to the outer select statement where the number of days is added to the @startdate parameter value to generate a date. This date is then inserted to our Calendar table. The 1 value is being used to hard code each day as a work day.

Next the session is configured to treat Monday as the first day of the week. This just makes it easier to tag Saturday or Sunday as 6 & 7 rather than SQL’s default of using 1 & 7.

A nice thing with SET DATEFIRST is that it is for the session only. Do note: It will not affect the DateDiff function. That always has Sunday as the first date to maintain its deterministic nature.

Now it is time to update the work days.

In my situation I had a table that could be modified to change the number of weekend days to be excluded.

This section basically checked the value in the configuration table and then changed the workday indicator in the calendar table appropriately.

I was redundant with the include weekends section as the initial insert of the dates defaulted them all to workdays. I am pretty sure it was subconscious DBA paranoia that made me do that.

Finally it was time to mark the holidays. This was done with a quick update of the dates that were in the Holiday table the client’s application had.

Overall this populated the table very quickly and the size was fairly small.

To use the table to count the number of days you only need to count records in the calendar table for the date range being looked at where the isworkday flag is marked.

Something along the lines of this:

Again, huge thanks to Aaron Bertrand (T|B) for providing the start of a nice solution to someone else that helped me, my client and will hopefully help you.