To SQL or not to SQL?

[Myron Oakley]

I have a very vertical product developed 100% in M/S Access. I developed it
myself in 1993 in the initial release of Access and been with it ever since.
It is currently very stable and running in Access 2003. My big question is
how or even *if* we should move it to SQL. My customers are getting bigger
all the time with more users. Typically our customers run one or two
workstations generally never more than 5. Performance, however, is starting
to slow with database sizes passing 500 meg. We also have a LOT of code
behind forms so are bound tightly to the Access development environment. A
move to SQL would require substantial work.

I recently installed a Dell 2900 dual quad core xeon Terminal Server and
quickly discovered the application now screams on this platform. My big
question now is if we should even consider moving to SQL. I know Access
2007 has a new db format. If that is faster or able to service more users,
maybe we should just push Terminal Servers and stay right where we are.

If anyone out there has experience along these lines, I would *gladly* pay
for some consulting time.

TIA

 

[Lance]

Access and SQL Server play well together, it may not be that difficult to
move your data to a SQL Server and continue using Access as your interface.
But, unless you have an existing server you can use there is the
consideration of the amount of money you're going to spend on licensing (last
price I saw was running about $13000 for the machine and licenses).

500meg isn't a terribly oversized database, but perhaps (if you haven't
already) you could archive some old data to get its size down.

 

[Larry Linson]

I'm not sure why you think "A move to SQL would require substantial work."
Well over half my paying work since 1993 has been on projects with an Access
client application to various server database, including Microsoft SQL
Server, but also including other ODBC-compliant databases such as Informix
and Sybase. Typically, if you split off the front end, move the tables and
data to the server, and link the tables, most Access databases run without
further ado. When they do not, it usually does not take much tweaking to get
them running. Then it may take a-little-to-a-lot more tweaking to improve
performance, but you can, in my experience, always make it run faster.

Were you under the impression that you'd have to abandon the Access UI with
which your developers and customers are already familiar and do a new "from
the ground up" development? Certainly not so.

But, with the very limited user audience you have and no bigger database
than that (not implying that 500 MB is tiny, but it's only 1/4 of the way to
the 2GB limit of a single Jet MDB, and I've know of people running multiple
Jet MDB back ends). I'd first take a look at how I could possibly improve
performance with Access / Jet. I'm not aware of any performance comparisons
between Jet and the ACCDB database engine of Access 2007. And, until we get
more feedback on SP1 to Access 2007, I'd hesitate to recommend someone put
the database that is their livliehood in in Access 2007.

There is a Microsoft-sponsored newsgroup, microsoft.public.multiuser,
specifically for multi-user Access and Jet Q&A, and there's a "core list" of
some very helpful resource websites at my user group's SharePoint site,
http://sp.ntpcug.org/accesssig/default.aspx. Start with MVP Tony Toews'
site, http://www.granite.ab.ca/accsmstr.htm, and next go to Jeff Conrad's
"Access Junkie" site, also in the same list, that's not to disparage the
other sites on that list, but I know the two I mentioned have good
information and links on your particular subject.

Larry Linson
Microsoft Office Access MVP

 

[Myron Oakley]

Thanks for the info. I think we could just use SQL server Express, which if
free, for smaller installations. Some of my customers already have a full
MS SQL license running.

 

[Myron Oakley]

Thanks for all the info, I will check it out.

I guess I really forgot to mention I did have the application "kind of"
running with MySQL and ODBC. Some dialogs and menus worked, but a lot
didn't. Performance was pretty bad. I have done a bunch of "reading up" on
a migration to SQL server in particular the Access 2007 VBA Programmer's
Reference and SQL: Access to SQL Server. We pretty well use bound tables or
queries on most all forms and I understand, with SQL server, there are
issues, record locking, updating, etc.. We're also using a DAO in vba, I
*believe* this would all have to be changed to ADO with SQL server. I guess
we'll just have to wait for a bit are far as Access 2007?

 

[Armen Stein]

Typically, if you split off the front end, move the tables and
data to the server, and link the tables, most Access databases run without
further ado. When they do not, it usually does not take much tweaking to get
them running. Then it may take a-little-to-a-lot more tweaking to improve
performance, but you can, in my experience, always make it run faster.

Hi Myron,

I agree with Larry's statements (as usual), and I'll add that there
are some cases where performance won't improve much (or can even
degrade) using linked tables with SQL Server. You might just want to
try it and see - it isn't too difficult to just relink all your tables
to the SQL backend and test it out.

There are a few code changes you'll need to make (for example, you
won't get a new AutoNumber/Identity key value after .AddNew in a
recordset, you need to retrieve it after the .Update), but they are
pretty straightforward.

We do a ton of Access-SQL development and we have a list of tips on
making it perform well. You can see a summary in the Best of Both
Worlds document at
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

Thanks for the info. I think we could just use SQL server Express, which if
free, for smaller installations. Some of my customers already have a full
MS SQL license running.

Hi Myron,

I agree. Even the free Express version of SQL Server will give you
more horsepower and features than Jet if developed correctly, and the
price is right! This would give you the ability to use one code base
for the small customers, scaling up for larger customers by upgrading
to higher versions of SQL Server.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[(PeteCresswell)]

Per Myron Oakley:

I recently installed a Dell 2900 dual quad core xeon Terminal Server and
quickly discovered the application now screams on this platform.

Could somebody clarify this?

What I'm getting out of it is that Myron set up a really fast box
that his users can use RemoteDesktop or something similar to get
to from their slower PCs and run the app there.

If I've got it right so far, it would seem that response time
would be limited only by the speed/capacity of that super box and
the ability of the LAN to carry the terminal server's screen
updates.

And I'd think the LAN's ability to carry those screen updates
would be moot since I get 100% acceptable response time on Remote
Desktop over a sub-five meg Verizon internet connection.

If this jells so far, two questions:
-----------------------------------------------------------------
1) Is there a product that will allow my users to open up a
window into the terminal server without having the client
take over their whole PC? I'm guessing Remote Desktop
is one. Gotchas with Remote Desktop? Better products?

2) Am I on the right track thinking that this could be a solution
to my client's more-or-less steadily degrading response time
on three of my applications?

Seems like with a fast enough box and a certain minimum LAN
capacity, the LAN and file servers would be completely out of
the picture response-time-wise.

 

[John W. Vinson]

1) Is there a product that will allow my users to open up a
window into the terminal server without having the client
take over their whole PC? I'm guessing Remote Desktop
is one. Gotchas with Remote Desktop? Better products?

A client of mine uses Citrix Server (and a distributed Citrix client as a
remote desktop). It needn't take over the client machine; it just runs in a
window. I have one open right now as a matter of fact.

2) Am I on the right track thinking that this could be a solution
to my client's more-or-less steadily degrading response time
on three of my applications?

Yep. Worth trying at any rate!

John W. Vinson [MVP]

 

[Myron Oakley]

1) Is there a product that will allow my users to open up a
window into the terminal server without having the client
take over their whole PC? I'm guessing Remote Desktop
is one. Gotchas with Remote Desktop? Better products?

RD does not "take over their whole PC". You just need more viewing area.
I run dual monitors and sometimes actually have two RD sessions running. On
a single RD session, I put it on one monitor and have my local desktop on
the
other. You can even cut and paste between your pc and the RD session.

2) Am I on the right track thinking that this could be a solution
to my client's more-or-less steadily degrading response time
on three of my applications?

Tell you what, you can kill youself trying to optimise the application
or, for around $5K, pretty well fix it all AND even provide decent
remote access. Yesterday I started playing around with Quickbooks
on the Terminal Server. Got it up and running in about an hour and was
totally amazed by that installation also. I think you do need some
real horsepower on the server. I have a dual quan core xeons with
4 gig of memory and raid 5 drive. If I was to go over 5 users, I'd
probably add another 2 gig of memory.

 

[(PeteCresswell)]

Per Myron Oakley:

I have a dual quan core xeons with
4 gig of memory and raid 5 drive. If I was to go over 5 users, I'd
probably add another 2 gig of memory.

Running the Server flavor of Windows XP?

 

[David W. Fenton]

you can kill youself trying to optimise the application
or, for around $5K,

It doesn't require that much money, actually, if you know what you
need to buy and have other servers on the local LAN (especially if
your servers are on a GB segment of the LAN), you don't need much
disk space in the terminal server, so you can spend all your money
on RAM. I always budget 128MBs per simultaneous user, and that seems
to work fairly well (as much of the RAM is shared; i.e., each user
doesn't end up with a full copy of all of Access running in memory).

pretty well fix it all AND even provide decent
remote access. Yesterday I started playing around with Quickbooks
on the Terminal Server.

This is an excellent way to provide shared access to Quickbooks
files. I have tried to get some of my clients who use QB and pass
around files to use Terminal Server so they can edit the same files.
One client is actually doing it (they have five branches with a home
office, and financial operations are maintained by each branch and
overseen by the accounting department in the main office), and it
works really great.

Got it up and running in about an hour and was
totally amazed by that installation also. I think you do need
some real horsepower on the server. I have a dual quan core xeons
with 4 gig of memory and raid 5 drive. If I was to go over 5
users, I'd probably add another 2 gig of memory.

I think that's *way* over spec for that number of users. I have a
client with 10 simultaneous users running very well on 2GBs of RAM
on a fast dual-core Xeon. It's actually a couple of years old, and
they've been very happy with it. They skimped on hard drive space in
the server, since they had a huge amount of it on nearby servers on
the same GB LAN segment.

 

[David W. Fenton]

Per Myron Oakley:

Running the Server flavor of Windows XP?

That would be Windows Server 2003, the best version of Windows ever
(next best is Win2K).

 

[(PeteCresswell)]

Per David W. Fenton:

That would be Windows Server 2003, the best version of Windows ever
(next best is Win2K).

I've got it available on my MSDN discs.

Do you think it's worth installing Windows Server 2003 instead of
Windows XP if/when I have to rebuild my developer PC from
scratch?

 

[David W. Fenton]

Do you think it's worth installing Windows Server 2003 instead of
Windows XP if/when I have to rebuild my developer PC from
scratch?

Not unless you want to test in a server environment (such as how
apps run in Terminal Server). On the other hand, MichKa used to
install the server versions on his laptop!