Fallback Considerations

Consider the following factors when you are setting up SQL Server fallback support:

The sp_fallback_ procedures should not be executed within a user transaction. Avoid initiating CREATE/ALTER DATABASE or DISK INIT statement activity while running sp_fallback_ procedures.
The sp_fallback_ procedures always return the value 0 to indicate success and a value greater than 0 to indicate failure. In almost all cases, a value greater than 0 will have been taken from @@ERROR.
Only enroll databases that do not share a device file with any other databases.
Issuing database commands against an enrolled database is not recommended. These commands include dropping, altering, creating, and renaming a database. Before executing a CREATE DATABASE statement in a self-enrolled server, make sure the fallback tables have no entries for the server.
Execute permission on sp_fallback_help is granted to the public group at installation. All other sp_fallback_ procedures can be executed only by the SA.
If you want to provide fallback support for replicating servers, the publisher and the distributor should reside on the same server.
ODBC and DB-Library clients can be set up to automatically switch to the fallback server under certain conditions. This works reliably when the primary server has exactly one fallback server defined.
A database switched to a fallback server includes the database user information in sysusers. However, the mapping between database users and SQL Server login IDs is not maintained when the database is enrolled and/or activated on the fallback server. This problem can be handled by maintaining identical login IDs (identical name and suid columns in the syslogins table) in both SQL Servers. Add the same login IDs in the same order in both SQL Servers.
Alternately, the system stored procedure sp_change_users_login could be used to fix the mapping between sysusers and syslogins. This, however, requires another fix after the database has been switched back to the primary server.