Schema Changes And Merge Replication

Jan 23, 2006

Hi there,

I wonder if I can ask for some advise? I wish to use SQL 2005 and merge replication but I have a few concerns over the schema changes that I am allowed to do on the publisher.

Ideally I want sql replication to push all my schema changes from the publisher to the subscribers but I don't know if it can. or even if it is recommended.

I see the BOL suggesting that schema repl. was mainly intended for ALTER type of statemenets, but can I use it for CREATE and DROP type DDL too?

For example, if I add a new table at the publisher I presume I have to add a new article too for this table. Then how can I create a new snapshot? Is this even the right way of doing it? Similarly if I want to DROP a table at the publisher I'm guessing I have to also delete any existing articles too, then recreate the snapshot.

Are there better / safer ways of doing schema changes? Perhaps I'm best disabling publishing and asking each remote site / db to update their schema manually and then re-enabling the publisher?

Any help would be much appreciated.

Regards.

View 4 Replies


ADVERTISEMENT

Merge Replication Between Same Schema Databases But Different Data

Jan 25, 2007

Hi all.

I 'm trying to set up merge replication between two servers that have the same schema databases. The two database have the majority of there data the same but as well as data inserted at a later time independently on the two servers. (The two servers were connected in a merge replication scheme that failed at some later  time and replication was paused, but users continout to insert data indepentedly at the two servers.)

I need to get them up and running.

I cleaned replication at both servers, I recreated the publication at the puplisher distributor and all is fine.

When I create a push subscription to the subscriper I get the error invalid column name 'rowguidcol' .

I so far managed to have merge replication running on two identical databases (schema and data).

Just some thoughts. After some reading I found that it might be related to identities and identity range or indexes.  I set the identity seed  and increment at 2, 2 at publisher and at 1,2 at subscriper. (On the same tables at  puplisher and subscriber.) Is that ok? is that the way to do it?

Digging a bit more  Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes.

Thanks allot for any help

Version.

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

View 5 Replies View Related

Merge Replication Schema Sync Problems

Sep 10, 2007


I am using a merge publication set to synchronize schema changes. Why am I getting the following message when I try to sync after having added columns in the publication database using ALTER TABLE statements?

The schema definition of the destination table ... in the subscription database does not match the schema definition of the source table in the publication database. Reinitialize the subscription without a snapshot after ensuring that the schema definition of the destination table is the same as the source table.

Also, why doesn't reinitializing the subscription with @upload_first = false not fix the problem?

View 1 Replies View Related

Error In Merge Replication The Schema Script...

Jun 25, 2007

I'm trying to set up a merge replication. Publication is created successfully and snapshot also, but when I create pull subscription on subscriber server and merge agent starts, after some time I get an error message of this type:

The schema script '\ANILREPLDATAuncANIL_BEJK_BEJK20070625142735dl_HF_vMSCene_3836.sch' could not be propagated to the subscriber.
It seams there is a problem with certain Views and SPs, because tables are successfully created, and some Views and SPs also.

I tried to exclude problematic articles, but every time another one pops up. Up until now, I excluded 7 articles from publication (1 Stored Procedure and 6 Views) but I still get errors.

I gave up because I can't exclude half of the Views and SPs just to make it work.

Is there something that can be done to solve this problem?

Thanks!

View 3 Replies View Related

Table Schema Changes Not Being Propagated - Merge Replication

Dec 9, 2005

Hi,

View 9 Replies View Related

Replication :: Merge Rep Schema Change And Processing Order

Jul 14, 2015

Question re Merge rep (pull) and processing order.  We have a group of changes associated with an app upgrade, the scripts run fine on the publisher.   Part of the change includes creation of a new table , followed by altering a view to use new table.Following the change at the publisher, when the sync is kicked off from the subscriber, it fails - the alter of the view throws an'invalid object' error with regard to the new table.  Seems as if the view alter is attempted before the dependant table has been created. 

I have tried to amend the processing order of the view using sp_changearticle, which executes (quickly) with a 0 return code.But it is to no avail , the error still occurs.   is it possible to change the processing order for a view article , which will be applied to schema changes ? Have

View 4 Replies View Related

How To Handle Schema With Sql Server 2000 Merge Replication

May 29, 2007

I have a problem with sql server merge replication in sql server 2000.
If my db owner €œdbo€? and replication setup under €œsa€? account it works with out any problem. But when I use another db owner it can not work properly.
For an example I have customer table ([dbo]. [Customer]) When I setup merge replication under sa account it€™s work properly.
Again I was setup merge replication using another db owner ([INV]. [Customer]) It doesn€™t work.

View 3 Replies View Related

Problem : Extreme Activity - Slow Merge Replication - From A Schema Update

Jul 4, 2007

Hi there,



We have a big problem here with merge replication, specifically whenever a schema change occurs. We are replicating schema changes, and triggers/stored procedures. The example is that we changed about 150 stored procedures and about 30 triggers. This is then replicated to the subscriber database (which is also a merge publisher for further remote systems that were offline at the time) over a 10Mb link - hardly low bandwidth. However, the replication process takes about an hour and a half - considering the SQL on the primary server took less than a minute to run this is a big suprise.



We've run a trace to see if we can identitfy what is going on. There seems to be a great number of calls to sp_MSunmarkschemaobject - we are still waiting for a trace to complete to fully analyse this however it looks like it calls this repeatedly for every stored procedure in the database. We are currently re-testing to one of the remote servers with the merge agent set to the slow profile (not much hope that this will alter the poor performance).



This task looks to be excessive - and certainly does not seem to function in a sensible manner. Has anyone else had similar issues or have any suggestions. This is very infuriating as it means that the servers are effectively offline for a minimum of and hour and a half (in fact the remoter servers take over 4 hours !).



Thanks.

View 2 Replies View Related

Merge Republish Schema Changes

May 4, 2007

Hello,



I'm working on a replication topology that is completely merge. We have a single consolidated instance (SQL 2005 SP1 Standard) that holds all data and is a continuous push merge publication filtered by region to regional instances (SQL 2005 SP1 Standard). Then we have individual user instances (SQL Express SP1) that pulls from the republished regional instances which is filtered by user. Both publications have Replicate Schema Changes set to true.



I'm testing out changes to tables and sps on a test system I've been using this process:



1-Run Snapshot on the Consolidated instance

2-Verify all published articles have a status of 2 in sysmergearticles

3-Run Regional Snapshot

4-Verify all published articles have a status of 2 in sysmergearticles

5-Run alter table scripts

6-Once all three levels have the table changes, run the alter sp scripts



I've gotten to step 5 and and the changes get replicated to the regional instance just fine however only the existing column changes get replicated to the SQLExpress instance, not the new columns. Looking at the articles in the regional publication it shows the new columns, but they are not selected. I know I can manually select them (or probably write a script that adds them to the publication although sp_repladdcolumn has been depreciated), but isn't there a way to make this a completely automated process since it's just a republished database? Also is the process I'm using the correct one?



Thank you,



Aaron Lowe

View 7 Replies View Related

Schema Changes On Merge Publication On SQL2000 And SQL2005

Apr 17, 2007

Hello



I've got the following problem: Our product is delivered with SQL2000 and SQL2005.

Now, there are some schema changes, which I'd like to deploy with T-SQL on the publishers. With SQL2000 I do it with the sp_addmergecolumn etc and on SQL 2005 (if replication compatibility level is 90) with replication of DDL. So far so good.

But how can my T-SQL Script determine, wether the replication of DDL is on? I know there exists sp_helpmergepublication, but how do I get the column replicate_ddl of the result-set?



thanks for your advices.

Aline

View 5 Replies View Related

Replication :: Difference Between Snapshot And Transaction And Merge Replication?

May 26, 2015

What is the main difference between snapshot and transactional and merge replication?

View 5 Replies View Related

How Do We Add A New Column To A Merge Replication Article, But Specify It As Not For Replication?

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

Schema Changes After Replication

May 18, 2006

I see from help books that table changes are not allowed using Enterprise Mgr. after a publication exists. The only way to make db changes is to use T-SQL. I found the correct sp to make changes below.

I am using

sp_repladdcolumn <tablename>,<columnname>,<datatype(25)>

sp_repladdcolumn does not allow datatype length to be supplied. Result defaults to 1 and sets to NULL.

I need a variable length on my datatype. Is this a syntax error on my part or am I not able to supply this parameter?

How to accomplish?



thx..bt

View 1 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

Replication Of The Schema Of A SQL Database

Nov 12, 2007

Hi all,
I am doing a replication of a SQL Server 2005 database from one server to another server, everything worked fine. Now, when I added some fields to this database, the replication stopped. can I do replication of a schema of a SQL database? how? Should the replication work even if I add some fields.

View 3 Replies View Related

Replication And Schema Modifications

Mar 30, 2000

SQL Server 7.0 (Service Pack 1 )

A table is currently set up with Transactional Replication. A new field needs to be added to the table. The system needs to continue to be live. What is the best way to handle this kind of situation?

The database model cannot be frozen once replication is in place, and a fresh snapshot cannot be taken each time this occurs since this is very timely on large tables, and errors could occur with other tables replicating since there are foreign key constraint in the Target database.

I haven't been able to find a way to be able to continue replicating the old records which may have been in the log during the addition of the field, as well as the new records with the new field. I must be missing something, since the model must be able to be altered once replication is in place, without the shuting down of the entires system.

View 5 Replies View Related

Schema Changes And Transactional Replication

Feb 9, 2004

Hi

If a column is added to a table which is a published article in a transactional replication, is it sufficient to do the following?

- drop subscribers
- amend the publication by dropping the affected table, then re-adding it
- re-pushing to subscribers

Or do I have to drop the entire publication and recreate it?

TIA

View 8 Replies View Related

Three Level Replication Schema

Nov 24, 2006

Hi I have to questionsproblems I would need help with. First I am creating a three level merge replication. I mean I have a main
publisher with its distributor, a subscriber/publisher and also with
it's own distributor and lastly a subscriber.
I start by creating the relation between subscriber/Publisher and the
subscriber and than I move up to the main Publisher and
SubscriberPublisher relation. After this second merge I get the
following errors and the articles in my SubscriberPublisher are
lost...


The merge process could not retrieve article information for
publication 'I-Test-Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147201017)


---------------------------------------------------------------------------­­------------------------------------


Cannot insert the value NULL into column 'tablename', table
'@tmp_table'; column does not allow nulls. INSERT fails.
(Source: DEVSERVER3MSDE (Data source); Error number: 515)
---------------------------------------------------------------------------­­------------------------------------


Failed to allocate new identity range.
(Source: DEVSERVER3MSDE (Data source); Error number: 21197)
---------------------------------------------------------------------------­­------------------------------------



Secondly








I have set up a two level merge replication with a publisher and a
subscriber and had modified the structure of a table in the publisher
with sp_repladdcolumn. During the day all was fine I continuously
tested it by adding more columns, droping columns and testing the
replication itself with data entry, but when I got back
the next morning my test table and all other table I presume had lost
its data and structure. The conflict table for the corresponding test
table had kept it's structure but no data were present since no
conflict had occured. This is a recuring problem that some colleagues
had.
Does anyone have any idea what might cause this problem and why it
happenned.
Thanx

View 7 Replies View Related

Unmatched Schema - Transactional Replication

Mar 18, 2008

Can you use Transactional replication for exchange data between tables where schema is not matching?



------------------------
I think, therefore I am - Rene Descartes

View 6 Replies View Related

Schema Change - Snapshot Replication

Apr 16, 2007

How can I keep schema changes in the subscriber's database when I replicate the snapshot from the publisher?



I just want to move data from a remote server, but it seems that the tables are being dropped which is not good as we use the subscriber as a development box.



Please advise

View 7 Replies View Related

SQL Server 2008 :: Snapshot Replication To Different Schema Name

Apr 25, 2015

I need to setup snapshot replication for 1 large table.

Source table: SourceDatabaseName.dbo.MyTable1
Target table: TargetDatabaseName.CustomSchema.MyTable1

In the above example, how can I specify the target DB's CustomSchema name (it's currently creating dbo, not CustomSchema)

View 1 Replies View Related

SQL Server 2008 :: Schema Changes With Transactional Replication

Jun 15, 2015

How do we know our schema changes will propagate to subscriber without breaking the replication? Is there any t-sql command to find out the option true or false?

View 2 Replies View Related

How To Reflect Schema Changes At Subscriber Using Transactional Replication

May 16, 2007

Hi

I need to setup transactional replication for a production server.

The tables (articles) at publisher being replicated can expect schema changes.



Is there any way to reflect schema changes at subscriber using transactional replication.



Early Thanks,

Salman Shehbaz.

View 1 Replies View Related

Schema Properties/attributes Not Transferred By Transactional Replication

Jun 15, 2004

Apart the IDENTITY property, what other properties or attributes are not transferred to the target schema?

I know that one can use NOT_FOR_REPLICATION for identities, but I am interested in a (complete?!) list of metadata objects that transactional replication *prefers* not to transfer across to the target by default.

TIA

View 2 Replies View Related

Replication: Any Way To Change Table Schema W/o Reinitializing Subscriptions

Mar 23, 2004

Hi all,

I'm admitedly a bit new to the world of replication, so please bear with me. I've got two SQL Server 2000 servers running in different locations. Server A does transactional replication over a push subscription to server B. If I need to make a minor change to one of the replicated tables (for example, dropping a no longer used column or changing a varchar field's length) do I need to drop the subscription, make the changes and then re-initialize the schemas and data?

For minor changes, I really hate having to knock out the site runnign off server B while the subscription is re-initialized and data is bulk copied back over. If I want to just make the changes manually on both servers will that cause problems down the line?

Any help is greatly appreciated.

tia.

-m

View 2 Replies View Related

Snapshot Replication And Schema Change On Sql Server 2000

Aug 10, 2006

can a sql server 2000 snapshot replication

solution handle a schema change.



as far as i know i can't drop a table involved in replication

View 4 Replies View Related

Replication - Are Schema Changes Replicated If The Sanapshot Agent Runs On A Schedule

Mar 4, 2003

I have Snapshot set up as a schedule task between server A and server B.

If I make changes to the db schema on server A - the changes are not replicated to server B.

Is this normal?

Do I have to restart the Sanapshot agent each time there is a schema change?

Thanks,

Bianca

View 1 Replies View Related

SQL Server Admin 2014 :: Skip Schema Changes For Transaction Replication

Jun 6, 2015

Whenever I am doing reinitialization, publisher table schema is applying at subscriber level

I don't want to apply all changes ...

How can I skip the schema changes for transaction replication ...

View 1 Replies View Related

One Way Merge Replication

Feb 14, 2002

Hi everbody,
I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.

Thanks in advance

View 1 Replies View Related

Merge Replication

Aug 12, 2002

Hi,

My production box is running on NT4.0,SP6, SQL Server7.0,SP2. We implemented Merge replication. Working fine last 7 months. Last weekend i disabled replication, Successfully removed Distributor and Publishor. After that try add new fileds but won't allowed me. It's give the error message. I Also found Some Conflict_tables found almost 20 tables. All system Tables. Can delete these these tables, if i delete any problem my database.
I added filelds many times but this time i got errors.

Please help me anybody.

View 6 Replies View Related

Merge Replication

Jul 31, 2000

I have just installed replication on our production server to Merge Replicate with a Laptop server that will travel from time to time. I have now noticed that we cannot add or change any fields or attributes on the tables which are being replicated (which are all tables in the DB). This is a problem because we are changing and adding columns all of the time. Is there a way around this issue like shutting down the replication service or something? I have been unsuccessful in finding a way around this other than removing replication while we make changes.

Thank you in advance for any help!

View 1 Replies View Related

Merge Replication

Jan 22, 2001

I have implemented a Merger replication on our development server and I get a fillowing error when I try to update one of the table in publisher.
"Transaction cannot start while in firehose mode"

What does this mean.

Thank You,
John

View 1 Replies View Related

Merge Replication

Dec 19, 2000

Hi,
I read some where that replication has two types conflict resolution, 1. row based and 2. Column based...
If I am right...
Can any one point me how to find out this option and how to set it up....

Thanks,

Mohammed.

View 1 Replies View Related







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