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
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
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
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
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
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
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
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
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
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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