Solved: Settin' up a Transactional Replication

Create Account

Microsoft SQL Server

Questions

Followers

Top Experts

anilkullam🇮🇳

Settin' up a Transactional Replication

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 :)

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Vitor Montalvão🇨🇭

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)?

anilkullam🇮🇳

ASKER

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...

anilkullam🇮🇳

ASKER

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..

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Vitor Montalvão🇨🇭

Did you try to change the snapshot replication to transactional replication? Should be more easier than create new one.

anilkullam🇮🇳

ASKER

Nopes that is not the way my comp ppl wanted ....can u pls share your knowledge that can address my problem

SOLUTION

Vitor Montalvão🇨🇭

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

anilkullam🇮🇳

ASKER

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

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

ASKER CERTIFIED SOLUTION

Zberteoc🇨🇦

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

Zberteoc🇨🇦

Here is a step by step guid just in case:

http://www.databasejournal.com/features/mssql/article.php/1438201/Setting-Up-Transactional-Replication-A-Step-by-step-Guide.htm

Vitor Montalvão🇨🇭

Zberteoc said all.

anilkullam🇮🇳

ASKER

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...

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

anilkullam🇮🇳

ASKER

@VMontalvao: Can you pls share the conversion process from one replication to another type...

Zberteoc🇨🇦

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.

anilkullam🇮🇳

ASKER

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.

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Vitor Montalvão🇨🇭

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

anilkullam🇮🇳

ASKER

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

Vitor Montalvão🇨🇭

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.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

anilkullam🇮🇳

ASKER

yeah ...i have sa privlages for my account...and as posted earlier...i do face such problems while proceeding with that

Zberteoc🇨🇦

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.

anilkullam🇮🇳

ASKER

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.

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Zberteoc🇨🇦

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.

Zberteoc🇨🇦

If you do open a second question you can paste here the link to it to make sure we get to it.

anilkullam🇮🇳

ASKER

yeppie sir, sure...

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Microsoft SQL Server

Questions

Followers

Top Experts

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.