Solved

CDC audit

Posted on 2016-07-20
17
84 Views
Last Modified: 2016-08-04
hi all,

I heard that Idera compliance manager can't audit table that is part of replication and the before and after value can't be capture, is that right?

how about MS CDC?
0
Comment
Question by:marrowyung
  • 10
  • 6
17 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41721119
I don't know idera compliance manager, but we use MS CDC and it does use the same underlying features as replication so we have to deal with everything as if we had replication turned on for the tables we have under CDC.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41722124
sorry, so it means if the table is under merge replication, it still auditable on who modify the record , when, by what  application, from which host and what is the queries modify that?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41732331
sorry I don;t know anything about the idera compliance manager to be able to answer your questions any better than what I provided about CDC behavior.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41737040
chris,


sorry, on vocation for the whole week.

you mean CDC can audit, and even provide before and after value for any table under merge replication?

any step by steps procedure on how to set it up?

any script to find out the existing configuration of CDC ?
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 250 total points
ID: 41738483
Yes, CDC does audit type functions where you can see before and after values.  There are a lot of articles that lay out the steps and features available.  There is also Change Tracking which is described in one of the MS articles below, but it does not capture the historic data to show the before.  The Simple Talk article is quite detailed for set up steps and showing how you can use it.
You can see what if any databases are set up for CDC with this
SELECT [Name] AS DBName, is_cdc_enabled FROM sys.databases;

Open in new window

If any are enabled, you can see what tables are set up by running this on the database:
SELECT  * FROM cdc.change_tables;

Open in new window


https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/
https://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/bb933994.aspx
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41738752
"There is also Change Tracking which is described in one of the MS articles below,"

yes, one more feature since SQL 2008,

but are you sure CDC can audit table in merge replication? this is the most important part instead of how  to set it up.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41739278
Other than saying I have read that they will work together, I can't tell you a definitive answer since I do not use both.  Not much more I can do to help, you have tapped the extent of my experience.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41740088
ok. the only thing left is to try it myself by this sense.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:marrowyung
ID: 41740289
chris,

"I can't tell you a definitive answer since I do not use both.  "

you mean you don't use CDC and Idera CM ?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41740681
yes, and I don't use CDC and replication.  we use CDC as part of our DW process, grabbing iterative updates to process hourly instead of daily.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41741700
"  we use CDC as part of our DW process, grabbing iterative updates to process hourly instead of daily."

hi,

so no merge replication here?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41741720
No I haven't used replication in production in about 10 years and then it was only Transactional replication.  I have not used them together.  I did research on Merge replication for another client since then but I did not do the install.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41741732
ok ok . the MS SQL server world is very hard by replication! A lot of thing can't be done. thoese talbe in replication can't put it RAM too.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41742248
I can see that you still struggling with Merge Replication & CDC. I guess you're company must be one of the few in all world using this kind of solution so I think the best thing to do is to get a test environment set with the same solution and perform a POC to see how it works.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41743600
then I will setup CDC, I already have Idera CM setup but that one don't do good for table under merge replication.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41743604
I think  I need to study more on CDC then.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41743608
you know, even Idera will say sth B.S. about CM on table with merge  replication, it said it will be very slow but it doesn't mean it can't do that audit.

we can only audit few tables under merge replication but not all. then that's it.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Not many admins are aware that GPOs can be activated and deactivated time-based. Time to change that :)
Creating and Managing Databases with phpMyAdmin in cPanel.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now