Need help choosing front end for SQL Server

I've been working on an Access 97 database that's pretty much reached
it's limit in terms of performance and reliability. Although it supports
relatively few users (5-10 concurrent) it contains a lot of data (around
30 tables, some with several million records) plus a lot of forms and
reports.
The company has recently purchased an MS SQL server and it's my job to
migrate the back-end as well as re-write the front-end (the front-end
needs re-writing anyway).
My question is do we keep Access as the front end (we'd probably upgrade
to 2003) or would we get better performance using a different solution
(VB.Net for example) ?
If Access, would it be best use MDB's or ADP's?
The main consideration is that we need to perform some complicated
queries on large datasets, which ideally should happen on the SQL server
(I'm currently learning about stored procedures and views etc).
Thanks,
PSB
I see no reason why you can't continue to use access as the front end.
If you have the budgets, and time to re-write the application, then I guess
you
can choose anything you like.
However, if you wish to keep the application, and not re-write, and not have
to re-train the users, then why not continue to use access as the front end?

> My question is do we keep Access as the front end (we'd probably upgrade
> to 2003)
This is going to be up to you. I see no reason why not to continue to use
ms-access. I mean, you got to write the application in something.

> or would we get better performance using a different solution
For the number of users you have, I see no reason not to use ms-access.

> (VB.Net for example) ?
It is a great platform. Do you have the developers...or perhaps you will
spend the time and learn .net? (you will need a few months of time). I mean,
how long did it take the people to learn ms-access? (it took me more then a
year to be become productive in ms-access). And, the sample applies to any
platform you will adopt.

> If Access, would it be best use MDB's or ADP's?
It is by far and away best to keep it as a MDB (and, of course, you used a
split database..and always distributed a mde to each user anyway..right?).
I would only choose a ADP for a brand new from scratch product (and even
then...likely I would not use a adp). So, for a existing application, it
makes NO
sense to use a ADP..as you can't use any of your DAO code.

> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL server
> (I'm currently learning about stored procedures and views etc).
Yes, for processing intensive things, you certainly want that to run on the
server, but you still need to choose a programming environment that lets you
write code, and create the application (and sql server does not do that!!).
If you got the time,a nd resources, and have the developer(s) to re-write,
then you can do such. It really comes down to budgets, and what benefits
you get by re-writing the application.
The skill level of the developer will make or break the project regardless
of the "conversion" issues.
I am a better hockey coach when Wayne Gretzky is on the team.
Hence the #1 consideration is at what level the developer is at. There are
certainly more levels then just "trained" or "not trained". Generally there
are a "lot" of skill levels, but the following breakdown is sufficient. **
Stage 1 Innocent (never heard of the product)
Stage 2 Aware (Has read an article about X)
Stage 3 Apprentice (has attended a three-day seminar)
Stage 4 Practitioner (ready to use X on a real project)
Stage 5 Journeyman (uses X naturally and automatically in his job)
Stage 6 Master (has internalized X, knows when to break the rules)
Stage 7 Expert (writes books, gives lectures, looks for ways to extend x)
One should NEVER attempt a project with a team consisting with Stage 3 or
lower people. This is a sure fire formula for failure. The team can consist
of stage 4's, but they should have at least access to Stage 5, or 6.
So, if your developers are skilled in the new platform, then that is
certainly a possible here to re-write.
However, I see no reason why you can't continue to use ms-access as the
front end to sql server. I mean, if your developers don't know how to use
sql server, then you can get a garbage and slow application if it is written
in VB, vb.net, or ms-access. The performance here is not going to be the
fact that you used VB or ms-access, but in fact that your developers know
how to utilize sql server.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal
|||For that number of users, Access should be able to handle if designed
properly (Seperated FE and BE and BE file size is not too big, say close 2G
limit). However, now you have already bought SQL Server, and as you stated
"the front-end needs re-writing anyway", so, there is doubt of moving to SQL
Server.
As for front end, to use Access, or not to use it, lisenceing Access itself
would not be a big problem here. That is, if you use Access FE, all users
must have it installed. for $300 of each, the cost does not weigh too much
in the entire situation. And for your situation (fairly small number of
user, and I'd imagine they are in the same office, so deploy/update your app
wouldn't be a serious issue), exactly what type of FE is not that important.
I do not know your skill level, but programming a non-Access front end (Win
App ot wep app) is a lot more different from Access programming and requires
quite different skill and knowledge. Whether such en expertise is available
or not would make quite big difference on development cost.
If you go with Access, probably ADP is the option (But you must use
Access2K or later, not Access97). And many experienced Access developers
claim they can produce Access solution a lot faster than other type of FE.
"PsychicStickleBrick" <sp@.m.me> wrote in message
news:421a3178$0$75435$ed2619ec@.ptn-nntp-reader01.plus.net...
> I've been working on an Access 97 database that's pretty much reached
> it's limit in terms of performance and reliability. Although it supports
> relatively few users (5-10 concurrent) it contains a lot of data (around
> 30 tables, some with several million records) plus a lot of forms and
> reports.
> The company has recently purchased an MS SQL server and it's my job to
> migrate the back-end as well as re-write the front-end (the front-end
> needs re-writing anyway).
> My question is do we keep Access as the front end (we'd probably upgrade
> to 2003) or would we get better performance using a different solution
> (VB.Net for example) ?
> If Access, would it be best use MDB's or ADP's?
> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL server
> (I'm currently learning about stored procedures and views etc).
> Thanks,
> PSB
>
>
|||Now there's the hundred thousand dollar question. The answer is it depends.
Moving the back-end to Sql Server is a good idea. And you can do this with
very little re-writing of the front end.
As for the front-end, it depends on how much of it needs re-written. If it's
just a little, then I'd probably stay with Access. You would get a little
performance hit, but not much if the queries ran server side.
If it's a lot of re-write, then I would go with VB.NET (or ASP.NET if you
want to make it a web app). You get a lot more functionality with these.
One word of note. Running a VB.NET app requires the Microsoft Framework also
be installed. This is a bit resource heavy and will run slowly on older
machines.
Good Luck.
Tom Collins
"PsychicStickleBrick" <sp@.m.me> wrote in message
news:421a3178$0$75435$ed2619ec@.ptn-nntp-reader01.plus.net...
> I've been working on an Access 97 database that's pretty much reached
> it's limit in terms of performance and reliability. Although it supports
> relatively few users (5-10 concurrent) it contains a lot of data (around
> 30 tables, some with several million records) plus a lot of forms and
> reports.
> The company has recently purchased an MS SQL server and it's my job to
> migrate the back-end as well as re-write the front-end (the front-end
> needs re-writing anyway).
> My question is do we keep Access as the front end (we'd probably upgrade
> to 2003) or would we get better performance using a different solution
> (VB.Net for example) ?
> If Access, would it be best use MDB's or ADP's?
> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL server
> (I'm currently learning about stored procedures and views etc).
> Thanks,
> PSB
>
>
|||"PsychicStickleBrick" <sp@.m.me> wrote in message
news:421a3178$0$75435$ed2619ec@.ptn-nntp-reader01.plus.net
> I've been working on an Access 97 database that's pretty much reached
> it's limit in terms of performance and reliability. Although it
> supports relatively few users (5-10 concurrent) it contains a lot of
> data (around 30 tables, some with several million records) plus a lot
> of forms and reports.
> The company has recently purchased an MS SQL server and it's my job to
> migrate the back-end as well as re-write the front-end (the front-end
> needs re-writing anyway).
> My question is do we keep Access as the front end (we'd probably
> upgrade to 2003) or would we get better performance using a different
> solution (VB.Net for example) ?
> If Access, would it be best use MDB's or ADP's?
> The main consideration is that we need to perform some complicated
> queries on large datasets, which ideally should happen on the SQL
> server (I'm currently learning about stored procedures and views etc).
> Thanks,
> PSB
My immediate reaction would be to stay with Access as your front-end,
and use an MDB for it. Some aspects of the application may need to be
rewritten, but in my experience that will probably be much quicker than
rewriting the whole thing in another devleopment environment. Moving
complicated queries to the server, though, is likely to take a fair
amount of work, and you have to budget for that. If the queries don't
use VBA functions or the First() or Last() functions, I don't think
you'll have too many problems, but if they do you'll have to figure out
how to accomplish the same function using SQL Server features and T-SQL.
Incidentally, one thing to watch out for -- and I know because it's
bitten me a few times -- is that your queries and application logic may
currently make assumptions about the order in which records are returned
in the absence of an ORDER BY clause. For example, in Jet SQL the GROUP
BY clause implicitly orders the records, but in T-SQL it doesn't. I
mention this only as something to keep in mind as you transfer queries
to SQL Server.
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|||Dirk Goldgar wrote:
> in Jet SQL the GROUP
> BY clause implicitly orders the records, but in T-SQL it
> doesn't.
Jet implicitly orders using what criteria?
Jamie.
|||onedaywhen wrote:
> Dirk Goldgar wrote:
> Jet implicitly orders using what criteria?
> Jamie.
Alphabetical/Numercal IOW "GroupBy SomeField" implicitly adds an "ORDER BY
SomeField" in Access/Jet.
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
|||"onedaywhen" <jamiecollins@.xsmail.com> wrote in message
news:1109076635.869509.10730@.g14g2000cwa.googlegro ups.com...
> Dirk Goldgar wrote:
> Jet implicitly orders using what criteria?
As I recall Jet orders the result set by the table's PK.
Steve
|||When you use GROUP BY in the absence of an ORDER BY clause Jet will sort by
the columns used in the GROUP BY clause, from left to right. For example,
this query ...
SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID;
... is equivalent to this query ...
SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID
ORDER BY tblTest.TestText, tblTest.TestNumber;
... however in this query ...
SELECT tblTest.TestText, tblTest.TestNumber, tblTest.ID
FROM tblTest
GROUP BY tblTest.TestText, tblTest.TestNumber, tblTest.ID
ORDER BY tblTest.ID;
... the explicit ORDER BY clause over-rides the implicit ordering of the
GROUP BY clause.
Brendan Reynolds (MVP)
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:Oco9FqOGFHA.1392@.tk2msftngp13.phx.gbl...
> "onedaywhen" <jamiecollins@.xsmail.com> wrote in message
> news:1109076635.869509.10730@.g14g2000cwa.googlegro ups.com...
> As I recall Jet orders the result set by the table's PK.
> Steve
>
|||Brendan Reynolds wrote:
> When you use GROUP BY in the absence of an ORDER BY clause Jet will
sort by
> the columns used in the GROUP BY clause, from left to right.
Thanks everyone. I got round to testing and I concur the following:
- the implicit ORDER BY is by columns left to right, ascending;
- the process occurs in Jet rather than MS Access;
- it is unaffected by clustered index, primary key, date/time entered
order etc.
Thanks again,
Jamie.

Need Help About SQL DEV 2000 SRV PERFORMANCE

Hi
we are using develeoper server 2000 with 2 gb RAM, G3 version 4 way XEON 2.4 cpu server. We are implementing ERP and data base for now small.But even this situation end users have slow working problems. Anybody have an idea about the whst kind of things I have to check? (for example; from performance monitor, how can I satart to use RAM capacity in tempdb, etc....
PS : pls inform me from my MikeK_70@.hotmail.com adress......
Than
GiacomoTry these:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/itwforsql.asp
http://www.sql-server-performance.com/performance_monitor_counters.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp
http://www.winnetmag.com/SQLServer/Article/ArticleID/26922/26922.html
http://www.sql-server-performance.com/q&a2.asp
GL
--
Ray Higdon MCSE, MCDBA, CCNA
--
"GiacomoC" <MikeK_70@.hotmail.com> wrote in message
news:09E6F9F3-DF74-41A4-A403-181AA04CA937@.microsoft.com...
> Hi,
> we are using develeoper server 2000 with 2 gb RAM, G3 version 4 way XEON
2.4 cpu server. We are implementing ERP and data base for now small.But even
this situation end users have slow working problems. Anybody have an idea
about the whst kind of things I have to check? (for example; from
performance monitor, how can I satart to use RAM capacity in tempdb,
etc....)
> PS : pls inform me from my MikeK_70@.hotmail.com adress.......
> Thanx
> Giacomo

Need Help About SQL DEV 2000 SRV PERFORMANCE

Hi,
we are using develeoper server 2000 with 2 gb RAM, G3 version 4 way XEON 2.4
cpu server. We are implementing ERP and data base for now small.But even th
is situation end users have slow working problems. Anybody have an idea abou
t the whst kind of things
I have to check? (for example; from performance monitor, how can I satart to
use RAM capacity in tempdb, etc....)
PS : pls inform me from my MikeK_70@.hotmail.com adress.......
Thanx
GiacomoTry these:
http://msdn.microsoft.com/library/d...r />
orsql.asp
http://www.sql-server-performance.c...or_counters.asp
http://msdn.microsoft.com/library/d...compilation.asp
http://www.winnetmag.com/SQLServer/...6922/26922.html
http://www.sql-server-performance.com/q&a2.asp
GL
Ray Higdon MCSE, MCDBA, CCNA
--
"GiacomoC" <MikeK_70@.hotmail.com> wrote in message
news:09E6F9F3-DF74-41A4-A403-181AA04CA937@.microsoft.com...
> Hi,
> we are using develeoper server 2000 with 2 gb RAM, G3 version 4 way XEON
2.4 cpu server. We are implementing ERP and data base for now small.But even
this situation end users have slow working problems. Anybody have an idea
about the whst kind of things I have to check? (for example; from
performance monitor, how can I satart to use RAM capacity in tempdb,
etc....)
> PS : pls inform me from my MikeK_70@.hotmail.com adress.......
> Thanx
> Giacomo

Need Better Performance

Dear friends,
I am having 10GB for my database. In my table having 10 Millian records per day. I want to select records per date and by product status. So,

1. How can I design my Database initialization paramet ?

2. How can I tune my database for better performance.

My Machine Configuration :

Windows 2003 Server OS,1 GB Ram, 80 GB Hard Disk, 3.99 GHz

We need more details about your hardware and environment, along with your table schema and one of the queries you want to tune.

Having a single hard drive for a SQL Server instance is going to be a big bottleneck.

Need ammunition against 'clustered index hampers performance'

(alexander.arvidsson@.gmail.com) writes:

Quote:

Originally Posted by

The creators of the software that my customer uses (two different
systems) BOTH claim that using clustered indexes hampers performance,
each and every time. I can't find ANY resource on the internet that
validates this, quite the opposite. I am told that the best practices
is to always us a clustered index on a table.
Following their own guidelines, there is no clustered index in sight,
and hence some tables have a whopping 30GB(!) of unused space.

SQL Server MVP Greg Linwood has argued fiercely for heaps, but it is
obvious that heaps require much more manual management. Else, you end
up with badly fragmented tables, as in your customer's case.

But if the developers think that clustered index is worse than sin, then
just set up a job that adds a clustered index to the table and then
drops it. It will run for a longer time than a regular reindexing,
as all non-clustered indexes will have to be rebuilt. Twice. Or drop
the non-clustered indexes first, and then add them back at the end.

Hopefully, someone will object to this and ask "isn't there a
simpler way?", whereupon you answer "sure, we could use a clustered
index instead, but it takes price to be on top".

If you can find the resources to set up a parallel environment as
DA suggested, then it should be an easy game.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxA parallel environment would be the best, but in this case I'll have
no such luck. I was a bit surprised to see that there is some dissent
as to what type of indexes to use. Don't get me the the wrong way; I
certainly understand that everything has its time and place, but I've
been fed that clustered indexes is the way to go, all the way, every
day, practically since I started with SQL Server. I'll burrow down in
Greg's blog and probably pick up Kalen Delaney's book as well.

A huge thanks to all of you for giving me perhaps not the answer I was
expecting, but instead something to ponder for quite a while :)|||(alexander.arvidsson@.gmail.com) writes:

Quote:

Originally Posted by

A parallel environment would be the best, but in this case I'll have
no such luck. I was a bit surprised to see that there is some dissent
as to what type of indexes to use. Don't get me the the wrong way; I
certainly understand that everything has its time and place, but I've
been fed that clustered indexes is the way to go, all the way, every
day, practically since I started with SQL Server. I'll burrow down in
Greg's blog and probably pick up Kalen Delaney's book as well.

Let me put it this way: a formula one race car is much faster than a
standard car. But if you want to go from Stockholm to Malm, you may
still make that trip faster with a standard car if you travel along.
The F1 car needs much more support and maintenance.

Greg has a very strong experience in the perf-tuning field, but his
advice is not for every one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Need advice on system performance

Hi,

I am starting a new job next week. Part of what I am required to do is
a "system diagnostic" on a SQL Server 2000 box to determine what areas
can use improvement - this would include configuration settings,
backup/recovery, sql tuning, etc... and anything else I may not have
mentioned here!

What I need is a thorough and systematic approach to doing this. Can
anyone please give me advice, or point to a FAQ or other links that
discuss this. I am running out of time.

THANKS MUCHtootsuite,

This is one link you might want to check out.

http://www.microsoft.com/downloads/...&displaylang=en
Also, this site has a wealth of performance information:

http://www.sql-server-performance.com/
-- Bill

<tootsuite@.gmail.comwrote in message
news:1170959288.304029.270560@.l53g2000cwa.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I am starting a new job next week. Part of what I am required to do is
a "system diagnostic" on a SQL Server 2000 box to determine what areas
can use improvement - this would include configuration settings,
backup/recovery, sql tuning, etc... and anything else I may not have
mentioned here!
>
What I need is a thorough and systematic approach to doing this. Can
anyone please give me advice, or point to a FAQ or other links that
discuss this. I am running out of time.
>
THANKS MUCH
>

|||Thank you

- this link shows how to do a performance audit:

http://www.sql-server-performance.c...icles_audit.asp

Near Zero latency partition updates + query performance

Hi,

Here is the scenario:

I have about 5 dimensions, 2 measure groups, 10 measures.

The data is in Sybase database.

The data arrival is Max 10,000 rows in 1 minute.

per day Max is 5 million rows.

The users want to see the latest data.

I cant use ROLAP or HOLAP because query performance is a issue.

1. I believe that I can't use Proactive caching because we dont use sql server. is this true?

2. I have tried process add, but am not sure if this will work. especially if I have to invoke it every 1 minute!

3. I have to try SSIS, but again am not sure how to do this.

4. Is this really possible, can I update the partitions so that new data arriving every 1 minute is avaliable to the users?

Please do give me differnet strategies that are possible, I would try them all out.

Regards

"1. I believe that I can't use Proactive caching because we dont use sql server. is this true?"

There are 3 notification mechanisms provided for Proactive Caching, only 1 of which requires a SQL Server data source:

http://www.microsoft.com/technet/prodtechnol/sql/2005/rtbissas.mspx#E2AAC

>>

SQL Server 2005

Real-time Business Intelligence Using Analysis Services

Published: April 1, 2005

By Paul Sanders

This paper covers features in SQL Server 2005 Analysis Services that overcome many of the barriers to gathering and analyzing real-time business intelligence data faced by companies today.

...

Notification Schemes

There are three notification schemes by which Analysis Services can be notified of updates to the underlying source:

?

Trace Events. This only applies where the underlying data source is SQL Server. Analysis Services will register to receive trace events on the necessary tables. This scheme requires that the service account of Analysis Services has Administrator privileges on the SQL Server database. Event delivery is not guaranteed if the database is unavailable (e.g., due to network issues) at the time of update.

?

Client Initiated. A client can simply send a notification message to Analysis Services, indicating that a specified table has been updated. This is relevant in cases where the application that is actually performing the update is aware of the impact on the Analysis Services cubes

?

Polling. The most generally applicable approach is to use polling. Polling queries are defined for each table, returning a single value (i.e., a single row, with one column), a change to which indicates an update to the table. For example, a query returning the maximum value of the LastUpdated timestamp column would be a suitable polling query. Along with the query, the designer also specifies the frequency with which Analysis Services should send the polling query.

>>