September 16, 2005 at 1:54 pm
Hi.
I've 3 databases (on different sites) that are under merge-replication.
Now, the problem is that the 'schema' of this database can change quite often (adding a particular column to a table, adding a table, etc....).
I know that there are specific stored procedures which allow me to perform changes to my schema, but I'd rather not use those procedures. The reason is quite simple: my database schema is in a Visio file, and when I have to update my schema, I generate a 'patch' via the RedGate tools.
I've created a little program which allows me to setup my merge-replication very quickly via SQLDMO (using this program I can define what servers are to be the subscribers, define my publication(s), etc....).
Now, offcourse, when I have to do a database-change, I cannot do this when my databases are under replication. Therefore I've been playing with the idea to 'tear down' the replication process, doing the DB changes, and setting up the replication again.
Off course, the problem with this is, that I do not know whether or not it is 'safe' to tear down my replication. I have to be sure that my 3 databases are 'in sync'.
(Before I tear down the replication, I can make sure that no one can connect to the DB). Is there any way to know for sure if my databases are in sync ?
Are there better options do handle this kind of problem (altering the database without using the specific procedures).
September 18, 2005 at 3:35 am
I've been thinking:
maybe I can do something with the sp_msenumchanges stored procedure to see if my databases are in sync.
September 21, 2005 at 2:57 am
Hi,
If you are unsure of using the specific sp's then it is quite reasonable to drop the merge replication to apply any database changes and then re-apply. Of course, in order to save you the pain of pushing out a new subscription it is a good idea to synchronise the data before dropping the replication. I am making the assumption that you have specific users that are configured to make changes to the database..if this is so then put the databases into DBO Use Only , and then run the merge agent a few times to synch the data. Once done you can drop the rep.
One thing to look out for when you re-apply the replication is the identity ranges and current identity values.
HTH...Graeme
September 22, 2005 at 12:08 pm
Hi, thx for the reply.
I'm not using identity columns, so this will not be a problem. My primary key's are GUID's (which are also the rowcolguid).
About putting the DB in 'restricted user mode': I've been thinking on this as well. This is something I surely have to do.
But, why should I run the merge agent a couple of times ? Isn't it enough to run it once ?
September 23, 2005 at 1:44 am
Well, all you are really trying to do is make sure the data is the same both ends of the replication. You can always do a row count to check the synch. I have just found that runnung the agent two or three times will make sure the backlog of transactions are cleared. 99 times out of 100 this is probably not necessary..but..you never know.
You don't have to put the DBs into DBO Use only to drop and reapply merge rep. It's more of a safe guard against non dbo users attempting to add data to the DBs while the replication is off.
HTH Graeme
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply