Jon Baker's Outer Join

For a pretty good hands-on lab of SSIS, see Kirk Haselden's post.  I've finally worked through these, and they are pretty interesting. 

At first I was very happy with the labs.  SSIS is so different from DTS, I hadn't been picking it up quickly at all.  This had been frustrating, because I'm pretty good with DTS. The first few labs really helped me get over some of the initial learning curve.

As I progressed through the labs, I started getting the feeling that I was missing something, like there should be some further instruction behind what I'm doing.  I then realized that I had stopped reading the explanations out to the left side, or they had gotten more sparse.  I was just following the step by step instructions on the right and, it seemed like I was only able to make things work if I followed the instructions exactly as they were typed.  This made me more like a robot than a student, like there is only one way to do this stuff, and deviation or creativity is not allowed.  I guess I could read the suggested BOL pieces if I want more information.

The labs have ended well, though.  They mostly put it all together, but I feel like I've looked at the first snowflake on the top of the iceburg.  I guess there's only so much you do in one setting/class.  I probably need to get a book to go a bit deeper, but these labs and BOL have gotten me off to a good start.  Kirk's book comes out in May, and the Wrox  book comes out the end of January.  Maybe I'll do a book review once I get through one of them.  If anyone knows of other good _FREE_ resources, please post here.  More snowflakes to look at...

Jeffrey Yao has written an article on SQL Server Central about how to be a good DBA.  Essentially, it is a roadmap for a developer or junior DBA to become the Sr. DBA.  While this is very high-level, it has some good points.  Since I'm often asked this question, I would feel fairly comfortable referring people to this article as a starting point. 

I do think Jeffrey missed some things, or at least didn't draw them out as plainly as I would have, so I thought, hey, why not add another entry to the blogsphere.

First, I think it is imperative to know set theory and relational theory.  Reading Dr. Ted Codd, Chris Date, Joe Celko, and Itzik Ben-Gan would be a good idea.  Often developers don't understand set theory and attempt to write data access logic using procedural constructs (loops, cursors, row-by-row processing). Yao hints at knowing T-SQL and data modeling well, which is good, but I think it is deeper than that.  T-SQL is an implementation (and not a very bad one) of SQL, which is an implementation of set theory.  Knowing the foundation is more important than knowing the implementation my opinion.

Second, I think Jeffrey glosses overy the importance of Information Delivery or Business Intelligence as a Sr. DBA.  This is all buried in the last point under Sr. DBA, but moving from relational structures to multi-dimensional structures that mean something to business users is a completely different way of thinking.  Being able to understand the business well enough to gather the requirements and communicate a proposed solution is a soft skill that Jeffrey mentions, but the technical side of that seems to be lost somewhere in the article in my opinion. 

I also think that Jeffrey missed a direction from which people migrate to DBA work.  I myself started as a programmer, moved to infrastructure, then settled in what I call the middle as a DBA.  I see people coming from both directions into the DBA role, and sometimes they lack the understanding from the other direction.  A network engineer coming to the DBA should learn some things about how to develop middle-tier and front-end code, just as a developer should learn the OS, network, and other infrastructure pieces.

On top of just learning how to program in a particular language or set of languages, I think an intermediate to Sr. DBA should work to understand the applications that use the data.  Not just the business, not just the language, but the architecture and implementation.  These will inherantly have quirks due to trade-offs or bad judgement.  The DBA should know these and learn how to work around them while not sacrificing the quality of the data.  The Sr. DBA should be part of the architecture team of an organization, helping to guide and direct the IT partnership with the business, including but not limited to how to fit new requests into the current architecture.

Lastly, I think it is important to stay current and up-to-date on skills and technologies.  This is hard to do, but it is very necessary.

Well, that's my $0.02.  Well done Jeffrey!  Thanks for writing this.

Jon

This was a great post I just read on the SQL CAT blog site.  It gives some nice practical uses for DMVs. 

http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx

As my vacation to the Mayan Riviera  approaches, I feel compelled to wish everyone Feliz Navidad.  Now if a particular song happens to get stuck in your head over the next few days, you can feel free to blame it on me! While this wish may not be as culturally sensitive as Kent Tegels, it seems especially fitting given my current bout with Short-Timer's syndrome.

As we approach the new year I wanted to also make a call for speakers to the Central Ohio SQL Server SIG.  We meet the 2nd Thursday of each month at the Microsoft office in Columbus.  If you have an interesting topic and/or want to advance your career in 2006, we would love to have you speak at one of our upcoming events. BTW...Dave Donaldson and I are currently working on a full day event in the March/April timeframe.  This would be like the Day of .NET, but adding a 2nd track for SQL Server 2005 related topics.  We could need speakers for that as well.  For those of you that attend the SQL SIG, if you don't volunteer to speak, your punishment is hearing me again, and again, and again in 2006.  Talk about motivation! :)  If you are interested, feel free to send an email.

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

I actually had someone question this week whether or not it was the right thing to do to use Windows authentication with SQL Server.  So that I don't have to go looking for it again, I thought I would post the links from the overwhelming amount of proof about this.  Hopefully it will save time for someone else, too.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec04.mspx

http://www.microsoft.com/sql/prodinfo/previousversions/securingsqlserver.mspx

http://www.windowsitpro.com/SQLServer/Article/ArticleID/39439/39439.html

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1126534_tax301336,00.html?adg=301324&bucket=ETA

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=130146&SiteID=1

That's what I found.  Feel free to chime in with your own.

Jon