Voice of the DBA

Category: Blog

  • My 2015 Plan

    I started this on Jan 1, but with family commitments, I ended up delaying it until today. I wrote about having a 2015 plan, and thought I should build one as well. I’ve tried a few things in the last couple years, and while I haven’t stuck to the plans, I have thought that my years were successful. This year I’m hoping to do a slightly more formal plan.

    I’ve broken this down into a few areas, all of which should help my career. These are the high level areas, and while I have some plans, I suspect that life will get in the way and things at work with change, so I have to be flexible.

    Certification

    While I’m not sure certification really helps me in my career, I do know that it is something that catches eyes on resumes. What it means after that is up to you and how you sell/market/position it.

    However I do know that certification efforts can drive learning. I took a number of the SQL Server 2012 exams when the product was released, but never took the last one I needed for certification. I didn’t have the time to schedule it at the time, and it’s bothered me every since. I need the 70-462 exam for the MCSA certification.

    In November or December, I noticed Colleen Morrow writing a series on exam prep for the 70-463 exam. It got me thinking. I should follow along, study, and then take the exam. Since I have a relatively light travel schedule for Q1, this is something I want to work on.

    PowerShell

    I tried to do the Powershell Challenge last year with the book Powershell in a Month of Lunches last year. I used to take the book, my laptop, and work through exercises while at my daughter’s volleyball practice. I reached a point where I got off track, had some networking issues, and ended up not completing it.

    However I learned things. I ended up writing quite a bit about Powershell and using it to accomplish small tasks. In the new year, I want to use it more, and one of the places it to replicate lab setup for new machines and rebuilds. That’s the goal as I build out new systems in 2015.

    I do want to complete the challenge, but we’ll see how it fits into my schedule.

    Speaking/Travel

    Last year I actually spoke less than 2013, but it felt worse. I think the problem was too many events in too short a period of time. Three in a few months and four in one. I need to balance that out in 2015.

    Red Gate is also looking to change some of our SQL in the City seminars. We may end up doing more private, as well as all day, training seminars on our tools and the Database Lifecycle Management (DLM) topics. I expect that I’ll need to spend more time here, which means less other events.

    I’ll likely also skip some of the bigger conferences. I’m speaking at SQL Konferenz in Germany and (hopefully) SQL Bits. I will likely submit to DevConnections, but that may be it. The multi-day events are a time sink and drain and if I’m going away for a few days, I think I’d rather invest in myself at a SQLskills event.

    As far as SQL Saturdays go, I think Q1 is the first time I don’t have a speaking engagement in a quarter. The Jan ones came up too fast for speaking deadlines at the end of the year and I wasn’t sure I was up for a trip. With overseas trips in Feb and Mar, I’m not sure I’ll make any, though Pordeone in Italy is tempting.

    I am thinking to get to Madison and New York City, but this might be a year where I speak at less than 10 of them. That’s usually been my goal, but scheduling is getting harder. Certainly Puerto Rico is tempting, as is Ottowa. We’ll see how many pop up and where in Q2/3/4 and I’ll work out a schedule.

    I also expect to evolve a couple of my talks and change them around. I’ve usually added a couple talks each year, but this year might be tough. If I get one more done, I think that will be plenty.

    Writing

    Writing has been mostly editorials and small content for the last few years. I’ve written a few articles over the break, and I’m looking to do a few more across the next few months as I delve into new topic areas. It’s a time sink, but I want to be sure that I get some longer technical writing done.

    Lab

    The last thing I need to do is build a better lab. I started setting one up last year and it worked OK, but I had a drive die with a few of the VMs on it (I know, I need backups. See PosH above), and I got busy. It also wasn’t quite the right design, and I was unclear on a few of the AO/AG/FCI items. We’ve had a lot of content this year in those areas, partially with me pushing for it, and after my day at Allan Hirt’s pre-con, I want to rebuild it in a new, better way.

    I also want to make it a better, more replicable setup. We’ll see. This may help with the speaking above, and certainly the other items, so I want to get going on this, preferably thinking about building up a small, portable device with labs.

    Conclusion

    I’m writing this post so that I can go back and evaluate it again at the end of the year. I’ll compare how I did and see where the plan failed, and where it changed. Without a doubt, things will change.

    I do have a reminder every month to go back and look at goals, so I’ll be checking this post each month to see how I’m doing it.

  • Speaking Stats

    I was updating my speaking schedule and CV, trying to get an idea of what might be planned for 2015. As I was doing so, I decided to do a quick count of events and talks to see where I was last year. Last year was tough, and I need to find a way to better balance life and travel in 2015.

    What I found surprised me. While I attended 26 events and gave 46 talks, that was down from the previous year. The numbers are:

    Year Events Talks
    2008 2 2
    2009 2 2
    2010 7 10
    2011 13 21
    2012 17 33
    2013 30 54
    2014 26 45

    I found 2014 to be a hard travel year, with quite a few trips out of town. However I think the main problem was that much of my travel was compressed. I didn’t travel for a few long stretches, including a 6 week stretch when I had surgery, which meant that other travel was compacted into a few short periods. Sept and Oct were particularly hard.

    I was surprised at how few events I’d had in 2010 and prior. I would have thought my load increased more linearly, but it hadn’t. Certainly in 2010, my daughter was still in elementary school, my wife was working full time for a corporation and traveling quite a bit, my middle son was starting middle school, and my oldest was graduating. 2011 freed up some time, and made it easier for me to travel. 2012 was the year when my life left her job, making things much easier for me to schedule trips.

    I suspect 2015 will be more like 2014, though I hope to spread the travel out. I’ll be speaking in at least one new country, which is exciting for me.

  • Powershell – Copy the Latest Backup

    I got an email recently where someone asked me how they can refresh a dev environment with Powershell. I guess I’d written something about this in 2009, though that would have been for testing as Red Gate had already banned me from development on SQLServerCentral by that time.

    I dug around and came up with a few partial scripts and cleaned them up for these posts. This post will look at getting the backup and a later one will examine the restore.

    Finding the Latest Backup

    I’ll assume that you make backups on a known path somewhere. My philosophy is that I want the machines to stand alone as much as possible. That means that I don’t want the source machine (the one making the backup) to be working on refreshing the backup elsewhere. I want a pull system.

    For a high level overview, this process looks like this:

    • Search the backup path for files matching a pattern.
    • Find the most recent one, based on date.
    • Copy that most recent file to another location.

    For the sake of consistency and easy, I want to copy the file with the same destination name every time. That will simplify my restore process, which I could easily then do in T-SQL.

    Let’s examine how to do this. I’ve got a folder with a few backups in it.

    backuplatest

    For my PoSh, I’ll start by setting a variable to the path.

    $backuppath = "D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup"

    Once I have this, I can now look for the files in this path. To do that, I’ll use Get-Children.

    get-childitem -path $backuppath

    This will return to me a list of the files. That’s what I want, but I want to limit the files to a pattern. In this case, I’m looking for .bak files, from the EncryptionPrimer database. All of these

    There’s nothing special about what I do that’s not contained in plenty of places. I don’t have this running on an environment currently as someone else manages that process, but here’s the process I’ve followed in the past:

    • Find the latest backup (whatever the date) in the source folder.
    • Copy this with a set name to the destination folder, overwriting previous backups with the same name.
    • Restore the known name to the development database, moving files as needed.

    I’ll go through each of these steps in my PoSh script.

    Find the Latest Backup

    This is fairly easy. I’ll use the Get-ChildItem method, which I found in a StackOverflow post. I’ll use a variable for the path I need, and then check the path.

    $backuppath = "D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup"

    get-childitem -path $backuppath

    That works well, but since I’m building a process for a specific backup type, I’ll add a filter.

    $backuppath = "D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup"

    get-childitem -path $backuppath -Filter "EncryptionPrimer*.bak"

     

    To find the latest backup, we’ll pipe the output through the Where-object filter, removing folders. Then we use sort-object to order things by creation date and select-object to get just the one file.

    $backuppath = "D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup"

    get-childitem -path $backuppath -Filter "EncryptionPrimer*.bak" |

        where-object { -not $_.PSIsContainer } |

        sort-object -Property $_.CreationTime |

        select-object -last 1

     

    The last part of the script is the copy-item command, which is again the recipient of piped output. We give a standard name, and path (another variable).

    $backuppath = "D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\Backup"

    $destpath = "d:\SQLServer\Backup"

    get-childitem -path $backuppath -Filter "EncryptionPrimer*.bak" |

        where-object { -not $_.PSIsContainer } |

        sort-object -Property $_.CreationTime |

        select-object -last 1 | copy-item -Destination (join-path $destpath "EncryptionPrimer.BAK")

    Once this is done we can restore things. I learned how to do this from PoSh using this post: http://stuart-moore.com/day-11-31-days-sql-server-backup-restore-using-powershell-basic-restore/

    However, since I have a standard backup file name, I’d probably do this in T-SQL and set a job that I can just run anytime. It’s simpler and easier, and since most of the time I’d want to do this from SSMS, a job works well.

    Here’s the PoSh script.

    Import-Module "SQLPS" -DisableNameChecking

    $sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("JollyGreenGiant\SQL2012")

    $BackupFile = "D:\SQLServer\Backup\EncryptionPrimer.BAK"

    #

    #echo ""

    #echo "Databases"

    #echo "———"

    #foreach ( $db in $sqlsvr.Databases) { write-host $db.name }

    echo " "

    echo "Begin Restore"

    echo "============="

    $Restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"

    $Restore.NoRecovery = $false

    $Restore.ReplaceDatabase = $true

    $Restore.Action = "Database"

    $Restore.PercentCompleteNotification = 10

    $BackupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($BackupFile, "File")

    $Restore.Devices.Add($BackupDevice)

    $RestoreDetails = $Restore.ReadBackupHeader($sqlsvr)

    $logicalFileNameList = $Restore.ReadFileList($sqlsvr)

    $Restore.Database = $RestoreDetails.Rows[0]["DatabaseName"]

    foreach($row in $logicalFileNameList) {

        $RestoreDBFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")

        $RestoreDBFile.LogicalFileName = $row["LogicalName"]

        $RestoreDBFile.PhysicalFileName = $row["PhysicalName"]

        $Restore.RelocateFiles.Add($RestoreDBFile)

            }

    $Restore.SqlRestore($sqlsvr)

    write-host ("Completed the Database Restore operation on server for Database " +  $RestoreDetails.Rows[0]["DatabaseName"] + " on server $server")

     

    That’s it. I ran this a few times, and it worked well. A handy script to get the last backup and have it ready in a dev/test environment.

  • A Week Away

    Almost. I’m off this week in Steamboat Springs, enjoying a vacation with the family, but I’ve got one commitment: Database Weekly.

    We rotate the workload, and most everyone else has something this month, so I got stuck with it. I didn’t want to try and mess up anyone else’s schedule, as we’re all on holiday, so I’m going to fit it in.

    It will be short, so don’t be disappointed next weekend.