Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

CDC audit

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
marrowyung
Asked:
marrowyung
  • 10
  • 6
2 Solutions
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
marrowyungAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
marrowyungAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
marrowyungAuthor Commented:
"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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
marrowyungAuthor Commented:
ok. the only thing left is to try it myself by this sense.
0
 
marrowyungAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
marrowyungAuthor Commented:
"  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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
then I will setup CDC, I already have Idera CM setup but that one don't do good for table under merge replication.
0
 
marrowyungAuthor Commented:
I think  I need to study more on CDC then.
0
 
marrowyungAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now