SQL Server and ACID property of Database

IMHO, it is a two fold maintainance. Both DB Admins (writing stored procedures ) and programmers should enforce ACID Properties. SQL Server maintains its own ACID properties internally and we don't have to worry about that.

ACID Properties are enforced in SQL Server.

Read this: Acid Properties of SQL 2005

But that doesn't mean that that the Database would handle everything for you.

According to Pinal Dave (blog.sqlauthority.com)

ACID (an acronymn for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

Above four rules are very important for any developers dealing with databases.

That goes for developers dealing with databases.

But application developers should also write business logic on which ACID properties are being enforced.

An example on Practical use of ACID properties would help you more I guess

Almost every modern database systems enforce ACID properties.Read this : Database transaction and ACID properties

ACID --> Atomicity, Consistency, Isolation, Durability

Atomicity:
A transaction is the fundamental unit of processing. Either all of its operations are executed, or none of them are.Suppose that the system crashes after the Write(A) operation (but before write(B).)
Database must be able to recover old values of A and B (or complete entire transaction)

Consistency Preserving:
Executing a transaction alone must move the database from one consistent state to another consistent state. Sum of A and B must be unchanged by the execution of the transaction

Isolation:
A transaction should not make its effects known to other transactions until after it commits.If two transactions execute concurrently, it must appear that one completed execution before the other started.If another transaction executing at the same time is reading (and/or writing to) accounts A and B, it should not be able to read the data in an inconsistent state (after write to A and before write to B)

Durability:
Once a transaction commits, the changes to the database can not be lost due to a future failure.Once transaction completes, we will always have new values of A and B in the database