About a month ago my client’s ISA server crashed. The I.T. guys discovered 30 GIGs of pictures and movies (mostly in german) on the box. Oops! So they had to rebuild that box and (I can’t even remember why) but also determined that they really needed to rebuild the server that is the webserver and sql server and I think also domain server. That box has all kind high tech RAID stuff on it and they want to continue using it.

Therefore we are temporarily moving over to a new box and I have to get everything set up for the move. But it’s not a copy and paste job. No sirree. The old box has Windows 2000 server and SQL server 7 and IIS5. Of course, we are taking advantage of this opportunity to get the client finally up to Windows 2003 and SQL 2000. Hooray.

Now I did this here in my little home office recently and knew enough to be dangerous, and enough to be afraid. Though I know all about the Network Service account on IIS6 now! 🙂 

But this has not gone easily or well. I have had all kinds of crazy permission problems which are not as easy to debug with things like web services on a production server. Then I got to my old rubber band and duct-tape application role in SQL Server. I have a major VB6 app in place there that uses ADO and a lot of SHAPED recordsets. I talked extensively with a lot of SQL folks before going with an Application Role to secure the database a few years ago (I was not terribly experienced with SQL Server admin). But I managed to get everything working and even worked around the problems with the odbc connection pool that are created with the application role.

However, on the new box, I was having huge headaches with my connections and finally decided it was time to kill the application role and just bite the bullet and create a sql server account that will only be accessed (one hopes) by that application. That turned out to be a really good idea. It took 1 minute to create the new login and 30 secnds to change the connection strings used for the app and then about 15 minutes to click all over the app to see that it worked. And now I don’t have to disable the connection pooling any more.

Here is what I think is the A#1 reason I have been through this hell.

No documentation on the database setup!

I have been creating this monster in bits and pieces over the last three years. Setting up the database stuff was just a way to get my apps working, so I was not as focused with it as I am with my applications. I often knew nothing about what I was doing and had to learn under fire (like most of us do…) A lot of it was experimentation. As I learned more, my solutions improved. However, there is a really nasty mix of the old and the new. Plus a lot of my experiments with permissions, logins, users, etc are still in there.

Attaching the database was not all that was necessary to do the move because of the fact that there are SO many asp.net apps and web services relying on the different databases and many many many stored procedures. And IIS6 has the Network Service account replacing my IWAM_server account. I had to recreate all of those permissions. (There is probably a nice little t-sql way to do that instead of manually). So the lack of documentation (or heck a better naming convention) of what sprocs belonged to what apps (of course there are a lot of shared objects) and which users needed what permissions for which objects..etc etc etc.

I have been taking very good notes through this whole process which I will organize after I get through this. But it has not been a lot of fun. I would MUCH rather be playing with VS2005, my tabletpc, my blogging application, my dog or possibly even doing the programming work that this same client is waiting for me to complete.

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!