Administration, Editorials, SQL Server

Spring (er, Fall) Cleaning For Your System

Recently, we’ve been going through our systems in anticipation of a move to a new SQL Server environment.  Partly because of the version upgrade, partly because it seemed originally like the right thing to do.  Now, after we’ve been starting to investigate further the process of figuring out the unused items, and the breadth of the issue, it seems even more like the right thing to do.

It’s not a simple task though – and there are a number of moving parts.  Part of the challenge is that, like I’m sure most of you, there are many things that are used all the time, and many more than are used infrequently.  But the killers are those that are used hardly at all, but are still important.  We’ve been faced with taking a step back and figuring out the various objects we want to work with, then figuring out how we determine the last time they were touched, and by what/whom/which processes.

It was inspired by this post over on Stack Exchange and the various referenced posts there about digging through DMVs and other tools to determine what’s important and what’s not.

We’ve had our share of fits and starts going through this.  Assuming we’d gathered the use cases and knew all of the things that touched other things, and been flat-out wrong.  Thank goodness for test systems and environments.  We’ve found a few solutions (we’re trying to do this with native tools now, to see if we can make sense of it all) that go a good part of the way there, but have been surprised by the inter-dependencies and the legacy code that is only used annually or less and things like that that make it a very muddy process.

More than once, we’ve stumbled into dependencies that span databases (and in some cases servers, and in one case, hosting providers) that opened up entirely new cans of worms.

At this point, it’s starting to feel like we could film a new episode of Indian Jones or some-such and have quite the treasure hunt on our hands.    It’s become a very dicey proposition.

Unfortunately (or fortunately) we have very few choices.  We have to make this move for a number of reasons and the clean-out, rather than just transplant, is the right thing to do.  I don’t have any illusions that we’ll miss *something* but I’m hopeful that it’ll be a minor issue that can be as simple as re-establishing a connection between systems when a feature breaks.

Have you had the experience of running through issues like this?  It’s one thing to identify an object type or single database that’s been touched in some period of time, but to historically go back through a large legacy of tables, many, many developers and database folks that have come and gone, formal and informal projects and all of that… it’s another to dig through the layers of dust that may just be important.

How do you approach these types of projects?  Or, alternatively, are you sitting back, grabbing the popcorn and pulling up your favorite foot stool to watch and take in the show while you snicker?  🙂

  • Kath Lomi

    I’ve always been a big proponent of cleaning as you go. Whenever something is replaced, it is required to figure out exactly what can be removed. Within 3-6 months after something has been in production, we then do the actual removing. I have some developers that have resisted this. One even said once, I don’t like to remove something just to remove it. But this is the exact reason it needs to be done. It makes for the muddy environment you speak of and that creates a LOT of work at upgrade/migration time. I think I finally have the nay-sayers convinced. But it is difficult to get the focus on it when the project work is happening. It is worth it.