replication - need input

Hello everyone,
I am planning to replace one of our database servers (SERVERPUB) with a
brand new one. The current server has few databases that are being replicated
(publishers) to another subscriber server (SERVERSUB) going through a
dedicated distributor server(SERVERDIS). All replications are transactional.
Also one of the databases on SERVERPUB is a subscriber. So to make a long
story short, SERVERPUB has databases being published and also hold a database
which is a subscriber.
My question is what is the best way to recreate the replication on SERVERPUB?
At this stage I am planning to do the following:
1. Generate all replication scripts for each database on the publisher
(delete and creation scripts)
2. Run the delete replication script to remove replication on SERVERPUB
3. Do all the work to rebuild the new server and have all databases, logins
and everything ready
4. Run the script to recreate all publications
With this solution I expect the following problem: when I'll start the
snapshot, the destination tables on the subscribers get dropped and recreated
and I lose thepermissions on those tables as the permission on the
subscribers are slightly different (report users) from the publisher. I need
to reapply all permissions.
Is there a better way of doing this?
--
Sasan Saidi
Senior DBA
When you create the article for the publication, you can specify the
destination tables to be dropped then recreated, or just delete all records
in the table. If you are using T-SQL to do this, look for sp_addarticle in
BOL. If you are using EM. Right-click on the
publication>properties>Articles Tab>click on the "..." button next to the
object>Snapshot Tab. You will see there are several choices you can select
under "name conflicts". I prefer T-SQL because you can use "truncate", which
is faster then delete all records and that option is not avaiable on GUI.
"Sasan Saidi" wrote:

> Hello everyone,
> I am planning to replace one of our database servers (SERVERPUB) with a
> brand new one. The current server has few databases that are being replicated
> (publishers) to another subscriber server (SERVERSUB) going through a
> dedicated distributor server(SERVERDIS). All replications are transactional.
> Also one of the databases on SERVERPUB is a subscriber. So to make a long
> story short, SERVERPUB has databases being published and also hold a database
> which is a subscriber.
> My question is what is the best way to recreate the replication on SERVERPUB?
> At this stage I am planning to do the following:
> 1. Generate all replication scripts for each database on the publisher
> (delete and creation scripts)
> 2. Run the delete replication script to remove replication on SERVERPUB
> 3. Do all the work to rebuild the new server and have all databases, logins
> and everything ready
> 4. Run the script to recreate all publications
> With this solution I expect the following problem: when I'll start the
> snapshot, the destination tables on the subscribers get dropped and recreated
> and I lose thepermissions on those tables as the permission on the
> subscribers are slightly different (report users) from the publisher. I need
> to reapply all permissions.
> Is there a better way of doing this?
>
> --
> --
> Sasan Saidi
> Senior DBA
>