SQL Server Configuration Recommendations

There is very interesting Blog post on Microsoft Dynamics NAV Team Blog about recommendations for SQL Server Setup.

Michael De Voe has written one document which consists of pointers for setting up SQL 2005 or SQL 2008 for Dynamics NAV.

Here's short list of recomendations:

• Max Server Memory,
• Auto-Create Statistics,
• Auto-Update Statistics,
• Auto-Grow,
• Database Compatibility Level,
• Trace Flag 4136,
• Trace Flag 4119,
• Data files for TempDB,
• Disk Alignment,
• Read Committed Snapshot Isolation (RCSI),
• Max Degree of Parallelism,
• Dynamics NAV Default Isolation Level,
• Dynamics NAV "Lock Timeout",
• Dynamics NAV "Always Rowlock",
• Maintenance Jobs,
• Instant File Initialization,
• Optimize for Ad Hoc Workloads,
• Page Verify,
• Lock Pages in Memory.

Here are links to:
Dynamics NAV Team Blog
Michael's SQL recomendations

SQL 2010 Login Error

Today I tried to test Dynamics NAV 2009 client on new SQL 2010 R0.

Installation went without problems, user creation also went without problem, and then I started classic client with task to create data base. Now problems started.

On my server I had two types of data base users: domain user (Windows authentication) and data base user.

Thing that bothered me was that I could log in with windows account but couldn't log in with database account.

After I verified all of SQL Server parameters, user permissions, and data base properties I found what was "wrong": SQL 2010 has new security feature that doesn't allow logging of accounts that have blank passwords.

Even thou your account has blank password with ALTER LOGIN function and even thou query returns Command(s) completed successfully You still won't be able to log in.

After setting passwords to my database account I was able to log in to SQL server and create/restore databases.