8 Replies Latest reply: Feb 6, 2009 4:11 AM by davidmaltby RSS

database maintenance in version 3.1

freemen

I'm a little confused by the changes in v 3.1 regarding database maintenance. In the Global Settings, you choose the time intervals to keep both uncompressed and compressed data. That much is like previous versions.

Now you also have Database Maintenance Settings. How do the two work together? For instance, if I elect to keep uncompressed data for 1 hour and compressed data for 30 days, and i also set the "compress databae and log files frequency" to once every ten days, what exactly is going to be happening?

I guess i don't understand how the settings  chosen on the "Global Settings" screen work in conjunction with the settings on the "Database Maintenance Settings" page.

Can anyone give me an explanation based on some hypothetical settings on both screens?

I would really appreciate it.

Oh, one more thing. Are the NetFlow data handling settings treated independently from the database maintenance setting in Orion NPM? Or, is there some netflow data summarization performed by Orion.

 
  • Re: database maintenance in version 3.1
    davidmaltby

    You know, now that I think about the wording of both of those, I see the confusion.  So, to explain, the uncompressed and compressed settings are the same as they were in NTA 3.0.  They have to do with the flow data itself.  The Maintenance setting indicates how often to perform a shrink on the SQL Server's database and transaction log files.  In Server SQL, those files can automatically grow as new database data and transactions in the database are made, but those files never get shrinked by the SQL Server.  It is left up to the database administrator or the application using the database to instruct SQL Server to shrink those files.

    Further  the NetFlow database maintenance is completely independent of the Orion maintenance.

    Hope this helps,

    David Maltby

    • Re: database maintenance in version 3.1
      freemen

      Thanks David. That's what I was after. A couple more questions if you don't mind.

      Under Global Settings, raw, uncompressed data is kept for the designated time, then it is compressed and kept for that designated time? Is that right? After that, it becomes "expired flow data"?

      If not, then what exactly is "expired flow data"? What is the function of the "IPs to process at a time" setting?

      Thanks.

      • Re: database maintenance in version 3.1
        freemen

        David?

      • Re: database maintenance in version 3.1
        davidmaltby

        Yes.  The uncompressed data is kept that way for 1 hour by default.  Then it is compressed and stays in the database until it is deleted.  Which is by default 30 days.

        The "IPs to process at a time" is for the nightly maintenance.  It controls the number of IPs that are cleaned up in one transaction to the SQL Server.  Some customer databases have restrictions on how large their transaction log can grow, so they may need to make this number smaller.  It was questionable as to whether we should have exposed this setting to the customer.  You typically shouldn't need to adjust it.

        Thanks,

        David

        • Re: database maintenance in version 3.1
          freemen

          What do you mean by IPs that are cleaned up? What exactly is happening? Sorry to be so dense.

          • Re: database maintenance in version 3.1
            davidmaltby

            If there are no more stored flows in the database with conversations involving an endpoint IP address, then its DNS information is deleted from the database during the nightly maintenance.

            • Re: database maintenance in version 3.1
              borgan

              OK, just to summarize then. Previous NTA versions allowed for NF data to be compressed and then kept for a certain period of time before being deleted, but that was all there was to "maintenance" of NetFlow data. Correct?

              The Orion Nightly Maintenance procedures and DB compaction procedure did not effect NF data?

              In version 3.1, we still have compression and retention settings, but now we also can set other maintenance procedures that are unique to NF data. Right?

              As I understand it those procedures are:

              - expired data deleted (what does expired mean?)

              - orphaned IP endpoint addresses and associated data deleted

              - database and log files compressed (how is this different from compressed data under the Global Settings menu?)

              Sorry for the persistence, but I want to fully understand exactly what the maintenance procedures are doing.

              Thanks for your patience.

              • Re: database maintenance in version 3.1
                davidmaltby

                Yes. That was all to the "maintenance" in previous versions.

                I don't believe, I'd have to check, but Orion's Maintenance didn't perform the SQL Server operation called "shrink" on the database file for the PRIMARY filegroup nor on the transaction log for the database.

                Yes, compression and retention settings are continuously executed by the service at frequent times.  The Nightly Maintenance occurs only once a day.

                Once data is "expired", then the service (who collected it) will delete it out of the database.

                For the "database and log files compressed", this is the SQL Server "shrink" operation that is performed on these files.  Google it up.  Here's an explaination:

                http://sqlserver2000.databases.aspfaq.com/how-do-i-reclaim-space-in-sql-server.html

                The compression that we do on flows themselves, combines older flows by conversation and sums up the total traffic numbers.  So first your data in the details table is uncompressed.  By default after an hour it is compressed into the Summary1 table by all the same conversations that occured in a 15 minute period.  After that data in the Summary1 gets 1 day old, then it again is grouped by conversation for 1 hour grouping and put into the Summary2.  After 3 days, then it is summaried into a 1 day grouping and put in the Summary3 table.  Then after 30 days (by default), it expires and it deleted from the Summary3 table.

                Thanks,
                David