mirror setup failed, but cannot delete mirror db

Question

mirror setup failed, but cannot delete mirror db

winston Smith

SSCoach

Points: 19498
More actions
January 8, 2012 at 5:48 am

#251075

I tried to set up mirroring. I restored my backup to the mirror and left it in a NO RECOVERY state. I then went through the mirroring wizard.
i got an error message stating: The server network address “TCP://SQLServer:5023″ cannot be reached or does not exist.
Im now trying to start from scratch.
When i try to drop the database that i restored to the mirror i get the error:
The database 'dbName' is enabled for database mirroring. Database mirroring must be removed before you drop the database. (.Net SqlClient Data Provider)
I have removed the mirror doing the following:
Alter database 'name' remove partner.
Out of curosity, anyone know why this happens?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The mirroring probably partially succeeded, it is several steps to configure and can fail on any of them, so it wasn't successfully mirroring, but knew that it was part of a mirroring setup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 233913 More actions · Answer 2

winston Smith (1/8/2012)
I tried to set up mirroring. I restored my backup to the mirror and left it in a NO RECOVERY state.

You must take and restore at least one log backup too.

First check the result of this query executed against the principal instance

select * from sys.database_mirroring

where database_id = DB_ID('yourprincipaldb')

winston Smith (1/8/2012)
I have removed the mirror doing the following:
Alter database 'name' remove partner.

Remove the mirror session using the following

ALTER DATABASE Mybdname SET PARTNER OFF

[/CODE]

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

"Ya can't make an omelette without breaking just a few eggs" 😉

winston Smith SSCoach Points: 19498 More actions · Answer 3

thanks guys. Mirroring still wont work on my lab (laptop actually).

Setup:

Instance1 (principal)

Instance2(mirror)

Steps:

1. Full Backup of Testdb on principal

2. Restore backup of Testdb to mirror with norecovery

3. TranLog backup of TestDb on principal

4. Restore tranlog backup to TestDB on mirror, with norecovery

5. on instance1, go to properties of Testdb, select Mirroring and click Configure Security.

6. set instance1 as principal on port 5023

7. set instance2 as mirror on port 5024

8. mirroring sets up successfully.

9. click "start mirroring". get the error:

The server network address "TCP://my-PC:5024" 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. (Microsoft SQL Server, Error: 1418)

To Debug:

1. Disabled firewall.

2. Checked ports were open via telnet.

3. ensured sql account on each server has access to the other server.

-actually changed sql services on both instances to run under account with SA Privileges, just to remove any security issues to get mirroring working initially. didnt work.

4. Checked endpoints are started. both are.

Any hunches what might be going wrong here? Ive set up mirroring a bunch of times before and never encountered this, or at least resolved it via a bit of firewall configuration.

Perry Whittle SSC Guru Points: 233913 More actions · Answer 4

Is your laptop part of a windows domain. You need to be able to provide and resolve FQDNs?

To setup mirroring in a workgroup you need to take a different approach to the setup

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

"Ya can't make an omelette without breaking just a few eggs" 😉

winston Smith SSCoach Points: 19498 More actions · Answer 5

Perry Whittle (1/8/2012)
Is your laptop part of a windows domain. You need to be able to provide and resolve FQDNs?
To setup mirroring in a workgroup you need to take a different approach to the setup

No, its just part of a workgroup. any logins originate on my laptop. sql accounts operate under myPC\sqlAccount service accounts.

I tried specifying the accounts and then just as a shot from the hip, setting up mirroring without specifying the accounts. same result either way.

Perry Whittle SSC Guru Points: 233913 More actions · Answer 6

To set up mirroring in a workgroup see the following link

http://blogs.msdn.com/b/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

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

"Ya can't make an omelette without breaking just a few eggs" 😉

padiamon SSC Enthusiast Points: 108 More actions · Answer 7

For me it was because of firewall udp 137 port which was not allowed.

This port is for network discovery so it is essential for endpoints to be reached successfully.

Hope this helps you, because I was stuck at it for many days with no solution.