Solved

CDC audit

Posted on 2016-07-20
17
122 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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
 
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 47

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

816 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

7 Experts available now in Live!

Get 1:1 Help Now