Hi all,
I am researching this topic as best I can via Google etc. Microsoft of course doesn't give information away cheaply (10 pages per useful nugget of info.).
I'm wondering if anyone here has experience of ADP and SQL Server Express in order that I can decide on whether or not I invest lot's of time in experimenting or perhaps discard the idea all together.
Current set-up:
Windows XP clients
MS Access 2003 - JET.
Split application (back-end data / front-end forms etc) - both on server.
Back-end size: 200 mb, front-end 30 mb.
Tables: 100. Queries. 200. Reports: 50.
50 users and growing. 20 max concurrent.
I understand that ideally: the front-ends should be on the clients, but after experimentation - it seemed to make v. little difference.
I also understand that 20 users is already 300% over-subscribed (performance-wise).
The application resides on a common data-file server (I know - I know, but you'll know how companies work also). Network speeds are atrocious and rapidly declining. I am going through a long drawn-out process of negotiating re-location to a 'dedicated' virtual server.
I use the 'split' method to make copying the front-end to other locations much more efficient for development purposes.
Before the move to a virtual server happens, I may as well experiment with a sub-set of the application by using Access 2003 as the front-end and SQL Server Express on the back-end. I realise that there is also a 5 user restriction (and then it get's slow - intentionally), but, I will still be able to compare data-retrieval speeds etc. Once proved, then budget won't be a problem for full SQL Server licenses.
The application also connects to different Oracle databases (which cannot be used for development purposes).
The database design is relational and normalised, app. is VB only (as opposed to macros), generally professionally developed.
The possibility of moving to a later MS Access version is possible (although will be traumatic - as you can't create an Excel spreadsheet without first checking with Governance).
Questions:
1) Has anyone got any experience of this intended set-up, and any negatives or positives to relate?
2) Is the transition (conversion to stored-procs etc) messy, many inconsistencies?
3) I'm not clear on the front-end data connections: linked / strings / pass-thru' queries or what?
4) Will a move to SQL Server Express show any immediately obvious performance gains (all other things being equal)? (I'm assuming that a 'true' client-server database will help).
Any pointers will be very much appreciated - thanks in advance.
ATB
Darrylle
Never argue with an idiot, he'll bring you down to his level - then beat you with experience.
I am researching this topic as best I can via Google etc. Microsoft of course doesn't give information away cheaply (10 pages per useful nugget of info.).
I'm wondering if anyone here has experience of ADP and SQL Server Express in order that I can decide on whether or not I invest lot's of time in experimenting or perhaps discard the idea all together.
Current set-up:
Windows XP clients
MS Access 2003 - JET.
Split application (back-end data / front-end forms etc) - both on server.
Back-end size: 200 mb, front-end 30 mb.
Tables: 100. Queries. 200. Reports: 50.
50 users and growing. 20 max concurrent.
I understand that ideally: the front-ends should be on the clients, but after experimentation - it seemed to make v. little difference.
I also understand that 20 users is already 300% over-subscribed (performance-wise).
The application resides on a common data-file server (I know - I know, but you'll know how companies work also). Network speeds are atrocious and rapidly declining. I am going through a long drawn-out process of negotiating re-location to a 'dedicated' virtual server.
I use the 'split' method to make copying the front-end to other locations much more efficient for development purposes.
Before the move to a virtual server happens, I may as well experiment with a sub-set of the application by using Access 2003 as the front-end and SQL Server Express on the back-end. I realise that there is also a 5 user restriction (and then it get's slow - intentionally), but, I will still be able to compare data-retrieval speeds etc. Once proved, then budget won't be a problem for full SQL Server licenses.
The application also connects to different Oracle databases (which cannot be used for development purposes).
The database design is relational and normalised, app. is VB only (as opposed to macros), generally professionally developed.
The possibility of moving to a later MS Access version is possible (although will be traumatic - as you can't create an Excel spreadsheet without first checking with Governance).
Questions:
1) Has anyone got any experience of this intended set-up, and any negatives or positives to relate?
2) Is the transition (conversion to stored-procs etc) messy, many inconsistencies?
3) I'm not clear on the front-end data connections: linked / strings / pass-thru' queries or what?
4) Will a move to SQL Server Express show any immediately obvious performance gains (all other things being equal)? (I'm assuming that a 'true' client-server database will help).
Any pointers will be very much appreciated - thanks in advance.
ATB
Darrylle
Never argue with an idiot, he'll bring you down to his level - then beat you with experience.
