Jon Baker's Outer Join

What ever happened to that guy...

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?

Tao of a Software Architect

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

Chocolate Covered SQL

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

Need Sr. SQL Server Architect in Columbus, Ohio

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

SQL Nexus - Not Quite!

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

Data Modeling Tools Review

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

SQL Nexus - MUST HAVE!

A little advertised session at PASS Friday was the real hidden gem of the conference. Ken Henderson and Bart Duncan presented a new tool released today by PSS to analyze SQLDIAG captures. For those not familiar with SQLDiag, it captures traces, blocking, wait types ,etc. SQL Nexus allows you to import all this data into a database. It then provides Reporting Services reports on top of the tables to analyze the data out of SQLDiag. These reports are graphical, parameterized, and very meaningful. Think READ80Trace on steroids! In addition to SQLDiag stuff, it can report on CPU stats and other performance data coming out of DMVs inside SQL Server.

I am completely blown away by this tool. It is a must have for any DBA. You gotta download it right now.

PASS Thursday - DBAs Baby!

Steve Ballmer was the keynote today. SteveB talked business and the future of Microsoft. There was a lot of info about how Microsoft is working to enable the information worker and business people to be more productive. He made the comparison to the days before PCs in the workplace, when cut and paste involved scissors and glue! This all seemed a bit ethereal, until the demo. They showed Vista and Office 2007 integration with Sharepoint, SSAS, and SSRS. This appeared seemless and powerful. Let’s hope it’s really that cool, because it would change how we work, today. The final question for Steve and Paul Flessner’s Q&A was if Steve was going to do a DBA dance (see PDC 2006 – “Developers, Developers, Developers…). Steve turned a bright shade of red, then gave his best Arnold Schwarzenegger impression saying “DBAs Baby!”

I saw a presentation on SQL Server for the SOA environment. Marin Mamic is obviously a developer and not a DBA, though his session was listed in the DBA track. He featured HTTP endpoints for exposing SPs as service methods. I like this to some degree, in that it doesn’t require IIS anymore; however, the hard-core DBA in me still has some fears. Marin also displayed Service Broker for some transactions. It was not a deep dive into any of the technologies, but just showing that it is possible. Interesting stuff.

During lunch, I just happened to sit down at a table with Joe Celko. What an incredible guy! I love SQL For Smarties, and it was great to talk with one of the fathers of our industry. I tried not to ask questions, but just listen to what things interest him and how he attacks problems. He was conversing with a developer from MSFT who had questions about a programming problem. I thoroughly enjoyed just listening.

Gert Drapers presented Schema management in DataDude (VSTS Database Professional Edition). This is an interesting new tool for compare and schema management, making source code the definitive source. I would like to see a data modeling tool on top of this, but Gert was adamant that he will not develop it. CA apparently is taking that up as an add on for ERWIN. Hopefully other software vendors will jump in to add to DataDude modeling tools (remember, I’m not an ERWIN fan!).

Dave Fackler presentations of Attribute Hiearchies was great. I met Dave at the Detroit Launch event last year. He does a great job, especially making relevant demos. I’m really catching the bug for SSAS. I’ve not spent a lot of real work time on AS, but I’m itching!

Ted Malone’s TDD for SQL developers really appealed to me. I like the ideas behind agile development. I also like that Microsoft is investing in it with DataDude.

For dinner, Jeff Mayer and I went to Benihana. While we were talking geek stuff, the guy next to us made a couple of interesting faces. I asked him if he was a technical person. He said no, but he works for Microsoft in the areas of business products (Office, Great Plains, etc.) He asked about the demo in Ballmer's presentation, which of course we told him was great. I mentioned one of my customers was looking at GP to replace a home grown accounting system. He was interested, since the customer has a famous brand (can't share, sorry). I asked him for a card, and Jeff Raikes, President of Microsoft Business Products kindly did! How cool is that!!!

I think my brain is getting full here at the conference. Not sure how much more I can stand, especially since I’m really distracted due to the OSU game this weekend. GO BUCKS!!!!

PASS Wednesday

The day started with Paul Flessner’s Keynote, where he introduced his successor, Ted Kummert. There was also a demo of DB mirroring that bombed miserably. Still not sure I like that feature in its current state.

The sessions were great today. I saw Joy Mundy give a session on SSIS ETL for Fact Tables and source extraction. This was awesome. It dug deeper even than her book.

I also Allen White present on SMO for automation of daily administration. This was interesting to me as I research RMO to replace the debacle that is Replication Monitor 2005. Allen is a great guy, whom I’ve enjoyed connecting with through SQLJunkies and here at the conference. If you are looking for him at PASS, look for the guy that looks like Bill Gates. For those Buckeye Fans staying in Seattle during the game on Saturday, Allen says the Alumni Association is watching the game at a bar near the Space Needle.

Later in the afternoon I saw sessions on improving performance of SSAS and some methodologies (ADAPT and SPINE) for modeling multi-dimensional databases (Dan Bulos). These were great. I spend a lot of time ER Modeling, now I can do the same in the MD space.

Later in the evening was the exhibitor session. Quest is giving away shirts and pins. If you wear their T-shirt, you could win $1000. I thought about wearing the shirt today, but its blue, and well, being a Buckeye fan, I just can’t do it this week. Sorry Mike Walsh!

I also got to meet Adam Machanic face-to-face. We’ve emailed and responded to each other’s blogs, but finally I can put a face with the name. Nice to meet you Adam!

After the exhibit was the PASS volunteer night at Gameworks. I was really tired and everyone else in the room seemed to be, too. I didn’t stay long, but it looked like a good time. Thanks to PASS for recognizing volunteers. This continues to be a first rate organization.

PASS 2006 Pre-Conference

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

Open Source IDE for .NET

A friend of mine, Scott Swisher, passed along this information. I thought others might find it interesting. SharpDevelop is an Open Source IDE for .NET development. While not complete, especially for ASP.NET, it is interesting to see where they are going with it. I haven't played with it yet, but I plan to. Has anyone else used this? Any interest in such a thing?

http://en.wikipedia.org/wiki/SharpDevelop

Changing SSIS Magic Folder

Basically, you can find this information in a few other places, but it took me a little while to find, so I thought I jot it down, so I remember and in case someone else thought it was useful.

First of all, I want to thank Kirk Haselden for his fine book on SSIS. It has truly been a lifesaver in developing SSIS standards for a customer. In his book, Kirk talks about the "magic folder" for SSIS. This is the folder where the SSIS Server looks for packages. Essentially anything with a .dtsx extension will show up in SSMS when managing an SSIS instance. By default, this is a folder buried deep in the bowels of Programs Files, namely C:\program Files\microsoft sQL Server\90\DTS\Packages. This is a relative path to the install (..\Packages), so it can also be moved if you move the install location for SSIS.

Just as Kirk describes in his book, you can change the location of the magic folder by editing the config file, MsDTSSrvr.ini.xml, changing the StorePath value (p.383). You can also add more StorePaths to have multiple file locations for SSIS to look. But you can get this just by reading the book...

Now for the good stuff. We wanted to have a known location for our SSIS packages, not the relative path that comes in the default config. We wanted to hang the store off the root of the E: drive in the SSISPackages folder. It wasn't documented anywhere, but there was a hint of it in Kirk's post about clustering the SSIS service. The path doesn't need to be relative, it can be hard coded to a drive letter if you like. We changed the StorePath value to E:\SSISPackages, restarted the SSIS service, and voila, we could see packages in the folder via SSMS.

Again, you can find this information in a few places but it wasn't explicit. Hope this helps someone other than me.

Jon

SSMS, UCS-2, and Source Control Issues

Bumped up against and interesting problem today. Seems that Management Studio by defaults save your code as Unicode, UCS-2 to be exact. Previously, Query Analyzer saved files in ASCII text format. You might be saying, who cares! Ah, but if you want to use source control (especially non-Microsoft flavors), you might. We found the problem when we realized that files were being checked in as Unicode, and our deployment automation tools were not working. At my current client, we use Borland StarTeam for Source Control. StarTeam was treating the files as Binary, which meant we couldn't diff, merge, search, etc.

Well after a bit of research, there is a work around for this UNICODE vs. ASCII problem in SSMS. You can save as something different by clicking File | Save (or Save As…). When the dialog box appears, click the drop down on the Save button. Choose Save With Encoding

Another box will appear allowing you to choose ASCII text. I could find no way of making this the default, so if someone does posting it would be appreciated.

So if you want your developers to be sure their files are ASCII, this is the only way I could find. Here's a less than politically correct opinion from HsuGotaQ on a blog. http://hsugotaq.spaces.live.com/blog/cns!B10E712EC5D7D360!863.entry. Thanks for that input.

Another use for DB snapshots

In his post on DB Snapshots, Andrew Calvett offers some great examples of how to use DB Snapshots. I'll offer another.

I currently work with a General Ledger and Accounts Receivables system that has some large batch cycles. Testing for these behemoths can take hours at a time, which only changes a fraction of the database. A second run of a batch test sometimes must wait on a database restore, which severly limits the number of test cycles in a day. I'm working to use snapshots to speed up the database restore time, allowing the QA team to get more than 1 test per day.

Pros :
Restore performance could be greatly increased
More testing = better code (or so you'd hope)

Cons:
If the batch changes a lot of data, size could be a factor
There will be some performance overhead as a write to the source DB means a copy of the page to the snapshot DB.

What do you think about database snapshots? How are you using them?

Jon

Long Absence, and a farewell

It has been nearly forever since my last post. I've been buried with projects of late. Currently I'm working on a major redesign of the external facing website database for a major automotive finance company. Also, I'm working for that customer to redesign the account receivables system from the ground up. Both projects should keep me pretty busy over the next few months.

I've been working on posts for RMO, Reference Architecture database design, a review of 3 major modeling tools' support for SQL Server, and some others. I'll try to get some content out there shortly.

Lastly, I want to let everyone know I'm no longer working with the Central Ohio SQL Server SIG. There's just too much going on right now for me to manage it with the energy it requires. You might still see me speak at events, but I won't be leading it any longer. It has been a great couple of years. We are working now on a transition plan with Microsoft and some local partners. Stay Tuned! If anyone is interesting in leading the group, please feel free to email me.

Thanks

Jon