Disaster recovery in multiple publishers environment

Hi,
I configured multiple publishers and one subscriber architecture on sql 2005
by having one publisher control the schema and indexes on the subscriber, and
the other publishers only append their data to the subscriber. I'm trying to
mimic a disaster recovery issue where one of the publishers fails (one
scenario is for the main publisher to fail, and one scenario is for one of
the other publishers to fail), but I'm having issue with restoring any of the
publishers (either type) and re-initializing the replication with the one
subscriber in a way that doesn't generate data conflicts and allow the
replication to continue.
Every time I try to do this, the subscriber reports of data conflicts
against the data that the same publisher has replicated already (this is
because the records have a primary uniqueidentifier key), and now tries to
replicate again, and therefore replication doesn't seem to keep pushing new
data to the subscriber.
How can approach either scenario without having to delete the failed
publisher data from the subscriber, and without allowing for duplicate data
(if I remove the primary key from the subscriber) before I re-initialize the
replication?
Thanks,
Gil
Can you outline exactly how the disaster recovery test is being done - do you
restore the publisher's publication database to the same or another server?
Are you synchronising the distribution database backups? Are you using the
same distribution database or does each publisher have its own one.
Thanks,
Paul Ibison
|||Have a look at the continue on data consistency error profile. This will
allow you to skip the pk violations.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gil Shabat" <GilShabat@.discussions.microsoft.com> wrote in message
news:96ABD1A2-F355-4A77-B44C-2B60EA410CDD@.microsoft.com...
> Hi,
> I configured multiple publishers and one subscriber architecture on sql
> 2005
> by having one publisher control the schema and indexes on the subscriber,
> and
> the other publishers only append their data to the subscriber. I'm trying
> to
> mimic a disaster recovery issue where one of the publishers fails (one
> scenario is for the main publisher to fail, and one scenario is for one of
> the other publishers to fail), but I'm having issue with restoring any of
> the
> publishers (either type) and re-initializing the replication with the one
> subscriber in a way that doesn't generate data conflicts and allow the
> replication to continue.
> Every time I try to do this, the subscriber reports of data conflicts
> against the data that the same publisher has replicated already (this is
> because the records have a primary uniqueidentifier key), and now tries to
> replicate again, and therefore replication doesn't seem to keep pushing
> new
> data to the subscriber.
> How can approach either scenario without having to delete the failed
> publisher data from the subscriber, and without allowing for duplicate
> data
> (if I remove the primary key from the subscriber) before I re-initialize
> the
> replication?
> Thanks,
> Gil
|||Thanks for your replies.
For now I'm using one server to backup, and then restore the database. I
haven't got to restore the distribution database, and this also exists on the
same server. After some experimenting, I found that if I delete the
subscription + publisher, then restore the database, run the following
command exec sp_removedbreplication @.dbname=’dbname’,@.type='both' to remove
records of the publication,
and re-create the publication + subscription, but configure the subscriber
not to initialize, I get to a point that the new data is replicated again
without too many issues on both scenarios. however, I'm not sure whether this
is the right approach to take. Can you please let me know your thoughts?
Also, on a related issue, I tested using the SQL 2005 Developer edition
changing the schema of the subscriber, by changing the main publisher, and
this seems to work just fine. Problem is that when I run same scripts against
a publisher that has the setting to 'Keep existing object unchanged', it
still tries to replicate the DDL (tried to add a column to an existing
replicated table), and therefore returns with error (something about the same
column name can't exist twice on the same table). In addition, if I update
one of the other publishers first I can clearly see that despite the 'Keep
existing object unchanged' setting it still replicates the changes.
I tried to follow the process above and this allowed me to get around this
issue (i.e. delete all publications other than main publication, make schema
changes to all including the main publication, so subscriber schema would
also be modified, then re-create the publications, and disable the
initialize), but this seems completely wrong to me. Any thoughts on that
Thanks,
Gil
"Gil Shabat" wrote:

> Hi,
> I configured multiple publishers and one subscriber architecture on sql 2005
> by having one publisher control the schema and indexes on the subscriber, and
> the other publishers only append their data to the subscriber. I'm trying to
> mimic a disaster recovery issue where one of the publishers fails (one
> scenario is for the main publisher to fail, and one scenario is for one of
> the other publishers to fail), but I'm having issue with restoring any of the
> publishers (either type) and re-initializing the replication with the one
> subscriber in a way that doesn't generate data conflicts and allow the
> replication to continue.
> Every time I try to do this, the subscriber reports of data conflicts
> against the data that the same publisher has replicated already (this is
> because the records have a primary uniqueidentifier key), and now tries to
> replicate again, and therefore replication doesn't seem to keep pushing new
> data to the subscriber.
> How can approach either scenario without having to delete the failed
> publisher data from the subscriber, and without allowing for duplicate data
> (if I remove the primary key from the subscriber) before I re-initialize the
> replication?
> Thanks,
> Gil
|||What you initially describe is known as a nosync initialization and this is
quite standard.
Am not too sure why the replication of ddl changes would cause a duplicate
column - can you elaborate. EG is the schema of the table on the subscriber
already different to the publisher before the change?
As a workaround which avoids a complete reinitialization, you could drop the
article, drop the subscription to the article, make the change then readd,
(see http://www.replicationanswers.com/AddColumn.asp) but this might not be
necessary depending on more info from the question above.
HTH,
Paul Ibison
|||Thanks for the info.
I basically tested by having two schema identical databases (publishers),
and an identical schema database subscriber. Any change to the schema (ALTER
TABLE ADD...) on either publisher caused an immediate replication of the DDL
to the subscriber regardless whether or not the 'Keep existing object
unchanged' setting was used or not (the main publisher used the 'Drop
existing object and create a new one' and the secondary publisher used the
'Keep existing object unchanged' setting). In our production environment I
envision running the same DDL code against all publishers, and having only
the main publisher replicate those schema changes to the subscriber.
Unfortunately, when I tried to run a simple add column statement, the first
one to update its ddl statements have also replicated its changes to the
subscriber, while the 2nd one tried to do the same, but return an error that
the same column name cannot exist multiple times. The following is the exact
message: "Column name 'additional_Sync' in table 'dbo.Primary1' is specified
more than once"
To create the environment, I created two separate databases, and I ran
against them the same DDL to create one table. Then I created the main
publisher and subscription, which created the environment on the 3rd database
(subscriber), and finally, I created a new publication for the secondary
publisher, but used the 'Keep existing object unchanged' setting, so schema
wouldn't be re-created. This resulted in having data from the 2 publishers on
the subscriber. At this point I tried to test with adding a new column to the
table I created on the publishers.
I would appreciate any thought you might have.
Thanks,
Gil
|||Please can you test something for me with your setup. I believe that in SQL
2000 I set this up differently. One publisher/subscriber was set up as per
usual. The other one was set up with the "keep the existing table unchanged"
option. Data was sent from the second publisher but there was no DROP TABLE
statement there. If the data is partitioned this is fine. The thing is that
after setting it up this way, using sp_repladdcolumn, the column was only
propagated from the first publisher and not the second. This is where I
believe the difference might lie - you are using ALTER TABLE instead, and you
have the "keep existing..." setting on each publisher's articles. Please try
setting it up my way (not yet tried in SQL 2005) and see if this works for
you...
HTH,
Paul Ibison
|||I believe that in SQL 2005 MS changed the option from "keep the existing
table unchanged" to 'Keep existing object unchanged', but both do exactly the
same thing
I tried already yesterday to disable the replication schema updates by
executing: exec sp_changepublication @.publication ='Publication
Name',@.property ='replicate_ddl', @.value=0
and then executed the alter table command, which wasn't included in the
replication. then I added the column to replication by executing the
sp_repladdcolumn on both publishers, and the column has been added, and again
like before, both DDL commands were sent to subscriber, and I ended up with
the same error
Gil

Disappearing parameter values

I have an ASP.NET application with a page which has a Reportviewer control
linked to a Reporting Services report on a Remote Server. I pass parameters
into it OK including the name of the database which is then referenced by a
dynamic connection string. The database is the first parameter. Other
parameters have dropdown lists based on datasets using the dynamic connection
string.
All of this works fine, but...
When I hide some of the parameters, because I know what value is required,
the value is not acceoted, nor does the report use the default value. It is
as though hiding the parameter prevents the list of available values being
requeried; consequently the passed in value is ignored as it does not match
an available value!
Can anyone help please?
Thanks
GeorgeGeorge,
I assume you are setting the Visible property of some web control that
holds the value of your report parameter. You should set the Visibility
Style of the control to 'hidden' so that it still gets rendered into the web
form but is not 'seen' in the web browser. This way the controls value is
still within the form and viewstate when the form is posted back.
Hope I made the correct assumption and this helps.
thx
-jsh
"George Davies" wrote:
> I have an ASP.NET application with a page which has a Reportviewer control
> linked to a Reporting Services report on a Remote Server. I pass parameters
> into it OK including the name of the database which is then referenced by a
> dynamic connection string. The database is the first parameter. Other
> parameters have dropdown lists based on datasets using the dynamic connection
> string.
> All of this works fine, but...
> When I hide some of the parameters, because I know what value is required,
> the value is not acceoted, nor does the report use the default value. It is
> as though hiding the parameter prevents the list of available values being
> requeried; consequently the passed in value is ignored as it does not match
> an available value!
> Can anyone help please?
> Thanks
> George|||Thanks jsh for replying so promptly
I'm not sure I've explained the problem correctly, as I could not find the
Visibility Style for the control as you suggested. I am using RS2005 and
ASP.NET 1.1 with a ReportViewer control linked to a deployed remote report.
But I think you may be on to something as the problem does not arise when
designing the report, nor in Report Manager, only when I run it from within
the ASP ReportViewer control as a Remote Report.
The parameter is passed into the Reporting Services report OK, but when the
Report Server processes the parameters it first takes the name of the
database as the first parameter, then it recognises that there are cascading
parameters because the dropdown lists require data from tables which have
been queried using the dynamic connection string. At this point it seems to
assume that because it is going to refresh the dropdown lists that it can no
longer rely on the selections supplied in the other parameters and scrubs
them i.e. it insists that the user rekey each of the parameter values
wherever the dropdown lists use the dynamic connection string.
This causes two problems:
1 When I want a parameter value hidden, it is impossible to set it and the
report fails with e.g. "Parameter zzz does not have a value". I could get
round this by using two parameters for each value, one for when I want to
force the parameter to be a certain value and hide it with no associated
dropdown list, and one for when I want to show it and ask the user to pick
from a list. Messy, but I could get the report SQL to test for either of the
values in the two parameters. But this still leaves problem number 2...
2 When cascading parameters are used, each time the user picks a value, it
postbacks to the server. This is not very clever of Reporting Services. It
should post back when the value for a parameter is changed where that
parameter is used by other parameters, not when a parameter changes which
uses the value from another parameter.
The long and short of it is that with eight parameters, the selection of
report options is excruciatingly slow and won't be acceptable to my users.
If you or anyone else can shed any light on this problem, I would be really
grateful.
Thanks again and best wishes
George
"jsh02_nova@.hotmail.com" wrote:
> George,
> I assume you are setting the Visible property of some web control that
> holds the value of your report parameter. You should set the Visibility
> Style of the control to 'hidden' so that it still gets rendered into the web
> form but is not 'seen' in the web browser. This way the controls value is
> still within the form and viewstate when the form is posted back.
> Hope I made the correct assumption and this helps.
> thx
> -jsh
> "George Davies" wrote:
> > I have an ASP.NET application with a page which has a Reportviewer control
> > linked to a Reporting Services report on a Remote Server. I pass parameters
> > into it OK including the name of the database which is then referenced by a
> > dynamic connection string. The database is the first parameter. Other
> > parameters have dropdown lists based on datasets using the dynamic connection
> > string.
> >
> > All of this works fine, but...
> >
> > When I hide some of the parameters, because I know what value is required,
> > the value is not acceoted, nor does the report use the default value. It is
> > as though hiding the parameter prevents the list of available values being
> > requeried; consequently the passed in value is ignored as it does not match
> > an available value!
> >
> > Can anyone help please?
> >
> > Thanks
> >
> > George

Disable calendar control RS 2005

Is there a way to disable the calendar control in Reporting Services 2005?Hi Larry,
Solutions to this that have worked for me are:
If you mean the default calendar controls that collect parameter information
when the parameter is a date, then like any other, these parameters can be
marked as 'hidden'. To do this, click on the layout tab and select
Report->Report Parameters. When you select the relevant parameter in the
list box on the left, there is a checkbox option to hide it. The parameter
will require its value sourcing by means appropriate to how you are
rendering the report.
If you want to enter the parameter value as normal, but not with the
calendar control, change the type of the parameter to string and convert its
value in the stored procedure. This will make it show as a textbox instead.
Ed Allison
"Larry Charlton" <LarryCharlton@.discussions.microsoft.com> wrote in message
news:12E64B9A-656B-4844-A74D-AD4F645A0AFB@.microsoft.com...
> Is there a way to disable the calendar control in Reporting Services 2005?|||Thanks Ed, That worked perfectly. Even better, I don't have to do any
conversions, I simply make date/time parameters string parameters and the
reports work exactly as before without the calendars. Hopefully the
usefulness of the calendar will improve with the next service pack and we can
start using them again.|||What don't you like about the date picker? Seems like a normal date picker
to me. What are you looking for it to do?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Larry Charlton" <LarryCharlton@.discussions.microsoft.com> wrote in message
news:29C652EC-CDB7-4923-9B43-137C203F5AE5@.microsoft.com...
> Thanks Ed, That worked perfectly. Even better, I don't have to do any
> conversions, I simply make date/time parameters string parameters and the
> reports work exactly as before without the calendars. Hopefully the
> usefulness of the calendar will improve with the next service pack and we
> can
> start using them again.
>|||One three separate servers I've installed RS on. When the reports are
deployed to the server the calendar exhibits two problems.
1) The buttons move around as you click through months.
2) The calendar will pause after about 7 clicks and after a few more it will
close.
I've seen it work on a Microsoft desktop but so far I'm unable to resolve
why we have the behavior here, just that's it's consistent and I can't get
rid of it.