SQL Saturday #64 - Baton Rouge - Presenting on Metadata

I'll be presenting at SQL Saturday #64 in Baton Rouge this Saturday, August 6.  My talk is called "Get a Lever and Pick Any Turtle: Lifting with Metadata".

My talk is all about metadata in SQL Server and using it for useful things like code generation, systems management, managing code quality.  I even use metadata to manage metadata.  I cover the standard INFORMATION_SCHEMA metadata as well as SQL Server's very powerful extended properties.  Hopefully I give a good coverage of the basic techniques as well as some ideas how you can use them to make your life easier by levering database facilities already available.

This particular presentation has also been delivered online at SQL Lunch and in person at the GNO .NET User Group.

I'm excited to be giving my first talk at SQL Saturday, and last year's event was really great.

Hope to see you there!


Online timesheets and invoicing - why is it so hard to get right?

I like and use Freckle. For a single consultant with multiple clients, it works great. You enter your time. It invoices all unbilled items and you're done. It doesn't handle expenses well, so I know I'm going to have to work around that soon. As far as notes about the tasks, it is also lacking, having tags, but not a way to submit any real description with the hours. That's fine until you need more details for some clients.

But I tried to expand my plan to multiple users and it doesn't handle it well. I want a few consultants to be able to submit their time on the clients they work on and then I can roll them up with my time on the project and submit a single itemized invoice. For instance, they can see all the invoices to my other clients. Not a great model if you want some reasonable control.

I looked at Replicon, and it has TONS of reports, but as far as I can tell, it doesn't do invoicing at all. It does the best job at organizing the project, but the timesheet is kind of awkward. And without real invoicing, it's kind of a waste of time to just have better timesheets if it doesn't translate all its great reporting into supporting information on a nice invoice.

I looked at FreshBooks, and it seems to work fairly well, but the subcontractors' work that comes in all ends up translated to one particular task - they don't really get to use the tasks you set up for a project, and you don't get any categories they might have set up. Arguably, you want them to account for time according to your project template, but ultimately they get lumped into one particular task.

I'm looking at Blinksale now and it looks like it has a fully stylable invoice and looks great, but it's missing the whole timesheets feature. It seems to handle everything else right - estimates turn into invoices. But the invoices are going to be lacking in day by day breakdown of tasks. I can't have people entering things on invoices or estimates gradually over time. That's what timesheets are for.

You have a good system for timesheets and invoicing that you like that you think might really work for a consultancy?


T-SQL Tuesday - Resolutions!: A Little Each Day

T-SQL Tuesday Hosted by Jen McCown

The official topic this month is:
What techie resolutions have you been pondering, and why?

I have resolved to work a little each day on a product I want to bring to market for SQL Server presenters to help them manage their presentations when they have code demos (e.g. in SSMS) in the middle of slides.

Not having a lot of time is an excuse I've always used, because I know it's going to take many hours to get this product into shape and I know the market is small.

But if I just put in 30 minutes each day in addition to my other business engagements, I will eventually have a product which I can use, and potentially which can stand on its own in the world and other people might want to use it.


Beware Google Mail/gmail External SMTP Feature

Last year, Google added a feature so that you could send authenticated email through another SMTP server.  This eliminated the need for the "on behalf of" Sender header.

It requires you to enter the authentication details of an account and it verifies that it can access the SMTP server at that time.

However, if that account changes (i.e. password change or anything which would cause authentication to fail), you will get no notification of email failures.  You can click send and the message will go into sent items, but will never arrive at the destination.  You will receive no bounce.  You will receive no message from Google that the account isn't working, there is no visual indication that anything is wrong.

Your mail will simply and silently not be sent.


T-SQL Tuesday - Business: Listen and Learn

T-SQL Tuesday Hosted by Steve Jones

The official topic this month is:
What issues have you had in interacting with the business to get your job done.

I have entitled my essay: Listen and Learn

I have found that most things in life obey an 80/20 rule. With regards to this rule, business is no exception. I find that dealing with the business, like dealing with anything else, requires you to listen carefully and understand not only the requests and the rationale for them but also the personal motivations of the people behind the requests.

There are many myths about "business" people - the first is that only "the business" is good at business. Well, only about 20% of business people are really good at business regardless of how you define it. And even out of those, there are other factors besides skill involved. A lot of business is not "business" - it's luck and human nature. And plenty of people who are "good" at business aren't "successful" at it, and plenty who are "successful" but aren't "good" at it.

Look at Steve Jobs. His "business" skills are product-oriented. The things he is good at are not financial problems, or production problems, or operations problems, or any number of other things you may associate with business. He's not a deal-maker CEO, either. This is a completely different motivation and focus than the next business person you may pick.

In the CEO or President role you will see different motivations than in the CFO or COO or CIO roles. Being aware of that is a key to being able to present solutions and strategic options.

IT is just part of the business and the users in IT who understand the business and understand the motivations will succeed beyond those who treat IT as a compartmentalized endeavor. If a CEO has weak technological skills, they will need to be augmented by someone who can provide technology options which can inform, enable or drive strategic initiatives. Even in an enterprise which is not technology driven, some technology (even if it may be considered outdated, like paper and pencil) will be used.

It's important to have IT be a strong partner in the business, through and through. For that to happen, IT people have to listen and learn the business completely.

Because some day you may be running the business.

What's up with Gawker and why it's a big deal?

On Gawker's side:

Amateur security fuckups - plain and simple - these are all basic security failures

You shouldn't be storing encrypted passwords in the first place - even if the data is encrypted, what are you using for key management? - is all the data encrypted with the same key?  Why worry about that - DON'T STORE PASSWORDS.

When you store a hash, store a salted hash to avoid identifying people who have the same password and help to avoid the easy attacks

They didn't notify any of the affected users until days after twitter was already full of information about it

On users' side:
We all know you can't remember a million passwords - you have to use a password vault software and where possible use central authentication like facebook connect or OpenID.

You can't re-use passwords - look at the site - Gawker is a multi-million dollar enterprise and they are security fuckups - how many sites do you think may not be even encrypting passwords

Looks like it might be a good idea not to re-use email addresses either, since the problem cascades with using the email address to identify users fairly uniquely - this is where the whol system breaks down (see my paragraph later about infrastructure)

When you go to a site you rarely use like Gawker or an online forum where you need help with a question about bicycle repair - take the time to make a unique email address and password for the site instead of using a throwaway weak password you use for lots of unimportant sites.  Because even if all those sites are meaningless to you and your important accounts are secured with strong passwords, you still can be majorly inconvenienced when your email address cascades through the systems and the owners decide to desiable your account because a site like Gawker is hacked.  In addition, you'll have a good record of all these little accounts and be able to go back and check them.  If you've taken to using the same email address and password for all these little sites, you'll have trouble finding their details, but with a password vault they will all be there for you.

Other sites:
It's nice that you have a list of affected users' emails - the effort to disable all their accounts and require resets is a good one.  So far I've received notification from facebook, Digsby and LinkedIn.  If you could quantity that cost and bill it back to Gawker, that would be great - because now even users with unique secure passwords on different sites have been inconvenienced by the fact that they didn't want to also have to remember a million email addresses so they re-used their email address for a ton of sites.

Too bad lots of other sites won't run the email list through their systems and notify their users

On the infrastructure side:
More than ever, it's clear that we need sites (especially those which don't have the resources to follow basic security principles) to move to OpenID, facebook connect or whatever, and that users need better tools to manage their digital identities over thousands of sites.

Even with a password vault, we are already managing too many user ids, emails and passwords, all with varying standards for strength.


T-SQL Tuesday: Why are DBA skills necessary? I pick the most important one...

T-SQL Tuesday Hosted by Paul Randal

What are DBA skills in the first place? There are so many skills which are generally useful in IT and particularly in the position that DBAs find themselves in.

I am not a DBA. I have done part of the DBA job before. I have been a developer and also an IT department director. I currently view myself as a systems architect - since I cover the gamut of knowledge necessary from infrastructure to building blocks to programming to database design to human factors and business (re-)engineering. Over the last 17 years, I have done a lot of OLTP database work and several years of OLAP work.

If you look at the ability to understand and organize complex systems, or the ability to understand a particular platform well, or to communicate well with business stakeholders, IT management and development staff there are a lot of skills which a good DBA is expected to have. I've decided to pick a single particular technical skill which I think it is very important for many people in IT to have, but extremely important for DBAs and network administration staff to have. It's needed in a situation where developers and business analysts will come for help, and a need which can never be outsourced or automated out of a job (don't we all look to put ourselves out of a job anyway?). When you're in the hot seat, that single skill is one that I think I value most in a DBA.

It's really a composite skill which combines a variety of traits and skills, but when I tell you the name, I'm sure you will all agree that it is a well-defined skill that you will recognize as such: troubleshooting.

When you are troubleshooting, you're in a particular mindset. Everything is up for debate. Is the hardware even working right? Is the network even connected? Is the cable bad? Did something change? It often starts as a controlled panic.

It takes a combination of knowing the platform itself thoroughly, and knowing not only how it should be behaving, and how it is actually behaving, and knowing how to find out how it is behaving. Being able to pull out network analysis, database and OS tools to monitor queries and the results. Being able to profile, being able to test ad hoc versions of queries and test data. Knowing the expected data profiles. Knowing what to throwaway completely to avoid wasting time and what to keep in the back of your mind as the next likely candidate to try. And never dismissing anything too soon, lest it come back - because the problem is always in the last place you look.

This all happens quickly in some environments. Even if time is not of the essence because the system is down, all the time used is some opportunity cost. There is nothing better as an IT director than knowing that you have a good troubleshooter working the problem, that he/she is going to come back to you with an answer (even if it's "I don't know yet, but we're going to find out"), that they are going to know the problem inside and out and own it and eventually come back with not only a solution, but with a plan to mitigate it in the near term and long term, perhaps including monitoring in some kind of proactive way.

It requires tenacity, a capacity for absorbing the system operations fully and synthesizing the interactions causing the problem. It generally requires experience and exposure in general systems work, as well as an understanding of the particular system being worked on. And in the end, it may require humility and forthrightness if you are troubleshooting your own work. Troubleshooting a developer's work lets the DBA know what's coming, lets them help to guide the developer away from dangerous areas and shows developers that DBAs can be a useful force, not just an impediment or obstacle. But no matter what, being good at troubleshooting is tremendously valuable.

Having good troubleshooters on your team is vital. It's a skill which is vital for DBAs to cultivate and improve and it's a reason you have DBAs in the first place, whether you call them that or not, the people with those DBA skills are necessary. That's why I want someone with DBA skills on the team and out of all those skills, the one I value most highly is troubleshooting.