Changing the schema ownership on subscriber

Hi,

I have a replication in which publisher, distributer and subscriber all runing on sql server 2005. all the tables that needs to be replicated are under 'dbo' schema on publisher.

Subscriber is a datawarehouse so i dont want to put tables coming from a system to go under dbo schema as there might be other application replicating same name tables to warehouse. i have created a schema for my application on warehouse but dont know how to tell replication to create tables under application schema created on subscriber. I am using snapshot replication that can be reinitialized if required.

any help will be appriciated.

Cheers,

Furrukh baig

Just found it myself. go to publication properties. click articles and then right click on any article and select "set properties for all tables" ... this will open the property window ... look for "destination object owner" and specify the value of schema name on target.

thanks,

Furrukh Baig

Changing the sa password of publisher and subscriber

Hi,

I have publish the data which have two subscriber ,one is in local network and other one is through internet.

Now As per compnay policy i am going to change tha sa password of Publisher and subscriber.

So i want know that it will effect replication or not.

if it will then what should i do to change the sa password.

Regards

Sanjay Tiwari

Depending on how you setup your repl. I suggest you take a look at Vyas' article.

http://vyaskn.tripod.com/repl_ans4.htmsql

Changing publisher for Merge Replication

We have a database application that is currently being replicated to 2
remote sites. In the near future, I expect that this will increase to many
more sites. My question is this, as we move forward it is probable that at
some time we will run into the need to replace the server that currently
functions as the publisher server. Is there some way to change all of the
publications to use a different server without having to remove replication
and reinstalling it?
TIA
Ron Lounsbury
Ron,
in my experience it is only possible to migrate replication databases from
one server to another if the server names are identical. If they are not,
then the best thing to do is to script out replication, change the
references to servernames and job owners appropriately, drop the 'old'
publications on the old server then recreate the publications on the new
server.
Another poster ran into this type of issue after restoring a merge published
database to another server and found when he tried to add a column he got
the error: 21260 "Schema replication failed because database '%s' on server
'%s' is not the original Publisher of table '%s'".
I investigated this and in sysmerge articles there is a publisherid. This
ID needs to have a corresponding record in sysmergepublications. However, in
sysmergepublications, the 'publisher' column needs to match the servername,
which it didn't in his case as he had restored to another servername. So the
problem is that the replication metadata tables 'hardcode' the original
server name.
HTH,
Paul Ibison
|||Paul
Thanks for the response. It's not what I wanted to hear, but I was
pretty much expecting it. One of the problems we will have is that not all
of our subscribers are connected at any given time, making it a bit
difficult to push the database out again. Also, there is a certain amount
of time (@.20 min or so) that the subscriber can't use the system while we
are doing this. Oh well, as I said, I was afraid that this would be the
answer.
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O6cJxkJeEHA.3476@.tk2msftngp13.phx.gbl...
> Ron,
> in my experience it is only possible to migrate replication databases from
> one server to another if the server names are identical. If they are not,
> then the best thing to do is to script out replication, change the
> references to servernames and job owners appropriately, drop the 'old'
> publications on the old server then recreate the publications on the new
> server.
> Another poster ran into this type of issue after restoring a merge
published
> database to another server and found when he tried to add a column he got
> the error: 21260 "Schema replication failed because database '%s' on
server
> '%s' is not the original Publisher of table '%s'".
> I investigated this and in sysmerge articles there is a publisherid. This
> ID needs to have a corresponding record in sysmergepublications. However,
in
> sysmergepublications, the 'publisher' column needs to match the
servername,
> which it didn't in his case as he had restored to another servername. So
the
> problem is that the replication metadata tables 'hardcode' the original
> server name.
> HTH,
> Paul Ibison
>

Changing path of data & log files on the fly

We have a SQL Server setup as a publisher to 15 subscribers. We need to change the path of the data & log files to a new drive (added a new harddisk). We plan to take a cold backup of the database and shift the data & log files to the new drive. Then we just attach the data & log files from the new path.

Will this disturb my existing replication Setup?
Is the the correct procedure for changing the path of the existing data & log files?
What is the appropriate method for shifting data & log file of a live database to a different location (directory/drive) ?

thanks in advancedisable replication;
sp_detach_db;
move files to new location;
sp_attach_db;
enable replication.|||Thanks for the suggestion. I'll try this out and confirm back. Thanks again anyways.

Changing DtaaType on the publisher

Vivek,
such a change is possible in SQL Server 2005 (using Alter
Table but not the gui on Beta2) but in SQL Server 2000 we
have one of 2 choices:
(1) Drop the subscriptions, make the change then
resubscribe
(2) You could add a new column with the new datatype
(sp_repladdcolumn), do an update on the table to populate
the column, then drop the old column (sp_repldropcolumn).
Do this again to create the column having the same
original name.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Thanks for your suggestion.
I tried the first one. I unsubscribed the subscriber and then tried changing
the length of the data type on Publisher. When I tried to save it sadi,
"Unable to modify since the table is being used for replication"
Please help
Thanks
Vivek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:2b5801c49faf$ab274ff0$a501280a@.phx.gbl...
> Vivek,
> such a change is possible in SQL Server 2005 (using Alter
> Table but not the gui on Beta2) but in SQL Server 2000 we
> have one of 2 choices:
> (1) Drop the subscriptions, make the change then
> resubscribe
> (2) You could add a new column with the new datatype
> (sp_repladdcolumn), do an update on the table to populate
> the column, then drop the old column (sp_repldropcolumn).
> Do this again to create the column having the same
> original name.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

changing distributors

Is it possible to change the distributor for a publisher without
affecting the publications?
Example:
D1 is SQL Server 2000 acting as distributor and publisher. X is SQL
Server 7.0 acting as a publisher. D1 pushes subscriptions to X and
also pulls subscriptions from X.
D2 is SQL Server 2000 and needs to act as the distributor, as well as
publisher. X is registered as a publisher with D2.
So here's the problem. I want D1 to continue pushing and pulling
subscriptions to and from X. However, I want D2 to be the distributor
for it. I don't care if D1 remains as a distributor or not. The
result should be that both D1 AND D2 push and pull subscriptions to
and from X, all being distributed by D2.
There is no supported way of doing this. You have to drop your publications,
make your change, recreate your publications and subscriptions.
"jsauri" <jeff_sauri@.hotmail.com> wrote in message
news:8c144331.0405061113.170d3b7a@.posting.google.c om...
> Is it possible to change the distributor for a publisher without
> affecting the publications?
> Example:
> D1 is SQL Server 2000 acting as distributor and publisher. X is SQL
> Server 7.0 acting as a publisher. D1 pushes subscriptions to X and
> also pulls subscriptions from X.
> D2 is SQL Server 2000 and needs to act as the distributor, as well as
> publisher. X is registered as a publisher with D2.
> So here's the problem. I want D1 to continue pushing and pulling
> subscriptions to and from X. However, I want D2 to be the distributor
> for it. I don't care if D1 remains as a distributor or not. The
> result should be that both D1 AND D2 push and pull subscriptions to
> and from X, all being distributed by D2.