March 24, 2004 at 4:35 am
hi,
Our application reads and writes to a database called "Primary". This is a very high I/O intensive database. We have requirements to aggregate and report on this data. And so, we have decided to implement Transactional Replication and create a replica of our database called "Secondary". On this secondary database, we will run processes to pre-aggregate the data for reporting and other processing.
Additionally, We also need to be able to
1. Add additional Tables to "Secondary". These additional tables are not defined on the "Primary" database
2. Add additional columns to one of the tables replicated from the Primary to Secondary. These additional columns are not defined in the "Primary".
3. These additional columns should be updateable
4. The updated data should not be sent back to the primary.
Are these things possible when implementing Transactional Replication?
Aditee Rele
March 24, 2004 at 5:55 am
You can always have more tables in the other database as it will not effect that. However I am not sure you can have the extra columns on the replicated copy (but I have never tried). If not you could possibly add another table for those columns and use the primary key from the main table in the extra for linking back then it can be transparent but still work anyway visually.
March 24, 2004 at 6:09 am
You can add new columns to the tables in the subscriber.
If you use stored procedures to replicate the transactions, you can see that in the stored procedures the inserts, updates or deletes have the columns names, so adding columns won't give you any problems.
But those columns must allow nulls values. All that you listed before can be done in transactional replication. You can add columns and update them, and those changes won't be replicated to the primary database.
March 24, 2004 at 8:26 am
Good to know for sure, however I would be concerned with what would happen if I had to restart replication for any reason from scratch then. racosta have you done this with succes?
March 24, 2004 at 8:47 am
IF you need ro resync or reinitialize the subscription, applying the Snapshot will fail. Because the BULK INSERT SQL executes assumes that the dest table has the same columns that the published article.
Here you will have to drop the new column and re create it once you have apply the snapshot, or also can modify the .bcp file and add a comma (,) at the end of each line, to simulate another column.
Also can add a pre execution script wich saves the table into another table and drop the column in the subscriber table. Then adding a post execution script, you can re create the column and add the records of that column you left in the other table.
Once you have resolve the apply of the snapshot you shoudn't have any problems. Even if you don't use stored procedures to replicate. If the transactions apply directly to the subscriber (with out using a sp), SQL still uses column names for inserts and updates. So if you add a column at the subscriber, replication shoudn't fail. For me it is better to use sp to replicate transactions.
Even if the column you add it is not at the end of the subscriber table, and it is in the middle, SQL still know wich columns to modify.
But you must be sure to allow NULLS in the new column. IF you need to use a default, you can create the constraint in that column, but you will have to modify the stored procedure, specifying to insert the DEFAULT in that column.
For example:
In publisher:
CREATE TABLE T1 (ID INT NOT NULL, Desc VARCHAR(255) NOT NULL)
In Subscriber:
CREATE TABLE T1 (ID INT NOT NULL, Desc VARCHAR(255) NOT NULL, NewCol Int NULL)
If in the publisher you insert:
INSERT INTO T1 VALUES (1, 'Wath ever')
SQL will use the sp and will replicate it like this:
EXEC sp_MSIns_T1 (1, 'Wath ever')
And it will replicate to the subscriber but in will leave the NewCol column with NULL.
If you have a default in that column, you will have to modify the sp, to be like this:
ALTER PROC sp_MSins_T1 @c1 INT, @c2 VARCHAR(255)
AS
INSERT INTO T1
(Id, Desc, NewCol) --here you will have to add the new column name
VALUES
(@c1, @c2, DEFAULT) --and here you add the DEFAULT.
March 24, 2004 at 9:02 am
Thank you very much, just wanted to make sure that potential issue was covered beforehand.
March 24, 2004 at 11:15 am
Agree. I should warn (Does this word exists? I meant warning) everyone before.
May 11, 2004 at 4:06 pm
Racosta,
Just what i needed... Thanks a million
Aditee Rele
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply