Tuesday, 15 January 2019

How to be Big Brother in Microsoft SQL Server!!!

A few years ago I was working as a SQL Developer at a large company in Sydney’s CBD. Although an established business, the environment was relaxed and based on many of the “start-up” type businesses which you see around today. For example the office was filled with comfortable lounge chairs and TVs everywhere, plus an on-site barista and cafe.

Of the roughly 120 staff in the Sydney office, over 100 would have been in IT, with probably 20 teams spread across everything from mobile web and app development, to myself in the database team and 3 or 4 full-time SQL DBAs.

As one of only a handful of people who had access to the production SQL environment, and the ability to view and change the production data, I would often get tickets in regards to importing or exporting data.

One day a request came in for me to import some data from Excel, and then use it to update some records.

To assist with this, I used the SQL Server Import And Export Wizard to import this data from Excel into a new table.

Within minutes of me doing this, my phone rang. It was the senior DBA. Ie, the man who all the database programmers and DBAs reported to.

His question was simple … “why did you just create a new table on a production database?”.

Even though I had all the boxes ticked from the testing team, and another senior manager (for which this task required) ... for whatever reason he didn’t see the authorisation and wanted to know what I was doing. When I explained it to him, he approved it and had no issue with what I was doing. But … it got me thinking … how did he know?

Not long after, I’m at a smaller company and am THE DBA when I notice that a .net programmer had snuck in some DDL changes into a release script. Once released into production, it caused some issues that did not appear in UAT. It was a lesson for me in that I needed to performance test the changes that came through via the developers.

As it was a smaller company (especially in terms of IT resources), I now wanted to keep more of an eye on what the .Net programmers were doing. And it reminded me of what happened to me earlier at my previous role - how did my boss know I made DDL changes almost immediately?

I soon discovered that you can setup what is known as Server Triggers.

There are many examples on sites such as stack overflow on how to set these up, so I won’t bother with typing the exact steps. But in short all you do is enable SQL Mail, and then create Server Triggers which are fired from the system tables. From the event which is fired, call a stored procedure which then writes the changes to an audit log (along with who did it, assuming you're using Windows Authentication) and also sends you an email.

Simply do this on whichever SQL Instance you wish to monitor and you too can play Big Brother and keep an eye on any cowboy developers! Personally, from now on I always do this on all UAT and Production instances. Often the Dev instances have too many changes made and I feel that if I get too many emails coming through saying “DDL change” then they become lost in my inbox of other alerts. But when I get one saying change(s) in UAT or production, then I can investigate … and if necessary get out the proverbial big stick!

Thursday, 10 January 2019

Database Example - How Qantas is going to know your every move ... with your permission!

Want to collect your customers' data?

Qantas is paying in Frequent Flyer Points to find out more about you.

With this #App, you earn points by telling Qantas what you do, and where you go!

(Not saying it's ethical, but the lawyers have made it permissible)


Wednesday, 9 January 2019

Friday, 4 January 2019

Brad Sugars explains this so well ...!

This 3 minute video is the perfect example of how the combination of a #database, and mobile #app, are required for all #businesses in 2019.

At I type this, I'll be at our HQ in #Caringbah until 4pm, so if you want to come in for a chat, please do!

Friday, 21 December 2018

Here's how I fixed the Reception Check-In at a Martial Arts School in the Sutherland Shire

You can read about this Android App that I wrote for the Caringbah based school on this link:


And here is a video of me explaining it:

Also, as mentioned previously, this was all coded in Microsoft SQL Server and Visual Basic ... in less than an afternoon (it attached to the existing SQL gym database)

Merry Christmas ... I'm aiming to write more in the New Year.

Thursday, 8 November 2018

Can a plumbing business use a mobile App?

To document some of the work we're doing in the App Space, we going to do some case studies based around the projects.

Here is the case study for an iPhone & Android App we just did for a plumbing business in the Sutherland Shire: 

Lisa & I look forward to meeting some other trade based businesses (Carpenters, Electricians etc ...) and hope we can help them too! 

Monday, 29 October 2018

Follow-up To Previous Post - I forbid you from showing me an outer join!

At the risk of repeating myself, and again quoting from Quora … I came across this today:


It still baffles me that even some senior, hands-on and so called technical people in IT, still do not know how to write efficient SQL.

Here is the full quote: - - - - - - - - - - - - - - - - - - - - - - - - - -

What are the pros and cons of having complex SQL queries vs. simple SQL queries, but using other programming languages to handle the complexity? Should I write a complex SQL query to handle everything?
Bill Karwin, author of "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" 

I saw a coworker, a senior Java programmer (with a PhD), struggling to get some code written. He was behind on his deadline. I asked him what he was trying to do and if I could help.
He said he needed data from two tables. He needed all data from the first table and only matching data from the second table. Where there was no matching data, he still wanted the data from the first table, but with nulls for the objects where there was no match in the second table.
“What’s the problem? That’s an outer join in SQL,” I replied.
He said he didn’t know how to use an outer join, so he had spent many hours trying to code the task from scratch using Java. He was fetching all the data from both tables, matching it up, and discarding data from the second dataset that didn’t match. He was trying to make the matching more efficient by implementing some sophisticated sorting algorithm and a B-tree data structure.
“No, really, the database can do that easily, it’ll run faster, require less code, and you’ll only fetch the results of the match. I’ll show you how,” I offered.
His face grew dark, and he literally said to me, “I forbid you from showing me an outer join!
He had invested so much in implementing his data structure that he couldn’t bear to admit he had wasted his time. He would rather finish his pages of Java code, and never know how to do the same task in one line of SQL.
Don’t be that guy. Use the right tool for the job.