CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
Denis
Denis

Reputation: 12077

Automatically checking the integrity of the SQL Server Database

Is there a way to check the integrity of a SQL Server database? I have these developers (OK, me included) that constantly change objects in our database (views/functions/stored procs) and sometimes we change signatures of objects or drop objects and other objects become invalid/uncompilable. I would like to get some sort of a report/alert on what objects are broken preferably one that could be integrated with CruiseControl.NET. Ideas?

Upvotes: 1

Views: 503

Answers (3)

user121489
user121489

Reputation:

I think what you need is database unit tests that can then be executed through CruiseControl.NET. I recommend using tSQLt (http://tsqlt.org). RedGate also sells a nice GUI over the top of this product, but is not required to get the functionality that you need. (http://www.red-gate.com/products/sql-development/sql-test/

Upvotes: 2

Neville Kuyt
Neville Kuyt

Reputation: 29629

The problem you describe is common - having multiple developers working on a database is problematic.

As far as I know, there's no easy way to check the integrity of the database objects in the way you mention - dependencies between stored procedures etc. are resolved at compile time, and the proc will return an error if that dependency is not valid at run time. You could re-compile all the procedures, functions, views etc. to flush out errors during a batch.

However, the problem is not that you need to check the integrity of your database, but rather that you don't have a process for avoiding problems with that integrity in the first place.

There are a number of solutions to this which minimize the problem; it kinda depends on your development set-up which way you go. The process is generally known as "continuous database integration".

Jeff Atwood provided an overview ages ago, and an update slightly less long ago (check out the links). Also look at the Redgate tools - these make the process pretty painless.

Upvotes: 1

Punter015
Punter015

Reputation: 1796

Olla Hallengran provides an excellant way to check the Integrity of databases. Please refer http://ola.hallengren.com/sql-server-integrity-check.html

Upvotes: 2

Related Questions