[Event Notifications] my current slide deck

Thought I'd share this - the entire folder I run off of is shared, but here's the slide deck, embedded.  It should get new changes as I make them. 

[Event Notifications] adding a new box to your environment

Short and sweet - just realized I don't already have this on here.  This makes several assumptions in my environment, and you can modify as needed . Yes, it's ugly.  Yes, security holes due to xp_cmdshell and the like.  You've been warned.

Practically: you run this on the box that already has the repository.  You tell it the name of the new server.  It connects to that server, makes sure each side can reach the other, then enables Event Notifications on that new server.  It ends by making a change, waiting several seconds, then selecting from your repository table.  If it shows, you're golden.  If not, time to roll up sleeves and figure it out.

[Event Notifications] Tackling Database Lifecycle Management and Database Drift (via Redgate)

First, I'd like to thank Bill Fellows, Megan Longoria, and the rest of the Kansas City crew for an amazing weekend at SQLSaturday #300, aka #SQLSatKC.  We hit 2 BBQ places with them, another BBQ place and Boulevard Brewing with some other attendees, then went to an amazing baseball game.  Saturday was a full day with some awesome training (everything from "What's a DBA" to collecting ultra-detailed memory-consumption information), even more awesome BBQ, and ended at the nearby amusement park. 

My presentation went quite well, aside from all 3(!) VMs deciding to BSOD on me 15 minutes before I went on.  Good number of attendees (always a concern for me, personally).  Had some good questions, I like the audience participation & humor I've added, and had a good conversation on it afterwards. Overall, a definite win.  Now I need a new presentation so I can come back next year!


Additionally...


Normally I wouldn't just link to someone else's article, but they are more eloquent than me.  A good article on why you should track database changes in production (like I do with SQL Watchdog, aka "Master of All I Survey", aka "Evil Overlord").  That being said, I love the term "Database Drift", and I'm glad to see more companies looking into solving this issue.

https://www.simple-talk.com/blogs/2014/09/10/tackling-database-drift-with-sql-lighthouse/#.VBbX7AevYv4.twitter

[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] SQLWatchdog! (Coming to KC in a week)

I'm pleased as punch - presenting at SQL Saturday #300, Kansas City, in a week.

The title is "Master of All I Survey", because I wanted a grandiose title that would get butts in seats.  I was the target audience during 2 different PASSes where it was presented, yet didn't go to it because the title/abstract didn't sound relevant.  Sad to say, since I was the target audience!  Queue the "better" title. 

Alas, I seem to be the only one in love with it, so I changed it to "SQLWatchdog", based on a coworker's suggestion (HI, JASON).  It gets the point across - a Watchdog for SQL. 

If you look in this blog, you'll see it referenced as a couple of things, but the tag I try to use is "Event Notifications", so you can find them at http://thebakingdba.blogspot.com/search/label/Event%20Notifications


And as the KC SQLSat group appears to know how to have a good time, I'm ALSO going to fill my belly with BBQ, share it with the world after hitting roller coasters, and even sneak in a game.  A wonderful weekend to be sure.

See you there!
http://www.sqlsaturday.com/300/eventhome.aspx