[Event Notifications] Automatically check in code changes to TFS

I'll be presenting this at SQLSaturday 255 in Dallas, on November 2nd 2013.

Two note to begin, because this is a lengthy post: there are two parts of this, and two scripts.
* The Powershell script will script out objects and check them into TFS.  No prereqs other than TFS and powershell.  You could just run this on servers daily and it would give you a basic "here's what the code is like". Basic but functional.
* The SQL script will look for new changes using an existing Event Notifications setup, then call the PoSH script for those objects.  More work, but IMHO more reward; you get real-time monitoring, information such as the LoginName of the user who changed it, and better comments.

So, I've been trying to figure out better ways to present code to our developers, as well as giving our group the ability to look back at code changes in production.  Our devs have Source Control for code that's deployed, but our Operations team has to look at objects that have been changed over time, be it via indexes, tweaks made in production for emergencies, permission changes, etc. 

There is one 3rd party app I know of that does this, but we had issues getting it to work.
So I decided to write my own.

And for me it seemed a natural to tie it in with Event Notifications.  My original idea was to just use EN to check the changes in, but that's not always feasible - if a table gets a new column, how do you represent that?  So I moved to a different model - EN detects the changes, and then a separate SP kicks off a powershell script that deals with TFS, by scripting out a new copy of the affected object(s) and checking it in.  And because we're using EN, we get information like the username of the person who changed it, what exactly they did, etc.


While this method can be used with any source control server, our company uses TFS to track changes.
However, I was trying to set it up on an existing server and didn't want a "heavy" client on that server.  Fortunately, I found on stack overflow a post (http://stackoverflow.com/questions/5503858/how-to-get-tf-exe-tfs-command-line-client, answer by ijprest) describing how to do this. Alternatively, you can install Team Explorer or Visual Studio with Team Explorer or use the Java TFS client.  The way licensing works, as long as you have a CAL, they don't care what client you use.

The next step was the scripting-to-disk.  I settled on using Phil Factor's code which uses powershell & SMO, then modified it for my use, including the ability to feed a server/database and have it script out the entire database and check it into TFS.  All the credit goes to him, all the blame goes to me.  Right now it's scripting "under" the database-level - you get all the changes within the database, but not changes ON the database, like changes to DB settings, files, and logins/permissions.  Version 1.1 will script at the server level and below.


So, at this point we have TFS, a SMO object scripter, next up is code that runs, looks at our Event Notification table, and call the Powershell script.  Our TFS does not allow every user to CHECKIN code, so rather than risk it failing I add their name to the COMMENT instead.  We are calling the powershell script via XP_CMDSHELL; there's probably a better way and I'll look at it for a future version, once PASS 2013 is over.

Overall, it seems to work well.  I need to work on some sort of dynamic exclusion list and a couple other tweaks, but overall I'm very happy with it.  As are our auditors.

Now, the install, which is intimidating.  But think of it the other way.  Create 3 directories, copy over a total of 11 files, run one script, create two tables and add one job.  The hardest part, personally, was getting the TFS workspace working.

First, create 3 folders:

C:\TFS_CLI

C:\TFS_CLI\App

C:\TFS_CLI\TF_Workspace

Next, copy the following 10 files from a valid TFS install into the \App folder.

 (Alternatively, install Team Explorer, which includes TF.EXE; you’ll need to change the commands as I’ve hardcoded the location of TF.EXE)

Microsoft.TeamFoundation.Build.Controls.dll

Microsoft.TeamFoundation.Client.dll

Microsoft.TeamFoundation.Common.dll

Microsoft.TeamFoundation.Common.Library.dll

Microsoft.TeamFoundation.dll

Microsoft.TeamFoundation.VersionControl.Client.dll

Microsoft.TeamFoundation.VersionControl.Common.dll

Microsoft.TeamFoundation.VersionControl.Controls.dll

TF.exe

TF.exe.config

At this point, set up your TFS workspace for this account/server. It assumes your TFS project is named $/YourENproject (I created a separate project just for this, so you don't risk overwriting new code), and the server is named your-tf-server-name.  The workspace will be called TF_Workspace, and will use c:\TFS_CLI\TF_Workspace

--unsure if this first line is needed or not. Run from command line in that folder

c:\TFS_CLI\app\>tf.exe workspace /new tf_workspace /location:c:\TFS_CLI\TF_Workspace

Unable to determine the source control server.

c:\TFS_CLI\App>tf workspace /new /noprompt /s:http://your-tf-server-name:8080 TF_Workspace

c:\TFS_CLI\App>tf workfold $/YourENproject c:\TFS_CLI\EN_Workspace /workspace:TF_Workspace /s:http://your-tf-server-name:8080

c:\TFS_CLI\App>tf workfold /unmap $/

 Now, try and check out an object - I put a test object in mine to make sure this worked.

c:\TFS_CLI\App> tf get $/Randolph/ftw-sv-db-03/assetexplorer/Tables/dbo.AaaAce.sql

Now, save the following script as sql_script_to_TFS.ps1 and place it in C:\TFS_CLI.  

Since it's Powershell you'll either need to sign it or run in an execution mode of unrestricted.  

Personally, I'd sign it.

https://gist.github.com/mbourgon/6873071 (in case it isn't directly below.

 Once you have it saved, try adding an object!

--SSMS, on my_personal_server

use  somedatabase

create table blah (id int identity)

Now from powershell:

C:\TFS_CLI\sql_script_to_TFS.ps1 -Server my_personal_server -Database somedatabase -objecttoscript blah -Author mydomain\validTFSuser

On mine, I get:

item exists; get, check out, script to override, check in

C:\TFS_CLI\YourENproject\my_personal_server\somedatabase\Table:
Checking in edit: dbo.blah.sql

(followed by a confirmation that it checked in)

All good?  Good.  Now for the final step - using EN to kick it off.

Create a couple tables to hold the details.  Mine are in my EN database, since that's where the data is.

CREATE TABLE TFS_Checkin_Errors (id BIGINT IDENTITY PRIMARY key, time_job_run DATETIME, original_id INT, resultant VARCHAR(8000))

CREATE TABLE TFS_Last_Time_Run (last_time_run DATETIME PRIMARY KEY )

Save out this script, then run it on the server.  

Run once to make sure it's acting as expected, then set up a job to run it every 5 minutes.

....and..... you're done!

Check the error tables for issues; right now it doesn't handle spaces in the object name, I'm fixing that this week.