Allen White

Thanks for a Great 2006, and Happy New Year for 2007

This has been one heck of a year for me. I earned MCITP: Database Administrator, MCITP: Database Developer, MCTS: SQL Server 2005, and Microsoft Certified Trainer certifications, had an article published at SQLServerCentral.com, presented at the Ohio North SQL Server User's Group three times and at the 2006 PASS Community Summit as well. I'm hoping for more successes in 2007.

I hope this new year brings you and those you care for everything you hope for as well.

Allen

Improving SMO Performance - Two Hours down to Six Minutes

I've finalized the programs I built to automatically rebuild my database maintenance jobs. One of the programs rebuilds the database integrity checks and optimizations jobs, and the other rebuilds the backup jobs. The process of rebuilding the optimizations jobs involves issuing this command for every index in every database:

ALTER INDEX [PKUser_Table] ON [dbo].[User_Table] REORGANIZE WITH ( LOB_COMPACTION = ON )

Of course, this means iterating through every database, and every table, and every index. The code was fairly simple to do this:

        Dim objDBColl As DatabaseCollection
        Dim dbDatabase As Database
        Dim objTableColl As TableCollection
        Dim objTable As Table
        Dim objIndexColl As IndexCollection
        Dim objIndex As Index
        Dim objViewColl As ViewCollection
        Dim objView As View

        objDBColl = srv.Databases
        For Each dbDatabase In objDBColl
            objTableColl = dbDatabase.Tables
            For Each objTable In objTableColl
                If objTable.IsSystemObject = False Then
                    objIndexColl = objTable.Indexes
                    For Each objIndex In objIndexColl
                        Console.WriteLine("ALTER INDEX [" + objIndex.Name + "] ON [" + objTable.Schema + "].[" + objTable.Name + "] REORGANIZE WITH ( LOB_COMPACTION = ON )" + CStr(Now))
                    Next
                End If
            Next
            objViewColl = dbDatabase.Views
            For Each objView In objViewColl
                If objView.IsSystemObject = False Then
                    objIndexColl = objView.Indexes
                    For Each objIndex In objIndexColl
                        Console.WriteLine("ALTER INDEX [" + objView.Name + "] ON [" + objView.Schema + "].[" + objView.Name + "] REORGANIZE WITH ( LOB_COMPACTION = ON )" + CStr(Now))
                    Next
                End If
            Next
        Next

Of course, the problem is that on one of my servers I have almost 100 databases, so this iterative process took over two hours! I've seen a number of people complain about the performance of SMO but I felt that there was something I could do to change it. There was, in the form of the SetDefaultInitFields method of the server object. Prior to iterating through the databases and views I added the following code:

        srv.SetDefaultInitFields(GetType(Table), "IsSystemObject")
        srv.SetDefaultInitFields(GetType(View), "IsSystemObject")

The application went from over two hours down to just over six minutes! Let's just say that two hours was unacceptable, but six minutes is "well within expected operating parameters."

Allen

Interviewing Developer Candidates

We've been looking for a new lead developer at my company, and as the SQL Server DBA I get to take part in the interview process. I both love and hate the process and sometimes I'm as nervous as the candidate, because I know that how they answer my questions will affect their life, and that's some serious karma.

I found what I think are a great set of questions at Tech Republic and have been using these with the candidates. What is disturbing me is that two of the three candidates I've interviewed so far didn't know what the difference was between a clustered and non-clustered index. I've even gotten comments back that my questions were too hard, and that the candidates were interviewing for a developer position, not a DBA position.

Now, if we were a shop which had a data architecture team and the developers used the data structures designed by that team I wouldn't be so concerned, but I've been told more than once that the developers are fully qualified to create the necessary data structures to solve their project objectives. As a result, I expect the lead developer to know the most fundamental archectural aspect of SQL Server before starting a project.

Maybe I'm wrong, but I don't think so.

Allen

Followup on the SMO MailServer Bug

I received an interesting response from the feedback I sent Microsoft yesterday on the SMO MailServer bug. They closed the bug with a (Postponed) note on it, and included the following comments:

Thanks for reporting your problems configuring mail servers programatically. Additional thanks for sharing your workaround code.

I have submitted this issue for a KB article to assist those that find themselves in the same difficulties as you did. Additionally, I have included this item in a list of those we hope to address as part of a feature completeness push for the next release of sql server.

So, the workaround I posted yesterday is the way to handle this particular problem.

Allen

Creating Database Mail Accounts in SMO and Working Around the MailServer Bug

I'm working on automating all my server maintenance processes, and decided that I'd build an application to build my maintenance processes that can be run any time, and it would build the jobs correctly based on the existing databases. When I set up a new server I just have to run the program and my standard maintenance jobs are automatically built.

The first thing I needed to do was set up Database Mail consistently on all the servers so I'm notified if any problems occur in any of the jobs. SMO provides objects for that so it's logical to use SMO to build an application to create the Database Mail account and profile needed for the notifications.

First we need to add the Smo.Mail namespace to the application:

Imports Microsoft.SqlServer.Management.Smo.Mail

Next, we need to connect to the server, define our Database Mail objects, and if the account we want to use already exists, drop it, so we know we're creating it based on our current standards.

        ' Connect to the server
        Dim srvMgmtServer As Server
        srvMgmtServer = New Server("MyServer")
        Dim srvConn As ServerConnection
        srvConn = srvMgmtServer.ConnectionContext
        srvConn.LoginSecure = True
 
        Dim mscMailSrvColl As MailServerCollection
        Dim msMailSrv As MailServer
        Dim objMail As SqlMail
        Dim objMailAcct As MailAccount
 
        objMail = srvMgmtServer.Mail
        objMailAcct = objMail.Accounts("sqldba")
        If Not objMailAcct Is Nothing Then
            objMailAcct.Drop()
        End If

Now we want to create the MailAccount object.

        objMailAcct = New MailAccount(objMail, "sqldba")
        objMailAcct.Description = "Database Administrator Email"
        objMailAcct.DisplayName = "Database Administrator"
        objMailAcct.EmailAddress = "sqldba@example.com"
        objMailAcct.ReplyToAddress = "sqldba@example.com"

At this point we should be able to create a new MailServer object to add to the MailServers collection of the MailAccount object, but there's a design bug in SMO and they left out the New() method for the MailServer object. I exchanged a couple of emails with Michiel Wories about this, and he filed a bug after verifying the bug. I submitted Feedback on the bug, but figured out a workaround. If I just execute the Create() method at this point, a MailServer object will be created in the collection, using the current server name as the default name of the MailServer object. By using the Rename() method I was able to set the MailServer object to the SMTP server I wanted to use. Here's the code:

        objMailAcct.Create()
 
        mscMailSrvColl = objMailAcct.MailServers
 
        msMailSrv = mscMailSrvColl.Item(0)
        msMailSrv.Rename("smtpsrv.example.com")
        msMailSrv.Port = 25
        msMailSrv.EnableSsl = False
        msMailSrv.UserName = ""
        msMailSrv.Alter()
        objMailAcct.Alter()

The last thing I need to do is to set up a profile. The sp_send_dbmail stored procedure takes a profile name as its way of connecting with a mail server, so we need to create the profile. Here's the code for that:

        objMailProfileColl = objMail.Profiles
        objMailProfile = objMailProfileColl.Item("DBAMail")
        If Not objMailProfile Is Nothing Then
            objMailProfile.Drop()
        End If
 
        objMailProfile = New MailProfile(objMail, "DBAMail", "Database Administrator Mail Profile")
        objMailProfile.Create()
        objMailProfile.AddAccount("sqldba", 1)
        objMailProfile.Alter()

In another post I'll share the code that creates the maintenance jobs. If you get a chance, check out the Feedback using the link I provided and vote for it so it'll be addressed in the next Service Pack.

Allen

Sarbanes-Oxley and Data Dude

I've been a bit buried lately with Sarbanes-Oxley requirements. In the last couple of weeks I've written new company policies on Data Integrity and Whistleblowing, as part of the effort to get everything documented by December 15.

One of the other requirements was to get all our production databases documented. This can be a daunting task, but fortunately the new Visual Studio Team System for Database Professionals (Data Dude) came out. I spent two solid days last week pointing Data Dude to each of my production databases and created a Database Project for each one. Because the database project is coded as a set of .sql files it qualifies as valid documentation under our Sarbanes-Oxley guidelines. I added all these projects to our source control system and that requirement is satisfied, and so am I.

Thanks to everybody on the Data Dude team for making my job easier!

Allen