Architecture/Design to maintain multiple version of application database

Question

Architecture/Design to maintain multiple version of application database

saikat.pan

SSC Journeyman

Points: 93
More actions
June 5, 2014 at 10:16 pm

#289520

We have a Silverlight based application which currently supports only one production version. Idea is to support three concurrent versions of the same application and user will switch to the newer versions based on their interest or they can still continue with the older version.
We still have to use the existing database for all these three versions.
What is the best way to architect this so that we can differentiate the code between the versions and still keep the data in sync and run all the versions in parallel.

Viewing 8 posts - 1 through 8 (of 8 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

saikat.pan (6/5/2014)
We have a Silverlight based application which currently supports only one production version. Idea is to support three concurrent versions of the same application and user will switch to the newer versions based on their interest or they can still continue with the older version.
We still have to use the existing database for all these three versions.
What is the best way to architect this so that we can differentiate the code between the versions and still keep the data in sync and run all the versions in parallel.

What you seek to do is hard, VERY VERY HARD!! No matter how you slice it it is going to be a nightmare, especially if the data storage requirements change between versions. I do NOT envy you your task, although I would actually love to work the project because it is truly interesting - and I don't get that much any more!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

saikat.pan SSC Journeyman Points: 93 More actions · Answer 2

Agreed, Hard but interesting at the same time!! I was trying to put my thoughts into this and this is what I can think of:

Assumption : 3 versions are named as 1.0,1.1,1.2

Create separate database schema for 1.0 and move all the db objects(tables, SP, views etc.) under this schema.

When the next version(1.1) comes in, create a separate schema for 1.1 and add synonym for the tables in 1.1 schema for 1.0 schema. Create a copy of other db objects from 1.0 to 1.1.

Same works when 1.2 kicks in.

The web service that invokes SPs will call SPs of the respective schema.

I can see there's lot of overhead as far as maintainability is concerned, but cannot think of other alternatives.

Any thoughts!!

free_mascot One Orange Chip Points: 27164 More actions · Answer 3

What if once user has version 1.1/1.2 and switch back to 1.0 or user use their login on old version 1.0 after he used higher verison? How data consistency maintained?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

saikat.pan SSC Journeyman Points: 93 More actions · Answer 4

Good point and there are lot of such scenarios which we need to address.

For this, as I said, 1.0 version of UI will be tied to 1.0 service and hence to 1.0 schema. Same applies for other versions as well.

Now if someone moves between versions, he will also move between schemas (pointing to the same table). If additional columns have been added in later versions and he tries to use older version, there will be data loss in UI which he has to bear with. Same applies when he wants to update the same data.

Do you think versioning the row level data will help here anyway.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

There's no easy way to do this, like Kevin says. One thing you can try is to use either views or in-line functions to expose a common object name to the code so that it doesn't have to be changed to deal with changes to the underlying structures. You probably should go with schemas as a mechanism for separating the objects, but you don't have to with this approach.

I don't think you'll be able to version by row since that doesn't take into account the changes in structure. If a column drops or a non-default column gets added, the code must take that into account.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

saikat.pan SSC Journeyman Points: 93 More actions · Answer 6

Thanks for your reply. Can you please explain a bit about this "One thing you can try is to use either views or in-line functions to expose a common object name to the code so that it doesn't have to be changed to deal with changes to the underlying structures."

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 7

Basically you can put IF logic within an in-line function to determine which table to query. That way the code refers to one thing, but the function breaks the output out to the appropriate table. It's not going to be easy to deal with, but it's the one way I could think of.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning