August 18, 2009 at 2:06 am
Hey All...
Firstly im not a DBA im a developer... our DBA is replicating X,Y,Z tables from ServerA to ServerB
however these tables seem to be a stright up copy from ServerA to ServerB...
For the past month weve been nagging the DBA about replication with Schema changes...
Yet we never seem to see a result of this...
My question is
1, Can replication change the schema (i.e. ServerA.Tab1.Start varchar = ServerB.Tab1.Start = new datatype of dateTime
ServerA.Tab1.MyColumnName = ServerB.Tab1.MyNewColumnName
i.e. can the data types and column names be changed so its no longer a direct match therefore not having to inherit lots of crap design in our new project....
Many thanks in advance guys
August 18, 2009 at 8:09 am
There are only some things that you can change. For instance, your Publisher has char column, you can make the subscriber a varchar but it has to have same length. (Or you might get truncation error). The publisher datestamp to a varbinary. If you want to change the column name itself, then you will have to do custom stored procs for replication and can be messy.
So I think in this instance I am with your DBA. If you want to have different column name and additional fields or anything else, it can become very messy to administrate. Keep in mind that if it is custom stored procs, you have to create the data manually on the subscriber.
For my two cents worth, I would not go around changing column names and order of columns in the subscriber. Not impossible to do, but not worth it.
-Roy
August 18, 2009 at 10:31 pm
With SQL 2005, it is quite easy to have schema changes replicated. When you create the publication (using stored procedure sp_addpublication), use "@replicate_ddl = 1" to enable replication of schema changes.
Changes made on the publishing using ALTER TABLE, ALTER VIEW, ALTER PROCEDURE, ALTER FUNCTION and ALTER TRIGGER will be replicated to the subscriber the next time the distribution agent runs. For more information, have a look at "Making Schema Changes on Publication Databases" in Books Online.
August 19, 2009 at 7:04 am
happycat59 (8/18/2009)
With SQL 2005, it is quite easy to have schema changes replicated. When you create the publication (using stored procedure sp_addpublication), use "@replicate_ddl = 1" to enable replication of schema changes.Changes made on the publishing using ALTER TABLE, ALTER VIEW, ALTER PROCEDURE, ALTER FUNCTION and ALTER TRIGGER will be replicated to the subscriber the next time the distribution agent runs. For more information, have a look at "Making Schema Changes on Publication Databases" in Books Online.
HappyCat, The question was if OP can have a different schema in the subscriber from the publisher. Also was asking of he/she can have varchar in Publisher and in the subscriber change that to DateTime. Another change he/she wanted was to have the column name different from Publuisher to subscriber.
-Roy
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply