locked
Auto redirect client in database mirroring in sql 2008 R2 RRS feed

  • Question

  • If someone is using sql 2005 version then need to be written code in application whereas I read in MS-sql 2008 Administration ,Implementation and maintenance book. If someone is using sql 2008 version or later then no need to write code in application .I am using sql 2008 r2 standard edition with window server 2012 .Can you tell me .How can i Use in it ?
    Tuesday, March 10, 2015 9:50 AM

Answers

All replies

  • Hello,

    Have you configured the witness server, so that it will automatically makes mirror server as principle and vice -versa.


    Regards, Pradyothana DP. Please Mark This As Answer if it solved your issue. Please Mark This As Helpful if it helps to solve your issue. ========================================================== http://www.dbainhouse.blogspot.in/

    Tuesday, March 10, 2015 3:07 PM
  • Hi sqldbasoon,

    If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server. Below is an example pointing out the failover functionality.

    Driver={SQL Server Native Client 10.0};Server=myServerAddress;
    
    Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;
    

    Please note if you are using TCP/IP (using the network library parameter) and database mirroring, including port number in the address (formed as servername,portnumber) for both the main server and the failover partner can solve some reported issues.

    For more details, please review the following articles.
    SQL Server 2008 connection strings
    Connecting Clients to a Database Mirroring Session (SQL Server)

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Wednesday, March 11, 2015 6:27 AM
  • I  have configured database mirroring with wittiness . Automatic failover is working fine , but client machine is not able to connect with principal server  automatically.

    Suppose I have two server and primary server  name is A  (192.168.23.10)  and secondary server name is B (192.168.23.254)  .I have made sever A as principal and made server B as mirror , Also I have installed one more sql instances at  server B for witness . 10 client machine is connected with sever A .If  server A is down then switch the role and server B became principal automatically but 10 client machine was not able to connect with the previous server I.P (192.168.23.10) and connect failed , Then I have to change the manually I.P on 10 client machine with this (192.168.23.254) instead of (192.168.23.10) then It worked .

    If I have to write code in application for the same then how can i do whereas I am not a developer and I don't have knowledge of .net and vb (visual basic) language , But I can write Query and Stored procedure If required.

    Wednesday, March 11, 2015 7:27 AM
  • Hi sqldbasoon,

    Have you checked my original post? Please also specify failover partner name in addition to the initial partner name in your client.

    For more details, please review the following articles.
    https://msdn.microsoft.com/en-us/library/ms366348(v=sql.105).aspx
    https://msdn.microsoft.com/en-us/library/ms366199.aspx

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Friday, March 13, 2015 6:04 AM
  • Kindly share the step by step configuration  for below script so that I can do accordingly.

    Driver={SQL Server Native Client 10.0};Server=myServerAddress;

    Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;

    Thanks for your support 

    Lydia Zhang

    Friday, March 13, 2015 8:15 AM
  • What is your client application, developed by VB language?

    How to write the vb code of database mirroring configuration, you can post here https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=vbgeneral
    Friday, March 13, 2015 9:39 AM