One of the earliest books on software for personal computers was named Nailing Jelly to a Tree. "Software is nebulous, and sometimes hard to get a grasp on," the authors wrote in the introduction. This is still true today: When people talk about software as a solution to a given problem, there's often a sense that simply throwing software at a problem will make it go away. The idea of planning how SQL Server is to be used -- asking questions about how the data should be structured and labeled and what sort of reporting services to use and so on -- often gets treated as an afterthought.
This sort of thinking is easy to fall into and nearly impossible to get out of. To keep you from falling into that trap, here are some key planning concepts to keep in mind whenever you use SQL Server as a solution to a particular business problem.
Know the variety of the problem
Is what you're trying to do a data warehousing problem (i.e., OLAP) or a transactional problem (i.e., OLTP)? The difference between the two usually comes down to volume and frequency. For instance, if you're writing a reporting system that produces thousands or even millions of records in a single report for only a few users, and the report is only run once a month, then the problem doesn't require as much subdividing and can generally be run as a single action.
If, however, you are producing million-row reports on demand many times a day, or hour, for many users at once, then it will put far more stress on your system. Upgrading to a faster server or one with more memory is not going to solve the underlying problem of an inefficient reporting system. In such a case, you need to subdivide the reporting process heavily into many smaller actions, each of which can complete separately so that other SQL Server functions can continue to run in parallel. There are other optimizations that can be made, such as keeping related data as close as possible physically, but this is one of the most important.
Think of it this way: Don't concern yourself with how fast a computer you need to run this particular problem, because in five years all that may change anyway. Instead, think about how the problem can be tackled now. If in the future you do upgrade and can, say, consolidate multiple reporting/analysis servers into one machine, the work you've done before to optimize the reporting or transactional processes will pay off all over again.
Use the right SQL Server tools for the right scenarios
SQL Server has many different tools for different jobs; get to know them and what they do, and don't try to make one do the work of the other. If you're dealing with a situation where much more data is coming back out of the system than is being put into it -- essentially, a data warehouse -- focus on learning SQL Server's Reporting Services. The Analysis Services are best for putting together multidimensional data analysis structures like cubes, but they can always benefit from intelligent optimizations on the part of the user -- which in turn often depends on how well the data itself is designed. (More on that in the next section.)
This caveat about the right tool also includes choosing between the different editions of SQL Server. If you're dealing with queries or analyses that routinely eat more than 2 GB to 3 GB of RAM, then the 64-bit edition of SQL Server (and a 64-bit machine) will be a big help now and further down the line.
Be conscious of your current and future data structures
SQL Server and its reporting tools may be out-of-the-box products, but you will have to devise independently the way your data is warehoused and stored. The more design issues you get out of the way early on, the better. Microsoft has some material specifically written for people building OLAP solutions.
The way you store things will be reflected in everything, including the naming conventions you use for your data structures. Even if you're not looking to get ISO certification, learn about ISO/ANSI naming conventions and put them to work in your data structures. They may seem a bit cumbersome and wordy at first, but in the long run they will spare you a great deal of pain. Remember: A little wordiness now means the data structures will become self-describing later -- especially if you move on and leave the project in someone else's hands.
Long-term planning is the most crucial thing: What good is all this work if it doesn't last for the years or decades it takes for a warehousing or reporting solution to be useful?
Learn from others
There's no reason you should have to make the same mistakes everyone else did. One particularly good book for understanding how other people might have solved the same problems is Impossible Data Warehouse Situations: Solutions from the Experts (Addison-Wesley Professional, October 2002). It lists almost a hundred common data warehousing problems and provides real-world solutions for them. The book is agnostic in terms of what products are used, so the solutions can be applied pretty universally.
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
