Options

How to check consistency of NAV on SQL database

Johannes_NielsenJohannes_Nielsen Member Posts: 206
Hi

Running NAV 5.0 on MS SQL 2008

I'm trying to figure out how to ensure that my backup routines are up to scratch.

As I've been told, the NAV database can be corrupt without SQL server knowing about it. I a worst case scenario this could mean one would be performing backups that are not restoreable.
One should do a restore test, for sure, but I'm not sure running it every day is nessasary...

But how Should you check for inconsistency of a NAV on SQL?
  • By running the Test database from File>Database...
  • By running some SQL utility
  • By doing a FBK backup and restoring this (rebuild all keys)

This thread states that doineg FBK backups on SQL is best avoided
http://www.mibuso.com/forum/viewtopic.php?f=23&t=26350&hilit=FBK+not

So I'm confused :-k

Any insights are welcome :P
Best regards / Venlig hilsen
Johannes Sebastian
MB7-840,MB7-841

Answers

  • Options
    ara3nara3n Member Posts: 9,256
    If you want to make sure SQL backup is good.

    Restore it on another server, and do a NAV backup. You don't need to restore the nav backup.


    I have not seen a sql db getting corrupted on it's own.

    What I have seen is hardware failure that corrupted sql db.
    Or modifying data directly through sql. E.g. inserting lowercase on code field
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    strykstryk Member Posts: 645
    To check the database integrity:

    1. You should ensure that the database property "Page Verify" is set to "CHECKSUM", which is some kind of on-the-fly check up
    2. Run a DBCC CHECKDB periodically (e.g. via TSQL or Maintenance Plan). This checks the physical integrity of the SQL Server database.
    3. Optionally you could run the NAV internal "Database Test" to verify the logical integrity

    Generally skip the old FBK native backup stuff with SQL Server - takes too long, causes too much workload and finally tells shit about the real SQL integrity (excuse my French) ...

    A SQL Backup is a 1:1 copy of the database (more or less, actually all occupied Pages), thus potential problems are included.
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Options
    Johannes_NielsenJohannes_Nielsen Member Posts: 206
    Thanks for the replies guys
    stryk wrote:
    To check the database integrity:

    2. Run a DBCC CHECKDB periodically (e.g. via TSQL or Maintenance Plan). This checks the physical integrity of the SQL Server database.
    3. Optionally you could run the NAV internal "Database Test" to verify the logical integrity

    A SQL Backup is a 1:1 copy of the database (more or less, actually all occupied Pages), thus potential problems are included.

    Do you thereby mean that the NAV Database Test, is required to fully rule out any corruptions?
    Best regards / Venlig hilsen
    Johannes Sebastian
    MB7-840,MB7-841
  • Options
    Johannes_NielsenJohannes_Nielsen Member Posts: 206
    ara3n wrote:
    What I have seen is hardware failure that corrupted sql db.
    Or modifying data directly through sql. E.g. inserting lowercase on code field

    That correspons with the stories of network problems or long time running reports and hardware errors, I've heard of, could cause corruption of the datebase.
    Best regards / Venlig hilsen
    Johannes Sebastian
    MB7-840,MB7-841
  • Options
    strykstryk Member Posts: 645

    Do you thereby mean that the NAV Database Test, is required to fully rule out any corruptions?

    No. Actually I think the NAV internal DB TEst is rather pointless. The physical integrity is what really matters - DBCC CHECKDB.
    The logical stuff in NAV - Table Relations etc. - has IMHO pretty low priority ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Options
    Johannes_NielsenJohannes_Nielsen Member Posts: 206
    stryk wrote:

    Do you thereby mean that the NAV Database Test, is required to fully rule out any corruptions?

    No. Actually I think the NAV internal DB TEst is rather pointless. The physical integrity is what really matters - DBCC CHECKDB.
    The logical stuff in NAV - Table Relations etc. - has IMHO pretty low priority ...

    Okay, so I guess I might have been misinformed about NAV being corrupted, without SQL being so.

    Thanks for your insights guys! :mrgreen:
    Best regards / Venlig hilsen
    Johannes Sebastian
    MB7-840,MB7-841
Sign In or Register to comment.