One of the foundation concepts that all database workers should keep in the back (or maybe front) of their mind is ACID. This small and very important acronym describes the characteristics of a reliable transaction. wiki
Atomicity – Transactions cannot be partially complete. Either the whole thing commits or nothing commits. This is true in normal operation as well as in cases of failure. The classic example of why this is so important is based on a bank transaction which is moving money from account A to account B. If a failure occurs during this transaction, then we can rest assured that the transaction will be rolled back (returned to the state prior to the transaction being processed).
Consistency – Only valid data will be accepted or written. If a transaction includes data that violates a constraint, for example, then this transaction will be considered invalid and rejected.
Isolation – Each transaction is independent of all others. When multiple transactions are attempting to modify the same data isolation will ensure that they are process independently. This ensures that the state of the data being modified is always equal to the results of the last transaction processed as opposed to a combination of transactions.
Durability – When a transaction is processed it must eventually be saved to disk. There is a chance that a failure could occur between the time that a transaction is processed and when it is written to disk. These changes & their state must be tracked to enable post failure recovery processes to complete the transaction.
SQL Server is compliant with these principles. Most prominently the transaction log and locking (row, page, key, etc..) help ensure that SQL Server reliably processes all transactions. SQL Server Integration Services is fully capable of leveraging these features as well. LeapFrogBI leverages SQL Server Integration Service and SQL Server Database Engine to ensure that all ETL transactions are processed reliably.
