January 11, 2013 at 7:15 am
Hello --
I am hoping to set up a maintenance plan that will check the integrity of one of our databases. The data within the database is static, and does not change very often. The idea is to establish a baseline status for the database in question, and then run the plan every morning to determine if any changes to the tables or fields had taken place within the past twenty-four hours.
My plan is to use SSIS to accomplish this purpose, and I had several questions concerning it:
1. The database server is running SQL Server 2008 R2 standard edition. If I am not mistaken, SSIS does not come bundled with that release. Do I need to upgrade to enterprise edition, or can SSIS be installed without adversely effecting the system?
2. If the answer to the above question is negative, can I set up a separate server with SSIS, and have the remote system conduct the checks that I described earlier?
3. If SSIS is not the best way to go, what would be a better approach here?
Thanks.
January 11, 2013 at 7:19 am
You can run SSIS on standard edition.
If the data is static and you are comparing the values to ensure they haven't changed, what are you using as a source to compare the values against?
Or are you just doing a checkdb?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 11, 2013 at 8:38 am
Hello --
Thanks for your reply. I am doing a comparison of the database to a date where it is determined the information that is present in the database has been verified to be what should be present. Essentially I will be establishing a "Day 0" as the standard to which comparisons will be made.
If a change is made, we will want to know when it happened, why it happened, and most importantly - should it have happened. If all three questions answered positively, the changes made will be kept in a log, and the process will begin again.
I did not see a reference to SSIS in the SQL Server start menu group, so I do not believe it has been previously installed onto the server. As far as installing SSIS, do we need a separate license for that, or does that come bundled with standard edition. Also, is installing SSIS simply going to the DVD medium, running the install program, and selecting the SSIS product?
Thanks.
January 11, 2013 at 9:03 am
kaplan71 (1/11/2013)
Hello --Thanks for your reply. I am doing a comparison of the database to a date where it is determined the information that is present in the database has been verified to be what should be present. Essentially I will be establishing a "Day 0" as the standard to which comparisons will be made.
If a change is made, we will want to know when it happened, why it happened, and most importantly - should it have happened. If all three questions answered positively, the changes made will be kept in a log, and the process will begin again.
I did not see a reference to SSIS in the SQL Server start menu group, so I do not believe it has been previously installed onto the server. As far as installing SSIS, do we need a separate license for that, or does that come bundled with standard edition. Also, is installing SSIS simply going to the DVD medium, running the install program, and selecting the SSIS product?
Thanks.
SSIS falls under the SQL license. So as long as you install it on an already licensed box - then you are good to go. If you install on a separate server, you will need to license that additional server for SQL Server.
To install, run setup for SQL Server from the DVD and select the Inegration Services option.
The process you are describing could also be done with tsql if you are more comfortable doing it that way.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 11, 2013 at 10:33 am
Hello --
I am open to using T-SQL as an option. I am not very adept at T-SQL so if you could provide the general syntax that would be used, I should be able to adapt it to our scenario.
Thanks.
January 11, 2013 at 11:08 am
kaplan71 (1/11/2013)
I am hoping to set up a maintenance plan that will check the integrity of one of our databases. The data within the database is static, and does not change very often. The idea is to establish a baseline status for the database in question, and then run the plan every morning to determine if any changes to the tables or fields had taken place within the past twenty-four hours.
Kaplan, I'm not entirely sure I'm clear on what you're intending to do.
Integrity checks, as discussed above, are done with DBCC commands, usually starting with DBCC CheckDB. However, that's not going to check for schema changes or data changes, it's going to check for corruption of the database, a very different issue.
Now, if you're looking to check the schema of the database, you have two different methods available to you without going into really odd design strategies. First is what's called ddl triggers. What they'll do is log whenever schema changes are made and the like. Start with this article: http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes. The other option for this is to use something like SQLCompare, from Redgate (they own the site but they're also one of the best out there) to compare a backup from yesterday to what's in production today. Neither of these are jobs you'd run.
Now, for data compare, you'd want to do some kind of auditing instead of doing row by row data checks between an older backup and current information. I'd recommend either you setup triggered auditing (you could write out to a local text file using BCP or any other number of consolidation techniques) or using built in auditing and monitoring the file for modifications.
Can you be more explicit in what, exactly, you're hoping to achieve with this process? From what you've mentioned, I fear this rabbit hole is a lot deeper than you'd expect, and a lot of us do standard integrity checks on the DBs without doing modification monitoring so that was what was assumed you wanted to do. I did the same thing until I reread your post four or five times. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 11, 2013 at 7:15 pm
Hello --
The data that has been entered into fields and tables of the database in question is, for the most part, static. There should not be any unexpected modifications to any field or table. What I am trying to accomplish is to confirm what was in the database yesterday is there today. If a change to any field or table is detected, a report or a notification to the administrators should be generated by Database Mail. Essentially I am doing a comparison of the database from one day to the next.
The integrity checks of the backups that you mentioned in your posting are a separate project that will be instituted in the maintenance plans once this issue has been resolved.
The auditing suggestion, correct me if I am wrong, sounds like that will accomplish what I am trying to accomplish. Could you provide more information on the BCP and built-in auditing and monitoring file modifications?
January 14, 2013 at 7:45 am
Hello --
I was doing further research into this issue over the weekend, and I came across Change Data Capture (CDC). Here is the information that I found about it:
Change Data Capture (CDC) captures the changes made to the table and stores them in the same database with different name and the basic purpose of CDC is to keep track of what has changed in the table along with their previous value.
This could be the answer to the original posting, but I wanted to get feedback on it.
Thanks.
January 23, 2013 at 1:50 pm
Hello --
I went through the properties of the database within the Studio, and one idea that I had was to have the Change Tracking option set to True for the database. I had several follow-up questions:
1. As far as tracking changes to the database, will this work?
2. Can I set up notifications to be sent via Database Mail to the administrator when a change to the database occurs?
The article that I read that led me to this posting was the following:
http://msdn.microsoft.com/en-us/library/bb964713%28v=sql.105%29.aspx
Thanks.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply