In response to requests made after I did a presentation on Microsoft TechEd Asia 2007 on SQL Server 2005 Always On Technologies, I’ve made the content available on my blog.
In as much as we want to, there are not as many options in SQL Server 2000 to address disaster recovery caused by human errors. And when we think about disaster recovery and high availability, we are often tempted to think about hardware solutions. With SQL Server 2005, we have a lot of options. This will be a series of blog entries discussion about the high availability options in SQL Server 2005. I would like to highlight, though, that disaster recovery is more a process than technology. We may have the available technologies at our disposal but if we do not have the right process, then, its practically useless. Prevention is one option. Let’s take a look at a simple scenario. Imagine a developer accidentally connecting to a production database and dropped a table. Now, if this table has implemented referential integrity and has foreign key relationships established on related tables, the related tables would definitely prohibit the execution of the DROP TABLE command due to referential integrity. But what about this table? Let’s say the Employees table is related to the Department table and a referential integrity is defined between them. We won’t be able to drop the Employees table because it references the Department table. But nobody is stopping us from dropping the Department table. The best way to prevent this scenario in SQL Server 2005 is to implement DDL(data definition language) triggers. We are all familiar with DML triggers in previous versions of SQL Server. DDL triggers are used to audit and/or prevent DDL statements at the server-level or at the database-level. You can prevent accidental data deletion (i.e., trapping and rolling back an attempt to drop a table) and unexpected schema changes. A benefit of using a DDL trigger is that it forms a part of an implicit transaction which caused the trigger to fire and, when necessary, rollback the statement when necessary. The data related to the event can be trapped even if the statement that caused the trigger to fire has been rolled back. This is beneficial if we want to audit who did what on either database or server level. A complete description of SQL Server 2005 DDL triggers can be found on the MSDN site.
Let me walkthrough a simple example on using a DDL trigger. Using the very familiar Northwind database (the Northwind database does not come with the default installation of SQL Server 2005. You can download the source from the Microsoft Download Center). First, create a table which will contain the metadata of the DDL query. We will use this to store those information for audit purposes
USE Northwind;
go
CREATE TABLE AuditDDLOperations
(OpID int NOT NULL identity CONSTRAINT AuditDDLOperationsPK PRIMARY KEY CLUSTERED,
LoginName sysname NOT NULL,
UserName sysname NOT NULL,
PostTime datetime NOT NULL,
EventType nvarchar(100) NOT NULL,
DDLOp nvarchar(2000) NOT NULL);
GO
CREATE TRIGGER PreventAllDDL
ON DATABASE
WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS DECLARE @data XMLSET@data = EVENTDATA()
RAISERROR ('DDL Operations are prohibited on this production database. Please contact IT Division for proper change control procedures.', 16, -1)
ROLLBACK
INSERT AuditDDLOperations (LoginName, UserName, PostTime, EventType, DDLOp)
VALUES (SYSTEM_USER, CURRENT_USER, GETDATE(), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
RETURN;
GO
The CREATE TRIGGER statement consists of
(1) the scope, which in this case is a database-level scope,
(2) WITH ENCRYPTION option to simply hide the trigger definition,
(3) the event on which the trigger will be associated with, this case will be all DDL events at the database level,
(4) the EVENTDATA() function which returns information about server or database events and is called when an event notification fires, and
(5) an INSERT statement to store the details of the EVENTDATA() function inside the table we created in the previous script.
Now, to test the trigger, simple run any DDL statement on the Northwind database.
USE Northwind GO CREATE TABLE Foo(col1 int); GO DROP TABLE AuditDDLOperations; GO
will create a database-level DDL trigger.
The previous statements are very simple DDL statements – one to create a table and one to drop a table. These statements will raise an error defined in the DDL trigger. To investigate the DDL statements and who executed them, we run a SELECT query against the table we created.
SELECT * FROM AuditDDLOperations; GO
The key thing here is implementing DDL triggers as part of your database implementation strategy to minimize disaster caused by human errors. In the next part of this series, I’ll talk about how you can recover from a dropped database in cases where you don’t have a DDL trigger defined in your database.
