Throwing in PRINT statements is the way to monitor the progress. Do you have SET NOCOUNT ON on the main proc? If so you need to remove it. Other than...

I am not sure if there's an "automatic" way to switch your user between the servers. Perhaps you can set some timeout property in your application and use Primary and...

Whatever you do please remove the bold on links feature. Its very annoying. Community Server is releasing(or has it released it already?) a new version CS2. I hear its pretty...

I would rather use the SET ROWCOUNT method than the dynamic SQL. Dynamic SQL is a different beast by itself.

Yes.

the windows (domain) account is local admin as well as sysadmin. So the user has every right to do what it wants.

You could script out the tables along with the indexes from Mgmt Studio.

You can use different accounts make sure they have appropriate permissions. If the snapshot is being written to a network drive, make sure the account your snapshot agent is using...

I understand. Its better you not get into the mess than get into it and take the blame officially.

Peer-Peer replication in SQL 2005 sounds promising. but again, you can...

Replication does not provide automatic provide automatic failover. Look into Mirroring. Theres synchronous vs asynchronous modes. Depending on your application/requirements you can go with either. Synchronous provides automatic failover.

Also...

Check out PSSDiag tool from MS. Its a free download. It combines both PerfLogs and SQLDiag into one tool and is pretty straight forward to configure.

I would recommend creating one account perhaps something like RplServiceAccount, making the user lodal admin on both the SQL boxes and also make the user sysadmin instad of dbowner. This...

Yes the user has to belong to sysadmin role to run profiler.

See if putting quotes around delimiters helps:

SET @CMD = @CMD + '-t"~" -r"/n" -f "C:\test\ACCOUNT.FMT" -S"IS763\SQLEXPRESS" -T -b50000'

Also, see if removing quotes for DB name helps.

SET @CMD = 'BCP...

AFAIK, you dont need to use WITH for NOLOCK. If you have index hints then you have to use the WITH keyword. but for Locking hints you dont need to...