problem with database mirroring

Question

Post reply

problem with database mirroring

mah_j

SSCrazy

Points: 2931
More actions
June 9, 2012 at 7:44 am

#257426

HI every one.

Any help would be appreciated.

i configured a test database mirroring

i have 3 instance in one server(Enterprise edition)

MSSQLSERVER principal

MSSQLSERVER2 mirror

MSSQLSERVER3 witness

it was all ok with principal and mirror server but when i want to add witness

i get this:

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

i have windows workgroup and all of my instances services log on as local system

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

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

Perry Whittle SSC Guru Points: 233792 More actions · Answer 1

Perry Whittle

SSC Guru

Points: 233792

June 10, 2012 at 9:14 am

#1499090

can you provide a little more detail on your setup?

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

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

Thomas Stringer SSCarpal Tunnel Points: 4038 More actions · Answer 2

Thomas Stringer

SSCarpal Tunnel

Points: 4038

June 10, 2012 at 11:18 am

#1499100

Is that port (5024) blocked?

Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com

Perry Whittle SSC Guru Points: 233792 More actions · Answer 3

Thomas Stringer (6/10/2012)

Is that port (5024) blocked?

On the same server, no.

But it could be in use by either the principal or mirror instance.

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

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

mah_j SSCrazy Points: 2931 More actions · Answer 4

In my test server I installed 3 instances (SQLSERVER Interprise Edition 2008R2 _RTM):

MSSQLSERVER is my principal,its role in sys.database_mirroring_endpoints is PARTNER with the state STARTED and ENCRYPTION enabled.i have windows workgroup so my server network address is TCP://servername:5022

MSSQLSERVER2 is my mirror,its role in sys.database_mirroring_endpoints is PARTNER with the state STARTED with the state STARTED and ENCRYPTION enabled.and server network address is TCP://servername:5023

MSSQLSERVER3 is my witness,its role in sys.database_mirroring_endpoints is

WITNESS with the state STARTED and ENCRYPTION enabled.and server network address is TCP://servername:5024

I restor a full backup and a log with NORECOVERY on mirror instance and named the same as database name in principal.

I turned off the windows firewall and antivirus firewall and checked the port 5024 ,its mode is listening,the other two port(5022,5023) are Established and listening.

I drop all the endpoint and create them agin.

I use windows authentication for configuring mirroring and create endpoint on each instance like below

--DROP ENDPOINT mirroring

CREATE ENDPOINT mirroring

AUTHORIZATION [servername\user]

STATE=STARTED

AS TCP ( LISTENER_PORT = 5024 )

FOR DATABASE_MIRRORING

(

ROLE = WITNESS,--PARTNER

ENCRYPTION = REQUIRED,

AUTHENTICATION = WINDOWS NEGOTIATE

)

i also try this with sa (sysadmin) and another user that i created with sysadmin permission.

i run this:

GRANT CONNECT ON ENDPOINT::Mirroring TO sa --or [servername\user]

and get :Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

but when i add witness server with the address TCP://servername:5024 and click start mirroring or run the alter database get that error again.

ALTER DATABASE databasename SET WITNESS = 'TCP://servername:5024'

i dont know if i can configure database mirroring witness with 3 instance all in one server?

By the way i replaced the witness and mirror(i changed the role in instanc with port 504 to PARTNER),i mean i use the TCP://servername:5024 for mirror and TCP://servername:5022 for prinicipal without any witness but i got this:

The server network address "TCP://servername: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)