Database Consolidation

April 12, 2008

We went through an exercise this week to combine three SQL Server instances into two new ones. The old instances were a combination of SQL 2000 and 2005 systems running on either x32 or x64 – the new instances are both x64, but we had to leave one at SQL 2000 because of a legacy application that doesn’t support 2005.

From my perspective as the development manager, this really wasn’t that big of a deal. With the exception of Microsoft CRM, all of our applications have their connection information centralized in their own config files. CRM is a little more annoying, in that you have to change a DSN and run a configuration utility. The utility is especially annoying as it requires a super-powerful account to run it properly. We also have a number of custom frameworks and applications that “hang off” of our CRM implementation, each of which required configuration.

The process, predictably, took longer than expected. There were occasional problems restoring the databases, and we weren’t really able to start the configuration effort until around 9pm. And then the problems started happening. Accounts lost their permissions in the restore effort. SQL 2005 has case-sensitive passwords, and mismatches between the config file and the password caused accounts to get locked out temporarily. Some config entries were missed or transposed. Overall, kind of a mess, and the configuration effort took until around 1am. I ended up leaving around 2:30, and the database guys didn’t leave until 4 – and they just went home and resumed their work remotely.

On Monday we will have a retrospective to try to think about how we learn from this. My focus may be on how we centralize our configuration management, but that’s a bit of a double-edged sword. Centralizing makes maintenance easier, but also creates a single point of failure if something bad happens. But there are of course ways to mitigate that. I’m just hoping that we can learn from this and make it a valuable experience.


April 8, 2008

Oh, how I hate you, Sharepoint. So, so much.

I’ve had nothing but problems with Sharepoint since the 2003 version. And I can’t say that it’s all Sharepoint’s fault. I probably just don’t know what I’m doing wrong, and it seems like we’ve had the three stooges of the Sharepoint contracting community in here to do things.

Our Sharepoint 2003 implementation was actually pretty stable, but for some dumb reason it used our domain account for the SQL Server Agent in some critical areas. It managed to plug along without any real problems, but we never really used it for more than a document repository.

Then along comes MOSS. We decided to upgrade in conjunction with our switch to Microsoft CRM 3 last summer. BIG MISTAKE. Not because of MOSS per-se, but because we couldn’t find anyone competent here locally to help us. One guy essentially disappeared in the middle of the project, and the next one decided to do a reinstall using his own domain account instead of any of the service accounts created for him. In addition, they both managed to completely under-spec the installation, leaving us with a solution that was hobbled due to a bad service account reference AND could hardly crawl its own content.

I found another Sharepoint resource and recommended it to our company as someone who could help reimplement our MOSS farm “for realz,” but the cost ended up being too high for our budget. It was too bad, because I’m certain those folks would have kicked butts and taken names. Luckily we did get some very good training in the process of trying to “smarten ourselves up” about Sharepoint, and we found a local person (a former employee) who was able to do what we needed.

So we have rebuilt our MOSS farm into a 3-server structure – one for front end, one front end for crawling and indexing and a back-end for the database. We have around 120gb of content in MOSS so far and it’ll just keep growing. Hopefully this reimplementation will work out, but as of right now I’m trying to determine if our interfacing systems – including the aforementioned CRM – can cope with the new MOSS structure. Right now CRM seems to be barfing in our custom callout that seemed to work in Staging, so I’m not certain what the problem is. Hopefully the developer will be able to get that fixed so that QA can finish and we can all go to bed…but that may be wishful thinking.

The problem is that I think MOSS is a really cool technology, and if leveraged properly it could really improve our ability to collaborate and interact. I just hope that our most recent iteration will work out – maybe the third time will be the charm.