Joining Infrastructure

   Posted by: jasoncbunn   in SQL Server

A little over a year ago, our team of DBAs was transferred to report to the director of infrastructure instead of the director of data services or the director of operations, our previous departments.  At the time it was met with a bit of concern from the team.  Data Services brings to mind the goal of delivering data solutions to the organization, building an operational data store or a data warehouse.  Infrastructure was viewed in some ways like keeping the lights on.  Keep the plumbing working.  Not unlike the CEO’s initial impression of infrastructure in The Phoenix Project.

However, over the last year, we’ve come to appreciate the closer connection to the teams that build our servers and provide our storage.  One example is the recent effort to reduce storage allocations for SQL Server.  With the storage team as a separate entity operating “over the wall” so to speak, we ask for storage and it’s received.  We didn’t get involved in the overall picture of the array.  But as a member of the infrastructure team, we’ve learned about the difference between storage subscription and storage allocation.  We’ve worked much more closely with the storage admins regarding I/O concerns and how the inner workings of the SAN function.  The idea of the SAN being “smoke and mirrors” as it was once described, is finally fading away as we develop a better working relationship with them.  I could describe similar situations talking with the server and network teams.  A year later, we’re happy to be part of the infrastructure team, although the change has not been without some negatives as we tend to become involved in some data services projects later than we used to, and often end up needing to challenge some assumptions and the translation from logical ideas to a physical database has been painful at times.

By the way, we did get paged on Easter night.  We were updating our custom job processing engine and ran into a powershell issue.  Turns out that the foreach command in powershell can loop once for a $null object.  That wasn’t tested since we didn’t consider a few servers still have powershell 2.0 running.  It was a feature fixed in powershell 3.0 and described here.




Easter Sunday- What alerts did you receive?

   Posted by: jasoncbunn   in IT Leadership

Today we celebrate Easter Sunday, when Christians celebrate Christ’s rising from the dead.  This should be a relatively quiet day for IT.  Depending on your business, it even may be the end of a three-day weekend with the offices closed for Good Friday.

In the database administration space, Sunday is our maintenance day, at least for our firm.  We actually recently expanded our maintenance window to encompass most of the weekend in a staggered fashion.  We used to launch our integrity checks all at once and that time quickly became very well known by the storage team due to the IO load on the SAN.  Thus, we fanned those out over most of Saturday since many of the applications are offline over the weekend.  Sunday is reindex/reorganize day and even that has become much more spread out across the course of the day.

If all goes well, we receive zero alerts.  No calls, no pages.  I’m always perturbed when we get a page or alert for something that is expected or typical.  If the response is, “oh yeah, that happens.  Just ignore it,” then why are we getting called, especially overnight or off hours?  If I’m going to ask my team to rouse themselves out of bed, to remotely connect to our systems, and read an error message, the very strong preference is that there is an actionable item that needs to be corrected at the other end of the message.  If it’s a benign configuration anomaly, or a status message, then send an email and wait until morning.  If the process normally retries successfully, then email on the first failure, retry, and let my team sleep unless it fails a second time.  I don’t shy away from the work– if there is work to be done, certainly wake us up.  But the ideal state is that we only use the immediate channel of a page, text, or call if there is action to be taken.

So, we expect a quiet Easter and will pick things up on Monday.  Happy Easter!


The blog- rebooted

   Posted by: jasoncbunn   in General

It has bee a few years since I reserved this domain name and it has been difficult to get going.  There’s the primary question of what to write about, of course.  Do I keep it 100% focused on database administration and IT leadership?  Should it expand to other professional or personal interests?  Should I look to share my own ideas, or use it as a venue to promote the good ideas of others that I uncover?  Plus, do I have anything worth saying and can it be done in a way deemed useful to others?

Recently, I’ve decided to attempt to restart posting my thoughts and commentaries.  There are a couple of developments that led to this decision.  First, I read a great article at Success.com on how blogging changed the professional life of Jordan Fried.  While I currently have no plans to start my own business, the idea of branding, of developing clarity and focus, makes a lot of sense.

Second, I am finding that there is a lot of value in recalling how events transpired and how obstacles were overcome and how progress was made.  There have been a number of situations where a past solution has become the basis to solve a current problem, and I’d like to capture these developments as they occur since they could help others.

So with that said, I will see how things go this time.  I am not the Australian viola musician, nor am I the monopoly champion of the same name.  And no, I’m not affiliate with the Bunn family of coffeemakers, either.  So who am I and just what will I be writing about?  Primarily, I will aim to share lessons learned, war stories, victories and undoubtedly some defeats in my professional life as an IT professional.  Specifically, I lead a team of database administrators and ETL administrators for a midwest insurance company.  We help ensure the availability and performance of over 4500 databases in both test and production systems.  My job is to make sure the team accomplishes our objectives, while building confidence and camaraderie within our group, making sure that each individual is growing into the best professional they can be.

While the focus will be technical and IT related, I’d like to consider that I live a well-rounded life with interests outside of work.  Thus, I may veer off to talk about raising children (we homeschool our four kids- two boys and two girls), Catholic fatherhood and faith (I am a life-long Catholic and I consider my faith an integral part of who I am), or even health and wellness (I have a long running struggle to win the battle of the bulge, and may occasionally share how that is going or other insights from our small business that has sold over $1.5 million in wellness products online over the last 10 years).

If in the end there is an audience for my musings, and even better, a conversation, that’s great.  But at present, my primary motivation is to make this a habit, to produce a body of work that can be a record of growth and achievement, and to gain those insights that come from an examined life.


Deadlines have meaning, even if no one is watching

   Posted by: jasoncbunn   in IT Leadership

The 4DX method talks about a cadence of accountability or a cadence of delivery. Making commitments and then following through on those commitments. It is where the rubber meets the road. It is where execution either happens, or doesn’t. When you put a date on a calendar, and tell yourself or others that the product will be released or the project will be done by that date, you are making a commitment.

There are two cases where I’ve recently seen problems. One is where there actually is no date defined, or it’s somewhat vague like “before the end of the year” or “this quarter sometime.” The claim is that there are too many variables, too many moving parts and too much dependence on other teams in order to define a firm date. While I sympathize, especially if you are relying on other teams, a goal isn’t a goal without a deadline… it’s just a dream. One of the skills an IT professional needs to develop is the ability to forecast and then manage the variables that feed into that forecast. Don’t just be content with Q42013, especially when it’s already October. Define a delivery date. Then, what are the blockers? Who do you need to come through for you in order to meet that date? What key metrics or interim deliverables are necessary? Get out in front of the potential problems, build contingency time into your plan, and if you need to adjust, it will be from a position of knowledge about specific issues that need to be resolved. Without a date, none of this can happen and you float through Q4 wondering what happened to all the time.

The other case is when a date is set, but no one cares, even you. Yeah, sure, we talked about December 1st, but if we miss it it’s not like anyone is waiting for it. While it may be true no one is waiting, people are watching. Once the date is mentioned, unless you operate in total isolation you have announced what your intentions are and people will be watching. It could be your supervisor or her peer, it could be another team delivering software to support your solution, whatever. If you don’t care about your date, why should they care when you need something from them? Do you want to develop a reputation for consistent, timely delivery, or do you want to be known as the guy everyone adds six weeks to whatever you say?

There are times when adjustments are needed, that is certain. No date is immovable. But if the target needs to be moved, it should be for specific reasons, identified far in advance. If you are shifting your end-goal any closer than 1 month prior to expected completion, there was a breakdown of project leadership, in my opinion. There are unavoidable edge cases that come up, but they should be viewed as failures to examine and learn from.

Consistent cadence of delivery builds a team the whole organization can trust. It’s worth the planning effort.


Database Administration Tiered Support

   Posted by: jasoncbunn   in SQL Server

As a database platform footprint grows, it’s natural to add DBAs to your staff to support the growing number of servers, instances, and databases.  In the beginning, the nature of the work of the new DBAs is not significantly different from the work of the first DBAs.  There are simply more heads to divide up the work.  However, at some point, it is worth considering if that approach is really the best one.

At our shop, we are considering the idea of tiered support.  We have hundreds of servers and thousands of databases, with nine DBAs.  The work is getting done– we’re not in a crisis mode, database backups are regular, and verified, deployments are relatively smooth.  However, there are a host of metrics that we collect, but we do not have the bandwidth to improve upon.  For example, our daily monitoring tool collects “interesting” events in the various error logs.  Those are reported in the form of an email, but it’s rarely looked at.  Non-production jobs may fail and not be investigated.  There is little proactive maintenance that occurs.

The idea of tiered support makes sense, but it’s something that seems to be rarely talked about.  At least, I couldn’t find many references out there.  What we’re looking to move to at least at first is a two tier support system.  For the first tier, we’d like to utilize our operations center to offload routine, non-production work such as granting permissions.  We provided the operations center with a powershell script that grants a limited set of permissions– read, write, and execute.  The script limits what can be done and it essentially only calls a stored procedure, so that limits the permissions that we need to grant to the operations center engineers.  The stored procedure constructs the GRANT statement.  Everything is logged in our configuration management tool.  We will be looking for more opportunities to push these routine tasks to the center this year.

The other step for the operations center is to be the “notification” tier.  We’d like to make the operations center the recipients of the calls and pages, at least the first contact.  If we provide the proper triage recipes, they will be able to conduct a preliminary analysis, determine if it is in fact a DBA event, and then, the second tier can be called with that all important initial investigation complete.  For example, if a server is down, the operations center can go through the steps of attempting to connect to the server and determining if the server itself is down (an issue for the server team) or the instance is non-responsive (an issue for the DBAs).

As we move in this direction, I have hopes that we’ll eventually get to three tiers.  The first, the operations center, is the reactive tier, responding to pages and alerts.  The second tier monitors the day to day operations and also responds to requests.  The third tier is that tier that looks at disk capacity, future needs, proactive maintenance and optimization,etc.  Each tier can be an escalation point for the tier below.  It will take some time to get there, but it has the potential to lead to a more capable DBA team that scales better as a server footprint grows.


Pondering direction

   Posted by: jasoncbunn   in General

This blog has been out there for over six months and so far, it hasn’t been much.  I haven’t posted much.  But I still think there is value in blogging, whether personally or professionally.  It has certainly been interesting in trying to ponder what to write about.  There basically three things that I could, and probably should, write about, for different reasons.

First of all, I’m a database administrator and a good portion of the SQL Server community give back in some why.  Many people write about their experiences, their trials and triumphs, in the professional IT community as a way to help someone that may run into that issue in the future.   I’ve started to do that and will try to do more.  I’ve actually had my first article posted online, Populating a Vertically Filtered Replicated Table, on SQLServerCentral.com.  So the blog is a good thing to do as a way of giving back, and also as a way to distinguish yourself as a professional.  If someone in a position to help you wants to see what you’ve done, just show them your blog.

A second reason to write is that I’m a Catholic father of four, ages 10, 8, 5 and 10 months.  Plus, we’re one of those wacky Catholic families that actually believe and try to follow what the Church teaches.  You know, all the hard sayings about contraception, abortion, divorce, co-habitation, etc..  While some medical issues have intervened this year to prevent us from having any more of our own biological children, we’ve always views children as a gift from God and adoption is a possibility down the road.  Catholics online are a small, but growing, group and it’s good to share what little reflections or insight I’ve gained, or what mistakes I’ve make (mostly the latter).  After all, it’s hard to raise saints, but that, quite literally, is what God calls us to do.

A final reason to blog is that social support and inspiration is key when you are trying to get healthy and lose weight.  I have about 150 pounds that I could lose.  That may be a bit too much, but let’s just say that the first digit on the scale is a 3.  I’ve struggled with this for a long, long, time and maybe, as I finally start to succeed, the story can help someone.

So there are plenty reasons to blog away.  The biggest obstacle is time, and a second is convincing myself that what I have to say is worth saying.  As for time, I can’t use that as an excuse, even with four kids.  I have the same 24 hours given to everyone on the planet, and at some point you just make the time if it’s important, even if you have to write a post with a 10 month old sleeping on your chest, as I’m doing now.  As for writing something worth writing, I did read today that you basically have write for yourself, from your heart.  Readers who resonate with what you say will continue to read, those who don’t will not, and so what?  In the end, it’s my blog, and if blogs started out as basically online personal journal, then its worth is ultimately measured by me.

So that’s my Saturday morning reflection, a first post for 2013.  We’ll see how it goes.


SQL PASS Summit 2012- Reflections

   Posted by: jasoncbunn   in SQL Server

Well, SQL Pass Summit 2012 is over.  I’m writing this on Friday evening, from the hotel.  The fact that I’m not out with new found SQL Server colleagues and friends tells you something of my experience and the whole networking aspect.  That part was, in fact, that was a dismal failure, and I fully admit 99.44% of that is my fault.

Before I get to that, I must say that the PASS organization and Microsoft put on one heck of a conference.  The facilites were top-notch, the staff helpful, the food was good, the technology worked (as I would hope so at an IT conference!).  It was a very professional and smooth running affair from start to finish.

The two keynote events were outstanding.  The world of data is changing, and it is clear that Microsoft is aware of this and is moving to become a leader.  It’s exciting and unnerving at the same time.  Exciting because it is all the more clear that there is power in information, and information comes from knowing how to acquire, manage and transform data.  As a database professional, it is clear that major, major gains in the future will come through efforts in this field.  It is also unnerving though.  There is a whole lot to learn, and what if the company that pays you is not willing to step in front of this trend?  Personally, the fact that my company didn’t want to pay for a copy of the conference talks to either download or take home may be an ominous sign.  It’s easy to get excited here about big data, hadoop, and transforming whole industries, but back home, if you are immersed with the standard tasks of take that backup, add this index, tune this query, all of what was learned here, the vision of it all, will be lost.

I did enjoy the technical talks, although it will take some time to digest it all.  I’m so glad the slides and demos are going to be on the PASS website.  There’s a lot to go through, but most of the talks did have specific takeaways that could be immediately applied to several issues back home.

So, about the networking.  SQL PASS tried to do something for first-timers, pairing groups of us with alumni who were willing to help us have a better experience.  I don’t know if it was just my alumni assignee, but after the welcome reception, that I missed because I was flying in that day, the alumni interaction ceased.  No emails, no contact at all.  I did see the guy a couple of times from afar.  I’m not sure what the right answer is, though.  Perhaps it was clear that after the welcome reception we’re on our own.  After all, he’s there for his own reasons, not to babysit us.  I get that.  But perhaps a breakfast with the group, or a mid-point “how’s it going?” session, would help those of us who didn’t get to the reception.

As for the other events, PASS had some good opportunities to meet people, but it was a combination of my natural introvertedness, and unfortunate choices, that stymied my feeble efforts.  For birds of a feather and one of the breakfasts, the reaction to my arrival at the table and saying hi was that some people grunted, and the rest didn’t look up from their phone or tablet or netbook.  I sat by myself for most of the regional luncheon as people filled in every table but the one I picked, although that did get better as the room filled up.

It was interesting watching everyone with their various electronic devices.  People don’t look up at your arrival when they are catching up on email and what not right from their chair waiting for the next talk to start.  Heck, I saw one guy updating his fantasy football roster for the weekend.  I wonder if the networking was different five or more years ago when you sat down next to a person who wasn’t plugged in 24/7.

So, the networking was a bust, and I do blame myself for that.  Hopefully, I’ll be able to come back to a future session (maybe Charlotte 2013) and get better at this.  But overall, it was a good experience, a lot was learned, and I highly recommend it.


Travel Day for SQL PASS Summit

   Posted by: jasoncbunn   in SQL Server

Today’s the day.  Traveling to Seattle today for the Summit.  So many people are so very excited online.  I admit I am too, but more intrigued than excited.  I want to see what it’s all about.  Also, there is a lot of potential for networking, so I figured I should update the blog in case there are actual visitors!

Traveling from 3:12 CST to 8:02 PST, which means I’ll be in the airline system for most of the election returns.  That should be interesting.  The country is so very divided.  Will it be a squeaker for the president, or perhaps a new course is chosen?  Either way, half the country will be ticked off by the time I land in Seattle.


Attending SQLPASS Summit in Seattle

   Posted by: jasoncbunn   in SQL Server

In early November, I’ll be heading out west for the first time in over six years to attend the Professional Association for SQL Server (PASS) Summit in Seattle. It’s billed as the premier event for SQL Server professionals and I’m really looking forward to it.

I’ve been a DBA for about six years and in that time I’ve progressed to a point where I’m looking forward to expanding my perspectives by learning from the best, and spending some time figuring out how to take things to the next level.

In my day-to-day activities, we have a great group of eight DBAs working to manage over 4000 databases encompassing over 50 TB. I’d like to think that most of the daily triage and the daily grind, so to speak, are things that we are good at at this point.  All databases are backed up at least daily, with reports about missing full backups, or databases in full recovery without a recent transaction log backup emailed to the team each morning.  We monitor disk space, security changes, error log messages, and a host of other metrics.  We’ve automated backups, weekly maintenance (without a maintenance plan), and have developed a rather sophisticated unattended restore process (allowing developers to manage a well defined restore operation without direct DBA involvement) and a deployment process that can support both replication and change data capture with relative ease.  Yes, I’m proud of the infrastructure we’ve developed.

At the same time, there is so much more to learn.  One of the great new features of SQL Server 2012 is always-on availability groups and we will be moving in that direction next year, complete with clustering across subnets.  I’d like to see what other pros have done with monitoring of transactional replication.  Does everyone use tracer tokens?  How widespread is the use of the replication checksum capabilities?

SQL Server is such a big product that there could be whole swaths of the software what you never touch, and I don’t mean simply not using reporting services or integration services.  Even within the database engine, if XML querying or spatial data types are not part of the daily grind, then it’s not something you are likely to be proficient in.  So do those who taken and pass certifications, even the vaulted MCM’s, learn a whole host of features just to pass exams?  I’m curious to see if there is any opinion in that regard.

I’m looking at this trip as an opportunity to relax a bit, and focus on the profession, rather than the job.  What area of expertise can I really develop?  Are there any experiences or proficiencies I can share?  What are the next steps in my journey?  Hopefully I’ll find out a bit more during the week.  The sessions look great, and it’ll be hard to choose which to attend, but I’m sure it’ll be fruitful.


Change Data Capture Access Role

   Posted by: jasoncbunn   in SQL Server

When enabling a table for change data capture, one of the options is to set up a user defined role to control access to the change data capture functions.  If a user is not a member of the role that is specified when change data capture is enabled, the user will not be able to use the functions that access change data.  This role is defined when sys.sp_cdc_enable_table is executed. For example:

USE AdventureWorks2012; 

EXECUTE sys.sp_cdc_enable_table
     @source_schema = N'HumanResources'
   , @source_name = N'Employee'
   , @role_name = N'cdc_Admin';

(source:  http://msdn.microsoft.com/en-us/library/bb522475.aspx)

In this example, a user must be a member of the cdc_Admin role in order to use the CDC functions.  Note that membership in the db_owner role or sysadmin fixed server role will supercede this gating functionality.

This week, an interesting situation arose– what if the role doesn’t exist?  This can happen if CDC is set up in one environment and then the database is copied to another environment with different permissions.  If that copy is done via a backup and restore with the KEEP_CDC parameter, all of the change data capture tables and meta data are retained, including the name of the gating role.  If the role doesn’t exist, the functions are not accessible to anyone with permissions lower than db_owner.

This happens because the functions do a simple check.  First, are you sysadmin?  No.  Are you db_owner?  No.  Is there a gating role defined in the cdc.change_tables system table?  Yes.  Are you in it?  No.  Well, tough luck.  The functions usually don’t even fail, but will return a null result set.  A good example of this is the get_ordinal_position function.  You can misinterpret a missing column as a permissions problem, or vice versa.  The function will return null if there is no column in the table you are interested in, but will also return null if you are not a part of the gating role.

If the CDC functions are giving you problems, check cdc.change_tables and ensure the user is a member of the gating role, if one exists.  If the gating role is defined in cdc.change_tables but doesn’t exist, create it and add the user you are interested in.  The role doesn’t even have to have any explicit permissions, it just needs to exist, and the user that wishes to use the cdc functions needs to be a member.