Replication :: Replicate Data From 3 Publishers To A Single / Central Subscriber Transactionally?
Oct 15, 2015
Is it possible to replicate data from 3 publishers to a single/central subscriber transactionally? In other words I have Server A, Server B, Server C with databases A,B,C respectively. I need to replicate 2 articles from A,2 from B and 2 from C to a central Server D that hosts database D. D will have only 6 articles. The replication is Transactional Replication.
If it is possible what will be the drawbacks of such implementation? (if one server goes down will the whole replication break?) If not possible then what is the best way of implementing this?
We need to replicate multiple databases (publications) to one central subscriber. The schema of those articles are identical in all publications and also the primary keys in publications do not have any overlap.
Is this possible?If yes is there any specific thing that I should consider for it's implementation? Should each publication has it's own dedicated distributor or all of them can share one distributor?
there are several remote locations where sql is running, my company has asked me to find a way to collect all the data from the remote locations to a central location automatically,for example day to day data should be synced at night time from 2am to 7 am and it should be compressed automatically before data transfers to the central location. NOTE there is no domain only standalone workstations
In my situation I have multiple transactional publishers replicating to a single subscriber in SQL2005. When making table changes (like adding a column) I want to be able to add the column to one publisher, let it replicate and then add the column to the remaining publishers and have it not replicate (because it would error trying to add a column that already exists on the subscriber). So I thought I could set the first publisher to "Replicate Schema Changes = True" and the remaining to False. Then add the column to each publisher and then insert new rows in each publisher table. It works for the first publisher, but the remaining publishers fail with "stored procedure sp_MSins_dboMYTABLE expects parameter @c3 which was not supplied". The remaining publishers don't seem to recognize the new column to pass the value the subscriber sproc.
I feel I'm missing something basic here. Is there a way to fix this? Or a better way altogether?
I have scaled out an application so that multiple servers contain identical production databases (various clients use different server databases.)
I have replication configured so that the same tables from these different databases are published into single subscription tables (to provide data warehouse and reporting across all production databases). The publications are set up to replicate ddl.
Everything works wonderfully except I have just discovered the need to alter the schema of a table that is within the production databases.
When I apply a script to change the ddl in all the publication databases I am getting errors in replication. I understand that I am NOT supposed to change an uderlying subscription table (this should be done through replication of the schema.) I suspect that the replication error is caused when the schema changes are replicated from the very first Publication update. What I'm effectively doing is changing the subscription table independently (and prior to) changing the schema of the OTHER (subsequent) publication table schemas. I experimented and unsubscribed my target table from all but ONE publication, and then my schema change is fine and replication is happy. But when I have multiple publications feeding the subscription I am not able to propagate the schema change across replicating publications without breaking replication. I am sure that other people out there must have similar situations as mine, where multiple publishers update a single subscription. How are you able to update schema successfully? Many thanks in advance, Normajean P.S. I have posted the script that I am running below....
And here is the error I get in Replication Monitor when I try to run the script: The index 'Idx_FacilityStayPayer_facStayID_PayerID' is dependent on column 'payerID'. (Source: MSSQLServer, Error number: 5074) Get help: http://help/5074 The index 'Idx_FacilityStayPayer_payerID' is dependent on column 'payerID'. (Source: MSSQLServer, Error number: 5074) Get help: http://help/5074 ALTER TABLE ALTER COLUMN payerID failed because one or more objects access this column. (Source: MSSQLServer, Error number: 4922)
------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ -- make the facilityStayPayer.payerID column non nullable
-- drop all of the foreignKeys and indexes that reference the column IF EXISTS (select constraint_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name = 'facilityStayPayer' and constraint_name = 'FK_FacilityStayPayer_Payer') BEGIN ALTER TABLE dbo.FacilityStayPayer DROP CONSTRAINT FK_FacilityStayPayer_Payer END
GO IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'Idx_FacilityStayPayer_payerID') BEGIN DROP INDEX [Idx_FacilityStayPayer_payerID] ON [dbo].[FacilityStayPayer] WITH ( ONLINE = OFF ) END GO IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'Idx_FacilityStayPayer_facStayID_PayerID') BEGIN DROP INDEX [Idx_FacilityStayPayer_facStayID_PayerID] ON [dbo].[FacilityStayPayer] WITH ( ONLINE = OFF ) END GO IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'UNQ_FacilityStayPayer') BEGIN ALTER TABLE [dbo].[FacilityStayPayer] DROP CONSTRAINT [UNQ_FacilityStayPayer] END GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u') BEGIN -- alter the column ALTER TABLE FacilityStayPayer ALTER COLUMN payerID UNIQUEIDENTIFIER not null END GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u') BEGIN
-- add all of the indexes and foreign keys back ALTER TABLE dbo.FacilityStayPayer WITH NOCHECK ADD CONSTRAINT FK_FacilityStayPayer_Payer FOREIGN KEY (payerID) REFERENCES dbo.Payer(payerID) ON UPDATE NO ACTION ON DELETE NO ACTION END GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u') BEGIN
CREATE NONCLUSTERED INDEX [Idx_FacilityStayPayer_payerID] ON [dbo].[FacilityStayPayer] ([payerID] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] END GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u') BEGIN CREATE UNIQUE NONCLUSTERED INDEX [Idx_FacilityStayPayer_facStayID_PayerID] ON [dbo].[FacilityStayPayer] ([facStayID] ASC, [payerID] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] END GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u') BEGIN ALTER TABLE [dbo].[FacilityStayPayer] ADD CONSTRAINT [UNQ_FacilityStayPayer] UNIQUE NONCLUSTERED ([facStayID] ASC, [payerID] ASC) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] END GO
I am trying to acheive the central subscriber model for Sql server 2005 replication. I have two publishers and the centrol subscriber. Both publishers are Sql server 2005 Workgroup edition. The following table shows the Software I have used for the test.
SQL version
Service pack
HotFix
Publishers
SQL Server 2005 Workgroup edition
2
1
Subscriber
SQL Server 2005 Enterprise edition
2
1
I have encountered an obstacle in the replication process to achieve the Central subscriber model. I have configuration table, It exists on servers TB1 and TB2 and want to amalgamate the data to a central subscriber. There are two duplicate primary key and overlapping values existing across the 2 publisher tables. These records need to be updated during the process of replication.
Normally, the default Article properties settings are used 'DROP existing table and re-create it', however here we don't want this setting, because the one table records will then be removed, leaving just the data from the other table. So, we modify the article properties on the snapshot tab to 'Keep the existing table unchanged'€™. As result we got the PK violation error.
I have tried using -SkipError option in distribution job Replication Agent Profiles, that did not work.
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
Hi, We have an existing merged replication schema that works well. One of the tables is named audit. Currently this is a bidirectional transfer. However, we want a new audit table that will transfer data from the subscriber to the publisher and not the other way round. I have a script that was generated as a backup script when replication was first created. I have no idea which parameter to change in order to have the merge replication going from subscriber to publisher. Any advise is helpful. Thanks.
Set publisher database table column to 1. Replicate table to subscriber (column is also 1 on subscriber once it is replicated). Set subscriber database column to 2. Update publisher database column to 1 (even though it is already 1). Change is NOT replicated to subscriber (it stays at 2).
This means that replication is comparing the insert statement to its current value in the database. Is there any way to change this behavior? We want the the column to replicate to the subscriber.
I have the merge replication (Push)Â for SQL Server 2008.The right-click option for Start and Stop Synchronizing in subscriptions has been disabled. How can I stop the merge replication and start again ?
This error happened 1 week after I created the merge replication. The merge data cannot sync to subscriber and only solution I can use is drop and re-create the merge replication again but error would be re-occur a week later. The merge replication work fine before and start from middle on May 2015, but it keep happen right now.
Dear group,i have the following setting:satellite databases in different locations with the same structure (andsometimes identical data) need to be replicated into one centraldatabase. The databases hold information on machine results andmachines are numbered from 1 to n.Results for machine 1 in location 1 should be entered in the centraldatabase whereas results for machine 1 in location 2 should also beentered.Thus i would need a copy of the tables in the satellite database whichadds one column for "location id" (or something like that).Is there an easy way to accomplish this ? Seems to me that thereplication scenarios in the "replication wizard" for SQL-Server missout this case.Some hint at documentation or websites would be extremely helpful.Thanks + Greetings from ViennaUli
Using SQL 7.0 I'd like to replicate just schema from DB on server A to DB on server B, then be able to replicate data only form DB on server B to DB on server A. I need help!!
Thanks for ANY information you can give me... ~Jepadria
I'm trying to set up snapshot replication between two SQL Server 2000 databases over the internet. Both servers run Windows 2000 server edition and SQL server 2000. The idea is to send certain local information, stored in database tables to a database from which the information is published on a website.
I've set up the distribution database (with the original name: distribution) and connected a publisher to it (let's call that one "source_database") using the sp_adddistpublisher stored procedure. No problems there... Next step, I added a publication, the tables in source_database, and a subscriber, the website database. I've opened up port 1433 on both servers, but still it won't send anything from the source_database to the website database....
It gives no errors, but just doesn't do anything... Now, I've looked for possible problems and I think these problems might be able to cause problems: - The initial snapshot is not reaching the subscriber - I've made a push subscription which may has to become a pull subscription - The RPC's aren't available, because both servers are highly secured - The wrong serverpack is installed (this one is a wild guess)
Does anybody have any idea on how I can get my distribution working or solve any of the problems above, cause I'm running out of idea's.... (P.S. I'm a bit of a noobie to replication, I've set it up in a test environment before and it just worked perfectly, but the real thing isn't)
I am having two NT/SQL Server 6.5 and i tried to replicate the database on SQL Server from One Server to another Server. When i try to replicate from Server A[Publisher] to Server B[Subscriber] it is giving error on Distribution History Log on Manage Scheduled Task Dialog. The Error is like this : 08001 [ODBC SQL Server Driver] [dbnmpntw] ConnectionOpen (Createfile()).
and it is trying again and again by using retry option.
Kindly guide me by mentioning the reason for this error.
We have a two SQL Server 2005 databases set up using Transactional Replication. My manager has asked me to set it up to replicate constraints (default, fk, et) on the tables in the main publication. I said sure, I can do it, but I will have to re-initialize the subscription. He said there was a way to do it without re-initializing the subscription. I cannot find anything in my research to indicate that there is a method to do so (via call sp_ functions, etc). Is it indeed possible to replicate constraints on replicated tables without re-initializing the who subscription? The reason he does not want me to perform a re-initialization is that we are a few weeks before moving into production, and a full re-initialization takes 1.5 days, which would impact system availability.
I have some issue when replicating a view (transactional replication).I have following objects on publisher:
Table: [Metadataschema].[Entity] View: [dbo].[EntityLogicalView] the view definition is:
create view [dbo].[EntityLogicalView] as (SELECT * FROM [Entity] WHERE OverwriteTime = 0)
this is what I get when I run "Script View as Create". The same query is used by the replication.Unfortunately this query fails on the subscriber "Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber". I cannot even runt his query manually on the subscriber, it only works when I change it like this:create view [dbo].[EntityLogicalView] as (SELECT * FROM [Metadataschema].[Entity] WHERE OverwriteTime = 0) Is there any way to get this working? Can I change the creation scripts used by the replication or force publisher to include schema names in all objects?
I'm trying to replicate client data from multiple databases into a single table. So database A, B and C replicate client data to a table in database Z. There is a settings table on A, B and C that holds the businessID and I use a function in a computed column to add this to the client table. At the moment I have just database A and Z as a test and what happens is the computed column gets replicated as a computed column with the formula.Â
Can I change this so that I can replicate the computed value (at db A,B,C) of this column?
I don't really want to add a businessID to the table if possible as I'm going to need to add a number of other tables to this replication which will each need to have the businessID, a computed column would be much easier and save updating a load of scripts.
If the above is not possible is there some other solution available so I can identify which database the records came from in the table on database Z?
I have two SQL 2005 SP2 Server and want to use the transaction replication with updateable subscriber. Now the problem is that i can do any changes on the master server. But if i want to change a record on the subscriber which contains a ntext, text, oder image column - then i geht the error that the field will be NULL on the master.
Is there any solution to fix this problem? I dont wan`t to change the datatype vom ntext to varchar(max) !!
I am using the "Pull Subscription Wizard" to set up merge replication. I go through all the steps of the wizard and then I hit "Finish" at the end. When it gets to the third step (Creating the Subscription), I get the old:
__________________________________________________ _________ mmc.exe has generated errors and will by closed by Windows. You will need to restart the program.
An error log is being created. __________________________________________________ _________
This happens whether I try to do this from my PC or from the actual server I am creating the subscription on.
Has anyone else had this issue? Is there a hotfix I need?
I hope anyone here can help me with this tough problem:
I have a replication enviroment with several subscribers. Publisher:MSSQL 2000, SP3 Subscribers: MSSQL200, SP3 and MSDE2000, SP3
Since a few weeks, one of the subscribers fails when synchronizing with the message "This process could not enumerate changes at the subscriber"
The erroroutput with verboselevel 3 doesn't help me much, I append it to the end of this posting. (unfortunately german)
I heard that this error can be caused by a "blank" in an image column or different Service Pack Versions - definitely not possible in this case! The only strange detail is that the SQL Server Versionnumber of this MSDE shown via EM is not the same as the number shown in controlpanel->software. By the way, is it ok that here are two lines for the MSDE? SP3 is definitely installed on all machines!
Any help would be greatly appreciated - i'm already losing hope... Best Regards, Gert
Microsoft SQL Server-Merge-Agent 7.00.623 Copyright (c) 1998 Microsoft Corporation
Percent Complete: 0 Verbindung mit Abonnent 'MyServerAT074' Connecting to Abonnent 'MyServerAT074.MyDB' MyServerAT074.MyDB: {call sp_MSgetversion } Percent Complete: 0 Verbindung mit Verteiler 'MyServer1s' Connecting to Verteiler 'MyServer1s.' MyServer1s.: {call sp_MSgetversion } MyServer1s.: {call sp_helpdistpublisher (N'MyServer1s') } MyServer1s.distribution: select datasource, srvid from master..sysservers where srvname = N'MyServer1s' MyServer1s.distribution: select datasource, srvid from master..sysservers where srvname = N'MyServerAT074' MyServer1s.distribution: {call sp_MShelp_merge_agentid (0, N'MyDB', N'MyDB', 9, N'MyDB')} Percent Complete: 0 Initialisiert MyServer1s.distribution: {call sp_MShelp_profile (19, 4, N'')} Percent Complete: 1 Verbindung mit Verleger 'MyServer1s' Connecting to Verleger 'MyServer1s.MyDB' Connecting to Verleger 'MyServer1s.MyDB' MyServer1s.MyDB: {call sp_MSgetversion } Percent Complete: 3 Ruft Publikationsinformationen ab Percent Complete: 4 Ruft Abonnementinformationen ab Connecting to Abonnent 'MyServerAT074.MyDB' Disconnecting from Abonnent 'MyServerAT074' Percent Complete: 4 Uploadet Datenänderungen zum Verleger Percent Complete: 5 Ruft die Liste der Löschungen von MyServer1s.MyDB ab Percent Complete: 5 Verarbeitet Artikel 'First'
...... SNIP .... Percent Complete: 5 Verarbeitet Artikel 'LastTable' Percent Complete: 0 Der Prozess konnte die Änderungen auf dem Abonnenten nicht aufzählen. Percent Complete: 0 Category:SQLSERVER Source: MyServerAT074 Number: 0 Message: Attributverletzung eingeschränkter Datentypen Attributverletzung eingeschränkter Datentypen Disconnecting from Abonnent 'MyServerAT074' Disconnecting from Verleger 'MyServer1s' Disconnecting from Verleger 'MyServer1s' Disconnecting from Verteiler 'MyServer1s'
I want to call a stored procedure to do the insert into my published arcticle/table. Is this possible? I know you can call a stored procedure to insert into the subscriber arctiles.
The problem is the published database has a generated primary key (max +1 )that is calculated via a stored procedure during all inserts. I would bring the stored procedure over to the subscriber but that would not help since the values in the target table will not be 'current' because the subscriber database is only updated nightly.
I am tring to use SQL Server Mobile 2005 to subscribe to a SQL Server 2005 publication from a pocketPC. In my code I have a SqlCeReplication object. One of the properties that must be set is: Subscriber, but I have no idea what that should be set to. When I was setting up the publication I don't remember specifying anything like that, and the class description of this property doesn't help: "Specifies the name of the Subscriber". Any help will be appreciated.
I have tried backing up the replication from Primary Server (Publisher) A and copying over to Subscriber B. When I sync. I am missing the data. I don't know what step I am missing or it is bug on SQL 2K SP3.
I don't know what i am doing wrong.
Can anybody give me a step by step detail how to backup the publisher database and copy to a subscriber and sync by saying the schema and data already available. As we have WAN problem all the times, and when I try the full snapshot of 20 gig it was taking more than 24 hrs and also if the connection fails my snapshot also fails
I installed 2 SQL2000 servers at a client site, the first acting as a publisher and distributor for transactional replication. The second is a push subscription to the first. The client wanted to change the database name, and some other stuff, so I removed the replication and set it up again for the new database name.
All's fine at the publisher / distributor but the subscriber is showing as still having active push subscription to the old (and new) databases
I am using merge replication with a push subscription type. I am wondering if the updates of the tables on the subscriber side are push to the publisher from the subscriber or pulled from the subscriber by the publisher when the syncronisation takes place. this makes a big diferrence for me and i can't find the answer to this question anywhere...
if anyone could answer it would be really appreciated
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.
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).
My manager does not want to go through the process of moving 40 GB of data to the subscriber database over the network as would occur during the initialization phase of replication (exact method of replication has yet to be determined). The subscriber db is for Web Access and will be read-only. He wants to back up a db and use tapes to restore the db on the subscriber db server, and then activate replication. I have told him that you have to allow SQL Server to create the objects on the subscriber side and you have to allow SQL Server to control the data flow, that manually creating the objects on the subscriber side will not work. He says that I am incorrect.
Who is right? I haven't been able to find an answer from the Microsoft site and news groups.
