Tuesday, July 24, 2012

SQL Saturday 122: T-SQL 2012 presentation

This last weekend, I had the pleasure of being invited down to Louisville, KY to speak at SQL Saturday #122. The volunteers there chose to have me speak on some of the new features of SQL Server 2012 T-SQL.

I barely fit everything in the session – finishing up just as time was over. I was unable to give the windowing functions the time they deserved as one of the coolest features of T-SQL 2012. However, most of the demos worked and it seemed that the audience understood what I was trying to communicate and participated quite well. I had a blast hanging out with Jack Corbett (@unclebiguns), Eddie Wuerch (@EddieW), Craig Purnell (@CraigPurnell) and several other SQL Server experts.

I’ve uploaded the script files to the SQL Saturday website if you want to go through any of the demos for yourself.

Please feel free to hit me up with questions both here and on Twitter.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek,
IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Sunday, July 8, 2012

Speaking at SQL Saturday 122 - Louisville

It has been a great couple of months for both me and for Perpetual Technologies (my employer). PTI has been able to sponsor several SQL Saturday events and we have had several members of the Microsoft team speak at numerous events.

I've been granted the pleasure to speak at SQL Saturday 122 in Louisville - just a couple hours south of home. I'll be speaking on some of the new features in SQL Server 2012 that relate to T-SQL. I'll be touching on some of the new .NET functions that are now built in to the engine, new ways to do error control, and wrapping up with some of the awesome windows functions introduced in T-SQL 2012. You can read the abstract here.

Get signed up here and get the opportunity to share, network, and learn from some local and national experts on SQL Server topics. It's only a couple weeks away - do not pass go, do not collect $200. Sign up today!

Look forward to seeing you there!

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Thursday, July 5, 2012

Powershell Add-Member: IsLogBackupAllowed ?

In my previous post, I shared some of the issues I had when attempting to get Add-Member to work with the SMO Database object. In this post, I’ll share some of the fruits of that experience as I show how to quickly determine if a database can have its log backed up through Powershell and SMO.

That’s Easy – just check the recovery model.

I could just say – yup – blog post done. However, it’s not. Consider the following code:
$instancename = ".\SQL2008R2"
$dbname = "TestFullRecovery"

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server ("$InstanceName")
$Backup = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Backup

$db = $srv.Databases["$dbname"]

if($db.RecoveryModel -eq "Full")
{
    #Take log backup of database only if the database is in full recovery        
    $Backup.Database = $db.name
    $Backup.Action = "Log"
    $Backup.Devices.AddDevice("C:\BAK\TestFullRecovery_log.trn", "File")
    try{$Backup.SqlBackup($srv)}
    catch
    {
        throw $_.Exception.GetBaseException()
    }
}
If you run this on a database that is in Full recovery mode but has not had a full backup, the following error will be thrown:

BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.

This is because the database is in what is known as “pseudo-SIMPLE” mode. Paul Randal describes this issue here as he offers a T-SQL function to determine whether or not a database is really in Full Recovery mode. This can happen if a new database is created and not backed up yet or if the database has recently been switched into Full recovery mode without a subsequent full backup. If you are running in an environment where the application generates new databases like mushrooms, this can become an important issue to resolve.

One option if you are using SMO and Powershell for backups is that you could just catch the error and ignore if the error message is identical to that described above. I do not generally like relying on this type of exception handling – why knowingly execute code that will fail. Let's look at a more proactive solution.

Adding the IsLogBackupAllowed Property

Using Add-Member, we can add a new boolean property to the database object. In this case - instead of adding it as a literal value with the NoteProperty MemberType, the ScriptProperty MemberType allows us to create a property that can be dynamic at run time. Using the special variable "$this" within the construction of the property allows the value to be determined based upon other properties that already exist on the object as well as executing methods of the object to which this new property belongs.

I decided to use the name of “IsLogBackupAllowed” for this new property. This name seemed to fit in well with the other boolean properties of the database object like IsDatabaseSnapshot or IsMirroringEnabled. Leveraging the –PassThru parameter of Add-Member, we can create a simple function that will bring the functionality that Paul Randal created in T-SQL into Powershell. When referencing this function - it will return back the object you put into it with the addition of the IsLogBackupAllowed property.
$instancename = ".\SQL2008R2"

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server ("$InstanceName")

function Add-DBMembers 
{
    param(
        [Microsoft.SqlServer.Management.Smo.Database]$Database
    )
    
    $db | Add-Member -Name IsLogBackupAllowed -MemberType ScriptProperty `
        -Value {      
      if($this.Parent.Version.Major -ge 9)
      {
            if(([Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full,
                [Microsoft.SqlServer.Management.Smo.RecoveryModel]::BulkLogged)`
                  -contains $this.RecoveryModel)
            {
            if($this.ExecuteWithResults(
                  [string]::Format("SELECT last_log_backup_lsn 
                          FROM sys.database_recovery_status
                        WHERE database_id = {0}", $this.ID)
                        ).Tables[0].Rows[0].last_log_backup_lsn.GetType() -ne `
                            [System.DBNull])
                  {
                        $true
                  }else{$false}
            }else{$false}
      }else{$null}
    } -PassThru
    
}
$db = $srv.Databases['TestFullRecovery']

$db = Add-DBMembers -Database $db

$db.IsLogBackupAllowed
The function adds a ScriptProperty of IsLogBackupAllowed to a database object that is provided as a parameter. To determine whether the transaction log backup is allowed, the script first determines if the RecoveryModel property of the database is either Full or BulkLogged. If the database is in Simple recovery mode, there is no chance of getting a log backup. Once it is determined that the database could potentially have a log backup taken, the ExecuteWithResults method is implemented to use the same select from Paul’s post to determine the ability of backing up the log. If anything other than “DBNull” is returned for the column status, a value of True is sent to the caller. If the instance is not SQL 2005 or higher, this property returns $null . SQL 2000 does not make this metadata readily available.

But wait – there’s more!

If you’re asking yourself, That’s fine and dandy, but all the work I’m doing in the Add-Member could be done inline when taking log backups.  What's with all the additional work of making it a property? I'll return your question with a question.

What if the Add-Member step was not necessary? What if you could just reference $db.IsLogBackupAllowed in your code? In the next post, I’ll be showing you how this can be accomplished using the Types.ps1xml files as well as showing how to extend various SMO objects with properties that I have found to be handy in routine maintenance tasks.

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter