Database mirroring connectivity Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Database mirroring connectivity Error

Author

Topic

thulasiram
Starting Member

1 Post

Posted - 2012-10-05 : 03:20:35

i have checked in local area network mirroring it worked fine but i have two server in static ip it does not having domain Name .i gave the ip and port number in the configuring security for both principal and mirror server and also i created certificate for both server when i use cmd after creating certificate in mirror server

ALTER DATABASE MirrorDB SET PARTNER = 'TCP://IP:5022'; GO

it will show error as ERROR:Neither the partner nor the witness server instance for database "MirrorDB" is available. Reissue the command when at least one of the instances becomes available.

then i have set partner off in mirrorDB ALTER DATABASE MirrorDB SET PARTNER OFF

then i have run query ALTER DATABASE MirrorDB SET PARTNER = 'TCP://IP:5022'; GO

it executed successfully and then i try to execute in principal server ALTER DATABASE MirrorDB SET PARTNER = 'TCP://principal serverip:5022'; GO

it will show error as ERROR:The mirror database, "MirrorDB", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.

whats the problem can any one help me to synchronize the mirroring please?

vandrebecq
Starting Member

2 Posts

Posted - 2012-10-09 : 12:53:30

hello
- -1 :o) in 2008 you need to grant connect on Endpoint
'GRANT CONNECT ON ENDPOINT::YOUR END POINT NAME TO public' (not secure to public)
-0 create on controle miroring endpoint on both server
sample
CREATE ENDPOINT Endpoint_Mirroring
AUTHORIZATION sa
STATE=STARTED
AS TCP (LISTENER_PORT=7069)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
Go

-1 alter database primary set RECOVERY FULL
ALTER DATABASE [my_db] SET RECOVERY FULL WITH NO_WAIT;
-2 backup full database on primary db
Backup DATABASE [my_db] TO DISK = '\\XXX\my_db.bak' WITH FORMAT,COMPRESSION ;
-3 backup transaction log on primary db
BACKUP LOG [my_db] TO DISK = '\\XXX\my_db.trn' WITH FORMAT,COMPRESSION ;
-4 restore with no recovery backup on secondary db
RESTORE DATABASE [my_db] FROM DISK = '\\XXX\my_db.bak' WITH REPLACE, NORECOVERY ;
-5 restore transaction log with no recovery on secondary db
RESTORE LOG [my_db] FROM DISK = '\\XXX\my_db.trn' WITH FILE=1, NORECOVERY;
-6 alter secondary database 'set partner' to primary (use the correct endpoint tcp port)
ALTER DATABASE [my_db] SET PARTNER ='TCP://PRIMARY:TCP PORT'
-7 alter primary database 'set partner' to secondary (use the correct endpoint tcp port)
ALTER DATABASE [my_db] SET PARTNER ='TCP://SECONDARY:TCP PORT'
-8 don't forget to create backup tran

regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources