Sql-server – Should I opt for a new database or adding more tables

best practicessql-server-2008-r2

this is a question around best practice that I'm currently dealing with.
I have a database (SQL Server 2008 R2) with a heap of tables (around 40)
with data, now the need has risen to have logging of which Changes are made
not from a technical standpoint but for instance if a row gets updated some
users needs to have a "tracking view" in the application in which they can see
which fields were changed and when.

So to my question, should I add a new table and store all this or is it better
to add a new database to handle all history stuff in? What are the pros and cons?

Best Answer

I would definitely err on the side of keeping the tables in the same database. It makes it easier to maintain referential integrity (unless you hard delete records in which case foreign keys won't work for you anyway) and create consistent backups (simply backup one database and you know it is in a consistent state, instead of taking two+ backups that my be slightly out of sync).

If you concern is wanting to spread the data between different storage pools (for instance keeping history and audit data on something big & cheap but potentially slow, while keeping active data on something faster but smaller & more expensive), or just keeping the different data on different spindle-sets to reduce potential IO contention, then you can achieve this within a single database by distributing your tables between files/filegroups instead of just having one filegroup containing everything. See http://technet.microsoft.com/en-us/library/ms189126(v=sql.105).aspx (amongst other reference locations in MS's sites) for more details on this.

Related Question