VSTSC's Community Server

Yup, you read the title right.  My blog is going to be moving.  For a number of different reasons it's time for me to strike out on my own, so I'm following Brent Ozar's excellent How To Start a Blog series to make that migration happen.  One of those reasons is that my wife has given me a project of migrating her ftp-hosted Blogger blog over to WordPress.  She's got scores of readers and I'd like to make that transition as seamless as possible for them.  And, it's against my nature to make changes to production without having done some sort of test, so my migration of my own blog to WordPress will be a good test run that, while not identical, will give me some basis of understanding for when I take the leap with her blog.

 I've also known that I have to move this year, and having this imminent change hanging over me was just another impediment to sitting down and getting my thoughts out, and getting to work on all of the other home tech projects I want to do this year.  It's time for action, I sez.

I'll make a more official announcement once I'm ready to declare my new home, but it shouldn't be to hard to find the new home if you'd like to watch the messy metamorphosis. 

Time to make my professional goals again for the new year.  In conjunction with listing my new goals, I'll also talk about some of the goals for last year and how I fared.

Certification:  Pass exam 70-453 in order to earn my MCITP: Database Administrator 2008.  Bonus Goal:  Pick another certification and take at least one exam for it.  Server Admin?  BI Developer?  Database Developer??

Last year:  I reached my goal of getting my MCITP: Database Administrator certification.

Blogging:  Make at least one blog post a week.  Move blog to a new site.

Last year:  Didn't quite make the one blog post a week.  I'm hoping to do better this year, starting now.  It's only 52 posts, right?

Reading:  One technology book a month.

Last year:  Man, didn't hit this one either.  Uh oh, I don't like where this review of last year is headed...

Technology:  Focus on learning Powershell, SSIS, and SSAS.

Last year:  Undefineable goal, but my early enthusiasm for Powershell really kind of died out as I didn't have any places to apply it at work.  2010 is starting out very differently, and I've already developed some automation scripts with Powershell for work, and I'm starting to recommend it for certain things to our developers as well.

Scripting:  Write 1 script a week for my toolbox.

Last year:  Tried this goal, and, by my count, I ended up with 74 new scripts, in both T-SQL and Powershell, that make my job easier.  Hurray!  I got one.

Learning: Attend 6 of the monthly local SQL Server user group meetings this year.  Attend PASS Summit.

Last year:  Yup, I did manage to attend my goal of 1 user group meeting last year, and I also attended the PASS Summit.

Volunteer:  Donate some of my time to PASS, or to my local SQL Server User Group.  I'm already slated to volunteer at SQL Saturday 31 in Chicago.

Last year:  Didn't make it a goal, so this one's a new one.

Recently when I tried to access Reporting Services' Report Manager for a SQL Server 2008 Developer Edition SP1 instance on a Windows 2008 R2 Server, I got a completely blank screen.  It was not a mostly blank screen still showing the header, nor was I getting the blank page after being prompted for my user credentials three times.  No, the page was loading immediately and was completely blank.  I checked the Windows logs, the SQL Server logs, and the Reporting Services log, and no errors were being logged.

Fortunately I knew that the security department had recently made changes to that instance to enable Kerberos authentication in order to support Kerberos delegation for a web app that connected to our SQL Server and Reporting Services.  They'd dutifully documented all of the changes, and so since I didn't have any error logs to go on, I knew this was the only change that had happened recently and likely had to be the culprit.

We used the same domain account to run both the SQL Server service and the Reporting Services in order to make our lives a tad bit easier, and I double checked that all of the SPNs were registered properly for the web server, SQL Server, and Reporting Services.  Figuring it had to be something related to the Reporting Services configuration, I noticed that they'd changed a setting in the RSReportServer.config file, located at C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer in a default install, and noticed they'd changed <AuthenticationTypes> to <RSWindowsKerberos/>.  I wanted to accept Kerberos or NTLM tokens, so BOL pointed me in the direction of using <RSWindowsNegotiate/>.  But, despite BOL suggesting that this setting would be omitted if I were running in native mode and using a domain user for the service, I caved to my rebellious streak, removed <RSWindowsKerberos/>, replaced it with <RSWindowsNegotiate/> and hoped for the best.  It worked!  The web app continued to work via Kerberos authentication/delegation, and I was able to access my Report Manager again.

I'm sure there's a moral to this story somewhere.  Mainly, though, I'm just very pleased that the security department and the consultant they had helping them were so diligent in documenting their changes.  I probably owe them a cookie or something.

In the age of SQL injection attacks, the dangers of dynamic SQL are pretty well documented.  But, as with most things, there's a time and a place for them.  I recently indulged in a trip down memory lane, reviewing the scripts in my 'toolbox' that I use for administration, and I was reminded of how much I use dynamic SQL for my day-to-day administrative tasks.  For many, this blog post isn't going to break any new ground.  Consider it an homage to my favorite aspect of dynamic sql in t-sql; the ability to do administrative tasks en masse.

For example, at work we use SQL Agent extensively for plenty of administrative tasks.  In those rare instance when one of those jobs should break, we like to know what happened in as much detail as possible.  So, we use SQL Agent's step history to log that information to a text file and email the log file to us.  But, when I'm building a large job, going into the Advanced page of the Job Step properties through SSMS and defining the output file for each step can get rather tedious.

 Enter dynamic SQL.  After I've built the job, I open up a Query window, and build a quick script like this:

DECLARE @sql varchar(max);

SET @sql = '';

SELECT @sql = @sql + 'EXEC msdb.dbo.sp_update_jobstep @job_id='''+CAST(job.job_id AS varchar(max))+''',@step_id='+CAST(step.step_id as varchar(10))+',@output_file_name=''G:\JobLogs\<jobname>.log'',@flags=6'
FROM msdb.dbo.sysjobs AS job
    JOIN msdb.dbo.sysjobsteps AS step
        ON job.job_id=step.job_id
WHERE job.name = '<jobname>'
AND step.step_id > 1;

 EXEC(@sql);

 As you can see, using the information from the SQL Agent system tables stored in msdb, I build a variable to string together executions of sp_update_jobstep in order to update all of the steps in my job, except the first one, to use the same log file, to append the step's history to that log file, and to also include the step information into SQL Agent's history, and then I execute that statement, which modifies all of the steps at the same time.

In jobs that have 10s, if not a 100 steps, I've saved myself quite a bit of time from having to go through the GUI.

Now,this certainly isn't the only way I could have done this task.  I could also have scripted out the job through SSMS, done a Find/Replace to change the values for @flags and @output_file_name.  but, since I'm weird and I also want my first step to NOT append to the log file because I want the file to be 'new' every time the job executes, I'd have to then go back and search through the code to find the first step and change the @flags value.  Then, in SQL Server 2005, I'd have to drop the job and run the script to recreate.  But, no, I think I like dynamic SQL just fine.

"I suppose you could say that everyone has an El Guapo."  Lucky Day, The Three Amigos

 As the title of my blog post says, I've got an El Guapo, a nemesis, an antagonist.  It's a mental monkey trap.  I cannot seem to figure out how the 'Limit size of job history log' setting for the SQL Server Agent would be useful to me in its current incarnation, which hasn't changed since at least SQL Server 2000.  In this History setting found in the SQL Server Agent properties, you can set the maximum job history log size in rows, which defaults to 1000, and you can also set the maximum job history rows per job, which defaults to 100.

 We use SQL Agent extensively...very extensively.  On some of our servers we have hundreds of jobs that handle everything from database and server maintenance to data loads.  While these jobs all follow a basic form, they have a variable number of steps depending on what the job is intended to do.  Our largest job has upwards of 100 steps by itself.  Additionally, these jobs run with a certain frequency, be it daily, weekly, monthly, or whatever other frequency we can concoct.

 And so, while we find the 'Automatically remove agent history' option very useful, I cannot seem to come up with a scenario where setting job history limits in terms of rows would be useful other than as a last protection from a SQL Agent job run amok.

 Any ideas?

Recently I was tagged by Jeremiah Peschka with a question that goes a little something like this:

 "So You’re On A Deserted Island With WiFi and you’re still on the clock at work. Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?"

By comparison of an impromptu poll at some webcast the other month which I don't remember, where I work is towards the low end of # of servers/DBA.  Still, for numerous reasons, we don't have any 3rd party tools that help automate the monitoring of our SQL Servers, so alot of what we do is a combination of automation that we've built ourselves, manual monitoring when necessary, and prioritizing the machines that need the highest level of scrutiny.

If I had this mythical month, I'd reinforce my monitoring web along a 3-part strategy, making every effort to use it as an opportunity to learn new technologies or skills.

Alerts

When something goes horribly wrong, the DBAs need to know about it right away, and preferably in advance of the users.  All of our servers should notify us directly of any catastrophic errors, and if applications have database functionality that requires high availability, these components should notify the DBAs when they break.  I'm happy to say that I'd have very little work to do in this area.

Reports

 In this arena, we have a fair number of gaps, and I could easily see myself spending quite a bit of time in my 'free' month shoring up our reports and initiating the collection of metrics for historical analysis.  I like reports for a number of reasons.  They provide another line of defense for alerts and help identify any alerting components that haven't been configured properly.  They help us identify the beginnings of issues so that we can intervene on them before they become alerts.  They help us know our systems better.  But, the type of reporting I'm referring to here is really only real-time, or over a window of time no longer than a month, and most likely just within the last 24 hours.

The chief technologies I'd leverage for this aspect of monitoring would be PowerShell and SQL Server Reporting Services.  I like PowerShell because of the ease with which I can use it to connect to many different sources of information, like the OS, hardware, SQL Server instances, AD, etc., and I like Reporting Services for the presentation layer to make the data I collect via T-SQL and PowerShell look pretty.

Analysis and Forecasting

 In this final layer, I'd start storing the data I'm gathering from my real-time or near real-time reports so that I can make more accurate long term projections about disk space needs, resource usage, user connections, and peak-use times, to name a few.  And, I'd be able to analyze historical trends to confirm or reject the notions that we currently have about our systems.

 Uhh....

 I know that to some of you the monitoring I've described above are things that you already have in place and couldn't live without.  In fact, you may think that not having these things in place borders on criminally negligent.  Well, for us, most of the systems that use SQL Server are secondary systems, at best.  Yes, they are still important, but most of them could tolerate outages of a day or more without adversely impacting the business.

To keep the question alive, I'd like to tag Stuart over at codegumbo, Trevor Barkhouse, who helped me immensely with a tricky PowerShell scripting question recently, and Chad Miller, who continues to lead the way on using PowerShell with SQL Server.

Sometimes Books Online really ruffles my feathers, and this is one of those times.  We have a separate security department that handles our IT security.  They'd like to have their accounts be able to just administer the security on our SQL Server boxes, and not have to worry about the tremendous responsibility of having sysadmin and the ability to do...well, everything.  So, having read up on fixed server roles in SQL Server 2005 recently, I said 'What about securityadmin?'  They said they'd tried it, but couldn't seem to do what they needed to be able to do, but they couldn't remember the specifics about what it was they weren't able to do.

 So, I went back to BOL and checked the entry on securityadmin to make sure I wasn't imagining things when I'd read that security admin could "also GRANT, DENY, and REVOKE database-level permissions".  Awesome, that sounded pretty close to what i needed.  So, I fired up my test SQL Server 2005 environment, created a securityadmin login, and then logged into the Object Explorer as my test securityadmin user.  I navigated to one of my test databases and tried to expand it and was met with the decidely unpleasant "The database X is not accessible (ObjectExplorer)" error.

 Not so easily defeated, I started a query, and tried to navigate to my test database.  I could select it from the dropdown list, but I always ended up back in master.  Yuk.  Attempting to USE X ended up with the "The server principal "<name>" is not able to access the database "X" under the current security context" error.

 OK, fine.  I'll give my fine securityadmin login a user in the database, and then surely it will be able to exercise it's vaunted ability to GRANT, DENY, and REVOKE database-level permissions; and that's just what I did, and it was able to do so.

You might be asking yourself, "What's Tim's beef?"  Simply this, while the BOL entry is technically correct, to me it seems a little absurd that a login would have certain permissions at the database-level even while they have no permissions to connect to any database.  And, well, perhaps my own belief about what a fixed-server role called 'securityadmin' should be able to do on a server colored my opinion.  I mean, shouldn't the role be able to administer security entirely on the server?  Should it not only be able to create logins and grant them server-level permissions, but also be able create users in any database and grant them database-level permissions?

So, I fear the security administrators will just have to carry the yoke of awesome responsibility that we DBAs must also bear.

Here I am, about 5 months into blogging in 2009, and I find myself unsatisfied with the frequency with which I update this blog.  Sure, there is some content with which I am well pleased, but I just can't seem to understand why I can barely reach the 1 post-a-week goal I had set for myself back in January.  What gives?

 So, over the past 2 weeks I started to seriously think about the entire process I use to decide whether to blog about a subject or not, and I realized that I ask myself a number of questions whenever an idea for a blog post floats through my grey matter.  The questions go something like this:

Has it been blogged already?  If I google the subject, will I find the answer out there already?  Am I contributing to the discussion in any way, or just adding to internet detritus?

Opinions are like....   Everyone's got an opinion.  What makes mine so special?  If I can't come up with an angle, I won't post it.

 Would I want to read about that?  If I were a follower of my own blog, would I want to read what I'd posted?

And I find that, while on any given day I can usually come up with 3 or 4 ideas that might make a passable blog post, most of them never make the cut because they can't make it through the preliminary examination questions.  I'm not terribly satisfied by that result.

Yes, the internet is one giant data store, but even those of us with the greatest of google-fu know that it is sometimes very difficult to find an answer to a particular question.  And, if you'd bear with my poetic license for a moment, data and information isn't just a reservoir we go to when we need something, it's the stuff we swim through.  It's the conversation I had today with my wife about carbohydrates, it's the facebook message I got from an friend about a place we used to both work, it's the question someone asked on Twitter about SQL.  And so, dealing with data is both a combination of looking for it, and it finding us when we least expect it.  When I self-limit what I blog about, am I preventing data from finding someone?

 So, as a warning to my kind readers, I think I'm going to experiment for a month by NOT asking myself those little questions about whether now is a time to blog, and see if that gets me to a happier blogging place.  Pardon the dust.