May 31, 2002 at 9:38 am
What is the best possible way to version data. i need to have different versions of data which will be accessed by a set of sprocs. the different versions of the db will be on the same server.
May 31, 2002 at 10:48 am
Can you describe what you are doing a bit better. There are many ways. including createing a field with a version number that you can alter to a new number as the version needs to increase but may not fit you needs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 31, 2002 at 12:11 pm
I agree with what Antares suggests - add a versionid col to the relevant tables. Im currently working on a solution that uses version across multiple tables, all using that method. I have one table that lists all the versions and one final table that indicates which is the "deployed" version.
Andy
May 31, 2002 at 6:12 pm
June 3, 2002 at 7:43 am
How many different versions of data are you talking about. Have you thought about using a partitioned table(view)? Sounds like that may provide a possible solution for you.
Tom
June 3, 2002 at 8:30 am
At any given point of time I am looking at 2 versions of data. Moreover i can't have the data in the same database since data / sprocs in the 2 versions can differ. So I am not sure adding a VersionID field would work for me.
June 3, 2002 at 9:45 am
Then can you give an overview of what you need to accomplish as we need to understand the points of your situation?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 11:53 am
i have a set of SPROCS and VIEWS. the sprocs and views currently access data in db1 (say ver1.0) from db2. The data in db1 is not version specific. I need to in a way have a copy of data in db3 (say ver1.5). When the user logs in the app will send the request to the specific sproc which is version specific and this request will be forwarded to a version specific DB. I have it until the version specific Sprocs are executed but i need a way to access data in a version specific DB.
June 3, 2002 at 6:16 pm
Here is what I believe will work. Depending on how your client works create a versions database that the client can connect to, check for the version it wants, returning the associated DB name and set the default database in the connection to that database. Then you should not have to do anything else as long as the client know what to expect from the DB, if not you could also program this into the version DB. Have it return these settings so the client understand format and associations needed to perform task (this is a logic issue which you have to build for your app). However ytou may not even need to go that far.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 4, 2002 at 2:57 pm
Currently I am using SQLXML connections to the Database. I do not have a ConnectionString but I connect using Virtual Directory Support in SQL Server. I have completed versioning as in, i can reach version specific sprocs the client has access to, but data versioning would require me to change some 300 odd sprocs and 50 odd views. This would be a problem since if i were to name the DB - DB1 and DB1.5 when we move to DB2 i would have to change references in all the sprocs and views to point to a different DB. What would probably work for me is that if it would be possible to pass at run-time the DB name which I need to access. Any suggestions???
June 10, 2002 at 5:50 am
Versioning can be quite a pain, unless a good design approach is identified. My suggestion, is if the need is to keep different versions of "DATA", then using triggers on one or all of the tables can actually save you allot of time. Triggers can pre-check for the existance of key related records, if it exist, it can automatically increment the version number of the record.
June 10, 2002 at 11:15 pm
I agree with codedoc, versioning can make a simple data model very complex, especialy for end-users quering it (well, its not that bad with some views but someone has to write them!).
Also consider the FK's relationships, the versioning should be able to flow from the keys without any issues of "lost" records etc, for example, if you update an "organisation" table record, do you flow the versioning through to the "organisation name" table, how do you query back historically through relationships??
Something to consider.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply