WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Tuesday, March 26, 2013

The eye pleasing data alert

There comes a time in any developer's life where they must write some code that emails folks when there are data problems.  Regardless of the system or causation of a data problem, an alert must be sent.  In the days of being a DBA, the alert would generally be a text file or just plain text dumped into the body of an email.  This worked fine for me for several years as I was generally the primary consumer of such alerts.  Those days are over.

Enter working in business intelligence.  Now the data matters and the format it is delivered in.  The learning curve for me has been steep; I have always been more focused on content than aesthetics.  Now both are a requirement.  Initially, I thought this was silly but I am starting to understand the importance of things being right and looking good.  Alerts, reports, etc will be used and paid attention to when they have the proper coloring and gradients to express what the data is saying.  Just dumping numbers to the screen will not suffice anymore.  In addition, the obvious mobile device usage in this day and age predicates that the alerts look good on a mobile phone as well.

The process is brute force HTML construction, but it yields a pretty nice looking alert and does not require outright looping or much code.  Essentially, you construct a style sheet and then use a few TSQL tricks to generate the data set and create ladderstriping with Row_number() functions. (yet another term I was unfamiliar with until BI).  I would not call the process graceful, but it passes the eye test with the very picky data consumers I have :).



DECLARE @Alert NVARCHAR(MAX) = '' +
      
N'' +

N'Data Alert Title' + 
    N' Order NumberLineBusiness UnitDocumentDocument TypeCompanyCustomer ' 

SELECT @Alert = coalesce(@Alert,'') +
' CASE WHEN (ROW_NUMBER() OVER (ORDER BY [Date] desc,Document desc,OrderNumber desc)) %2 = 0 THEN 'r2' ELSE 'r1' END 
+ '">' +
'' + ISNULL(OrderNumber,'') + '' +
'' + ISNULL(CAST(LineNumber AS VARCHAR(10)),'') + '' +
'' + ISNULL(BusinessUnit,'') + '' +
'' + ISNULL(CAST(Document AS VARCHAR(24)),'') + '' +
'' + ISNULL(CAST(DocType AS VARCHAR(24)),'') + '' +
'' + ISNULL(Company,'') + '' +
'' + ISNULL(CustomerNumber,'') + '' 
FROM 
FROM dbo.MyDataTable
        WHERE alertProblem=1 )
DECLARE @rc INT = @@ROWCOUNT

SELECT @Alert += N'
' ;
IF(@rc > 0 AND @Alert IS NOT NULL) 
BEGIN
DECLARE @MailProfile VARCHAR(255) = 'mailprofile'
DECLARE @subj varchar(128) = @@SERVERNAME + ': DataAlertName'
DECLARE @FromAddress varchar(128) = @FromAddress

EXEC msdb.dbo.sp_send_dbmail  
@from_address = @FromAddress
,@reply_to = @FromAddress
,@recipients = @email
,@body = @Alert
,@subject = @subj
,@profile_name = @MailProfile
,@body_format = 'HTML' ;
END


Looking forward to trying one of these out for fixing fences and hauling rocks this spring.


Tuesday, March 19, 2013

Solving the Space Wars via Powershell

The constant battle as the consumption of data grows is storage space.  When a disk space alert goes off, you generally will jump on the server over RDP and start hunting through the drive for what has caused the growth to hit the "wake me up" limit.  

This has been an occurrence in my career for a number of years, but seems to be happening more and more often.  As my career has progressed, I create more and more monitoring for various things that cause outages ultimately but this was never put together.  Given the simplicity of this script, I wish I had done it when my primary job was being a DBA.  Sure would have helped on our development and QA servers with finding what data file grew out of sight, or what log file had gotten carried away.

So here it is, a pretty basic thing that just dumps the information to the out-gridview control.  This could be shortened and condensed but that would sacrifice the readability and that is more important :)

Note: You may need a second variable for the SQL Instance name if not default
 $Server = "DBServer"  
 [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  
 
 #Load up the SMO for the server      
 $sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $Server  
 
 # Grab list of drives - this should get us mount points as well as regular drives (not tested)  
 [Array]$Drives = Get-WmiObject -ComputerName $server -Class Win32_Volume |   
     Select -Property DriveLetter, Label, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } |  
     Where-Object{$_.DriveLetter -ne $null}   

 # Grab the data/index files for each database  
 $dbFiles = $sqlServer.Databases |   
   Select-Object FileGroups -ExpandProperty FileGroups |  
   Select-Object Files -ExpandProperty Files |  
   Select FileName,Name,UsedSpace,Size  

 # Grab the log Files for each database    
 $dbFiles += $sqlServer.Databases |   
   Select-Object LogFiles -ExpandProperty LogFiles |  
   Select FileName,Name,UsedSpace,Size  

 #Container to hold the information in    
 [Array]$diskSummary=@()  

 #Loop through each drive ordered by free space on the drive  
 foreach($drive in $drives | Sort-Object FreeSpaceMB )  
 {  
   Write-Host $drive.DriveLetter  
   Write-Host $drive.Label   
   #Grab the size and used space for each database file on the current drive  
   $size = ($dbFiles | Where-Object{$_.FileName.StartsWith($drive.DriveLetter)} | Measure-Object Size -sum).Sum   
   $used = ($dbFiles | Where-Object{$_.FileName.StartsWith($drive.DriveLetter)} | Measure-Object UsedSpace -sum).Sum      
   #Add that information to the array for output later  
   $diskSummary += $drive | Select DriveLetter,Label,FreeSpaceMb,@{Label="DBFileSizeMB";Expression={$size/1024}},@{Label="DBFileUsedMB";Expression={$used/1024}}  
 }    
 #Show the files and disk information  
 $dbFiles | Sort-Object DriveLetter,FreeSpaceMB -descending | out-gridview -Title "DB Files"  
 $diskSummary | out-gridview -Title "Disk Summary"  

So there is the basics to get you started.

I will be speaking on this and a lot more bright and early on April 6th, 2013 at SQL Saturday 206 in


Hope to see you there.

Thursday, March 14, 2013

Telecommuting, the beginning is ahead of us


Fascinating stuff has been written lately about telecommuting.  Yahoo is pulling in their telecommuters and in the wake of it, Scott Hanselman wrote a really accurate piece on the matter (my opinion based on 4.5 months of actually doing it).  And again, Thomas Larock wrote about it.  It is hard to know what the real motivation is at Yahoo, but the key public component comes down to accountability.

Or simply put.

Can the company see that you are doing your job and can you prove you are doing it well? 

In thinking about how it works for the team I am on, I can safely say the fact that we deliver cubes, reports, etc every 2 weeks helps in terms of visibility to the business and the stories completed/points burned shows that individuals are doing their part.

Here is how the team setup unfolds:
  • The bulk of my team, including the leads are remote.  The folks that live near the office work remote a fair amount of time.  I think that addresses the work place jealousy aspect (or at least I presume it does).
  • We use Yammer, Lync, IP phones and do Agile so everyone has at least 5 minutes of vocal contact per day and multiple ways to converse, badger each other, support, whatever. 
On the personal side:
  • I have been in IT for 12 years, and a "senior" level SQL DBA, so I have been in the industry awhile.
  • My previous jobs have allowed some degree of telecommuting, but never any extensive amounts.
  • My career path and presentations prove to some degree that I am "driven".
  • I require very little social interaction, thus the need for daily office interaction is less important.
With all that said - here is what I have experienced/noticed in my first few months on the job in no real order.
  1. I work too much.  Just plain not used to being the weak spot on a team, so its hard to not keep going, especially during these miserable winter days and nights.  Not to mention sometimes what I am working on is interesting enough I just keep working on it.
  2. Communication can be hard.  Nearly every single time a phone call, IM or screen share will solve it, but I think sometimes being in person would be easier.
  3. I get monumentally more work done in a day working from home.  I don't have to listen to a loud salesman "we can help align your vertical markets with value added synergies" and the bizarre lives of nearby cube mates.  I have become somewhat blissfully ignorant of the outside world - probably not a long term thing, but has sure allowed me to think about things that do matter, like fishing trips with the kids, foraging for mushrooms and the impending maple syrup season:)
  4. Daylight - I have been able to spend more lunch hours hiking with my dog (w or w/o a gun) then I ever have during the week.  I live in Wisconsin, daylight is short in the winter, commuting chewed up an hour and a half of my day.  Lunch hour hikes are the norm now, to the point my dog comes in and reminds me.  At this rate I might be in decent shape this year.
  5. Sunday nights used to be source of dread for me, the long drive in on Monday, the traffic, snow storms, rain, crashes, etc.  There has not been a Sunday since I started telecommuting where I hated what Monday brought.  That may be because my job is challenging and interesting too, but I know I hated the commute more than the work all along.
  6. Kids.  I have been to 4 school functions in the past few months, which might total the amount I had seen the previous few years.  I even wait for the bus with my son occasionally.  Some mornings they will get up excited and I get to be there to talk about fishing, ninjas, bugs or video games.
  7. Internet speed.  I live in the country, and I have good internet speeds, but not great.  At times this has become a bit of an issue as doing screen sharing where I am the host can be problematic.  But that is about the only real problem I have had.
  8. Leaving your work in the home office.  There is always talk about never being able to leave your work when its at home, but I don't recall that ever being the case in my neck of IT, we were working after we got home then too.  You just don't have to haul a laptop home with you.  
  9. I make fun of the "green" movement, but my family actually lives it.  Meaning a huge garden, lots of raising, harvesting and foraging of our food, and all the other subtle things we can do to lessen our impact on the environment.  Even though I carpooled into work as much as I could, the amount of miles I put on my car has gone down dramatically.  Not to mention I utilize a space in my house that would be heated regardless and I found a really cool highly efficient lamp that I use for light (when the sun isn't out).  These are all things my work place would have to provide for someone who is there 9 hours out of 24 during the week.
  10. While I am thinking about it from the business side, I think a telecommuting worker is willing to work for less money.  Some might not, but for someone like me that lives out in the country, that commute was a killer in many aspects of the word.  I was willing to give up money for life (and a really good opportunity).
There is more really, but that is a good place to stop.  Already looking forward to a noon time hike to a nice waterfalls with all this new snow on the ground and 34 degree temperatures.  



My office if lived on a lake :)


On that note, I better get back to work.