I thought I'd share my experience with moving a heavily used production database for a live website from one server to another this weekend. The database in question is used to support AspAlliance.com, but since it has been around for a long time, and since getting additional databases has not always been easy or free, there are several other sites that rely on this same database. Additionally, on AspAlliance.com there are a large number of individual ASP and ASP.NET applications, many of which store their connection string information locally. I'm still not 100% done tracking down all the apps that need updated, but the important ones are done.
Why The Move?
The move was required for a few reasons, mainly centered around performance. The site's old db server was a shared box that was housing several dozen clients for my host, OrcsWeb, and I was using about 90% of the resources of the server, so it was time for me to be politely asked to leave. Also, my negotiations for hosting for 2004 netted me a dedicated database server, and moving to it would let me take advantage of its serious horsepower.
I worked closely with Scott Forsyth of Orcsweb. Scott is an AspInsider and general IIS and hosting guru. He also is one of the few people that sleeps as little as I do (though I'm not sure that's by his choice), and he has always been a great aid for me whenever I screw up my sites. We decided last week that the best time for the move would be late Friday/early Saturday, when traffic to the impacted websites would be minimal. We pulled some baseline performance benchmarks for the destination server (which was already handling all of the mailing list data for AspAdvice.com) so that we would be able to see how much this new load would impact the server. In the course of watching how the database performed on the shared server, we were able to observe, by Sql Server login, how many cpu cycles were used in a given time period. Using this information led us to an idea: since this database is used by half a dozen different websites, including several busy ones, it would be useful to know which ones were responsible for varying amounts of the total load.
Logging Performance By Username
Since we needed to update connection strings for all of the sites anyway, we decided that instead of using the same connection string everywhere, we would set up logins for each site. So we created logins like 'aspadvice.com', 'aspalliance.com', 'ads.aspalliance.com', etc. After testing that Sql Server didn't mind the '.' in the names, we decided this would work.
Flipping the Switch
Shortly after midnight Saturday morning, Scott took detached the old database, copied the files to the new server, and re-attached them. This process took about 5 minutes, during which time I was ftp-ing web.config files to the various sites to update their connection string information, and Scott was updating a couple of machine.config entries that held similar info. When the database came up, it didn't work immediately. We found that for some reason IIS or ASP.NET's connection pool was holding a connection to the old database but was trying to use the new uid. Each site needed to have its appdomain restarted. Another issue was that some sites had been using 'ssmith' as their user id, and some of the objects (tables and stored procedures) they were refencing were owned by ssmith. Now that they were using a domain name as their username, they couldn't view these objects, so we needed to change the owner of these objects to 'dbo' so that all users could use them. An old script I have (which David Penton originally provided to me) came in very handy, and allowed us to quickly switch all the important objects over to 'dbo' ownership.
Checking each site and making these db changes, as well as generally monitoring things and seeing how well the new server was performing, took us another hour or so. Once I was confident that all of the critical sites had been migrated, we set up a Sql Profiler on the shared server to record the requests that were still coming in to it so that I could track down the applications responsible and point them to the new database.
1. I've moved databases before, so having centralized connection strings was something I already knew the importance of. Having everything in web.config and/or machine.config files made this move a lot easier than it might otherwise have been.
2. Even though total downtime was only a matter of minutes, I still got a few concerned IM's from people about the site being down. I would love to have a better way to move a database from one box to another with less downtime. A tool that would allow one to copy files from a live database (without the need to detach it) would be helpful here, I think.
3. Having the right skills is very important. Some of the tasks required I didn't know how to do or had never done before, but Scott was easily able to accomplish. I was intimately familiar with my own applications, so I was able to quickly track down the needed configuration settings and change them myself or direct Scott to them. If either one of us had been novice or unfamiliar with the application, things would have been a lot hairier.
4. Use separate logins for different sites (and possibly applications) so that you can determine easily which users of your database are responsible for most of its load. I wasn't sure if the major contributor to the db's load was AspAlliance.com, with its 4M page views per month, or Ads.AspAlliance.com, which serves almost 50M advertisements per month. It turned out that AspAlliance.com was the major culprit, so now I know I need to work on optimizing its design further (it's quite db chatty at the moment).
Although sometimes you start out knowing you need a custom control, what more often happens is you find that you're using the same functionality in more than one place, so you start packaging it up into a control in order to remove duplication and improve reusability. This article takes a look at when and how you should do such things by taking a fairly simple piece of ASP.NET functionality and evolving it from some code on a form to a user control to a fully-functional custom web control.
Ok, so I got newsgator last week (http://weblogs.asp.net/ssmith/posts/41415.aspx) and today was the first day since then that I actually rebooted my machine and got the nag prompt to activate it when I started Outlook. So, having become enthralled with the product, I decided what the heck and spent the $29 to buy it. Got my activation key in an email a few minutes later, life was good, I clicked on NewsGator in Outlook and went to find the Activate link.
However, unless I'm blind, there is no Activate link. Sometimes they'll put it in the About section. Nope. Other vendors think it goes under Help. Nope. I even went into the Help to see if there was Help on Activating. Nope. Ok, fine, I knew it nagged me when Outlook started, so I restarted Outlook.
Voila - nag prompt for the Activation Code. I click 'Activate' and it asks for the code. Well, no problem, it's in an email - let me just... oh - the nag prompt is a modal window and I can't touch any emails while it's up. Nice. So I have to cancel again. Find the email again. Copy the key to Notepad. And finally restart Outlook again before I can register the product.
Note to the Newsgator people - if you want people to activate the product, make it a little easier to do so via a menu, rather than relying on your interruption-based nag prompts. Otherwise, though, great product so far.
I've never used Newsgator before but since I just redid the RSS feed on AspAlliance.com I thought I should give it a shot and see how it works (I've also only recently gone to Outlook from Outlook Express (see past archives for my thoughts on the switch)). I have to say I really like it and am starting to subscribe to a bunch of blogs that previously I would manually periodically check with my browser (I know, how archaic is THAT). So now, that thing on the side of my blog that says 'Blogs I Read' will actually be a bit more accurate, since I'll get notified of new posts to those blogs in semi-realtime.