January 13, 2011 at 7:34 am
I have also posted this in the Strategy forum but then found this section which might be better...
Hopefully I can explain this correctly....
We are currently contemplating applying some logic adopted from our legacy application written in Pascal using B-Tree filer system for it's data store. We are developing in .NET using SQL Server.
Basically, the way this is implemented is to write a transaction (to a generic file) that then "triggers" an action on the live file corresponding to the record type and transaction type. To explain further I will try to give an example:
Let's say we have a Customer File/Table....
I want to update a Customer, to do this it must follow this logic:
1. Insert record in to transaction file/table (Record Type = "Customer", Transaction Type = "Update")
2. This then calculates that the change must be made to the live Customer file/table.
I want to then insert a Customer, to do this it must follow this logic:
3. Insert record in to transaction file/table (Record Type = "Customer", Transaction Type = "Insert")
4. This then calculates that the insert must be made to the live Customer file/table.
Imagine this same logic applied to every file/table (could be hundreds).
The advantage that this gives in the Pascal/B-Tree system is that if the Customer file gets blown away it can easily be reconstructed from the Transaction records (we call this "Replayability". It also gives a full history of what has happened on the database.
At this stage I am not quite sure how this would be implemented, but what I am really looking for is if someone else has used this same strategy in their systems?
If not, does anyone have any thoughts on if this is actually needed in a system using SQL Server?
January 13, 2011 at 8:35 am
craigbroadman (1/13/2011)
If not, does anyone have any thoughts on if this is actually needed in a system using SQL Server?
I would say no, you are trying to mimick the Atomic properties that are already built into sql server which seems like a waste of resources
The advantage that this gives in the Pascal/B-Tree system is that if the Customer file gets blown away it can easily be reconstructed from the Transaction records
why do you think your customer file (table?) would get blown away , sql server does not randomly delete tables.
And even if for some reason you did accidently manually delete a table you would be better off relying on backup/restore strategies to recover your data.
January 13, 2011 at 9:13 am
Since you are handling
Imagine this same logic applied to every file/table (could be hundreds).
I would recommend hiring a SQL SERVER Database Administrator (DBA) to join your group to assist in this effort, or a consultant to assist as well as train your own staff. That said,
1. Become familiar with the MERGE statement available within sql server to handle the decision as to wether the data being supplied must be added to a table or utilized to update an existing table entry.
2. Become familiar with the different backup techniques, including those which allow for a database recovery to a given point in time, thus potentially allowing for the "replacement" of what you have termed
(we call this "Replayability".)
3. Their are also many techniques to retain data so that you can look at the history of additions/updates to tables.
It also
gives a full history of what has happened on the database
You will have to be intimately familiar with many aspects of SQL Server and its vast array of capabilities, and so may I reinforce my suggestion to hire a DBA or consultant to join in assisting in the changes contemplated as well as to train existing staff in basic T-SQL and backup/restore aspects of good database maintaince.
January 13, 2011 at 9:40 am
craigbroadman (1/13/2011)
I have also posted this in the Strategy forum but then found this section which might be better...Hopefully I can explain this correctly....
We are currently contemplating applying some logic adopted from our legacy application written in Pascal using B-Tree filer system for it's data store. We are developing in .NET using SQL Server.
Basically, the way this is implemented is to write a transaction (to a generic file) that then "triggers" an action on the live file corresponding to the record type and transaction type. To explain further I will try to give an example:
Let's say we have a Customer File/Table....
I want to update a Customer, to do this it must follow this logic:
1. Insert record in to transaction file/table (Record Type = "Customer", Transaction Type = "Update")
2. This then calculates that the change must be made to the live Customer file/table.
I want to then insert a Customer, to do this it must follow this logic:
3. Insert record in to transaction file/table (Record Type = "Customer", Transaction Type = "Insert")
4. This then calculates that the insert must be made to the live Customer file/table.
Imagine this same logic applied to every file/table (could be hundreds).
The advantage that this gives in the Pascal/B-Tree system is that if the Customer file gets blown away it can easily be reconstructed from the Transaction records (we call this "Replayability". It also gives a full history of what has happened on the database.
At this stage I am not quite sure how this would be implemented, but what I am really looking for is if someone else has used this same strategy in their systems?
If not, does anyone have any thoughts on if this is actually needed in a system using SQL Server?
Personally I do not think adopting this model is a good idea, but I am happy to hear off people to get other opinions. If there are valid reasons for adopting this concept then I am happy to change my mind, if not then I need to be able to put a good case forward opposing it.
I completely agree that this is replacing the properties of what SQL Server does for you. I can completely understand why it is important to Audit data but as for "replayability" from transaction records I am not so sure....
One point that I did forget to mention is one of replication, for example, if I need to import data from someone elses system I can simply download their transaction records and "replay" them to reconstruct their data. However I am sure there are better approaches using SSIS or something similar.
I think we are setting up a development team forum next week to talk over/debate this concept and I would like to be fully prepared. I am grateful to hear anyones opinion on this subject...
January 13, 2011 at 9:51 am
in no way can i see this a good idea, and i can not imagine any developer arguing for it..
If by replayability you mean the ability to recover from a disaster then SQL server can do this for you..
if you need to a record of transactions then you can do this in a number of ways in sql server.
January 13, 2011 at 3:08 pm
steveb. (1/13/2011)
in no way can i see this a good idea, and i can not imagine any developer arguing for it..If by replayability you mean the ability to recover from a disaster then SQL server can do this for you..
if you need to a record of transactions then you can do this in a number of ways in sql server.
Anyone else think the same?
January 16, 2011 at 9:47 am
craigbroadman (1/13/2011)
steveb. (1/13/2011)
in no way can i see this a good idea, and i can not imagine any developer arguing for it..If by replayability you mean the ability to recover from a disaster then SQL server can do this for you..
if you need to a record of transactions then you can do this in a number of ways in sql server.
Anyone else think the same?
I don't know Pascal and I don’t understand what you are saying about your application but transaction in the Microsoft platform is not complicated. In the application layer you use System.Transaction which gives you loosely coupled transactions that requires a resource manager. Oracle 10g R2 and up, SQL Server 2005 and up, ADO.NET and EntityFramework are some of the resource managers you can enlist to convert System.Transaction operation to an atomic unit of work transaction. It is better to use SQL Server 2008 because ANSI SQL defined transaction nesting at the point of connection which Microsoft implemented in SQL Server 2008 and up, which means if you are running complex operations in the application layer with System.Transaction you can avoid some known nesting related issues if you use VS2008-2010 and SQL Server 2008 and SQL Server 2008 R2. If you cannot design your business objects you can use Enterprise Library to take care of your data access to both SQL Server and Oracle. The only requirement when last I used Enterprise Library you must install it in your C drive. I can answer most questions about transactions in both layers in the Microsoft platform, when in doubt use Enterprise Library.
I am assuming you know transactions in RDBMS is mathematical unit of work that can be quantified, if you don’t know that then you must use Enterprise Library so Microsoft will take care of converting none atomic System.Transaction operations to atomic transactions in SQL Server.
Kind regards,
Gift Peddie
January 16, 2011 at 5:31 pm
Gift Peddie (1/16/2011)
I don't know Pascal and I don’t understand what you are saying about your application but transaction in the Microsoft platform is not complicated. In the application layer you use System.Transaction which gives you loosely coupled transactions that requires a resource manager. Oracle 10g R2 and up, SQL Server 2005 and up, ADO.NET and EntityFramework are some of the resource managers you can enlist to convert System.Transaction operation to an atomic unit of work transaction. It is better to use SQL Server 2008 because ANSI SQL defined transaction nesting at the point of connection which Microsoft implemented in SQL Server 2008 and up, which means if you are running complex operations in the application layer with System.Transaction you can avoid some known nesting related issues if you use VS2008-2010 and SQL Server 2008 and SQL Server 2008 R2. If you cannot design your business objects you can use Enterprise Library to take care of your data access to both SQL Server and Oracle. The only requirement when last I used Enterprise Library you must install it in your C drive. I can answer most questions about transactions in both layers in the Microsoft platform, when in doubt use Enterprise Library.
I am assuming you know transactions in RDBMS is mathematical unit of work that can be quantified, if you don’t know that then you must use Enterprise Library so Microsoft will take care of converting none atomic System.Transaction operations to atomic transactions in SQL Server.
I'm sorry, Gift, but your response has left me totally confused.
January 16, 2011 at 5:34 pm
craigbroadman (1/13/2011)
steveb. (1/13/2011)
in no way can i see this a good idea, and i can not imagine any developer arguing for it..If by replayability you mean the ability to recover from a disaster then SQL server can do this for you..
if you need to a record of transactions then you can do this in a number of ways in sql server.
Anyone else think the same?
Sounds to me that you are converting from using b-tree files to using SQL Server for storing your data. I agree, hire a DBA or consultant to work with your group. You will need to change how your application interacts with the data. Let SQL Server do what it does best, handle the data and keep it secure.
January 16, 2011 at 5:44 pm
Lynn Pettis (1/16/2011)
Gift Peddie (1/16/2011)
I don't know Pascal and I don’t understand what you are saying about your application but transaction in the Microsoft platform is not complicated. In the application layer you use System.Transaction which gives you loosely coupled transactions that requires a resource manager. Oracle 10g R2 and up, SQL Server 2005 and up, ADO.NET and EntityFramework are some of the resource managers you can enlist to convert System.Transaction operation to an atomic unit of work transaction. It is better to use SQL Server 2008 because ANSI SQL defined transaction nesting at the point of connection which Microsoft implemented in SQL Server 2008 and up, which means if you are running complex operations in the application layer with System.Transaction you can avoid some known nesting related issues if you use VS2008-2010 and SQL Server 2008 and SQL Server 2008 R2. If you cannot design your business objects you can use Enterprise Library to take care of your data access to both SQL Server and Oracle. The only requirement when last I used Enterprise Library you must install it in your C drive. I can answer most questions about transactions in both layers in the Microsoft platform, when in doubt use Enterprise Library.
I am assuming you know transactions in RDBMS is mathematical unit of work that can be quantified, if you don’t know that then you must use Enterprise Library so Microsoft will take care of converting none atomic System.Transaction operations to atomic transactions in SQL Server.
I'm sorry, Gift, but your response has left me totally confused.
What part of my answer are you confused about?
Kind regards,
Gift Peddie
January 16, 2011 at 5:50 pm
Gift Peddie (1/16/2011)
Lynn Pettis (1/16/2011)
Gift Peddie (1/16/2011)
I don't know Pascal and I don’t understand what you are saying about your application but transaction in the Microsoft platform is not complicated. In the application layer you use System.Transaction which gives you loosely coupled transactions that requires a resource manager. Oracle 10g R2 and up, SQL Server 2005 and up, ADO.NET and EntityFramework are some of the resource managers you can enlist to convert System.Transaction operation to an atomic unit of work transaction. It is better to use SQL Server 2008 because ANSI SQL defined transaction nesting at the point of connection which Microsoft implemented in SQL Server 2008 and up, which means if you are running complex operations in the application layer with System.Transaction you can avoid some known nesting related issues if you use VS2008-2010 and SQL Server 2008 and SQL Server 2008 R2. If you cannot design your business objects you can use Enterprise Library to take care of your data access to both SQL Server and Oracle. The only requirement when last I used Enterprise Library you must install it in your C drive. I can answer most questions about transactions in both layers in the Microsoft platform, when in doubt use Enterprise Library.
I am assuming you know transactions in RDBMS is mathematical unit of work that can be quantified, if you don’t know that then you must use Enterprise Library so Microsoft will take care of converting none atomic System.Transaction operations to atomic transactions in SQL Server.
I'm sorry, Gift, but your response has left me totally confused.
What part of my answer are you confused about?
The whole thing. Nothing in it really makes any sense to me. And to be honest, I'm not really asking for clarification either.
January 16, 2011 at 5:59 pm
Lynn Pettis (1/16/2011)
Gift Peddie (1/16/2011)
Lynn Pettis (1/16/2011)
Gift Peddie (1/16/2011)
I don't know Pascal and I don’t understand what you are saying about your application but transaction in the Microsoft platform is not complicated. In the application layer you use System.Transaction which gives you loosely coupled transactions that requires a resource manager. Oracle 10g R2 and up, SQL Server 2005 and up, ADO.NET and EntityFramework are some of the resource managers you can enlist to convert System.Transaction operation to an atomic unit of work transaction. It is better to use SQL Server 2008 because ANSI SQL defined transaction nesting at the point of connection which Microsoft implemented in SQL Server 2008 and up, which means if you are running complex operations in the application layer with System.Transaction you can avoid some known nesting related issues if you use VS2008-2010 and SQL Server 2008 and SQL Server 2008 R2. If you cannot design your business objects you can use Enterprise Library to take care of your data access to both SQL Server and Oracle. The only requirement when last I used Enterprise Library you must install it in your C drive. I can answer most questions about transactions in both layers in the Microsoft platform, when in doubt use Enterprise Library.
I am assuming you know transactions in RDBMS is mathematical unit of work that can be quantified, if you don’t know that then you must use Enterprise Library so Microsoft will take care of converting none atomic System.Transaction operations to atomic transactions in SQL Server.
I'm sorry, Gift, but your response has left me totally confused.
What part of my answer are you confused about?
The whole thing. Nothing in it really makes any sense to me. And to be honest, I'm not really asking for clarification either.
You told the poster to hire a DBA I have covered what a DBA will not do for the poster because the OP said data will also be coming from other sources. There are at least five different Transactions in the Microsoft platform only one is used in the relational engine which can be enlisted as a resource manager.
Kind regards,
Gift Peddie
January 16, 2011 at 6:04 pm
Gift Peddie (1/16/2011)
First, I wasn't the only one to suggest hiring a DBA or consultant.
Second, the OP also indicated that they could possibly use SSIS to pull data from other systems.
Given the above, and rereading your original post, still confused. Let's leave it at that, okay?
January 16, 2011 at 6:43 pm
Lynn Pettis (1/16/2011)
Gift Peddie (1/16/2011)
First, I wasn't the only one to suggest hiring a DBA or consultant.
Second, the OP also indicated that they could possibly use SSIS to pull data from other systems.
Given the above, and rereading your original post, still confused. Let's leave it at that, okay?
A DBA can design the database but a .NET application also comes with Business Object layer and Data access layer each running a different type of transaction, Enterprise Library is used by many companies as the data access layer of many .NET applications.
Kind regards,
Gift Peddie
January 16, 2011 at 7:12 pm
Gift Peddie (1/16/2011)
Lynn Pettis (1/16/2011)
Gift Peddie (1/16/2011)
First, I wasn't the only one to suggest hiring a DBA or consultant.
Second, the OP also indicated that they could possibly use SSIS to pull data from other systems.
Given the above, and rereading your original post, still confused. Let's leave it at that, okay?
A DBA can design the database but a .NET application also comes with Business Object layer and Data access layer each running a different type of transaction, Enterprise Library is used by many companies as the data access layer of many .NET applications.
What ever you say.
Using nHibernate where I am working now and I think it is a pain in the butt.
Oracle is too, but I am learning the differences and simularities between Oracle and SQL Server.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply