Stupid Replication Q

Question

Hi,

Here's the brief.

I have DB_A on Server A, which I'm replicating to Server B. I then want to replicate tables from DB_A on Server B, out to DB_X on Server_B, but when I try and create the publication, the row is locked from selection, as it's obviously locked for replication from Server_A to Server_B.

Does anyone know if there is anyway round this? I ideally want Transactional Replication as our need is to have an identical copy for other calculations to be done and reporting purposes.

(Server_A is on it's last legs, so we have Server_B to do all the calculations and host MSRS)

Would we just be better off getting a bigger, more powerful server to replace A and B ?

Cheers

Steve

Answer 1

Steve,

Re-publishing data is a supported scenario and you should be able to do this. If I understood you correctly you are trying to set up a publication in DB_A on ServerB while ServerA is delivering the initial snapshot or a large set of incremental changes (which will take locks on the table(s) in DB_A on ServerB) and this is preventing you from setting up the publication (btw, what was the error you were getting?)

If my hunch is correct you may want to temporarily shut down the distribution agent that is delivering snapshot/incremental changes to ServerB, setup the publication on ServerB and then restart the agent.

Hope this helps.

VaqarPirzada
Microsoft

Answer 2

Hi Vaqar,

Thats exactly my problem.

So what you are saying is that do Transactional Replication on Server B, I need to stop the TransRep from Svr_A to Svr_B, Create the Publication on Svr_B to push from DB_A to DB_X on Svr_B, then once that's created and pushed to subscribers, then restart the subscription from Svr_A to Svr_B?

So it would go something like:

Svr_A (DB_A) >>>> Svr_B (DB_A) >>>> Svr_B (DB_X)

all on a transactional basis?

Cheers for your help.

Steve

Answer 3

Right,

I've done what I assumed to be right, which is create all the replications on Server B, then create the Publication and Subscription on Server A to push to B.

The server to server replication works perfect, but it's not then replicating on from B. I've checked the replications, etc and all is as it should be, but it's just sitting there, saying 'No Replicated transactions available'

Any pointers

Answer 4

You can publish the tables on DB_A on Server A to DB_A on Server B and then to DB_X by just running the publication wizard on Server A and selecting the tables from DB_A. Can you provide details on what is not working?

Answer 5

I just realized that we got out of order in our responses and you haven't gotten this working. So you setup transactional publications and subscriptions from A->B and synchronize it and then once it is working you setup transactional publications and subscriptions from B->A, correct? And can you verify that when you setup the subscription from B->A that you specify that the subscriber already has the data (and thus doesn't need the initial snapshot)?

Matt Hollingsworth
Microsoft SQL Server Program Manager

Answer 6

Morning Matt,

Not quite, no.

http://www.aoqz41.dsl.pipex.com/Drawing3.jpg

Have a look at the pic for a quick overview of what I want to achieve.

We have 2 servers. 1 hosts the Client information. That is then replicated to an identical DB on Server B, which in turn, various tables from DB_A are replicated around Server B.

Replication between SQ01 and SQ02 is fine, no problem, but when I try and setup the internal replication on SQ02, I cant select the tables I want, as they are locked for replication by the 'SQ01:SQ02' replication.

Someone mentioned setting up the internal replications on SQ02 first, then setting up the 'SQ01:SQ02'. I've done this, but it doesnot recognise anything being entered to SQ02:ClientDB, so wont replicate. I've restarted Log Readers, Reinitialised, to no avail.

It's looking more and more likely that I need to move to one server for everything :(

Any help appreciated.

Answer 7

Would it help if you would stop the agents on the servers and then setup replication on SQ02. Would the tables show up then??

Answer 8

Hi Mahesh,

Already tried that, but didnt work...

Answer 9

Have you tried to set up replication from server B to server C as a pull subscription?  You would set up your original server to publish and push to server B and then set up server C to pull from server B.

This may take away the contention since server C would wait for changes to be applied to server B (before being notified) and then pull these changes to server C.

Worth a try?  :-)

Answer 10

Ah...I see I didn't read your original question correctly.  Looks like you are replicating from server A to server B and then internally from server B onto itself to a third database?

If so, my suggestion is probably not possible.  I've never tried it.

Sorry.