June 9, 2012 at 7:44 am
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
June 10, 2012 at 9:14 am
can you provide a little more detail on your setup?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 10, 2012 at 2:49 pm
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" 😉
June 10, 2012 at 10:58 pm
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)
June 10, 2012 at 11:02 pm
also because i use windows workgroup my services in sql (for 3 instances_SQL Server (MSSQLSERVER) ) log on as local system
June 10, 2012 at 11:31 pm
After all of these ,finally i found the problem
I drop the port 5024 and create the new witness endpoint and set LISTENER_PORT =5025
June 11, 2012 at 1:50 am
Hey Dude,
As you are using Windows workgroup you would not have fully qualified server name.
So when it ask start mirroing then on that pop up press "DO NOT START MIRRORING" and change ServerName with Server IP
ZIshan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply