locked
Mirroring WILL NOT WORK!! RRS feed

  • Question

  • I am new to using SQL Server, but I have tried seemingly everything in an attempt to establish a mirroring session between two SQL Server 2005 machines.  Both are located on same subnet (10.10.1.XXX).  I altered the hosts file on both machines to point to each other.  From the command prompt, I can ping NETBIOS name, FQDN, or IP Address.  I can establish Telnet sessions with both servers on the port that was used during Endpoint creation.  I have created a User on both machines that has sysadmin priveleges, and is the owner of the database I am trying to mirror.  I have also tried creating certificates for both of the machines and then passing those certificates to the other machine respectively (although, I was unsure of where to place these certificates, and they are currently located on the root of the machine, the c:drive)  Another important thing to mention would be that I am using VMWare Workstation with two virtual machines.  I have the network settings set to Bridged so that each machine operates seperately from the others.  However, I have since set up two actual computers with Windows Server 2003, and I am still encountering the same issues.  When I attempt to Alter the Database and Set the Partner on the Principal Server (which is of course the second to last step in a 50-step process), I get an error message that states the partner can not be reached.  Upon further inspection, I found the SQL Server Agent Log states that it could not find the certificate for logging on to the partner.  The timeout always occurs at roughly 20 seconds after the T-SQL statement is executed.  Any help on this issue would be greatly appreciated.  I would ask for a clear and concise tutorial for setting up a mirroring session, but I have went through about ten of those already, and all of them are different (half of them don't specify which machine you are working on principal or mirror; it just seems that most of the tutorials out there are for people who already know a lot about SQL Server, and I don't)
    Friday, June 25, 2010 3:53 PM

Answers

  • Here is a script which will walk you through setting up mirroring, it does not utilize a witness but could be easily added.  Give it a try and let me know if it helps.  If you have an existing master key you will have to drop it or skip that step.  To drop the key the command is DROP MASTER KEY.

    -- =====================================
    -- Mirroring with Certificates Template
    -- =====================================
    -- CONFIGURE OUTBOUOND CONNECTIONS
    ---- ON PRINCIPAL SERVER - PRIMARY
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
    GO
    USE master;
    CREATE CERTIFICATE PRIMARY_cert
       WITH SUBJECT = 'PRIMARY certificate';
    GO
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7024
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE PRIMARY_cert
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       );
    GO
    BACKUP CERTIFICATE PRIMARY_cert TO FILE = 'C:\CERTS\PRIMARY_cert.cer';
    GO

    ---- ON MIRROR SERVER - MIRROR
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
    GO
    CREATE CERTIFICATE MIRROR_cert
       WITH SUBJECT = 'MIRROR certificate for database mirroring';
    GO
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7025
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE MIRROR_cert
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       );
    GO
    BACKUP CERTIFICATE MIRROR_cert TO FILE = 'C:\CERTS\MIRROR_cert.cer';
    GO

    -- Configure Inbound Connections
    ---- ON PRINCIPAL SERVER PRIMARY
    USE master;
    CREATE LOGIN MIRROR_login WITH PASSWORD = 'P@ssword1';
    GO
    CREATE USER MIRROR_user FOR LOGIN MIRROR_login;
    GO
    CREATE CERTIFICATE MIRROR_cert
       AUTHORIZATION MIRROR_user
       FROM FILE = 'C:\CERTS\MIRROR_cert.cer'
    GO
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIRROR_login];
    GO

    ---- ON MIRROR SERVER MIRROR
    USE master;
    CREATE LOGIN PRIMARY_login WITH PASSWORD = 'P@ssword1';
    GO
    CREATE USER PRIMARY_user FOR LOGIN PRIMARY_login;
    GO
    CREATE CERTIFICATE PRIMARY_cert
       AUTHORIZATION PRIMARY_user
       FROM FILE = 'C:\PRIMARY_cert.cer'
    GO
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [PRIMARY_login];
    GO

    -- BACKUP DATABASE ON PRINCIPAL
    ---- FULL BACKUP
    USE dbname;
    GO
    BACKUP DATABASE dbname
    TO DISK = 'C:\CERTS\dbname_Full.Bak'
       WITH FORMAT,
          MEDIANAME = 'dbnameBackups',
          NAME = 'Full Backup of dbname';
    GO

    -- RESTORE DATABASE TO MIRROR
    ---- FULL BACKUP RESTORE - WITH NORECOVERY & MOVE
    RESTORE DATABASE dbname
       FROM DISK='C:\CERTS\dbname_Full.Bak'
       WITH NORECOVERY,
          MOVE 'dbname_Data' TO
             'D:\Data\dbname_Data.mdf',
          MOVE 'dbname_Log' TO
             'D:\Data\dbname_Log.ldf';
    GO

    ---- TRANSACTION LOG BACKUP
    USE dbname;
    GO
    BACKUP LOG dbname
    TO DISK = 'C:\CERTS\dbname_Log.trn'
       WITH FORMAT,
          MEDIANAME = 'dbnameBackups',
          NAME = 'Log Backup of dbname';
    GO


    ---- TRANSACTION LOG BACKUP RESTORE - WITH NORECOVERY
    RESTORE LOG dbname
        FROM DISK = 'C:\CERTS\dbname_Log.trn'
        WITH NORECOVERY
          MOVE 'dbname_Data' TO
             'D:\Data\dbname_Data.mdf',
          MOVE 'dbname_Log' TO
             'D:\Data\dbname_Log.ldf';
    GO

    -- CONFIGURE MIRRORING PARTNERS
    ---- ON MIRROR SERVER MIRROR
    ALTER DATABASE dbname
        SET PARTNER = 'TCP://PRIMARYSERVERNAME.DOMAIN.com:7024';
    GO

    ---- ON PRINCIPAL SERVER PRIMARY
    ALTER DATABASE dbname
        SET PARTNER = 'TCP://MIRRORSERVERNAME.DOMAIN.com:7025';
    GO


    MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    • Marked as answer by John Bart Tuesday, July 20, 2010 6:04 PM
    Thursday, July 15, 2010 6:36 AM

All replies

  • Sounds like your servers are not in the same domain?  I haven't set it up, but maybe this article will have some pointers as to what might be wrong.

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


    David A. Bermingham, Director of Product Management, SteelEye Technology
    Friday, June 25, 2010 5:07 PM
  • This is actually one of the Tutorials that I used (the most concise one I've used thus far), and the hang-up was on step 13 "Prepare the Principal Server for the database mirroring session"

    Now that the mirror database has been prepared for the database mirroring session, we will do the same for the principal database specifying SQLServer2 as the partner.

    --Run this on SQLServer1 
    USE master 
    GO 
    ALTER DATABASE Example1 SET PARTNER 'TCP://SQLServer2:9999'
    GO 

    Error 1418: unable to connect to server ---- but upon further investigation (since you can ping IP, Netbios, or FQDN name from command prompt) I found the error logs were referring to not being able to find a certificate for the connection... Do these certificates need to be placed in a certain location??? or Do they need to be Installed??? If so, how is this accomplished???

    Friday, June 25, 2010 5:44 PM
  • Error 1418 indicates that the server network address cannot be reached or does not exist, and it suggests that you verify the network address name and reissue the command. Excluding database mirroring for a moment, are you able to connect to other server using SQL Server Management Studio (from principal SQL Server Studio to mirror and vice-versa)?

    Also can you check "endpoints" as mentioned in http://msdn.microsoft.com/en-us/library/ms189127.aspx ?

    Sunday, June 27, 2010 8:44 AM
  • Vijay,

         Yes, I am able to connect from Principal to Mirror and vice versa from SQL Server Management Studio.  Endpoint configuration is as follows:

    Principal (SQLServer1):

    type_desc = DATABASE_MIRRORING

    port = 9999

    state_desc = STARTED

    role = 3 (ALL)

    connection_auth = 4 (certificate)

    Mirror (SQLServer2):

    SAME AS PRINCIPAL

    The exact statement that I used for the endpoint creation is as follows:

    Principal (SQLServer1):

    create endpoint endpoint_mirroring state = started

    as tcp(listener_port=9999, listener_ip=all)

    for database_mirroring (authentication = certification SQLServer1_cert,

    encryption = required algorithm aes, role = all);

    go

    Mirror (SQLServer2):

    create endpoint endpoint_mirroring state = started

    as tcp(listener_port=9999, listener_ip=all)

    for database_mirroring (authentication = certification SQLServer2_cert,

    encryption = required algorithm aes, role = all);

    go

    Monday, June 28, 2010 5:40 AM
  • From the SQL Server Log:

    Error 1: Source: Logon  ;  Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason:  Certificate not found.  State 89.'. [CLIENT: 10.10.1.140]

    Error 2: Source: spid25s  ;  Error:1474, Severity: 16, State: 1.

    Error 3: Source: spid25s  ;  Database mirroring connection error 5 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.'for'TCP://sqlserver2.john.local:9999'.

    Error 4: Source: Logon  ;  Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason:  Certificate not found.  State 89.'. [CLIENT: 10.10.1.140]

    Error 5: Source: spid21s  ;  Error: 1443, Severity: 16, State: 2.

    All of these Messages occurred during the twenty second time period that the Principal was attempting to connect to the Mirror.  There were fourteen error messages total and all of them were simply duplicate errors of the errors already stated above. 

    Monday, June 28, 2010 9:56 PM
  • I am having the same issue. My servers are Windows Server 2008 R2, running SQL Server 2008.

    My servers are not on a domain, so I am using certificates. All of the T-SQL to create the mastery key, the certificates, the logins, backups, restore, etc. all runs without error. It all looks good until I try to set the partners.

    I issue the command from the mirror that is in restore mode:

         ALTER DATABASE DBName SET PARTNER = 'TCP://MyServerA:5022' and it is successful.

    I issues the command from the Primary to the mirror:

         ALTER DATABASE DBName SET PARTNER = 'TCP://MyServerB:5022' and it fails.

    The SQL Server log says what your Error 1 says: certificate not found. State 89.

     

    Things I have checked and tried:

    * The user account that owns the Database services is the same on both servers.

    * I have added the server names and IP addresses to the hosts file on both machines.

    * I have tried to fake a domain by adding a .local to the server name.

    * I have scrapped and recreate the two databases three times (pre-production system).

    * I can ping and telnet and the ports are listening.

     

    I have been trying to figure this out for days.

    Wednesday, June 30, 2010 12:31 AM
  • I've had this problem in the past, and the way I managed to get it to work was to put MyServerA and MyServerB as lines in the hosts file (C:\Windows\system32\drivers\etc) on both servers (or on all three servers, if a witness is used).
    Monday, July 5, 2010 5:35 AM
    Answerer
  • I think you need to grant connect to permission for the SQL Server Engine account on both the endpoints.

    SKG: Please Marked as Answered, if it resolves your issue.
    Monday, July 5, 2010 11:07 AM
  • I have granted connect on endpoint to the user I was using for authentication (TestLogin) between the servers.  I then tried granting connect to "NT AUTHORITY\NETWORK SERVICE"

    I did this on both the principal and the mirror.

    I am not sure if this is what you were prescribing or not.   Please elaborate on "SQL Server Engine account"

    Thanks

    -John B. 

    Tuesday, July 6, 2010 7:52 AM
  • What I was suppose to mean is the account used to run the SQL Server Database Engine Service (SQL Server Service). If you are using a domain account to run the SQL Server Service, I suggest to authorize the same accounts on all the endpoints.

    The steps to configure Database Mirroring is as follows:

    1. You need to create endpoints on the Principal, Mirror and Witness instances. For testing purpose, you can create three instances on the same server, and test the database mirroring features. However, in production environments, you should use different servers.

    2. If you are configuring mirroring on the same server on three different instances, make sure the endpoints use three different ports to avoid the conflict.

    3. Now say, you have created three endpoints on the three instances Default, Test1, Test2 with ports 5024, 5026, 5028 on the server DeepTest. You need to create a DB mirror for the database TestDB on the Default instance. Create a full backup of the database, followed by a transactional log backup. Restore it on the instance Test1 with NORECOVERY option.

    4. Execute the following query on the respective instances as mentioned below:

    -- On Test1 instance, where you have restored the database with Norecovery option
    ALTER DATABASE [TESTDB] SET PARTNER = 'TCP://DeepTest:5024';
    GO
    -- On Test2 instance, which is the Witness instance
    ALTER DATABASE [TestDB] SET PARTNER = 'TCP://DeepTest:5024';
    GO
    -- On default instance, where you have your main database
    ALTER DATABASE [TestDB] SET PARTNER = 'TCP://DeepTest:5026';
    GO
    ALTER DATABASE [TestDB] SET PARTNER = 'TCP://DeepTest:5028';
    GO

    5. Once you configured as said above, right click on the TestDB database on the default instance, open database mirroring monitor. You should get both of your databases and get all the details about the mirroring. It may take a little time to synchronized, but surely will not take longer.

    Hope this may help.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, July 6, 2010 8:35 PM
  • Sudeepta,

    I have performed these steps while using only two servers.  I do not have a witness server, and I have two computers with Windows Server 2003 installed on them.  I then fully updated Windows and installed IIS.  I then Installed SQL Server 2005, and updated to service pack 3. I first attempted using certificates to establish mirroring, so I could be sure it would work across domains.  However, now I am just tring to get it to work on one domain "john.local"  I have altered the hosts files of both computers to reflect this domain membership.

    I then created the SQL user "TestLogin" on both Servers. (I gave this user sysadmin priveleges, granted this user connect on the Mirroring Endpoint and made it owner of database: "Example1")

    I created a database titled "Example1" which I backed up in full recovery mode.

    I then created a Transactional log backup as well.

    I then moved these two files "Example1.bak" and "Example1TransLog.trn" to the Mirror Server.

    I then created a Database titled "Example1" on the Mirror Server.

    I then right clicked on the database--> Tasks --> Restore

    I then selected the Example1.bak file and selected the NORECOVERY Option.

    I then did the same with the Transactional Log backup.

    I then opened a query on the Mirror and I performed the Set Partner command.

    Command Successful.

    I then attempted the Set Partner command on the Principal (which obviously points towards the mirror).

    However, I received the Error 1418 which led me to believe a DNS problem was occurring.

    I then altered the hosts file such that:

         From the command prompt, you can ping the Netbios name, FQDN, and IP address of both servers. Telnetting into both servers also works.

    However, upon further inspection of the SQL Server Event Logs, I found that there were three re-occurring errors:

    1.) Error 1474, Severity: 16, State: 1

    2.) Database Mirroring connection error 4 'An Error occurred while receiving data: '10054 (An Existing connection was forcibly closed by the remote host.

    3.) Error 1443, Severity: 16, State: 2

    Thank you for helping me with this problem Sudeepta and all others, I appreciate it very much.

    Wednesday, July 7, 2010 1:30 AM
  • Well this leds to the same question which I asked before. Are you running your SQL Server Services using a domain user account, in your case, "John.local"? If yes, then grant the connect to permission on the endpoints for the domain accounts used to run SQL Server Service.

    Using two servers, you can install three instances of SQL Server, which can be used as a Witness server (most commonly, on the instance, where the Principal database resides during Mirroring implementation). You don't need a separate windows server for that for testing purpose. Just to add, SQL Server Express Edition with Advance service can be used as a witness server, which can be installed on Windows XP.

    About the problem, you have reported, I will try to find out more information on that. However, for the time being can you let me know the accounts under which you are running the SQL Server services and after you grant the permission to connect to the endpoints for those accounts, are you able to start mirroring???

    Waiting for your further update. Thanks.


    SKG: Please Marked as Answered, if it resolves your issue.
    Wednesday, July 7, 2010 6:56 AM
  • Sudeepta,

    I have the accounts set up as such:  I am not using a domain user account because I was attempting to do this locally without the use of a DNS server or Active Directory.  As such, I had attempted doing it this way:

    Principal:                                                                                                Mirror:

    SQL installed with mixed mode authent.                                        SQL installed with mixed mode authent.

    Logged onto computer using local account "john"                           Logged onto computer using local account "john"

    Logged onto SQL using account "TestLogin"                                   Logged onto SQL using account "TestLogin"

    It should be noted that there are TWO john usernames and TWO testlogin usernames... one for each server both with the same password.  For mirroring to occur, should I be using a domain level account?  What if I don't have a domain, and I want to mirror servers?  I tried to Grant connect to endpoint on SQLSERVER2 with this statement so that the user from sqlserver1 would be able to connect, but I was given the error message does not exist or you do not have the priveleges to make this change.

    Grant connect on endpoint::endpoint_mirroring to [sqlserver1/TestLogin]

    go

    Thursday, July 8, 2010 3:37 PM
  • Apologies if the link below is the same that you may have seen already but I have used this in the past and it worked perfectly first time. I have found it to be very consise and it shows exactly on which machine to apply which piece of TSQL

    http://msdn.microsoft.com/en-us/library/ms191140.aspx

    Friday, July 9, 2010 8:32 AM
  • Here is a script which will walk you through setting up mirroring, it does not utilize a witness but could be easily added.  Give it a try and let me know if it helps.  If you have an existing master key you will have to drop it or skip that step.  To drop the key the command is DROP MASTER KEY.

    -- =====================================
    -- Mirroring with Certificates Template
    -- =====================================
    -- CONFIGURE OUTBOUOND CONNECTIONS
    ---- ON PRINCIPAL SERVER - PRIMARY
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
    GO
    USE master;
    CREATE CERTIFICATE PRIMARY_cert
       WITH SUBJECT = 'PRIMARY certificate';
    GO
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7024
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE PRIMARY_cert
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       );
    GO
    BACKUP CERTIFICATE PRIMARY_cert TO FILE = 'C:\CERTS\PRIMARY_cert.cer';
    GO

    ---- ON MIRROR SERVER - MIRROR
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
    GO
    CREATE CERTIFICATE MIRROR_cert
       WITH SUBJECT = 'MIRROR certificate for database mirroring';
    GO
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7025
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE MIRROR_cert
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       );
    GO
    BACKUP CERTIFICATE MIRROR_cert TO FILE = 'C:\CERTS\MIRROR_cert.cer';
    GO

    -- Configure Inbound Connections
    ---- ON PRINCIPAL SERVER PRIMARY
    USE master;
    CREATE LOGIN MIRROR_login WITH PASSWORD = 'P@ssword1';
    GO
    CREATE USER MIRROR_user FOR LOGIN MIRROR_login;
    GO
    CREATE CERTIFICATE MIRROR_cert
       AUTHORIZATION MIRROR_user
       FROM FILE = 'C:\CERTS\MIRROR_cert.cer'
    GO
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [MIRROR_login];
    GO

    ---- ON MIRROR SERVER MIRROR
    USE master;
    CREATE LOGIN PRIMARY_login WITH PASSWORD = 'P@ssword1';
    GO
    CREATE USER PRIMARY_user FOR LOGIN PRIMARY_login;
    GO
    CREATE CERTIFICATE PRIMARY_cert
       AUTHORIZATION PRIMARY_user
       FROM FILE = 'C:\PRIMARY_cert.cer'
    GO
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [PRIMARY_login];
    GO

    -- BACKUP DATABASE ON PRINCIPAL
    ---- FULL BACKUP
    USE dbname;
    GO
    BACKUP DATABASE dbname
    TO DISK = 'C:\CERTS\dbname_Full.Bak'
       WITH FORMAT,
          MEDIANAME = 'dbnameBackups',
          NAME = 'Full Backup of dbname';
    GO

    -- RESTORE DATABASE TO MIRROR
    ---- FULL BACKUP RESTORE - WITH NORECOVERY & MOVE
    RESTORE DATABASE dbname
       FROM DISK='C:\CERTS\dbname_Full.Bak'
       WITH NORECOVERY,
          MOVE 'dbname_Data' TO
             'D:\Data\dbname_Data.mdf',
          MOVE 'dbname_Log' TO
             'D:\Data\dbname_Log.ldf';
    GO

    ---- TRANSACTION LOG BACKUP
    USE dbname;
    GO
    BACKUP LOG dbname
    TO DISK = 'C:\CERTS\dbname_Log.trn'
       WITH FORMAT,
          MEDIANAME = 'dbnameBackups',
          NAME = 'Log Backup of dbname';
    GO


    ---- TRANSACTION LOG BACKUP RESTORE - WITH NORECOVERY
    RESTORE LOG dbname
        FROM DISK = 'C:\CERTS\dbname_Log.trn'
        WITH NORECOVERY
          MOVE 'dbname_Data' TO
             'D:\Data\dbname_Data.mdf',
          MOVE 'dbname_Log' TO
             'D:\Data\dbname_Log.ldf';
    GO

    -- CONFIGURE MIRRORING PARTNERS
    ---- ON MIRROR SERVER MIRROR
    ALTER DATABASE dbname
        SET PARTNER = 'TCP://PRIMARYSERVERNAME.DOMAIN.com:7024';
    GO

    ---- ON PRINCIPAL SERVER PRIMARY
    ALTER DATABASE dbname
        SET PARTNER = 'TCP://MIRRORSERVERNAME.DOMAIN.com:7025';
    GO


    MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    • Marked as answer by John Bart Tuesday, July 20, 2010 6:04 PM
    Thursday, July 15, 2010 6:36 AM
  • Everyone,

    Thank you all very much for your help. I appreciate it more than you all know. 

    Randall, Your script worked great for me, and I noticed what I was doing wrong in the first place as well.  Turns out I was granting connect on endpoint to the user as opposed to the login.

    I believe this is what was causing my issue, but nevertheless the mirroring session is now operational, and I am ecstatic. 

    Thank you all again for your help, and I hope that some day in the future I can be of help to others in these forums as you have all helped me.  Have a good day everyone!

     

    - John Bartels

    Tuesday, July 20, 2010 6:01 PM
  • Dear All,

    I have followed all the steps from Randall Neth all the steps worked fine but got an error while executing following command.

    ---- ON PRINCIPAL SERVER PRIMARY
    ALTER DATABASE dbname
        SET PARTNER = 'TCP://MIRRORSERVERNAME.DOMAIN.com:7025';
    GO

     

    FYI,

    I am using 2 systems under same domain for DB mirroring.

    Principal sys. -  OS - Windows server 2008 R2 standard & SQL server 2008 R2.

    Mirror sys. - OS - Windows XP sp3 & SQL server 2008 R2.

    Following are the check list I have done :

    1.    mssqlservices are running under NETWORK SERVICE account

    2.    Filestream for TSQL is not enabled

    3.    firewalls OFF.

    4.    I am a local admin in both machines & also is a sysadmin user in both sqlservers.

    5.    I am using Windows authentication in both system.

    6.     DB backup with full recovery model & DB restore to a mirror database with WITH NORECOVERY option

    7.     Since endpoint is already present by Mirroring name so i have not deleted it instead  i have used alter command as follows.

    alter ENDPOINT [Mirroring]

    STATE=STARTED

    AS TCP (LISTENER_PORT = 7023, LISTENER_IP = ALL)

    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

    8.    Same in mirror too only Listener port is different. i.e 5022      

    9.    When I issue this command from mirror sys. its working fine

    ALTER DATABASE RajDB

    SET PARTNER ='TCP://PC32.MYDOMAIN.SOFT:7023'

    but from principal sys.  

    ALTER DATABASE RajDB

    SET PARTNER ='TCP://U15349479.MYDOMAIN.SOFT:5022'

    then its throwing an error -- L

    Msg 1418, Level 16, State 1, Line 1

    The server network address "TCP://U15349479.MYDOMAIN.SOFT: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.

    In error log - Database mirroring has been terminated for database 'RajDB'. This is an informational message only. No user action is required.

     

    Please help me to completing this task, working on this from past 1 month. Even I have gone though all the post but no solution.

    I am looking ahead of your valuable supports.

    Thanks,

    Vincent

     

     


    Thanks & regards, Vincent
    Wednesday, February 16, 2011 2:29 PM
  • hi ,

     

    Please tell me where should i add this C:\Windows\system32\drivers\etc line of link.

    Please guide me.

     

    Regards,

    Vincent

     


    Thanks & regards, Vincent
    Wednesday, February 16, 2011 2:35 PM
  • Hi John,

    I have followed all the steps from Randall Neth all the steps worked fine but got an error while executing following command.

    ---- ON PRINCIPAL SERVER PRIMARY
    ALTER DATABASE dbname
        SET PARTNER = 'TCP://MIRRORSERVERNAME.DOMAIN.com:7025';
    GO

    Please let me know what addition steps or any checks you have done.

    Regards,

    Vincent


    Thanks & regards, Vincent
    Wednesday, February 16, 2011 2:40 PM
  • Vincent,

    Verify the port number is correct:

    SELECT type_desc, port FROM sys.tcp_endpoints;
    GO

    Verify the endpoints are started:

    SELECT state_desc FROM sys.database_mirroring_endpoints

    Verify the port availability (run command on mirror):

    netstat -an | find ":5022"

    Verify the login has the correct permissions:

    SELECT 'Metadata Check';
    SELECT EP.name, SP.STATE,
       CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
          AS GRANTOR,
       SP.TYPE AS PERMISSION,
       CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
          AS GRANTEE
       FROM sys.server_permissions SP , sys.endpoints EP
       WHERE SP.major_id = EP.endpoint_id
       ORDER BY Permission,grantor, grantee;
    GO

    Post the output here and I'll look at it.

     


    MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    Wednesday, February 16, 2011 9:01 PM
  •   Randall,

    Thanks for your reply.

    I have checked the following points:

    1.    Verify the port number is correct: Correct

    2.    Verify the endpoints are started: Started

    3.    Verify the port availability (run command on mirror): OUTPUT

    TCP   0.0.0.0:7025                 0.0.0.0:0                   LISTENING

    TCP   192.168.0.155:7025     192.168.0.32:1500   ESTABLISED

    TCP    [::]:7025                        [::]:0                          LISTENING

    4.    Verify the login has the correct permissions:

    Here I am using 3 logins

               1.  sa login on both mirror & principal m/c

               2.  GLOBAL\Vincent  login on both mirror & principal m/c

               3.  Then  MIRROR_login & PRIMARY_login , mirror & principal  

                    m/c respectively  - this login I have created by seeing your post 

                    with certificate.

    FYI, even I logged in sql server using this two login then I tried executing

              ALTER DATABASE RajDB

        SET PARTNER = 'TCP:// U15349479. MYDOMAIN.SOFT.com:7024'; Command from mirror system with other port it worked fine but problem in principal system … same error

    The server network address "TCP://U15349479.MYDOMAIN.SOFT:5022" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

     

     

    i have tried all the possible way , still in the same place... LLLL

     

    Please guide me .....

    Regards,

    Vincent

     


    Thanks & regards, Vincent
    Thursday, February 17, 2011 6:46 AM
  • Vincent,

    What port is the mirror listening on in #1 above, you state that it is 'correct'.  The reason I ask is that your ALTER DATABASE statement is specifying port 5022:

    SELECT type_desc, port FROM sys.tcp_endpoints;
    GO

    1.    Verify the port number is correct: Correct

    Whereas you NETSTAT outpout does not show port 5022 listening:

    TCP   0.0.0.0:7025                 0.0.0.0:0                   LISTENING

    TCP   192.168.0.155:7025     192.168.0.32:1500   ESTABLISED

    TCP    [::]:7025                        [::]:0                          LISTENING

    It shows port 7025 which is correct if you did not modify the script above:

    ---- ON MIRROR SERVER - MIRROR
    USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword1';
    GO
    CREATE CERTIFICATE MIRROR_cert
       WITH SUBJECT = 'MIRROR certificate for database mirroring';
    GO
    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7025
          , LISTENER_IP = ALL
       )

    ---- ON PRINCIPAL SERVER PRIMARY
    ALTER DATABASE dbname
        SET PARTNER = 'TCP://MIRRORSERVERNAME.DOMAIN.com:7025';
    GO

     

    It would be easier to troubleshoot if you post the actual output from the commands from the last post.


     



    MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    Thursday, February 17, 2011 2:33 PM
  • Hi Randall,

    Thanks for your reply.

    Sorry I forgot to specify the change the update port no i.e 7025.

    I check the both port is started & LISTENING.

    Following output is from mirror system.

    TCP   0.0.0.0:7025                 0.0.0.0:0                   LISTENING

    TCP   192.168.0.155:7025     192.168.0.32:1500   ESTABLISED

    TCP    [::]:7025                        [::]:0                          LISTENING

    But I did not get ESTABLISED from principal system. Only listening.

     

    ALTER DATABASE RajDB

    SET PARTNER = 'TCP:// U15349479. MYDOMAIN.SOFT.com:7024';

    ERROR : The server network address "TCP://U15349479.MYDOMAIN.SOFT:5024" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

     

    I am looking ahead of your valuable solution

     

    Regards,

    Vincent


    Thanks & regards, Vincent
    Thursday, February 17, 2011 2:52 PM
  • Vincent,

    Can you ping the mirror from the primary?

    Is the Windows Firewall on and blocking anything?

    Verify that you can telnet to port 5022 on the mirror server.

    Verify that when restoring the database you have specified "WITH NO RECOVERY".

    Backup the log again and restore to mirror.

    ---- TRANSACTION LOG BACKUP
    USE dbname;
    GO
    BACKUP LOG dbname
    TO DISK = 'C:\CERTS\dbname_Log.trn'
       WITH FORMAT,
          MEDIANAME = 'dbnameBackups',
          NAME = 'Log Backup of dbname';
    GO


    ---- TRANSACTION LOG BACKUP RESTORE - WITH NORECOVERY
    RESTORE LOG dbname
        FROM DISK = 'C:\CERTS\dbname_Log.trn'
        WITH NORECOVERY
          MOVE 'dbname_Data' TO
             'D:\Data\dbname_Data.mdf',
          MOVE 'dbname_Log' TO
             'D:\Data\dbname_Log.ldf';
    GO

     


    MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    Thursday, February 17, 2011 3:36 PM