February 10, 2010 at 9:01 am
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
February 10, 2010 at 9:08 am
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.
February 10, 2010 at 9:10 am
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.
February 10, 2010 at 9:14 am
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 "
February 10, 2010 at 9:20 am
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
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.
February 10, 2010 at 9:31 am
Correct.
If we have SP2 installed we dont require enabling trace flag 1400....
Cheers,
- Win.
" Have a great day "
February 10, 2010 at 9:33 am
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
February 10, 2010 at 9:40 am
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.
---------------------------------------------------------------------
February 10, 2010 at 9:45 am
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.
February 10, 2010 at 9:46 am
Also, quick question, are the SQL Server instances running under a domain account?
February 10, 2010 at 10:33 am
Yes Both of them are on Same server and same Domain Control
February 10, 2010 at 10:41 am
Yes..They are on the same server and same domain
February 10, 2010 at 10:49 am
Have you started looking at CREATE ENDPOINT?
Are the two instances running under a domain account?
February 10, 2010 at 12:49 pm
Lynn is asking about the service accounts, not the host computer. Are you using domain accounts.
February 11, 2010 at 4:41 pm
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