some rough notes on preparing a table which is written to by SQL Server replication.
It is best to replicate in one direction only.
If bi-directional replication is required, then have an 'up' staging database, and a 'down' staging database, to keep the database transfer simplex (one-direction per staging database).
if you wish to preserve identity columns when replicating records,
then for the target staging table:
- do NOT use the IDENTITY property on the key field, as this auto generates a new value on INSERT
- do not use foreign keys
- consider using uniqueidentifier rowguidcol to still flag the field as being unique (this is an that uses GUID values)
the target table is usually in a staging database.
You need another process to copy data from the staging database, into the final destination.
SSIS can be used, although I find LINQ to SQL quicker to develop with.
the destination table should have the same properties as the staging table (i.e. no IDENTITY),
except that it can have foreign keys.
______________________________________________
here is some SQL to alter a table, to prepare it as a replication target:
______________________________________________
SQL Server Replication creates target tables for you
Apparently Replication can create the target tables for you.
However on a project we are using LING2SQL to migrate data from replicated tables to target tables,
so we needed to create the replicated tables ourselves (we only set up replication in production not in dev environment).
It is best to replicate in one direction only.
If bi-directional replication is required, then have an 'up' staging database, and a 'down' staging database, to keep the database transfer simplex (one-direction per staging database).
if you wish to preserve identity columns when replicating records,
then for the target staging table:
- do NOT use the IDENTITY property on the key field, as this auto generates a new value on INSERT
- do not use foreign keys
- consider using uniqueidentifier rowguidcol to still flag the field as being unique (this is an that uses GUID values)
the target table is usually in a staging database.
You need another process to copy data from the staging database, into the final destination.
SSIS can be used, although I find LINQ to SQL quicker to develop with.
the destination table should have the same properties as the staging table (i.e. no IDENTITY),
except that it can have foreign keys.
______________________________________________
here is some SQL to alter a table, to prepare it as a replication target:
1: --alter tables to prepare them as targets for replication 2: --in particular, remove IDENTITY properties, to prevent auto creation of IDs 3: -- 4: --This is so we can have the same SQL scripts to create tables for eLicensing_WEB + eLicensing_DOWN_FromWEB 5: -- PRS.tblPRSLicence ======================================================= 6: alter table PRS.tblPRSLicence drop CONSTRAINT [PK_tblPRSLicence] 7: alter table PRS.tblPRSLicence drop column PRSLicenceAuto 8: alter table PRS.tblPRSLicence add 9: PRSLicenceAuto int NOT NULL -- replication target, so not using IDENTITY 10: ALTER TABLE PRS.tblPRSLicence add CONSTRAINT [PK_tblPRSLicence] 11: PRIMARY KEY CLUSTERED 12: ( 13: [PRSLicenceAuto] ASC 14: )
______________________________________________
SQL Server Replication creates target tables for you
Apparently Replication can create the target tables for you.
However on a project we are using LING2SQL to migrate data from replicated tables to target tables,
so we needed to create the replicated tables ourselves (we only set up replication in production not in dev environment).
Comments
Post a Comment