SQL Server Performance Forum – Threads Archive
redirct to mirror server by T-SQL
I use T-SQL to flush database server (principal role) but not find out the way to hanlde failover occur for my demo<br /><br />Can you help???<br /><br />Here is my code (without success) wishing any suggetion<br />declare @ConnectA as smallint<br />declare @id as bigint<br /><br /><img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />etvar Database2Mirror Mirroring<br /><img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />etvar partnername partnera<br /><br />set @ConnectA = 1<br />set @id = 1<br /><br />while (1 > 0) begin<br />set @id = @id + 1 <br />begin try<br /><font color="red">:connect $(partnername)</font id="red"><br />use Mirroring;<br />print ‘Insert in to servername: ‘;<br />insert into dbo.UserInfor (IncreaseNumber) values (@id);<br />end try<br />begin catch<br />w <br />if (@ConnectA = 1)<br /><img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />etvar partnername partnerb<br />set @connectA = 2 <br />else<br /><img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />etvar partnername partnera<br />set @connectA = 1<br /><font color="red">:connect $(partnername)</font id="red"><br />continue <br />end catch<br /><br />end<br />Are you getting any errors? BOL specifies
quote:To manually fail over a database mirroring session
Connect to the principal server. Set the database context to the master database: USE master; Issue the following statement on the principal server: ALTER DATABASE database_name SET PARTNER FAILOVER, where database_name is the mirrored database. This initiates an immediate transition of the mirror server to the principal role. On the former principal, clients are disconnected from the database and in-flight transactions are rolled back.
Connect to the principal server. Set the database context to the master database: USE master; Issue the following statement on the principal server: ALTER DATABASE database_name SET PARTNER FAILOVER, where database_name is the mirrored database. This initiates an immediate transition of the mirror server to the principal role. On the former principal, clients are disconnected from the database and in-flight transactions are rolled back.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
looking at my code, you can see that it try to fill data to a Mirroring system and what I try to do is handling the failover happen (that mean the script will continue to fill data in the Mirroring system) The problem is that, when I make the failover happen, my script stop to run because it can not automatic change to the mirror side that became principal Can you help, please!
Check the following thread… http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=20420
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>