SQLServerPedia

Note: Let me start off by stating my definition of physical database design might be broader than the classical definition.

A look into the past

The landscape changes a little with each release. In SQL Server 2005, partitioning, partial database availability and mirroring really stand out as things to consider when planning the physical design. Less databases are better for mirroring. You must think in filegroups when partitioning and when implementing partial database availability.

Learn and plan today

What about SQL Server 2008? Well, at first look, nothing really changes from the physical database design concepts except filestream and filtered indexes. Check out what's new in SQL Server 2008.

However when you pull out the feature palette and start painting, it is deeper. What if you want to encrypt and compress data\backups? They don't work well together. You could put the sensitive data in a separate database and use transparent data encryption(column level encryption could be argued for as well.) The idea being the sensitive data is a small percentage of the total data. With filestream, considerations go further than just the location of the files. It would probably make sense to separate file stream data in its own database as well. Filestream doesn't play well with others just yet. Some issues are mentioned here. Mirroring, TDE, snapshot isolation and other are not supported on a database with filestream enabled. How about partitioning and compression? You could compress the partitions that are less often queried and less likely to be in cache while leaving the hottest data uncompressed. Sprinkle on compressed ,covering, partitioned but aligned, filtered indexes with a cherry on top just for fun!

Design for the future

At any rate, I don't have any production SQL Server 2008 servers yet much less any databases designed from the ground up for SQL Server 2008+. We should still plan for new features even if we are still designing SQL 2000\2005. Some of these are hard to change later. Imagine squashing several databases together that have been in production for several years. How about moving all of the objects out of the primary filegroup to implement partial database availability on a database that needs to be ..err... available.

Any other angles I am missing?