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
ADVERTISEMENT
Nov 18, 1999
SQL Version 7.0
I have set up bi-directional replication between two databases on one table that has the structure of
Table Name : tblTrade
id : int (identity)
TicketNo : int (globally assigned via a sp - therefore always unique)
Location : int (On Server 1 defaults to 1) ---|PRIMARY
(On Server 2 defaults to 2) ---|KEY
.General Trade Fields...
Hence when a trade is entered into Server1 the Location defaults to 1 and is then replicated via an horizontal partition filter on the Location field. And similar for Server2.
Now Inserts work fine. However when a the following operations occur
DELETE - A DELETE against a record on Server1 results in ALL records being deleted from Server2
UPDATE - An UPDATE operation against a record on Server1 then ALL records get updated with the UPDATE SET clause.
The same happens with Updates on Server2 replicated to Server1.
Now it looks like the WHERE clause is missing from the replicated statements.
WHAT IS GOING ON, AND DOES ANYONE HAVE AN IDEA OF HOW TO GET AROUND THIS OR SET UP REPLICATION TO WORK PROPERLY
Regards
Steve
View 1 Replies
View Related
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
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
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
Sep 27, 1999
The docs state that a timestamp column is required for bi-directional
replication. I am not syncing the databases since they are too big and
it is a new project so I can guarantee at start that there will be two
exact copies of data. The data is being converted from a FoxPro db to
sqlserver.
The question is:
How do you design the timestamp columns for partioned bi-directional
replication? Does the publisher get the timestamp and the subscriber get
a binary(8)? Since there are two publishers is a distinct timestamp and
binary pair required for each server? See below...
Server: REP1 REP2
Table: tblTest tblTest
Columns: col1... col1...
tsREP1 timestamp tsREP1 binary(8)
tsREP2 binary(8) tsREP2 timestamp
Do the binary(8) columns need to be removed from the articles? Can
anyone explain how the timestamp is used to stop loop back? Is the loop
back check done on the Publisher or Subscriber?
Thanks,
Norman
View 1 Replies
View Related
Jan 14, 2000
Server_1 contains DB_A and DB_B. Server_2 contains DB_A and DB_B.
Can replication be setup where Server_1 DB_A (publisher) publishes to Server_2
DB_A (subscriber) and Server_2 DB_B (publisher) publishes to Server_1 DB_B (subscriber)? The environment is NT 4 (sp3) and SQL 6.5 (sp5a). Thanks in advance.
View 1 Replies
View Related
Nov 12, 1999
Hi All
I am trying to set up bi-directional replication (between two domains) on a simple test table prior to installing it on my Live system. The table structure is along the lines of
id int (Primary Key)
name varchar(50)
location int
I use the Location field and the filter for the Publication. i.e. 1 = Server1 and 2 = Server2
Well I set the replication up on Server1 to Server2 and test the subscription and everything replicates perfectly. Note : When I set the Server2 subscription to Server1 I recreate the structure of the table on Server2
After performing a few replicated INSERTS into the table on Server1 and they have replicated through to Server2. I set Pub/Sub on Server2 to Server1. NOTE : on the setup of the subscription I select the option that the schema and data on the target table is correct.
As soon as I enter a line into the table on Server2 the replication monitor errors on Server2 with the message that the insert stored procedure for the replication cannot be found, indicating that they have not been created.
WHAT AM I DOING WRONG FOR THE SET UP OF BI-DIRECTIONAL REPLICATION??????
Thanks
Steve
View 2 Replies
View Related
Feb 28, 2006
Besides loopback detection, is there anything else that is needed to prevent the replication command from sending back to the source. Cos i am having problem with the subscriber B sending back the replication to A in the form of B as the publisher and A as the subscriber.
:confused:
View 3 Replies
View Related
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
Aug 25, 2005
Two Servers,1st 2nddatabase a -------> after replication a'b' <------- after replication bon 1st server a is replicated to 2ndon 2nd server b is replicated to 1stI tried with merge replication it is working fine.But if i tried to usetransactional replication it give some error like "accessviolation".Using tansactional replication is it possible.plz help me toslove this problem.Regards,Senthil prabu R
View 1 Replies
View Related
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
Oct 19, 2004
Hi,
I am trying to setup bi-directional transactional replication between 2 SQL Servers,
I add the subscription on both the servers using the code below.
EXEC sp_addsubscription @publication = N'test',
@article = N'all', @subscriber = 'AnyServer',
@destination_db = N'test', @sync_type = N'none',
@status = N'active', @update_mode = N'read only',
@loopback_detection = 'true'
GO
I have defined 'AnyServer' on both the servers using cliconfg
(Server1's AnyServer pointed towards Server2 and Server2's AnyServer pointed towards Server1, I need to do that because there is a restriction to run the same code on both the servers),
After inserting a record on server1 in the 'test' database, the changes successfully transfers to the server2, then server2 sends it back to server1 and server1 generates the error of
"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'."
It seems as if loop detection is failing if I keep the same subscriber name on both sides.
It runs fine when I change the subscriber name in the subscription (@subscriber = 'Server2' for server1 and @subscriber = 'Server1' for server2).
Can anybody explains this behavior to me?
View 2 Replies
View Related
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
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
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
View Related
Mar 20, 2008
From a service broker newbie...
Most of the examples I've found and played with demonstrate two way conversation. A sender initiates a call, and gets a message back.
My Requirements doesn't really need two way communication. I have a scenario where triggers on two different tables result in modifications to a third table, and I don't want the triggers to deadlock each other, so an asynchronous queueing mechanism seems like the perfect solution...
But I can't seem to make it work one way.
I can get one message through, and then all subsequent messages hang up in the transmission queue with the very informative "One or more messages could not be delivered to the local service targeted by this dialog."
I'm thinking all the examples work the way they do because you have to notify the transmitter that the message was
received by sending a message back... and by not doing this I'm stuck in the first conversation. I was thinking that by doing END CONVERSATION <Msg Handle> in the stored procedure bound to the receiver's queue was doing that.
Do I have to communicate bi-directionally always? I guess this is a safety feature but I trust MSMQ to deliver messages...
Thx
View 3 Replies
View Related
Jul 23, 2005
Hi,Our company is an independent Voice applications solution provider withnumber clients using our suite. We have a CT application suite which isrunning with Application Server and SQL Server 7 / 2000 as DB Enginesat the back end.The SQL server has two databases configured:Logging Database - Massive updates every second, the data growsrapidly,Configuration Database - Generally small-sized and updatedoccasionally.Now we want to have the reslience implemented on the server. We have tosynchronize the two databases 'real-timely' and in 'efficient'manner, so that if Primary server or its Databases gets unavailable,the users are seamlessly switched over to the Secondry server that willhave its own set of data updated and well synchronized.Typically, it can be explained as follows:1. We will have 2 database servers A - Primary (acting as publisher)and B - Secondary (acting as subscriber). Our application will beinitially connected to A.2. When A becomes unavailable (for whatever reason), the applicationwill fail-over to B.3. All the users will be switched to server B and the updates are beingdone accordingly without being replicated on Server A temporarily.4. When A is back on-line, A needs to be brought up-to-date with Bautomatically (In other words, I shouldn't have to manually export allthe data from B to A ).Our requirements are:- The system should support Bi-directional Synchronizationbetween both the servers for their set of databases (the logging andconfiguration).- There will be constant and heavy activity in LoggingDatabase, thus if one server gets down the data should be logged andmaintained as it is on second server and on fail-back no data-lossshould occur with minimum latency time.- There could be a scenario when a server fails-over for aweek's time, there will be constant logging each second! Once itfails-back the system should rapidly synchronize the data withoutnoticeable delay among the two server database sets.- The system should also work fine if certain amount ofrecords are purged over a time period.Our concern is, observing the above scenario, how any of your SQLserver replication strategy can help us achieve the requirements.ThanksJohn
View 4 Replies
View Related
Nov 20, 2006
Greetings,
My desire is to have my primary db in FL and an online hot standby in both my MI and CA offices. The hardware is in place, as well as T1 size pipes. The solution I'm looking for would have all databases online in case of hardware / environmental failure although the application would only be pointing at the primary (or designated) database server. We have another 3rd party solution available, but I'd like to stay native SQL if possible.
Is replication the way to go?
Does one-to-many work?
Can all the db's be online and available while replicating?
Can I catch up if switched to one of the other db's temporarily?
note:This is 100% SQL 2005.
Thanks -- sorry for the newbie-flavor of the question, but I'm sure a gazillion people have already been down this decision branch.
Dan Ribar
View 5 Replies
View Related
Jul 4, 2007
I create a distributed database for mobile application. I replicate a table that distribute on mobile device. I follow instruction how to create distributor, publication, replication, web synchronization, and subscriber database. I have done fine for synchronization between mobile database into desktop database (in this case SQL Server 2005 Standard Edition). But the problem is how can setup publication so it can bidirectional, not only from mobile database into desktop database, but also from desktop database into mobile database. So in the mobile database can have same data with desktop database even on mobile database lost some old data.
Its like data exchange between both engine. Desktop and mobile have same data. For filtering I can put filter on the desktop server for replicated table, so don't worry how I split the data.
Thanks a lot.
View 1 Replies
View Related
Aug 31, 2007
Hi guys,
We have been looking for books which cover the DB synchronisation subject. We have started creating a plugin to our socket server but we quickly realised how much of a challenge it would be (We are creating it "from scratch"). The conflict problems, the order to tables (rows) be synchronised (Child - Parent) and how to delete rows were some of the problems we found. The main goal is to synchronise devices in the field (Compact framework) to the server using less bandwich as possible.
We are trying to be "agnostic" about the DB in both Server and Client side.
We are pretty sure there are books out there which cover this subject, we just couldn't find them!
We know this will be a challenge but we also know it will be a great feature to add to the server.
Does anyone could point me out to any book?
Cheers
Jean Baro
Dynamic Devices Ltd
View 3 Replies
View Related
Jun 15, 2007
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies
View Related
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
Jan 17, 2002
Hi,
In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don
View 1 Replies
View Related
Aug 22, 2007
Hello,
I have this problem on a Production database.
DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.
Thank you.
View 4 Replies
View Related
Mar 6, 2007
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
Jul 28, 2006
Hi,
I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows
- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem
- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine
however.......
- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error
- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.
I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.
I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc
Any advice appreciated !
Thanks
Ronan
View 2 Replies
View Related
Jun 28, 2007
Hi all,
I have recently setup a transactional replication in MS SQL 2000. After setting up the replication the clients TempDB grew by almost 60GB. Now the client is Blaming me for the TempDB GROWTH and saying that its because of the replication being setup i tried to convince them but they are not satisfied yet. Can anybody please tell me does replication cause the tempdb to grow. If yes then how. can u suggest any good link for getting to know the internal working of SQL Server replication????
Thanks in advance
Jacx
View 3 Replies
View Related
Aug 30, 2007
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
View 3 Replies
View Related
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
Apr 17, 2007
Hello everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony
View 11 Replies
View Related
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
