locked
Mirroring Problem in SQL Server 2005/SP3 RRS feed

  • Question

  • I have 2 servers sitting side by side in the same equipment rack/ same network. I can not get mirroring to work between them. Originally they were in a work group. When I couldn't get mirroring to work right, I promoted one to a domain controller and put both machines into the domain. (So that I could use FQDNs instead of IP addresses) .  I have sql server running under the Administrator account. Endpoints start ok on both machines. Both ARE listening on port 5022. I am getting the infamous 1418 error  "cant be reached or doesn't exist". I had contacted MS PAID support previously on this and they dont know what the ____ they are doing. I specifically asked for a support technician the really really understood mirroring. I eventually canceled the support call because they were unable to solve the issue. I have a production server in the same rack that mirrors 3 DBs successfully to a remote location over the internet. It has worked flawlessly after I got it running. I stopped mirroring on one of the databases and when I tried to restart it (set partner on) it wouldn't restart mirroring. Same 1418 error which some jerk seems to have used for every error that happens in mirroring.

    I am frustrated to the max with failures in mirroring. I have previously fixed this kind of issue by simply uninstalling sql server 2005 and resinstalling. That's not a good option in this case because the servers are 500 miles away at DisneyWorld.  I belive that it is related to IP address changes in the network. After changing the IP address on the machine and restarting sql server, the sql server configuration utility, where you tell it to listen on additional ports besides 1433, did not have the correct new ip address .. it still had the old one. I changed it to the new one and have restarted both slq server and the machine multiple times. I believe there must be a registry entry or something that still has the old ip address and that is the one that sql server is using to listen on for mirroring despite anything that netstat -an shows.

    Does anyone out there have a clue as to how to fix this.  Please dont tell me to restore the mirror database to "restoring" with the logfiles. I know all about that. I have done it successfully at least a hundred times in testing and production to implement mirroring. It seems as though once this problem starts, only uninstalling sql server 2005 and reinstalling seems to fix the problem. That is why I think it is an IP problem. If sql server is installed with the ip address already set and no changes are made, then mirroring works easily .. othewise if you change ip addresses after installing sql server you are really in trouble.

    Any insite will be appreciated. 

    Del (an old frustrated programmer)

    Monday, May 17, 2010 3:05 PM

All replies

  • Promoting domain controller is not a solution, let you know why.

    First reboot both server.
    Removed the EndPoint in both server and started the process from the beginning
    IF you are using terminal to connect each other server, Stop it,  Don't do it through terminal session, login through console.
    Use/Set Same password on both server for SQL account.
    Make sure that Master DB is not getting updated when you prepare mirror server with restoring latest full backup.
    Restored the database with the "WITH NORECOVERY" option
    Run Below query on both servers
    TCP and name pipes must be available in both servers.


    ALTER DATABASE "NAME"
    SET PARTNER = 'TCP://Master_FQDN:xxxx'
    GO
    ==== ON Mirror===================================

    ALTER DATABASE "NAME"
    SET PARTNER = 'TCP://Mirror_FQDN:xxxx'
    GO

    Both should show success in result..

    Having DC environment is not recommended as if primary server goes down there wont be any server to authenticate user account. [SQL account]
    if you wish to have domain controller then both server should be domain controller, MaterDB as domain controller, MirrorDB as Additional domain controller with DNS on same server. Again in case if primary domain controller fails you will have to Transfer/Seize FSMO role>

    Doing this you will have to administer 2 things DC/ADC environment and SQL Mirroring environment which will definitely increase your frustration.

    You must be knowing this, but still I thought to inform/remind you.

    TO use FQDN use DNS suffix  [computer properties >> name >> more >> DNS suffix >> append DNS suffix check box

     


    http://eukhostsupport.com. Eukhostsupport 24/7 Proactive support, Outsource. EMAIL="contactus@eukhostsupport.com
    Tuesday, May 18, 2010 6:11 AM
  • Hello,

    Check this link, I hope all your issues has ben sorted out with these links

     

    http://www.codeproject.com/KB/database/sqlmirroring.aspx

    http://www.mssqltips.com/tip.asp?tip=1705

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

    http://blogs.msdn.com/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

     


    UK-Sam [url] http://eukhostsupport.com[/url] email: contactus@eukhostsupport.com
    Tuesday, May 18, 2010 2:47 PM
  • UK-Sam,

    I am only testing the mirror in the domain/pdc environment because all other attempts at configuring a test mirror have failed. I did this so that I would be using FQDN's in a REAL domain, not in a workgroup. I would not make the DC the Mirror, but might make it the Witness. If I remember right, if the Witness fails, mirroring continues but must then be manually failed-over in the event the Principal fails. This was only a temporary setup and should NOT have caused mirroring to not work.

    These machines are in a remote data center. I can not log into them directly through the console. Why would that make any difference ? The end points are being created correctly and they are started.

    I currently have mirroring running in production and did not have to build certificates. The Mirroring Wizard took care of that for me ... I dont understand why it should be failing now. Microsoft needs to fix this stuff if they are going to advertize that they have it as a feature of their product. It is unacceptable that it works part of the time and fails part of the time in the same network and using the same login IDs and user names.

    I'm going to uninstall SQL Server and Reinstall it with the correct private IP addresses for the network in which the servers are to operate (not in my test network in the office as I previously configured the machines) and I'll bet 50 dollars that it Mirroring comes up and works correctly the first time. I'll let you know. If that is the solution, it is certainly a sorry one and verifies my theory that there are things happening in setting up mirroring that we dont know about.

    Tuesday, May 18, 2010 3:29 PM
  • We will wait for your update, meanwhile you can also check the Ports you have used in SQL Mirroring is get blocked by Firewall or not. Also, you can add the FQDN entry in the host file. Please let us know the exact error you are getting in SQL Mirror Wizard. It might help us to resolve your issue.

    Regards,


    UK-Sam [url] http://eukhostsupport.com[/url] email: contactus@eukhostsupport.com
    • Proposed as answer by LearnerSql Sunday, September 18, 2011 3:49 PM
    Thursday, May 20, 2010 5:32 AM