How much to convert to SQL server

[Jan]

Hi:

I have an Access database that's been running (in one form or another)
for a couple of different clients for a few years. Now a new client has
requested that it be implemented with a SQL server back-end. I'm doing
my best to learn about SQL server, and I plan to leave the front-end
more or less as-is, just linking to the SQL server back end, but here's
a basic question:

The db has a front-end linked to two back-ends. One of the back-ends has
completely static data, and so in an all-Access installation it sits on
the C drive along with the front end. Only the 2nd backend sits
on the server.

Now, should I convert both back-ends to SQL server, or just the one on
the server? Reasons, pros, cons?

TIA.

Jan

[Larry Linson]

Your new client is the one who wants the conversion and, presumably, is
paying you to convert the database. You should be asking the client what
_they_ want, but be ready with logical arguments if they want something
"flakey".

The following applies only to Access MDB, using ODBC drivers, to link to an
SQL server database:

Relatively unchanging lookup tables are often kept local to an Access
application -- States in the US is a prime example, company structure
(divisions, departments) is another. I'd personally see no need to migrate
static data to the back-end and have to access it across a
possibly-slower-than-I'd-like network.

Still, the client may have their own reasons (or even prejudices) and ideas
about what can reasonably be moved to the backend.

Larry Linson
Microsoft Access MVP

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

Hi:

I have an Access database that's been running (in one form or another)
for a couple of different clients for a few years. Now a new client has
requested that it be implemented with a SQL server back-end. I'm doing
my best to learn about SQL server, and I plan to leave the front-end
more or less as-is, just linking to the SQL server back end, but here's
a basic question:

The db has a front-end linked to two back-ends. One of the back-ends has
completely static data, and so in an all-Access installation it sits on
the C drive along with the front end. Only the 2nd backend sits
on the server.

Now, should I convert both back-ends to SQL server, or just the one on
the server? Reasons, pros, cons?

TIA.

Jan

[Jan]

Hi, Larry:

Thanks for the quick reply.

Unfortunately, the client isn't directly mine; I'm more or less a
subcontractor. I'm not sure they know exactly why they want SQL Server,
except that they think it's the "better" way. And I'm not in a position
to argue. They do have some issues with lots of users possibly spread
out over several offices.

Anyway, I just wanted to be sure there wouldn't be some advantage to
having all the linked tables be converted to SQL Server. And yes, I'm
planning to use the ODBC link to the SQL Server backend. My preference
is certainly to keep as much of the data local, and in Access, as
possible, if only because it's what I know best.

Anyone have any other views?

Jan

Larry Linson wrote:

Your new client is the one who wants the conversion and, presumably,
is paying you to convert the database. You should be asking the
client what _they_ want, but be ready with logical arguments if they
want something "flakey".

The following applies only to Access MDB, using ODBC drivers, to link
to an SQL server database:

Relatively unchanging lookup tables are often kept local to an Access
application -- States in the US is a prime example, company
structure (divisions, departments) is another. I'd personally see no
need to migrate static data to the back-end and have to access it
across a possibly-slower-than-I'd-like network.

Still, the client may have their own reasons (or even prejudices) and
ideas about what can reasonably be moved to the backend.

Larry Linson Microsoft Access MVP

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

Hi:

I have an Access database that's been running (in one form or
another) for a couple of different clients for a few years. Now a
new client has requested that it be implemented with a SQL server
back-end. I'm doing my best to learn about SQL server, and I plan
to leave the front-end more or less as-is, just linking to the SQL
server back end, but here's a basic question:

The db has a front-end linked to two back-ends. One of the
back-ends has completely static data, and so in an all-Access
installation it sits on the C drive along with the front end. Only
the 2nd backend sits on the server.

Now, should I convert both back-ends to SQL server, or just the one
on the server? Reasons, pros, cons?

TIA.

Jan

[Rick Brandt]

Jan wrote:

Hi, Larry:

Thanks for the quick reply.

Unfortunately, the client isn't directly mine; I'm more or less a
subcontractor. I'm not sure they know exactly why they want SQL
Server, except that they think it's the "better" way. And I'm not in
a position to argue. They do have some issues with lots of users
possibly spread out over several offices.

Anyway, I just wanted to be sure there wouldn't be some advantage to
having all the linked tables be converted to SQL Server. And yes, I'm
planning to use the ODBC link to the SQL Server backend. My
preference is certainly to keep as much of the data local, and in
Access, as possible, if only because it's what I know best.

Anyone have any other views?

Well even if you want to keep a few tables local (I see no reason to) you would
often want another copy of the same table on the server if it is ever used in a
query. You don't want to create queries in Access that use both a local table
and a link to a server table. That would definitely be an inefficient query to
run unless the local table was very small).

In my Access FE/SQL Server BE apps ALL the tables are on the server and
performance is just fine on a standard 100 mb LAN.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[david epsom dot com dot au]

If you are using transactions, you probably want to re-write
any transactions that include the static tables. If you use
local jet tables, the transactions are more likely to block
internally, and if you use SQL Server tables, the transactions
are likely to block other users.

(david)

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

Hi, Larry:

Thanks for the quick reply.

Unfortunately, the client isn't directly mine; I'm more or less a
subcontractor. I'm not sure they know exactly why they want SQL Server,
except that they think it's the "better" way. And I'm not in a position
to argue. They do have some issues with lots of users possibly spread
out over several offices.

Anyway, I just wanted to be sure there wouldn't be some advantage to
having all the linked tables be converted to SQL Server. And yes, I'm
planning to use the ODBC link to the SQL Server backend. My preference
is certainly to keep as much of the data local, and in Access, as
possible, if only because it's what I know best.

Anyone have any other views?

Jan

Larry Linson wrote:

Your new client is the one who wants the conversion and, presumably,
is paying you to convert the database. You should be asking the client
what _they_ want, but be ready with logical arguments if they
want something "flakey".

The following applies only to Access MDB, using ODBC drivers, to link
to an SQL server database:

Relatively unchanging lookup tables are often kept local to an Access
application -- States in the US is a prime example, company structure
(divisions, departments) is another. I'd personally see no
need to migrate static data to the back-end and have to access it across
a possibly-slower-than-I'd-like network.

Still, the client may have their own reasons (or even prejudices) and
ideas about what can reasonably be moved to the backend.

Larry Linson Microsoft Access MVP

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

Hi:

I have an Access database that's been running (in one form or another)
for a couple of different clients for a few years. Now a
new client has requested that it be implemented with a SQL server
back-end. I'm doing my best to learn about SQL server, and I plan
to leave the front-end more or less as-is, just linking to the SQL
server back end, but here's a basic question:

The db has a front-end linked to two back-ends. One of the back-ends
has completely static data, and so in an all-Access installation it sits
on the C drive along with the front end. Only
the 2nd backend sits on the server.

Now, should I convert both back-ends to SQL server, or just the one
on the server? Reasons, pros, cons?

TIA.

Jan

[Jan]

No transactions in this one.

david epsom dot com dot au wrote:

If you are using transactions, you probably want to re-write
any transactions that include the static tables. If you use
local jet tables, the transactions are more likely to block
internally, and if you use SQL Server tables, the transactions
are likely to block other users.

(david)

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

Hi, Larry:

Thanks for the quick reply.

Unfortunately , the client isn't directly mine; I'm more or less a
subcontractor . I'm not sure they know exactly why they want SQL Server,
except that they think it's the "better" way. And I'm not in a position
to argue. They do have some issues with lots of users possibly spread
out over several offices.

Anyway, I just wanted to be sure there wouldn't be some advantage to
having all the linked tables be converted to SQL Server. And yes, I'm
planning to use the ODBC link to the SQL Server backend. My preference
is certainly to keep as much of the data local, and in Access, as
possible, if only because it's what I know best.

Anyone have any other views?

Jan

Larry Linson wrote:

Your new client is the one who wants the conversion and, presumably,
is paying you to convert the database. You should be asking the client
what _they_ want, but be ready with logical arguments if they
want something "flakey".

The following applies only to Access MDB, using ODBC drivers, to link
to an SQL server database:

Relatively unchanging lookup tables are often kept local to an Access
application -- States in the US is a prime example, company structure
(divisions , departments) is another. I'd personally see no
need to migrate static data to the back-end and have to access it across
a possibly-slower-than-I'd-like network.

Still, the client may have their own reasons (or even prejudices) and
ideas about what can reasonably be moved to the backend.

Larry Linson Microsoft Access MVP

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11***** ********@corp.s upernews.com...
Hi:

I have an Access database that's been running (in one form or another)
for a couple of different clients for a few years. Now a
new client has requested that it be implemented with a SQL server
back-end. I'm doing my best to learn about SQL server, and I plan
to leave the front-end more or less as-is, just linking to the SQL
server back end, but here's a basic question:

The db has a front-end linked to two back-ends. One of the back-ends
has completely static data, and so in an all-Access installation it sits
on the C drive along with the front end. Only
the 2nd backend sits on the server.

Now, should I convert both back-ends to SQL server, or just the one
on the server? Reasons, pros, cons?

TIA.

Jan

[Jan]

Hi, Rick:

This is a key one, I guess, because there are certainly queries that run
with some tables from each of the different sets. Most of the tables
are in the local set, but there are a crucial few that need to be on
the server. In the past, when it has been an entirely Access database,
I moved all the static tables to the local drive because it vastly
improved performance.

So do you think that with a SQL server backend I wouldn't run into the
slowness issues that I had with an all-Access db?

I have to say I'm really anxious about this whole conversion process.
The client is out of town and I have to make it work in a very short
time period when I'm out there. I can test it here on my machine, with
the "developers " version of SQL Server, but I worry that it isn't a good
proxy for the
"real thing."

Jan

Rick Brandt wrote:

Jan wrote:

Hi, Larry:

Thanks for the quick reply.

Unfortunately, the client isn't directly mine; I'm more or less a
subcontractor. I'm not sure they know exactly why they want SQL
Server, except that they think it's the "better" way. And I'm not
in a position to argue. They do have some issues with lots of
users possibly spread out over several offices.

Anyway, I just wanted to be sure there wouldn't be some advantage
to having all the linked tables be converted to SQL Server. And
yes, I'm planning to use the ODBC link to the SQL Server backend.
My preference is certainly to keep as much of the data local, and
in Access, as possible, if only because it's what I know best.

Anyone have any other views?

Well even if you want to keep a few tables local (I see no reason to)
you would often want another copy of the same table on the server if
it is ever used in a query. You don't want to create queries in
Access that use both a local table and a link to a server table. That
would definitely be an inefficient query to run unless the local
table was very small).

In my Access FE/SQL Server BE apps ALL the tables are on the server
and performance is just fine on a standard 100 mb LAN.

[Rick Brandt]

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

Hi, Rick:

This is a key one, I guess, because there are certainly queries that run
with some tables from each of the different sets. Most of the tables
are in the local set, but there are a crucial few that need to be on
the server. In the past, when it has been an entirely Access database,
I moved all the static tables to the local drive because it vastly
improved performance.

So do you think that with a SQL server backend I wouldn't run into the
slowness issues that I had with an all-Access db?

If a query is slow because it is poorly designed then putting the tables on a
server won't magically fix that. If a query is slow because it is working
against very large tables then putting those tables on a server won't magically
cure that either.

There are many advantages to moving to a server-based data engine. Raw query
performance is not one of them. People often see performance gains when setting
up a new box for SQL Server because they will usually build that server with
high-spec'd hardware. Several years ago just about any server would be WAY more
capable than a desktop PC. That is still true, but not to the degree that it
once was because desktop PCs are simply very capable these days.

Client/Server performance is largely driven by minimizing traffic over the LAN
and good design. Those same strategies would likely result in an MDB Based
database that also performed well.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Larry Linson]

"Rick Brandt" <ri*********@ho tmail.com> wrote

Well even if you want to keep a few tables
local (I see no reason to) you would
often want another copy of the same table
on the server if it is ever used in a
query.
I specifically said, rarely-changing _lookup_ tables, with examples. I've
done this, with a master copy on the server, from which the local tables are
refreshed at startup, if need be. Running over a WAN, it is likely you'll
see some performance improvment if there are quite a few.
You don't want to create queries in Access that
use both a local table and a link to a server table.
That would definitely be an inefficient query to
run unless the local table was very small).
I _strongly_ agree. In fact, even if the local table is very small, the
performance impact can be substantial because the entire table may be
brought from the server to the user's machine for the join. <OUCH!>
In my Access FE/SQL Server BE apps ALL
the tables are on the server and performance is
just fine on a standard 100 mb LAN.

Of course. But, move some of your users to the boonies on a WAN that shares
a T-1 line and you are likely to see a discernable difference. That's where
you may need to resort to "performanc e tricks".

If you have appropriate instrumentation , the difference may be "measurable "
on a 100 MBPS LAN, but it's unlikely to be discernable to someone sitting in
front of a screen. We didn't bother with local tables when everyone was on
the high-speed LAN.

Larry Linson
Microsoft Access MVP

[Jan]

I don't know why exactly the database runs slow with the tables on the
server; I only know that when I moved them to the local disk, a
particular activity (which involves a lot of data manipulation, writing
to a Word document, and a variety of other tasks) went from taking 5
minutes down to less than one. I always blamed it on the slowness of
the client's network, but frankly didn't spend a lot of time
contemplating the reasons; the solution was very effective and I left it
at that.

Maybe this will help you understand the situation: the users are
entering data on rental units. The database is running a model that
involves manipulating a large amount of stored data on other rental
units (that's the static part; the research is done once and stays the
same for a year), but it has to be compared and calculations made in
order to come to some recommendations on the newly-entered unit.
Thus, most of the data is static, but that new record has to be involved.

Make any sense?

So maybe the question is this:
if I haven't had a performance hit from running queries that involved
both local and server tables in the past, would I have that hit when the
"server" tables are SQL Server and the local tables are Access?

Jan

Rick Brandt wrote:

"Jan" <ja*@stempelcon sulting.com> wrote in message
news:11******** *****@corp.supe rnews.com...

Hi, Rick:

This is a key one, I guess, because there are certainly queries
that run with some tables from each of the different sets. Most of
the tables are in the local set, but there are a crucial few that
need to be on the server. In the past, when it has been an
entirely Access database, I moved all the static tables to the
local drive because it vastly improved performance.

So do you think that with a SQL server backend I wouldn't run into
the slowness issues that I had with an all-Access db?

If a query is slow because it is poorly designed then putting the
tables on a server won't magically fix that. If a query is slow
because it is working against very large tables then putting those
tables on a server won't magically cure that either.

There are many advantages to moving to a server-based data engine.
Raw query performance is not one of them. People often see
performance gains when setting up a new box for SQL Server because
they will usually build that server with high-spec'd hardware.
Several years ago just about any server would be WAY more capable
than a desktop PC. That is still true, but not to the degree that it
once was because desktop PCs are simply very capable these days.

Client/Server performance is largely driven by minimizing traffic
over the LAN and good design. Those same strategies would likely
result in an MDB Based database that also performed well.