SQLSimon.com

Listed below are some of the best practice tips gleaned from various books and experiences:-

• System databases should be stored on a different drive to the main database. This helps to avoid disk contention issues which could affect performance.
• Put the transaction log file on a separate disk to the main filegroups. This helps to avoid disk contention issues which could affect performance.
• Keep databases of the same collation on the same instance. Problems arise when using temporary tables where there is a mismatch between the collations of the originating database and the system databases (in particular tempdb.
• System databases should be backed-up and restored with the other databases, otherwise database users are "orphaned".
• When partitioning tables, partition the indices using the same partition function, so that they are aligned. This enables filegroup back-ups and archive procedures to be implemented.
• Only grant application users the permission to use stored procedures. This helps reduce the risk of SQL injection (a hacking technique).