Data Layer testing

Data layer testing has been an issue for me for quite some time. All tests need to be atomic, that is a fundamental rule when it comes to testing and is especially important and difficult to achieve in the database testing. Some things that come to mind are Identity fields. Every time you insert a record, the database gets changed, even if you delete the record manually at the end of the test. Also cleaning up manually a distributed database could be quite hectic, same applies when you need to insert a lot of data to efficiently test your functionality (consider a tree-like structure that is stored in SQL). The best way to solve all of the above mentioned problems and still ensure atomicity is transactions. Here is more on the topic:
COM+ 1.5 Transactions
The article uses Enterprise Services.
One waring though, the tests will randomly fail if your testing code has a transaction that is supposed to fail (Exceptions, boundary cases, faulty input ... etc) When you say Transaction.Rollback() which one is the one that gets rolled back?
I have spent numerous hours trying to figure out randomly failing unit tests.
If you have had code that randomly fails and passes under the same conditions, you know very well this is not a very pleasant situation.

So keeping all that in mind, the article is pretty good.