Replicating DDL From Multiple Publishers To One Subscriber

Feb 22, 2007

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?

Thanks!

View 4 Replies


ADVERTISEMENT

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?

View 3 Replies View Related

Add A Column To Published Table Without Replicating To Subscriber

May 25, 2006

I would like to add a column to a published table but not have that column replicated to subscribers. I can accomplish this via the UI by adding the column and then unchecking it. This adds the column to the publisher table but does not replicate it to the subscriber.

I am looking for a programmatic method to add a column to the base table and unmark it for replication.

Thanks for your assistance.

View 4 Replies View Related

Replicating Multiple Tables

Aug 20, 2007

I can't seem to find the answer to this in the documentation.

I have several tables that I want to replicate via transactional replication. These tables do have some foreign keys between them.

What are the advantages/disadvantages of "one table per publication" versus "one publication with all of these tables listed as articles"?

If I do not define these in the same publication, will I get into trouble with the order that data is loaded to satisfy the foreign key constraints?

View 1 Replies View Related

Replication Multiple Subscriber Database

Nov 28, 2007

I have a central server (CS1) with database CDB and a branch server (BR1)
Inside the branch server, there are two database DB1 and DB2.
CDB contains data for DB1 and DB2

I made a subscription in the brancserver named BR1:DB1(this will replicate the data from CDB to DB1)
I also made a subscription for DB2 named BR1:DB2(this will replicate the data from CDB to DB2)

Unfortunately, upon starting the synchronization for BR1:DB1, the data replicated into DB1 also contains data for DB2. same happens with the BR1:DB2..

How do I filter the data that will be replicate per database..Example:only data for DB1 coming from CDB should be replicated into DB1 if that specific subscription(BR1:DB1) is synchronized.

Thank you

View 1 Replies View Related

Distributing Subscriber Database On Multiple Machine - Install

Dec 9, 2005

Hi,

View 4 Replies View Related

Schema Changemultiple Publishers Into Single Subscription Table

Jan 16, 2008

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

View 1 Replies View Related

Replication :: Replicate Multiple Databases (publications) To One Central Subscriber?

Sep 2, 2015

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?

View 2 Replies View Related

Replicating From 7.0 To 6.5

Aug 30, 1999

I need to replicate from a SQL 7.0 server to SQL 6.5 server.
Has anyone done it, or does anyone know if it can be done?


Thanks in advance

Joe Gentile

View 2 Replies View Related

SP2 Replicating With Pre-SP2?

Jul 23, 2007

Hi gang,



I'm 99% sure this is possible, but I wanted to confirm before I go upgrading one box in our replication scheme without having to do all the others (which are geographically dispersed):



Can an SP2 box replicate (merge replication in our case) with pre-SP2 servers? Most of our servers don't even have SP1 applied, and we're ready to upgrade, but I want to be sure I can do them one at a time rather than all at once.



Cheers,

Matt

View 9 Replies View Related

Replicating Views

Jan 11, 2002

Can we replicate views in SQL 2000 or SQL 7.0 ? If yes please let us know How


Thanks

View 1 Replies View Related

Replicating Permissions

Feb 22, 2001

We are performing snapshot replications from one server to another, however the permissions are not being taken across.

When we manually reset them, the next replication will quite happily remove all permissions.

Any help appreciated.

Peter

View 2 Replies View Related

Replicating Design Changes

Mar 5, 2005

I am trying to create an auto off-site backup of an entire database. This would include databases and users. It should also include changes made throughout the day.

Something challenging about this is I want it to also include design changes that may have been done throughout the day.

I understand log shipping or replication can deal with the data part of my solution. But how can I copy over the logins, users, and design changes?

Is it possible to have design changes replicated from publishers to subscribers?

View 3 Replies View Related

Replicating Database

Mar 5, 2004

I am using Sql2000 and have 6 servers. On this 6 servers 4 servers have the same database

MY QUESTION is i need a script or advice that will help to do this:

Every time data chages in 1 of the 4 servers that have the same database, I want all changes to happen in the other 3 so that they must always have the same information

View 1 Replies View Related

Replicating SysTables

Apr 21, 2004

Good Day Gurus

I posted a question about replicating logins to the database and the answer i got about doing a DTS to transfer logins is not good for me, is it possible to replicate syslogins tables so that i can do this if so, how because they are not listed Database and Publications when i try to create a publication, only individual created databases can be seen.

The reason for this is because when the DBA decides to change the user permission, i want the info to be merge replicated to the subscriber. At this moment i can run the DTS to transfer the login but it won't know when the logins have been updated and hence i won't know when to run it.

Thanks in advance

View 2 Replies View Related

Replicating Data

Mar 31, 2008

Hi,

I am very new to SQL Server. Plenty of SQL knowledge but the whoe SQL server enviornment is new.

I am working with SQL Server 2005. My task is to generate reports without affecting our live database. I have setup a second server and installed SQL Server 2005 on that too. My thought was that maybe I could mirror or replicate the table I require over to this new server and run my queries from here. Is this easy to do ?

I read that mirroring might not work as it is solely for back up /fall over purposes and that data on the mirrored server would not be accessible.

I have also been looking at SSIS but at the moment this is all a bit like double dutch to me ! Can anyone point me in the right direction, preferably somewhere beginner friendly ie not overly complicated !!

Thanks a mill,

Sophie

View 3 Replies View Related

Replicating DDL In Sql 2005

Aug 11, 2006

I am using Sql 2005 and merge replication. I am relying on the feature where schema changes are replicated to subscribers but I have come across a situation where schema changes stop being replicated.

This is the scenario:

I create a database and publish it for merge replication.

I add subscribers.

If I need to change the published database I can use ALTER TABLE ddl and the subscriber gets the changes.

If I have to add or remove a merge article as part of a database change I specify the @force_invalidate_snapshot=1, @force_reinit_subscription=1. No any ALTER TABLE statements following the article change will NOT be replicated.



Is this a known 'feature'? Is it because @force_reinit_subscription is set to 1?

Can anyone help me?

Graham

View 5 Replies View Related

Help Please Some Data Not Replicating

Oct 10, 2006

Hello,

We have four mobile devices that are set up for merge replication via the web. We are not receiving errors but some of the data is not coming over to the devices. If we manually add a record that record will come over, but there is data that is on the server that isn't on the devices. If we run the snapshot for each device (We're using host_name as a filter) nothing happens. If we do validation check we get errors. If we reinitialize all devices it works but the next days data (sql job populates data to the publisher db at night) isn't on the device after syncing the next morning. Any help would be appreciated.

John

View 5 Replies View Related

Replicating UDT's To Subscribers

May 21, 2007

I have a sql 2005 publisher and distributor and a sql 2000 subscribers. for some reason on one of the subscribers i'm getting errors that it can't replicate the UDT's. i tried a new snapshot and made sure it was set not to replicate UDT's but i'm still getting Create Type errors.



would anyone have any idea why it's trying to create UDT's at the subscriber when i specify not to replicate UDT's?

View 5 Replies View Related

Replicating Schema Changes

Jun 26, 2006

Hi there,

I'm using Sql 2005 merge replication and I have noticed something, I'm not sure if this is true or not:...

My publication is set to replicate schema changes (replicate_ddl = 1). Now, I have noticed that schema changes are only replicated if the current snapshot is valid. Is this right? If so why?

My next question carries on from the first. If I'm about to run a TSQL script on my publisher that will add a column or two to a published table, how do I ensure my snapshot is valid inorder for the ddl changes to be replcated? Should I be using:

EXEC sp_mergearticlecolumn

@publication = <publicationname>,

@article = <article name>,

@force_invalidate_snapshot = 1,

@force_reinit_subscription = 1

on each table I modifiy, after I have added the new column?

Thanks for your help

Graham

View 30 Replies View Related

Replicating Schema Only

Mar 13, 2006

Hi,

I have a database that is being set up for merge replication (Sql 2005), but there is one table that I only want the schema replicating, not the data - I never want the data to be replicated in either direction. I can see from sp_addmergearticle that you can do something like this for sp's or functions but is it possible to do this for tables?

Regards

Graham

View 1 Replies View Related

Replicating Roles

Nov 13, 2006

Hello,

I use a merge replication between Sql Server and Sql Server Express.

When I enable a DB for .NET features (eg RoleManager), I have new tables and roles that are created and some GRANT are given on SPs.

When I replicate these DB to another one, none of my roles are replicated and I also loose my roles. Is there a way to replicate also the roles and the permissions ?

Thanks

Pierre-Emmanuel Dautreppe

View 1 Replies View Related

Replicating From SQL 2000 To 2005

Jun 26, 2007

I am wondering if there is a way to replicate changes in a SQL 2000 DB to 2005 without backing up the DB and restoring it in 2005.
We are running an ERP system using SQL 2000 and are moving to a later version that supports 2005 and we want to test it out before going live but I'd like to sync with the current system from time to time instead of having to convert the DB and get it ready again and again everytime I want to update the data.
 Thanks for any help you can offer,
Chad

View 1 Replies View Related

Replicating &#34;join&#34; Tables

May 13, 2001

Hi,

I have a number of "join" tables ie joins records from two other tables for example, an employee may be responsible for more than one product so the join table would look like this:

table name: employee_products
Employee_id foreign key from employee table
product_id goreign key from products table


My question is, how do I replication this table? Replication requires all table to have a primary key field. In this case, both fields are foreign keys and I dont have a primary key as the same data appears regularly in either field.

How should I get around this so I can implement replication? I dont want to have to add another field to be the primary key field.

Thanks

View 1 Replies View Related

REPLICATING STORED PROCEDURE

Apr 4, 2000

Hi,
My transaction replication is working perfecly.
I am doing some changes at publication database ,altering stored procedure
code , now i want that changes should also take place at subsciber
any one has idea please suggest me
Thank u.
Nil

View 2 Replies View Related

Replicating Structure As Well As Data

Dec 27, 1999

Are there any known problems with replicating structure changes (New table, etc...), as well as data in MSSQL Server 7.0?

View 2 Replies View Related

Replicating Across Time Zones

May 10, 1999

We're considering one database in the far east, using merge replication with a database in London.

There's a time difference of maybe 7 hours between the two sites - has anyone ideas on conflict resolution (thinking about the Far East updating a record at 16:00 their time, London updates the same record at 15:30 GMT - how does SQL Server know that London's time is the correct record to use?).

Any info gratefully accepted.

Thanks
Dan

View 2 Replies View Related

Replicating Tables In SQL Server

Sep 23, 1998

I have a table that is used for reporting, the problem is that the data in the table is refreshed every 30 minutes with a bulk insert. I am trying to find a way to have two tables that are mirror images of each other and when the loading table is loaded, then the table assumes the identity of the reporting table. The basic prinicpal is I need to have the table be available almost all the time and when the bulk insert is happening, users cannot query. Any help would be greatly appreciated.

Thanks in advance
Marc Brown

View 1 Replies View Related

Replicating Data Across Servers

Jun 17, 1999

We have a database that I would like to replicate on another server but am unable to use regular replication via publish/subscribe due to the fact that the production database has no primary keys on tables, only clustered indexes. The backup db needs to be as close to real-time synchronized as possible and will be in fairly active use most of the time. Has anyone had success in developing such a system? How did you do it and what are the pitfalls? Any advice would be greatly appreciated. Thank you.
W.

View 1 Replies View Related

Replicating Data Over The Internet

Mar 5, 2005

I have SQL Server running on my internal LAN. I want to have a second SQL Server running on a hosted (shared) website. I then would like these servers to talk to each other. At some scheduled time I need to publish data to the web, and I then need to subscribe to data input on the web by various clients. My internal LAN can see the Internet via our cable modem.

What is the best way to do this? What software will I need to run. I'm looking for the big picture.

Thanks for the help everyone.

View 4 Replies View Related

Replicating To An Archive Database.

Nov 27, 2005

I want to replicate to an archive database. This means that the subscriber will have data that has been removed from the publisher. In my reading, I haven't seen any discussion of this specific scenario.

Here's what I imagine the solution might be:

EXEC sp_addpublication_snapshot
@publication = N'My_Publication',
@frequency_type = 1 -- only create the snapshot once
GO

...

EXEC sp_addarticle
@publication = N'My_Publication',
@article = N'My_Table',
@source_owner = N'dbo',
@source_object = N'My_Table',
@del_cmd = 'NONE',
GO

I set the publication snapshot to only execute once, that would be during the maintenance window when it is initially installed. Then, on the tables that will contain archived data, I specify that deletes aren't replicated.

Here's my concern: aren't there times when you need to resync?

If you could push a new snapshot that dropped the tables on the subscriber and built the thing up from scratch, then things would sync-up just fine. But in this scenario if you drop the subscriber tables then you've just lost your archive.

What's the best way to handle this?

Thanks,

-=michael=-

View 6 Replies View Related

Replicating Only One Table, What's The Best Approach ?

Apr 6, 2008

I have a single table on database A that needs to be replicated to two databases B and C in as-near-to-real-time as possible. The table is cleared every morning, and receives about 40,000 records per hour. Records are only added to the table, never updated or deleted.

I'm wondering what is the best approach to replicate this table :

1 - Transactional Replication
2 - A trigger on the INSERT on table in A that then inserts the record into B and C
3 - Timed jobs running on B and C that pull new records from A

thanks all in advance.

View 1 Replies View Related

Replicating Using ODBC Connection

May 7, 2007

Hi

I have EE 05 installed and need to replicated tables from an odbc connection. How would i go about doing this? I cant find a way to like import tables that i need from the odbc connection.

Thanks!

View 3 Replies View Related







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