Summer in Medora - the Nitevision episode

By aaron.axvig, Wed, 08/22/2007 - 03:00

Nitevision: a name which strikes fear into the hearts of many a Medora call-center workers.

To be fair, it's not that bad of a program.  It's what Medora has used for motel reservations for many years.  Written by REMco Software out of Dickinson, ND, Nitevision is a client/server application which keeps tracks of motel reservations, who is checked in, which rooms are clean, etc.  As near as I can tell the client sends raw SQL queries to the server which then spits back some data for the client to display.  A workable strategy, I think.  However, there are some problems.

I started working in Medora's call center at the beginning of the summer of 2005, as a lowly Customer Service Representative (don't be fooled, I have really really really enjoyed all of my jobs in Medora).  I vaguely remember how the Nitevision server had to be restarted quite often because all the clients on the workstations would simply lock up.  I remember more closely how this also happened in the summer of 2006 when I was a team leader, supervisor of CRSs.  At one point I was even trained in as to how to restart the server because the IT guy wanted a day off.  I don't think I ended up having to restart it, but the Internet connection did go down at one point while he was gone, which is another story on its own (credit card processing requires an Internet connection).  Anyways, the restarts were so frequent that Nitevision got its own server so the ticketing system could stay up while it was rebooting.

Enter me (again), in the summer of 2007, as the IT assistant.  Now instead of crossing my fingers in hopes that it didn't crash, I had half of the workers asking me why Nitevision crashed on them all the time.  I didn't really know, but us two IT guys spent a lot of time thinking about it.  Many hours were spent on the phone with REMco support, and they even remoted into the server to delete some rows in a logging table that looked like they were taking a lot of space.  The problem went on though, with crashes becoming a daily occurrence, and often-times hourly during busy times of the day (early morning: lots of reservations, and mid afternoon: lots of checkins).  We poured over all the diagnostics we could find: CPU usage, RAM usage, HDD activity (which is actually difficult to monitor), network activity, and the Event Viewer.

Finally I cracked open the SQL Server logs.  I should have done this sooner, but SQL Server Management Studio wasn't installed on the server and I didn't have it on my desktop.  When I got it installed on my laptop though, I found the following error message repeated tens of times in the minutes leading up to each server crash: "This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by xx queries and performance may be adversely affected."  xx would be a 1 or 2 for about 20 minutes (always spaced evenly exactly one minute apart) and then it would jump to 20 or 30 for the last few minutes before the crash.

Shortly thereafter we discovered that the server was running the Microsoft Data Engine, better known as MSDE, also well-known for being limited to 8 concurrent queries.  We have 10 call center computers, 6 front desk computers, call accounting, online reservations, accounting staff, and 3 group sales computers fighting for database access. REMco would not really acknowledge that this was the problem, and it's quite possible that they had no experience with this scenario, because judging by a list on their website of their customers, I suspect that we are their largest.  In the end though they did decide to help us move to a trial version of full-blown SQL Server 2000.

Migration day was quite exciting.  I arrived for work at 1:00pm to discover that they had taken down the server at 10:00am to start the migration.  And it still wasn't up.  I found a number of funny things going on:

  • They had backed up the databases and were then restoring them.  One backup was corrupted, and they were going to restore to the backup made during the night, losing an entire morning of new reservations.  So I taught the REMco tech how to detach and attach a database.

  • They were using "SQL Editor."  I had seen this tool before on the Nitevision server.  It seems like some watered down version of Management Studio.  I suspect the tool does not have functionality for attaching and detaching databases, which may be why they weren't doing that before.  I don't think it supports Windows authentication either, because they weren't able to connect to the new database engine...and that's because...

  • They installed the new engine with only Windows authentication.  Yes, the entire Nitevision program runs using SQL authentication.  SQL Editor uses SQL authentication also.  Upon pointing this out, it seemed that it wasn't merely an oversight on their part.  Rather, I think they genuinely did not know the difference between the two authentication methods.

We finally got the thing running around 3:00pm.  Since then it has only required reboots every other week as it gradually begins to more frequently freeze up for 30 seconds at a time.  End result?  Nitevision humming along acceptably, except for some annoying accessory apps running on the server that are poorly setup.  I'll elaborate on them some other time...along with several other interesting stories as I remember them.