Avoiding database design issues

In this world of mass data storage, database administration and design are two of the important issues concerning security of data and its optimisation.  This article points out some common database design pitfalls. 

Let's start with security

The login process of a database should be as secure as the data inside it.  How many databases still use the inadequate P@ssw0rd?  How many logins still use 'sa' and still have Local administrator groups active?  Allowing second-rate access safety measures can easily compromise your database.  Administrators should promote setup of complex passwords (but avoid such complex ones that they need to written down).

Security recommendations

Regarding access permissions, schemas are supported so use them.  If more than one application accesses the database, assign different schemas.  You can also utilise chain ownership, this is when a called stored procedure can have access to a table to perform operations by chain ownership.  This restricts the user in directly manipulating the table (if you restrict access to the table), but you can give them an execute permission on the stored procedure instead.

Backed up - Are you sure?

This is so important that I bet everyone is familiar with regular back up protocols.  But how do you know your backup worked unless you verify it?  You should always verify a backup where possible to ensure data was not lost during backup process.  You don't want to find out it failed when you need it most.

Avoiding performance issues

The design of a database should be as important as its security.  This helps optimise the database for complex queries.  Firstly, databases shouldn't be too flat as more tables equal less disk reads.  Another disk read concern is unsorted data due to heaps.  Avoid heaps if possible because this implies more disk reads.
Although some developers still insist on using cursors, where possible they should be avoided because 99% of the time they can be implemented in a set-based way and have better performance.  Naming is also very important. To ensure legibility use consistent table names. Avoid naming your stored procedures with the 'sp_' prefix as these are reserved for system procedures, and there are performance penalties if you use them outside of system databases.
When using databases and front-end application, separate the data from the business logic.  Avoid putting business logic in the database.  

Did you know?

• In newer versions of SQL Server, varbinary(max) was introduced to deprecate text and image because it carries no encoding issues and is very good for storing persistent xml.  Types should be defined appropriately according to the data contained rather than the default varchar length, for instance.
  
• Many people will know not to use @@Identity to get the last entered record identifier and to use the Scope_Identity() function instead. However did you know that using the OUTPUT clause on the INSERT statement can perform this more efficiently?

• And, lastly, to ensure fail-safe operation use transactions!