Question About Database Mirroring

Question

Post reply

Question About Database Mirroring

Athanasios Tsioplakis

SSC Enthusiast

Points: 124
More actions
December 5, 2006 at 2:09 pm

#115588

I would like to know if anyone has had any success mirror SQL Server 2005. I am able to mirror it to another Enterprise SQL Server 2005 database, but I have yet to implement a witness. Microsoft suggested that I use SQL Server Express as the witness. I have setup the witness but I can't seem to get the three different computer to come online. What I mean is when I have a principal, mirror, and witness. The principal who initates the mirroring process can't seem to bring the witness online. Has anyone had any luck with this ? As far as I can tell they all have the same securtiy model as the principal, so I do not believe it is that.

I hope someone can give me a clue on this one.
-Thanas

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

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

I did but I used Principal and Witness as one server and Mirror as another server in my test environment...

What do you mean by Pricipa can't bring Witness online?

Witness is always online to check the status of Principal.

Is it sync or async mirroring?

Async mirroing you can't make use of auto failover.

MohammedU
Microsoft SQL Server MVP

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

Please post the exact error. i have many databases mirrored.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Athanasios Tsioplakis SSC Enthusiast Points: 124 More actions · Answer 3

This is the error message that I am getting know. I distinctly remeber it being something different, but let's just deal with what I have in front of me.

TITLE: Database Properties

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

An error occurred while starting mirroring.

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

ADDITIONAL INFORMATION:

Alter failed for Database 'DiXXXXXXXXXXXX2XXXXXXXXXXXXXDev'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

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

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

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

The ALTER DATABASE command could not be sent to the remote server instance 'TCP://QAXXXXXX-XXX.XXXX.XXX:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)

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

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

BUTTONS:

OK

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

Athanasios Tsioplakis SSC Enthusiast Points: 124 More actions · Answer 4

Has anyone been able to figure this out? The strange thing is that I can get the mirror up without the witness, but I like that witness would keep an eye out on both Servers and help in the automatic roll over if the principal fails. Does anyone have any ideas why I can't seem to get the witness to come oneline as a witness in this design.

thanks, for all you help

-Thanas

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

Has this server been the witness previously? And has there been any server changes?

If you remove the witness, it retains the configuration settings. If you reconnect, it over-writes the previous settings with the new. I have heard that there is a problem with trying to reconnect to the same server if something has changed such as the IP address of the server. The witness will refuse the connection because it has an entry in it's catalog for the server but it doesn't match up right. My guess is that MS would rather have the server refuse the connection than to connect to the wrong server and appear to be functioning correctly if it is not.

Last I heard, they did not have a way to clear out the witness catalog data. I know of at least one person that ended up uninstalling and reinstalling SQL to get it working again. I have also heard that restoring a backup of the msdb database taken from before it was initialy a witness works as well.

I have others say that they resolved this issue by using the IP address instead of the fully qualified name, while others say the reverse is true. My guess is that maybe by switching the IP/qualified name, the witness saw it as a different server and therefore allowed the connection.

That last part is probably the least painful thing to try.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Athanasios Tsioplakis SSC Enthusiast Points: 124 More actions · Answer 6

Okay I tried all your suggestions. With no luck. Let me ask a stupid question. I was told by MS Tech that I can use a SQL Server Express 2005 as a witness on a seperate server. We didn't want to buy two licenses just to have it sit there and not do anything. That is exactly what I have been trying to do for a while now. For whatever reason I get the above error. I tried the ip address the full qualified name I even uninstalled and reinstalled, without any success. So the question is, can a SQL Server Express instance act as a Witness, as told by Microsoft Tech?

Oh and to just to add a second kink to this problem, I am able to make another server a witness, but that server has a Entriprise Evaluation Edition of SQL Server 2005. Something funny is going on here.

thanks

-Thanas

Robert Davis One Orange Chip Points: 28027 More actions · Answer 7

No, the MS tech was correct. SQL Server express can be the witness. I'm not using it as a witness in my environment, but many people are. What I am doing is having servers double up as both a witness and a mirror server.

For example, consider I had 4 servers: SQL1, SQL2, SQL3, and SQL4. SQL1 and SQL2 have live databases on them. SQL3 is the mirror for the SQL1 databases and the witness for the SQL2 databases. SQL4 is the mirror for SQL2 and the witness for SQL1.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Athanasios Tsioplakis SSC Enthusiast Points: 124 More actions · Answer 8

Then I am official stumped once again. I don't know if this would help, but I usual try to work from the principal server server. In other words, I use management studio on that studio. I register the Mirror and and the witness. I notice that there is like a play symbol next to the Principal and the mirror, but not next to the witness. Does this give you a clue on what could be wrong?

-Thanas

Robert Davis One Orange Chip Points: 28027 More actions · Answer 9

Where are you seeing this? In the Management Studio? The little server in the registered servers with the green circle and a while triangle means that it is running. If it has a red circle with a white square, it's not running. If it has an empty white circle, then the Management Studio does not have any information about it's running state.

Can you connect ot it? If not, make sure that it is running. If you can connect to it, make sure that the SQL Agent is running (should be set to start automatically.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Athanasios Tsioplakis SSC Enthusiast Points: 124 More actions · Answer 10

I seem to have a white circle in Management Studio. I also check to see if that installation has SQL Agent and it does not. Do you know if SQL Server Express has SQL Server Agent?

-Thanas

Robert Davis One Orange Chip Points: 28027 More actions · Answer 11

To be honest, i've never used SQL Server Express. I would assume that it has it, but I can't be sure.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

MohammedU SSCoach Points: 19644 More actions · Answer 12

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp

The following features are not available...

* Availability features such as data mirroring, clustering, etc.

* Full-text search

* SQL Agent

* Reporting Services

* Business Intelligence Platform, such as Notification and Analysis

Services

MohammedU
Microsoft SQL Server MVP

Robert Davis One Orange Chip Points: 28027 More actions · Answer 13

SQL Agent must not be needed for this then.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Athanasios Tsioplakis SSC Enthusiast Points: 124 More actions · Answer 14

It still doesn't explain why I can't seem to get SQL Server Express to be a witness. And the whole white circle thing is wierd too. I just don't get it. I should not be this hard to get a witness up.