SQL Server or Sharepoint?

[C.Voce]

I am considering starting a business offering a cataloguing and library
management system to potentially hundreds of clients. Individual clients will
ideally have their own custom-made databases, although all of them will have
essentially the same features, altered slightly from case to case.

I would like to have a main table of data (common to all clients) which only
I can add to and edit, and then give clients the ability to copy any number
of records from this table into their database, also adding in other
information to related tables (which will differ from client to client). I
will need to give clients access to forms and reports, but also ideally have
the ability to monitor all of their data input remotely for the purcposes of
checking and consistency.

Would this be possible using SQL Server - having my main table on SQL
server, and then giving an .accdb file to my clients, linked to my main
table? Or will all the information need to be stored on my server in order
for me to be able to edit it? Alternatively, would Sharepoint better suit my
needs.

Any guidance greatly appreciated.

 

[Sylvain Lafontaine]

Sharepoint is not a server or a database server: it's a collection of
technologies and it needs SQL-Server for its database service; so the
question is: do you need SQL-Server or SQL-Server+Sharepoint.

What you can do can be done directly with only SQL-Server. However, if you
take into consideration things like security and the fact that Sharepoint
already provide a mecanism to synchronise some local ACCDB tables with a
SQL-Server by using web services instead of a direct communication, then
adding Sharepoint to it could be a good decision from your part.

There is also the question of licensing. Sharepoint is licensed for an
intranet use, not an extranet. This means that all of your clients should
have the proper CALs to connect to your server(s) for Windows 2003 and/or
SQL-Server; if the later is not licensed on a per processor basis.

Sharepoint itself is divided into WSS (Windows Sharepoint Services) and MOSS
(Microsoft Office Sharepoint Server). The basic functionality of Sharepoint
is provided with WSS. Moss add other functionalities on top of that but
require an additional licensing (both for MOSS itself and for the CALs).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Fred]

Sounds like sharepoint puts the usual Microsoft fuzzy haze in between people
and data.

Although a collaboration tool that is based on databases is a step forward
in collaboration.

 

[Sylvain Lafontaine]

Sharepoint has been primarely designed as an Intranet tool for the
management of documents and simple list of datas. While it can be more or
less used over the Internet for synchronising a collection of data between a
local database and a central server - ie., by using ODBC Linked tables to
Sharepoint Lists - this is not its primary design goal and other tools -
designing your own web services or using a local copy of SQL-Server Express
with the synchronising mode of SQL-Server 2008 (but also with SQL-2005 or
2000 to a lesser extent) - are available who are much more suited to this
task.

Sharepoint can be used to synchronise data over the internet for simple
cases where RI is not to much involved in the process; however, for more
complexe situations, you should use other tools. You should also notice
that with modern designs of databases distributed over the internet such as
with cloud technology and the REST protocol, the RI are de-emphased and is
to be replaced by other concepts.

There is no fuzzy haze from MS between people and data; however, many people
often don't use the most suitable tool for their particular task or are
stuck into past designs.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Fred]

Sylvan,

The people (like yourself) who know such products 100 times better than the
average user would not understand what I mean by the Microsoft fuzzy haze.

 

[Sylvain Lafontaine]

Oh, it's quite possible that I might have misunderstood your concept of
"Microsoft fuzzy haze". However, if I may refer to the OP and the main line
of discussion of this thread, my point was that with Sharepoint or even with
others, possibly more adapted - but also more complicated to use -
technologies, you cannot port a database over the Internet and expect it to
behave exactly like if it was still exclusively on your local computer, as
if the Internet was not there at all.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Albert D. Kallal]

SQL Server and Sharepoint are different tools. SQL Server is a powerful
database, Sharepoint is a collaboration tool that sits on top of SQL
Server.

If you use Sharepoint and have your clients connect through the Internet,
you'll pay an extra $41,000 (
http://office.microsoft.com/en-us/sharepointserver/FX102176831033.aspx).
The
number of records in each list needs to be kept low (<6,000) or
performance
will degrade on an Internet connection between Access and Sharepoint.

Just use the free officelive.com. You get SharePoint for free and there is
no up-front cost or licensing. Heck, you can have SharePoint up and running
and have linked tables to ms-access IN LESS TIME then it going to take me to
write this response. OfficeLive is really the bargain of the century right
now,
and furthermore it allows you to get into share point development without
having to set up (or purchase) share point.

One big problem with Sharepoint lists (pseudo-linked tables) is they don't
enforce referential integrity. If you or your clients don't have a way to
correct for that, your clients will have orphaned records. Do you or your
clients have this skill?

The above is not always such a problem. It depends on the complexity of the
application. I mean web developers were for years tooting their horn about
how good MySQL was. Yet it was only a few years ago
that MySql had referential integrity out of the box.

And, zillions of programs where developed using dbase and FoxPro and
clipper.
And again none of these applications had RI enforced at the engine level. A
few lines of recursive code in access, and you'll have yourself a cascading
delete routine in no time at all (I have one I written for SharePoint).

The very popular simply accounting package is based on jet and an
access mdb file. They don't use engine level enforced RI.

My point is here is that for many types of applications, simply adding one
extra line of code to delete child records is often not such a big deal.
Any developer who spend any amount of time in this industry will likely
have worked with many systems that don't necessarily have engine level
RI.

There's no question RI an extremely important feature to
have, but for many applications it's not that large of a shortcoming.

If your clients connect to Sharepoint through Access, they need Access
2003
or newer. Access 2003 has limited functionality with Sharepoint and can't
connect to the newest version of Sharepoint.

I not experienced the above. Access 2003 does connect for me ok.
On the other hand access 2007 works far better with share point.
I would NOT recommend access 2003 for this.

 

[C.Voce]

Many thanks for the various replies - all really useful. It sounds like
Sharepoint isn't really what I'm going to need for this. So now I would love
to find out more about SQL server. Apologies in advance for any silly
questions...

- Am I right to think that SQL server would store the tables and queries,
and I could then create an access application with the forms and reports etc
to install on my clients machines? How exactly would those applications
communicate with my server? Is an internet connection enough?

- Which version of SQL server would I need to support possibly a couple of
hundred separate clients accessing their own separate databases and tables on
SQL server. I'm thinking the 2005 express edition is a good place to start,
but I don't know how far that would go?

- What file extension should I be using for the Access end of the
applications? I can't work out whether .accdb or .adp would be better. I
would ideally like it to appear just like an Access database on the clients
machines, but I don't know if that's a possibility?

- I'm basically enlarging and upsizing an existing database structure, but I
will need to invest in some technology. Is a high-powered PC enough to put
SQL server on, or am I looking at something much more extensive and complex?

Many thanks again for your help. I'm really impressed with how much
information there is available on this kind of thing, but I just want to
check that I've got the basics sorted first!

 

[Paul Shapiro]

Yes, SQL server would store the data, indexes, stored procedures, queries
and functions. The front-end, which could be Access, a website, etc., would
have the forms and reporting. Although SQL Server Reporting Services are a
very capable report engine too.

Yes, user workstations can communicate with SQL Server over the Internet. If
you have a web frontend, that's obviously what's happening. But it works
with an Access frontend too. In either case the SQL Server probably wants to
run on its own computer, as much for security as for performance. There
should be a firewall protecting the server, and just allowing the necessary
connections on TCP port 1433. Even better if you can limit the external IP
addresses allowed to connect.

You would want to read about client-server development approaches. For an
Access application on a LAN, a form still works fine with a record source
like Select * From Person, with 50,000 or more rows in the table. For WAN
users, you want to instead retrieve the minimum necessary data, usually a
single row after they've selected from a dropdown or no more than a few
hundred rows after they entered some search criteria. You want to minimize
the round-trips between your workstations and your server, because each
round trip incurs delay. One round trip is no big deal, but 100 could be
very slow.

For a few hundred users, a desktop pc today could be powerful enough. But
the data and productivity loss in the event of machine failure probably
warrants a real server. They are built to be more robust- 24 hour/day
operation, higher-performing disk systems with redundancy, more cooling fans
(and noise!) and more redundant parts.

You can develop with SQL Server Express edition, and then upgrade to a
higher version if necessary. You don't lose anything you've done. The higher
editions are 100% compatible with the lower editions. They just add
additional features and reduce or eliminate the artificial limitations on
number of CPU's, ram and db size in the lower editions.

I would suggest you get some books and start reading while you're deciding
how to proceed. While it's all certainly quite doable, it will go better the
more you learn about these issues.

 

[edson batista miranda]

I am considering starting a business offering a cataloguing and library
management system to potentially hundreds of clients. Individual clients
will
ideally have their own custom-made databases, although all of them will
have
essentially the same features, altered slightly from case to case.

I would like to have a main table of data (common to all clients) which
only
I can add to and edit, and then give clients the ability to copy any
number
of records from this table into their database, also adding in other
information to related tables (which will differ from client to client). I
will need to give clients access to forms and reports, but also ideally
have
the ability to monitor all of their data input remotely for the purcposes
of
checking and consistency.

Would this be possible using SQL Server - having my main table on SQL
server, and then giving an .accdb file to my clients, linked to my main
table? Or will all the information need to be stored on my server in order
for me to be able to edit it? Alternatively, would Sharepoint better suit
my
needs.

Any guidance greatly appreciated.

 

[Albert D. Kallal]

- Am I right to think that SQL server would store the tables and queries,
and I could then create an access application with the forms and reports
etc
to install on my clients machines? How exactly would those applications
communicate with my server? Is an internet connection enough?

An internet connection is enough, but you want to read the following article
of mind of using access over a wide area network:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Also keep in mind it's quite easy to set up the connection. The problem here
is do you have the expertise to set a proper security? In fact it was a
couple of months ago I was setting up SQL server and I opened it up to the
Internet to do some testing for just a few hours. Within less than an hour I
was receiving logon requests and attempts from people on the internet to get
into that server. If you ever seen the old move "war games" there's a scene
where the guy has an auto dialer on a modem to look for unsecured computers.
Today there is internet "bots" that look over hundreds of thousands of IP
addresses in a fairly short time and they are looking for open unsecured
versions of SQL server. Once they find one, then and other bot takes over
and starts to entering hundreds of thousands of logon attempts of the most
common types of passwords and logon names used. At this point they then can
take the data from the SQL server, or often worse even get some basic
controls the actual server itself. So I can only say if you don't know what
the heck you doing in terms of networking securty you better hire and get
somebody else to set this up for you. This is not a do it yourself projects
unless you have some good training in this area.

Again I will restate this: Setting up the data connection is really easy and
anybody can do it in a matter of minutes. Setting up this security correctly
is a whole and other matter an issue.

- Which version of SQL server would I need to support possibly a couple of
hundred separate clients accessing their own separate databases and tables
on
SQL server. I'm thinking the 2005 express edition is a good place to
start,
but I don't know how far that would go?

How many users your system will support is an issue of how much data and how
much complex the processing is involved for each user. I don't see the
problem of hanging 50 users off of sql 2005. After that you could monitor it
and always upgrade to the traditional higher version that has more users and
processing capabilities.

- What file extension should I be using for the Access end of the
applications? I can't work out whether .accdb or .adp would be better. I
would ideally like it to appear just like an Access database on the
clients
machines, but I don't know if that's a possibility?

I only suggests using an ADP project if you're rewriting a new application
from scratch OR you're just converting an application has very little record
set code that's based on DAO. When you migrate to an ADP project a lot of
your code can break, so I can only recommend this as a good choice if you're
starting a whole application from scratch (or as metioned there not a lot of
DAO code in your existing application).

There are some performance advantages to an ADP. Another significant
advantage of the ADP projects is that it takes less skills to get better
performance over a network. Since you're using a network which has 100 times
less performance than your typical office network, then any amounts of
improvement in performance is going to be very important for the success of
this project. As a general rule I would avoid ADP project, but if you have
limited skills to SQL server then your performance will likely be better
with less effort by choosign an ADP project.

- I'm basically enlarging and upsizing an existing database structure, but
I
will need to invest in some technology. Is a high-powered PC enough to put
SQL server on, or am I looking at something much more extensive and
complex?

For starters a standard pc is enougth. Your biggest challenge will be to
reduce the applications bandwidth requirements and ensure you have the
security of that system set up correctly.

You could also consider the cloud edition of SQL server and is a really
interesting video of using access + sql server in the cloud with what's
called huron here:

http://channel9.msdn.com/pdc2008/BB40/

 

[Nurse Nancy]

I am completely confused by this.

I am in the process of creating an access db using 2007 for a small company
of approx 10 users.

There are 2 offices 1 in LA and 1 in NY.
I have them on a Sharepoint Hosted Site

I was planning on just putting the Completed DB on Sharepoint and
maintaining a backup copy and a development Copy.

But now i am hearing that I should be exporting all of the tables / queries
and reports to Sharepoint Lists, or I should be splitting the DB into a
Front End and a Back End.
and keep the Back End on Sharepoint Server, but give out local copies of the
Front End.

Why would i need to export it all to sharepoint lists?
Please HELP ME

 

[John W. Vinson]

I am completely confused by this.

I am in the process of creating an access db using 2007 for a small company
of approx 10 users.

There are 2 offices 1 in LA and 1 in NY.
I have them on a Sharepoint Hosted Site

I was planning on just putting the Completed DB on Sharepoint and
maintaining a backup copy and a development Copy.

But now i am hearing that I should be exporting all of the tables / queries
and reports to Sharepoint Lists, or I should be splitting the DB into a
Front End and a Back End.
and keep the Back End on Sharepoint Server, but give out local copies of the
Front End.

Why would i need to export it all to sharepoint lists?
Please HELP ME

If the users in LA and in NY need concurrent access to the same data, you
CANNOT use just a native Access database. Access doesn't "play nice" over a
Wide Area Network or the internet. The data itself would need to be in some
shared data storage medium, either in New York or in Los Angeles - and the
other office would need to connect to it.

If neither office needs any information from the other office you could just
give each office a copy. But a .mdb or .accdb file can't be executed as an
item on a Sharepoint list, and you can't connect from a .mdb/.mde/.accde
frontend to a remote backend.

What you CAN do is store the data on a Sharepoint List, and use Access to
connect to that data. This has disadvantages - you can't enforce Referential
Integrity on sharepoint lists as you can in a table, and would need to use VBA
code to ensure that you're not adding garbage data to the tables (e.g. putting
in sales to nonexistant customers of nonexistant products).

 

[David W. Fenton]

If the users in LA and in NY need concurrent access to the same
data, you CANNOT use just a native Access database.

Sure you can, as long as you host the app on a Windows Terminal
Server in LA or NY.

 

[Nurse Nancy]

thanks, i am still a bit confused.
NY and LA do need to share information.

My main tables are customer approx 2000 records
and Stations approx 6000 records. Is this manageable in sharepoint lists?
How would all of the queries forms, reports that are in the DB work if i
exported the tables to sp lists?

Going back to the question of splitting the DB,, i thought you could have a
front end on local machines connecting to backend on a server?

 

[John W. Vinson]

thanks, i am still a bit confused.
NY and LA do need to share information.

My main tables are customer approx 2000 records
and Stations approx 6000 records. Is this manageable in sharepoint lists?

Easily. That's a tiny list.

How would all of the queries forms, reports that are in the DB work if i
exported the tables to sp lists?

The sharepoint list IS the table. It works very much the same as a local table
- you can use it as the basis of a query, a form, or a report.

Going back to the question of splitting the DB,, i thought you could have a
front end on local machines connecting to backend on a server?

You can... *if the server is on the same fast, stable local area network*. A
LAN is typically 100MByte/sec or better (often much better). A good wide-area
network, on a good day with light traffic, might be 1% of that. Access simply
*will not work* if your Access backend is in New York and the frontend in LA.

Actually it's worse: it can be done, and simple tests might trick you into
thinking that it's usable. But it will be too slow, too unreliable, and much
too prone to corruption and destruction of your database.

Another option you might want to consider is having the database (front and
backends) in the "main office" and having the remote office use Windows
Terminal Server, Citrix Server, or some other remote terminal connection to
log on to the home computer. This just transmits screen images from NY to LA,
rather than having the database contents vulnerable.

 

[David W. Fenton]

Another option you might want to consider is having the database
(front and backends) in the "main office" and having the remote
office use Windows Terminal Server, Citrix Server, or some other
remote terminal connection to log on to the home computer. This
just transmits screen images from NY to LA, rather than having the
database contents vulnerable.

I would like to voice my vote for this as being by far the easiest
solution, assuming the server infrastructure is already in place and
all that would be necessary would be acquiring the WTS CALs and
adding some RAM to the machine promoted to serve as a Windows
Terminal Server.

 

[Nurse Nancy]

I have built a sharepoint site for them via APTIX hosting. Right now the
Access DB is in one piece and resides in a document library in sharepoint.
Only 2 people are using it and they area able to check it out and enter their
data and run queries, reports by taking turns.

I thought Sharepoint could be used to share the DB,, and allow multiple
people to use, update the DB at the same time via active directory but it
doesn't look like that is allowable.

Soon the other 9 people will need acess. I think sharepoint is the best
option for them since they are already paying for it.

When i export the files to lists, can the queries and reports remain in the
access db? Would i give each person a copy of the DB to run locally and link
to the tables in Sharepoint?

 

[John W. Vinson]

I have built a sharepoint site for them via APTIX hosting. Right now the
Access DB is in one piece and resides in a document library in sharepoint.
Only 2 people are using it and they area able to check it out and enter their
data and run queries, reports by taking turns.

I thought Sharepoint could be used to share the DB,, and allow multiple
people to use, update the DB at the same time via active directory but it
doesn't look like that is allowable.

Soon the other 9 people will need acess. I think sharepoint is the best
option for them since they are already paying for it.

When i export the files to lists, can the queries and reports remain in the
access db? Would i give each person a copy of the DB to run locally and link
to the tables in Sharepoint?

I don't have A2007 available at the moment, but my understanding is that you
can use File... Get External Data... Link to link *to a Sharpoint list* as a
table. Storing the database file in a document library is a different
operation; what we're proposing is that each user would have their own
separate copy of an Access database (the "frontend") with links to the
Sharepoint list on a central server. The list would contain the data, and
users could read or update it from their own frontend.

 

[David W. Fenton]

I think sharepoint is the best
option for them since they are already paying for it.

But if it can't do what you want and requires re-engineering your
Access app, what good is it to save the money?