[dba-SQLServer] Upsizing and consolidating Access data into SQL Server

Steve Erbach erbachs at gmail.com
Wed May 16 12:56:12 CDT 2007


Dear Group,

I've been the chief designer for a vertical market application that
was created with Microsoft Access 2003. The application has been
installed in a handful of locations around the country, some
single-user, some multi-user.

The man who markets and owns 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 customer
installation of the product, every customer currently using the Access
product would append his data to a "master" set of tables in one
database 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 customers).

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).  Just for a quick look at the current app:
http://www.swerbach.com/EnviroPlus/ .

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 in Access 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



More information about the dba-SQLServer mailing list