Database for internet applications

I have a colleague who wants to build a web application that is expected to
have a fairly low number of web requests and have this site capture data in
a database. His heart is set upon Access as the back-end database.
Now, I'm happy to accept that Access is a great product, but I know very
little about it - I've always used SQL Server. My gut reaction is to
suggest that he uses SQL Server Express in preference to Access, but
unfortunately I don't know enough about Access to argue why it shouldn't be
used as a database behind a web application. I don't even know much about
the licensing model.
Could someone provide the details here (or persuade me otherwise)?
Thanks everyoneGriff
SQL Server 2005 Express Edition is free but limited to 4GB db size.If I
remeber well Access is limited to 2GB db size.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||On Oct 18, 6:18 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> I have a colleague who wants to build a web application that is expected to
> have a fairly low number of web requests and have this site capture data in
> a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't be
> used as a database behind a web application. I don't even know much about
> the licensing model.
Some notes here:
http://members.optushome.com.au/anandasim/asp.htm
You don't have to worry about licensing. Because when you use
Access .mdb as a back end database, you are not using an Access
license - you are using IIS + ADO/ADO.NET + mdb - Access does not even
need to be installed on the webserver.
One concern is .mdb is not 24x7 - you need to take it offline from
time to time to pack the database and back it up - on the other hand,
if you get the time slot and scheduled batch jobs running, you can
just grab the .mdb and put it in your pocket.
One thing to consider is if you are using a development IDE against
the Access .mdb will there be design time issues with controls and
connections working properly - I used to have those years ago. And Jet
is single threaded - when I had SQL that was half baked during
development, the whole web server would be unresponsive.
HTH
Ananda|||The salomic decission
Use SQL server as the datastore & msAccess as a FrontEnd for maintenance,
reports etc
For the solution you describe the only drawbacks of using Access as a
Back-end is that it has no built in maintenance plan,
must be "offline" for backup, can get corrupted.
Pieter
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||There are no licensing issues with supplying data from an Access database
file to a web page.
There is some advantage in using an Access file to store the data, if you
need to enter, query, and report on the data from within Access.
It is not difficult for someone with experience in something like ASP or PHP
to design a web page where data is read from and/or written to an Access
database.
Access data is not really secure, so you need to ensure you handle the
security at another level. For example, last year I was able to download the
entire MDB from a computer hardware vendor because the database went
corrupt, and the error message showed the name and location of the file. In
this case, the db contained only information that was on the web (i.e. parts
and prices, not customers and orders) so no harm was done (though I did
notify them.)
If you just need to export the data from an Access table to a web page (e.g.
a product listing that needs to be re-exported whenever products or prices
change), you can also do that easily. Here's an example:
http://allenbrowne.com/AppOutputHtml.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone|||One thing about Access as far as web applications are concerned is that it
is still a file. If I happen to know the physical path where the Access
database file is stored (a vulnerability exposed can do this), I can
download the database file from the site ;-)
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||Wow - thanks for all the feed-back everyone.
Another thing I've just thought of... Access didn't have the concept of
stored procedures - I don't know if the latest version now does. If not,
then it's of course more vulnerable to SQL Injection attacks.|||You can emulate stored procedures in Access but only in version 2002 and
higher altough a few claim that Access 2000 can do it too. There are some
limitations though such as no temp tables, output parameters cannot be used,
does not use the @. character
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:e%231tviXEIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Wow - thanks for all the feed-back everyone.
> Another thing I've just thought of... Access didn't have the concept of
> stored procedures - I don't know if the latest version now does. If not,
> then it's of course more vulnerable to SQL Injection attacks.
>|||In article <uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl>, griffithsj_520
@.hotmail.com says...
> I'm happy to accept that Access is a great product
Access is only good for single user database tasks, it's always been
unstable for more than one user at at time.
Use one of the MSDE SQL versions or other versions for your solution and
you'll be a lot happier than using Access.
If you need reports you can use MS Access to pull them, but don't use
Access for a database.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Leythos wrote:
> In article <uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl>, griffithsj_520
> @.hotmail.com says...
>> I'm happy to accept that Access is a great product
> Access is only good for single user database tasks, it's always been
> unstable for more than one user at at time.
> Use one of the MSDE SQL versions or other versions for your solution and
> you'll be a lot happier than using Access.
> If you need reports you can use MS Access to pull them, but don't use
> Access for a database.
You probably missed the fact that this thread is crossposted to an
Access NG, or do you really believe what you wrote about Access?
--
Roy-Vidar|||Allen,
I took a look at your link with some interest, do you know how I can go
about doing the reverse; importing data Into Access 2007 from a webpage;
specifically Googlemaps. Here's what I want to do:
I want to click on an address filed in my Access form and take that address
to googlemaps and have it return a Latitude, longtitude. Before I continue,
let me explain that I already have a fully functioning database with a form
that does the first part succesffuly, namely sending the address (in the
correct google friendly format) to googlemaps, and the browser succesfully
finds the address, and using a combination of a single line Javascript (not
authored by me, I don't understand Java) and some cumbersome Access Sendkeys
I am able to get the Lat/Long and pasted it back into the field. This
approach has it's drawbacks, it involves Alt-tabbing two application windows
and when the net connection is busy, then the timing of the Senkeys gets out
of sequence and the whole process fails, If I increase the timings, then the
process becomes to slow. In summary, it is a bit of a Heath-Robinson setup.
So I was wondering if you could give me some pointers and an understanding
on how to go about automating the process properly. I can understand VBA to a
certain extent, but really don't know where to start. What do you think, can
it be done?
"Allen Browne" wrote:
> There are no licensing issues with supplying data from an Access database
> file to a web page.
> There is some advantage in using an Access file to store the data, if you
> need to enter, query, and report on the data from within Access.
> It is not difficult for someone with experience in something like ASP or PHP
> to design a web page where data is read from and/or written to an Access
> database.
> Access data is not really secure, so you need to ensure you handle the
> security at another level. For example, last year I was able to download the
> entire MDB from a computer hardware vendor because the database went
> corrupt, and the error message showed the name and location of the file. In
> this case, the db contained only information that was on the web (i.e. parts
> and prices, not customers and orders) so no harm was done (though I did
> notify them.)
> If you just need to export the data from an Access table to a web page (e.g.
> a product listing that needs to be re-exported whenever products or prices
> change), you can also do that easily. Here's an example:
> http://allenbrowne.com/AppOutputHtml.html
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> "Griff" <griffithsj_520@.hotmail.com> wrote in message
> news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
> >I have a colleague who wants to build a web application that is expected to
> >have a fairly low number of web requests and have this site capture data in
> >a database. His heart is set upon Access as the back-end database.
> >
> > Now, I'm happy to accept that Access is a great product, but I know very
> > little about it - I've always used SQL Server. My gut reaction is to
> > suggest that he uses SQL Server Express in preference to Access, but
> > unfortunately I don't know enough about Access to argue why it shouldn't
> > be used as a database behind a web application. I don't even know much
> > about the licensing model.
> >
> > Could someone provide the details here (or persuade me otherwise)?
> >
> > Thanks everyone
>

database files locked out by SQL?

I have a SQL2005 SP2 server hosting several databases for different
applications. Recently i stood up a virtual server with Office
Communications Sever 2007 using my existing physical SQL 2005.
Everytime a reboot is required on the SQL server, the databases for OCS get
locked out. It took me a while to figure out what was going on, at first i
thought they were getting corrupted. After using SysInternals Process
explorer, I was able to determine that it is SQL itself that is locking the
database files and logs out, but apparently it isn't communicating that with
itself because when I try to bring them online, i get an error stating that
the files are in use or the disk is full.
Has anyone seen this before? I'm not an SQL expert by any means, so please
bear with me.
Cheers
Joe> Everytime a reboot is required on the SQL server, the databases for OCS get
> locked out.
I don't understand exactly what is meant here. The database files are indeed
locked by the SQL Server process, and that is expected. The files are not
supposed to be accessed by anything other than SQL Server. What error
messages are getting? What are you trying to accomplish that is prevented by
the files being locked?
Linchi
"Josef Hanning" wrote:
> I have a SQL2005 SP2 server hosting several databases for different
> applications. Recently i stood up a virtual server with Office
> Communications Sever 2007 using my existing physical SQL 2005.
> Everytime a reboot is required on the SQL server, the databases for OCS get
> locked out. It took me a while to figure out what was going on, at first i
> thought they were getting corrupted. After using SysInternals Process
> explorer, I was able to determine that it is SQL itself that is locking the
> database files and logs out, but apparently it isn't communicating that with
> itself because when I try to bring them online, i get an error stating that
> the files are in use or the disk is full.
> Has anyone seen this before? I'm not an SQL expert by any means, so please
> bear with me.
> Cheers
> Joe|||Sorry, guess I should have been more clear with the issue. After the reboot,
the databases will not come online properly. OCS gets application connection
errors, and you can't actually expand the database folder within SQL manager.
If I try and take the databases offline and then online again, I get a file
in use access error. I've managed to get the databases online by stopping
all SQL services and restarting but I shouldn't have to do this after a
reboot.
"Linchi Shea" wrote:
> > Everytime a reboot is required on the SQL server, the databases for OCS get
> > locked out.
> I don't understand exactly what is meant here. The database files are indeed
> locked by the SQL Server process, and that is expected. The files are not
> supposed to be accessed by anything other than SQL Server. What error
> messages are getting? What are you trying to accomplish that is prevented by
> the files being locked?
> Linchi
> "Josef Hanning" wrote:
> > I have a SQL2005 SP2 server hosting several databases for different
> > applications. Recently i stood up a virtual server with Office
> > Communications Sever 2007 using my existing physical SQL 2005.
> >
> > Everytime a reboot is required on the SQL server, the databases for OCS get
> > locked out. It took me a while to figure out what was going on, at first i
> > thought they were getting corrupted. After using SysInternals Process
> > explorer, I was able to determine that it is SQL itself that is locking the
> > database files and logs out, but apparently it isn't communicating that with
> > itself because when I try to bring them online, i get an error stating that
> > the files are in use or the disk is full.
> >
> > Has anyone seen this before? I'm not an SQL expert by any means, so please
> > bear with me.
> >
> > Cheers
> >
> > Joe|||"Josef Hanning" <JosefHanning@.discussions.microsoft.com> wrote in message
news:BEC23679-2F5F-448E-BBC4-5ECA8C8E6C70@.microsoft.com...
> Sorry, guess I should have been more clear with the issue. After the
> reboot,
> the databases will not come online properly. OCS gets application
> connection
> errors, and you can't actually expand the database folder within SQL
> manager.
> If I try and take the databases offline and then online again, I get a
> file
> in use access error. I've managed to get the databases online by stopping
> all SQL services and restarting but I shouldn't have to do this after a
> reboot.
Sounds to me like something is competing for the files - first one wins and
locks the other(s) out. Do you have multiple instances of sql server
installed - each of which is configured to use the same user database(s)?|||Scott,
Thanks for the reply. There looks to be 3 instances of SQL running on the
box, the default, the RTC instance created by the OCS installation, and an
OCS instance installed using SQL setup by a co-worker. The OCS instance has
never been used to my knowledge and I have not done any database
configuration to any of the other instances myself.
From your question I gather that I need to somehow configure my RTC
databases for OCS to be used by only one of these instances of SQL?
thanks!
-Joe
"Scott Morris" wrote:
> "Josef Hanning" <JosefHanning@.discussions.microsoft.com> wrote in message
> news:BEC23679-2F5F-448E-BBC4-5ECA8C8E6C70@.microsoft.com...
> > Sorry, guess I should have been more clear with the issue. After the
> > reboot,
> > the databases will not come online properly. OCS gets application
> > connection
> > errors, and you can't actually expand the database folder within SQL
> > manager.
> > If I try and take the databases offline and then online again, I get a
> > file
> > in use access error. I've managed to get the databases online by stopping
> > all SQL services and restarting but I shouldn't have to do this after a
> > reboot.
> Sounds to me like something is competing for the files - first one wins and
> locks the other(s) out. Do you have multiple instances of sql server
> installed - each of which is configured to use the same user database(s)?
>
>|||"Josef Hanning" <JosefHanning@.discussions.microsoft.com> wrote in message
news:84729A32-1E6D-4CED-9343-A1FE3EC35E02@.microsoft.com...
> Scott,
> Thanks for the reply. There looks to be 3 instances of SQL running on the
> box, the default, the RTC instance created by the OCS installation, and an
> OCS instance installed using SQL setup by a co-worker. The OCS instance
> has
> never been used to my knowledge and I have not done any database
> configuration to any of the other instances myself.
> From your question I gather that I need to somehow configure my RTC
> databases for OCS to be used by only one of these instances of SQL?
What you need to do I cannot answer. I can only say that the set of
phsyical files that comprise a database should only be used within a single
instance. Multiple instances cannot "share" a database. It sounds like
you may need to get additional expertise to help with this situation.

Database Design Question

Which of the following options is best?

Option A)

One database with all the tables for all applications (HR, TechStaff, Inventory, Purchasing, etc.). The bonus to this is all the data is stored in one place and the data is not duplicated, but when the database has to come down, all applications will be unavailable. Plus, volume may be present an read/write row/column issue.

Option B)

Multiple databases... one for each application. Then to replicate the necessary tables to each database as needed. For example, replicate the employee table (ID, firstname, lastname) to the databases that would require that data. The replication would be setup to be one-way replication. Bonus is that if one database goes down, the others are still available, but a downside is the same data is stored in multiple places and updated through replication (which could slow down the server).

Thoughts?

Seems like you are using 'database' to refer to an 'instance' of SQL Server? If your primary concern is high-availability, you may want to consider cluster or mirroring or even log shipping.

Thanks

|||

I would go with B option. It proved to be more flexiblein my case. I used a database for each application. The employees were stored in a separate database that was populated from Active Directory. Each application had an employee table that contained only the authorized employees for that specific application.

|||

Sunil Agarwal wrote:

Seems like you are using 'database' to refer to an 'instance' of SQL Server? If your primary concern is high-availability, you may want to consider cluster or mirroring or even log shipping.

Thanks

Seperate databases vs a single database on one instance os SQL Server.|||

Cristian Lefter wrote:

I would go with B option. It proved to be more flexiblein my case. I used a database for each application. The employees were stored in a separate database that was populated from Active Directory. Each application had an employee table that contained only the authorized employees for that specific application.

That is what I was doing now... but instead of maintaining multiple employee tables across many databases, I would setup the server to automatically replicate the employee tables from the main employee databases to cascade the changes down to all the other databases the required some sort of employee data.|||Both have their benefits but replication does seem to be a bit of an overhead in this case.

There is an alternative and that is to create individual databases for each application. Then, rather than replicate the data, you can create a view in the source database selecting the data you need to display in new database (eg EmployeeList - a list of employees in a HR database). This can join several table together as you would in any normal view. In the new database you create a new view which contains a simple SQL statement of "SELECT * FROM HrDbName.EmployeeList" and call it anything you like. You can then use this view as you would any other table or view. Provided your users have access to the data in both databases then it will appear as though the data resides in the same database.

This does not stop the problem of one database being unavailable affecting the others but it should result in a number of smaller databases targeted at one objective while still enabling you to effectively share the data between applications.

If the databases are on a different server then you can include the server name in the SELECT statement but you will also have to set up security between the servers.

This is a technique I have used for externally provided applications where we write our own queries. By setting up an enquiry database with views direct to the tables in the suppliers database I can create as many views as i need without any dabger of breaking the application now or in the future.

database design from existing DBases

Hi All,
There are 3 applications each one of has respective databases.Now i am trying to design a database which should capture the important entities from these systems.
what is the best approach to follow?
Thanks,
rajHave you defined the important entities that need to be included in your new database?|||I am in the process of identifying the entites.
The problem is : 3 legacy applications store the same piece of information with different names in their databases.

I am building a new application taking attributes, union of all systems.
because there is no DB design design avaialble for existing systems.
I am using profiler and tracking the queries fired on to server and identifying the attributes and entities.

Is there any other best approach for doing this?

Thanks,