Replication - skipping sp_MS[del|upd|ins] triggers in 6.2?

mbourgon

For some reason the "Ignore replication triggers" doesn't seem to work. Even though I have it selected, I still get a list of all the sp_MS triggers. Is there some sort of trick to this? Either on or off, I wind up with the same number of changes. This is in 6.2.0.271 professional edition.

Thanks.

Michelle T

Ignore replication triggers only works on merge replication triggers. Other kinds of replication triggers won't be affected by this option.

mbourgon

Michelle, thanks. However, I'd like to request it be added. The code already exists in order to pull out Merge replication - can it be extended to exclude Transactional as well? I don't know about most sites that use replication, but we use a pretty bog-standard one-to-many transactional setup. While I'm glad it can filter Merge, I'd really like for it to support Transactional.

Please add it to whatever request list y'all have.

Thanks,
Michael

Michelle T

Forum thread added to feature request for 'ignore replication triggers' to cover transactional as well as merge replication.

mbourgon

MANY thanks!

kev1609

In fact, it would be great to have a facility to specify a list of regular expressions and then skip any objects that match those regular expressions

David Atkinson

You'll be pleased to know that we're considering thinking about improving the object filtering in SQL Compare, although this is penned for no earlier than v7.1.

Can I ask whether where in the SQL Compare process would you like to specify such regular expressions? Can you give me one or more specific examples of the regular expression(s) that you would like to exclude from the object list?

David Atkinson
Product Manager
Red Gate Software

kev1609

David Atkinson wrote:

You'll be pleased to know that we're considering thinking about improving the object filtering in SQL Compare, although this is penned for no earlier than v7.1.

Can I ask whether where in the SQL Compare process would you like to specify such regular expressions? Can you give me one or more specific examples of the regular expression(s) that you would like to exclude from the object list?

Well, it's largely related to replication at the moment, in that even with the replication triggers option ticked, my comparison still flags things like the replication stored procedures (sp_sel_#######, etc), and views (msmerge_contents_{TABLE}). Clearly you could add these as an explicit replication setting, but it strikes me it would be more flexible to allow the user to specify his own list of filters.

As another example, we have one application which is modular. Some sites will not use certain modules, and consequently don't need us to sync the relevant objects. We have a naming convention to determine which items belong to which module, and a regexp filter would allow us to set up a project specific to each site.

As to how it would be set up, perhaps a section for each type of object containing a list of filters for that type of object. You could then elect to ignore any stored procedure that matches a given filter. Does that make sense?

Kev

David Atkinson

Please could you try SQL Compare v8.0 (beta) as this now provides the ability to selectively filter objects. Read more about it at:

http://www.red-gate.com/MessageBoard/vi ... php?t=8303

Please let us know if this meets your expectations.

Kind regards,

David Atkinson
Product Manager
Red Gate Software

kev1609

David Atkinson wrote:

Please let us know if this meets your expectations.

I've taken a look at it and it certainly addresses our needs. I'll continue playing and see if I encounter any problems, but it looks good.

One small thing I noticed is that I can no longer double click on the compare direction arrow to reverse the syncronisation. Is this just missing from the beta, or has it gone for good, or has the functionallity just moved elsewhere?

Kev

David Atkinson

Thanks Kev. We've had a fair number of requests about reinstating the switchable synch arrow!

With regards to replication, I'm curious about how you're dealing with comparing against tables which have additional MSmerge rowguid replication columns.

David Atkinson
Red Gate Software

kev1609

David Atkinson wrote:

With regards to replication, I'm curious about how you're dealing with comparing against tables which have additional MSmerge rowguid replication columns.

Nothing very exciting, I'm afraid. We have a guid column with the rowguid attribute set in all of our tables on both the development servers and the replicated production servers.

Kev

David Atkinson

Did you add them to your development environment manually or did you use SQL Compare to migrate them across?

Also, are there any problems associated with having these additional fields on your development servers as well?

And how were you dealing with the other replication objects beforehand? Did you create these on the dev environment as well, or did you simply ignore them from SQL Compare's results set?

Kind regards,

David Atkinson
Red Gate Software

kev1609

They were added manually early on in development. Our convention is to have a guid column as the first column in all tables on a replicating database, followed by the primary key column(s). As for interfering with anything, no, they're just an additional column that isn't used directly by the applications.

Any other replication objects we are settin up filters for. These we specifically don't have on the dev environment. In the past i've needed to manully uncheck them before doing a sync, which isn't as bad as it sounds as they occur for the most part in contiguous chunks in the output. Having them ignored by a filter is one less thing to get wrong though!!

Kev

David Atkinson

Thanks for the explanation. This will help us continue to identify and understand how to improve replication 'awareness' in SQL Compare as we move it forwards. I'm really glad that the filtering is a big step in the right direction to satisfy this use case.

If you have any other issues with SQL Compare, whether they be replication-related or otherwise, please let us know!

Kind regards,

David Atkinson
Red Gate Software