You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment. You are creating a SQL Agent job that uses Transact-SQL to update data in two related databases on two different servers. You have the following requirements:
* The job can only execute once each evening.
* The databases on each server use the full-recovery model.
* Transaction log backups for the two databases occur at different times.
* The job uses transactions to ensure that in the event of an error, all updates are rolled back.
You need to ensure that when you restore a database on either server, the two databases are restored to a state that reflects the last time the job successfully executed. What should you do?
A.
Ensure both databases are altered using the NO_WAIT termination clause.
B.
Use the Windows Sync Manager to ensure that the databases can never be out of synchronization.
C.
Use saved transactions. When a database failure occurs, restore both databases by using a saved transaction.
D.
Use marked transactions. When a database failure occurs, restore both databases by using a marked transaction.
Explanation:
When you make related updates to two or more databases, related databases, you can use transaction marks to recover them to a logically consistent point. However, this recovery loses any transaction that is committed after the mark that was used as the recovery point. Marking transactions is suitable only when you are testing related databases or when you are willing to lose recently committed transactions.