locked
Could not locate entry in sysdatabases for database RRS feed

  • Question

  • Hi Gurus,

    I am facing below problem while configuring mirroring between 2 machines in same domain.

    I am doing below steps.

    1)       Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

    2)       MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

    3)       MRRUSR is the MSSQLSERVER service account in both machines

    4)       Machine A is gonna be the PRINCIPAL, B the MIRROR.

    5)       Log on in my machine as MRRUSR

    6)       Open Sql server Studio. Connect to both machines using windows authenticacion.

    7)       Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

    8)       Backup the new DB (Full backup) with .bak extension

    9)       Backup the new DB (Transaction log) with .trn extension

    10)   Copy the both files to a location in B machine.

    11)   Restore .bak into a new DB using NORECOVERY option

    12)   Restore .trn into the previous DB using NORECOVERY option

    13)   Go to Machine A, open a new query

    14)   Take a look to the endpoints

    SELECT type_desc, port FROM sys.tcp_endpoints;
    SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

     

    15)   Delete existing ones (DROP ENDPOINT [NAME])

    16)   Create a new endopoint

    CREATE ENDPOINT [Mirroring]
    AS TCP (LISTENER_PORT = 5023)
    FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

     

    17)   Start it

    ALTER ENDPOINT [Mirroring]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5023)
    FOR database_mirroring (ROLE = PARTNER);
    GO

    At these stage i am getting following error.

    "Msg 911, Level 16, State 1, Line 1

    Could not locate entry in sysdatabases for database 'NAME'. No entry found with that name. Make sure that the name is entered correctly."

    any help appreciated.

     

    regards

    Vijay

     

     

     

     

    Monday, August 23, 2010 12:08 PM

Answers

  • Hi sudeepta,

    I have resolved the issue.I removed both the instance from both the nodes.Reinstalled the same,while installing and creating instances i selected domain user option instead of local id in both instances.And that domain user must be same in both the installations .Secondly dns server ip in principal and mirror should be same when seeing thru ICCONFIG/ALL.

    Regards

    Vijay

     

    • Marked as answer by YoYo Yu Monday, August 30, 2010 5:54 AM
    Tuesday, August 24, 2010 12:07 PM
  • Glad to know that your issue got fixed.
    SKG: Please Marked as Answered, if it resolves your issue.
    • Marked as answer by vijay salian Monday, August 30, 2010 1:54 PM
    Tuesday, August 24, 2010 1:04 PM

All replies

  • )   In this example we are inside a DOMAIN. We have a domain user called MRRUSR 

    1)       Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

    2)       MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

    3)       MRRUSR is the MSSQLSERVER service account in both machines

    4)       Machine A is gonna be the PRINCIPAL, B the MIRROR.

    5)       Log on in my machine as MRRUSR

    6)       Open Sql server Studio. Connect to both machines using windows authenticacion.

    7)       Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

    8)       Backup the new DB (Full backup) with .bak extension

    9)       Backup the new DB (Transaction log) with .trn extension

    10)   Copy the both files to a location in B machine.

    11)   Restore .bak into a new DB using NORECOVERY option

    12)   Restore .trn into the previous DB using NORECOVERY option

    13)   Go to Machine A, open a new query

    14)   Take a look to the endpoints

    SELECT type_desc, port FROM sys.tcp_endpoints;
    SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

     

    15)   Delete existing ones (DROP ENDPOINT [NAME])

    16)   Create a new endopoint

    CREATE ENDPOINT [Mirroring]
    AS TCP (LISTENER_PORT = 5023)
    FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

     

    17)   Start it

    ALTER ENDPOINT [Mirroring]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5023)
    FOR database_mirroring (ROLE = PARTNER);
    GO

     

    18)   Do steps 13 to 17 in machine B.

    19)   Return to machine A. Query.

    20)   Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

    ALTER DATABASE NAME
    SET PARTNER ='TCP://YBL402DTP111:5023'

    At these stage i am getting following error.

    "Msg 911, Level 16, State 1, Line 1

    Could not locate entry in sysdatabases for database 'NAME'. No entry found with that name. Make sure that the name is entered correctly."

    any help appreciated.

     

    regards

    Vijay

    Monday, August 23, 2010 12:15 PM
  • ALTER DATABASE NAME SET PARTNER ='TCP://YBL402DTP111:5023'

    Please don't use reserved names for database. In this case you are using "Name". If you still need to use it please use it as [Name]

    Hope this helps.

     




    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Monday, August 23, 2010 3:03 PM
  • Hi,

    My database name is not 'NAME',its vijayprod.

    Do you mean to say my command should be

    ALTER DATABASE Vijayprod SET PARTNER ='TCP://YBL402DTP111:5023'

    above command i used was from one of notes i searched in google.Kindly clarify.

    Regards

    Vijay

     

     

     

    Monday, August 23, 2010 3:18 PM
  • Hi gurus,

    Need your help,

    I have one instance with Vijay and database vijayprod in YBL-402-DTP-109 machine,and second mirroring instance with Vijay and database vijayprod in YBL402DTP111 machine.Now while giving the below command is 1st machine YBL-402-DTP-109

    ALTER DATABASE Vijayprod SET PARTNER ='TCP://YBL402DTP111:5023'

    I get the below error

    Msg 1418, Level 16, State 1, Line 1

    The server network address "TCP://YBL402DTP111.yesbank.com:5023" can not be reached or does not exist. Check the network address name and reissue the command.

    But same command when i fire from 2nd machine YBL402DTP111

    ALTER

     

    DATABASE vijayprod SET PARTNER ='TCP://YBL-402-DTP-109:5023'.This is successul without any errors.

    Need your help.

    regards

    Vijay

     

    Tuesday, August 24, 2010 5:01 AM
  • Hello Vijay,

    Can you ping to both the machines? Look like a DNS related issue, make sure the FQDN is getting resolved without any issue.

    You need to execute the above command on the Mirror instance before you execute it on the Principal instance.

    You may also need to verify whether the SQL Server Database Engine Accounts should have GRANT CONNECT TO permissions on the endpoints.

    Here is a link for a step-by-step guide on how to configure database mirroring?

    Hope, this may help.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, August 24, 2010 7:07 AM
  • Hi Sudeepta,

     

    I have checked the ping status and telnet,its working properly.Will check the ur link,and get back if anything i missed.I checked the error log in principal its shows below error.

    "Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'.  [CLIENT: 10.15.4.88]":

    Regards

    Vijay

    Tuesday, August 24, 2010 7:49 AM
  • Hello Vijay,

    From your first post, on Step-16, while creating the endpoint, you have disabled the encryption in the endpoint definition. Is there any specific reason for doing that?

    As of I remember, normally, database mirroring endpoints use encryption to transport data between the database mirroring session.

    Following is a sample code for creating database mirroring endpoints:

    -- Create Database Mirroring Endpoint on Principal Server
    
    CREATE ENDPOINT [Mirroring]
      AUTHORIZATION [TESTLAB\SQL_D_TestLab1]
      STATE=STARTED
      AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)
      FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
      ENCRYPTION = REQUIRED ALGORITHM RC4);
    GO
    
    If you are testing it in a test environment, then try to enable encryption for the database mirroring endpoints.
    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, August 24, 2010 9:15 AM
  • Hi sudeepta,

    Ya its a test environment,i have dropped the endpoint and again recreated it,in both prinicpal and mirror.But the problem remains the same.


    CREATE ENDPOINT [Mirroring] AUTHORIZATION [yesbank\vsw0000095]
    STATE=STARTED    AS TCP (LISTENER_PORT = 7028, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM RC4)
    ;
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring TO [yesbank\vsw0000095];

    ALTER DATABASE vijayprod SET PARTNER ='TCP://10.15.4.88:7028'

    ----> at this stage  this fails in principal server,in mirroring server its successful.

    "

    Msg 1418, Level 16, State 1, Line 1

    The server network address "TCP://10.15.4.88:7028" can not be reached or does not exist. Check the network address name and reissue the command."

    And in error log i find this error in principal

    "Message
    Database mirroring has been terminated for database 'vijay'. This is an informational message only. No user action is required."

    regards

    Vijay

     

    Tuesday, August 24, 2010 9:35 AM
  • Does the DB engine service account of Principal server has GRANT CONNECT TO permission on the Database Mirroring endpoint on the Mirror Server?

    Just a query, have you restored the database on the Mirror server with NORECOVERY option?


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, August 24, 2010 9:42 AM
  • Hi,

    Yes i have given the following command on prinicpal and on mirror server

    GRANT CONNECT ON ENDPOINT::Mirroring TO [yesbank\vsw0000095];

    Sqlserver and agent services are up using above domain id.

    Yes i have restored using 2nd option norecovery.I have followed ur doc for configuring.

    When i configure mirroring on same machine its sucessful.Problem is only when i use 2 different machines in the same network domain

    Regards

    Vijay

     

    Tuesday, August 24, 2010 9:49 AM
  • Want to re-check once again, are you executing the following command on both the Principal and Mirror instances:

    GRANT CONNECT ON ENDPOINT::Mirroring TO [yesbank\vsw0000095];

    If not, execute the above command on both the instances, then execute the following command(make sure you execute the following comman first on Mirror and then on Primary):

    ALTER DATABASE vijayprod SET PARTNER ='TCP://10.15.4.88:7028'

    Since you are using the same account for both the instances, the configuration should work.

    The only possiblity is, if you execute the GRANT CONNECT command on one instance only.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, August 24, 2010 9:58 AM
  • Hi,

    I have exactly followed the same document which u provided how to configure database mirroring in the same order.No change except the database name.Not able to figure out the problem.

    Regards

    vijay

    Tuesday, August 24, 2010 10:12 AM
  • Can you check in the SQL Server Configuration Manager --> Network Protocols - -> TCP/IP?

    Is TCP/IP is enabled in Configuration Manager for both the instances? Also, you may need to verify on which IP addresses the servers are listening on? You will find the details in Configuration Manager.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, August 24, 2010 10:54 AM
  • Hi,

    Yes, TCP/IP is enabled in Configuration Manager for both the instances

    Kindly clarify what do you mean by  which IP addresses the servers are listening on,so you clarify so that i can check the same,if anythin is wrong

    Vijay

    Tuesday, August 24, 2010 11:15 AM
  • If you open the SQL Server Log using SSMS (SSMS --> Management --> SQL Server Logs). Double click on the latest log and go to the end of the Log file viewer. You will get an message inside SQL Server Log : Server is listening on [xxx.xxx.xxx.xxx <ipv4> 1433].

    The above mentioned IP Address and port number on which your SQL Server is listening on.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, August 24, 2010 11:47 AM
  • Hi sudeepta,

    I have resolved the issue.I removed both the instance from both the nodes.Reinstalled the same,while installing and creating instances i selected domain user option instead of local id in both instances.And that domain user must be same in both the installations .Secondly dns server ip in principal and mirror should be same when seeing thru ICCONFIG/ALL.

    Regards

    Vijay

     

    • Marked as answer by YoYo Yu Monday, August 30, 2010 5:54 AM
    Tuesday, August 24, 2010 12:07 PM
  • Glad to know that your issue got fixed.
    SKG: Please Marked as Answered, if it resolves your issue.
    • Marked as answer by vijay salian Monday, August 30, 2010 1:54 PM
    Tuesday, August 24, 2010 1:04 PM