On-the-fly schema changes with transaction replication. How to?

Question

On-the-fly schema changes with transaction replication. How to?

debrucer1

SSCertifiable

Points: 5366
More actions
October 9, 2006 at 10:09 am

#91794

I have read the posts about "seamless replication"; but this process seems to be too manual. My client's application changes the schema on the fly, so the database could be in any state at any time, depending on what user has run what software. We are only replicating to one node, so at least the direction is always known; but how do I check for schema changes prior to each scheduled replication? It is early yet, Monday morning, a holiday at that... I've got a week to come up with a solution, and three weeks to make it work without intervention... and then one week to roll it out.
Any suggestions would be appreciated.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

Schema changes are not automatically replicated in SQL 2000 T-REP. So you would need to modify the code to include the appropriate commands before issuing the schema change.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

debrucer1 SSCertifiable Points: 5366 More actions · Answer 2

yes, that is why I asked the question "but how do I check for schema changes prior to each scheduled replication? "

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

homebrew01 SSC Guru Points: 55544 More actions · Answer 3

You could run a "DIFF" between the databases, to identify difference between each table in your Publication Database and Subscription Database and output the results to a work table. Then perhaps run scripts that look at the work table of "diffs", and create scripts to modify your publications. Sounds a bit messy.

Are the Publication & Subscriber Databases on the same server ?

Here's some generic "DIFF" scripts I wrote that might be helpful. They show tables where colums attributes are different from 1 db to another, and also check for columns that are in one table, but not in the other .. ie ... dropped or added columns.

+++++++++++++++++++++++++++++++++++++++++++

--DATABASE "DIFF" using PRD and TRN databases
-- 1) Write Table schemas for PRD database to work table
-- 2) Write Table schemas for TRN database to work table
-- 3) Write differences in existing columns to another work table
-- 4) Look for columns not in PRD, but in TRN
-- 5) Look for columns not in TRN, but in PRD
-- AdninDB is a database to store the DIFF info.
====================================
-- Dump out schemas for a database
-- Create the work table if it doesn't already exist.
CREATE
column_length [sql_variant], column_precision [sql_variant], column_scale [sql_variant], column_allownull [sql_variant],
column_default [sql_variant], column_description [sql_variant])

truncate

use
DECLARE
Set
DECLARE
SELECT name FROM sysobjects where type = 'U' and status > 1 order by name
OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT
SELECT
obj.[name] AS 'table_name', col.colorder AS 'column_order', col.[name] AS 'column_name',
typ.[name] AS 'column_datatype', col.[length] AS 'column_length',
CASE WHEN typ.[name] = 'decimal' THEN CAST(col.[prec] AS nvarchar(255)) ELSE '' END AS 'column_precision',
CASE WHEN typ.[name] = 'decimal' THEN CAST(col.[scale] AS nvarchar(255)) ELSE '' END AS 'column_scale',
convert(varchar(254), rtrim(substring(' YES',(ColumnProperty (col.id, col.name,'AllowsNull')*3)+1,3))) as 'column_allownull',
ISNULL(com.text,'') AS 'column_default',
ISNULL(ext.value,'') AS 'column_description'
FROM sysobjects obj
INNER join syscolumns col on obj.id = col.id
INNER JOIN systypes typ ON col.xtype = typ.xtype
LEFT join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @table_name, N'column', null) ext on col.name = ext.objname collate SQL_Latin1_General_CP1_CI_AS
LEFT OUTER JOIN syscomments com ON col.cdefault = com.id
WHERE obj.name = @table_name AND typ.[name] <> 'sysname'
ORDER BY col.colorder
--CODE ENDS HERE
FETCH NEXT FROM tablenames_cursor INTO @table_name
END
CLOSE
DEALLOCATE

select

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Differences between existing fields
select
a.table_name, a.column_name, a.column_dataType as 'PRD_FieldType', b.column_dataType as 'TRN_FieldType',
a.column_length as 'PRD_length', b.column_length as 'TRN_length', a.column_order as 'PRD_Column_Seq', b.column_order as 'TRN_Column_Seq',
a.column_allownull as 'PRD_Nullable', b.column_allownull as 'TRN_Nullable', a.column_scale as 'PRD_scale', b.column_scale as 'TRN_scale',
a.column_precision as 'PRD_precision', b.column_precision as 'TRN_precision', a.column_default as 'PRD_default', b.column_default as 'TRN_default',
a.column_description as 'PRD_description',b.column_description as 'TRN_description'
from
join
on a.table_name = b.table_name
and a.column_name = b.column_name
and (a.column_dataType <> b.column_dataType -- Data type mismatch
or a.column_length <> b.column_length -- Field length mismatch
or a.column_order <> b.column_order -- Column order different
or a.column_scale <> b.column_scale
or a.column_precision <> b.column_precision
or a.column_default <> b.column_default
or a.column_description <> b.column_description
or a.column_allownull <> b.column_allownull) -- nullable

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Find missing fields. In PRD but no TRN
select
from
join
left
where
group
order

-- Find missing fields, in matching tables. In TRN but no PRD
select
from
join
left
where
group
order

debrucer1 SSCertifiable Points: 5366 More actions · Answer 4

Thanks for the response, and for the scripts. I will be looking at them carefully with the next five days (probably weekends included )

No, the databases are not on the same server, in fact, it gets a bit more complicated. The source DB is on a mirrored pair of hosts (done with EMC RepliStor), file level mirror, active/passive configuration, and the target site is in Arizona connected with a T3 line.

To further complicate matters, the Reporting Services are installed in a funky way to keep from having to pay for an enterprise license. You can't connect two sets of processes to one set of RS databases without enterprise edition - so when I replicate the RS DBs, I will also have to figure out which ones to point to in Arizona - where there is just one pair.

Okay, TMI...

But, believe me, your help is appreciated very much.

David

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982