May 11, 2007 at 2:34 pm
Dear Group,
I've been the chief designer for a vertical market application that was created with Microsoft Access. The application has been installed in a handful of locations around the country, some single-user, some multi-user.
The man who markets the application would like to convert it to a centralized web-based application. To keep the cost of maintenance and upgrades to a minimum I have suggested that rather than creating a separate SQL Server database for each installation of the product, every customer currently using the Access product would append his data to a "master" set of tables on one SQL Server.
The upshot would be that each company would have its own ID and the transactions and products specific to each company would be tagged with that ID. (There are certain tables that could be shared in common...certain lists of items common to all installations).
Each company would use Views, etc., that show just its own data. None of this data is proprietary or particularly sensitive (it's hazmat record-keeping).
The SQL Server capability would be rented from one of the commercial web hosts. The volume of data is actually quite small. We're talking maybe 5 MB for a couple years worth of information for each company.
Of course, if the server goes down then everybody goes down. But the positives, I think, would be ease of upgrading, keeping everybody at the same revision level simultaneously, and low cost.
Do you see any flies in the ointment here? I think it's very feasible, but I'd welcome any cautioning voices.
Sincerely,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com
May 16, 2007 at 11:51 am
Looks like this idea is reasonable and workable and all that? Just looking for a caveat or two.
Regards,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com
May 17, 2007 at 9:32 pm
Steve, if you can, I'd go with a separate database for each client, single SQL Server installation. If there are some issues between installations, this should protect you.
If you have central tables, you could easily set them up in a central db and replicate out to the other databases.
If this doesn't work, I might suggest separate owners/schema for the various clients. Again, prevent problems that you might not be aware of.
Other than that, it makes sense to me. If you keep separation, if someone outgrows the shared situation, you can then easily move them to their own server. If you combine them all, this might not be as easy to accomplish.
May 18, 2007 at 1:48 pm
Steve,
I appreciate the input. You are no doubt correct about protecting myself by setting up separate databases. I'm sure it would work; I'm just concerned about simplicity of support.
Again, my idea is to load all of this data onto one of the commercial ISPs using its SQL Server capabilities. For example, I use CrystalTech and they give me two SQL Server logins for the single database I get in the shared hosting plan I've purchased. I've told the owner of the software that this is the least expensive option, with plenty of headroom for moving into limited-share hosting or even a dedicated hosting plan.
CrystalTech tells me that there's a small extra fee each month for additional logins to the SQL Server database. That seemed to me to be workable since each of the users of the web application would have his own SQL login with all security set by login as well as by role.
Could you please explain a bit more about "separate owners/schema for the various clients"?
As far as outgrowing, this is one of those situations where it would take real ingenuity and a couple of orders of magnitude more data entry than is done now for a client to outgrow this setup. 5 MB for a couple of years worth of transactions is what they're all using up now in Microsoft Access mdb's. Maybe 500 products and 3000 to 5000 transactions per year...and that's being generous.
Thank you for taking the time. I hope things are going well with the transition to Red-Gate.
Sincerely,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com
May 21, 2007 at 2:46 am
Schema as defined in bol:
A database schema is a distinct namespace that is separate from a database user. You can think of a schema as a container of objects. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.
This way you can have one large database and use schemas to organise logically.
May 21, 2007 at 8:26 am
Sam has defined it, but in SQL 2000, you had a database, say Sales, with a table, say MyTable. The owner of the table was usually dbo, but it could be Sam. So the table would be Sales.Sam.MyTable. If Bob owned tables, he could also have a Sales.Bob.MyTable in the same database.
This was equivalent to a schema, but it had restrictions. In SQL Server 2005, there truly is a schema, so you can have Sales.Client1.MyTable and Sales.Client2.MyTable in the same database. Then you assign a particular schema to a login, like Client1 login has rights to the Client1 schema . The app stays the same, a "select MyCol from MyTable" issued by Client1 will go to the Client1 schema first. Client2 can have a similar situation and everything is essentially separated.
Hope this makes sense.
May 24, 2007 at 9:54 am
Steve,
» Hope this makes sense. «
Yes, it does! That's actually very exciting! I knew that that sort of ownership existed, but I was under the misapprehension that it was only for temporary tables created by a particular user after, say, a SELECT INTO query. Very interesting. Thanks very much!
Sincerely,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com
May 24, 2007 at 9:55 am
Sam,
Thank you for pointing that out. Your post and Steve's make the possibilities open up for me considerably.
Regards,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply