Most everyone I know works with environments that are carefully controlled and structured. All changes go through rigorous testing and full documentation. Absolutely nothing happens in a production environment that hasn’t been thought through, discussed, planned for and written down. But, there are a few, a very few, who work in a slightly different kind of environment that they refer to as “the real world.” In this “real world” changes to a production environment can happen without approval, planning or testing. Scary, right?
There’s good news for these poor benighted souls. Red Gate is testing out a new piece of software called SQL Lighthouse. It’s meant to monitor your systems for changes so that you know what has happened and when in case you don’t have a good source control system in place with a well managed deployment process. Right now it’s only running against Windows Azure SQL Datbases for testing, but you can try it out. It’s pretty easy. Go to SqlLighthouse.com and create an account. You can then add a database:
Everything should be pretty straight forward on this screen. The server name is the full name; xxx.database.windows.net. I recommend using the Test button after you put in your user name and password.
Representing a “real world” crazy DBA, I’m going to introduce a clearly unauthorized change to my database:
CREATE TABLE CrazyDBA
(
CrazyDBAID INT NOT NULL
IDENTITY(1, 1) ,
CrazyDescription NVARCHAR(200) ,
CrazyValue DECIMAL(19, 2) ,
CrazyMoney MONEY ,
CreateDate DATETIME
);
A heap table, no primary key defined, poor data type choices like MONEY in use. No one ever does stuff like this. But, if we switch back to SQL Lighthouse after making the change, my monitored database looks like this:
Which is, of course, clickable. Clicking on it, you’ll see a listing of changes that have happened to your database:
Someone has been going nuts inside my database because I’ve got a long list of changes that have all occurred today. You can see the bread crumb trail forming at the top which will allow us to navigate back to previous windows. We can configure alerts for this database from here (more on that in a moment). Each of the changes is clickable as well. Opening up one, I see this:
And there’s my change from earlier when I created the table.
In the mean time, I might notice that I have new mail in my inbox. By default I’ll get emails automatically with the changes that have occurred:
And as it says, I can click on “More details” to go to the web site to see the details of the changes. Now, getting an alert for every change or set of changes might be a little chatty. So you can configure the alerts for the database:
This can help to reduce the noise if you’re dealing with a lot of changes occurring over time.
That’s it. It’s nice, clean and simple. We’re just getting started with it, figuring out what it can do and where we can go with it. I know that most of you don’t have to deal with this so called “real world,” but for those who do, this might be prove extremely useful.
Looks good and all but also looks overly complicated and convoluted. A simple database trigger that captures any DDL and emails you who was the user and what was the code they ran seems like a much simpler way in the real world ???
But you can’t set that style of trigger inside WASD. Also, a lot of us are kind of freaked at the prospect of triggers in our systems. Or, some of us may not trust our own skills at correctly writing triggers.
Regardless, you’re right. That is another way to take care of this issue.
ObEventNotifications comment, since DDL Triggers have caused breakages to replication, as well as failed patches. But I saw SQLLitehouse at PASS and it seems pretty cool!