changing column type

Hi I have transactional replication and I need a tables column data type
changed from char(30) to varchar(40). What would be the best way and least
dangerous to edit this column and get it to replicate the changes succesfully.
thanks for any advice
Sammy
Sammy,
please check out these articles:
http://www.replicationanswers.com/AddColumn.asp
and
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)

Changing column length of a replicated table

Can I increase the length of a varchar column of table involved in transactional replication without dropping and recreating publication/subscription?

Any help/short-cuts/undocumented features greatly appreciated.

Regards

Opal

Are you using SQL Server 2005? If so, you can turn on @.replicate_ddl option (set it to 1) for a publication then use regular "ALTER TABLE ... ALTER COLUMN ..." DDL syntax to increase the length of the column, the DDL change will be automatically replicated to subscriber by replication. (@.replication_ddl options is automatically turned on if you use management studio to create the publication)

Hope that helps,

Zhiqiang Feng

|||

Thank you Zhiqiang for your reply, but sorry we're using 2000.

Opal

|||

Sorry there is no easy workaround in SQL Server 2000. The only alternative is that you can use sp_repldropcolumn then sp_repladdcolumn to drop and re-create the column, but that way you will lose all the data for that column.

Thanks,

Zhiqiang Feng

Change Sql Server 2000 Enterprise to Standard Edition

Hi,

One of our main servers running on SQL Server 2000 Enterprise edition which has transactional replications on it which replicates to other servers running on the SQL Server 2000 Enterprise edition as well.

Due to Hardware problems the server is being migrated to a new machine but the client has installed SQL Server 2000 standard edition on the new machine.

We will be using a two processor cpu with 4GB RAM and we are also not planning about clustering. Is there any problem if i migrate the server in Standard Edition will the replications work properly between Standard and Enterprise editions.

What other complications can be there if i switch over to standard edition from enterprise edition

Thanks in ADVANCE

Jacx

In SQL 2000 there are no edition aware features for transactional replication (other than for MSDE). So you will be fine!

Change replicationAgent that starts SQLSERVERAGENT

Hi,
We are using Push subscription in our transactional replication setup in SQL
2K and I noticed that the SQLSERVERAGENT service is started by a local
account called replicationAgent. Is it a problem for me to change it to a
different local account? If it is ok to change it, do I need to change it
also in the subscriber computer and is it necessary that both account name
and password need to be the same?
Wingman
It is ok to change it to an account in the local administrator group, or an
account which has write permission to the default snapshot share.
Make sure that the job owner is sa otherwise it probably will not start.
Hilary Cotter
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
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:67E05E17-2202-477C-BB62-75695B821516@.microsoft.com...
> Hi,
> We are using Push subscription in our transactional replication setup in
> SQL
> 2K and I noticed that the SQLSERVERAGENT service is started by a local
> account called replicationAgent. Is it a problem for me to change it to
> a
> different local account? If it is ok to change it, do I need to change it
> also in the subscriber computer and is it necessary that both account name
> and password need to be the same?
> Wingman
|||Thanks as always.
Wing
"Wingman" wrote:

> Hi,
> We are using Push subscription in our transactional replication setup in SQL
> 2K and I noticed that the SQLSERVERAGENT service is started by a local
> account called replicationAgent. Is it a problem for me to change it to a
> different local account? If it is ok to change it, do I need to change it
> also in the subscriber computer and is it necessary that both account name
> and password need to be the same?
> Wingman

Change publisher server

I have transactional replication on SQL2000 with 60 subscribers.
For disaster recovery procedure i need to establish new replication within 15 minutes on another server with same subscribers.
Can i just confogure another DB as publisher and connect exist subscribers to new publisher ??

ThanX.Yes and no.

Before we begin, a cluster might be a better solution for this case. Either active-active or active-passive will allow you to avoid the down time and the hassle of the move. There is a trade off in terms of the complexity of the cluster, but that still seems like a better choice to me for your situation.

I'd suggest that you check KB #822400 (http://support.microsoft.com/default.aspx?scid=kb;en-us;822400) for more details on Disaster Recovery for SQL Server.

-PatP|||First of all thank you for answering.
Now i have cluster in my environment (Active / Passive ) .I am talking about real disaster - cluster have gone , so i need to move the publisher during minimal time to another location.
Thanx

Change publisher of replication

I have transactional replication on SQL2000 with 60 subscribers.
For disaster recovery procedure i need to establish new replication within 15 minutes on another server with same subscribers.
Can i just confogure another DB as publisher and connect exist subscribers to new publisher ?
ThanX.
This isn't quite so simple. How are you synchronizing with the standby publisher? Are you intending to use 'sync with backup' which ensures the publisher backup is never behind the distributor backup. Will you be restoring earlier copies of the subscriber
to ensure they are not ahead of the distributor? have a look in BOL at "Strategies for Backing Up and Restoring Transactional Replication".
HTH,
Paul Ibison