1 / 23

SQL 101 for Dynamics NAV

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

hume
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related