Replication Failure Warning 1

[Bluegirl]

I wish to set up replication between 2 databases both on the same sql server. I wish to do this so that if the live db fails there is a mirrored version immediately available. ( is Snapshot or Transactional replication best for this I have read up on both but not sure still)

However as the wizard progresses, i am immediately told that the server is not set up as a distributor.and i dont know how to do this.

the warning or error i get is "because sql server agent on databasename uses the system account, sql server replication between servers will fail. to use replication between servers, select another server to be the distributor for bss-sql" I am reluctant to use our file server which doubles up as our exchange box so what option do i have please?

Many Thanks

Bluegirl

 

[MikeTom]

If it is a mirrored database snapshot is best because it includes the table schema.

You need to install publishing on the sql server with the source database. you then have the option of making the same server the distributor or setting up another server as a remote distributor. if you use a remote distributor the remote sever must have publishing installed and a distributor database setup.

by using a remote server all the jobs wich need to be replicated will be sent from the publishing database server to the remote distributor server , where the subscribing servers will look for infomation to be replicated.

hope this helps.

 

[Bluegirl]

Thanks Very Much

I have completed that and in the main replication between the Pub and and the sub exists. However, i just want to ensure that replication is actually working.

There are no objects in the subscriptions directory even though the publication properties shows the subscription database in there as a push subscriber

the Distribution agent reads as Multiple PUblications...is that correct?

Basically, which areas do i need to investigate to ensure that replication is running? Because I ran the snapshot type of replication is the snapshot agent the only agent i need to watch?

Thanks again for your help previously.

Bluegirl

 

[MikeTom]

I may have given you some wrong advice on the type of replication to use. If large amounts of data are being replicated it may be better to use transactional.

Multiple publications i think means you have more than one publication that the agent handles.

The snapshot agent will tell you how many snapshots have been copied to the distribution database. you also have a log reader task wich should run automatically.

You should also have a distribution agent. One for each suscribing database.if you donot have tis agent yet you most probanly havent subscribed to the article yet.

 

[Bluegirl]

Thank you.

i have just finished reading about transactional replication
where it says that if one of the transactions fail to go to the subscriber then none of the changes go at all.

However, snapshot replication which is what I have gone for is apparently good for updating early on in the day and then remaining static until end of day or next day.

I am looking to have, in the event of failure the ability to go back at the most 2 hours previously. In snapshot the entire data set is being copied and it is therefore recommeded not to use too frequently


Should i configure the existing replication so it is now transactional or should i remove and add a completley new replication, this time transactional.

Thanks for your time.(much appreciated)

Bluegirl

 

[MikeTom]

I would change to transactional replication. The way i ould do it is to unbulish the server and republish and resetup the article using transactional replication.

 

[Bluegirl]

Thank you