Windows: June 2006 Archives
Some of you know that I admin a nice, small business windows shop. Today (right as I'm typing this, actually) we're upgrading the sole app that keeps us on windows. However, this app in question is being updated to use SQL server instead of access. Hard to complain, right? Moving from access to an actual database.
To upgrade, I'm using a java-based remote administration tool with a guy in North Carolina. The tool itself is rather nice, I can allow/disallow access to any window in specific. Plus, it has integrated file transfer, so he could copy over the access databases and analyze them. Useful little thing.
However, as I'm watching him do the upgrade, I notice things. For instance, he creates the databases manually, then runs .sql scripts in order to fill the tables. Is there something horribly wrong with throwing in a "CREATE DATABASE Blah" in the script? It's not like you can't manage the permissions through SQL either, and frankly, MSSQL uses T-SQL for a reason. Other little things too, like creating some new directories three deep with three root directories, then systematically doing a backup into those folders. Shell scripts were invented for a reason, and hey, even window's cmd.exe is capable of basic file manipulations (and it's actally capable of quite advanced file operations, some of which are just plain dirty/tricky/ugly/etc. I'll have to post a script that I know affectionately as the "Cow Virus" later).
All right, I'm now offically scared. We had just under 200,000 rows of data stored away in access. 200k rows! Access! Scary!
Now he's running a script which is renumbering all 200k rows. On the flip side, we're in SQL server now, instead of Access. Of course, it's still taking a while to complete. We don't have a crap server (3.0GHz, 2GB DDR2 EEC, SATA RAID), but in the words of a very wise person on IRC: "Active Directory is to system resources as Joseph Stalin is to human rights." Let's just leave that as that.
Well, it's finished. Suffice it to say, when users hit the "search" button, it's several orders of magnitude faster. Oh, and the total row count that was stored in access (no more!)? >600,000.
