473,698 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Feb 15 '06 #1
29 2634
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

Feb 15 '06 #2
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


Feb 15 '06 #3
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

Feb 16 '06 #4
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



Feb 16 '06 #5
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

Feb 16 '06 #6
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.

Feb 16 '06 #7

"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
Feb 16 '06 #8

"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
Feb 16 '06 #9
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.

Feb 16 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3348
by: Guy | last post by:
I got a big Access file (1 400 tables) to convert to SQL and I would like to be provided with some automated tools, except upsizing wizard and DTS, to convert it on my own. I got a lot of forms and query to convert too. Can someday provide me with at least one tool name ? Thanks,
25
4374
by: cory | last post by:
Hi, I have an Access database and am having an ASP.NEt application written for it. It is almost complete. I have a hosting company that I signed up with a month ago but before I did anything I asked them if Access and ASP.NET would work on their servers, they said yes so I bought in. Now they are saying my application wont work on their servers using MSaccess and I can only use SQL or asp 3.0. They are saying Microsoft is trying to...
2
5631
by: VenuGopal | last post by:
Hi Everyone, in my application i am interacting to a known server. i send a request and get a response from the server. i am using XML and HTTP here. part of the job is that the XYZ server is sending a image in the Ascii format using XML. ASCII is not useful for me to display the image. i need to convert it to BASE64 format... so i can go ahead and view the image. How do u convert ASCII to Base64 in C#.
5
2725
by: manmit.walia | last post by:
Hello All, I am stuck on a conversion problem. I am trying to convert my application which is written in VB.NET to C# because the project I am working on currently is being written in C#. I tried my best to convert it, but somehow the app does not work. I am also not getting any errors when I complie thus, letting me know I am on the write track. Basically what I want to do is edit,add,delete, and update an XML file in a DataGrid. Below...
2
4234
by: egoldthwait | last post by:
I need to convert a 17mb access 2000 db to Oracle and house it in a Citrix farm. The issue: we have never converted an Access Db to Oracle but can probably use Oracle's Workbench to assist with this. Also - the citrix folks do not want us to keep the FE in Access as the queries and other activities consume a lot of power. The users will be in 3 different offices across the globe all accessing the 1 Oracle DB in Citrix. Does anyone have...
4
118814
by: dba_222 | last post by:
Dear Experts, Ok, I hate to ask such a seemingly dumb question, but I've already spent far too much time on this. More that I would care to admit. In Sql server, how do I simply change a character into a number?????? In Oracle, it is:
6
3010
by: tshad | last post by:
Apparently, I can't do: Dim da2 As New OleDb.OleDbDataAdapter("Select PR, Convert(varchar,getchar(),1),F1, F2, F5, Sum(F4) from temp .... I am getting this error. 'undefined function "convert" in expression'
1
3597
by: johnlim20088 | last post by:
Hi, Currently I have 6 web projects located in Visual Source Safe 6.0, as usual, everytime I will open solution file located in my local computer, connected to source safe, then check out/check in some files and work on it. Let say, I want add new page to web project named websiteOrder.sln, i will open websiteOrder.sln in my local computer, connected to websiteOrder.sln located in Visual Source Safe 6.0(source safe located in another...
7
16680
by: groups | last post by:
This is my first foray into writing a generic method and maybe I've bitten off more than I can chew. My intent is to have a generic method that accepts a value name and that value will be returned from the source. My first attempt was as follows; (please ignore that error handling is not present in this example) public T GetValue<T(string objName) { T results;
0
9157
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9027
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8861
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6518
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5860
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4369
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3046
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2329
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2001
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.