Allen White

Building Alert-based Transaction Log Backups - Part Three

In this final installment on building alert-based transaction log backups we'll build the alerts that will kick off the backup jobs.

The first thing we need to do is define a class to hold a collection of object names. We need to drop the existing alerts before creating new ones, but if you try to drop an object from within a collection of those objects .Net will throw an error. There's no "enum" function to give us the alerts defined on the server, but we can collect the names of the qualifying alerts from the Alerts collection. Then we can loop through our collection and drop the existing alert objects.

Public Class dbAlert
    Public name As String
    Sub New(ByVal newName As String)
        name = newName
    End Sub
End Class

Then, within the Main routine we can drop the alerts. I've used the word "threshold" at the end of the alert name (yep, Sybase again) to identify the Performance Condition alerts to watch the transaction log "threshold".

        Dim colAlertColl As AlertCollection
        Dim altAlert As Alert
        Dim colAlerts As New Collection
        Dim objAlert As dbAlert

        'Delete existing alerts
        colAlertColl = srvMgmtServer.JobServer.Alerts
        For Each altAlert In colAlertColl
            If Right(altAlert.Name, 9) = "threshold" Then
                If Not (colAlerts.Contains(altAlert.Name)) Then
                    colAlerts.Add(New dbAlert(altAlert.Name), altAlert.Name)
                End If
            End If
        Next
        For Each objAlert In colAlerts
            Dim altDropAlert As Alert
            altDropAlert = srvMgmtServer.JobServer.Alerts(objAlert.name)
            altDropAlert.Drop()
        Next

The last thing we need to do is create the alert. We need to know if we're working with a default or named instance, and we can get that from the InstanceName property of the Server object. If it's empty, then the Performance Condition starts with "SQLServer", otherwise it starts with "MSSQL$" followed by the name of the instance. Add to that the actual performance condition to be monitored, in this case ":Databases|Percent Log Used", plus the database name, then the value to be watched "|>|50". We then create a new Alert object, set the name to be the name of the database concatenated with "_log_threshold", the CategoryName to "[Uncategorized]", the PerformanceConditon to the string we built, and set the job to be initiated to the JobID we created in Part Two. (Note that this code goes right after the "jobDumpJob.Alter()" code from Part Two.) Call the Create method and we're done.

                Dim altThresh As Alert

                'Define the Performance Condition for the Alert
                If srvMgmtServer.InstanceName = "" Then
                    strPerfCond = "SQLServer"
                Else
                    strPerfCond = "MSSQL$" + srvMgmtServer.InstanceName
                End If
                strPerfCond = strPerfCond + "Databases|Percent Log Used|" + strDBName + "|>|50"

                'Define the Alert	
                altThresh = New Alert(srvMgmtServer.JobServer, strDBName + "_log_threshold")
                altThresh.CategoryName = "[Uncategorized]"
                altThresh.PerformanceCondition = strPerfCond
                altThresh.JobID = strJobID
                altThresh.Create()

Once you run this code you'll have automatically triggered transaction log backups whenever the log exceeds 50 percent of its capacity, which means you're less likely to run out of space in your transaction log, which of course means less down time, and that's a good thing, right?

Allen

Building Alert-based Transaction Log Backups - Part Two

In this second part of the post on building alert-based transaction log backups, I'm going to focus on creating the SQL Agent jobs that will call the stored procedure we created in Part One.

Before we create the jobs, though, we need to clean up any existing jobs, so we don't leave any orphans out there. Within SMO at different levels are objects labeled "Enum", all of which return a DataTable object, and they enumerate properties of the object. These are quite useful in exploring your server, your database, or other objects in SQL Server. In this case we're going to load a DataTable with the EnumJobs object within the server's JobServer object. We'll then loop through the rows returned in the DataTable and drop any jobs whose name ends with "log_dump", because that's how we're naming the transaction log backup jobs. (I know, Sybase again.)

        Dim tblServerJobs As DataTable
        Dim rowServerJobs As DataRow

        tblServerJobs = srvMgmtServer.JobServer.EnumJobs
        For Each rowServerJobs In tblServerJobs.Rows
            If Right(rowServerJobs("Name"), 8) = "log_dump" Then
                Dim jobDumpJob As Job
                jobDumpJob = srvMgmtServer.JobServer.Jobs(rowServerJobs("Name"))
                jobDumpJob.Drop()
            End If
        Next

Now that the log_dump jobs have been successfully dropped, we can loop through the databases, creating a new log_dump job for each database we find that's not a system database or a snapshot database. For each database we create a new Job with the name of the database plus the "_log_dump" string so the job is easily identified, define the remaining properties, and create the job. We define a GUID variable called strJobID to capture the internal ID of the job so we can use it in defining the job step, next.

The job step is defined to execute the db_log_dump stored procedure we created in Part One, and we pass as a parameter the name of the database we're currently using, set the remaining properties and create the step. We then grab the StepID (integer) to set the Job.StartStepID to that value. We also need to set the Job.ApplyToTargetServer value to the name of the server we're running against, then alter the job. Here's the code:

        Dim dbcDatabases As DatabaseCollection
        Dim dbDatabase As Database

        'Loop through the non-System databases to create the backup jobs and performance alerts
        dbcDatabases = srvMgmtServer.Databases
        For Each dbDatabase In dbcDatabases
            If (Not dbDatabase.IsSystemObject) And (Not dbDatabase.IsDatabaseSnapshot) Then
                Dim strDBName As String
                Dim strJobID As Guid
                Dim jobDumpJob As Job
                Dim jbsDumpJobStep As JobStep
                Dim intStepID As Integer

                strDBName = dbDatabase.Name
                jobDumpJob = New Job(srvMgmtServer.JobServer, strDBName + "_log_dump")
                jobDumpJob.Description = "Threshold Backup for Database " + strDBName
                jobDumpJob.Category = "[Uncategorized (Local)]"
                jobDumpJob.OwnerLoginName = "sa"
                jobDumpJob.Create()
                strJobID = jobDumpJob.JobID

                jbsDumpJobStep = New JobStep(jobDumpJob, "Step 1")
                jbsDumpJobStep.DatabaseName = "msdb"
                jbsDumpJobStep.Command = "exec db_log_dump '" + strDBName + "'"
                jbsDumpJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
                jbsDumpJobStep.OnFailAction = StepCompletionAction.QuitWithFailure
                jbsDumpJobStep.Create()
                intStepID = jbsDumpJobStep.ID

                jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
                jobDumpJob.StartStepID = intStepID
                jobDumpJob.Alter()

            End If
        Next

In the last post we'll add the code to set up the performance condition alert.

Allen

Building Alert-based Transaction Log Backups - Part One

This next post is going to be in three parts, because it touches on three different areas within SMO.

In my production environment I have maintenance plans set up to backup the databases every night, and backup the transaction logs every hour. That handles most of the transaction load so I'm fairly safe from data loss. There are times, though, when a high amount of activity will cause the transaction log to expand dramatically between hourly backups, so I set up an alert to watch for Percent Log Used value for each database and, if it exceeds 50 percent, automatically kick off a transaction log backup for that database.

This is handled through a stored procedure, which is passed the name of the database to be backed up, a SQL Server Agent job, which is triggered by a Performance Condition Alert. In this post I'll describe and share the code that creates the stored procedure which performs the backup.

The first thing we'll do is connect to the server, and grab the location of the backup directory from the Server.Settings object.

        Dim strBackupDir As String
        Dim dbDatabase As Database

        ' Connect to the server
        Dim srvMgmtServer As Server
        srvMgmtServer = New Server("MyServer")
        Dim srvConn As ServerConnection
        srvConn = srvMgmtServer.ConnectionContext
        srvConn.LoginSecure = True
        strBackupDir = srvMgmtServer.Settings.BackupDirectory

Next, we need to attach to a database and build the stored procedure framework. I've chosen to place the procedure into the msdb database because 1) it's not the master database, and 2) because I know it exists on every instance of SQL Server. I haven't had any ill effects of this decision, but please leave me comments if this would be considered outside "best practices".

Once I've connected to the database I create a StoredProcedure object and name it 'db_log_dump'. I'm showing my Sybase roots here, because that's what we called them pre-SQL Server 7. We also need to add a parameter to the stored procedure for the name of the database to be backed up.

        Dim spStoredProc As StoredProcedure
        Dim prmDBName As StoredProcedureParameter
        Dim strSPText As String

        dbDatabase = srvMgmtServer.Databases("msdb")
        spStoredProc = dbDatabase.StoredProcedures("db_log_dump")
        If Not (spStoredProc Is Nothing) Then
            spStoredProc.Drop()
        End If
        spStoredProc = New StoredProcedure(dbDatabase, "db_log_dump")
        spStoredProc.TextMode = False
        spStoredProc.AnsiNullsStatus = False
        spStoredProc.QuotedIdentifierStatus = False
        prmDBName = New StoredProcedureParameter(spStoredProc, "@database", DataType.VarChar(50))
        spStoredProc.Parameters.Add(prmDBName)

Note that we check to see if the db_log_dump stored procedure already exists, and drop it if it does, just to make sure we don't run into an error.

Lastly, we build the text of the stored procedure. I've declared two variables, one for the backup device name, and the other for the string holding the date and time value to be part of the log backup file name. The date value will hold the date and time (to the second) that the backup was initiated. Once the date string is built, then it's concatenated to the backup directory, the database name and the '_tlog_' designator to let me know it's a log backup. Tack the '.TRN' extension on and we've got the full pathname of the backup file to pass to the BACKUP command.

        strSPText = "declare @strbackup varchar(500),  @strDate as varchar(30)" + vbCrLf
        strSPText = strSPText + "set @strDate = CONVERT(varchar, getdate() , 112)" + vbCrLf
        strSPText = strSPText + "set @strDate = @strDate + Left(CONVERT(varchar, getdate() , 108),2)" + vbCrLf
        strSPText = strSPText + "set @strDate = @strDate + SubString(CONVERT(varchar, getdate() , 108),4,2)" + vbCrLf
        strSPText = strSPText + "set @strDate = @strDate + SubString(CONVERT(varchar, getdate() , 108),7,2)" + vbCrLf
        strSPText = strSPText + "set @strbackup = '" + strBackupDir + "' + '\' + @database + '_tlog_' + @strDate + '.TRN'" + vbCrLf
        strSPText = strSPText + "BACKUP log @database to disk = @strbackup" + vbCrLf
        spStoredProc.TextBody = strSPText
        spStoredProc.Create()

At this point the stored procedure exists in msdb, to be called by the SQL Agent job we'll create in my next post.

Allen

Programmatically migrating to the Unified Large Object Programming Model

Sorry about the long time since my last post. I've had a bunch of travel and project work preventing me from discovering new features of SMO to share. I hope I'm back on track now.

In any event, I'm attempting to take advantage of some of the features of SQL Server in our existing applications, and one of my favorite features is the Unified Large Object Programming Model. That's a fancy way of describing the (MAX) length value for varchar and varbinary data types.

So, to change datatypes in existing databases to use the MAX length types I built a small SMO program, so I don't have to browse all the tables to determine where the change is appropriate. I decided on an arbitrary value of (1000) - any datatype defined with a length of 1000 or greater will get converted to (MAX). You may want to do things differently, but this helps me show how easy it is to change the datatype using SMO.

After connecting to the server (you've seen that code in my blogs before) I connect to the database in question. I could have looped through the collection, but in this case I targeted a specific database. Once I determine that the database has a compatibility level of 9.0 (SQL Server 2005), I then grab the table collection and loop through that one table at a time. Then I grab the column collection and loop through the columns. Then, for each column I check the column's DataType.MaximumLength value. If it's greater than 999, regardless of data type, it qualifies for my change. By setting the DataType.MaximumLength value to -1 it effectively changes the column to the MAX length. I then set a flag indicating the table's been changed, and after evaluating all columns for the table, if the table has been changed I call the Alter method on the table.

Here's the code:

        Dim dbExpand As Database
        Dim tbcTables As TableCollection
        Dim tbTarget As Table

        ' Connect to the server

        dbExpand = srvMgmtServer.Databases("DemoDatabase")
        If dbExpand.CompatibilityLevel = CompatibilityLevel.Version90 Then      ' Database must be SQL 2005 level
            tbcTables = dbExpand.Tables
            For Each tbTarget In tbcTables
                Dim colcColumns As ColumnCollection
                Dim colTarget As Column
                Dim boolTableChgd As Boolean

                boolTableChgd = False

                colcColumns = tbTarget.Columns
                For Each colTarget In colcColumns
                    If colTarget.DataType.MaximumLength > 999 Then
                        colTarget.DataType.MaximumLength = -1
                        boolTableChgd = True
                    End If
                Next

                If boolTableChgd = True Then
                    tbTarget.Alter()
                End If
            Next
        End If

Once that's been done I no longer have to worry about applications that don't check the length of incoming data causing the loss of data.

Allen