SQL Server Express: Small Work Group Sans Server?

  • Thread starter Thread starter (PeteCresswell)
  • Start date Start date
P

(PeteCresswell)

Extrapolating from David W. Fenton's comments in the "Rules of
thumb" thread, it seems to me like there is an argument for
making most new MS Access projects use a SQL Server Express back
end - even if they just go at it via ODBC links the same as they
would against a .MDB.

Given that, and given that at least some projects' reason for
being is tb independent of an IT organization, what would the
downsides be of implementing a work group's SQL Server Express
back end on a plain old Windows XP PC right in their physical
area instead of becoming a tightly-controlled tenant on one of
IT's servers?

The first issue that comes to my mind is backup. I'm guessing
that the tools are there within SQL Server - but am otherwise
clueless.

Other issues?

Comments?
 
My opinion is that if the requirement can be met by the Access product, then
there is no need to go to sqlserver.

The fundamental question is whether or not the need can be met by Access or
not.
 
Per GBA:
My opinion is that if the requirement can be met by the Access product, then
there is no need to go to sqlserver.

The fundamental question is whether or not the need can be met by Access or
not.

I don't know enough to comment in detail, but over the past few
years - in the environment I've been working in - I've seen
across-the-LAN performance of several applications go down the
tubes.

Move the DB to C: and they speed right back up to where they
were.

Even my personal timekeeping app. Runs like a flash when
everything's on C:. Runs almost as fast when the .MDB back end
is on my Windows Home Server box. But take it into my work
environment, put the back end on a file server... and it becomes
painfully slow.

Didn't used to be that way 4 years ago.

What's going on? Dunno... but my first guess would be LAN
traffic combined with how they're implementing the file servers.

Certainly they've moved from hardware PCs (one box = one server)
to virtual PCs on some humongous mother computer.

Also, when I walk around the place, it seems like very third PC
is running full-motion video of something-or-other.

In that context, SQL Express seems to promise a little more
control by limiting LAN traffic and letting me config a dedicated
box and throw money at it until performance is good enough.
 
Per GBA:

I don't know enough to comment in detail, but over the past few
years - in the environment I've been working in - I've seen
across-the-LAN performance of several applications go down the
tubes.

I have not seen this at all, not in any of my client installations.
Move the DB to C: and they speed right back up to where they
were.

My clients are all small businesses, most of them with just
peer-to-peer networks and no server at all (even a workstation
serving as a server). They don't have virtualized servers running on
huge big iron servers, nor are they part of a huge networking
infrastructure where their segment of the network is only part of a
huge network.

Your description of your working environment sounds like your
clients are large corporations that virtualize their Windows servers
and underprovision them in terms of sharing CPU cycles. Likely, they
underprovision their network infrastructure, as well.

But from where I sit, what you describe is quite exotic and unusual.
Even my personal timekeeping app. Runs like a flash when
everything's on C:. Runs almost as fast when the .MDB back end
is on my Windows Home Server box. But take it into my work
environment, put the back end on a file server... and it becomes
painfully slow.

Then whoever is in charge of your work network environment is
completely incompetent. Period. End of statement.

Well, that, or too cheap to live, which is really another form of
incompetence.
Didn't used to be that way 4 years ago.

What's going on? Dunno... but my first guess would be LAN
traffic combined with how they're implementing the file servers.

Most LAN traffice is web and email, and that's really low usage in
comparison even to Word or Excel, let alone Access. In 1998, I
worked in an office with about 20 people sharing a 64KB fractional
T1. That's right, about twice the speed of a dialup modem.

And until Napster came along, it was fine. We had to ban Napster in
the office, but shortly thereafter a different Internet provider
took over servicing the building and we got a T1 for almost nothing
(it was cheaper than had previously been paid for the 64KB frac T1,
but in those days, pricing was outrageously high for significant
bandwidth), and then there were no issues.

All that said, never had there been issues on the local LAN, with a
fairly heavyweight Access app being used by about 10 simultaneous
users hitting a well-provisioned (for the time) NT server
(dual-processor, 266MHz, lots of mirrored SCSI disk space, though I
can't recall how much -- 20GBs? This when workstations still had 2GB
hard drives).

I've got clients with much better infrastructure today, but not
really hitting it any harder, and I don't see much of a degradation
in network performance. I *do* see degradation in workstation
performance, but I think that's mostly do to the anti-virus tax
(whcih might very well be mostly to blame in the environments you're
encountering issues).
Certainly they've moved from hardware PCs (one box = one server)
to virtual PCs on some humongous mother computer.

Also, when I walk around the place, it seems like very third PC
is running full-motion video of something-or-other.

In that context, SQL Express seems to promise a little more
control by limiting LAN traffic and letting me config a dedicated
box and throw money at it until performance is good enough.

You live in a very exotic world in comparison to me.
 
Given that, and given that at least some projects' reason for
being is tb independent of an IT organization, what would the
downsides be of implementing a work group's SQL Server Express
back end on a plain old Windows XP PC right in their physical
area instead of becoming a tightly-controlled tenant on one of
IT's servers?

Well, the workstation will be limited to 10 connections, but I think
so is SQL Server Express. I dunno, I usually push for a full
dedicated box running Windows Server. It's pretty cheap to provision
one with plenty of disk space and RAM for under $2K these days.
Proper server-level backup software is expensive (and problematic --
I've never seen enterprise-level backup software that was anything
other than incredibly hard to wrestle into submission and keep
running reliably). Because disk space is so cheap, you could go to
disk mirroring for fallover and overnight disk imaging to a large
RAID array (using cheap drives you can put together several
terrabytes for as few hundred $$). For proper granularity and ease
of recovery you'd still need some kind of backup software for
versioning (or all you'd have is the latest backup, which is a bad
thing when you need to restore a file from last week).

In short, you'll probably spend on backup a significant fraction of
the money you spend on the server itself. At least, that's my
experience.
The first issue that comes to my mind is backup. I'm guessing
that the tools are there within SQL Server - but am otherwise
clueless.

It can be scripted, but usually hot backups are done with
server-aware backup software. I forget the name, used to be Arcsoft,
or maybe it's Verbatim, but they all come with hooks into SQL Server
and Exchange Server so they can do hot backups.

In regard to your main question, I wouldn't use SQL Server until
there was an actual need for it. But I've been building my Access
apps with client/server optimization built in from the ground up
(insofar as that's possible with a Jet back end), with upsizing in
mind from the design stage on. I spent several years trying to get
some clients to upsize, but their Jet apps worked so well, they
couldn't see the benefit (in two cases, they had Small Business
Server already, so they'd already paid for SQL Server!), and I
didn't do an upsizing until 2004. It went swimmingly and vastly
exceeded my expectations in terms of ease of implementation. The
client saw some significant performance increases in a few places
and they were pleased. I've done other since, and most have gone
smoothly, such that I have never had to learn much at all about
T-SQL, since most things just work. With the exception of building a
few server-side views, converting a few operations to use
passthroughs, and create an sproc or two, I haven't had to do much
at all.

On the other hand, in one case, the client ended up with worse
problems after the upsizing than before, because (as it turned out)
they had misconfigured DNS on their LAN, and this was causing them
to drop their ODBC connections to the SQL Server, with the result
that they had never encountered DISK OR NETWORK ERROR until the
point after which they had upsized to SQL Server! The network tech
was clueless, and I researched it and suggested he look at DNS
configuration, and he ignored it for two months, and then I
suggested it again, and he checked it this time (because he'd been
getting lots of heat from the client), and lo and behold, it was a
DNS problem all along. I didn't tell the client that they'd gone
through two months of this that they hadn't needed to, since the
network tech was the guy who hired me for the Access project.

Anyway, I'm rambling at this point. That's just my experience, FWIW.
 
across-the-LAN performance of several applications go down the

Packet Signing was a big hit. IT should run separate file servers and
domain controllers, and should turn that off for file servers, but they
won't, because they would rather have you on their SQL Server.

But if you are talking about dedicating your own box, you might
be able to do that, depending on how Group Policy is set.

SMB "optimisations" in later versions of Server are responsible for
another big hit. In particular, the loss of NetBUI/NetBIOS, and
changes to small packet handling. (These changes make very very
large networks with routing and lots of workstations work better,
at the expense of even ordinary large companies with typical work-
group sized workgroups.) It would be very difficult if not impossible
to "fix" this other than by going back, perhaps as far as NT Server 4,
and even I wouldn't advocate that.

Any way, at present our SUS Server (Microsoft Update) at work
is off line, and has been for 5 months. To get it work again, we
are supposed to re-install SUS, because the Master Database
has been corrupted by a UPS failure. Back-up for SQL Server
is only a little bit complicated, but even that little tiny bit is the
difference between 5 months for SQL Server and 1 day for files
I backed up myself. Your mileage may vary.

(david)
 
(PeteCresswell) said:
Given that, and given that at least some projects' reason for
being is tb independent of an IT organization, what would the
downsides be of implementing a work group's SQL Server Express
back end on a plain old Windows XP PC right in their physical
area instead of becoming a tightly-controlled tenant on one of
IT's servers?

None, if you can install SQL Server Express without the IT departments
blessing of course.
The first issue that comes to my mind is backup. I'm guessing
that the tools are there within SQL Server - but am otherwise
clueless.

Yes, backup can be quite simple and is a matter of a few SQL Server
commands which can be scheduled to run at a specific time of day.
Then the file produced can be copied to a file server elsewhere for
backup.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
David W. Fenton said:
Well, the workstation will be limited to 10 connections,

For file and printer sharing yes. SQL Server Express? Who knows.
but I think so is SQL Server Express.

MSDE was throttled to five connections and any more had to wait until
one of the other connections was finished. There was one person who
stated he had 75 Access users on an MSDE install. SQL Server
Express 2008 has memory and database file size limits but no
connection limitation
I dunno, I usually push for a full
dedicated box running Windows Server. It's pretty cheap to provision
one with plenty of disk space and RAM for under $2K these days.

Or possibly Small Business Server.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
Per David W. Fenton:
I spent several years trying to get
some clients to upsize, but their Jet apps worked so well, they
couldn't see the benefit

Funny thing: My latest app began as "Hey Pete, Daisy's
spreadsheet is getting out of hand. Can you write a little
Access app to replace it?".

Within three months the "little access app" had morphed into a
mission-critical fullblown bond trading system with a dozen
concurrent users.

About six months ago, I went to the Powers That Be and told them
that this app had gone far, far beyond a single caffeine-crazed
contractor and that they needed to bring in an IT-blessed team to
do the job right: DotNet with a SQL Server back end.

Turnover was on the first of this month. Before turnover, I was
poking around in the new application (with back end on SQL Server
Express on the developer's PC and several other
developers/testers logged in at the same time) and it was
blindingly fast - even faster than my old app with back end on my
C: drive.

I thought "Wow, my old clunker of an app is really looking bad".

A week later, I had the chance to spend almost an hour working
side-by-side with one of the traders who was using the new app -
since elevated to a production server.

The main complaint? "This new application is soooooo slow. Yours
was much faster."

Go figure...
 
Having read everyone's comments so far (all good and helpful...) but can't
help but go back to what seems to be the original premise which is a
presumption that Access will be too slow and thus one should go to an
sqlserver/.net design...and for this I can't necessarily agree.

You mention that the environment is blade; which only eliminates the desktop
PC hardware to centralize them in one location for simpler
maintenance....this doesn't impact speed.

You mention video - which definitely will compete for LAN bandwidth.

Yes, sqlserver architecture has the query in the back end resulting in less
data volume pushing to the client...

But I'm just not sure one really can make a broad statement that inherently
Access is going to be too slow - due to Access' architecture........ the
volume of data involved in an Access db is a nit compared to video....such
that the relative difference between the sqlserver architecture vs Access
architecture may not be relevant to the overall LAN congestion.

Which is to say if Access was going to represent 2% of the LAN data - -
-moving to sqlserver in order to only represent 1% of the LAN data isn't
compelling logic. There is and can be compelling logic to go to sqlserver -
don't get me wrong - but in this dialog it seems to be revolving around LAN
speed - - - and on this aspect I am not in agreement.

The networking guys, depending on brand & vintage routers, can prioritize
packet thru put by machine, or even application. This would have a big
impact on Access user speed...plus of course whether or not the server of the
BE is bogged down with other stuff.....

I guess all I'm theorizing is that LAN slowness strikes me as not the normal
reason to favor sqlserver over Access...
 
For file and printer sharing yes. SQL Server Express? Who knows.

It's a server restriction. Both workstations and Windows Server have
a server service that takes care of file and print sharing. That is
limited to 10 connections (I think based on machine, but I could be
wrong on that), and that will include SQL Server, in any version.
MSDE was throttled to five connections and any more had to wait
until one of the other connections was finished. There was one
person who stated he had 75 Access users on an MSDE install.
SQL Server Express 2008 has memory and database file size limits
but no connection limitation

I didn't know that. Good to know.
Or possibly Small Business Server.

Nah. None of my clients need Exchange, nor do they want it sucking
up all the CPU cycles for their domain controller/file/print server.

I've never thought SBS was all that good a buy, really. It's loading
too much on one box, in my opinion. Better to have more than one
cheap machine serving limited functions than having it all piled on
one machine that has to be overprovisioned just so it's not brought
to its knees by Exchange.
 
Which is to say if Access was going to represent 2% of the LAN
data - - -moving to sqlserver in order to only represent 1% of the
LAN data isn't compelling logic. There is and can be compelling
logic to go to sqlserver - don't get me wrong - but in this dialog
it seems to be revolving around LAN speed - - - and on this aspect
I am not in agreement.

My guess is that the slowness has zilch to do with the LAN and
everything to do with the file system the back-end data files live
on, and the servers serving that data. I don't want any of my apps
having their back ends on anything but a native Windows file system
served up by native Windows SMB (i.e., no Novell, no Linux/Samba). I
know many people report good results, particularly with Linux/Samba,
but I just don't trust it. Jet is too tied to the file system for me
to trust it to anything else.
 
I didn't know that. Good to know.

Microsoft didn't do themselves any favors by using the "number of
connections" limitation for MSDE. That limitation itself was
short-lived (Express replaced MSDE over four years ago) but the belief
in it continues on.

Here's two SQL edition comparison charts. As you can see, the Express
versions have limitations, but nothing to do with simultaneous
connections.

2005:
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

2008:
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
... it seems to me like there is an argument for
making most new MS Access projects use a SQL Server Express back
end - even if they just go at it via ODBC links the same as they
would against a .MDB.

Well, we take that approach in our shop. Most of our Access FE
applications use a SQL Server BE. But there are techniques that
should be followed for minimum network traffic and best performance.
And there are a few must-have approaches just to make it work, like
determining an autonumber/identity key after adding a record in code.

I've written a PowerPoint presentation on techniques for using Access
as a client-server front-end to SQL Server databases. It's called
"Best of Both Worlds" at www.JStreetTech.com/Downloads. It includes
some thoughts on when to use SQL Server, performance and security
considerations, concurrency approaches, and techniques to help
everything run smoothly.
Given that, and given that at least some projects' reason for
being is tb independent of an IT organization, what would the
downsides be of implementing a work group's SQL Server Express
back end on a plain old Windows XP PC right in their physical
area instead of becoming a tightly-controlled tenant on one of
IT's servers?

As others have said, this "SQL Server Under My Desk" approach would
work for non-mission-critical applications. Hmm, SSUMD... I think I
just coined a new acronym! :)
The first issue that comes to my mind is backup. I'm guessing
that the tools are there within SQL Server - but am otherwise
clueless.

SQL Express doesn't come with an automatic Agent. See the Enterprise
Manageability section at:
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

....but automated backups can be set up other ways. See this article
at FMS:
http://www.fmsinc.com/freE/NewTips/SQL/SQLServerExpressDatabase/Automated_Backup.asp

Cheers,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Per Armen Stein:
As others have said, this "SQL Server Under My Desk" approach would
work for non-mission-critical applications. Hmm, SSUMD... I think I
just coined a new acronym! :)

I like it!
 
Microsoft didn't do themselves any favors by using the "number of
connections" limitation for MSDE. That limitation itself was
short-lived (Express replaced MSDE over four years ago) but the
belief in it continues on.

Here's two SQL edition comparison charts. As you can see, the
Express versions have limitations, but nothing to do with
simultaneous connections.

2005:
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

2008:
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx

I was just at those charts on Friday because I'm helping a client
upgrade SQL Server for a 3rd-party app (they have 2000 Standard) and
the vendor is claiming that have to upgrade to 2005 "Full" (which is
nonsense, as there's no such version). I instructed the client to
grill the vendor on exactly which features their app uses that
require 2005 and prevent use of 2008 (2005 is four years old
already, after all), and which features their app uses that are
unavailable in either 2005 Express or 2008 Express.

I suspect this is one of those "voodoo" recommendations in that they
don't have any good reasons for restricting to 2005 Standard (which
is what I assume they mean), except that they want to avoid
supporting anything else. This kind of thing suggests to me that the
developers of this app don't really have a handle on what they are
doing in regard to abstracting the database layer from their
application layer. It really ought to be architected the way
BlackBerry Enterprise Server is designed, which is that any version
of SQL Server from 6.5 up is fine for your BES data store. That
means they are using the most generic data types and the most
generic T-SQL and the most generic scripts for creating their
database.

Tying your application to a particular version of SQL Server
indicates to me that somebody is being rather narcissistic.

For a non-profit (which is the case here, though the 3rd-party app
is for tracking sales of the things this non-profit sells), the
difference between standard and express is signicant. And asking
them to pay to upgrade to 2005 standard at the end of 2009 with the
potential that sometime soon they'll have to pay again to upgrade to
2008 standard annoys the hell out of me.

Anyway, I've wandered off topic...
 
I suspect this is one of those "voodoo" recommendations in that they
don't have any good reasons for restricting to 2005 Standard (which
is what I assume they mean), except that they want to avoid
supporting anything else. This kind of thing suggests to me that the
developers of this app don't really have a handle on what they are
doing in regard to abstracting the database layer from their
application layer. It really ought to be architected the way
BlackBerry Enterprise Server is designed, which is that any version
of SQL Server from 6.5 up is fine for your BES data store. That
means they are using the most generic data types and the most
generic T-SQL and the most generic scripts for creating their
database.

More likely, it's the version they develop with and they don't want to
bother testing it against a lower edition or different version.
Tying your application to a particular version of SQL Server
indicates to me that somebody is being rather narcissistic.

Or its related adjective, lazy.
For a non-profit (which is the case here, though the 3rd-party app
is for tracking sales of the things this non-profit sells), the
difference between standard and express is signicant. And asking
them to pay to upgrade to 2005 standard at the end of 2009 with the
potential that sometime soon they'll have to pay again to upgrade to
2008 standard annoys the hell out of me.

Do they take advantage of non-profit software pricing? Check out
www.techsoup.org. As an example, I see SQL Server 2008 Standard
listed there for $240.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
More likely, it's the version they develop with and they don't
want to bother testing it against a lower edition or different
version.

The answer came back today that it was something about encryption. I
think they are probably lying.
Do they take advantage of non-profit software pricing? Check out
www.techsoup.org. As an example, I see SQL Server 2008 Standard
listed there for $240.

That's where they're buying from, yes.
 
David W. Fenton said:
The answer came back today that it was something about encryption. I
think they are probably lying.

No, no, no. The client is talking to the vendors marketing group..
They're not really lying. Just ordinarily incompetent.

Hint - see Dilbert.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
No, no, no. The client is talking to the vendors marketing
group..

Actually, they were talking with a developer who helps coordinate
upgrades, not with the marketing folks.
They're not really lying. Just ordinarily incompetent.

Hint - see Dilbert.

They gave "encryption" as one of the differences between SQL Server
2005 Standard and Express. But this feature matrix:

http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

....affirmatively says that the encryption is identical in both.

The client has already ordered 2008 standard (they can't buy 2005,
so will have to downgrade; at least they will have 2008 ready to
roll out at the point that the vendor next forces them to upgrade in
2012 or so), so it's a moot point by now, but it really irks me.
 
Back
Top