SQLSaturday 41 Status Update

I know many people have been worried about me because of the recent personal issues that Iā€™ve been dealing with, but things have finally started to stabilize.Ā  I know Iā€™ve promised that before, so no promises to return to blogging or getting more involved in the community, but Iā€™m starting to climb out of the pit (and hey, I have light bulbs)!

Anyway, despite me, SQLSaturday 41 on April 24, 2010 is plugging along, thanks to a group of dedicated volunteers that have really pushed me to keep this on track.Ā  Thankfully, Iā€™ve been able to give them the information they need, and theyā€™re quite capable of making this happen.Ā  Weā€™re a little more than a month out, and weā€™re almost full with our speakerā€™s list, and are sitting at nearly 60% registration.Ā  Seats ARE filling up, so if you havenā€™t registered, nowā€™s the time to do so.

Hereā€™s a short list of topics so far (in no particular order):

A Lap Around SQL Server 2008 Master Data Services
Whitney Weaver
Beginner

Advanced Parameters in SQL Server Reporting Servic
Mike Davis
Intermediate

Can you control your reports?
Ryan Duclos
Intermediate

Common Table Expressions
Ryan Duclos
Beginner

Data Warehouse Assessments – What,Why, and How
Noah Subrin
Beginner

Database Design Fundamentals
Louis Davidson
Intermediate

Database Design Patterns
Louis Davidson
Intermediate

De-mystifying Execution Plan Analysis
Dave Turpin
Intermediate

Dynamically Configuring Packages
Mike Davis
Intermediate

Full Text Searching – A Guide for DBAs & Devs
Robert Cain
Beginner

Introduction to Data Warehousing / BI
Robert Cain
Beginner

Introduction to Performance Tuning
Mike Femenella
Beginner

Introduction to Performance Tuning
Mike Femenella
Beginner

Introduction to Transactional Replication
Troy Gallant
Beginner

Loading Data In Real Time
Mike Femenella
Intermediate

Off and Running with PowerPivot for Excel 2010
Robert Cain
Beginner

PowerShell for the Data Professional
Aaron Nelson
Intermediate

RESTful Data
Chris Eargle
Beginner

Slowly Changing Dimensions–Done Well.
Julie Smith
Beginner

Solving Real World Problems With DMVs
Whitney Weaver
Intermediate

SQL Server 2008 R2 Overview – Session 1
David Rodriguez
Beginner

SQL Server 2008 R2- BI Drill Down Session 2
David Rodriguez
Beginner

SQL Server 2008 R2- DBA Drill Down Session 3
David Rodriguez
Beginner

SS2008 Data Mining with Excel 2010 and PowerPivot
Mark Tabladillo
Intermediate

Survey of Windows Azure Platform Storage Options
Glen Gordon
Intermediate

The Art and Science of Data Modeling
Audrey Hammonds
Beginner

Tuna Helper for SQL Server DBA’s
Janis Griffin
Intermediate

Using Event Handlers in SSIS for Auditing and Noti
Mike Davis
Intermediate

Virtualize This!
Aaron Nelson
Beginner

Wait-Time Based SQL Server Performance Management
Janis Griffin
Intermediate

When GEO meets SQL: Hotwiring Data to Locations
Michael Clifford
Beginner

March 15, 2010   Posted in: Conferences, SQLServerPedia Syndication  No Comments

#TSQL2sDay 003: Maslow and relational design

Rob Farley is hosting the third installment of TSQL Tuesday, and itā€™s a fun one: relationships (in honor of Valentineā€™s Day).   While Iā€™m not currently in much of a mood to opine on the virtues of love and databases, I did think I wanted to post something a bit more esoteric this time.  Not many of you may know that I donā€™t have a formal background in Information Technology (some of my more sarcastic friends just held their tongues at that one); I actually have a Master of Arts in Communication, and a Masterā€™s of Education in Instructional Technology.  I tripped into IT when I failed my comprehensive exams for the doctoral program in Speech Communication at the University of Georgia.  Awful time, but ultimately one of the best things to ever happen to me.

Anyway, why is this relevant?  Because the goal of this post is to attempt to extend one of the more famous models of social psychology and communication to database design; bear with me (Iā€™m assuming that many of you either have no background in social psych or slept through it), but Iā€™m hoping that this extension to the metaphor will benefit you in terms of your application design.

Maslow: the crash course.

The following is a BRIEF introduction to the theory; if you want more details, Wikipedia is your friend. In a nutshell, Abraham Maslow proposed that humans, as a social animal, were driven to fulfill certain basic needs in a quest for self-actualization or enlightenment.  He proposed a pyramidic model of five sets (or stages) of these needs, with the four lowest ones being required to achieve before attempting the fifth; few people ever attain the fifth level, but the quest to reach that is part of our collective experience.  Iā€™ve defined the five stages below:

maslows_hierarchy_of_needssvg Physiological:

The basic requirements for human existence; food, water, etc.

Safety:

This often translates into security, but itā€™s different than the term we use in information technology careers; safety is the ability to acquire and maintain goods for ongoing existence.  The Physiological elements are immediate needs; Safety elements are the ability to fulfill those immediate needs at a future date.

Social:

Where do we belong?  How do we interact with others who need us (and we need)?  What is our role, and how does that affect our definition of the self?

Esteem:

Esteem stems from the social need; once our relationship with others has been established, we can truly begin to define ourselves and the virtue of our importance in the world.

Self-Actualization:

Self-actualization is the ultimate fulfillment of oneā€™s potential; to be what one is, without need for constant reinforcement from other beings, yet able to exist in harmony with purpose.  Few people have ever attained this stage, but as stated before, the quest to reach the top of the pyramid drives human development.

So what does this mean to the database designer?

Why is all of this important?  This is not a perfect analogy, but if we extend Maslowā€™s model to the area of database design, some interesting questions arise (particularly in the third and fourth stages, which is why I felt like this point would be relevant to the TSQL Tuesday challenge of relationships).  Letā€™s take each stage, and step through them again.

Physiological:

While applications donā€™t have physiological needs, they DO require certain basic elements for long term survival.  Questions to consider at this stage are things like: How much space will I need?  What are the server requirements?  Can my database live in cloud or a mobile device?   What sort of I/O concerns do I have?

Safety:

Recall that safety is NOT security (in terms of who has access to the data), but it is security in terms of long-term survival of the application.  Is the database youā€™re designing intended for a long-term project, or is it ā€œthrow-awayā€ code?  Have you designed it in such a way so that itā€™s easy to replace without impacting the dependent application?

Social:

Speaking of dependent applications (and herein lies the relationship aspect of this post), is your database application designed so that it is loosely related and decoupled from the application?  Does the database fulfill the needed role within the relationship (data storage), without treading too far into business logic?  Can the database handle multiple relationships with various applications (UI/reporting/business services).

Esteem:

Closely related to the social nature of the database within the application stack is the need for self-esteem within the database; can the database meet the the needs of the dependent applications WHILE retaining enough information to establish new relationships?  A classic example of this is the lookup table; a database with low self-esteem will only store the enumerated values provided to it by some other application. 

Without the enabling application, the database lacks sufficient internal definition to validate meaning; in practical terms, this means that the database is not decoupled from the application enough to enable the development of alternate accessing applications.  For example, my day job is to reverse engineer vendor databases; few things in the world are more disturbing than a table full of numbers without any sort of category associated with that number.  The application designer decided to store that enumeration in the application; security through obfuscation IS a method of securing your database, but not a very effective one.

A high-self esteem database will store all of the appropriate lookup values (complete with constraints) in order to provide complete validity within the structure.  The database can then be reused by several different applications, without requiring a complete set of business rules to determine those relationships.    The data layer is definitional; the business layer should be procedural.

hal[1] Self-Actualization:

I have to admit that discussing self-actualization in regards to application design makes me think of HAL.  ā€œIā€™m sorry, Daveā€¦.ā€

To try and stay on track with this metaphor, self-actualization is the basic premise of BI; when your database can start providing you knowledge instead of just data, it has attained the highest level of potential.  Few apps make it that far without requiring substantial redesign, but the ones that do are invaluable to the enterprise they support.

So where are we?

Dunno.  I hope this little exercise made your brain hurt just a bit, and opened up a new metaphor for understanding database design issues within the application stack.   If you have more questions than answers, thatā€™s a good place to be.

February 9, 2010   Posted in: SQLServerPedia Syndication, TSQL2sDay  One Comment

SQL Saturday 41 is official!

Itā€™s live; weā€™re limited to 250 seats, so register now for a great day of training in Atlanta on April 24.  Weā€™re also looking for speakers and sponsors, so please feel free to spread the word.  Iā€™ll continue working on the stub in order to finish the site out, so check back often.

This will be a new challenge for me, since weā€™re planning on running this by committee (which has both benefits and challenges); Iā€™ll keep you posted as to how thatā€™s working out.

Stay tuned!

February 3, 2010   Posted in: Conferences, SQLServerPedia Syndication  3 Comments

Shhhh! SQLSaturday Atlanta 2010 request has been submitted.

Weā€™re looking at April 24 at the Microsoft facility in Alpharetta, GA.  I just submitted the request on the website tonight, so it probably wonā€™t be official for a few days, but I a) needed something to blog about tonight, and b) wanted to get the word out to start some buzz.

Like last year, there will be a waiting list; weā€™re limited on space, and it will probably book quickly, so keep an eye out on this website for the official announcement.  Weā€™re hoping to have several tracks again, as well as a mixture of experienced speakers and newcomers.

Watch and wait :)

January 28, 2010   Posted in: Conferences, SQLServerPedia Syndication  No Comments

We now resume our regularly scheduled programming…

OK, so I havenā€™t exactly lived up to my promise to keep blogging on a regular basis despite my personal issues.  Sorry.  Iā€™ve been wasting a lot of time lately, just moping around the house.  Itā€™s funny, when youā€™re depressed, you have all this time on your hands, all this nervous energy, and yet, you donā€™t get anything done.  I havenā€™t even changed light bulbs.  I just sat there in the dark, and watched a lot of tv.  Iā€™ve also been reconnecting with friends on Facebook; even though I had previously canceled my account, I realized that I had recently been in touch with a lot of friends, and theyā€™re a support system.

Today, I realized that I was sitting in a dark house, and I had a ladder and a light bulb.  I decided that I was going to do something productive today, and I changed the damn light bulb.  I also realized that I could write a blog post, and that I needed to commit to doing something productive every day, or I was going to slide slowly into the abyss.

So, here we are, you and me, and this blog post that isnā€™t going anywhere.  Think of it as a public commitment; I vow to blog at least once every week.  I need to reconnect with people, and this blog had been a great vehicle for that in the past, and it will be one for me again.  So, if youā€™ve hung around waiting on something to happenā€¦ itā€™s happening.

January 26, 2010   Posted in: Blogging is FUN!, The Social Web  6 Comments

#TSQL2sDay 001: Date/Time Issues and Data Modeling

This is my first post for T-SQL Tuesday (thanks, Adam, for the idea!); Iā€™m hoping this will be a continuing block party, and hopefully connect a lot of us SQL bloggers together.  Thereā€™s a lot of great content out there, and sometimes itā€™s just tough to find.

Just a few notes before I get started on this; for this particular post, Iā€™m pulling from my experiences as a data architect for the last few years.  I spend a lot of time reverse-engineering other databases in order to centralize and analyze data from a variety of sources.  Most of my efforts pre-date SQL 2008, so I donā€™t have a lot of experience with the newer datetime data types introduced in that version; however, Iā€™ll point you in that direction if I think it might address an issue that I raise.  Also, thereā€™s no particular order to my list of notes; think of it as a random collection of date-time issues.  Some of them may be specific to T-SQL and SQL Server, and others may just be procedural notes about data integration (and thus may be applicable to other platforms).

Ready?  Letā€™s get started.

1.  If you are a database professional that works with SQL Server, strike the word ā€œtimestampā€ from your vocabulary.

Way back when SQL Server was in its infancy, some developer decided that timestamp would be a good name for a binary column that changes whenever the row was updated.  I donā€™t know the origins of the timestamp datatype; all I know is that it has nothing to do with date or time.  So why am I including it in a post about Date and Times?  Because for most speakers of the English Language, a timestamp is a marker that indicates when an event happens.  Many junior SQL Server developers and even senior developers on other platforms will often be confused by the timestamp datatype, and wonder whatā€™s going on with that column that they just added.

My recommendation?  Avoid using the word timestamp in design discussions.   When someone mentions ā€œwe need to add a timestamp to that rowā€, make sure that you understand what they mean.  Usually they want you to add a column to capture what datetime the event happened, NOT an indicator of when the row will change.  Note that Iā€™m suggesting that you avoid the WORD timestamp; obviously, if a database requires a timestamp column, use it.

2.  Does anybody really know what time it is?

One of the benefits of a distributed architecture is that your database server doesnā€™t have to live on the same box as your application server; in fact, your database server may not be in the same timezone as your application server.  And, if youā€™re using a webserver as an application host, your user may not be in the same timezone as either your application or your database.

I mention this because as a database designer, you may need to capture multiple points-in-time (or you may not); you cannot assume that the application is consistently providing the same time reference for all users.  If youā€™re using a truly distributed design, you may have one host in New York and one in Los Angeles, and your users may connect to either one to enter in data; what date do you use?

For this reason, I usually advocate two things: 1) always include a database-centric referential date of event (in other words, include a column with a default value of the database time for important data), and 2) always use UTC for saving time.  That second statementā€™s important enough to warrant a note of its own.

3.  When in doubt, use UTC.

Time is relative to geography; the application you build today in New York may eventually have clients in New Delhi.  If your processes could ever have the potential of crossing multiple time zones (for example, you want to track shipping from Atlanta to Oregon), youā€™d get the most benefit out of storing your data as UTC in order to understand the relationship between event A (which occurred in EST) and event B (in PST).  Itā€™s a lot easier to change the display of events to a local time from UTC than it is to convert from local time to UTC, do the math, and then convert back.

One problem with recording data with UTC times that Iā€™ve encountered is that is you have centralized data, but distributed users that want to see their data in local time, building OLAP cubes is a pain.  If youā€™re wanting to analyze data for Atlanta and data for Oregon, Atlantaā€™s day begins 3 hours earlier than Oregonā€™s.  I have not found an easy solution to this yet, but am starting to investigate the new datetimeoffset data type in SQL 2008.

4.  Use a standard notation for calculating differences between dates.

Obviously the new date and time datatypes in SQL 2008 address a desire of many in the development community for a method of storing ONLY the date or time of an event, but if youā€™re using an application that still stores the datetime of an event and you need to round the data to the nearest day, hour, week, whatever unit of time, then use  a standard method for doing so.

(I am assuming that you are using date-based arithmetic rather than converting to a varchar and back).

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

is the preferred method; you can read why at my previous post.

5.  Use a standard notation for referring to dates.

Americans are weird about dates; weā€™re one of the few countries that puts the middle container before the smallest container followed by the largest container.  Clueless?  When people ask you whatā€™s your birthdate, how do you answer?  Most people rattle off Month-Day-Year.   An international notation would be Year, Month, Day.

Again, if youā€™re designing databases and queries that might cross international borders, youā€™ll save yourself some debugging time in the future if you can get into the habit of using an international standard notation for referring to datetimes.   A query that asks for data WHERE DateOfEvent > ā€˜11/1/2009ā€™ may work fine in the US (returning data newer than November 1, 2009), but may blow up in the UK (returning data greater than January 11, 2009).

How do you know if youā€™re a DBA thatā€™s been bitten by this?  You start dating everything (including personal checks) using a notation like ā€˜2009-11-01ā€™.  I  get odd looks all the time (probably not just because of the way I date my checks).

6.  Finally, get used to the fact that thereā€™s lots of methods to store datetimes.

If youā€™re doing any sort of database integration, youā€™re probably familiar with many of the same issues I laid out.  There are others that I havenā€™t covered, but the important thing I want to focus on is that just because I think these are good habits to get into, others have different opinions.  Some db proā€™s prefer to use epoch time rather than a standard datetime; I not only have to be familiar with my own methods of doing something (and prepared to defend those methods), but I also have to be willing to understand other methodologies so that I can do the conversion.

December 8, 2009   Posted in: TSQL2sDay  One Comment

OT: Endings and new beginnings

This is a personal post; I need to write it to explain my absence from blogging, and hopefully announce my return with new vigor.

My wife and I have decided to separate after 14 years of marriage; although itā€™s a painful process, Iā€™m hoping that weā€™ll both survive this, and come out of it as better people.  I wish I could say that it was completely mutual, but to be honest, Iā€™m no longer able to understand her motives.  All I know is that I needed something to change in order for me to be healthy and happy, and this seems to me to be the only option left (and please, if you have an opinion about that, keep it to yourself.  I appreciate the concern, but I donā€™t need advice at this point).

Anyway, Iā€™ve been busy setting up a new home and working out visitation with my kids (who I think are doing remarkably well under the circumstances).  Itā€™s been tough making the adjustment, but I think Iā€™m finally at a place where I can start writing again.  If youā€™re still out there, dear readers, I hope that I can soon start contributing to the community at full blast over the next few days.  Itā€™ll give me something to do :)

Thanks for your patience, your thoughts, and your prayers.  Life happens when we least expect it.

November 30, 2009   Posted in: Blogging is FUN!, The Social Web  One Comment

PASS Summit Day 2: the brief wrapup #sqlpass

Iā€™m writing this post quickly, because I have a 6:30 SQLSaturday alumni meeting at a local donut stop.  Please donā€™t let the brevity of the post throw you off; lots of goals accomplished yesterday.  Unfortunately, going to sessions was not one of them :)

  • I spent a lot of time networking, and discussing Agile techniques for database developers with a lot of different people, as well as looking at some of the latest and greatest tools for SQL Server.
  • Speaking of vendors, I shipped home two huge boxes of free stuff, most of it from Quest, Idera, and Red Gate.  My shipping costs were $80, so you can imagine the stuff I had.
  • Finally, and Iā€™ll probably return to this in a later post, I met with the Board of Directors of PASS in an intimate session on governance.  It was fascinating, and Iā€™m surprised that more chapter leaders were not there, because we discussed A LOT of issues related to the local chapters, and how PASS runs itself.  I donā€™t think that any particular goals were met, but I do believe that the following is true:
    • Change is coming for the local chapters; the PASS board is going to try to do more to connect with the local chapter, but will want more from the local chapter as well.  Think of it as having more concrete discussions about how the local community uses SQL Server, and what niche can PASS fill.
    • Steve Jones asked a series of great questions, including: ā€œWhat have you done for me?ā€  He wanted the Board to be able to be more transparent, and expose their accomplishments beyond their individual audiences (for example, Greg Lowā€™s work with the chapters should be available to those who are not chapter leaders).
    • More to come.
  • The day finally wrapped with an afterparty at a local arcade, hosted by Microsoft.  Lots of fun, but I was exhausted.

Gotta run; day 3 will be a blast.

November 5, 2009   Posted in: Conferences, SQLServerPedia Syndication  No Comments

PASS Summit Day 1.5

So this is the first official day of Summit, but since I got here in the middle of the day yesterday, Iā€™m cramming a brief post in combining yesterday and today.  I could sum up the experience in one word:

Awesome.

I have met SO many people face to face that I normally follow on twitter and other social tools, and itā€™s been amazing to sit and chat with other SQL geeks.  I ended up buying the DVD set today because I had too many sessions to attend, and too many people to chat with.  Itā€™s truly inspiring to hang out with a bunch of intelligent people who are really passionate about what they do.

Highlights:

  • Pinal Dave stole my pen.  Well, he actually borrowed it to fill out his sqlbingo card, and then forgot it.  Itā€™s OK, because I know if I make him feel guilty, heā€™ll fly back to the States from India to bring it to me.  And I could make him speak at our newsgroup.
  • SQLBingo has not taken off as well as I had hoped, but itā€™s going OK.  Theyā€™re drawing the first two winners tomorrow morning, and I hope to pitch it harder tomorrow.  I forget that just because Iā€™m a social person, not everyone else is.
  • I learned some new stuff at Michelle Uffordā€™s presentation on VLDBā€™s.  Need to check things when I get back in the office to see if theyā€™ll help some performance issues.
  • Met Patrick Leblanc, Tim Mitchell, Lori Edwards, Jeff Rush, etc., etc.,etcā€¦
  • Met Remus Rusanu today after Brent Ozarā€™s session, and was told by him that he liked some of my responses on StackOverflow.  That really made my day, because he gives some great advice on that website.
  • Kevin Kline gave me AND the user group autographed copies of his latest book.
  • MidnightDBA gave me free stuff.

LetDowns:

  • I didnā€™t win any blogging awards.  More incentive to keep working at it.
  • I realized I should have submitted something to present.  Paul Waters and I made a pact to come up with 3 submission ideas for next year.

OK, Iā€™m officially out of steam.  More to do tomorrow.

November 4, 2009   Posted in: Conferences, SQLServerPedia Syndication, The Social Web  One Comment

Packing my bag for PASS Summit 2009

Flying out on Monday, and Iā€™m so excited.  Hereā€™s the stuff Iā€™m throwing in my bag (as if any of you care).

  1. My Archos 5 IMT ā€“ 6+ hours and an extended battery.  Gotta load it up with all of the TV shows Iā€™ve missed over the last few months on my SageTV rig.  Since I canā€™t find my boosteroo audio amp, Iā€™ll need to go see if I can get a good set of headphones for the plane.
  2. Pad and pens.
  3. My laptop computer, complete with the Nokia softphone software.  Iā€™ll still have to touch base with the office occasionally, and this way I can have portable VOIP.
  4. Iā€™m writing a review for The Art of Unit Testing for the AtlantaMDF Reviews site.  Need to pack the book for the plane.
  5. Airborne Vitamin C.  I know itā€™s not likely to prevent germs, but boosting my vitamin C intake before I travel is almost a superstitious rite for me.
  6. My #sqlbingo cards and tag.
  7. My PASS Summit itinerary;  I was going to load it into Outlook, but I remember that the last time I did that, everything got screwed up because of the time change. 
  8. My phone, and an extra battery (for all of the twittering I plan on doing).
  9. Business cards.

And I still gotta pack clothing and essentials.  Iā€™m hoping I can do it all with the carryon+laptop limitation, particularly since I have a layover on my way back.

October 31, 2009   Posted in: PASS, SQLServerPedia Syndication  No Comments