human choroinic gonadotropin
24
Oct

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.

13
Jun

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.

23
Mar

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.

9
Nov

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.

6
Nov

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.

12
Oct

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.

8
Sep

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; 
GO 

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

(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.

24
Aug

Inbox 0: An Awesome Place to Be

   Posted by: jasoncbunn   in SQL Server

As an enterprise DBA, I get a ton of email. Requests, notifications, announcements, server alerts, summaries, meeting minutes, and ongoing conversations about all of the above. In a typical week I probably receive over 200 emails.

Deciding what to do with those emails can be a nightmare.  Without some sort of order and organization, you can quickly find yourself with thousands of emails that are neither junk nor critical.  These emails may contain important information, may be a reminder that you should do something, or a note to yourself.  To make matters worse, in my case since we have a whole other inbox for our general DBA team mail, and we use the read/unread setting to denote new vs. ongoing tasks, I’ve set outlook not to mark an email as read when it’s in the preview pane.  So I ended up with literally thousands of unread email stretching back months.

Until today.  Today, I have nothing in my inbox.  Zero.  Zilch.  Nada.  Getting there took two steps.

First, I realized that, with Enterprise Vault, all emails older than 30 days old are archived.  So, I went from thousands to maybe one thousands by hitting the delete key to drop all shortcuts to the vault out of the inbox.

Second, I took a tip from Getting Things Done, by Dave Allen.  Every email is categorized, and I am getting much better at deleting intermediate conversational emails.  Today, when I get a new email, it goes into a folder in Outlook:

  • Action Pending:  Something to do
  • In Progress:  When I’m going to work on it right away
  • To Read
  • Waiting for Calendar:  When I have to act later.  I flag it with a reminder for the future and forget it
  • Waiting for Response:  If I’ve asked a question, I dump the email here and forget it until someone responds.

My work flow consists of moving inbox emails into the folders.  Once there are no more emails in the inbox, I choose what I want to work on and move the request from Action Pending to In Progress.  Once the task is complete, I move some email into a “Completed” folder that I use to write my weekly status report.  Daily I’ll check the two WaitingFor folders to ensure things are current and moving along.

This has freed up a lot of time and reduced the inbox clutter to zero.  When there’s nothing in the Inbox or Action Pending, I complete what is In Progress and then I can concentrate on my own assignments, research and development.  It’s a great feeling to know that you are on top of the inbox monster.  Whatever method you choose, Inbox: 0 is a worthy goal to shoot for.

19
Aug

Bulk loading one table from another- SSIS vs. INSERT INTO

   Posted by: jasoncbunn   in SQL Server

With 10 days to develop and approach and implement it, we were presented with the need to combine data from two sources into a single set of tables.  One of our premium calculation databases is in production with data from one source system.  Meanwhile, data from a second source system has been converted into the same format and we are tasked with combining the data from the two systems.  Unfortunately, the converted data was built without considering the identity column in the four key tables.

For example, production may have data using identity values 1 to 5 million for a key table, and the converted data has overlapping identity values, from 1 to 60 million.  The goal is to have a single 65 million row table.  Since the 5 million rows are in production and resequencing the identity columns will have downstream impact, we will be inserting the larger data set into the smaller one.

Our basic plan is to, in this example, add 5 million to the identity column of the larger set and insert it into the smaller set:

INSERT INTO DBO.Prod_Table (
prodTable_Key,
<remaining columns>
)
SELECT convertedTable_Key+5000000,
<remaining columns>
FROM Converted_Table

Of course, to do this we set IDENTITY_INSERT on for the target table.

To test this, we just created an empty table and inserted the 60 million records into the empty table.  The total duration was about 2.5 hours.  Looking to improve upon this, we first tried, just to see the effect, skipping the identity insert and letting SQL Server populate that column in the target table.  When we ran the test, the transaction log of our database filled just before the load was complete, but we were looking at about 1 hour, 20 minutues.  This is better, but we wanted to see if we can improve further.

Incidentally, we discovered that if we drop the primary key (identity column) and just loaded into a heap, SQL Server decided to single-thread the load, which would take about 12 hours on the hardware we were testing with.

I read through the SQL Server Data Loading Performance Guide and ended up with few things to try.  The guide is good, but is geared towards loading from a flat file via bcp or BULK INSERT.  In our case, the data to load is in SQL Server, and the time to unload the data to a flat file would negate any bcp gains.  But the guide had one interesting graphic that was the key to our solution.

Here, we see that if you bulk insert into different parts of a target table, you can multi-thread the load to improve performance.  Combined with SSIS, we were able to achieve our objective.  We set up a simple SSIS package with separate data flow tasks.  Each data flow selected a subset of the source table and inserted into the target table.  We set the FastLoadOptions to define the incoming data as ordered.   How many data flow taskes and how much data to load in each was determined by trial and error, but we had good results with 12 flow tasks, and that could be related to the fact that this is a 24 core server and thus we had 12 threads for reading and 12 for writing.  In the end, we were able to load the 60 million records, which was about 120 GB of uncompressed data, into our empty table (primary key defined as identity column) in less than 30 minutes.

There are a few tests to run to fully vett this approach.  In particular, I want to test with a compressed target.  However, this approach is certainly viable and was a useful alternative to INSERT INTO…SELECT.