"Wait, why do you need to shut the SQL Server down?" Henry O. didn't like where this conversation was going. "We've got a lot of people using the database right now. I'd really like to do this upgrade without shutting it down."

"Well, we have to shut it down," Tom, the vendor, reiterated. "Because... because."

"Seriously, why?" Henry demanded.

"Well, this is our software. I represent Initrode Global, and I know how our software works. We just have to shut the server down for a little bit." Tom tried to sound confident.

Henry thought back to when they first hired Initrode Global. Years ago, they'd asked for a warehouse management system, and aside from a few minor annoyances with the software, they were happy with it. As the company grew, though, they were reaching the limits of what they could do with the Access 2000 backend and asked Initrode to upgrade the software to work with their new SQL Server database.

Meetings with Initrode were awkward. This was the first time they'd heard of anyone hitting the limits of the software, and it made them nervous. Still, they swore they'd get their best people on it, and upgrade the application to .NET/SQL. Several months and meetings later, they were finished.

All of this work resulted in Henry's current problem. Trying to get an answer from Tom about why the databases had to be taken offline.

"It'll take maybe five minutes," Tom insisted.

"Fine." Henry caved.

Tom began typing and clicking, closing all open connections and taking the server offline. "Oh, wait," Tom said, as a realization dawned on him. "I guess I didn't have to do that. I'll start up the database again; it'll be back to normal in a minute."

Henry gritted his teeth. They'd just kicked all of the users off the database for no good reason.

"Yeah, as it turns out, the tables I need are already there!" Tom chuckled nervously. "Still, I'm going to replace the database." Henry kept a close watch over Tom's shoulder as he worked.

"OK," Tom said with a proud smile. "The tables have been created! Now I just need to run the import script!" Tom ran a custom-developed console application called "import.exe." It didn't take in any parameters, produce any output, or have any progress indicator. He and Henry sat there for about five minutes, before Henry asked if any progress was being made. "Probably," Tom replied. "I mean, almost definitely probably."

Henry took over the keyboard so he could check rowcounts on the tables while the import app was chugging away. F5-ing his query a few times, he saw that the tables were growing, just really slowly. After a half hour with only two tables populated, he figured that a custom-developed import application might be slower than using the Import Data wizard. And he was right — he managed to get everything imported in three minutes.

With the data copied and the .NET application ready, testing could begin. And testing began right around the same time it stopped, since it was erroring out immediately. First it was some obvious stuff — incorrect configuration, an invalid connection string, etc. — then they started hitting more complex issues.

First, there were permission denied errors for dropping a view. "Hey, Tom, why is this happening?" Henry hadn't done anything he thought could've caused a problem; he just tried running an inventory summary report. "Why would this report try to drop a view?"

"Oh, make the user that connects a database owner. We ran into that too."

"Uh, why does it need to alter the database when I run a report?"

"Because," replied Tom, "...because."

"No, I really need a solid answer this time."

After some pressing, Tom finally explained why the system needed to create and drop views and outlined the workflow:

  • Each request is queued by a central request server on another machine that ensures only one request at a time is served
  • The request server sends a command to the database server to drop the current view
  • The system recreates the view with parameters changed
  • The new view is used by three other views to display data to the user
  • Repeat for every single request, with just one parameter changed in the original view

As soon as Henry could speak, he asked why Tom didn't just use a stored procedure to do the work.

"A 'stored procedure?' Oh yeah, I think I heard of that somewhere..." Tom began flipping through a dog-eared SQL reference that more resembled Indiana Jones's dad's notebook. "I'll have to try that, there's this big section in here about them!"

There were eight other views used like this, in a system that's queried thousands of times daily.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!