DB Mirroring doudts RRS feed

  • Question

  • Hi Team,

    I have some couple of questions reg db mirroring,

    1) Can we use mirror database has active secondaries (mirrors) so that we could actually use one as a reporting server and/or perform reads on your mirror on sql server 2012 ?

    2) If Yes,can you provide me the steps?

    3) What is the advantages,Disadvantages and limitations of having Witness server other than Automatic fail-over?

    4) Is it SQL 2012 Express edition is Free version of software to download?

    5) I have a scenario when Principle goes down how do i manually change mirror database to live (or) principle when witness is not present.Can you provide me the steps?

    6) If we go with witness server for automatic fail-over (or) Principle server crashes how does application recognizes which is primary and mirror and do we need to do any changes on app side?



    Tuesday, September 4, 2012 3:31 AM


  • 1) No, you need to create a snapshot database of mirroring and then use for reports that snapshot..

    3) It is about your business requirements. If principal has failed end users won't be automatically redirect to the mirroring(becomes principal) without  Witness server. 

    4) Yes it is

    5) http://msdn.microsoft.com/en-us/library/ms179481.aspx

    6) You need to specify mirror database /server in connection string.

    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=SQLNCLI10;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
    MS SQL Consultants: Improves MS SQL Database Performance

    • Marked as answer by Sarvan_19 Wednesday, September 5, 2012 4:32 AM
    Tuesday, September 4, 2012 5:25 AM