?
Solved

SQL Transactional replication trouble

Posted on 2012-12-27
9
Medium Priority
?
262 Views
Last Modified: 2013-01-15
We have setup a publication of TABLE_INFO on SERVER1 to TABLE_INFO_SERVER1 on a remote subscriber server. This works fine as all entries made at SERVER1 are being replicated to the remote subscriber server destination table of TABLE_INFO_SERVER1 successfully.

When we try adding a second subscription from another server of TABLE_INFO on SERVER2 to TABLE_INFO_SERVER2 the replication initializes correctly, but the SERVER1 table stops updating after initialization.  Then instead of SERVER1 going to its destination table and SERVER2 going to its destination table, all records from both SERVER1 and SERVER2 are now being delivered to the SERVER2 destination tables.

I have checked to ensure that the destination tables at the publisher are configured correctly already. The only thing I can think of is that SQL doesn't like that the name of source tables on both servers are the same. Any assistance is appreciated...
0
Comment
Question by:MicroAutomation
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 

Author Comment

by:MicroAutomation
ID: 38725459
Thanks. Not sure how I chose the wrong zone but definitely needed to be moved.
0
 
LVL 8

Expert Comment

by:venk_r
ID: 38725611
Is there any custome stored procedures involved in this replication.
?Is the distribution Server on a separate server and is that being shared by both the subscriptions?
0
 

Author Comment

by:MicroAutomation
ID: 38725638
No there is no custom stored procedure in use for this.

The Distribution server is a separate server it is being shared by both subscriptions. It is setup to allow each both Publishers to use it. Both publishers are using separate distrib databases. At least as far as I can tell they are.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:venk_r
ID: 38725676
If its more than one distribution database please verify the setup by going thru the below link and make sure they'r setup correctly.The distributor may have been pointing to same destination.

http://www.sqlservercentral.com/articles/Replication/69663/
0
 

Author Comment

by:MicroAutomation
ID: 38728606
At the distributor we have separated the distribution databases, locations, snapshot folders, etc for each publication. We have read the link and reviewed all the distributor settings.

Same results.
0
 

Author Comment

by:MicroAutomation
ID: 38733424
Any other thoughts on this?
0
 

Accepted Solution

by:
MicroAutomation earned 0 total points
ID: 38753333
We have started again on this and have setup the initial distributor, publication and subscriber. The tables are being updated in near real-time at the subscriber successfully now. Now just need the other half.

Attached are the setups scripted out for each.

We need to add to this:

Distributor (single server for both publishers)
   - a second allowed publisher on the distributor called abas02 (or IVRNASC01P11)
   - needs to use its own abas02 db and abas02 folder

Publisher (second publishing server)
   - a second publisher with the same publication but everything is abas02 (or IVRNASC01P11)
   - the target tables also change from _as01 to _as02

Subscriber (single subscriber for both publications)
   - add second subscription same as the first but everything is abas02 (or IVRNASC01P11)
   - both publications come to same database on this server only target table names here are different. (_as01 & _as02)


Thanks for any continued assistance...
rep.zip
0
 

Author Closing Comment

by:MicroAutomation
ID: 38777548
We have corrected the problem on our own. Thanks.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question