A Different Hybrid Option

• David Rueter

  SSCrazy

  Points: 2661

  More actions

  April 22, 2014 at 10:34 am

  #286046

  We have used a different kind hybrid of "Option 2- SaaS vendor maintained separate database" and "Option 3 – Single multi-tenant database".

  Ours approach uses single-tenant databases, but with integrated versioning support that allows us to maintain a single code base in a Master database across all individual databases.

  The individual single-tenant databases can each apply updates from the Master database at will (automatically). But in all respects each single-tenant database is autonomous--there is no shared data or functionality needed at run-time that is stored in the Master database.

  This allows us to preserve the benefits of individual databases:

  • More flexibility and control over performance, scalability, disaster recovery, and SaaS vs. on-premise deployment decision
  • Simple backups and restores
  • Low risk of cross-client data leakage

  It also eliminates the first three cons you sited:

  • Has a separate code base for each customer
  • Makes upgrades difficult across customers
  • Hard to maintain each customer due to customizations

  The details of our approach is beyond the scope of this little message...but the approach does allow for customization and a single code base at the same time.

• Sean Woehrle

  Mr or Mrs. 500

  Points: 539

  More actions

  April 22, 2014 at 12:16 pm

  #1707867

  Hey David,

  I too have done projects where we had a master database that we synced the code and structures to the specific databases. We did this manual sync with SQL Compare to find table/view/proc differences from the master.

  I am curious how your "automatic logic" addressed the sync and also retained customer specific code/structures as needed.

  Thanks,

  Sean

  [font="Tahoma"]Cheers,

  Sean :-D[/font]

• David Rueter

  SSCrazy

  Points: 2661

  More actions

  April 22, 2014 at 12:55 pm

  #1707876

  Initially we used SQL Compare, but have now matured to the point where this all happens natively in T-SQL code.

  Some of the elements of our approach include:

  • Use a SQL schema for all system-managed objects
  • Use a different SQL schema for all custom user-specific code
  • Implement user configurablility in a way that tables and related objects can be configured by the user, but managed by the system (by storing application-level metadata needed to rebuild SQL objects, etc.)--in the system-managed schema
  • Use database DDL triggers to calculate and log a version hash of every DDL change
  • We can programatically drop all system-managed objects (except actual tables) as needed (and rebuild later from metadata).
  • The system-wide structure imposes almost no restrictions or requirements on the user-configured tables
  • Updating is free to update anything in the system-managed schema, but does not touch anything in the user-managed schema (custom code)
  • Updating involves comparing the hash of the DDL of each SQL object with the hash of the objects in the Master database, and then retrieving the needed DDL from the master to execute on the single-tenant database required to update

  There are some other aspects to this approach too...but the above give a rough overview. It has been something that we have evolved over the past 6 years--and works very well.