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 :)
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 :)
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...
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..
Did you try to change the snapshot replication to transactional replication? Should be more easier than create new one.
ASKER
Nopes that is not the way my comp ppl wanted ....can u pls share your knowledge that can address my problem
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
http://www.databasejournal.com/features/mssql/article.php/1438201/Setting-Up-Transactional-Replication-A-Step-by-step-Guide.htm
Zberteoc said all.
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...
Also thanks for the responses...
ASKER
@VMontalvao: Can you pls share the conversion process from one replication to another type...
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.
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.
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.
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
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
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
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
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.
You should check for replication jobs owner after creating replication. Should be user with full permissions.
ASKER
yeah ...i have sa privlages for my account...and as posted earlier...i do face such problems while proceeding with that
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.
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.
Will keep you posted with the proceedings.
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.
If you do open a second question you can paste here the link to it to make sure we get to it.
ASKER
yeppie sir, sure...
This new replication (Transactional) gonna replicate same tables of the existing replication (Snapshot)?