SQL mirroring partners

select db_name(database_id),mirroring_partner_instance, mirroring_witness_name from sys.database_mirroring where mirroring_guid is not null

Advertisements

setup mirroring databases on SQL 2005

Problem: Not able to set up database mirroring through management studio

Getting error 1418
Resolution:
Changed  SQL startup account from Local system to Domain account
for Principal, Mirror ,Witness

Tried to set up mirroring through management studio

Ran the following command on Principal and Mirror:

ALTER ENDPOINT Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (
AUTHENTICATION = WINDOWS NTLM,
ENCRYPTION = DISABLED,
ROLE=PARTNER)

Now Its failing with Error 1456
Provided sysadmin privileges to domain user on principal ,Mirror, Witness.

But still getting the same error

Ran the following command on witness also:

ALTER ENDPOINT Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (
AUTHENTICATION = WINDOWS NTLM,
ENCRYPTION = DISABLED,
ROLE=PARTNER)

(for SQL exspress:

ALTER ENDPOINT MirroringSTATE = STARTEDAS TCP (LISTENER_PORT = 5022)FOR DATABASE_MIRRORING (AUTHENTICATION = WINDOWS NTLM,ENCRYPTION = DISABLED,ROLE=WITNESS)     )

Setup Database Mirroring successfully.

 

-- Grant the user access to the endpoint 
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLSrv2_mirroring_login]
GO