locked
database mirroring RRS feed

  • Question

  • Hi all,

    how to client automatically connect to mirror server when fail over occur in principal server(automatic fail over)...?

    please explain that scenario...

    Tuesday, June 11, 2013 9:11 AM

Answers

  • Please read

    http://msdn.microsoft.com/en-us/library/ms189590(v=sql.105).aspx

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Fanny Liu Wednesday, June 12, 2013 8:01 AM
    • Marked as answer by Fanny Liu Tuesday, June 18, 2013 7:44 AM
    Tuesday, June 11, 2013 9:15 AM
  • You need to specify a partner (mirroring) within a connection string

    http://www.connectionstrings.com/sql-server-2012

    Database mirroring

    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.

    Provider=SQLNCLI11;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;
    Initial Catalog=myDataBase;Integrated Security=True;

    There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Fanny Liu Wednesday, June 12, 2013 8:01 AM
    • Marked as answer by Fanny Liu Tuesday, June 18, 2013 7:44 AM
    Tuesday, June 11, 2013 9:32 AM
  • Yes  it is possible with synchronous with witness server(High availability) .

    Please go through below link, it may help you.

    @@http://technet.microsoft.com/en-us/library/ms175191.aspx

    • Proposed as answer by Fanny Liu Wednesday, June 12, 2013 8:02 AM
    • Marked as answer by Fanny Liu Tuesday, June 18, 2013 7:44 AM
    Tuesday, June 11, 2013 11:32 AM

All replies

  • Please read

    http://msdn.microsoft.com/en-us/library/ms189590(v=sql.105).aspx

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Fanny Liu Wednesday, June 12, 2013 8:01 AM
    • Marked as answer by Fanny Liu Tuesday, June 18, 2013 7:44 AM
    Tuesday, June 11, 2013 9:15 AM
  • You need to specify a partner (mirroring) within a connection string

    http://www.connectionstrings.com/sql-server-2012

    Database mirroring

    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.

    Provider=SQLNCLI11;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;
    Initial Catalog=myDataBase;Integrated Security=True;

    There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Fanny Liu Wednesday, June 12, 2013 8:01 AM
    • Marked as answer by Fanny Liu Tuesday, June 18, 2013 7:44 AM
    Tuesday, June 11, 2013 9:32 AM
  • Yes  it is possible with synchronous with witness server(High availability) .

    Please go through below link, it may help you.

    @@http://technet.microsoft.com/en-us/library/ms175191.aspx

    • Proposed as answer by Fanny Liu Wednesday, June 12, 2013 8:02 AM
    • Marked as answer by Fanny Liu Tuesday, June 18, 2013 7:44 AM
    Tuesday, June 11, 2013 11:32 AM
  • SQL 2012 AlwaysOn handles this with the use of a Listener
    Tuesday, June 11, 2013 12:42 PM