[Event Notifications] Getting auto-TFS checkin set up for TFS 2013 (SQLWatchdog)

We recently upgraded to TFS 2013, so here are the steps (written as I do it) to get it running.

Aside: Since I'm using Powershell to run all this, why do I use the TF.cmd application instead of the Cmdlets?  Simplicity.  The Cmdlets appear to need the FULL version of a TFS client installed, plus you have to grab and install the cmdlets... and there are bugs if you don't use the right (x32 vs x64) version of the cmdlets with the right version of powershell.  So I built it around the TF CLI (Command-Line Interface).  The original post was for 2008, the new one (plus script below) are for 2013.

IMPORTANT:
Upgrading from 2008 to 2013 blows, since the config and cache can be screwed up.
What I had to do was completely drop the workfold and the workspace (PROTIP: first, list your workfold and workspaces and SAVE THOSE DETAILS), delete the entirety of the cache folder, delete everything from my EN_Workspace folder on disk, then rebuild from scratch.
One other oddity: before, from the root of my installation (c:\tfs_cli) I could run the script, and it could properly list the folders underneath it.  That appears to have changed for some reason; my Powershell script now CDs into the subfolder before it proceeds.  Not a major change... but took several hours to diagnose, since I was also fighting with other issues on the setup.  Caveat Emptor and all that.

Also for the TFS newbies: the default path you use for a LOT of things is:
http://myservername:8080/tfs/DefaultCollection .

Steps to get going:

• Install Java.  Got it from Oracle, which doesn't appear to have the same cruftware that Sun's does. Version 7-something
• Grab Team Foundation Everywhere 2013. Reading the docs, it works for TFS 2010, 2012, 2013, and Online. http://www.microsoft.com/en-us/download/details.aspx?id=40785
• Unzip into one massive folder (I called mine "c:\TFS_CLI\APP_2013"), then try running (since I'm on Server 2012, in Powershell):
• ".\tf.cmd" 
• it works - success!  Sorta.
• Found this page which seems to make the below setup easier: 
• http://msdn.microsoft.com/en-us/library/hh873092.aspx
• For the rest of this, I'm doing it as the Service account that SQL Server is running under on the box that will run the job. Oddly enough, it wasn't set that way when I built it on 2008, but it continued to work even though my password changed.  Note that nowhere in here do you actually put in a password.
• Type ".\tf eula", then accept ("y"). 
• muddle through the web site (tfsservername:8080/tfs) and add the SQL Service account as an admin to the branch I'm using (YourENProject).
• Using the MS page above:
• .\tf workspace -new TF_Workspace -collection:http://mytfservername:8080/tfs/DefaultCollection
• the collection URL is pretty standard - it should work in 2010,2012,2013.
• .\tf workfold -map $/YourENProject -workspace:TF_Workspace c:\TFS_CLI\EN_Workspace
• This line has several names.  Let's break them out.
• $/YourENProject is the "Branch" in TFS.  Call it something like $/ProductionEN or something like that.  I created the branch using SSDT before I did any of this, but you can create one using the TF.exe
• TF_Workspace is the workspace you created in the step above.
• c:\TFS_CLI\EN_Workspace - the physical folder on your EN server (NOT the TFS server).  I named them differently to make it more obvious what each part of the command is doing.
• now try and check something in.
• Get to the workspace
• cd c:\TFS_CLI\EN_Workspace
• new-item test.txt -type file
• &cmd /c "c:\tfs_cli\app_2013\tf add test.txt"
• &cmd /c "c:\tfs_cli\app_2013\tf checkin /author:memyselfandi"
• I use that weird syntax to catch errors and return as text into Powershell, so it can properly eat and pass on errors as necessary, as opposed to having PS choke on it.
• Pull and update something: this is where I had massive problems. (use script)
• Finally verify it worked (I used my "real" TFS client on my desktop).

Now, set up the SQL Script (SP), the Powershell script, and a job that calls the SP.

SP:





 Powershell:

[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.