Newb trying to setup mirror

  • I have a database that I am configuring to mirror, with a witness on top of that. I've configured as best I know how all three servers, and all are ready to start mirroring. When I click "Start Mirroring" I get the following error (server names have been changed to simplify):

    The server network address "TCP://primary.domain.com: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)

    I've googled a bit on this, but I can't seem to find any definitive way to fix it. It should be noted that I have next to NO SQL experience, so bear with me please. I'm confused as the primary is the machine I'm setting this up on; why is it saying that the localhost doesn't exist?

    Microsoft SQL Server Management Studio 9.00.2047.00

    Microsoft Analysis Services Client Tools 2005.090.2047.00

    Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)

    Microsoft MSXML 2.6 3.0 6.0

    Microsoft Internet Explorer 6.0.3790.3959

    Microsoft .NET Framework 2.0.50727.42

    Operating System 5.2.3790

  • The endpoint for your other server is not available. Just like you connect to SQL Server on TCP port 1433, mirroring uses a different port, the default being the one listed there.

    This might help you: http://msdn.microsoft.com/en-us/library/ms179511.aspx

    You need to be sure that SQL server is listening on the endpoint and be sure that your network allows communications on this port.

  • Forgive me, as I'm just learning all this.

    Isn't the error telling me that my primary server (the one where the DB originally resided) is the one not listening? How would I set up an endpoint then? I have no firewalls in place between the servers, and it seems that the witness and secondary servers have their ports listening without any problems (and I didn't even do anything with them).

    Thanks for the article, reading it helped answer some questions. Just not the ones above. 😀

  • Did you use the "Configure Database Mirroring Security Wizard" to setup mirroring?

    You can get some information about the endpoints on each of your servers by querying the sys.endpoints DMV. That should at least show you if ot exists and is started.

    SELECT * FROM sys.endpoints WHERE type = 4

  • I did use the Wizard. In fact, it's all I used. I don't even know how to do anything else you just said. I'm not a DBA, but I'm the best my company has, so they tasked me with doing this. :ermm:

    They actually asked me if I could help them with working in Microsoft Squirrel.

    I know it's a bit of a pain, but if there's a way you could tell me either how to check what you said, or if there's a GUI option, I'd really appreciate it.

  • Microsoft Squirrel? Sounds like what every squirrel that's lost a nut could use.

    In SQL Server Management Studio (SSMS) is where you'd run that query to find out the status of the endpoints. Open a new query window to run that.

    Before you started the mirroring wizard did you do the following steps?

    1. Check that the principal database is in Full recovery mode.

    2. A backup of the principal has been taken and restored to the mirror server using "NORECOVERY"

    3. Have a list of the service accounts that each instance of SQL is running as.

  • I'm sorry; I realize I didn't let you guys know one piece of the puzzle.

    When I finished the wizard, it said it was configuring the endpoints on all servers concerned. It gave an error for the primary. I did it again, and this time it was it was successful on each of the three servers. It continues to give the 1418 error, though. Thanks again!

  • I ran that query and it listed it in state 0 and started. I did it on each of the three servers and they all said the same thing. The only difference is that the principle ID was the same on the witness and the secondary, but different on the primary. Don't know if that means anything.

  • You might want to delete the existing mirroring enpoints on each server, then go through the wizard again since you got an error the first time through.

    Before using the Wizard, recheck the network configuration on each server (DNS, Default Gateway, etc) and make sure you can ping each instance by host and FQDN before starting to help rule out

    any configuration issues at that level.

    Ref:

    MSSQLSERVER_1418

  • I think I'll do that.

    Sounds like a dumb question, but how do I remove the endpoints? It appears that the SQL Manager has remembered all settings, even though it has yet to be able to start mirroring.

  • To drop those endpoints from in SSMS open the Server Objects\Endpoints\Database Mirroring. Delete is available from the context menu.

    Did you verify that the database you restored on the mirror was in a 'recovery' state?

  • Todd,

    It's Monday, I'm back at work, and dealing with the mirroring again.

    Just to make sure I'm starting clean, I've done the following:

    Made a new full backup of the primary, and restored it to the secondary with NORECOVERY

    Deleted all the mirroring endpoints that the wizard had set up

    As for checking with service accounts the running instances are under . . . do you mean if it's a System or User process?

    Also, is there a way to install the SSMS 2005 as a stand alone app on my local machine, so I don't have to keep remoting into the server?

  • For the service accounts I mean the accounts that are being used to run the SQL Service. I could be wrong but I believe you need to use domain accounts if you want to use mirroring; unless you want to use certificates for securing your endpoints, which is something you might do in a workgroup configruation. Again, could be wrong about that but check into that especially if you're using the Network Service account or another local account.

    If you need to change your service accounts make sure to use the SQL Server Configuration Manager instead of making them through the Services MMC.

    And yes, you can install SSMS only. If you were installing from a CD just start the installation wizrard as if you were installing SQL Server, then on the Components to Install page select Workstation components, books online and development tools.

  • Thanks for that! I added the instance name I was running it from, and it moved to a new error (yay). It's the backups I did, evidentally it didn't like how I made them. So I blew away the secondary DB, and tried to create it from a restore command, instead of creating one, then telling it to restore (if that made any sense).

    It's 10% through the restore right now, then I'm going to try the mirror again. I'll report back later! 😛

  • I am feeling utterly defeated by this.

    Last night, I started the process from the beginning, just to make sure I did it all correctly.

    1) Performed a full backup of the primary, saved as a 9.5GB BAK file.

    2) Moved the BAK file to the secondary server

    3) Told the secondary SSMS to restore database from file

    4) Configured primary to mirror to secondary

    Primary told me that the transaction logs did not match, and that I should perform a restore of them as well.

    5) Backed up the transaction log from primary, moved file to secondary

    6) Told secondary to restore transaction log only to get the following error:

    The log in this backup set begins at LSN 201501000000050300001, which is too recent to apply to the database. An earlier log backup that includes LSN 20149300000012330001 can be restored.

    Now, the database on secondary has a (Recovering) behind it all the time, and the primary database is telling me that it can't mirror because "The remote copy of database has not been rolled forward to a point in time that is encompassed in the local copy of the database log."

    I know that I'm not an actual DBA, but no where in any manual or tutorial was I led to believe that something as simple as mirroring should be so complicated.:crazy:

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply