Alternate Synchronisation Partners

Yes, I know synchronisation to alternate partners is deprecated in SQL2005 but....

In SQL2000 there is a Sync Partners tab in the publication properties dialog that allows you tick a checkbox for each co-publisher to be enabled as an alternate synchronisation partner. What is the equivalent in SQL2005?

I've set up replication in SQL2000 following these instructions http://support.microsoft.com/?kbid=321176 and it works. Now I'm trying to do the same thing in SQL2005 but I can't find a substitute for steps 10 & 11 in the section "Set Up the Alternate Synchronisation Partner". What's the answer?

Thanks in advance.

As you mention that alternate sync partners is deprecated, there is no furhter development for this feature. You can use what is available in SQL 2005 (using stored procedures) but again I would not count on it working the way it used to in SQL 2000.|||

Will Windows Server 2003 Clustering with SQL Server Clustering able to help? Such as having a cluster server for publisher and a cluster server for subscriber on active/active mode. Will this able to increase the performance, such as when a either publisher / subscriber is busy, it will look for the cluster server among them and sync the data.

Pls let me know your thought.

Thanks

|||

You could try clustering, which can provide you some relief when server (node) goes down, but you should know that it comes at a price of extra administration and will still not replace the alternate sync partners behavior that you were looking for.

If you do not have high number of nodes in your topology and always need up-to-date data you should probably also look at Transactional Peer-to-Peer replication. But if you are currently using the conflict handling and other merge replication features, this may not be a solution.

|||

Imagine a situation in which a server at head office replicates information to branch office servers over very slow lines. The branch office servers republish the data and the travelling workforce sync their laptops before they go out for the day and sync them again when they get back. Sometimes they return to a different office from the one they started out in (and they don't always know that's going to happen) but there's no link between the branch offices.

Synchronisation to alternate partners works quite well in this situation. What other solutions are there? Please don't suggest changing any part of the problem as that's not an option.

|||

Unfortunately alternate sync partners is deprecated and will not be supported in the next release.

If the traveling workforce will have internet connection, you should look at Web Synchronization in SQL 2005.

That way, the traveling workforce need not always go to the same office to sync with the dedicated branch office. They can go to any of the branch offices but still sync with the brnach office off of which they subscriber using the internet connection.

|||

The problem is that it is deprecated, just like a couple of other replication features that are being used in organizations.

This is one feature deprecation that I don't agree with. Unfortunately, there aren't any options and there aren't any replacements to this functionality. I use it very extensively in a few very large merge architectures. The basic scenario essentially boils down to what you are using it for. Basically, you are allowing synchronization to route around the unavailability of the primary publisher for a subscription. Whether that is a traveling workforce in your case or it is to route entire sections of a merge hierarchy around outages (my scenarios), it is really the same core capability.

Web synchronization is not acceptable in my case, because it can not handle the load in the first place and in the second place it is not an infrastructure option that is available.

I'm still trying to find a way around this so that I can continue to use it, but I haven't gotten everything wired together properly and it requires very significant hacking of the replication metadata tables to even get part way there.

The only suggestion that I have is the product feedback center. It would have been nice to have an alternative if the feature was deprecated, but there aren't any alternatives.

|||

ok, here is another thought. You could have your publisher (or republisher) machine A run mirroring with another server B and make this mirror setup replication aware.

So your subscriber will sync with say machine A and then when machine A goes down for some reason, machine B now becomes the active machine and your subscriber can now sync with machine B. When machine A resumes back, your susbscribers can continue to sync with mahcine A.

The caveat in this setup is that your republisher machine B will not be able to sync with its (root) publisher. Only machine A will be able to do so.

|||Database Mirroring doesn't work with the publisher, although it does work with the distributor or subscriber. Been trying to force feed that one for almost a year now and haven't gotten anything working yet.|||

Database Mirroring should be working for the publisher machine.

Please let us know what issue you are encountering when you are trying to set it up.

Altering table & trans. replication

Hi,

How can I modify table with publication (change of one column length)
without completely breaking replication.

Thanks in advance"Wagner" <wagner@.email.t-com.hr> wrote in message
news:1x0k6ml5is0vs$.mmdq6nunj5l6$.dlg@.40tude.net.. .
> Hi,
> How can I modify table with publication (change of one column length)
> without completely breaking replication.

Besides the method you found, I've also done the following:

Create a NEW column of the type you want, call it foo_temp.

Copy data into it.

Then sp_repldropcolumn on the existing column.

Then sp_repladdcolumn with the same name, but new definition.

Copy data back.

> Thanks in advance

Altering constraint in a column in a publication

Hi All,
I have a replicated (published) database with 1 push subscription to it on
another server. Replication type is Transactional with "pushing" occuring
every 2 hours.
1) In BOL it is mentioned, for making a change (besides adding or dropping a
column) in a published table -
a) delete all subscriptions to the publication
b) remove the article/object from the publication
c) change the table as required
d) add the article back to the publication
e) create the subscription again
My questions -
- Do I always need to go through the entire subscription creation process
each time I need to alter a table in my publication? I mean for a table
change that will take 2 minutes, I need to spend 3 hours (thats the time the
snapshot creation takes for me) creating the subscription?
- In step a), does it mean removing the subsribing database ? What about the
subcribing server ?
2) In BOL, its mentioned that to delete subscriptions through Enterprise
Manager, I need to go to SQL Server Group -> <Registration Name> ->
Replication -> Publications -> <Publication name>. In the right pane, I'll
see the subscription. I need to right click on it and say delete.
My questions -
- If I go to Tools -> Replication -> Configure Publishing, Subscribers and
Distributors, go to the Subscribers tab and remove the tick on the
subscriber, is it the same thing as deleting the subcription?
- Secondly, instead of going through the process of creating a new
subscription after altering my table, if I just go back to the above
mentioned location and tick the required subscriber, is it the same thing as
creating a subscription?
- If answers to both above questions is Yes, can I replace steps a) and e)
in question 1) with above two steps (unticking/ticking)?
Unrelated queries -
- If I disable publishing through option "Disable Publishing" in right click
of SQL Server Group -> <Registration Name> -> Replication, is there any
option to "enable" the publishing or do I need to go through the publication
creation process?
- How do I monitor the performance of replication?
Salil.
Salil,
some changes can be made using sp_addscriptexec; it depends on what you are
trying. If this isn't permitted, you can do a no-sync initialization if you
want to avoid the cost of the snapshot. If you go down this path, be sure
that the data is synchronized first.
Dropping the subscription doesn't require you to drop the subscriber's
database, and is distinct from enabling a subscriber.
Disabling publishing has to be followed by enabling publishing if you want
to use replication again.
You can monitor the performance using the replication-specific counters in
Performance Monitor. Also you can query the replication history tables for
short-term monitoring, and msdistribution_status in transactional
replication.
HTH,
Paul Ibison
|||Thank you Paul.
Point 1 (about no-sync initialization) and 4 (replication monitoring)
definitely helps.
But my query about "ticking/unticking" still remains unanswered.
i.e. If I do -
1) Untick subscribing server
2) remove article from publisher
3) alter article
4) add article back to publisher
5) tick subscribing server
Is 1 and 5 the same as "deleting" and "adding" subscirbers respectively?
Salil.
"Paul Ibison" wrote:

> Salil,
> some changes can be made using sp_addscriptexec; it depends on what you are
> trying. If this isn't permitted, you can do a no-sync initialization if you
> want to avoid the cost of the snapshot. If you go down this path, be sure
> that the data is synchronized first.
> Dropping the subscription doesn't require you to drop the subscriber's
> database, and is distinct from enabling a subscriber.
> Disabling publishing has to be followed by enabling publishing if you want
> to use replication again.
> You can monitor the performance using the replication-specific counters in
> Performance Monitor. Also you can query the replication history tables for
> short-term monitoring, and msdistribution_status in transactional
> replication.
> HTH,
> Paul Ibison
>
>
|||Salil,
unticking the subscriber in the distributor properties box will unsubscribe
all subscriptions to this server, not just the one you are concerned about.
Afterwards you'll need to recheck this box and subsequently resubscribe to
the publication.
HTH,
Paul Ibison
|||Thank you Paul.
"Paul Ibison" wrote:

> Salil,
> unticking the subscriber in the distributor properties box will unsubscribe
> all subscriptions to this server, not just the one you are concerned about.
> Afterwards you'll need to recheck this box and subsequently resubscribe to
> the publication.
> HTH,
> Paul Ibison
>
>

Alter View Hangs - Merge Replication SQL 2005

Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.

Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.

Here is what I am doing:

Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,

table2.txt_sort_order

FROM table1 join table2 on table1.status_code = table2.status_code

The query I am executing:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW myview

AS

Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc

FROM table1 join table2 on table1.status_code = table2.status_code

If this view is the only article in the publication, then it is a known issue.

Add a dummy table to the publication and your alter should succeed.

Alter table in a Publication

Can i alter a table included in a Publication?

I am having problems with that.

Yes, if you are using SQL Server 2005. Additionally, if you are using Merge replication your publication compatibility should be set to 90RTM.

With this, you can alter a table and add/drop/alter a column and this DDL action will be propagated to the subscribers.

|||

For DDL opertion on replicated object tables , in SQL 2000 you can use sp_repladdcolumn/sp_repldropcolumn to add/remove the table columns

|||

Mahesh Dudgikar wrote:

Yes, if you are using SQL Server 2005. Additionally, if you are using Merge replication your publication compatibility should be set to 90RTM.

But if my compatibility level is set to 80 - can I change it to 90, call ALTER TABLE, and then change it back to 80? Will it cause any problems?

|||

You can change the compat from 80 to 90 and then use alter table.

However once the publication compatibility is set to 90, you cannot go back to 80.

|||If your compatibility is set to 80 then you are running in SQL 2000 mode. So you have to use the sp_repladdcolumn/sp_repldropcolumn to add/drop columns.|||

Mahesh Dudgikar wrote:

You can change the compat from 80 to 90 and then use alter table.

However once the publication compatibility is set to 90, you cannot go back to 80.

1. Is compatibility level defined separately for DATABASE and for PUBLICATION?

2. If not: I have tried to change the compatibility level for unpublished database (both upgrading and downgrading), and it succeeded. Does publication lock downgrading of compatibility level of the database?

Thanks in advance!

|||

Sorry for not being clear.

Above, I meant @.publication_compatibility_level of the publication setting, not the database compat level.

They are separate settings. The database compat level has nothing to do with the DDL method (sp_repladdcolumn and alter table)

sql

Allowing Transformations when Creating Publication for Replication

I am at my wits end here. For Replication the Books Online clearly state:

"The option to allow transformations is set at the time you create a publication"

However, I cannot find any options that allow me to do this in the Create Publication Wizard.

Once the Publication has been created I see in the Properties in the Subscription Options tab that "Use DTS to transform data before distributing it to a Subscriber" is set to No and there is no way to change it.

Where am I going wrong?I'd actually like to know the EXACT same thing. I'm trying to use replciation and need only to do some transformations to the data, but as you mention that option is greyed out.