| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
11-20-09, 00:11
|
|
World Class Flame Warrior
|
|
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
|
|
|
|
Take a vote. I use cascade deletes on some occasions, such as for many-to-many relationships or when the deletes would not affect data that has value independent of the parent records.
__________________
If it's not practically useful, then it's practically useless.
blindman 
www.chess.com: "sqlblindman"
|
|
11-20-09, 04:17
|
|
Registered User
|
|
Join Date: Nov 2009
Posts: 4
|
|
Quote:
Originally Posted by dportas
Same way you access any other data. With a query. Usually in a stored procedure. You access CDC data with a SELECT statement.
You would use triggers rather than CDC? Any particular reason other than familiarity? One of the disadvantages of a trigger for audit is that the audit all happens in the transaction, which means your server works twice as hard. CDC reads the log and it does so asynchronously so the overhead is almost negligible.
If you only have Standard Edition then you don't have CDC so that's a reason to use triggers (or to upgrade).
|
I think u didn't got my point.
Still i am waiting for my proper answer.
Craig
Database Development Company San Diego
|
|
11-20-09, 08:19
|
|
SQL Server Street Fighter
|
|
Join Date: Nov 2004
Location: Down The Rabbit Hole
Posts: 7,979
|
|
|
|
Quote:
Originally Posted by Brett Kaiser
OK, NOW you need to back THAT tidbit up with some facts
|
when i get a moment i will post some code..
__________________
software development is where smart people go to waste their lives
|
|
11-20-09, 08:25
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Triggers in 2000 and earlier read the inserted and deleted tables from the log. In 2005 and later, the data for inserted and deleted are written to tempdb. This is considered much more efficient since the drives for tempdb are optimised for random access whereas log drives should be optimised for sequential write.
As such, if CDC reads the data it requires from the logs then I am surprised that this is being presented as a "negligible cost"
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
11-20-09, 08:28
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Quote:
Originally Posted by totaltech
I think u didn't got my point.
Still i am waiting for my proper answer.
|
I think he did. If you don't think he did you will need to elaborate on your question.
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
11-20-09, 10:38
|
|
World Class Flame Warrior
|
|
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
|
|
Quote:
Originally Posted by pootle flump
Triggers in 2000 and earlier read the inserted and deleted tables from the log. In 2005 and later, the data for inserted and deleted are written to tempdb. This is considered much more efficient since the drives for tempdb are optimised for random access whereas log drives should be optimised for sequential write.
As such, if CDC reads the data it requires from the logs then I am surprised that this is being presented as a "negligible cost"
|
You are in serious danger of impressing me.
A link to a white paper or BOL reference would be nice, though.
__________________
If it's not practically useful, then it's practically useless.
blindman
www.chess.com: "sqlblindman"
|
|
11-20-09, 14:46
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
|
|
11-20-09, 16:22
|
|
The SQL Apostle
|
|
Join Date: Jul 2003
Location: The Dark Planet
Posts: 1,394
|
|
Quote:
Originally Posted by blindman
A link to a white paper or BOL reference would be nice, though.
|
Looking for this ???
__________________
Get yourself a copy of the The Holy Book
order has no physical Brett in The meaning of a Kaiser . -database data
|
|
11-20-09, 16:39
|
|
World Class Flame Warrior
|
|
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
|
|
Quote:
Originally Posted by pootle flump
You are in serious danger of disappointing me
|
Thank God I've never given a crap.
But now I want to know how you dig up those old posts so easy. I can't get the search on this forum to do jack.
__________________
If it's not practically useful, then it's practically useless.
blindman
www.chess.com: "sqlblindman"
|
|
11-20-09, 17:12
|
|
World Class Flame Warrior
|
|
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
|
|
Quote:
Originally Posted by Enigma
|
Really?
Quote:
When you have a schema change on your source table, you should do the following:
1. Change the schema of your source table.
2. Create a new capture instance. That new capture instance is created with the new (changed) schema of the source table.
3. Wait for a change to occur, and then read the minimum LSN from the new capture instance (using sys.fn_cdc_get_min_lsn(new capture instance name)).
4. Read and process all changes from the old capture instance up to but not including the first LSN from the new capture instance.
5. Disable the old capture instance.
6. Update all references to the old capture instance name with the new name.
7. Continue reading from the new capture instance.
|
...or here Introduction to Change Data Capture (CDC) in SQL Server 2008
Quote:
|
UPDATE operations always result in two different entries in the tracking table. One entry contains the previous values before the UPDATE is executed.
|
Yuk. I'll stick with my trigger scripts, thank you.
__________________
If it's not practically useful, then it's practically useless.
blindman
www.chess.com: "sqlblindman"
|
|
11-23-09, 05:15
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Quote:
Originally Posted by blindman
Thank God I've never given a crap.
|
Nonsense - you exist for forum approval.
Quote:
Originally Posted by blindman
But now I want to know how you dig up those old posts so easy. I can't get the search on this forum to do jack.
|
Archivist
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
11-23-09, 10:34
|
|
World Class Flame Warrior
|
|
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
|
|
Quote:
Originally Posted by pootle flump
Nonsense - you exist for forum approval.
|
Sometimes the truth hurts more than I can bear...
__________________
If it's not practically useful, then it's practically useless.
blindman
www.chess.com: "sqlblindman"
|
|
11-23-09, 10:47
|
|
Wage drone 24601
|
|
Join Date: Jan 2003
Location: Massachusetts
Posts: 4,899
|
|
Quote:
Originally Posted by pootle flump
Triggers in 2000 and earlier read the inserted and deleted tables from the log. In 2005 and later, the data for inserted and deleted are written to tempdb. This is considered much more efficient since the drives for tempdb are optimised for random access whereas log drives should be optimised for sequential write.
As such, if CDC reads the data it requires from the logs then I am surprised that this is being presented as a "negligible cost"
|
You may be forgetting the Log Buffer, which is a memory structure, and can be read just about any way you like. Reading the Log Buffer for retrieving records may be quicker than registering a table (and writing a few more log entries) in tempdb.
|
|
11-23-09, 11:25
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
I was at SQLBits this weekend and one of the presenters mentioned that CDC uses the replication transaction reader and that it is a very cheap process.
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
SQL Server 2008 functionality for Audit Log
