Jon Baker's Outer Join

Well it's been forever since I last posted to this blog, mostly because I'm buried.  In March I took a new position as the Sr. Information Architect at a captive automotive finance company. Those of you in the Columbus, Ohio area likely know which one, but I'm contractually obligated not to say which luxury car maker I now work for on forums such as this as so not to imply an endorsement of any kind :)  I'm very excited about this new role, but sadly it has not afforded me much time to keep up with the SQL Server community. 

With a release of SQL Server 2008 emminent and we just happen to be TAP customers :), I'm planning to put some information here based on what we are working on.  I'll have to dance around the NDA with Big Brother (Microsoft), and hopefully, there will be good information for the community to salivate over until the release. 

I'm curious to hear what others are doing with SQL 2008.  Any body download the CTP?  New feature thoughts? 

A friend of mine, Rick Playko, passed this to me the other day.  It's a bit old, but still a good reminder.

http://www-128.ibm.com/developerworks/rational/library/4032.html

Jon

http://thedailywtf.com/Articles/Chocolate_Covered_SQL.aspx

Hit the link and check out the ingredients.  Sounds like someone is begging for a SQL injection to me...:)

Jon

One of my clients is looking for a full-time Sr. SQL Server Architect here in Columbus, Ohio area.  Any one interested in moving to balmy Columbus is also welcome!  Here are some qualifications they are looking for.

Minimum Requirements:

Bachelors Degree (or consistent progress towards a degree)

2-3 years BI / DW architecture, multi-dimensional data modeling, and ETL design / development experience

2-3 years project management related to iterative / spiral design methodologies

3-5 years database administration and production support

5-7 years relational data modeling and Transact - SQL development / tuning

7-10 years database or system design experience

 

Preferences:

1-2 years server configuration / administration, C# or .NET experience

2-3 years financial services industry experience

2-3 years general application or web development experience

Interested, send me a resume?

Jon

In the first Star Trek Next Gen movie, there was a strange ribbon floating through space which created a personal alternate reality that essentially was a place where all your dreams come true.  This ribbon was affectionately known as The Nexus.  Being inside it was like being on drugs!  People went through withdrawl and did everthing in their power to get back to it if they ever left, or in the movie, were accidentally ripped out.  The crazed inter-spacial terrorist was even willing to blow up an entire planet to get back there. 

At PASS 2006 in Seattle, Ken Henderson and Bart Duncan presented a Skunk-works PSS tool that would automatically analyze the output of SQLDiag. I was amazed, enamored, and even giddy like a..., well, let's just say I was eager to try it out.  So eager, I blogged here about it.  For an independent consultant often called in to figure out performance problems, this could be the alternate reality I've been looking for.  All my hopes and dreams could be rolled up in here.  SQL Nexus purports to have built-in Reporting Services reports for blocking, wait-types, long running queries, how much faster SQL is than Oracle (that last one might be in a future release!)

On my first try to get it working, SQL Nexus itself was the only thing blowing up. I was able to set it up and get the analysis database created (a manual process). However trying to import data from SQLDiag appeared to work, but imported no rows. The reports look like they will be nice, but none of them work if the data has 0 rows.  In fact, they error out.  The parameterization of the reports seems to have a bug, too, as it throws an error for a bad parent reference something or other.

I can only hope that PSS will fund this effort and provide a more ready-for-prime tool. The promise of it is incredible, to the point of addiction. However, it is not quite ready for simple, consumable use.  As of now, it stills seems to be floating out somewhere in space.

If anyone involved with SQL Nexus can comment or provide details, I'd be very happy to hear about it.

Jon

I recently assisted in a review of data modeling tools for a customer.  I won't mention which one we chose, but I would like to give a general review of the tools.  The 3 major tools on the market for data modeling are CA Allfusion Data Modeler, Sybase PowerDesigner, and Embarcadero ER/Studio, and we looked at these 3 competitively.  Each has their own pros and cons. Some provide better SQL Server 2005 support than others.  Some are just plain easier to use than others.  I won't go into a diatribe about data modeling, but if you are looking for a new tool, or if you are looking to begin data modeling for your development projects, you might want to read on to see which tool might be a good fit for you.

CA AllFusion Data Modeler R7

This is the tool formerly known as ERWIN. If you've been in data management for very long, you've probably seen this tool at least once before, most likely as shelf-ware along with many other data management products.  That's not to say ERWIN isn't a fine tool, just that it is underutilized.  ERWIN has gone through a beleaguered history bouncing from LogicWorks to Platinum and then being consumed by Computer Associates.  It was really the first of the PC-based graphical data modeling CASE tools.  ERWIN, or I should say AllFusion Data Modeler as CA now calls it, is in it's 5th major version, but it's being called R7 (mostly I think to keep up with the Joneses - see versions of the other products being reviewed).  The latest version provides additional support for DBMS platforms other than SQL Server and it has little (if any) support for SQL Server 2005 new features. ERWIN's strengths continue to lie in green-field development.  It is very fast at creating a new logical data model, converting that to a physical data model, and generating the schema to create the database objects.  ERWIN's weaknesses continue to lie in metadata management, reporting, graphical layout of the model, and creating ALTER TABLE schema when comparing to an existing database.  The latter is the reason it is difficult to use for anything other than green-field development.  Both of the other tools provide much better means for developing enterprise databases for the long-haul.

Pros

Easy to create new structures
Long History
Full-Featured logical modeler
New Schema DDL Generation

Cons

Metadata Management
Poor Support from helpdesk or website
Graphical Layout - one method, not easy to read
CASE tool to generate ALTER DDL
Lack of SQL 2005 support
Price - it was the most expensive of the 3 solutions

Sybase PowerDesigner 12

Sybase has put a lot of effort into designing a comprehensive modeling suite. Their enterprise edition modeling tools allow for collaborative modeling through all phases of the SDLC.  The ideal scenario for PowerDesigner is to start by capturing Use Cases and modeling the results in PD.  The Use Case Model then pushes down to a number of sub-models, including a Conceptual Data Model.  Data Items can be defined by the