Jon Baker's Outer Join

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 business analyst in the Use Case, and the data modeler then adds the Data Item to the appropriate entity.  This is an incredibly empowering scenario for the business, as IT is no longer the only one with data definition capability and responsibility.  It also takes a lot of work off the data modelers' plate as now they have less work to do defining the nitty-gritty of each attribute. 

Once a Conceptual Model is complete, it can be pushed down to a Logical Model for further definition.  A complete Logical Model can be pushed further down to a physical model based on a specific DBMS.  All of these models are decoupled, in that changes to one do not affect the other(s).  PD does provide a link and synch tool to push changes up and down the model chain, allowing you to model at any level, while still keeping all the levels in synch. 

PD is an excellent end-to-end, formal modeling solution providing a collaboration repository.  Its strengths are many. The rich feature set allows the modeler to do just about anything you'd ever want to do.  Besides data modeling, the CASE tools provide Object Modeling and code generation in virtually all the major languages, making it a great place to start any project.  The downsides are just as you might expect.  The capabilities of this tool bring much complexity.  Ripping out a simple data model for a new project is not all that easy.  The tool really pushes you down a process based development pattern.  Also, the interface is not all that intuitive. I have been data modeling for many years, and it took me a  significant amount of time to be able to create entities and relationships for 20 entities.  Lastly it struggled much like ERWIN in creating ALTER DDL.  It was quick good at creating new DDL, but struggled when comparing a model to an existing database.

Pros

Complete business proccess and IT modeling tool
Rich Collaboration
Exceptional Metadata Management (Blobs and Definitions and search capability)
Flexibility in configuration
OK SQL 2005 support

Cons
Complexity
User Interface
Comparison and Script Generation

Embarcadero ER/Studio 7.1
I believe ER/Studio is the youngest of these modeling tools, but it certainly can hold its own with the older brothers.  ER/Studio provides rich data modeling capabilities, metadata management, reporting, and automation.  The last point is a feature I really like, the macros.  Users can write their own productivity and standard enforcement scripts to change models in whatever way necessary.  Embarcadero opens up the object model for access to program your own, but they also provide many helpful macros in the box.

Like PD, Embarcadero decouples the physical model from the logical model, providing compare and synch capabilities between models and the target database.  This last point makes ER/Studio stand-out.  It does a great job of creating alter DDL when comparing to an existing database.  Many tools will rename the table, recreate the table, and re-insert the data for even the simplest changes.  ER/Studio does a pretty good job of knowing when you just need an ALTER TABLE ADD COLUMN script.

The user interface and model layout features of ER/Studio are beyond belief.  There are six ways to layout a model. All of them are meanigful and fast, even with large models.  There are some areas of the user interface that take longer to do things than say ERWIN or PD, but in general it is intuitive and easy to use.

Lastly, ER/Studio now comes in a just for SQL Server edition, which is much less expensive than the competitors, which charge for all the databases, even if you never plan to use them.

Pros

Great Model Layout
Automation Macros
Good Comparison tool and DDL Generation
OK Metadata management
Great Helpdesk and feature enhancement support
SQL 2005 support
Price (SQL Server Edition)

Cons

Some tedious areas of the interface
A little buggy (needed to call support a few times)

As you can see, all of these tools can provide an organization with good data management abilities.  One thing I didn't mention in the reviews is that data management is more than implementing a tool.  It takes discipline and process enforcement.  None of these tools will do that for you. That's the people part of this industry.  Hopefully, this review will help others as they look to choose a tool to implement in their SDLC.

Jon

So after much turmoil and headache getting through O'Hare Monday night, I finally made it to Seattle. To my surprise, it's raining. :O) I still love this city though. It's full of culture and there's so much to do.

Today, I sat in on Erik Veerman's SSIS class. I think my ears are bleeding. He filled us with so much good stuff. The best session was actually the last one. Erik dug into the internals of SSIS and taught us some tuning and troubleshooting that I've not seen anywhere else. Definitely worth the price of admission. Most of his examples came from ProjectREAL or the Kimball book, which surprised me, since he helped author the Wrox book (which I won in the give away, woohooo!).

I had the chance to chat briefly with Rick Heiges. He is a busy guy this week. Sounds like another great conference on tap. Over 1700 people are signed up as of right now. They are expecting near 2000 folks. This used to be a small intimate conference. Somebody let the secret out.

Some info from the PASS rumor mill. I had dinner with Jeff Mayer who is on the membership committee. He tells me they are working to make membership more meaningful. I'm looking forward to that. Can't wait to see what the team comes up with.

I'll try to blog as the battery life allows this week. PASS has gone paperless, so the only way to access slides is to print your own or carry a laptop. I've elected for the latter, but with the competition for power today, this might have been a bad idea!

Jon

I continue to be amazed at the "creativity" of developers for ISVs.  It seems each time I assist with an installation of a 3rd party product somebody comes up with a new way to do this wrong!  That may sound politically incorrect, but this is getting ridiculous.  It's not that hard.  I mean, really, SQL Server is a database - not a file server, not a web server, not a conduit to an underlying operating system - a database.  With that said, here are some things I've actually seen during 3rd party product installs, and things you should never do, with a little bit of "why" thrown in for good measure.

1. Using SA as the only possible account for creating the database and objects.  I mean really, aren't we past this.  What if I don't allow mixed mode or SQL Authentication in the environment.  Also, I'm not likely to give up the SA password to the installer sent onsite by a reseller.

2. A close cousin to #1 is the famous "blank" password for SA - required. Yes, I've seen it, and recently!

3. Still in the ballpark with SA is the ubiquitous need to have a new user in the sysadmin server role in order to install and run the application.  Usually 3rd party apps want to create a user, create a database, and populate that database.  That can be solved with db_creator and security_admin roles, though I'm not fond of giving these up either.  The bad news, Microsoft is one of the worst vendors about doing this (see Sharepoint, Commerce Server, CMS, LCS).  I would prefer to see an application that has db_owner rights on the database with no server rights.

4. Requiring a unique sort order for the server installation - Yes I saw this recently.  It was not sufficient to have the database or even specific columns set to a collation, but the entire server had to be, because it did things in TempDB that required the specific collation setting.  This was rather frustrating, since they could have just created the temp tables with the proper collation and it would have been fine.  Instead, we had to create another named instance for this app.

5. Creating folders on the database server during the installation, and using those folders for passing data between the app and the database.

6. Using xp_cmdshell in the install and during the normal operation of the application.  I hope we are all locking this down by now, at least for admins only :).  Apps should not need to access the OS from the database.  If so, how about a custom XP or .NET SP with EXTERNAL CAS?

7. Requiring a specific MDAC version on the database server.  Hopefully, SNAC will fix this, but until then this is still a problem.

8. Creating an obfuscated database structure that essentially recreates the SQL Server system tables within user tables.  I've seen this from 2 different applications, and boy does it make it hard to troubleshoot data related issues for that application.  Especially when the table names are T49, T101, S10, etc.  If you are reading this and recognize these table names, you know who you are.

9. Enforcing Primary Keys and Referential Integrity within the application.  This is a bad practice whether you are an ISV or in house developer.  Data changes outside the app, whether you like it or not.  The only way to be sure it fits within the rules of the data model is to create constraints.  It's not that hard, and, no, it doesn't impact performance.  I know, I know, the database won't be "platform agnostic." 

Also, if there is a need  to replicate the data for reporting, replication requires Primary Keys.  One large CRM vendor who shall remain nameless is known for this practice.  I've actually seen people create PKs, so they can replicate the data for reporting.

10. Last but certainly not least - requiring the SQL Service to run as Local System.  This one baffled me, too, but I saw it!  The application actually didn't work if the account running the service was a domain or local user.  When I called the vendor to see if there were OS permissions I had to grant, they didn't know and said they wouldn't support another configuration. 

I'm sure others have run into some interesting ones.  Post yours vendor install experiences here, so we can share in your misery.  Hopefully, someone will read this and avoid one of these worst practices.

Jon

 

Awhile back I started at a new customer doing some project work and some production support work (sigh). One thing the customer requested was for a DBA to run a script on all production servers and review the output to make sure nothing was wrong, then email a summary to the other DBAs so they also knew nothing was wrong.  The interesting thing was that there was no clear cut set of criteria of what "wrong" was.  This all sounded rather automatable to me, so I set out to save myself and fellow DBAs some time and learn some things I had been meaning to learn, namely Reporting Services. 

In addition to saving time every day,  I also wanted to be able to track some stats that were collected over time, especially things like disk space remaining, DB size, etc.  The current monitoring solution employed by the customer didn't have this feature, so it would be a helpful addition.  I also wanted to nail down what the DBAs were looking for, so there was consistency in the monitoring, and I wanted to make it a push subscription instead of having to physically do something each day. Here's was I did.

First I created a new DBA database on each server to be monitored.  Then I turned pieces of the daily monitoring script the DBAs were using into stored procedures, but I altered them only to collect data that I didn't already have or didn't care about - no dups, no extra error messages, nothing I didn't want to see. BTW, this gave me a lot of cool scripts to use in other engagements, too. Next I scheduled these stored procs to run each day, collecting data to tables in my DBA database.

Next I created a repository for centralized reporting - a monitoring ODS of sorts.  The tables in the repository were identical in structure to the local server repositories.  I then created a DTS package to copy the data from the source server to the repository.  This would run each night sometime after the stored procs were executed on each server.

Finally, I created some reports.  The first reports were detail reports for things like errorlogs, backups, jobs, etc. The really important report was the summary report(see below), which the DBA team subscribes to as a daily email.  It basically provides a dash board for each production server. You can see at a glance where the problems are.  Oh, yeah, I also worked with the DBAs to determine what "wrong" meant, and what were acceptable thresholds for the things to be monitored. Each of the items drills down into the detail reports, so you can see specific data on each server.  So if you see something is "Not OK", you can find out why. (Note there's a lot of conditional formatting that you can't see unfortunately due to some limitations either of the posting tool, or my HTML abilities)

Daily Monitoring Summary
Server Name	Backup Status	Error Log Status	Job Status	Disk Status
Server1	OK	OK	OK	OK
Server2	OK	OK	OK	OK
Server3	OK	OK	OK	OK
Server4	OK	NOT OK	OK	OK
Server5	OK	OK	OK	OK
Server6	OK	OK	OK	OK
Server7	OK	OK	OK	OK
Server8	OK	NOT OK	OK	OK
Server9	OK	OK	OK	OK
Server10	OK	NOT OK	OK	OK
Server11	OK	NOT OK	OK	OK
Server12	OK	OK	OK	OK

I thought this was pretty cool.  It has saved quite a bit of time.  We estimate it saves about 2-3 hours a week.  That's a good bit of savings.

I have lots of ideas of how to improve it including more parameterization and categories to check.  I've also thought about creating an online service where maybe people submit an XML document over SOAP with the details from each server to monitor, and then they can use the reports for their own environment.  Just a thought.

I'm sure I'm not the only one to do something like this. What have you done?

Jon

 

I came across something where I was executing a util from xp_cmdshell (:$), and it behaved differently in Windows 2003 than in Windows 2000.  Thus the need for some code to determine which version of Windows my SQL box sits on.  Here's my shot at it.  Anyone have a better idea?

select case

when substring(@@version, charindex('5.',@@version), 3) > '5.0' THEN '2003'

ELSE '2000'

END