June 8, 2018 at 7:58 am
Hi Guys,
I have to build a model where i am tracking DDL and DML changes and have possibility to go to previous version.
Goal to achive:
1. Customer uses some version
2. User knows what it is
3. User makes changes and he knows what he is changing.
4. User releases new version and knows what it is.
General goal: have different versions/states of tables and have possibility to go back to previous version and to not deleting data (preserving existing ones).
I was thinking to create:
1. snapshosts for each version
2. Restore snapshots if necessarry with ddl and dml changes
3. track all changes using event triggers
Problem is that only one version of snapshot can be used in the same time.
Tables do not have the same problem, by multiple copies of tables at the same time you can consume number of versions, it is matter of querying,
I am using postgresql database but most of functions in ms sql server are similar.
Please help, maybe some of you had similar problem or have the idea how to solve it?
Best,
Jacek
June 9, 2018 at 1:58 pm
Anyone?
Jacek
June 11, 2018 at 7:52 am
Version control software would handle this much better. High-volume transactional tables will be problematic for you here.
For slowly changing data tables, you could maybe use audit tables, where any INSERT/UPDATE/DELETE gets logged to a mirrored table that also captured the time of change and type of change. Then you could set triggers to capture that info and log into the audit table (triggers are maybe not the best option here, research alternatives and make sure you make the best choice for you).
You'd also have to think about "previous version" and how it would impact things if you rolled Table A back but nothing else, and how you could be impacting the way data is tied together throughout the system inadvertently (in other words, are you going to analyze when data was updated in all tables if you roll TableA back to point in time, and roll all back to that point in time? Or just tell the user to deal with it?). This might have an impact on data that isn't a foreign key relationship either, and would just end up being orphaned in other tables accidentally.
Also doesn't handle truncate/drop of tables, I've seen a couple suggestions for that.
http://jackworthen.com/2018/03/19/creating-a-log-table-to-track-changes-to-database-objects-in-sql-server/
https://www.mssqltips.com/sqlservertip/2583/grant-truncate-table-permissions-in-sql-server-without-alter-table/
I'm restricting rights to our database through security groups, then also adding unittests for whether objects exist, so that if one fails I know to go fix it.
hope those thoughts help.
Jon
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
June 12, 2018 at 1:23 am
Hi Jon,
thank you for your reply.
I have idea how to track all changes.
I want to create temporary database and create there copies of tables as version.
And if user wants to go back to previous version he can go and from dump file restore databse into temporary and see tables for exact version.
And working on new version using GIT solution for that.
Best,
Jacek
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply