locked
Can a database mirroring configured on a single stand alone installation? RRS feed

  • Question

  • Hello,

    I want my database to be mirrored . is it possible to mirror it on the same machine.

    I have tried to create a named instance but while trying to configure its not visible.? kIndly help.

    Am using SQL SERVER 2008

    Wednesday, July 15, 2015 5:56 PM

Answers

All replies

  • Hello,

    I want my database to be mirrored . is it possible to mirror it on the same machine.

    I have tried to create a named instance but while trying to configure its not visible.? kIndly help.

    Am using SQL SERVER 2008

    you should be able to configure database mirroring between two instances running on the same machine.

    what do you mean by not visible??


    Hope it Helps!!

    Wednesday, July 15, 2015 6:08 PM
  • I have created a two seperate instances namley DRV(Default) and MIRROR.

    But when I go to Mirror option and select the principal instance is in DRV instance and in next screen if I select MIRROR  instance and select sql authentication to connect. its throwing me an error

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to mirror.

    ------------------------------
    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Kindly help me

     
    Wednesday, July 15, 2015 7:04 PM
  • then first try connecting to the mirror instance from management studio using the same login information - you should get the same error. so, this means it not mirrioging issue but sql instance issue.

    and, this is common issue-  refer this article on how to fix it... 

    http://stackoverflow.com/questions/18060667/cannot-connect-to-server-a-network-related-or-instance-specific-error

    http://www.sswug.org/articlesection/default.aspx?TargetID=44331

    make sure you mixed authentication enabled and also, TCP /IP proctol is enabled.


    Hope it Helps!!

    Wednesday, July 15, 2015 7:13 PM
  • I have been succesfull in configuring the mirror instance also.

    Now when I click on start mirroring am getting an error as :

    TITLE: Database Properties
    ------------------------------

    An error occurred while starting mirroring.

    ------------------------------
    ADDITIONAL INFORMATION:

    Alter failed for Database 'DPOS'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    The server network address "TCP://DRV:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    I have also enable a new profile in firewall to allow port numbers that i am using for the principal and mirror instances.

    Kindly help out....!!!

    Thanks in advance

    Wednesday, July 15, 2015 8:14 PM
  • Hi Vijay,

    If you are just trying to learn setting up SQL Mirroring, I personally recommend not to waste too much time around this as this is going to be deprecated as of SQL Server2012 and will be removed in the near future. Try configuring if possible/learn how to configure  SQL Always On which is the new HA feature which is a replacement for mirroring.

    Coming back to your question, have you created firewall rules for the mirroring ports? If not create a inbound firewall rule for your mirroring port(5022) and try again. 

    Also see this link for help

    http://blogs.msdn.com/b/grahamk/archive/2008/11/21/how-to-configure-database-mirroring-between-2-instances-on-a-standalone-server.aspx

    http://blog.sqlauthority.com/2010/01/11/the-server-network-address-tcpsqlserver5023-can-not-be-reached-or-does-not-exist-check-the-network-address-name-and-that-the-ports-for-the-local-and-remote-endpoints-are-operational-microso/

    Hope this helps

    Thanks

    Bhanu 




    • Edited by bhanu_nz Wednesday, July 15, 2015 10:05 PM
    Wednesday, July 15, 2015 9:39 PM
  • Yes it is possible to mirror the database on the same machine with different instances, not on the same instance.

    I am sure this is not your production requirement. From the technical point of view you can configure mirroring between two different instances. YOu need to use different end points and configure correctly.

    Here is the link some one tried long time ago. 

    Thursday, July 16, 2015 5:08 AM