SQL Server Best Practices: User-Defined Schemas

Schemas are a feature that gained wide use in SQL Server 2005. They are essentially containers that allow some objects to be grouped together. Actually, there have always been schemas in SQL Server, it’s just that we only exposed one – called "dbo" (short for Database Owner). In SQL Server 2005 and higher, you can…

1

SQL Server Best Practices: File Layouts (Revisited)

In a previous “SQL Server Best Practice” post, I made reference to file layouts and where various file types should go. I got a comment there that I felt deserved another post. The question was: “…7 individual drives?  If this is correct do you also recommend drive redundancy like raid 1 or 5?  Are there…

4

SQL Server Best Practices: File Layouts

In a previous post, I mentioned that you should use Filegroups and I talked a little about those. In this post, the best practice I’ll point out is where those files should live. As a general rule of thumb, more “spindles” in a database are better. What that means is that a larger number of…

5

SQL Server Best Practices: Create a Recovery Plan

That’s right – not a "Backup Plan", which most DBA’s already have, but a "Recovery Plan". What that means is that you are working towards the end-goal – a recovered system. Your Recovery Plan will certainly contain your backup plan, since you can’t restore a database that isn’t backed up. But the Recovery Plan has…

1

SQL Server Best Practices: Setting a Default Filegroup

When you create a database, you should always create Filegroups to store the data on multiple drives. Don’t confuse this with just creating multiple files in a database – that is useful, but not the same. Let’s take an example to explain what you want to accomplish. First, you can create a database with just…

9

SQL Server Best Practices: Following Best Practices

I’ve worked on several platforms, from Oracle and DB/2 to Sybase and SQL Server. Perhaps it’s because SQL Server is so simple to set up, use and manage, but it seems that in this platform I see a lot of problems with people following best practices. In fact, in some cases the DBA isn’t even…

1