SQL Server Reference Guide

People who work in the “database” field are normally divided up into two or three major camps: Database Administrators (or DBA’s), Database Developers, and Data Architects. I’ve even explained what those “levels” mean to me, based on my observations.

But I’d like to propose another term that I think all of us that work in the database field should adopt: The Data Professional.

The reason I think this term is a better moniker than “DBA” or “Database Developer” is that it better describes the role we play in an organization, especially among Microsoft shops. You see, SQL Server is often touted as “easier” to work with than other platforms. For the developer, the Microsoft languages are geared to working with the “data platform” in the ways that SQL Server expects. Visual Studio, the default tool of the Microsoft developer, is even instrumented to have SQL Server objects embedded directly in the projects and solutions it uses. For the administrator, the management tools have multiple wizards, right-click actions, built-in help and other aids that the management tools in Oracle and IBM can only dream about.

But this “ease of use” comes at a price. I have heard from many managers that they can hire a Microsoft developer or DBA more cheaply than one that manages or programs Oracle or IBM systems. This perception of ease sometimes leads to a comparison of the skill sets found in some Microsoft professionals with those to another platform’s DBA, with statements like “Brand X DBA’s are more educated or familiar with their platform.”

I’ll actually agree to some of that criticism. I’ve been an Oracle, IBM and Ingres DBA in my time, and I have found that many of those DBAs really know their platform. But I’ll push back a little on that conversation to say that most of the DBAs I know that work with SQL Server do far more than just manage a database platform. In almost every case (and I’ve met a LOT of database professionals) we are asked to know about hardware, operating systems, applications, and other areas in technology than a “pure” DBA on another platform.

And I think that our role in a company is (or at least should be) more than just someone who makes sure that the database is backed up or that the user accounts are up to date. Of course, those things have to be done — but hopefully the ease of those tools I mentioned allow us to focus less on the mundane day-to-day tasks and instead focus on the more strategic functions where we add value to the business.

In this article, I want to put forth a plan that we can follow that provides a roadmap to getting to that data professional title. I’ll explain my thoughts on the things we should know and how and where we can find them out.

Like most of my articles, I’ll divide this into logical areas for us to focus on. I use the term “us” here because I’m always studying things to increase my knowledge. It’s one of the ways I add value to my company and frankly, the way I keep my job. There are three areas I’ll concentrate on: base knowledge, platform knowledge and experience, and business knowledge.

Interestingly, there isn’t a single course on all this information. I teach a college course on database technology, but even in the track our college uses there isn’t an “end to end” representation of this information. That’s OK, however, because you can put together a plan to follow to get the information yourself. It’s even available for free, in many cases. In most countries there are public libraries that can provide information in all of these areas, even if it might be a bit dated.

There are also “pay-for” versions of this information, such as the books you can find on this site and even courses you can take for various parts of the information. I’ll try and point out as many ways that you can find out more in each of these sections as I can, in the references area at the bottom of this article. The list I’ll show is by no means exhaustive, or even authoritative, but it will give you a starting point to e nhance your career.

Base Knowledge

I’ll start with the “base knowledge” that I think every data professional should have. If I had to pick out one area that I’ve found deficient in a DBA it is this one.

A data professional should have a solid working knowledge about two distinct areas before they ever consider working with a database platform. The first area is general computer science.

The term “computer science” is one that has evolved over time. Originally this area of study, and then later college degree, involved a heavy mathematical background. As it evolved, it included an expansion of the maths to include algorithms, which applied the math formulas to solving problems in a consistent, reliable manner.

As computers have evolved, so has the study of “Computer Science.” It now includes everything from the electronic components used in computing hardware to the logic and programming constructs used to write everything from operating systems to database platforms and the applications that run on them.

You can take a complete college degree in almost any country in computer science. I like to see a data professional with this kind of background, because it is an intensive period of training and covers some older concepts that are still valid.

But with a lot of discipline, you can get a similar background, and if you combine the enduring concepts in computing science with new material, you’ll find yourself right on target.

So what are the kinds of things you need to know in this area? Well, I’ve mentioned the maths and the algorithms, and those are a great place to start. And you should include a healthy dose of computing history. I’ve included a great reference at the end of this article that has a rundown of computer history, at least up into the 90s, which is quite useful. Knowing the history shows you why things are the way they are, and allows you to realize the leaps that are possible.

You should also know about the hardware in a computer, and how it works. I’ve met far too many DBA’s who aren’t familiar with how a CPU works, the effect of 64-bit architectures (and higher) on memory, the way a SAN operates, or basic networking techniques.

The second area of knowledge in the basics for any database professional is an understanding of data modeling techniques. This should include not only how to convert a set of requirements into an Entity Relationship Diagram, but even more general data modeling techniques.

Platform Knowledge and Experience

Platform knowledge involves two areas for the SQL Server data professional: the operating system and SQL Server itself.

You need to know the operating system because SQL Server is so tightly bound to it. You need to know the security setup in the operating system, the networking layer, and the domain entities and so on. You need to know how to read the logs and how to measure performance at the OS level.

And of course you need to know the SQL Server platform. If your duties revolve more around administering the system, you’ll want to study more of the “DBA” tools and information, and if you are a developer you’ll obviously want to study T-SQL and data programming constructs, but in both cases you want to have more than a passing knowledge of the other area as well. And in both cases you’ll want to understand “extra-platform” information such as Business Intelligence, including Analysis Services and Reporting Services. No, you can’t be an expert in every area, but don’t let that become an excuse not to learn a little about those areas as well, and any others that Microsoft puts into the product.

Along with this knowledge, you need to add some experience. Ask for, and pursue, new and challenging uses of database technology. Work with areas that are unfamiliar to you, even if you have to volunteer to do it. It’s well worth the effort – experience is always the best teacher.

Business Knowledge

In most of my articles you’ll hear me use the term “organization” to describe places where you and I work. I use that word on purpose. While many of us work at a for-profit business location, many other data professionals do not. They work at non-profit, government or volunteer locations, so I call these places “organizations” to capture both environments.

But in this section I’ll use the term “business,” even if you work at a company that doesn’t make a profit. That’s because business information includes effective and efficient uses of resources like time and money and how to get the most out of them. Most successful non-profits I know employ many good business practices.

But beyond this general business knowledge you should learn a lot about the business of your organization. I won’t include references for that, of course, since that will be specific to where you are.

But why should the data professional know a lot about the business? The developer doesn’t always have to learn about medicine to write a medical application, nor does the system administrator need to know metallurgy to take care of the network at a steel plant. The difference is twofold. First, you need to know about a business’ needs to accurately model its data. For instance, the business won’t know enough about technology to explain the data types to store a particular unit of medicine, and if you don’t know anything about chemistry, you won’t know to ask them how it should be stored. Second, it’s in this area where you can really add value. As the data professional, you know what data a company stores, where it is stored, how it is accessed and so on. By learning about what the company or organization does, you can discuss data retention strategies with your organization, security implications and other data protection methods. By knowing how to do an ROI (Return on Investment) calculation, you can save them money, and that makes you more valuable than just another DBA, no matter how much they know about their platform.

As you can see, there’s a lot to learn and know. To claim the title “Professional,” you need to put in the time and effort to earn it. And to earn the title “Data Professional,” follow the advice here. It’s worth it.

InformIT Articles and Sample Chapters

Computer Science: There’s an interesting take on this concept in the article Is Computer Science Dying?, and it covers some of the topics I mentioned in this article.

Data Modeling: I recommend you read How to Improve Data Quality to learn more.

The Windows Operating System: There’s an entire InformIT Reference Guide for Windows Server.

SQL Server: Here’s a list of all my articles on this topic.

Business Organization and Administration: We’ve bundled an entire set of articles on Business here.

Books and eBooks

Computer Science: There is no better CS book than The Art of Computer Programming, by Donald Knuth. I know, it’s only on programming, but it is amazing nonetheless.

Data Modeling: I’ve been reading A Developer's Guide to Data Modeling for SQL Serve: Covering SQL Server 2005 and 2008 lately, and it is very useful.

The Windows Operating System: Even if you don’t take the exam, MCSA/MCSE 70-290 Exam Cram: Managing and Maintaining a Windows Server 2003 Environment, 2nd Edition is very helpful.

SQL Server: Of course, I’m kind of partial to my own book, Administrator's Guide to SQL Server 2005.

Business Organization and Administration: Eating the IT Elephant: Moving from Greenfield Development to Brownfield is a great book that bridges IT and business.

Online Resources

Computer Science: I often use college course listings to figure out what I should study in a subject. Here’s one from Caltech. Once you have the titles, you can find books here at InformIT or at the library to learn each topic.

Data Modeling: Scott Ambler has a good reference for this that you can read along with my explanations here at InformIT.

The Windows Operating System: Here’s the primary resource for learning more.

SQL Server: The official site is here.

Business Organization and Administration: I use this site for business information quite often.