April 28, 2010 at 12:00 pm
I have set up SQL Server 2005 mirroring(with principal, mirror and witness) and have configured for automatic failover, but before the mirror can be used as the principal I need to run a script to autofix the necessary database users. I created an alert so that when the status of the database changes to Manual Failover or Automatic Failover(select * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 or State = 8) that a script will run that runs the autofix command(sp_change_users_login 'auto_fix', 'xxxxxx'). This doesn't consistently happen even though the event occurs. Am I missing something?
Thanks.
April 28, 2010 at 12:10 pm
tas2k2 (4/28/2010)
I have set up SQL Server 2005 mirroring(with principal, mirror and witness) and have configured for automatic failover, but before the mirror can be used as the principal I need to run a script to autofix the necessary database users. I created an alert so that when the status of the database changes to Manual Failover or Automatic Failover(select * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 or State = 8) that a script will run that runs the autofix command(sp_change_users_login 'auto_fix', 'xxxxxx'). This doesn't consistently happen even though the event occurs. Am I missing something?Thanks.
I'd like to clarify something first, the event occurs, but the ALERT doesn't always fire, is that what you are seeing when a failover occurs?
April 28, 2010 at 12:53 pm
Thanks for your reply. I mistyped. The alert gets triggered but the script I have attached to the alert doesn't run. This occurs on the mirror when it fails over to the principal.
TAS/10.
April 28, 2010 at 1:21 pm
Is the alert configured on both servers to run the script?
April 28, 2010 at 1:23 pm
yes
April 28, 2010 at 1:44 pm
I'm not too sure if I can be of much more help. I didn't use alerts to handle the failover between my principal and mirror databases, I chose to use an event notification and a Service Broker activated stored procedure to handle the processing required during a failover. The only change I have had to make to my original process was to use msdn.dbo.sp_update_job to handle the enabling/disabling of database specific jobs.
April 28, 2010 at 1:48 pm
Thanks Lynn, I'll figure it out.
TAS/10.
April 28, 2010 at 1:55 pm
I did set up an alert once for a test on something totally unrelated, and iirc, there was a delay between the event and the alert firing. I can't remember how long between the event and the alert, but it was quite noticable.
April 28, 2010 at 4:23 pm
why not take orphaned users out of the equation and use sp_help_revlogin to synch the sids for the logins?
---------------------------------------------------------------------
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply