Solved

Settin' up a Transactional Replication

Posted on 2011-02-13
23
318 Views
Last Modified: 2012-05-11
We have 2 servers A and B.
There is already a snapshot replication set up between A & B. And now Iam supposed to setup a transactional replication with updateable subscription on the same A & B, but as this req requires a linked server, I also had trouble in creating one.Whenever I tried to create a linked server on the publisher on A, I was not able to map a login to impersonate, the logins does not show up at all....

So thinking that I could succeed with simple tran replication tried to do it the following way:
Since distributor is already configured on the publisher A, I have created a push publication on A(which is also the distributor) and also the subscriber on another server.
Though it didnt give me any errors, the snapshot is not running even when tried to run it manually from the replication monitor.
Tried to configure it few more times but in vain...

And then poured some efforts in googling and found that it could be fixed thru sp_link_publication...but again while providing login details as parameters it is not accepting the same. I have a win NT login with sa admin privilages and am using the same for accessing diff severs and for setting up HA features also.

I have tried to create the tran replication on a fresh databases on my test servrs and succeeded :)
I agree that Im naive while creating one in an existing environment, So I seriously need ur help from you experts...please lend a helping hand in getting this done.

Lemme know if you need more info...as I may be missing some imp detail for you to understand.
Thanks in advance for all who put in ur efforts in working towards fixing my issue and for also sharing ur valuable info and ur invalubale expertise with me :)
0
Comment
Question by:anilkullam
  • 11
  • 6
  • 6
23 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 34887038
First of all, if you already have a replication between those two servers, why do you need to create a linked server?
This new replication (Transactional) gonna replicate same tables of the existing replication (Snapshot)?
0
 

Author Comment

by:anilkullam
ID: 34887618
yes...you guessed it right! gonna replicate the same tables.It is prompting for a linked server conn while trying out with a tran repl with updateable sub...
0
 

Author Comment

by:anilkullam
ID: 34887966
Haaa I didnt ans ur Q completely,since snapshot repl is configured for a nightly run, the upper ladder wants to try with tran repl over the same servers and test it. And later remove snapshot repl..
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 34888337
Did you try to change the snapshot replication to transactional replication? Should be more easier than create new one.
0
 

Author Comment

by:anilkullam
ID: 34889615
Nopes that is not the way my comp ppl wanted ....can u pls share your knowledge that can address my problem
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 34889640
I only worked with transactional replications but I think you are having problems because you are trying to replicate same tables to same database that are already set in snapshot replications.
So my suggestion is to drop (or change) snapshot replication.
0
 

Author Comment

by:anilkullam
ID: 34889811
Hmmm so can you please share that the steps to whatever you sugges..possibly in a detailed manner...converting is one im unaware of...kinda new to chnage the existing setup
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 250 total points
ID: 34889828
You have to decide what replication you want to keep. You are not supposed to create 2 replications between the same 2 servers for the same objects. You don't need a link server for the transactional replication.

I suggest to drop the snapshot replication because transactional will have a more frequent sync period.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34889832
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 34889898
Zberteoc said all.
0
 

Author Comment

by:anilkullam
ID: 34891234
Guys can you pls confirm just one thing...Is it that Im not supposed or we cannot create two types of replications on the same set of servers.
Also thanks for the responses...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Closing Comment

by:anilkullam
ID: 34891265
@VMontalvao: Can you pls share the conversion process from one replication to another type...
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34891636
As I know you can't. Even if you did imagine only what would happen when both replication try to insert the same row into subscriber. You will get PK violation errors and alerts all the time.

You can't convert replication. You have to drop the one that you don't need first and then create the other one. Make sure you write down what tables need to replicate.
0
 

Author Comment

by:anilkullam
ID: 34894402
Though it is between the same servers A & B....the target DB used in tran repl is different one....however the source is common for both the tran replications types.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 34914223
Sorry I was out these last 2 days.
You can have plenty of replications between same servers and databases, but be very careful when having you want to replicate same articles (tables). Still work good if you replicate different columns but for sure you'll have problems when you try to replicate same columns.
Cheers
0
 

Author Comment

by:anilkullam
ID: 34914471
nopes...the articles are at table level and not column level, thanks for the response..you have zeroed in almost ...just a step away...can we use the same tables for both snapshot and tran repl with just the target DBs being different for the repl's.  
Whats in my mind:the errors that i get while configuring over this setup are different..which means...with a simple tran, the snapshot agent not running even when tried manually... and for updateable subscriptions...prompting for linked server login cred...as such
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 34914559
You shouldn't have problems in having different targets.
You should check for replication jobs owner after creating replication. Should be user with full permissions.
0
 

Author Comment

by:anilkullam
ID: 34914569
yeah ...i have sa privlages for my account...and as posted earlier...i do face such problems while proceeding with that
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34915043
One object(table) can only be part of one publication a a time but can have any distinct subscribers. The reason for that being the way SQL server implements the replication. For each table a publication creates a set of 3 triggers, for insert, update and delete. When you add a table to a second publication the triggers from the first one will be removed.
0
 

Author Comment

by:anilkullam
ID: 34915167
I'm taking your stmts for granted and informed the business the same,also I strongly wish you to lend a helping hand till Im done with this setting up.

Will keep you posted with the proceedings.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34915307
Maybe if you can describe what you want to achieve we can come with suggestions. I also suggest you to open a separate question if possible to describe it there as this question was answered.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34915315
If you do open a second question you can paste here the link to it to make sure we get to it.
0
 

Author Comment

by:anilkullam
ID: 34915349
yeppie sir, sure...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now