SQL 2005 Database Mirroring Issue‏

  • I am trying for Sql Mirroring on Sql 2005 server for one test database and I am facing issue since past 3days.

    I am not knowing the way to fix and work further.

    Hope you can help me

    Here are the steps

    1> Created a database named as [test_SqlMirror] on Sql 2005 Server instance

    2> ALTER DATABASE [test_SqlMirror] SET RECOVERY FULL

    3> BACKUP DATABASE [test_SqlMirror]

    TO DISK = N'D:\Data\test_SqlMirror\test_SqlMirror.bak'

    4>Now connected to another Sql Instance on the Same server.

    5>Executed the the following Query on Master db

    RESTORE DATABASE [test_SqlMirror] FROM

    DISK = N'D:\Data\test_SqlMirror\test_SqlMirror.bak'

    WITH

    MOVE N'test_SqlMirror' TO N'D:\Data.Thirdwave\test_SqlMirror\test_SqlMirror.mdf',

    MOVE N'test_SqlMirror_log' TO N'D:\Data.Thirdwave\test_SqlMirror\test_SqlMirror.ldf',

    NORECOVERY

    6> DB is created but it showing as follow test_SqlMirror(Restoring...), with a Green Up arrow.

    7> It is showing the same for more than 2days no matter what ever I do

    8> I tried checking the properties and it displaying the following error message

    TITLE: Microsoft SQL Server Management Studio

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

    Cannot show requested dialog.

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

    ADDITIONAL INFORMATION:

    Cannot show requested dialog. (SqlMgmt)

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

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

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

    Database 'test_SqlMirror' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

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

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

    BUTTONS:

    OK

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

    9> I also tried the approach mentioned in the following URL

    http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004_mem1,00.html

    Still it did not work

    10> I took the full backup and then the trasactional back up from Principle server and tried restoring them one after the other on Mirror in noRecovery mode, still it failed

    Can you help me the way in which I can make it working

    Please tell where I am wrong

  • Have you created the database mirroring endpoints on the two servers?

    Are you using NT Authentication or Certificates for the connection between the two servers?

    Have you granted CONNECT permissions as needed to the database mirroring endpoints?

    Have you ALTERed the databases to point the databases to their respective partners?

    All I can see that you have done is restore the database to the secondary server. What you have done in that regard is correct, Full Backup + Transaction Log.

  • And yes, I am currently in the process of setting up database mirroring myself. There is actually quite a bit to do to get it up and running.

    You should do some more reading in Books Online. As you work through the reading, ask questions. There are some other gotcha's that aren't readily apparent in the BOL documentation or else I just missed it.

  • You have to follow sequence in configuring the SQL Server Database Mirroring.

    Full backup,

    Trans backup,

    Restore full backup on secondary with NORECOVERY,

    Rectore Trans log backup on secondary with NORECOVERY,

    Enable Trace flag ;-T1400,

    end points,

    configure.....

    Hope these helps:

    http://www.sqlserver.in/index.php/administration/48-database-mirroring/97-database-mirroring.html

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

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

    Cheers,
    - Win.

    " Have a great day "

  • winslet (2/10/2010)


    You have to follow sequence in configuring the SQL Server Database Mirroring.

    Full backup,

    Trans backup,

    Restore full backup on secondary with NORECOVERY,

    Rectore Trans log backup on secondary with NORECOVERY,

    Enable Trace flag ;-T1400,

    end points,

    configure.....

    Hope these helps:

    http://www.sqlserver.in/index.php/administration/48-database-mirroring/97-database-mirroring.html

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

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

    I have database mirroring up and working, and did not need to enable trace flag 1400. I believe this was only needed when SQL Server 2005 was first released. It was activated in SP 1, iirc.

  • Correct.

    If we have SP2 installed we dont require enabling trace flag 1400....

    Cheers,
    - Win.

    " Have a great day "

  • Q-1>Have you created the database mirroring endpoints on the two servers?

    Ans>No I did not, do I need to ?

    Q-2>Are you using NT Authentication or Certificates for the connection between the two servers?

    Ans> Yes, NT Authentication on both the instances on the same server

    Q-3>Have you granted CONNECT permissions as needed to the database mirroring endpoints?

    Ans> I have no clue about this, can you assist me.

    Q-4>Have you ALTERed the databases to point the databases to their respective partners?

    Ans>I am not clear with this question.

    I am doing this porcess for the first time and I tried following steps from

    http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004_mem1,00.html

    but it did not work

  • right click on the primary database, select properties, select database mirroring, follow the steps through the wizard.

    A witness is not a requirement, up to you whether you require high availability mode.

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

  • george sibbald (2/10/2010)


    right click on the primary database, select properties, select database mirroring, follow the steps through the wizard.

    A witness is not a requirement, up to you whether you require high availability mode.

    That is true, you could use the wizard. Only problem I have with that is you don't learn what needs to be done.

    Since you are setting up a mirror between to instances on the same server, the first thing you need to do is setup the endpoints for each instance. Also, since you are the same server, each instance will need to use a different tcp port.

    Look up CREATE ENDPOINT in books online. Post back the SQL you think you need.

  • Also, quick question, are the SQL Server instances running under a domain account?

  • Yes Both of them are on Same server and same Domain Control

  • Yes..They are on the same server and same domain

  • Have you started looking at CREATE ENDPOINT?

    Are the two instances running under a domain account?

  • Lynn is asking about the service accounts, not the host computer. Are you using domain accounts.

  • Hi

    Thanks for all the help I was able to make it work with Same Domain

    Now I am trying between two domains

    and I am getting the following error msg

    TITLE: Database Properties

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

    One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click Start Mirroring again.

    The syntax for a fully-qualified TCP address is:

    TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>

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

    BUTTONS:

    OK

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

    Can anyone suggest me the way to fix this issue

    Both the db server are in different networks

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

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