Using SQL Server (Express?) As If It Were A .MDB?

[PeteCresswell]

I'm coming around to thinking there is a third area between a strictly-
local-non-mission-critical JET-based application and the classic
mission-critical application where SQL Server or some other "real"
client-server DB is manditory.

Downsides I see to going fullblown SQL Server:

1) IT now manages the DB and ad-hoc changes, as they should be, are
impossible.

2) It now manages the DB and what's best for the client is not
necessarily what's best for the corporation. Specifically, the DB may
wind up implemented in such a way that response time suffers, but
expenses are lower.

3) It now manages the DB and their standards may complicate the
application. e.g. 20-byte limit on field names, field names all
uppercase, multiple required prefixes/suffixes where something as
simple, self-explainatory, and unambiguous as "AccountName" becomes
"ACCTNM_TXT".

3) Per-seat licenses, which cost real dollars, are needed.


OTOH, some sort of local implementation using SQL Express would seem
to get around the above limitations yet provide:

1) Auditability via triggers and audit tables

2) Response time that is less vulnerable to network speed.

3) Tighter security as access (no pun intended) can be limited to
tables/views.

I'm thinking maybe a box right in the user's area running plain-
vanilla Windows XP Pro with an instance of SQL Express on it.

The whole package doesn't have to be classical SQL Server-
optimized.... just something that works and is recoverable in the
event of a disaster.... or even that server's mobo getting cooked at
11 in the morning on a busy day.

Anybody been here?

Backup strategies?

Recoverability?

Complexity of maintainence/administration for somebody who is not an
SQL Server expert?

 

[David W. Fenton]

m:

The whole package doesn't have to be classical SQL Server-
optimized.... just something that works and is recoverable in the
event of a disaster.... or even that server's mobo getting cooked
at 11 in the morning on a busy day.

Anybody been here?

Backup strategies?

Recoverability?

Complexity of maintainence/administration for somebody who is not
an SQL Server expert?

You might want to browse through this thread on StackOverflow.com:

http://stackoverflow.com/questions/29044/good-free-alternative-to-ms-
access

 

[PeteCresswell]

You might want to browse through this thread on StackOverflow.com:

http://stackoverflow.com/questions/29044/good-free-alternative-to-ms-
access

Interesting thread.

I'm limited to JET or some flavor of SQL Server.

 

[Tom]

I'm thinking maybe a box right in the user's area running plain-

vanilla Windows XP Pro with an instance of SQL Express on it.

I have a question about that - what would you expect to see
performance wise vs an Access only solution?

My (very limited) understanding has always been that you wanted to
push as much processing off to the server as possible when operating
in an Access-SQLSERVER environment to 1) reduce the amount of data
sent over the network AND 2) because the server was likely a more
powerful computer and could do the computations faster than your local
machine.

If you are talking about setting up some plain-vanilla computer would
you necessarily see any performance enhancements due to item 2 above?

I guess restated, on an identical computer does SQL Server do a better
job/same job/worse job of dealing with a complex query than Jet?

Thanks

Tom

 

[John W. Vinson]

I guess restated, on an identical computer does SQL Server do a better
job/same job/worse job of dealing with a complex query than Jet?

Any or all of the above, depending on the configuration and the query. I.e.
"no simple answer".

In my experience using straightforward queries against a SQL backend (on my
own desktop computer, not a dedicated server) gives "reasonable" performance,
i.e. I can't distinguish it from JET just looking at the screen. I haven't
done any extensive timing trials.

Obviously you will benefit from proper query design, proper indexing,
avoidance of function calls in the query (such as dlookups or IIF), minimizing
the number of records returned, etc. - but these will benefit JET/ACE just as
much as they'll benefit SQL.

 

[Paul Shapiro]

With an unaltered application, an Access backend usually performs better
than a SQL Server backend on a local area network. SQL Server does a much
better job optimizing complex queries, but that also means it generally
takes longer to prepare an execution plan for a simple query. Access doesn't
try as hard at the optimization, whereas SQL Server can spend more time
figuring out how to execute a simple query than it spends executing the
query. With complicated queries, or very large data volumes, SQL Server can
be drastically faster. So the answer is "It depends."

SQL Server offers other advantages that are clearer: More robust (data
corruption should never occur due to network interruptions, for example),
larger maximum data volume, better security, triggers, stored procedures,
online backups without interrupting users, able to work over a wide area
network (with an appropriately designed application), etc.

 

[(PeteCresswell)]

Per Tom:

I have a question about that - what would you expect to see
performance wise vs an Access only solution?

My (very limited) understanding has always been that you wanted to
push as much processing off to the server as possible when operating
in an Access-SQLSERVER environment to 1) reduce the amount of data
sent over the network AND 2) because the server was likely a more
powerful computer and could do the computations faster than your local
machine.

If you are talking about setting up some plain-vanilla computer would
you necessarily see any performance enhancements due to item 2 above?

I guess restated, on an identical computer does SQL Server do a better
job/same job/worse job of dealing with a complex query than Jet?

I've done several JET-To-SQL Server conversions where the mandate
was: "Just migrate the tables, don't change anything else, and
change the apps existing ODBC links to connect to the migrated
tables."

My experience with those was that performance went down the tubes
- but I can't say whether it was ODBC-ing into DAO instead of
stored procedures into ADO or just that they put the DB on an
already-overloaded server.

OTOH, quite a few years ago I wrote a SQL Server-based project
management system - coming into it knowing nothing about SQL
Server (and coming out of it not knowing much more....).

For that system, I set up the DB as best I knew how and wrote
stored procedures for all data delivery and used ADO recordsets
to access same.

There were several fairly-heinous screens - like 7 or 8 subforms,
and a lot of flipping data on it's side, as in a table of
dates/projects/hours that gets presented as a grid with days of
the week across and projects going down.

I was expecting the worst, and the SQL Server guy on site wrote
at least 10 pages telling me all the things that were technically
lacking in my implementation.

But I was quite surprised: the thing ran much, *MUCH* faster that
I would have expected a JET implementation over LAN connections
to run.

It was "Real" SQL Server running on a Windows Server box under
some engineer's desk... but definitely nothing special in the way
of hardware.

One of my takeaways from that is that if I'm populating many sub
tables on a screen, a stored procedure stacking all the datasets
into one ADO recordset is faster than opening up many sub tables
via ODBC to JET.

Another is that, using the right techniques, SQL Server over a
LAN is probably going tb faster than JET over a LAN...

Of course there are LANs and then there are LANs.... but in the
environment I currently work in, speed of LAN access to files has
been going slowly but steadily downhill for several years.

Something to do with all the streaming video I see when walking
around looking at people's monitors would be my first guess.

IT saving a buck by converting what used to be free-standing PC
file servers into virtual file machines in a shared box would be
my second guess.


Finally, my last application was one of those mega-scope-creep
scenarios. "Hey Pete, Daisy's running out of columns on her
spreadsheet: can you write something in MS Access to replace it?"

Six months later, I'm up to my neck in a mission-critical
full-blown municipal bond trading system. Just me and this
malstrom of changes that led from the spreadsheet replacement to
the current system.

At that point, I convinced the client's management that we were
way, *way*, WAY behond the point of something that could be
entrusted to a single person - no matter how nice a guy he was
and no matter how much coffee he drank - and that they should
bring in a team to write something that IT can feel comfortable
taking over.

Well, they went for it.... and the resulting .Net/SQL Server
application very, very closely mirrors my MS Access app.

The coming months will be educational.

My MS Access app is really quick on C:, pretty good single-user
over the LAN, but slow as death with 10 concurrent users over the
LAN.

The .Net/SQL Server version is pretty quick right now - but it's
running solo user in a dedicated development environment.

I'm squirreling away various response time measurements for the
Access version - and looking forward with great interest to how
fast the .NET/SQL Server version stacks up under real world
conditions.

 

[David W. Fenton]

m:

Interesting thread.

I'm limited to JET or some flavor of SQL Server.

Well, then, there's no way to get around the issue.

BTW, another thing to consider is that machines with Firefox have
SQLLite installed, as it's now used for the bookmarks database. I'm
not sure if the installed package is usable from outside Firefox,
but if it is, any site that uses Firefox as its default browser
(which should be a lot more sites than it is, as it's so far
superior to IE that it's pathetic) would have that available as a
light data store that uses a single file for storing the data.

 

[David W. Fenton]

I've done several JET-To-SQL Server conversions where the mandate
was: "Just migrate the tables, don't change anything else, and
change the apps existing ODBC links to connect to the migrated
tables."

My experience with those was that performance went down the tubes

This contradicts my experience.

Performance in a *few* areas of such an upsized app goes down the
tubes, but of the apps I've upsized, 90% or more of each app
exhibited no performance issues.

And, surprisingly, I have never been able to predict with any
reliability which parts are going to be the performance dogs,
despite knowing the architecture of the app such that it should be
predictable. In general, almost everything that I thought would need
to be redesigned turned out to not be a bottleneck, and things I
never thought would be a problem turned out to be dogs.

Fixing the latter never took a helluva lot of time, either, as it
almost always took nothing more than moving some complex joins to a
server-side view, which is about as easy as it gets for an Access
developer who doesn't have strong SQL Server chops.

 

[David W. Fenton]

co m:


Well, then, there's no way to get around the issue.

BTW, another thing to consider is that machines with Firefox have
SQLLite installed, as it's now used for the bookmarks database.
I'm not sure if the installed package is usable from outside
Firefox, but if it is, any site that uses Firefox as its default
browser (which should be a lot more sites than it is, as it's so
far superior to IE that it's pathetic) would have that available
as a light data store that uses a single file for storing the
data.

Another database engine to consider:

http://en.wikipedia.org/wiki/SQL_Server_Compact

I have no idea what's involved in using it, nor if it can be used
with an Access front end. But it's worth a look for your restricted
situation, I think.

 

[(PeteCresswell)]

Per David W. Fenton:

This contradicts my experience.

Performance in a *few* areas of such an upsized app goes down the
tubes, but of the apps I've upsized, 90% or more of each app
exhibited no performance issues.

That seems to support my suspicion that the performance issue
might be around the server they put it on.

This was a huge financial company. I don't they even *had* any
PC-based servers.... all virtual machines on IBM mainframes or
somesuch.... so there's got tb a tension there between how many
servers/DBs are crammed onto a given hunk of hardware and user's
perceived performance.

 

[David W. Fenton]

Per David W. Fenton:

That seems to support my suspicion that the performance issue
might be around the server they put it on.

Well, to be fair, your scenario is not *impossible* -- certainly
it's going to depend on your particular application.

This was a huge financial company. I don't they even *had* any
PC-based servers.... all virtual machines on IBM mainframes or
somesuch.... so there's got tb a tension there between how many
servers/DBs are crammed onto a given hunk of hardware and user's
perceived performance.

I've never upsized in an environment in which the server was
handling any significant SQL Server databases other than the one I
upsized. But that's because my clients are small businesses,
micro-businesses, even (given how 500 employees is still considered
a small business in the business press).