SQL server upgrade?

[davidoc]

Folks,
I appreciate any experience/advice on this one.

I have a MS access 2002 database system with a FE/BE combination. There are about 100 forms, 400 queries, 50 reports on the FE, and some 30,000 records in different tables on the BE which resides on a server. Currently there are 12-14 users and things are working ok (not lightning fast but acceptable).

They now want to extend the number of users to 20+ and I'm not sure exactly what increase in volume of records this will mean but there will definately be a noticable effect.

Will speed/efficiency become a problem? From what I have read/seen I fear that it will...

What are my options? From what I gather, an upgrade of the BE to SQL server would be an appropriate solution. I only have access and limited oracle experience so this is unknown territory to me. Would the MS access FE work perfectly with a SQL server BE? Are there any pitfalls I should be aware of? Would MSDE be a viable alternative to SQL server in this case.

I have been searching through the archives and haven't found much. I'd appreciate any help

thanks!

Dave

 

[davidoc]

Thanks Pat, that has answered all of my questions! I'm going see how it copes with the increased load first before I jump in with any new BE. (All users have a local copy of the the FE BTW)

I've been careful about table/col names so that shouldn't be a problem. I would be more worried about queries using access specific sql syntax and parameters though I don't know if that is really a problem (something I am currently reading up on...)

cheers
Dave

 

[Parker]

Thanks Pat, that has answered all of my questions! I'm going see how it copes with the increased load first before I jump in with any new BE. (All users have a local copy of the the FE BTW)

I've been careful about table/col names so that shouldn't be a problem. I would be more worried about queries using access specific sql syntax and parameters though I don't know if that is really a problem (something I am currently reading up on...)

cheers
Dave

While you're 'reading up' have a look at MySQL it's a very real alternative and v cost effective.

 

[Kevin_S]

While you're 'reading up' have a look at MySQL it's a very real alternative and v cost effective.

Personal SQL (MSDE) is NOT AN OPTION with multiple users as it has a govenor to automatically decrease performance... Personal SQL/ MY SQL/ MSDE (which ever you want to call it) is designed to be used by one user or - at the most - 3-4 users... any more then that and it automatically decreases performance...

HTH,
Kev

 

[Parker]

Personal SQL (MSDE) is NOT AN OPTION with multiple users as it has a govenor to automatically decrease performance... Personal SQL/ MY SQL/ MSDE (which ever you want to call it) is designed to be used by one user or - at the most - 3-4 users... any more then that and it automatically decreases performance...

HTH,
Kev

Errr don't wish to appear argumentative but have you told Yahoo about this? A good part of Yahoo is run on MySQL.

On a half decent server MySQL will support upwards of 500 simultaneous connections without even noticing.

I don't know how you can group MySQL with MSDE
I think you may be confusing MySQL with something else.
Also Its open source -- the best form of software

 

[Kevin_S]

Errr don't wish to appear argumentative but have you told Yahoo about this? A good part of Yahoo is run on MySQL.

On a half decent server MySQL will support upwards of 500 simultaneous connections without even noticing.

I don't know how you can group MySQL with MSDE
I think you may be confusing MySQL with something else.
Also Its open source -- the best form of software

Oh...!

Thought you were just calling it My SQL the same as some people call MSDE "Personal SQL".... wasn't making the distinction between the two as I've never had any interaction with MySQL (always associated MySQL as more web-centered then other RDBMS)...

I'm a little thick headed today anyway... must be something in the water...

 

[Kodo]

MySQL Lacks some enterprise level power. Triggers, Stored Procedures, UDF's.. It's great for websites, but not that great for enterprise level development. I'm surprised Yahoo is using it. You could cram about 1/3 of your logic into Sproc's and UDF's as opposed to scripting it all out and let the db do more of the work than your web server.

MySQL was made popular by a bunch of PHP zealots. The fact that it's free is its' only saving grace.

 

[Parker]

MySQL Lacks some enterprise level power. Triggers, Stored Procedures, UDF's.. It's great for websites, but not that great for enterprise level development. I'm surprised Yahoo is using it. You could cram about 1/3 of your logic into Sproc's and UDF's as opposed to scripting it all out and let the db do more of the work than your web server.

MySQL was made popular by a bunch of PHP zealots. The fact that it's free is its' only saving grace.

Me ooow.

Cor thats opened up a can o worms.

I'd take a fresh look at it if I where you 'cuase you'll get left well behind ifn you're not careful

Next thing is people will be saying that Linux is not a threat to Bill Gates --- Funny seeing as how he keeps having a pop at Linux or any open source community. And is trying to introduce a Risk bassed OS.

Do you know what Apple OS is bassed on?

Don't indiscriminatley knock MySQL it has come a long way and is still developing. It dosn't have any more issues than any other system. And no software is perfect. The differences are.

1. Its very cost effective
2. Dosn't have any of the restrictions of most of the smaller comercial systems
3. is open so it can be tweeked/developed on the fly
4. Is rapidly gaining in support
5. Isn't rulled by Bill Gates (YET)

And as for PHP well

 

[Kodo]

not knocking PHP at all. PHP isn't my bag.

I've used MySQL in the past. Wasn't a bad DB for web stuff. It was very cost effective license wise. It was a PITA to set up on Windows Server. So much so that I ended up in one instance having to use MSSQL.

Not be rich my self, I figured that it was a definite solution to my dilemma, but I very much enjoyed the power of SPROC's and TRIGGERS.. Saved me a lot clutter in my code. Which saved me time overall.

I think my post came off way more negative than I intended it to be. I was just amazed that Yahoo is using it when they could be using something so much more powerful like MSSQL, ORACLE or DB2.. Unless of course they are using MaxDB (MySQL) which is supposedly their enterprise version which I believe is another vendors DB that they (MySQL org) purchased. Can't remember which though.

For what most people do internally, MySQL is a very suitable DB. Just a tad over-rated IMO.

 

[davidoc]

I've had a look at mySQL - it looks like it could be a very cost effective solution. the company in question has a bit of a microsoft fetish so I might be stuck with microsoft products .

Thanks for all the replies though, esp Pat - you've been a great help

Dave

 

[Kodo]

if you try MySQL, then you can try something like NaviCat to administer it. The default tool that comes with it and the other one available from MySLQ.org are awful.