Materialized View Logs on Log shipped secondary server database

Question

Materialized View Logs on Log shipped secondary server database

sqlbi.vvamsi

Ten Centuries

Points: 1194
More actions
November 30, 2012 at 10:31 am

#267665

Hello,
I've a database in primary server that log ships to a database in secondary server. I want to capture only deltas after log shipping is done. To do that, I'm wondering if I could implement materialized view log on each table in database on secondary server. pls let me know your 2 cents. Thanks.
Note:I cannot have access to primary server.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Not sure I follow what you're trying to do. What do you mean by 'materialized view log'?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlbi.vvamsi Ten Centuries Points: 1194 More actions · Answer 2

Based on what I know, Materialized View Log is a master table that can capture deletes/inserts/updates just like CDC tables.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

There's no such term or feature in SQL Server as 'materialised view log'. A brief google search shows it's an Oracle feature.

What are you trying to achieve here?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlbi.vvamsi Ten Centuries Points: 1194 More actions · Answer 4

That's right. I'm looking for an oracle solution. Perhaps, I'm at wrong place. Thanks.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

sqlbi.vvamsi (11/30/2012)
Based on what I know, Materialized View Log is a master table that can capture deletes/inserts/updates just like CDC tables.

You are correct, Materialized Views are an Oracle concept.

Having said that and just to clarify your statement above...

"Master table" is the base table (or tables) used to create a Materialized View.

"Materialized view" is a snapshot-in-time copy of a base table, or a subset of it.

"Materialized view log" is a log on the base table used make materialized view refresh quicker.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.