Changing a column type with replication

I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.