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.
