Bidirectional Transactional Replication And Remote Distributors

Nov 27, 2006

I want to be able to setup the following configuration using bidirectional transactional replication on SQL 2005

instance A lives on machine 1
instance B lives on machine 2

Instance A publishes to a transactional subscription on Instance B
Instance B does the reverse and publishes to a transactional subscription on Instance A

Instance A pushes to a distribution database on machine 2
Instance B pushes to a distribution database on machine 1


Problems Implementing Configuration

I can setup each instance as a distributor and create separate distribution databases using
sp_adddistributor and sp_adddistributiondb

I can then enable each publisher to use the correct distribution database using sp_adddistpublisher

However, when I try and run sp_replicationdboption to setup a database for publication, I get the error:

Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 55
The Distributor has not been installed correctly. Could not enable database for publishing.

If I try and configure a publication with the wizard, it says that instance A must be enabled as a publisher before you can create a publication - and then presents the dialog to add instance A to be handled by the local distribution database - which I don't want.

It appears that I need to run sp_adddistributor on the publisher as well as the distributor for the appropriate instance, but if I do this I get the error:

Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109
The server 'instance A' is already defined as a Distributor.

It seems that you can't select a remote distributor if you already have a local distributor configured.

Is there a way round this limitation?

Note that configuring the environment with a single distribution database works fine

View 7 Replies


ADVERTISEMENT

Bidirectional Replication - Use Merge Or Transactional?

Feb 6, 2003

Hello:

I am planning to set-up a bidirectional replication where both servers are remore - i.e. they do not share a common domain.

Would it be better to replicate using Transactional Replication or Merge Replication?

How stable is bidirectional replication? I could not find much material on this. :(

Thanks,

Bianca

View 2 Replies View Related

Multiple Distributors For Merge Replication.

Oct 2, 2007

I have 1 publisher in MI and 3 pull subscribers (1 local subscriber in MI and 2 in NC).



I am trying to decide whether to use a central distributor or a distributor per subscriber. It is my understanding that if I use a central distributor, large transactions that are being process will block other transactions from happening and increase latency. Most of the transaction will be relatively small, but several times during the day clients update large amounts that cannot be predicted. I want to make sure the other locations are not waiting on a large update to be processed through the distributor.



My current plan is to create subscribers with their own distributor. What are the issues with doing that over a central distributor?

View 1 Replies View Related

Bidirectional Replication

Nov 24, 2005

Hi,

can you pls help me to find the suitable Version of SQL Server?

SQL Server will work on the serverside. MS Access on the clientside.

Between these two DBs i need asynchronous bidirectional replication.

What version of SQL Server would be the best solution for this?

Thanx for ur help


Greetz Crean

View 4 Replies View Related

Bidirectional Replication

Oct 26, 2006

Hello,

is it possible to implement bidirectional replication with queued updating subscriptions in SQL Server 2000? I am currently testing bidirectional replication on two servers and it works well so far. My concern is how to I update the subscriber or publisher once both servers become disconnected? How to I resync? thank you for your help,

Lars

View 3 Replies View Related

Is Bidirectional Replication Possible With Sql Server?

Jun 18, 2008

Hi,
 We have Two Database server at location A and at location B, Say 'Server A' and 'Server B'.
 Both database contain's same schema and data.
A group of user's will make updation's to 'server A' and rest  using 'Server B'.
My requirement is that i want the both database to be identical, ie if an updation or insertion in server A is made then it should also affect 'serverB' and vice versa.
* Can go for batch updation , because very 10min or lesser i want the datbase to be syncronized.
* can go for single side replication as both database will be updating
Thanks In Advance,
KCube

View 1 Replies View Related

Bidirectional Replication Solutions

Apr 9, 2008

According to this article from last year:http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1251149,00.htmlThese are the main options:* Merge Replication* Bi-directional Transactional replication* Immediate Updating* Queued Updating* Peer to Peer* RDAAre there any new alternatives that have popped up over the last year? Are all of six above still good options based on needs?We currently have a three server topology using merge replication.ServerA (App1DB) <--> ServerB (App2 DB)ServerA (App1DB) <--> ServerB (App3 DB)ServerA (App1DB) <--> ServerCServerA supports 1 intranet application using 1 DBServerB supports 2 extranet applications using 2 DB's (1 per application)ServerC is our DW server that we have installed a Search DB which is used by all applicationsPrior to our "upgrade" to merge replication we were using 1-way Transactional Replication so our topology looked like:ServerA --> ServerB (App2 DB)ServerA --> ServerB (App3 DB)We also had linked servers between ServerB and ServerA as well as between ServerC and ServerA to update data on ServerA. We would simultaneously update/insert the tables on ServerB/C and create custom stored procedures to handle the data already processed from the subscribers.With our new implementation we are seeing more latency as well as locking since merge replication is not running off of transaction logs anymore.My main question is would we see an increase in performance and less locking as a result of a topology like this:Master <--> ServerA (App1 DB)Master <--> ServerB (App2 DB)Master <--> ServerB (App3 DB)Master <--> ServerCWhere Master is a server and DB supporting no applications (hence no OLTP). Would latency be the same/better/worse? Should we stick with our current implementation and just performance tune it?A secondary question I have is given the bidirectional replication options above did we choose the best one for us? These servers are all on the same network hosted by the same provider over Gigabit Ethernet (I assume). I think we have the polling interval set at 5 seconds and we are thinking of moving it to 10 seconds at most. Real-time latency is not critical to our business but it would be a "nice to have". For conflict resolution we are keeping it simple, whichever was inserted/updated last "wins". It looks like Bi-directional Transactional replication might be a better option for us. Would it give us the autonomy we are looking for? Any major "cons" to using Bi-directional Transactional replication over merge replication (beside scalability). Scalability may come into play a few years down the road but for now it is not a high priority. Also would the Master model described above using Bi-directional Transactional replication be a successful implementation?ETA - One thing merge replication gives us is autonomy between our application servers, particularly when ServerA needs to come down for upgrades, the applications on ServerB can still function without any dependencies like we had before with 1-way transactional replication with linked server calls.

View 2 Replies View Related

Bidirectional Bi-directional Replication

Oct 24, 2007

Following setup s1<--- p1<-->p2 ---->s2 (bidirectional replication between publishers as each one have its own subscriber)
What are the disadvantages of this solution if only one publisher gets written to at the time.
How about schema changes (would I need to stop all activity on p1 & p2 similar to p2p replication) ?
Would changes get republished to s1 & s2 ?
Are identities the only problem when instead on p1 as main server I start using p2 ?
Thank you.

View 4 Replies View Related

Bidirectional Replication In SQL Server 2005 SP2

Feb 25, 2008

Hi,

I have a problem configuring Bidirectional replication in SQL Server 2005 SP2. I configured Publication and Subscription on two different SQL 2005 instances on different machines (Station1SQL2005 and Station2SQL2005 respectively). Databases are DBTest1 in Station1 and DBTest2 in Station2. I have two tables one in DBTest1 and the other in DBTest2.

Script for the above configuration:

This below configuration does not work if i configure Publication and Subscription on the same machines

---*************************************************************************************************

For Station1:
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'dbtest1')
DROP DATABASE dbtest1;

CREATE DATABASE dbtest1
go

--Create table named two_way_dbtest1 that have an IDENTITY column with the NOT FOR REPLICATION option set
USE dbtest1
go

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_dbtest1')
DROP TABLE two_way_dbtest1;
GO

CREATE TABLE two_way_dbtest1
(
pkcol INTEGER PRIMARY KEY NOT NULL,
intcol INTEGER IDENTITY(1,1) NOT FOR REPLICATION,
charcol CHAR(100),
timestampcol TIMESTAMP
)


/*Allocate a predetermined range of values to the primary key column
so that the values on the different servers are not in the same range.
For example, you can enforce 1-1000 as the key range for the two_way_dbtest1 table in the dbtest1 database,
and then enforce 1001 -2000 as the key range for two_way_dbtest2 table in the dbtest2 database.
To do so, use the following code:
*/
-- Constraint to enforce a range of values between 1 and 1000 in database dbtest1
USE dbtest1
go

ALTER TABLE
two_way_dbtest1
WITH NOCHECK
ADD CONSTRAINT
checkprimcol CHECK NOT FOR REPLICATION
(
pkcol BETWEEN 1 AND 1000
)
go


--Enable your server as the distributor, and then create a distribution database
--Ensure SQL Server Agent service is running before executing the below statement.
USE master
go
sp_adddistributor @distributor = 'Station1SQL2005'
go

--create a distribution database for the distributor
USE master
go
sp_adddistributiondb @database='distribution'
go

--Enable the computers running SQL Server that are participating in the replication as publishers
USE master
go

exec sp_adddistpublisher
@publisher = 'Station1SQL2005',
@distribution_db ='distribution',
@security_mode = 0,
@login = 'xxxxxxx',
@password = 'xxxxxxx',
@working_directory ='F:ReplicationReplication Working Directory'

--Enable the identified databases for replication
USE master
go

exec sp_replicationdboption N'dbtest1', N'publish', true
go

--Create the custom stored procedures in the dbtest1 database
USE dbtest1
go

-- INSERT Stored Procedure

CREATE PROCEDURE sp_ins_two_way_dbtest1
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier
AS
INSERT INTO two_way_dbtest1
(
pkcol,
intcol,
charcol
)
VALUES
(
@pkcol,
@intcol,
@charcol
)
go

--UPDATE Stored Procedure

CREATE PROCEDURE sp_upd_two_way_dbtest1
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier,
@old_pkcol int
as
DECLARE @x int
DECLARE @y int
DECLARE @z char(100)

SELECT
@x=pkcol,
@y=intcol,
@z=charcol
FROM
two_way_dbtest1
WHERE
pkcol = @pkcol



DELETE
two_way_dbtest1
WHERE
pkcol=@pkcol



INSERT INTO two_way_dbtest1
(
pkcol,
intcol,
charcol
)
VALUES
(
CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END,
CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END,
CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END
)
go

-- DELETE Stored Procedure

CREATE PROCEDURE sp_del_two_way_dbtest1
@old_pkcol int
AS
DELETE
two_way_dbtest1
WHERE
pkcol = @old_pkcol
go

--Create a transactional publication, and then add articles to the publication in both the dbtest1 and the dbtest2 databases
--In the database dbtest1.
USE dbtest1
go

-- Adding the transactional publication.
EXEC sp_addpublication
@publication = N'two_way_pub_dbtest1',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Transactional publication for database dbtest1.',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false',
@immediate_sync = N'false',
@allow_sync_tran = N'true',
@autogen_sync_procs = N'true',
--To avoid expiry if there are 5 continuous holidays for a company. If 0, well-known subscriptions
--to the publication will never expire and be removed by the Expired Subscription Cleanup Agent.
@retention = 120
go

EXEC sp_addpublication_snapshot
@publication = N'two_way_pub_dbtest1',
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 1,
@frequency_subday = 2,
@frequency_subday_interval = 10,
@active_start_date = 20080225,
@active_end_date = 99991231,
@active_start_time_of_day = 070000,
@active_end_time_of_day = 235959
go

-- Adding the transactional articles.
EXEC sp_addarticle
@publication = N'two_way_pub_dbtest1',
@article = N'two_way_dbtest1',
@source_owner = N'dbo',
@source_object = N'two_way_dbtest1',
@destination_table = N'two_way_dbtest2',
@type = N'logbased',
@creation_script = null,
@description = 'two_way_dbtest1 table data will be replicated to two_way_dbtest2',
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F1,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_ins_two_way_dbtest1',
@del_cmd = N'CALL sp_del_two_way_dbtest1',
@upd_cmd = N'CALL sp_upd_two_way_dbtest1',
@filter = null,
@sync_object = null,
@identityrangemanagementoption = 'manual'
go

/*In this scenario, the dbtest1 database is the central subscriber.
Create transactional subscriptions in the dbtest2 database that subscribe to the publication at dbtest1
and in the dbtest1 database that subscribe to the publication at dbtest2
*/
--Create all the subscriptions with the LOOPBACK_DETECTION option enabled
--Adding the transactional subscription in dbtest1.
USE dbtest1
go
EXEC sp_addsubscription
@publication = N'two_way_pub_dbtest1',
@article = N'all',
@subscriber = 'Station2SQL2005',
@destination_db = N'dbtest2',
@sync_type = N'none',
@status = N'active',
@update_mode = N'sync tran',
@loopback_detection = 'true'
go

EXEC sp_addpushsubscription_agent
@publication = N'two_way_pub_dbtest1',
@subscriber = 'Station2SQL2005',
@subscriber_db = N'dbtest2'
go

For Station2:
--Create database named test1
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'dbtest2')
DROP DATABASE dbtest2
go

CREATE DATABASE dbtest2
go

--Create table named two_way_dbtest1 that have an IDENTITY column with the NOT FOR REPLICATION option set
USE dbtest2
go

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_dbtest2')
DROP TABLE two_way_dbtest2;
GO

CREATE TABLE two_way_dbtest2
(
pkcol INTEGER PRIMARY KEY NOT NULL,
intcol INTEGER IDENTITY(1,1) NOT FOR REPLICATION,
charcol CHAR(100),
timestampcol TIMESTAMP
)


/*Allocate a predetermined range of values to the primary key column
so that the values on the different servers are not in the same range.
For example, you can enforce 1-1000 as the key range for the two_way_dbtest1 table in the dbtest1 database,
and then enforce 1001 -2000 as the key range for two_way_dbtest2 table in the dbtest2 database.
To do so, use the following code:
*/
-- Constraint to enforce a range of values between 1 and 1000 in database dbtest1
USE dbtest2
go

ALTER TABLE
two_way_dbtest2
WITH NOCHECK
ADD CONSTRAINT
checkprimcol CHECK NOT FOR REPLICATION
(
pkcol BETWEEN 1 AND 1000
)
go


--Enable your server as the distributor, and then create a distribution database
--Ensure SQL Server Agent service is running before executing the below statement.
USE master
go
EXEC sp_adddistributor
@distributor = 'Station2SQL2005'
go

--create a distribution database for the distributor
USE master
go
sp_adddistributiondb @database='distribution'
go

--Enable the computers running SQL Server that are participating in the replication as publishers
USE master
go

exec sp_adddistpublisher
@publisher = 'Station2SQL2005',
@distribution_db ='distribution',
@security_mode = 0,
@login = 'xxxxxxxxx',
@password = 'xxxxxxx',
@working_directory ='E:ReplicationWorking Directory'

--Enable the identified databases for replication
USE master
go

exec sp_replicationdboption N'dbtest2', N'publish', true
go

--Create the custom stored procedures in the dbtest1 database
USE dbtest2
go

-- INSERT Stored Procedure

CREATE PROCEDURE sp_ins_two_way_dbtest2
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier
AS
INSERT INTO two_way_dbtest2
(
pkcol,
intcol,
charcol
)
VALUES
(
@pkcol,
@intcol,
@charcol
)
go

--UPDATE Stored Procedure

CREATE PROCEDURE sp_upd_two_way_dbtest2
@pkcol int,
@intcol int,
@charcol char(100),
@timestampcol timestamp,
@rowidcol uniqueidentifier,
@old_pkcol int
as
DECLARE @x int
DECLARE @y int
DECLARE @z char(100)

SELECT
@x=pkcol,
@y=intcol,
@z=charcol
FROM
two_way_dbtest2
WHERE
pkcol = @pkcol



DELETE
two_way_dbtest2
WHERE
pkcol=@pkcol



INSERT INTO two_way_dbtest2
(
pkcol,
intcol,
charcol
)
VALUES
(
CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END,
CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END,
CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END
)
go

-- DELETE Stored Procedure

CREATE PROCEDURE sp_del_two_way_dbtest2
@old_pkcol int
AS
DELETE
two_way_dbtest2
WHERE
pkcol = @old_pkcol
go

--Create a transactional publication, and then add articles to the publication in both the dbtest1 and the dbtest2 databases
--In the database dbtest1.
USE dbtest2
go

-- Adding the transactional publication.
EXEC sp_addpublication
@publication = N'two_way_pub_dbtest2',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Transactional publication for database dbtest2.',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false',
@immediate_sync = N'false',
@allow_sync_tran = N'true',
@autogen_sync_procs = N'true',
--To avoid expiry if there are 5 continuous holidays for a company. If 0, well-known subscriptions
--to the publication will never expire and be removed by the Expired Subscription Cleanup Agent.
@retention = 120
go

EXEC sp_addpublication_snapshot
@publication = N'two_way_pub_dbtest2',
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 1,
@frequency_subday = 2,
@frequency_subday_interval = 10,
@active_start_date = 20080225,
@active_end_date = 99991231,
@active_start_time_of_day = 070000,
@active_end_time_of_day = 235959
go

-- Adding the transactional articles.
EXEC sp_addarticle
@publication = N'two_way_pub_dbtest2',
@article = N'two_way_dbtest2',
@source_owner = N'dbo',
@source_object = N'two_way_dbtest2',
@destination_table = N'two_way_dbtest1',
@type = N'logbased',
@creation_script = null,
@description = 'two_way_dbtest2 table data will be replicated to two_way_dbtest1',
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F1,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_ins_two_way_dbtest2',
@del_cmd = N'CALL sp_del_two_way_dbtest2',
@upd_cmd = N'CALL sp_upd_two_way_dbtest2',
@filter = null,
@sync_object = null,
@identityrangemanagementoption = 'manual'
go

/*In this scenario, the dbtest1 database is the central subscriber.
Create transactional subscriptions in the dbtest2 database that subscribe to the publication at dbtest1
and in the dbtest1 database that subscribe to the publication at dbtest2
*/
--Create all the subscriptions with the LOOPBACK_DETECTION option enabled
--Adding the transactional subscription in dbtest1.
USE dbtest2
go
EXEC sp_addsubscription
@publication = N'two_way_pub_dbtest2',
@article = N'all',
@subscriber = 'Station1SQL2005',
@destination_db = N'dbtest1',
@sync_type = N'none',
@status = N'active',
@update_mode = N'sync tran',
@loopback_detection = 'true'
go

EXEC sp_addpushsubscription_agent
@publication = N'two_way_pub_dbtest2',
@subscriber = 'Station1SQL2005',
@subscriber_db = N'dbtest1'
go

---*************************************************************************************************
It would be grateful if somebody gives me a solution.

Thanks in advance.

Ravi.

View 9 Replies View Related

How To Do Bidirectional Replication In SQL Server 2005?

Oct 4, 2007


Greetings...
Presently, I am doing one way replication in SQL Server 2005. Server-A is local server at local place and Server-B is remote server at different place. There is not a problem in one way replication.
Server-A is Distributor and Server-B is Subscriber in one way replication.
I want to setup the following configuration using bidirectional replication (two way replication) on SQL Server 2005
And I am not able to do it. What should I do for this?
Should I use Merge Replication for bidirectional Replication.
Server-B is live server for users which cannot be stop for a moment. Server-A is local server which is live too.
Now please let me know how to do Bidirectional Replication. So whatever data in Server-B (Which is live) should replicate to Server-A or Vice versa ...
If we add some column into Server-B's table of Database what could be the effect on Server-A...

Please someone help me out into this.

ThanksRicky

View 9 Replies View Related

Combining Peer-to-Peer Transactional Replication With Standard Transactional Replication

Dec 1, 2006

Hello,

I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:

We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.

Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?

We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.

That's the scenario.

Any feedback is welcome and appreciated.

Thanks,
Terence

View 4 Replies View Related

Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication

Sep 13, 2007

Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.

View 2 Replies View Related

Replication :: Transactional Replication Removal Sequence

Sep 2, 2015

I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication.  I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS.  I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication.  SQL-P (publisher), 

SQL-D (distributor) and SQL-S (subscriber).  Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.

View 6 Replies View Related

Merge Replication Set Off Transactional Replication

Oct 9, 2007

I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.

This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??

Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??

thanks...

View 5 Replies View Related

Transactional Replication

Jul 30, 2001

On my distribution server there is a directory called mssql7
epldataunc that houses the bcp, sch and idx files for replications. A directory is created for each publication. Most directories are empty but some contain all the table idx, sch and bcp files for the publication. Some of these files are over 6 month. Does anyone know if these files are neccessary to keep and why do only some publications have these files. I am wondering if they are kept when you push the publicaiton and it errors out originally and you have to restart it? If any one can shed some like on this I would appreciate it.

View 1 Replies View Related

Transactional Replication

Dec 28, 2000

Hi,
I am planning to do a transactional replication from production server to reporting server. In one of my database is having 150 tables in it, only 100 tables are having primary key . Remaining tables are not have primary keys. So i am not able to do transactional replication for those 50 tables. Could any one tell me how i have to approch transactional replication. Because i need to have production data at my reporting server in regular interval.
So pls suggest me .
I am using Sql 7.0 with service pack2.

Thanks!
Kavira

View 1 Replies View Related

Transactional Replication

Feb 13, 2001

I have a problem with transactional replication in SQL Server 7. I have two databases (a publisher-distributor server and a suscriber server) and when I do an UPDATE in the publisher database the Log Agent Reader reads the UPDATE as an DELETE-INSERT and when the suscriber pull the suscription and try to do first the DELETE the transactional replication stops because of the Foreign Keys in the suscriptor database (that are the same as the publisher).
If anybody knows how to solve it, please tell me

Thanks

View 1 Replies View Related

Replication - Transactional - 7.0

Nov 1, 1999

I have two servers networked via workgroups (no domain) and am trying to install Transactional Replication. Everything appears to install correctly, but when I run the initial Snapshot Agent I get the following error -

The process could not create file 'CONCEPCIONC$MSSQL7ReplDatauncCONCEPCION_Erik_Rep lication_Erik_Replication19991029153207Employer.sc h'.
Access is denied.

The SQLAgent is starting with same user account on both machines - admin rights and the same password. I have added this user to the (
epldata) folder permissions as well. I even see a "Employer.sch" file in the directory.

What am I missing?

View 2 Replies View Related

Transactional Replication

Oct 25, 1999

Hello,

I am planning to implement replication between two servers (either snapshot/transactional). Those two servers are physically connected but we don't have domain concept i.e we connected both servers physically and it can be pinked from each other, but not under any domain (not also under different domain). How should I register SQL server& agent services to start with 'Service startup Account'. In this case I cannot start both servers using same domain Account, they how can I relate...

Thanks

View 1 Replies View Related

Help... Transactional Replication

May 22, 2003

Hi guys can anyone help me with this issue. if so thankyou kindly.

1. created transactional publication (for tables, stored procs & views )
2. created empty database at subscriber
3. set up pull subscription at subscriber
4. ran the initial snapshot agent at the publisher
5. checked the schema files & found out it hadn't scripted the primary keys on the tables.

Problem: PRIMARY KEY AINT GOIN ACROSS, COULD ANYONE TELL ME IF IVE MISSED SOMETHING? THANK YOU MUCH APPRECIATED GUYS.

? Could it be that it is not picking up the primary keys on the tables because I am replicating stored procs and views with that publication?

? Should I break my publications into 3; say 1 for each object?

Thank you guys for taking the time to read this.

View 1 Replies View Related

Transactional Replication

Feb 24, 2005

Hi

I am tring to implement transactional replication.

How do i publish tables which donot have primary keys?...because transactional replication seems to publish only the tables which has a primary key.

Thanks

Madhukar

View 7 Replies View Related

Transactional Replication

May 1, 2002

Hello all! I am setting up my first Replicated database, and want to get this right.

We are a Newspaper, and I will be Publishing the database that is the back end of the web server. The goal is to load balance the web site as well as to have a second identical server in case something goes wrong. Both the Subscriber and the Publisher need to be kept in sync as new articles are posted, so my client will just be updating the Publisher. But I'd like to keep a single set of usage statistics, so I was thinking of using Immediate Updating so the Subscriber can update the Publisher using two-phase commit.

What will happen if the Publisher shuts down and an update is attempted on the Subscriber? Will it just hang? Let's assume the worst - what if my Publisher is kaput? How do I determine that my other database is a Subscriber; and how do I stop it from being a Subscriber?

Thanks in Advance,

View 1 Replies View Related

SQL Transactional Replication

Jun 28, 2002

Hi

I have a requirement for pushing transactional publication to various subscribers. Each subscriber will need to receive a different subset of rows from a table. So I need all rows published, but with horizontal filtering at subscriber level. Is there a way of applying filtering at the subscription level.

Thanks
Tariq

View 1 Replies View Related

Transactional Replication

Sep 11, 2007

I am using transactional replication between two SQL 2005 databases. On Sunday evenings, we run a maintenance plan that rebuilds the indexes on the publisher DB. When the plan runs, the replication receives errors and cannot continue. The error appears to be at the publisher level. The publisher runs continually and the subcriber runs every 10 minutes. The error states "cannot run sp_replcmds" Other than Sunday, everything runs fine.

Thank you

View 1 Replies View Related

Set Up Transactional Replication

Jul 7, 2015

I want to set up the transactional replication. How many service accounts we need and what kind of permissions it required in windows level?Only two servers we are using. Pub/Distributor and Subsciber.Which option is better Push or pull?

View 1 Replies View Related

Transactional Replication

Jul 27, 2007

at the 'specify articles" on the wizard. Some tables have a key next to it that doesn't let me select them to be replicated. This is happens on transactional replication. But if i do a snapshot, the keys next to the tables are not there.

How am I able to select those tables with a key?

=============================
http://www.sqlserverstudy.com

View 8 Replies View Related

Re Transactional Replication

Jan 11, 2006

Hi All!

Need help guys... I'm new to SQL Replication, though I successfully created a transactional replication testing on my machine using PUBS DB and a separate DB.

I'm planning to establish/setup a Transactional Replication. There are two SQL Servers, one is being used by WebApp_1 and the other is being used by WebApp_2.  Both servers are maintain by different administrators and situated on different domain/location (both used Win2003 Server).  We will transfer some data from Server_1 to Server_2. Server_1 will be setup as Publisher and the other is the Subscriber, then additonalServer_3 will be added and will serve as the Distibutor.

My questions are:

1. Do I need to request separate domain account from the two different servers to establish connection to the Distributor?

2. Will the User databases from Publisher in compromise/affected as far as database settings and data are concerned?

3. Any useful links?

Any help provided will be greatly appreciated...

Thanks!

 

View 1 Replies View Related

Merge Vs. Transactional Replication

Jul 13, 2001

Hi everybody,

I'm still pretty new when it comes to SQL server and I'd like to get some opinions about replication. I need to set up replication between 10 sites. The database is about 2 GB and at each size about 10 users are entering data. The databases need to be synchronized at least 3 times a day. Now our application developer told us to use transactional replication while I'm thinking of using merge replication.

Can anyone give me some more advice about the pro and cons of either replication type.
TIA

Charlie

View 1 Replies View Related

Replication - Merge Or Transactional??

Jun 26, 2000

We have 3 sites & would like to do replication btw the 3 sites. 1 publisher & 2 subscribers. All 3 sites should be able to do updates. Subscribers should be able to operate even if publisher is down. Any advice will be appreciated.

View 1 Replies View Related

Transactional Replication Errors

Feb 7, 2000

I setup Transactional replication between two SQL Servers.

I am getting the following errors: " Could not find stored procedure 'sp_MSins_stores'." and the job fails.

I dont have any idea why I am getting these errors. I am getting this error for every table in the publication.

Any suggestions are welcome.

Thanks in advance.

View 1 Replies View Related

Transactional Replication - Error 207

Jan 19, 2003

Hi Guys,

I have a small problem with the transactional replication when I run the initial snapshot and distributing some of the stored procedures. For eg., Say if the stored procedure has the following statements:

---------------------------------------
create procedure abc
as
declare @paytype varchar(50)
set @paytype='check'

insert into acct select cusname,cusacctno,@paytype from cuspay
---------------------------------------

I get the following error:
for statement:

set @paytype='check'
--------------------
Invalid column name 'check'.
(Source: ServerSQL1 (Data source); Error number: 207)

I tried changing to double quotes. even tried adding article using query analyzer without using EM

exec sp_addarticle @publication = N'ReplicationSrc', @article = N'abc', @source_owner = N'dbo', @source_object = N'abc', @destination_table = N'abc', @type = N'proc schema only', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000001, @status = 16
GO

Still I get the same error. The server has SQL server 2000 SP2 running with remote distributor and pull subscription.

Am I missing anything.


Please help.

Thanks,
Anu

View 2 Replies View Related

Problem With Transactional Replication

Jan 29, 2005

hi ,
i am trying out transactional replication to replicate over 60 gb of data.

i am having a problem during the initial creation of the article.

the system hangs at article 73 out of a total of 600 + articles

i have tried this using sp_addarticle as well
but in vain

any suggestions
please let me know

thanks

View 3 Replies View Related

GUID&#39;s Req&#39;d For Transactional Replication??

Aug 22, 2002

Does SQL Server 2000 (Enterprise Edition), mandate the creation of a GUID on each table used in transactional replication?

I was told that in addition to defining a Primary Key, I "MUST" also define a GUID on each table. As I recall - this is not true. Can someone who's setup Transactional Replication please field this question?

thx -
~BT

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved