Simple SQL Source Control using DDL triggers

In the company I work for, we use SQL Servers with a lot of business code in stored procedures and functions. With growing complexity and multiple developers modifying the same code the need for source control has risen.
Unfortunately source control for SQL servers is not provided in a useful way out-of-the-box. TFS integration in SQL Management Studio uses local file which is a very unconvenient way. 3rd party solutions, like RedGate, are not fully intergated with the server, so it is possible to make changes without interaction of the source control...
We wanted a solution that enabled monitoring of every change on the database, regardless of the user having installed the product or not, and which does not require any overhead (such as the need to click on 5 screens just to change a simple procedure).

So we were looking for some other ways. After a little research we found this article, which suggested using DDL triggers. And that seemed to be the solution. Based on the article we created our own simple SQL Source Control system.

The solution

We added a separate Source Control Database named SourceControlDB on the SQL server. It contains a simple table (log) which is filled with database-change-logs.

CREATE TABLE [log]
(
    [PostTime] [datetime],
    [ServerName] [nvarchar](256),
    [DataBaseName] [nvarchar](256) ,
    [Event] [nvarchar](256) ,
    [ObjectName] [nvarchar](256) ,
    [TSQL] [nvarchar](MAX) ,
    [Login] [nvarchar](256)
)

On databases we want to monitor, we add a DDL trigger like this:

CREATE TRIGGER [SourceControlTrigger]
ON DATABASE
FOR DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TRIGGER_EVENTS
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO SourceControlDB.dbo.log (PostTime, ServerName, DatabaseName, Event, ObjectName, TSQL, Login)
VALUES
(
    GetDate(),
    @ed.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(256)'),
    @ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(256)'),
    @ed.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(256)'),
    @ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
    @ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)'),
    @ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)')
)

Now every change on that database is logged to the log table of the SourceControlDB database:

E.g.:

PostTime: 2011-08-19 13:17:38
ServerName: MyServer1
DatabaseName: MyDatabase
EventType: CREATE_PROCEDURE
ObjectName: sp_testLog
TSQLCommand: CREATE PROCEDURE sp_testLog AS SELECT 'Log'
LoginName: Company\User

How to evenly distribute a set to 2 parts

A few years ago I came acrross this problem. I had to evenly distribute a set of databases (of different sizes) on 2 servers, so that each server would be evenly loaded. After some thinking I came up with the following simple algorithm and afterwards proved it to be correct.


The algorithm

In the beginning we have - 1 set of items, 2 empty groups (into which the items will be distributed)

1. Sort the set by the size of its items, descending
2. Take the first item out of the set and put it in the group whose items have the smaller sum of values (or if they are equal in any one)
3. Repeat 2. while there are items in the set

Pseudocode

function distributeSet (set, group1, group2)
{
   order set descending;

   var sum1 = 0, sum2 = 0;

   while (set not empty)
   {
      var item = next item from set;

      if (sum1 <= sum2)
      {
          add item to group1;
          sum1 = sum1 + item.value;
      }
      else
      {
          add item to group2;
          sum2 = sum2 + item.value;
      }
   }
}

The proof

This algorithm can be prooved by using the mathematical induction.

We have a (descending) ordered set of items and two groups.

(1) The items in the two groups are evenly distributed in the beginning, because the groups are empty.
(2) 
a) Assume that the items in the groups are evenly distributed
b) We add the first item from the set, which is smaller or equal than any other in the groups (because the set is ordered descending), to the group that is currently smaller (let's call this group - A, and the other one - B). Before adding the item to A, we are sure that A <= B (smaller or equal). Now there are 2 possible cases:
(i) After adding the item to A, it is still A <= B and
(ii) it is A > B.
In case of (i) the items are for sure evenly distributed because the relationship of the sizes of the groups remains the same.
If in case of (ii) the items were not evenly distrubuted, and they would be evenly distribued if we put the item in group B, we would have increased the difference between the sizes of A and B. And because the set was ordered there is no item in B that is smaller than the current item that we could move to A to make the distribution even. Therefor in case of (ii) the items are also evenly distributed.

According to the mathematical induction we can conclude on the basis of (1) and (2) that this way of distributing the items into the groups is even.