A Different Hybrid Option

Question

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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Woehrle Mr or Mrs. 500 Points: 539 More actions · Answer 1

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 · Answer 2

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.