replication and schema change

Hello everyone!
We are software developement company. We are planning to setup replication
for few our customers. But our software is still under developement and
database schema is changing quite often.
What is the best, fastest and easiest way to implement this database chnages
at our "replication" customers?
Uros
Uros,
sp_repladdcolumn and sp_repldropcolumn cater for a lot of schema changes. To
alter a column take a look at this article:
http://www.replicationanswers.com/AddColumn.asp. On SQL Server 2005 things
are easier: http://www.replicationanswers.com/AlterSchema2005.asp.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||An alternative method which allows for more than just adding or removing 1
column at a time...
--Drop Subscription & Article
exec sp_dropsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
exec sp_droparticle @.publication = 'fxDB6_Pub1'
, @.article = 'MyTable'
-- Make DDL changes
ALTER TABLE MyTable
ALTER COLUMN...
-- Add article
exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
@.source_owner = N'dbo'
, @.source_object = N'MyTable', @.destination_table = N'MyTable', @.type =
N'logbased', @.creation_script = null
, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16
, @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_MyTable',
@.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
@.filter = null
, @.sync_object = null, @.auto_identity_range = N'false'
-- Add Subscription(s)
exec sp_addsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
, @.destination_db = 'SubscrDBName'
, @.sync_type = 'automatic'
-- Start Snapshot agent - creates snapshot only for MyTable article
-- Distribution agents will ship to subscribers
Another solution for changes to many tables:
- drop all subscriptions
- drop publication
- make changes
- re-create publication, with script of course (ui takes too long w/ many
tables)
- add subscriptions
- fire up agents...
Regards,
ChrisB
www.MyDatabaseAdmin.com
"uros" wrote:

> Hello everyone!
> We are software developement company. We are planning to setup replication
> for few our customers. But our software is still under developement and
> database schema is changing quite often.
> What is the best, fastest and easiest way to implement this database chnages
> at our "replication" customers?
> Uros
|||Chris - Good suggestion. However, "@.schema_option =
0x00000000000000F3" can be a different value depending on the options one
may have chosen through the interface during the first attempt for the setup.
-A
"Chris" wrote:
[vbcol=seagreen]
> An alternative method which allows for more than just adding or removing 1
> column at a time...
> --Drop Subscription & Article
> exec sp_dropsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> exec sp_droparticle @.publication = 'fxDB6_Pub1'
> , @.article = 'MyTable'
> -- Make DDL changes
> ALTER TABLE MyTable
> ALTER COLUMN...
> -- Add article
> exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
> @.source_owner = N'dbo'
> , @.source_object = N'MyTable', @.destination_table = N'MyTable', @.type =
> N'logbased', @.creation_script = null
> , @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x00000000000000F3, @.status = 16
> , @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_MyTable',
> @.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
> @.filter = null
> , @.sync_object = null, @.auto_identity_range = N'false'
> -- Add Subscription(s)
> exec sp_addsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> , @.destination_db = 'SubscrDBName'
> , @.sync_type = 'automatic'
> -- Start Snapshot agent - creates snapshot only for MyTable article
> -- Distribution agents will ship to subscribers
> Another solution for changes to many tables:
> - drop all subscriptions
> - drop publication
> - make changes
> - re-create publication, with script of course (ui takes too long w/ many
> tables)
> - add subscriptions
> - fire up agents...
> Regards,
> ChrisB
> www.MyDatabaseAdmin.com
> "uros" wrote:

replication and data move

Hi,
I'm planning to move MS SQL Server 2000 instance to another server. I know
that the easiest way is to copy data directory from one machine to another
and set new server name to the old one. Because of some reasons I can't do
it in thisy way. Besides that I don't watn to move all databases but some of
them. Unfortunately, some of these databases are replicated (part of them is
published and another part is subscibed).
How to move MSSQL instance to another machine keeping replication working
properly without starting replication from scratch? Is there way to restore
database (published or subscribed) with already set replication?
Thanks
Marcin> Hi,
> I'm planning to move MS SQL Server 2000 instance to another server. I know
> that the easiest way is to copy data directory from one machine to another
> and set new server name to the old one. Because of some reasons I can't do
> it in thisy way. Besides that I don't watn to move all databases but some
of
> them. Unfortunately, some of these databases are replicated (part of them
is
> published and another part is subscibed).
> How to move MSSQL instance to another machine keeping replication working
> properly without starting replication from scratch? Is there way to
restore
> database (published or subscribed) with already set replication?
> Thanks
> Marcin
--
You can't do it. According to SQL Server Books Online:
Restoring Backups of Replicated Databases to a Different Server or Database
When you restore a backup of a replicated database to a server or database
other than the one on which it was created, your replication settings
cannot be preserved. For publishing databases and merge subscribing
databases, a full restore of the database and logs is followed by an
automatic removal of replication meta data from the database when the
database or server you restore to differs from the one on which the backup
was created.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

replication and data move

Hi,
I'm planning to move MS SQL Server 2000 instance to another server. I know
that the easiest way is to copy data directory from one machine to another
and set new server name to the old one. Because of some reasons I can't do
it in thisy way. Besides that I don't watn to move all databases but some of
them. Unfortunately, some of these databases are replicated (part of them is
published and another part is subscibed).
How to move MSSQL instance to another machine keeping replication working
properly without starting replication from scratch? Is there way to restore
database (published or subscribed) with already set replication?
Thanks
Marcin
> Hi,
> I'm planning to move MS SQL Server 2000 instance to another server. I know
> that the easiest way is to copy data directory from one machine to another
> and set new server name to the old one. Because of some reasons I can't do
> it in thisy way. Besides that I don't watn to move all databases but some
of
> them. Unfortunately, some of these databases are replicated (part of them
is
> published and another part is subscibed).
> How to move MSSQL instance to another machine keeping replication working
> properly without starting replication from scratch? Is there way to
restore
> database (published or subscribed) with already set replication?
> Thanks
> Marcin
You can't do it. According to SQL Server Books Online:
Restoring Backups of Replicated Databases to a Different Server or Database
When you restore a backup of a replicated database to a server or database
other than the one on which it was created, your replication settings
cannot be preserved. For publishing databases and merge subscribing
databases, a full restore of the database and logs is followed by an
automatic removal of replication meta data from the database when the
database or server you restore to differs from the one on which the backup
was created.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

replication and data move

Hi,
I'm planning to move MS SQL Server 2000 instance to another server. I know
that the easiest way is to copy data directory from one machine to another
and set new server name to the old one. Because of some reasons I can't do
it in thisy way. Besides that I don't watn to move all databases but some of
them. Unfortunately, some of these databases are replicated (part of them is
published and another part is subscibed).
How to move MSSQL instance to another machine keeping replication working
properly without starting replication from scratch? Is there way to restore
database (published or subscribed) with already set replication?
Thanks
Marcin> Hi,
> I'm planning to move MS SQL Server 2000 instance to another server. I know
> that the easiest way is to copy data directory from one machine to another
> and set new server name to the old one. Because of some reasons I can't do
> it in thisy way. Besides that I don't watn to move all databases but some
of
> them. Unfortunately, some of these databases are replicated (part of them
is
> published and another part is subscibed).
> How to move MSSQL instance to another machine keeping replication working
> properly without starting replication from scratch? Is there way to
restore
> database (published or subscribed) with already set replication?
> Thanks
> Marcin
--
You can't do it. According to SQL Server Books Online:
Restoring Backups of Replicated Databases to a Different Server or Database
When you restore a backup of a replicated database to a server or database
other than the one on which it was created, your replication settings
cannot be preserved. For publishing databases and merge subscribing
databases, a full restore of the database and logs is followed by an
automatic removal of replication meta data from the database when the
database or server you restore to differs from the one on which the backup
was created.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

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
>

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 replicate
d
(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 databas
e
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 recreate
d
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 DBAWhen 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", whic
h
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 replica
ted
> (publishers) to another subscriber server (SERVERSUB) going through a
> dedicated distributor server(SERVERDIS). All replications are transactiona
l.
> 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 datab
ase
> which is a subscriber.
> My question is what is the best way to recreate the replication on SERVERP
UB?
> 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, login
s
> 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 recrea
ted
> and I lose thepermissions on those tables as the permission on the
> subscribers are slightly different (report users) from the publisher. I ne
ed
> to reapply all permissions.
> Is there a better way of doing this?
>
> --
> --
> Sasan Saidi
> Senior DBA
>

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 DBAWhen 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
>