Great Bosses

We are not all lucky enough to have a nice boss who appreciates our efforts.  Today, I was reminded that I do.   He does little things with some regularity that show that he does appreciate the work that we do, and today he brought me and the other members of our team each a slice of Key Lime Pie from Sullivans Steakhouse.   He had gone there for lunch and thought it would be nice to share a bit of the joy that he experienced there.

This was definitely not the first time he has done this sort of thing.  In the past, he has brought us Cinnamon Rolls from Quick Trip, which is much better than you might anticipate and my favorite memory of something he has brought us in was when he brought in a Tartufo from Jaspers which is still my favorite dessert that I have ever eaten.   According to the linked site, it is a “Homemade white chocolate gelato dusted with chocolate biscotti and chocolate sauce”, and it tastes even better than it sounds.

It does not take much to be a great boss, but sometimes it is these little things that make all of the difference.   They were not requested, were completely unnecessary but definitely showed me and the other members of our team that he cared.  Sometimes that is enough.

If you have any stories of things your boss has done to show you they appreciate the efforts that you put in, please share them.  I know I always enjoy hearing stories about people who have done something extra to take care of their own.  It seems far more often, it is the other side of the story that gets told.    But there are lots of good ones out there, so why not share what they have done even if it seems small.

Create or Organize Your Script Library

Do you have your own script library?  If not, you should absolutely create yourself one as they can be invaluable and can save you from a world of pain.   Jeremiah Peschka blogged about versioning your scripts and it shows a great way for you to go through this process using a few different free options.   If you are starting from ground zero, start here or at the very least create yourself a working folder on your computer and save off anything that you come across.

Now comes the second group of people, we will call them people like me.   You have a script library (in my case a very large one) and you can find your scripts without any problem but the scripts are not well organized.   Now is the time, get them organized and maybe put them into a GitHub or BitBucket.   Start versioning them appropriately as you make changes to them or as software changes versions and they need to be modified.   This is the situation I am in today, and my plan is to get them neatly organized and put up in a very easy to access, easy to reference repository within the next couple of months.

For those of you who are already on top of this, tell the rest of us the way that you did it.   I am sure there are dozens or hundreds of ways to save off your script library and any tips you have for those of us without or who are less organized would be greatly appreciated.

And two months from now, I will put a new post up saying where I have made it.

SQL Agent – Powershell Task Appears To Do Nothing?

Have you ever come across a situation where you have some Powershell task within a SQL Agent job and it appears to be taking WAY longer than you feel like it should?   If you have, you are not alone and if your situation is like mine, this is by design!

Now the question becomes, what can you do?   The answer is pretty simple, you just have to tell SQL to allow Powershell to use more than 2 threads at a time.    The number 2 comes from the msdb.dbo.syssubsystems table, which you can view all of the settings by running the following:

SELECT * FROM msdb.dbo.syssubsystems

And as it turns out, you can very easily change this by running SQL similar to this (pick your own number…pick it wisely):

Update msdb.dbo.syssubsystems
set max_worker_threads = 10
where subsystem = 'Powershell'

 

And after restarting the SQL Agent, you can have up to that many jobs with Powershell steps all running simultaneously.

I have not personally come across any repercussions of changing this setting, but if you do please fill me in.  I would love to hear those stories  before I have to live through them myself,

SQL Express – How do I back this up?

An alarming number of third party applications come with an installation of SQL Express, and without any intention of it ever getting backed up.   I have been lucky to have never found this out the hard way, but I can envision many who have.   So what do we do about these SQL Express instances that have no SQL Agent and no simple way of backing them up?   I have a couple of approaches that we have used in the past and figured they are worth sharing.  If this does not help anyone, maybe it got you to go out and scan your LAN’s for SQL Express instances out there which would be a win on it’s own.

  • The SSIS Approach.

Assuming you have another server pretty much anywhere that has SQL Server Integration Services installed on it, you can create yourself a very simple package with very simple Execute SQL Task that runs the backup against the remote SQL Express instance.  You can follow this up with file deletion using either a Script Task to delete expired files or via the xp_delete_file command that Patrick Keisler talks about very nicely in his blog.  A simple approach and it is usually within most people’s basic tool set, since it is just using SSIS and a fairly simple package.

 

  • The Powershell Approach

With the Powershell approach, you need to have another SQL Server instance that is SQL 2008 or above (and not SQL Express), so that it can run a SQL Agent Powershell script task.  This approach allows you to specify the Server\Instance, Databasename, path you want to backup your DB to and the Path you would like to copy the file over to and because you should, it even includes running your CheckDB for you.

####set up parameters
$servername = "mysqlexpress\instancename"
$dbname = "YourDBHere"
$bkdir = "\\UNCPath\Share\Name\AndFolderHere\"
$copydir = "\\AnotherUNCPath\ToCopyTheFileTo\SoThatYouHaveRedundantBackups\"
$searchstr = "*DBName*"  
$searchext = "*.bak"
$dt = get-date -format yyyyMMddHHmmss
$bkfile = $bkdir +  $dbname + $dt + ".bak"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null
 
# create source and destination sql servers
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $servername
# set backup directory, dbname and grab date
#create backup object and set properties
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$dbbk.Action = 0
$dbbk.BackupSetDescription = "Backup of " + $dbname
$dbbk.BackupSetName = $dbname + " Backup"
$dbbk.Database = $dbname
$dbbk.Devices.AddDevice($bkfile, 2)
$dbbk.MediaDescription = "Disk"
$dbbk.Incremental = $false
 
#Backup the database
$dbbk.SqlBackup($s)
Copy-Item $bkfile $copydir -force

Invoke-SQLCMD -Query "DBCC CheckDB([$dbname]) with no_infomsgs" -ServerInstance $servername

Nothing earth-shattering here, but if you ever find that you have an extra SQL Express instance out there and it is not being backed up, either of these two approaches should get that job done for you.