For the tech guys

If you are involved in the information technology scene you can read this, if not please jump to the funny part below.

I am a software developer at a relatively small company, and this weekend my company is migrating (although migrating does not seem to describe what we are doing) over to a SAN. Our company has lots of manual data entry. The data entry screen is what I maintain and update. We run a visual FoxPro front end and a SQL server backend for the data entry system (SQL server is fine, but who ever decided to use fox pro for the front end is not to bright) . We presently have around 20 clients that run on this current system, each client has their own database. For around three to four years we all our clients were on the same server and been growing our client base.

Our current configuration has worked well, but has some major draw backs. If one user for client X starts a major process, it uses up the pooled resources (CPU's and disk IO's) and affects other clients. The nature of the company is that large processes, which move very large amounts of data around start regularly, so the entire company was being affected negativity.

Our company decided to purchase a SAN and several servers. So we went from having all our databases on the same server to having the databases spread out among several servers. Some clients have their own server, other smaller clients are grouped together on the same server. All servers are connected to the san through fiber. All servers are under one subnet with the san.

EVERY SINGLE piece of code that this company has which touches SQL server had to be inspected to insure: a ado connection was built properly and that any of the client queries use the linked server names (if applicable).

Connections must be built dynamically because databases can be moved at a whim ( in order to balance CPU usage on the servers and IO's on the SAN). To build connections a table, which contains databases and server names for each databases, is pulled down by connecting to a server and performing the query to pull down the table. The servers are numbered, so you start at one and loop through until you get a connection.

Any queries in the client code must be checked to see if there are any cross database queries. While there are never any cross database queries between clients, there are databases which contains metric information and application information for every single client database, and many times queries from client access the metric data.

Their are several things my company has done correctly, and their are several things my company has not done so well. I fell our soft ware department(s) has done ok,but our infrastructure department seems a little weak.

We had the servers we purchased in test. The servers were built, windows server 2003 and SQL were installed, and one of our brand new servers kept crashing for no reason. That server had every single part replaced and still was crashing. We swapped databases with another server, and it still kept crashing. Dell had a lemon clause and they are working building that server right now. So we are going live with one less server than we wanted. Which in theory will be a good exercise to make sure everyone has updated their code correctly. We will move the databases that belongs on that server when the new server comes in, though I do not know how it is going to work in practice. Secondly, on the infrastructure side we don't have active directory and we really needed that today.

On the software side, I do not thing this idea of going to linked servers was researched very well. Yes it works, but Microsoft only suggests going to linked servers after building up as far as you can. Which we did not do that. Also linked servers are very inefficient,large amount of data is moved between servers, but that is something that can be worked around in most cases.

If you have every worked with linked servers in SQL or for large scale database, I would love to hear about you experiences.

FUNNY PART

I arrived at work at 7:30 am today (Saturday). I have been so busy the last two weeks I have not washed clothes. Today all I have left are whities tighties. Today is going to be a long sucky day.

cube