sql 101 for dynamics nav n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL 101 for Dynamics NAV PowerPoint Presentation
Download Presentation
SQL 101 for Dynamics NAV

Loading in 2 Seconds...

play fullscreen
1 / 23

SQL 101 for Dynamics NAV - PowerPoint PPT Presentation


  • 176 Views
  • Uploaded on

SQL 101 for Dynamics NAV. Nick Haman. Agenda. SQL compared to C-Side Security Management and User Setup SQL Backup/Restore NAV/SQL Setup SQL Recommended Configuration SQL Management Studio basics Additional Information. SQL compared to C-Side. VSIFT vs. SIFT

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

SQL 101 for Dynamics NAV


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
    Presentation Transcript
    1. SQL 101 for Dynamics NAV Nick Haman

    2. Agenda • SQL compared to C-Side • Security Management and User Setup • SQL Backup/Restore • NAV/SQL Setup • SQL Recommended Configuration • SQL Management Studio basics • Additional Information

    3. SQL compared to C-Side • VSIFT vs. SIFT • Backup Strategy • SQL allows for Point-in-Time recovery • SQL database allows for easier integration • SRS (SQL Reporting Services) • SQL Query • .NET Integration

    4. SQL compared to Native • File size limitations on C-Side • Temp File size of 2GB is still a limitation on SQL • Native DB has limit of 256GB • Native database being removed in version 7

    5. Security Mgmt. and User Setup • Enhanced Security Model • Uses individual application roles • Slower on sync due to recreation of application roles • Requires Security Sync run manually if: • Insert / Modify / Delete of Windows Login • Security Model change • Restore a backup

    6. Security Mgmt. and User Setup • Convert a database • Update executable files • Update the application • Sync can be run per Login • Standard Security Model • Uses one application role • Runs sync automatically for Windows Logins • Run sync manually database restored • Standard is faster and easier to use

    7. Security Mgmt. and User Setup • Security managed through Classic Client • Login • Database Login setup in SQL • Requires Security Sync in both Security Models • Windows Login created in database during Security Sync

    8. SQL Backup/Restore • Recovery Model • Simple • All data in a database or filegroup is backed up • Can use Differential Backups between Full Backups • If restore needed, data is lost from last Full or Differential Backup to time of failure

    9. SQL Backup/Restore • Full • Allows for Transaction Logs for Point-in-Time recovery • Can use Differential Backups between Full Backups • If restored needed, data only lost from last Log Backup to time of failure • Bulk-logged • Special-Purpose model for bulk inserting data

    10. SQL Backup/Restore • SQL Backup • Full Backup • All data in a database or filegroup is backed up • Differential Backup • All data that has changed since last full backup • Transaction Log Backup • All log information since last Log Backup

    11. SQL Backup/Restore • SQL Backup compared to NAV Backup • No separation of: • Companies • Common data • Application Objects

    12. NAV/SQL Setup • Database Information (File>Database>Information) • Database • Database Name • sp_helpdb • Database Used (KB) • sp_spaceused • dbcc_updateuseagewill update sp_spaceused

    13. NAV/SQL Setup • Alter Database (File>Database>Information) • General • Database Name • sp_helpdb • Database Files and Transaction Log Files • Logical Name • sp_helpdb • File Name; File Growth; Maximum Size • select physical_name from sys.database_files

    14. NAV/SQL Setup • Collation • Collation Type/Collation Description • sp_helpsort • Options • Single User • EXEC sp_dboption ‘database name', 'single user', 'true‘ • EXEC sp_dboption ‘database name', 'single user', false‘

    15. NAV/SQL Setup • Recovery Model • sp_helpdb • sp_dboption ‘database name' • Integration • Objects • Settings are all for Integration such as creating views on tables or modeling of foreign key relationships

    16. NAV/SQL Setup • Advanced • Lock Timeout (File>Database>Alter Advanced tab) • Wait time if resource locked by another user • Set between 10-20 • select * from [$ndo$dbproperty] • Advanced • Always Rowlock (File>Database>Alter Advanced tab) • Holdover value for SQL 2000 32 bit • Most cases disable to free up lock memory

    17. SQL Recommended Configuration • Auto Create Statistics – enable • Overhead is minimal • Important to SQL Query Plans • Auto Update Statistics – enable • Overhead is minimal • Important to C/AL for FIND operations • Still recommend SQL job periodically to update

    18. SQL Recommended Configuration • Auto Grow – enable • Can create bottleneck and should only be used for emergency • Normally want to grow Data and Log manually during off peak hours by 10-25% • Database Compatibility Level • SQL Server 2008 or 2008R2 • Set to 100 (SQL Server 2008 level) • NAV uses OPTIMIZE FOR UNKNOWN query hint

    19. SQL Recommended Configuration • Trace Flag 4136 • If NAV 2009 SP1 and Compatibility level 100 – this is not needed • Still can be good for other databases/applications • Disables parameter sniffing • Max Degree of Parallelism • Set to 1 to prevent Parallelism

    20. SQL Recommended Configuration • Default Isolation Level • SERIALIZABLE by default • NAV 5.0 SP1/NAV 2009 SP1 Build 30482 and later • Can set to REPEATABLEREAD by updating $ndo$dbproperty table • NAV 2009 SP1 HF • Cursors changed to Dynamics FFO for Browse with OPTION (FAST x)

    21. DemoSQL Management Studio basics

    22. Additional Information • SQL Backups - Database Management • http://msdn.microsoft.com/en-us/library/ms187048.aspx • Database Properties • http://msdn.microsoft.com/en-us/library/ms188124.aspx • Blog Entry for SQL Server Recommendations • http://blogs.msdn.com/b/nav/archive/2010/09/28/microsoft-dynamics-nav-sql-server-configuration-recommendations.aspx

    23. Thank You