SQLServerTimes2

A couple of weeks ago, I was saddened to learn that someone that I had worked with many years ago when I was a sales associate at Foley’s Department Store, one of the first jobs that I had worked while going to college, had passed away of bladder cancer.  When I started at Foley’s, Ralph was an older gentleman who had previously taken early retirement from a middle management position back East and had decided after moving to Tucson that he was not ready to be completely retired.  So, he embarked on a second career at Foley’s and I was fortunate to have him be the one to show me the ropes when I started my position there.  Ralph was a pretty amazing fellow in that he always saw the bigger picture.  Thus, when we he went to train me to be a sales associate, he took time to not only train me on what I needed to know to do the job at hand, he also took it upon himself to impart wisdom that he had learned over the years that he thought would be useful to me way beyond this sales position, which he knew I wasn’t planning to make my career.

 

As I reflected back on my friendship with Ralph after I had learned of his passing, one piece of advice that he had given me came to mind.  This was a piece of advice that I had used constantly throughout the 25 years since I worked at Foley’s and it was Ralph’s mantra.  The advice was “plan your work and work your plan.”  Oh, how simple that advice seemed at the time it was given to me, but that simplicity is very deceiving.  Almost every time that I have screwed something up over the course of my career, it can be traced back to a failure to follow this simple piece of advice.  It is not enough just to have a plan; you have to make sure that you actually follow it.  How many times do we have processes or checklists that we fail to follow because we think we know how to do the work faster or we think we know the process by heart only to have that come back and bite us because we left something out.  This advice has become golden to me over the years and constantly keeps me from doing stupid things.  I still find it amazing today that sometimes the simplest advice that we are given can be the most prolific.

 

In honor of Ralph, I wanted to share this story and bit of advice in the hope that someone might reap the benefits from it that I have. 

 

Rest in peace old friend, you deserve it.

 

Ralph Pennacchio 1930-2013

I know that I haven’t blogged in forever, but I thought this particular bug might hit some of my fellow IT people out there.

A couple of months ago, I was fortunate enough to pick up a consulting gig. This was when I realized that I’d been working off of laptops that came from my employers and didn’t have a decent one of my own. I purchased a Lenovo laptop (that I’m very happy with) and, with it, came Windows 8. I actually like it for the most part. It’s shiny and that’s good. The ability to group the tiles and title them really appealed to my OCD. It was great for everything except for the reason that I purchased it.

When I initially started the consulting work, I was working off of an older laptop that had Windows 2008 Server. I connect via a VPN to a VM that they had set up for me. Everything connected perfectly fine and I was working away at the desk that I’d set up upstairs. A couple of weeks ago, my new laptop arrived and at the time it was easier for me to work downstairs. Set up the VPN on my Win 8 machine and everything was great.

Once I moved back upstairs, though, everything went crazy. My VPN connection started disconnecting constantly – I couldn’t keep a connection for more than 5 minutes. I brought up my old laptop, connected to the VPN and everything was fine. I went back downstairs with my new laptop and the connection was fine. By the way, my other internet connections on the Win 8 machine were never affected – only the VPN connection. In case you need help catching up:

• Old laptop – Windows Server 2008
• Downstairs – Internet and VPN connections work
• Upstairs – Internet and VPN connections work
• New laptop – Windows 8
• Downstairs – Internet and VPN connections work
• Upstairs – Internet connections work – VPN disconnects every few minutes

As one last try before going back to my old laptop, Tim put a Win 7 VM on my new laptop last weekend. Guess what? It works fine everywhere. I’ve been working on it all day(upstairs) with no disconnects. Same laptop, same wireless card, same router, same VPN client – just an earlier version of Windows. I’m baffled.

While I hope I’m the only one that’s seen this type of craziness, I doubt it. Maybe this will help someone else from spending a week running up and downstairs.

After attending the PASS Summit last year, I made a decision to become more active in the PASS Community. During the Summit, I had the opportunity to meet so many incredible people from the community – chapter leaders, regional mentors, speakers, board members and just normal folks like me.

As many of you know, I’ve been a part of the Program Committee for the last six months. Originally, I was tapped to head up a task team – a group that would work on projects that had been on the radar, but hadn’t had the manpower to get them completed. Along the way, I became more involved with other aspects of the Program Committee – the things that need to happen so that the PASS Summit can occur.

I think I was sucked in by the vision of the weekly meetings being wonderful opportunities in which we were magically transported to a beautiful meadow with full-on double rainbows, prancing unicorns and woodland nymphs presenting us with bacon-wrapped treats. It was that, sometimes, but it also was long hours, endless emails, looming deadlines and seemingly insurmountable roadblocks. Even with those, the thought of being involved in pulling together a valuable, enjoyable event for the community pushed us forward. I had the opportunity to work with a huge number of volunteers (many of whom I’ve never met face to face) that put in extraordinary effort and working with members from PASS HQ that were very helpful and hardworking.

Reading the tweets and blogs over the last few days makes me wonder if I’ve been duped. I’m continually seeing that PASS has failed and that PASS doesn’t want to get it right and how people are frustrated with PASS. Apparently PASS is some evil, faceless organization that has committing mayhem and creating obstacles as its sole agenda. I’ve listened while people close to me have become disenchanted with PASS as a community, not because of decisions that have been made, but by the reactions of the community members in these last few days.

I’ve been accused of being a pollyanna before and probably will be now, but I thought PASS was more than the BOD and committees. I thought PASS was the people that lead and contribute to user groups and virtual chapters, speakers, volunteers, Summit and SQLSaturday attendees and all of the rest of the people that participate with PASS in some manner. Things have happened that I disagree with and missteps have been made. I’ve voiced my opinion when I thought it was necessary and tried to address issues with the people that inolved – I strive to listen and understand the reasons behind decisions just as I hope that they listen and try to understand my points. In any large group of passionate, intelligent professionsals, there will always be disagreement. The only difference is how that dissent is expressed and handled.

If I were a data professional that was just beginning to read blogs and get involved with Twitter, I seriously doubt that I would join PASS. I definitely wouldn’t volunteer for anything PASS related. So if you really believe that PASS is irrevocably broken, walk away – people will stop joining and stop volunteering and PASS will eventually fade away.

For me, PASS is the community of its members. That community is valuable to me, so for now, I will continue to volunteer and continue to suggest changes. I will continue to believe that the vast majority of PASS is committed to making this community a valuable organization.

Not-for-profit organizations can be awesome and extremely tough at the same time.   About 15 years ago, I joined a not-for-profit community service organzation.  Much like PASS, there were local groups, regions and  an international level.  On all three levels, this organization was definitely able to make a difference. I was fortunate to serve at both the club and regional levels and take part in an international event in Japan.  It was definitely a life-changing experience.

 

One of the problems that I noticed while I was serving on the regional board was that the more that we were able to accomplish, the more that was expected of us.  That was great and it was exciting to see the possibilites, but the problem that we faced is that our resources hadn’t changed.  Because of our tax status, we had to be very careful about how we used the funds raised during our fund raisers and the vast majority of that went to other non-profit organizations that we supported.  In order to give the regions and clubs more funding, we had a couple of options: raise dues, raise conference attendance costs or have more fundraisers during our conferences.  Raising dues and raising conference attendance had the possible side outcome of fewer members or fewer people attending conferences.  Additional fundraisers at the conferences took time away from what we were meeting about.  So we made the decision to make better use of what we had and revisit those ideas in the future.

 

I see a similar issue with PASS.  We’re incredibly fortunate to have an organization that provides as many resources as PASS does and with a free membership.  While there is a great staff at PASS, much of what gets done here is because of a community of dedicated volunteers.   I’ve been fortunate enough this year to be a part of the program committee and that has allowed me the opportunity to understand more of what goes on at PASS.

 

There have been many discussions about what PASS does well and less well along with what they should be doing.  The latest discussions have been about the PASS Summit survey results.  There have been a number of blog posts about it - Brent Ozar (Blog/Twitter), Tom LaRock (Blog/Twitter), Steve Jones (Blog/Twitter) and Andy Warren(Blog/Twitter), to name a few.  I’m not picking on these particular bloggers or even this particular discussion topic.  They all make valid points. 

 

The questions that I found myself asking (and answering) are:  Is this survey the best possible survey?  Probably not.  Did it provide PASS with valuable information?  Yes.  Are there people in the community that might be more skilled in writing/interpreting survey results?  Possibly.  Is paying for a company to write and interpret surveys for PASS the best use for our funds?  I don’t know.

 

If I were to look at the wish list for PASS, I’m sure that it would be huge.  Especially when it comes to items that require funding.  If there are additional things that are needed that will require additonal funding, that money needs to come from somewhere.  Do we increase the registration cost at the PASS Summit?  Do we institute dues?  Both of those choices have a direct affect on PASS membership and the members that are able to take part in the PASS Summit.  Short of that, we have to look at either companies or individuals that are willing to donate their time and resources.   Anyone that has volunteered for not-for-profit organizations know that getting companies and/or people to donate isn’t always the easiest thing.

 

I believe that members should continue to provide (constructive) criticism of PASS when it’s needed.  I don’t believe that there should be a step up or shut up attitude.   But if you can’t volunteer, then understand that PASS can’t continue to grow without also growing its resources.  If you have ideas, provide them.  If you have time, volunteer.  If you find that leprechaun at the end of the rainbow, take him out, steal the pot of gold and donate some of it to PASS.

 

I think most of us will agree that PASS is a pretty amazing organization.  It’s up to us to make it even better.

 

 

Last year my better half, Tim, suggested that we start a blog. It made sense for any number of reasons, but it scared the heck out of me. I couldn’t imagine that there was anything that I could ever blog about that hadn’t already been posted and probably by someone with much more experience than I have. I have a tendency (as I did today) to go out and search for other blog posts that cover the material that I’m about to write about to ensure that I’m at least adding something new with my post.

 

In school, if you’re assigned a paper on the pastoral imagery used in “The Adventures of Huckleberry Finn”, the instructor knows that there have been several works on that particular subject and assumes that you will be using (and referencing) information from those works. Blogging, for most people though, is not an assignment – it’s something that you make the choice to do. The people that read your blog assume that the ideas, tips and facts that you blog about are yours, unless you attribute them.

 

Over the past few months, there have been numerous tweets and blog posts about bloggers that have been plagiarizing other people’s works. In some cases the posts are lifted word-for-word and other cases they have selectively reworded the blog posts, but they were still identifiable. I have no idea whether it was intentional or that they were uninformed about how to use information from other posts. K. Brian Kelley [Blog/Twitter] wrote a post ‘Avoiding Plagiarism’ a couple of weeks ago. I thought I’d take this opportunity to add a little more information. As a note, I’m not an expert in plagiarism, so if any of you reading this post find errors, please comment and I’ll update this post.

On dictionary.com, the Random House Dictionary definition of plagiarism is:
“1. the unauthorized use or close imitation of the language and thoughts of another author and the representation of them as one’s own original work.
2. something used and represented in this manner.”

 

Reading this definition clarifies the reasons for my fear of blogging. I would never lift language from another blog post, but there have been blog posts that have inspired me (like K. Brian Kelley’s) to write a post. Here are some ways that I handle referencing other works.

 

I think you should read this

Example: Kevin Kline wrote an excellent post about the pains of not being included in a meme. You should read it here: http://kevinekline.com/2010/01/14/goals-and-theme-word-for-2010/
In this case, I have nothing to add, but I want to give my audience the opportunity to read great posts that I’ve come across.

 

You can say it better than I can

Example: PowerShell is fabulous. It’s so awesome that it’s caused some otherwise contentious DBA’s to wander astray. Colin Stasiuk [Blog/Twitter] admitted as much in a recent blog post : “…it’s no secret that I’ve been having an affair on TSQL. I’ve been seeing PowerShell behind her back and I gotta tell ya even after the initial excitement of a new language I’m still loving it. “
I know that I couldn’t have said it better than Colin, so in addition to linking to his post, I quoted his remark. Quotes should be used sparingly – if you find yourself quoting more than a sentence or two, you should probably use the example above.

 

Note: Blogs, whitepapers or other articles that are copyrighted require permission prior to their use. In addition, some online works have posted requirements on how they can be used. Brent Ozar (Blog/Twitter) has a good example of that here.

 

This is what I researched

Example: While the sp_change_users_login has the option to auto_fix logins, that action assumes that the username and login name match. If they don’t, it will fail. Using the Update_One option is a safer and the preferable way to handle it. For SQL Server 2005/2008, the ALTER USER statement is the preferred method for mapping users to logins. Greg Low’s (Blog/Twitter) article ‘Much ado about logins and SIDs’ provides a good explanation for these methods.

 

This is probably where unintentional plagiarism occurs most often. If, during your research, you read blog posts, articles, whitepapers, etc. and find useful information, your best bet is to attribute them. If you recall the definition of plagiarism above, it applies to both language and ideas, so if you learned something that you’re passing on a blog post or if you’re using that information to validate your ideas, they need to be cited. Again, keep in mind any copyright laws that might apply.

 

What doesn’t need to be cited

Common knowledge/generally accepted facts

Items that are common knowledge or generally accepted facts do not need to be cited.  Examples of common knowledge are:

• A table can only have one clustered index
• SQL Server is an RDBMS
• Most SQL Server based questions can be answered with “It Depends”

 There is a decent article on common knowledge here.

 

 

Results of personal research

If you’re blogging about an incident that occurred or the results of test that you ran, they don’t require a citation. That is, unless, you did research to solve the incident or used other information to validate your test results.

 

Fair Use

The term ‘Fair Use’ had been bandied about in the recent plagiarism incident. The idea of fair use has no exact definition, but is determined by a set of guidelines. There is a good definition at Plagiarism.org and a good article titled “The Basics of Fair Use” by Jonathan Bailey. According to Plagiarism.org the guidelines look at:

1. The nature of your use
2. The amount used
3. The affect of your use on the original

The ability to define fair use is pretty obscure and personally, I wouldn’t want to try and stand behind that argument.  The incident mentioned above definitely fell outside of those guidelines, in my opinion.

 

Public Domain

At some works fall out of their copyright term and become part of the public domain.  The Wikipedia article regarding public domain can be found here.  While the copyright laws no longer apply, they still require citations.  This point is moot for any SQL Server blogs, since, at this time, there aren’t any works old enough to have fallen out of their copyright term.

 

Conclusion

There is a huge amount of helpful information in blogs. Blogging also provides an opportunity for us to share information and experiences. I think that it’s understood that we learn from other people – just ensure that you credit those people for their hard work.

/*Note – I have yet to see if this breaks anything – this post is for observational use only.  If it does break replication, I’ll add some notes to the bottom of this post */

 

Late last week, we had the fabulous opportunity to see just what happens when the dual power supplies for your SAN are plugged into the same PDU and that PDU gets powered off.  As you can imagine, this caused all sorts of neat things to occur.  Fortunately, the situation was remedied fairly quickly, but not before breaking replication for one of my databases.

 

The error that I received was: “The concurrent snapshot for publication ‘xx’ is not available because it has not been fully generated…”.  There were probably different routes to go to fix this issue, but I decided to reinitialize the subscription from a snapshot and went ahead and created the snapshot.  This particular database is a little under 500GB, so I knew that this would take awhile.

 

The next morning when I came to work, I could see that the snapshot had completed and the subscription had been reinitialized.   I knew that it would take some time for all of the commands that had queued up in the distribution database to be written to the subscription and so I went about my day.

 

Later, I noticed that the number of undistributed commands didn’t seem to be going down at all and was in fact growing.  This is a fairly heavily utilized database with writes going on very frequently, but it still seemed odd.   I did some querying to try to figure out just how behind we were, but, to my surprise, the subscription was completely up to date.  This is where it started to get strange.

 

I queried the MSdistriibution_status view to get an idea of what was still outstanding and saw the following:

Article_id	Agent_id	UndelivCmdsInDistDB	DelivCmdsInDistDB
1	3	0	2130
2	3	0	3295
3	3	0	8275
4	3	0	2555
5	3	0	8310
6	3	0	2521
1	-2	2130	0
2	-2	3295	0
3	-2	8275	0
4	-2	2555	0
5	-2	8310	0
6	-2	2521	0
1	-1	2130	0
2	-1	3295	0
3	-1	8275	0
4	-1	2555	0
5	-1	8310	0
6	-1	2521	0

 

You’ll notice that the row counts for agents -1 and -2, the number of undistributed commands for each article was the same as the distributed commands for agent 3.

 

When I queried the MSDistribution_Agents table, I saw three distribution agents.  One was named identically to the distribution agent job.  The other two had subscriber_ids with negative numbers and the subscriber_db was ‘virtual’. 

 

I did some digging around on some unnamed search engine to try to figure out what virtual subscriptions were.  To be honest, there wasn’t  a lot of information to be found.  I did find a question on Eggheadcafe.com from 2007 where it seemed like the person was having a similar issue.  Reading through, it looked like it had something to do with the settings for allowing anonymous subscriptions and immediately synchronizing the snapshot.  I looked at my publication and both those were set to true.

 

Of course, I posted a question on Twitter at the same time and Hilary Cotter [Blog/Twitter], stated that the virtual subscriptions “are to keep your snapshot up to date, ignore them”.  That seemed to be consistent with what I was reading, but it still seemed odd that they appeared to be holding onto undistributed commands.  This, in turn, was causing my distribution database to grow.

 

Since, at this point, I figured that I was going to have to tear the whole thing down and rebuild it, I thought that I’d try to modify the publication first.  I knew that I didn’t want to allow anonymous subscriptions and I didn’t need to immediately synch from a snapshot, so I ran the following commands:

 

exec sp_changepublication @publication = 'xxxxx', @property = N'allow_anonymous', @value = 'false'

exec sp_changepublication @publication = 'xxxxx', @property = N'immediate_sync', @value = 'false'

 

I reran my query of the MSdistribution_status and voila, the undistributed commands were gone.  When I queried MSDistribution_agents, there was still a virtual subscriber, but it was no longer filling up my distribution database with undelivered commands.

 

I still don’t know what caused this – it seems like something occurred when I reinitialized the snapshot.  So far, replication is continuing to run without errors.  I’d be interested to hear if anyone has run into a similar issue.

A couple of weeks ago, we had a database that had become corrupted due to a disk failure.  After running a DBCC CHECKDB, it appeared that there was one table with a corrupted cluster index.  Paul Randal (Twitter/Blog) verified that this was the case and that it was, unfortunately, non-recoverable without data loss.  Fortunately, we had backups and this database was a replication subscription, so between the backup and publication, we were able to rebuild the database without losing any data.  We then had to rebuild a kind of goofy replication set up, but that’s another story.

 

As I was doing some validation about the data that was contained in the backups and what needed to be pulled down from the publication, I realized that there was at least one other table that was corrupted.  This is where it became confusing.   I went back through the output from the CHECKDB and noticed that the table in question was not mentioned.   At this point, I went through and matched up all of the tables that were listed in the CHECKDB output against the actual tables in the database and found that there were three tables in the database that were not listed on the DBCC output.  I ran DBCC CHECKTABLE against the missing tables and while two of them came back with no error, one was definitely corrupted.  The CHECKTABLE command actually “terminated abnormally” on the corrupted table.   I went back through the the error logs and dump files that had been generated at that time.  Aside from the references to the CHECKTABLE that was run, the only object referenced was the table that had shown up in the CHECKDB output.

 

As a note, this is a 2008 SP1 database.  I know that, when running CHECKDB from SSMS, it will only display the first 1000 errors, but there were only 274 errors mentioned in the output, so I didn’t think that was the issue.  I asked Paul Randal (mentioned above) and his thought was that perhaps the metadata had also been corrupted and because of that CHECKDB may not have seen those tables as existing. 

 

We’ve recovered from the incident, but it was a curious experience.  Up until now, I’ve gone off of the CHECKDB output, but this gives me reason to think that there might be tables that aren’t included. I am interested to know whether anyone else has ever run into a similar incident.  Hopefully it was just an (odd) learning experience.

I was tagged by Jorge Segarra (Blog – Twitter) who had been tagged by Thomas LaRock (Blog – Twitter) in his post about his goals and themeword for 2010.  I was going to try to remain blissfully ignorant about being tagged, but then Tim went and posted his goals.  So I guess I’m on the line now.  My theme word for this year is:

 

Recharge

While there are many things that I want to accomplish this year, I don’t know that (m)any of them will occur until I can figure out a way to recharge.  I’m typically a self motivated type of person, but it seems like, during the previous year, I’ve hit the wall.

 

I’m not entirely sure what has caused this, but I’m guessing that it is some combination of the cyclical nature of job satisfaction, having a boatload of things going on at home and the disconnect between the amount of things that I would like to learn and the amount of free time that I have.

 

I realize that there is no magic button that will instantly recreate the hunger for knowledge that I had when I began learning to be a DBA.  What I can do, though, is set some goals, work hard to follow through on them and be patient.   My hope is that in the process of achieving these goals, I’ll rejuvenate my love of this career path.

 

Goals

Pick one or two topics to focus on

I have at least three SQL Server books sitting on my desk and more at home that I haven’t done much more than flip through.  Rather than setting a goal to read all 3000 pages (doable, but daunting), I’m going to pick a couple of subjects to focus on and learn them as thoroughly as possible.  This is ongoing – if it’s March and I know everything there is to know about database corruption (or whatever it is I end up focusing on), I’ll move on to the next subjects.

 

Blog more

My first love is teaching.  It invigorates me and gives me purpose.  Blogging provides me an arena to hopefully teach people that are learning to be DBAs and the chance to share what I’ve learned.

 

Become more involved with PASS

As I’ve mentioned in previous posts and as Tim mentioned in his goals for 2010, we’ve talked often about starting a PASS chapter in Tucson.  This ties into my love of teaching and will help us to connect with folks locally who have similar interests.  I would also like to take part in other committees within PASS as needed.    This will definitely require a balancing act with work and family, so I’ll be taking baby steps to ensure that I don’t shortchange other areas in my life.

 

What does this all mean?

None of these individual goals are earth-shattering and that’s intentional.  I have a tendency to swing for the bleachers, but end up hitting to the pitcher and it makes me grumpy.  My hope here is that I make some good, solid line drives and then I’ll be set up to hit it out of the park.

 

I’m tagging a couple of people that have unknowingly helped me to recharge (some thank you, eh?) 

TJay Belt (Blog – Twitter)

Wendy Pastrick (Blog – Twitter)

Kendal Van Dyke (Blog – Twitter)

A few days ago, in his blog Goals and Themeword for 2010, Jorge Segarra (Blog - Twitter) tagged Lori and me to write a blog about our goals and theme word for 2010.  While the title of my blog is somewhat sarcastic, it really reflects facing a year that will be full of immense opportunity and challenges as the result of a successful 2009. 

 

In 2010, I face opportunities on all fronts in my life, professional and personal, so here I outline some of those and end with what I feel will have to be my theme word for the year.

 

Professional

On the professional front, 2010 is going to be a year full of many opportunities.  The biggest challenge will be to take full advantage of these opportunities without letting everything else slip.  Here are the major opportunities, as I see them:

1.  It is starting off with me shifting my focus in my position with my employer from more of a support role (production DBA) to more of a strategic role by leading a team of DBAs and System Architects and striving to make them stronger as a team as they take on what seems to be an impossible list of projects.  As anyone who has made this shift knows, the key to doing it is learning to delegate effectively, however, as most that have made this transition also know, you never get completely out of the support role.  As part of this, I have the unique opportunity to get to mentor someone who has recently shown a great deal of aptitude on our help desk and help mold him into a junior DBA, something I know will be extremely exciting and rewarding to participate in.
2. As I move out of my DBA comfort zone, I will be required to learn a ton of technologies that my team will be responsible for that I haven’t really had to worry about up to this point.  Some of these technologies (SharePoint, SCOM, PowerShell, SSRS) I find to be extremely exciting opportunities and will be a great chance for me to learn and grow.  Some of the technologies I will be required to learn (i.e., SSIS) are technologies that I have tried to avoid and would rather not deal with, but circumstances dictate that I must and I know they will end up being growth opportunities for me, nonetheless. 
3. Getting to attend the 2010 SQL PASS Summit.  Unfortunately, I missed the 2009 PASS Summit and, consequently, missed many great opportunities to learn and network.  I have promised myself that this year I am going if I have to beg, borrow or steal (okay, that might be a bit of an overstatement, but you get my point ).
4. Starting a PASS chapter in Tucson, AZ.  This is something that Lori and I talked about for a while and that we are going to be very passionate about in 2010.

 

Of course, this new focus and need to learn these new technologies means a huge investment of time which leads me to my next challenge/opportunity – work/life balance. 

 

Personal

Throughout 2009, one of the biggest things that I never thought I could get right was balancing the needs of my job with the needs of my family.  In 2010, this challenge will become exponentially more difficult.  If 2009 taught me anything it is that I need to go into 2010 with some sort of system or plan to try to make sure that I give my family the time that they deserve while still living up to my work commitments.  This is a challenge that I am still working on cracking.  Some of the personal opportunities and challenges I face in 2010, other than spending more time with my family include:

1. Dealing with some personal issues of one of our kids as he strives to find out who he is on the way to adulthood.  We have had some challenges with this over the last year and are seeking some supplemental help, but the challenge will be to define and stick to a plan that will help our son become a happy, productive, well-adjusted adult.
2. Getting more involved in our church.  This has actually been on the list for a while now, but it needs to become a priority.  This is where we lead by example, not only for our church, but for our family as well, and is something that I see as essential for us to get to where we need to be spiritually.  I know that we have been blessed with many great gifts and talents in our family and it is time that we use those to give back.
3. Continuing to grow the relationships that we have cultivated with our many friends in the SQL Server community.  I have to say that getting to network with the SQL Server community around the world in 2009 via virtual conferences and social media was one of the most unexpected and rewarding professional experiences of 2009 and, probably, my career.    Most people would put this as a professional goal, but as I have interacted with many of you, I see the friendships that are cultivating as much more than professional connections and feel blessed to have been able to have these friendships.
4. Blogging more.  Again, this could be go either way, professional or personal, but I consider it a personal goal as it isn’t something that is really required by my employer (or something that probably a lot of my coworkers even know I do) and our blogs aren’t always technical in nature.  If you told Lori and me at this point last year that we would we start a blog in 2009 and be syndicated by SQLServerPedia, we would have laughed and said that would be ridiculous because we couldn’t come up with enough to write about that anyone would want to read.  Fortunately, we did get the blog off the ground in the last few months of 2009 and wrote some articles that people had some interest in, so the next challenge for us is to keep putting out content from our professional and personal experiences that, hopefully, people will want to continue to read.  In this way, we can feel like we are contributing something back to the great SQL Server community that has helped us out so much.

 

Theme word

So, all of this leads to my theme word for 2010, management!  In order to have a shot at accomplishing all of these goals, it is going to require management; management of time, priorities, expectations, and resources.  This is going to be probably the biggest challenge that I have faced so far, but if I am successful, the rewards will be great and have an impact on my life and my family that will pay dividends for many years to come.

Here is hoping that all of you have a successful and happy 2010!

I have been managing DBAs for over ten years now and one question that always seems to come up, usually from management, is what do DBAs do anyway?  Hopefully, you have read my prior post, “Yes, Production DBAs Are Necessary!” and you know where I stand on this issue. 

 

I fully believe in the role of a production DBA and, as someone who has been in this role for well over a decade, would like to define, based on my experience, what I think that role should be.  The most effective way of doing this, I believe, is to define what a production DBA should and shouldn’t be expected to do.  Granted, this won’t work perfectly for every situation and this is not intended to be an exhaustive list, but the following should be a good guideline for defining what a production DBA does and why they are critical to ensuring that your data is safe and available.

 

User data

A production DBA should not have anything to do with data. There, I said it. This statement alone is going to generate tons of controversy, but let me explain and specify that I’m referring to user data in user databases. This confuses many people because, after all, DBA does stand for database administrator. Let me clear up that confusion right now by saying that database is one word and is a manageable entity just like a server. Data lives inside a database, much like an application lives on a server. Typically, there is a lot of data in a database, just like there can be many applications on a server, each of which may have their own application administrator. As in the case of the application analogy, we don’t expect the system/server administrator to necessarily manage all of the applications on the server, the production DBA should not be expected to manage the data in the database. The production DBA is responsible for the uptime of the database, the performance of that SQL Server instance and the safety (backups/DR) of the data in the database, not for the data itself. There should be other roles in the enterprise responsible for determining how the data gets into the database and how it is used from there, namely, data architects, database developers, etc.

 

Optimizing T-SQL

A production DBA should work with database developers to help them optimize T-SQL code. I emphasize work because production DBAs should not be writing code, outside of administration automation. This goes hand in hand with #1 above and, when you think about it in practical terms, makes sense. A production DBA may be responsible for managing hundreds or thousands of user databases all containing data for different uses. A production DBA can’t practically understand all of this data and be able to write code against it effectively. What the production DBA can do, though, is help the database developers optimize code by looking at available indexes and discussing with them how to best to arrange joins and where clauses to make the most effective use of indexes and/or create additional indexes. Additionally, if your organization falls under the Sarbanes-Oxley or PCI regulations, this segregation may be mandatory. Your developers should not have access to production databases. Their code should be promoted by the production DBA, who does have access to the production databases. This also means that you should have a development environment that reasonably approximates your production environment.

 

Managing Instances

The production DBA should manage the SQL Server instance(s). This is a big job and if your data is important and you have more than a handful of instances, it is a full time job. Let me breakdown what this actually includes to illustrate just how large a job it is:

1. Install/patch SQL Server instances – The production DBA is responsible for installing any new instances of SQL Server and making sure that existing instances stay current on Microsoft patches. This isn’t just a matter of running through an install wizard. A number of things have to be considered by the production DBA when a new instance of SQL Server is installed. These include:
   • Collation settings. Questions have to be asked about how the application that uses this data expects the database to handle the case of words, accents on words, code pages that the application expects to be used (this gets into localization and language for those shops in other countries or that are responsible for servers that reside or are going to be accessed by users in other countries).
   • Drive/File Layout – To make the database instance run optimally, the production DBA has to consider what drive resources are going to be available and how the database files should be laid out across those resources. During this phase of the installation the production DBA has to consider whether only local storage is available or whether storage will be housed on a SAN. If it is going to be housed on a SAN, the production DBA needs to work with the SAN administrator to ensure that the LUNs are set up appropriately for optimal SQL Server access, which in and of itself requires a lot of knowledge and experience.
   • Scalability – The production DBA should be involved in developing the specifications for the hardware. Questions that the production DBA will be asking of the various parties include, how many concurrent users will be accessing the data, how many databases will there be, is the data static or changing and how does it change (i.e., batch load, transactional updates), etc. This will give the production DBA a better idea of what kind of resource utilization to expect and drive the hardware specification process. It will also help determine recovery model and backup strategies.
2. Create, and periodically test, a backup and recovery scheme for your enterprise’s data. Things the DBA has to consider as part of this include:
   • Is the data development or production data? Yes, development data gets backed up and is considered in an effective backup/recovery scheme because, after all, you don’t want to lose any of your development effort; it just isn’t prioritized as highly as production data.
   • How often data is updated in databases and how is it updated (online transactions, batch, etc)? This determines the recovery model that should be used and leads the DBA to ask another question, what is the maximum acceptable data loss in terms of time? With the answers to these questions, the DBA can more effectively determine the type and the frequency of backups (full, differential, transaction log, etc).
3. While somewhat tied with backup/recovery, the DBA is also tasked with helping to come up with a disaster recovery strategy for the SQL Server environment, within the constraints of the enterprise’s available resources.
4. Uptime/performance – The DBA is tasked with managing/monitoring those things that would impact the availability of the databases. This includes CPU utilization, I/O performance, disk space, reviewing event and SQL Server logs,etc.
5. Help design the security model for SQL Server instances. Within SQL Server, there are a number of built in security roles, both at the instance level and database level that users can made members of in addition to the ability to create custom roles. Additionally, SQL Server can operate in Windows Authentication mode, which integrates your domain security and uses the users’ Windows domain accounts or Mixed (or SQL Server and Windows Authentication Mode) Mode, which allows accounts to either be Windows domain or native SQL Server accounts. There are a number of factors to be considered here to make sure that your users can use your system while still protecting and safeguarding sensitive data and the SQL Server configuration.

 
So as you can see, the production DBA has plenty of things to contend with in order to effectively and efficiently maintain a SQL Server environment.  The bottom line comes down to how valuable is your data to you and how much are you willing to risk by not allowing your DBA to dedicate themselves to maintaining and safeguarding it.