Locking a database design, is it really possible ?

I wish I could see some improvements in the future version of SQL Server and .Net about their dependances.

I try to explain. In SQL Server if something happen to one of your database, whatever the catching error system you implement, you finish all the time by broken your beloved .Net application.

A good example is the identity field which is linked to an index key. This is a typical case where if you destroy the index, you lose the identity feature, and kaboom you trigger by accident an error on your application.
This is also true if you import export tables from one server to another.

Is it possible with Yukon to 'lock' the database design ? I like the Apple way to implement a lock in OS X settings, where you can lock/unlock the panel (if you are the administrator) and then up to you to change what ever you want.

Today I know I can implement some triggers in my database, but if you look at the big picture I think it could be a good idea to protect some long hours of database design.

 

6 Comments

  • In SQL Server Yukon, you will be able to put triggers on DDL statements, whereas before triggers were limited to DML statements. So if anyone tries a "create table" statement, you can setup a trigger that will disallow the action. That should include indexes too.

  • Thanks Darrell for the info. What about import/export ? It happens that Indexes are not created after this operation.

  • DDL trigggers in Yukon just like Oracle - you can add roolback in the trigger witrh audit.

  • Paschal, not sure about the import/export, as I don't have any Yukon bits. After I get a hold of it, I'll check into it though! :)

  • Paschal,



    What exactly do you mean by "import/export"? Is that DTS? Or some other tool?



    An index should be independant of whether a column is an identity column.



    As Darrell said, you can really restrict database changes in Yukon but that's a totally different thing than importing and exporting data.

  • If I use DTS to import a table from a Server A to a server B I lost systematically the index.

    And of course it's happen that I forget to recreate the index immediately so this obviously broke the code.

    Yes I can put more tests in my code but I am thinking in my post more about a strong partnership between the database and the code.

    Another thing happened to everybody human, like changing the type of a field. This can be disastrous but not so obvious. Try to change a text type field to a varchar(8000). In some cases your crude functions will stop to works and give you the so infamous error page. My point is that it's so easy to break a SQL database !

Comments have been disabled for this content.