Search results matching tags 'SQL Server' and 'Writing'

Why We Write #1 - An Interview With Thomas LaRock

drsql — Fri, 22 Mar 2013 00:03:00 GMT

I 've been a writer of trade level technical materials for over 13 years now, writing books, articles, blogs, and even tweets for a variety of outlets, almost exclusively about Microsoft SQL Server. While I won't claim to be the best writer in the world, I feel like I have the process of writing down fairly well, yet, for the life of me, there is still the question of "why do I do this?" stuck in the back of my mind that I have yet to appease.

Note that my quest specifically deals with non-verbal communication, because it seems to me that presentations are a completely different sort of "why" altogether.

So I have decided to survey as many of my technical writing colleagues and find out their answer to the "why" question. The only criteria for being included in this set is that you write about a subject like programming, gadgets, computer administration, etc.; and that you don't make your most of your living from writing (in other words, if you stopped writing today, tomorrow you would not be in fear of sleeping in the gutter.)

To get the process started, I have asked Thomas LaRock to be my first survey participant. Tom is a SQL Server MVP, has written a very popular book called DBA Survivor for Apress, frequently tweets as @sqlrockstar, and blogs at www.thomaslarock.com where he maintains a popular ranked list of SQL bloggers (of which I am listed in the tempdb category). He is a member of the executive committee of SQL PASS, and is very active in the SQL community as a speaker. He currently works for Confio as a Technical Evangelist. Tom is also quite well known in our SQL communitiy as a lover of the delightful cured porcine meat known as bacon.

If you want to see Tom in person, he will be doing a pre-conference seminar with Grant Fritchey and Dandy Weyn this year at Tech-Ed North America in early June in New Orleans entitled How to Be a Successful DBA in the Changing World of Cloud and On-Premise Data.

-------------------------------------

1. Every good superhero (or in your case, SQL Rockstar) has an origin story. What got you involved in writing?

Tom: The birth of my daughter. I wanted to record as many details as possible and since I had 10MB of available space for a website as part of my cable package (yeah...10 MEGABYTES BABY!) it was easy enough to get a website up quickly and easily. The writing came easily, too, since I was writing about something so close to my heart, something I remain passionate about to this day.

2. We all have influencers that have advanced our careers as writers. It may be a teacher who told you that you had great potential? Another writer who impressed you that you wanted to be like? Or perhaps on the other end of the spectrum it was a teacher who told you that you were too stupid to write well enough to spell your own name, much less have people one day impressed with your writing? Who were your influences that stand out as essential parts of your journey to the level of writer you have become?

Tom: I never try to be exactly like someone else. If I did then I would always be second best. Instead I've learned to take bits and pieces of different people and shape them into who I am today. The writer I admire most these days is Bill Simmons followed by Gregg Easterbrook. Both are known more for their sports writing but their style of writing is one that I try my best to emulate: it's human. I do not enjoy the dryness of technical writing, I prefer to write from my heart about things that I enjoy. That makes it less of a chore.

3. My writing process is pretty drawn out, often starting on my phone in OneNote, sometimes finishing in 10 minutes, but often taking a year (or years) to finish an idea. Can you describe the process you go through to write, from inception of an idea until it gets put out for consumption?

Tom: I used to start a draft inside of WordPress but lately I have been using EverNote to track my ideas and take notes. From there I just decide to go and get it done. I do my best to follow a very loose format: describe a problem, explain why it's an issue, help readers understand any and all tradeoff (cost, benefits, risks), and a few action items for them to use as a take away. Once I have that framework in my head it doesn't take long to get to a finished product. I think I may spend more time on finding a decent image to use with my post than the actual writing itself.

4. Assume a time machine has been created, and you are allowed to go back in time to speak to a group of potential writers, in which you and I are in attendance. What would you tell "past us", and do you think that your advice would change where you and I are in our careers now?

Tom: Write for yourself first. Feed your own soul. Don't worry about what your readers want. You can't write for others, they will never be happy with what you have done. The only person that needs to be happy with your words is you. When you write and share yourself then your readership will grow with people who are naturally drawn to you, and it makes it easier for you to keep sharing your words with people that want to hear them. And no, this advice wouldn't change. Ever.

5. Finally, beyond the "how" questions, now the big one. There are only 24 hours in a day, and there are no doubt tremendous pulls on your time from family, friends, and pork products, yet, even considering just your blog output, you obviously sit down at a keyboard very often to write. Why?

Tom: Most of the time I just feel that I have words that need to be written. Doing so helps to feed my soul. I'm at a keyboard a lot because my job requires it, and I am able to spend a lot of my day just writing as a way to communicate with others. Sometimes it's an email, sometimes it's a support ticket, other times it's a blog post.

-------------------------------------

I want to thank Tom for being my first participant in my experiment. I find his answer to the “why” question very similar to mine, in that he doesn’t so much offer a tangible reason, but more that he feels compelled to do so. I have to say that the question of how he got started is really quite unexpected, and very interesting, and is going to affect my future questions I ask because more than just the origin story, it will be interesting to see whether people started writing technically first, or for some other reason. I know that before I wrote my first book, I had never written 2 pages of material that wasn’t graded rather harshly by someone with PhD behind their name (or at least one of their low paid minions.)

Unfortunately (or fortunately if you enjoyed this first entry) Tom certainly did not resolve any of my questions to any level of satisfaction so I am going to have to continue to ask more of my technical writer colleagues for their opinion as well.

To that end, my next interviewee will be Mark Vaillancourt, whose website is http://markvsql.com/ and whom has a degree in English and Theatre (so he will know if it should have been whom or who earlier in this probably run on sentence), so that could make for quite an interesting interview. Perhaps he may resolve my curiosity about how one can go from the seemingly non-technical to spending his time working on SQL Server Business Intelligence. I don’t know but I look forward to finding out.

SSIS Design Patterns, the Book

andyleonard — Mon, 06 Aug 2012 16:37:43 GMT

For the past two years, I have had the honor and privilege or authoring SSIS Design Patterns alongside Jessica Moss, Michelle Ufford, Tim Mitchell, and Matt Masson. Publication of the book – like many projects of this scope – has been delayed. The current publication date is 27 Aug 2012 and I have high confidence in this date.

I take responsibility for publication delays and apologize to those who pre-ordered the book. The reasons for the delays are not important. I have built a career as a software developer and architect based on the following maxim:

Deliver quality late, no one remembers.
Deliver junk on time, no one forgets.

The shared goal of everyone working on this project has been to deliver quality. Proofing the manuscripts, I believe we have achieved that goal.

:{>

Chapter 7–Enforced Data Protection

drsql — Tue, 21 Jun 2011 04:36:13 GMT

As the book progresses, I find myself veering from the original stated outline quite a bit, because as I teach about this more (and I am teaching a daylong db design class in August at http://www.sqlsolstice.com/… shameless plug, but it is on topic :) I start to find that a given order works better. Originally I had slated myself to talk more about modeling here for three chapters, then get back to the more implementation topics to finish out the book, but now I am going to keep plugging through the implementation tasks, then finish up with modeling task (which I hope I might end up getting some help with…emails are going out once I talk it over with my editor).

In the last edition, the chapter on data protection was more inclusive, including programmatic data protection, including client code and stored procedures. But, keeping with the basic, implementation type chapters (and trying my best to shorten chapters to more realistic chunks (the free chapter shouldn’t be 1/2 of the book, or so I am told), I will put that off to probably the final chapter.

This chapter was broken up into two main sections, Check Constraints and Triggers. I will demonstrate the following scenarios, and if you see anything missing, please do make suggestions

Check Constraints

Simple value checks – Like when you want to make sure there are no blank string values CHECK (len(value) > 0)
Value reasonableness checks – Like if a value should be a non-negative integer, CHECK (value >= 0)
Checks using different tables – Like setting up a data driven format check

Triggers – Broken down by AFTER and INSTEAD OF Triggers

AFTER

Range checks on multiple rows – Like when you want to make sure that the sum of rows related to (and including) the newly inserted rows is > 0
Maintaining summary values (only as necessary) – Denormalization, pure and simple, but if you are going to do it, triggers are the way to go (you really shouldn’t)
Cascading inserts – Like creating child rows to ensure a 1 to at least 1 row relationship is met, or creating a parent
Child-to-parent cascades – Like deleting parent rows when the last child row is deleted
Maintaining an audit trail – Also something that will come up in security, but implementing an audit trail of actions on a table. Less needed these days with auditing, but
Relationships that span databases and servers – sometimes you just have to implement RI between databases, so it is back to 6.0 style RI

INSTEAD OF

Automatically maintaining values – For example, if you want to implement a bulletproof rowLastUpdatedTime column to know when the row last changed, but don’t trust the client (who does?)
Formatting user input – Like formatting words in all caps, or proper case. Another thing that might be better done outside of SQL Server, but it is very straightforward to implement
Redirecting invalid data to an exception table – For example, eliminating data that is outside of the norm. Possibly done better outside of SQL Server code, but if you really want to build something that takes previous data into consideration, this might be a reasonable way.
Forcing no action to be performed on a table, even by someone who technically has proper rights – Simple do nothing trigger that works because in an instead of trigger you have to replicate the action, so you don’t.

It might seem weird to consider formatting data or redirecting invalid data to another table as data protection, but the point of data protection is to make sure that they data ends up in a reasonable state, and triggers can do some “magical” seeming stuff. Admittedly, triggers are not a fan favorite with many programmers because they do those magical stuff that they cannot directly control, but in many ways that is the point. If the dev forgets to update the last update date, the ETL may not see the row, and oops your data is out of sync.

In any case, I do my best to make it clear that you don’t in fact have to do any of this, but here are the tools in the tool bag.

Normalization and How to Know When You Are Done… The short version…

drsql — Sun, 29 May 2011 20:54:15 GMT

A while back, I was working on a short article about Normalization for a book that never got published (admittedly I wasn’t getting paid for the article, and it wasn’t for charity, so I wasn’t that broken up over it.) The task at hand was to, in 2 pages or less, describe the process of normalization and help you to know when you have finished. In my upcoming book Pro SQL Server 2000 + N (where N > 10) Relational Database Design and Implementation, it takes about 45 pages. So it wasn’t really a realistic task, especially considering I have spent about a full paragraph letting you know how hard the task is going to be. The most important thing that is missing from this short introduction is examples, which I include in the book in truck loads.

There are two distinct ways that Normalization is approached. In a very formal manner, there are a progressive set of “rules” that specify “forms” that you are working to achieve. There is nothing wrong with that definition, but progressing through the forms in a stepwise manner is certainly not how any seasoned data architect is likely to approach the problem of designing data storage. Instead, you design with the principles of normalization in mind, and use the normal forms as a test to your design.

The problem with getting a great database design is compounded with how natural the process seems. The first database that the past uneducated version of me built had 10+ tables, all of obvious ones like customer, orders, etc. set up so the user interface could be produced to satisfy the client. However, tables for address and even order items were left as part of the main tables, making it a beast to work with for queries, and as my employer wanted more and more out of the system, the design became more and more taxed. The basics were there, but the internals were all wrong and the design could have used about 50 or so tables to flesh out the correct solution. Soon after (at my next company, sorry Terry), I gained a real education in the basics of database design, and the little 1000 watt halogen light bulb went off…

That light bulb was there because what had looked like a more complicated in the college database class that no normal person would have created (bet you can’t guess what my grade was in that class!) was really there to help my design fit in with the tools that I was using. Turns out that the people who create relational database engines use the same concepts of normalization to help guide how the engine is created that I needed to for a database to work well. So if the relational engine vendors are using a set of concepts to guide how they create the engine, it turns out to be actually quite helpful if you follow along.

First, lets look at the “formal” rules. The normalization rules are stated in terms of “forms”, starting at First Normal Form, and including several others some of which are numbered, some are named for the creators of the rule. (Note that in the strictest terms, to be in a greater form, you ought to also conform to the lesser form. So you can’t be in third normal form and not give in to the definition of the First). To be honest, it is rare that a data architect will actually refer to the normal forms in conversation specifically unless they are having a nerd argument with a developer that is trying to design an entire customer relationship management system in a single table, but understanding the basics of normalization is essential to understanding why it is needed. What follows is a very quick restatement of the normal forms:

First Normal form/Definition of a Table – Attribute and row “shape”
- All columns must be atomic—one value per column
- All rows of a table must contain the same number of values – no arrays
- Each row should be different from all other rows in the table – unique rows
Boyce-Codd Normal Form – Every candidate key is identified, and all attributes are fully dependent on a key, and all columns must identify a fact about a key and nothing but a key.
- Encompasses:
  - Second Normal Form - All attributes must be a fact about the entire primary key and not a subset of the primary key
  - Third Normal Form - All attributes must be a fact about the primary key and nothing but the primary key
Fourth Normal Form - There must not be more than one multivalued dependency represented in the entity. That is to say that every attribute relates to the key with a cardinality of one. Not a common rule to violate, but it definitely does occur.
Fifth Normal Form - All relationships are broken down to binary relationships when the decomposition is lossless. Very rarely violated in typical designs.

There are other, more theoretical forms that I won’t mention, but they are rare to even encounter the definition. In the reality of the development cycle of life, the stated rules are not hard and fast rules, but merely guiding principles that can be useful to help you avoid certain pitfalls. In practice, we end up with denormalization, (meaning purposely violating a normalization principle for a stated, understood purpose, not ignoring the rules to get done faster) mostly to satisfy some programming or performance need from the consumer of the data (programmers/queriers/etc)

Once you deeply “get” the concepts of normalization, you really will find that you build a database like a well thought out Lego creation, desiring how each piece will fit in to the creation before putting pieces together, because disassembling 1000 Lego bricks to make a small change makes Legos more like work than fun. Some rebuilding based on keeping agile can be needed, but the more you plan ahead, the less data you will have to reshuffle.

In actual practice, the formal definition of the rules aren’t thought of at all, but instead the guiding principles that they encompass are. In my mind, I use the following four concepts in the back of my mind to guide the database I am building, falling back to the more specific rules for the really annoying/complex problem I am trying to solve:

Columns - One column, one value
Table/row uniqueness – Tables have independent meaning, rows are distinct from one another.
Proper relationships between columns – Columns either are a key or describe something about the row identified by the key.
Scrutinize dependencies - Make sure relationships between three values or tables are correct. Reduce all relationships to binary relationships if possible.

The question in the title still has yet to be conquered. “How to know when you are done?” What I left out of the description of Normalization was the granularity you go with. The word “atomic” is a common way to describe a table or column that is normalized enough. Atomic would tend to indicate something that is broken down to its absolute lowest form. But unless you are not a nerd (and would you really be reading this if you weren’t?) we know that there are lots of particles smaller than an atom. When you try to mess with particles smaller than the atom, you get a mushroom cloud that even Timothy Leary would not have approved of.

It is the same way with databases. Tables and columns split to their atomic level have one and only one meaning. Deal with them at a higher level, and you will suffer with lots of substrings, switching attributes that you use to find out what a table means in a situation. But break things down too far, and you will suffer even more. My best example of this is a column that holds a large quantity of text. If you never need to us part of the data using SQL, a single column is perfect (a set of notes that the user uses on a screen is a good example.) You wouldn’t want a paragraph, sentence, and character table to store this information. On the other hand, that same character column is abused when the users start putting coded information (because users WILL find a way to work if your software fails them). Then and you have to search for, you will need to begin working with the less comfortable string manipulation functions in SQL… And just try to index a part of a large text column. Possible? Sometimes. Best way to go? Never.

The key to knowing what is normalization and what is an academic exercise for a nerd is to understand the needs of the users (commonly referred to as requirements, as in “Why don’t we ever have good requirements before we code!?!”). If it is clear that the user is planning on maintaining a list of values and will need to update them programmatically, then it is your job to make each value a row in a table. But if there is no requirement to ever search on a value in that list or programmatically access part of the value, then it might be overkill to do anything other than leave the value alone. It is often best to err on the side of caution, but the ideal relational storage for a document would be minimally at the word/punctuation level. If you are read this far and are convinced that would be the proper solution, then you need to get a complete book or take a class on the subject before you start creating a relational database.

The reasonable answer to when you are done normalization is when users have exactly the right number of places to store the data they need and you can query/use the data without parsing it… Easy enough until the user changes their mind, huh?

On Writing

andyleonard — Tue, 26 Jan 2010 12:00:00 GMT

Introduction

Brent Ozar (Blog - @BrentO) published a series of posts on experinces authoring his first book. If you're interested in writing, Brent is fresh from the battlefield and eloquently conveys a sense of the trials and triumphs of the experience.

Additional Links

Kevin Kline (Blog - @kekline):
The Book Writing Business, Part 1
The Book Writing Business, Part 2

Me:
On Book Authoring (for the first time)
On Book Authoring (for the first time), Part 2
On Book Authoring (for the first time), Part 3
On Book Authoring, Part 4
On Book Authoring (for the first time), Part 5

:{> Andy

Blog (Durnit!), Getting Started

andyleonard — Tue, 15 Dec 2009 12:00:00 GMT

Introduction

I got some cool responses to my post Blog (Durnit!). This includes at least one blog, Adventures In SQL, by David Levy (Blog / Twitter), started as a result! Wow, how cool!

Reading comments and email, I realize I left out one important part, where to...

Get Started

First, you need to decide whether you're going to host your own site or pick a site at which to blog. I recommend starting on one of the free sites - just until you get your sea legs. There's a ton of free sites out there, but three stand out:

Of those three, I've heard more positive things about Wordpress than the others. I'm not an expert on these things, so please take any recommendation from me with a grain of salt.

Caution, Steep Grade Ahead

I know blogging looks easy, I used to think it was easy too. It turns out blogging is work. It's fun work because you get to decide what to blog about, but it's not all fun and games. It's easy to fall into the trap of thinking blogging is easy: we're reading the blogs of brain scientists like Brent Ozar and thinking "Look at all he writes! That can't be that difficult?"

Blogging can be daunting. Don't be fooled (as I was) by looking at the quantity and quality of content on someone else's site. One mark of someone skilled at their craft is they make it look easy.

Some Advice

Again, Brent Ozar offers some great advice about getting started in his post Building Your Blogging Momentum. I can't recommend this post enough. Brent's advice to "schedule your posts to be published at least one week after writing" is a proven way to build your writing stamina.

Conclusion

Get in the game! Start blogging!

:{> Andy

Blog (Durnit!)

andyleonard — Fri, 11 Dec 2009 12:00:00 GMT

Introduction

The past few days I've read several blurbs from folks expressing why they don't blog. The reasons range from "I have nothing interesting to say" to "I don't know enough" to "Everything I want to write about has been blogged already."

Pfffft (to quote @RachelAppel)

Come on. Seriously? If you think, you can blog.

I Have Nothing Interesting To Say

Yes you do. And you'll say it in a way that no one else will. One day - perhaps months or even years from now - someone will encounter some problem you solved and about which you blogged. They'll search for an answer and your post will pop up in the results. They'll click on that link, read your post, and solve their problem. All because you blogged. Every now and then, someone will leave you a comment or send you an email thanking you. That's a cool feeling right there.

Andy's Little Secret: If I solve the same problem twice, I blog about it... for me!

I Don't Know Enough

Yes you do. You solve problems for a living. No, they're not the same problems I solve, nor are they the same problems Bob Ward or Jimmy May solves. But they are problems and you solved them. You know enough to blog.

Maybe you solved them with help from another post. That's great! When you blog about how you solved the problem, link to that post. Then describe how you used that technique to solve your problem, and include anything you learned along the way.

I can hear you asking "But why Andy?" I'm glad you asked! Because everyone writes in their own style (you should! See Steve Jones' article on plagiarism), you will use different words and phrases than others. The way you describe the issue may be the best match for a future search.

Andy's Other Little Secret: I learn more from mistakes or false assumptions than anything else.

Everything I Want To Write About Has Been Blogged Already

No it hasn't. Technologists the world over suffer from a terrible bias when it comes to evaluating their own knowledge. In a nutshell, we think if we figured it out, anyone can. We value own knowledge at 0. And we consequently over-value everything anyone else knows - especially if we know little or nothing about the same topic.

As stated earlier: Even if it has been blogged before, write about your experience solving the problem.

Andy's Last Little Secret: I get my best blogging ideas from forums.

The Rules of Blogging

Rule #1: There are no rules.

Rule #2: Please see Rule #1.

Brent Ozar has an outstanding post called Building Your Blogging Momentum. It's a classic. Done reading it yet? Wasn't that an awesome post? I thought so too! The next post in the series is good too.

Conclusion

If you think, blog.

:{> Andy