DB architecture for complex Access app upsizing to SQL Server.

Question

We have an Access application that we're upsizing to SQL Server (UI in Access, DB in SQL Server 2008 or above).  The application manages equipment data for about 15 distinct physical sites.  Each of these sites is subject to being "spun off", i.e., sold to another company.  When the site is sold, the acquiring company will take with them the data relevant to that site, along with a copy of the Access front end.  To facilitate this inevitable future transfer (which could take months to years to come about for any given site.) We want to architect the SQL Server database in such a way that it is relatively straight forward to extract the acquired site's data and deliver it to the new company.  It is also important that the architecture also makes it as easy as possible for the new company to setup a new SQL Server instance and get the application running again.

One detail....  The application includes a three categories (or 'sets') of tables.  Two of the sets (about 12 tables) are unique to a site, i.e., data about specific pieces of equipment at a site.  One of the sets (about 20 tables) is common between all site, i.e., lookup tables with equipment types, categories etc.  When a site is spun off, it will take it's own data (about 12 tables) and a copy of the ~20 common tables.

There seem to be four obvious architectural alternatives:

1) Tag every database record in every table with the site's ID, i.e., add a 'site_id' field to every table.  Then use queries to segment and export the data base on site_id.  Then the new company would import the data into a new SQL Server instance, setup the Access front end table links.  

2) Create a distinct database for each site, all under a single SQL Server instance.  The just export the database and then import it into the new companies SQL Server instance.  We assume we'd implement ALL three sets of tables in each database, including the lookup tables -- it just feels hinky to share data in an app between different databases.

3) Create a distinct schema for each site, all under a single database.  The export/import process would be similar to 2), but we wouldn't create a copy of the lookup tables, i.e., each schema would share the lookup tables from a common 'lookup' schema.  Because all the schemas would be contained in a single database, this feels less hinky.

4) Create a separate SQL Server instance for each site.  We wouldn't do this (it' the hinkiest of all); just calling it out as an possibility.

Our druthers as of now are to implement option 3), distinct schemas.

What would you do?  What do you think we should be thinking about to make this decision?

Thanks!

BC

Answer 1

A- The application manages equipment data for about 15 distinct physical sites.  

1) Tag every database record in every table with the site's ID, i.e., add a 'site_id' field to every table.  Then use queries to segment and export the data base on site_id.  Then the new company would import the data into a new SQL Server instance, setup the Access front end table links.  

What would you do?  What do you think we should be thinking about to make this decision?

A- When you say 'manages equipment data' is that property, maintenance, or utilization?

1- I would have thought you would have 'SiteID' already but maybe your data collection never outputs reports by site.

---

Build a little, test a little

Answer 2

Thanks, Karl.  The data is regarding various inherent characteristics of the equipment (e.g., type, capacity, etc.) as well as the results of various testing and maintenance regimens.  The existing app had the data and the UI segregated in 15 separate Access files, one for each site; so there was no need to include a site id field -- each set of files contained all and only the equipment relevant to that site.  Now that all the data will be up-sized into a single SQL Server instance, the question arises....

Answer 3

Why can you not have '15 sets of tables, forms, queries/views, etc' that include a site identifier in the names? 

Then have a form where you select the 'set?'    I have not worked with SQL Server for a long time but there should be a way to do that.

---

Build a little, test a little

Answer 4

That's actually just the environment that we're moving out of.  It's a change management nightmare to keep 15 sets of everything synced.  Any add/change/delete the comes up needs to be implemented 15 time... exactly the same... every time.  

Answer 5

Hi BC,

In my option, you do not need to create separate sql server instance for each site. And I suggest you create different tables for different sites under the same instance.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.