Data base mirroring with Replication

Question

Data base mirroring with Replication

dakshinamurthy-655138

SSCertifiable

Points: 5172
More actions
August 21, 2009 at 5:07 am

#225167

Firstly i would like to say sorry, i had replied to one of the forums in the same sql server central report seeking the help, was not having any luck to get the reply. So i am putting the request seeking help here.
I am HAving SQL server 2005
Even i would like to have the Hot fix downloaded, i am not getting the link from where to download.
In My scenario, i had a Server A as Principal with DB1 and Server B as Mirror With DB1 acting as Mirror and DB1_Sub in server B for Replication to happen. I am not having the witness.
I configured Mirroring from Server A to Server B.I had transactional Replication from DB1 of Server A which is also Principal with the DB1_Sub which acts a subscriber db in Server B.
Once a Failover is made, failover happens but the Changes done to DB1 in Server B which acts as Principal once the failover happens is not replicating to DB1_Sub DB which is in Server B.
I have also exectue the command
exec sp_add_agent_parameter
@profile_id = 6, @parameter_name = N'-PublisherFailoverPartner',
@parameter_value = N'DB NAme'
exec sp_add_agent_parameter
@profile_id = 1, @parameter_name = N'-PublisherFailoverPartner',
@parameter_value = N'DB Name'
how do i fix this issue pls let me know.
With Regards
Dakshina Murthy

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

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

Lempster SSCoach Points: 15651 More actions · Answer 1

Why do you want to do this - mirroring and replicating between the same servers? Is it so that you can run reports against the second database on the Mirror server? If so, why not take a snapshot of the mirror database and report against that?

Regards

Lempster

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 2

Hi,

Thanks for the reply.

To Provide High Availability of the Database. We thought of establishing this aspect.

I was refering the Document from net with the name ReplicationAndDBM

With Regards

Dakshina Murthy

Lempster SSCoach Points: 15651 More actions · Answer 3

OK, but that document refers to Subscribers on a separate server. The configuration that you have (if I understand correctly) is that your subscriber is also your mirror, so I still don't see why you would want to both mirror and replicate to the same server..?:unsure:

Regardless, let's look at why replication is not working after a failover; are you using a Local or Remote Distributor? As per the document, a local Distributor does not support mirroring.

Lempster

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 4

Hi Sir,

Thanks for the support and help you are providing, i am having the distributor on my Server B, distributor is not in a separate server. i have configured the distributor in either of ServerA or ServerB, So as per the document we need to have a separate server from Principal , mirror. So can we configure the distributor DB on witness server. If so will it work. Do i have commited any mistake in this command

exec sp_add_agent_parameter

@profile_id = 6, @parameter_name = N'-PublisherFailoverPartner',

@parameter_value = N'dakshinamurthy\sql2005'

is the above statement is correct, please let me know. In the document i also read that if we are using SQL server 2005 we need to have an hotfix, i have downloaded the hotfix the .exe is of 101 MB, after installing the hotfix i have the following when i execute select @@version

With Regards

Dakshina Murthy

Lempster SSCoach Points: 15651 More actions · Answer 5

Using a Witness server as a remote Distributor is not something I've ever tried, but in theory it should work. However, if you lose that server, both your HA strategies could be wiped out (depending on whether the Principal and Mirror servers can form a partner-to-partner quorum or not)...which is the exact opposite of what you are trying to achieve.

The safest (but more expensive) option would be to use separate servers for the Witness and the Distributor.

I would certainly test the various scenarios before implementing on Production! 😉

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 6

Hi sir,

Thats right, need to check this several times before implementing into the production. So as you said expensive but safer to have separate servers for witness and distributor.

Once again thanks a lot, if you find any time to work on this please let me know.

With Regards

Dakshina Murthy