SQL Server Performance

redirct to mirror server by T-SQL

Discussion in 'SQL Server 2005 Database Mirroring' started by kernelvn, May 10, 2007.

  1. kernelvn New Member

    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 &gt; 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 />
  2. satya Moderator

    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.


    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.
  3. kernelvn New Member

    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!
  4. MohammedU New Member

Share This Page