MSSQL - Nightmare on Broad Street

dalecosp

So, anyone have much experience with MSSQL server?

A couple of cases in point:

a] I talk with this IT guy about MSSQL server. His hardware: Dual Xeon 2.8's, 1+GB RAM, OS on RAID 1, db's on RAID5 array . . . should really smoke. "No other services on this box", he says, "I want it to be fast."

Me: "How many clients?"

Him: "2".

😕

b] I go to this office; they want a new server. They have MSSQL and it's slow, slow, slow; the box is Pentium 2.8, 1 GD DDR --- and there are only 5 clients in the entire domain.

I look at some performance graphs, and the thing is doing 100 locks/second.

I look some more, and I find that the server is replicating itself to another MSSQL server on every workstation in the building. :eek:

What is up with this cruft? With FreeBSD and MySql or PostGres I could handle this load on a PII/233Mhz and still have enough cycles for DNS or Sendmail. Does anyone manage an MSSQL server? Is it really this bad*?

*Yes b] sounds a tad like a ridiculously stupid implementation. I doubt it's any defense, but it's also the PDC (probably a 2nd mistake, from what I've been told). Off to the library, I guess....

Weedpacket

dalecosp wrote:
I look some more, and I find that the server is replicating itself to another MSSQL server on every workstation in the building.

Well, at least they don't have to worry about losing any data if the machine falls over.

But from what I have seen of MSSQL, it does like to be the only kid on the box. I know of more than one outfit that decided to migrate to it, and discovered this. Caveat: while I have often used it (always in this dedicated box configuration), I've never had to administrate it.

dalecosp

Weedpacket wrote:
Well, at least they don't have to worry about losing any data if the machine falls over.

😃 Indeed. And seems likely it will/does/has. I noted that it seemed to have been up for 10 days and the facilities director said, "Really?" 😉

But from what I have seen of MSSQL, it does like to be the only kid on the box. I know of more than one outfit that decided to migrate to it, and discovered this. Caveat: while I have often used it (always in this dedicated box configuration), I've never had to administrate it.

I've heard this too. And, it seems that moving MSSQL to a new box and stepping down the replication a tad would be the thing to do. Unfortunately, the bid was to "replace" the server, not add it to the farm, which means that I didn't include the cost of another license in my quote (and somehow, I'm doubting that an $89.00 copy of Win XP Home is gonna suffice --- open source wins again; or at least it would if their apps could run against something besides MSSQL).

Ah, well; 'tis a government agency; perhaps Bill && Melissa will have compassion . . . now, to figure out what the heck to do with the mess of wires on that rack ....

goldbug

Here at work, we run MSSQL as our primary DB system, and frankly, it's pretty solid, imho. Seems to perform resonably well, only ever becomes the bottleneck when we're requesting a seriously large data chunk, and it's only ever crashed once** in my recollection--granted, we're also running it on decent hardware, and clustered.

** The one time it did crash... it was VERY VERY BAD. A nice perfect storm where our backups for the SQL cluster also failed. We lost a few hours of data, and it was a dark dark week.

Only "annoying" problem I routinely encounter with MSSQL: when migrating databases to new servers, depending on the type of backup/restore detach/reattach praying/voodoo you use, the individual users assigned (granted) to each database can cause conflicts. Often the users will already exist on the box, or be copied over, but the internal binary ID for the user at the -server- level does not match the internal binary ID of the corresponding user at the -database- level. This causes the "real" login to not be valid; the "real" user does not show up as a user for the individual database; you cannot then grant the user privileges on that database, because it whines that they already exist (vestigal mismatch still living in the individual sysusers table). --this probably wasn't a terribly clear explanation 🙂
Luckily, I whipped up a handy-dandy script a while ago that automatically "fixes" the issue 😃

dalecosp

Heh, better than 50% chance I may be PMing you about that script within 3-4 weeks. 😃

Thanks for the info.

Doug_G

www.sqlteam.com is a good site for ms sql resources.

dalecosp

Doug G wrote:
good site for ms sql resources

TY for that.

Looks like I was mistaken about a couple of things...the workstations appear to be running MDSE, perhaps, instead of "full blown" SQL Server. (Give me a break, though, I was only there about 2.5-3 hours, and most of that was talking to the boss....)

I suppose that gives me something to look at regarding their issues. But, the contract was "build a better server and set it up", not, "spend a week analyzing what's wrong with the current system". :rolleyes:

And, this configuration (which apparently is a requirement of one SW vendor) still seems overly redundant to me. A "client-server/server" paradigm sounds a little funny to my less-than-well-trained ear. 😕

Ah, well....

Doug_G

afaik MSDE uses the same database engine as SQL Server with a couple limitations, there is a maximum db size in msde, there is a built in throttle that limits throughput when there are over 5 or so connections, and you don't get any client tools with MSDE. The 2005 version may be different, I hear there is a SQL Server Express or something now.

However, I can say SQL Server and MSDE has very good performance with properly setup db's and installed on properly running servers and networks. Proper table indexing can often work wonders on responsiveness. My guess is the replication you mentioned is probably the cause of the slowness. Typically replication is used to keep a "hot" backup server, or to keep a db in sync with a remote location.