Microsoft Corp. announced the release to manufacturing of Microsoft SQL Server 2008, the new version of the company's acclaimed data management and business intelligence platform.

SQL Server 2008 (code-named "Katmai") aims to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime.

SQL Server 2008 will also include support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects), but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them. According to Paul Flessner, senior Vice President, Server Applications, Microsoft Corp., SQL Server 2008 can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial, etc as well as perform search, query, analysis, sharing, and synchronization across all data types.

Other new data types include specialized date and time types and a spatial data type for location-dependent data. Better support for unstructured and semi-structured data is provided using the FILESTREAM data type has been added, which can be used to reference any file stored on the file system. Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system. Such files can be accessed both via Win32 file handling APIs as well as via SQL Server using T-SQL; doing the latter accesses the file data as a binary BLOB. Backing up and restoring the database backs up or restores the referenced files as well. SQL Server 2008 also natively supports hierarchical data, and included T-SQL constructs to directly deal with them, without using recursive queries.

Spatial data will be stored in two types. A "Flat Earth" (GEOMETRY or planar) data type represents geospatial data which has been projected from its native, spherical, coordinate system into a plane A "Round Earth" data type (GEOGRAPHY) uses an ellipsoidal model in which the Earth is defined as a single continuous entity which does not suffer from the singularities such as the international dateline, poles, or map projection zone "edges". Approximately 70 methods will be available when SQL Server "Katmai" ships to represent spatial operations for the Open Geospatial Consortium Simple Features for SQL, Version 1.1.

SQL Server includes better compression features, which also helps in improving scalability. It also includes Resource Governor that allows reserving resources for certain users or workflows. It will also include capabilities for transparent encryption of data as well as compression of backups. SQL Server Katmai will support the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be build around the Entity Data Model. SQL Server Reporting Services will gain charting capabilities from the integration of the data visualization products from Dundas Data Visualization Inc., which was acquired by Microsoft.

On the management side, SQL Server 2008 will include the Declarative Management Framework which allows configuring policies and constraints, on the entire database or certain tables, declaratively. The version of SQL Server Management Studio included with SQL Server 2008 supports IntelliSense for SQL queries. However, with the current CTP, it is limited to SELECT queries; it will be expanded to other T-SQL constructs over later releases. SQL Server 2008 will also make the databases available via Windows PowerShell providers and management functionality available as Cmdlets, so that the server and all the running instances can be managed from Windows PowerShell.

SQL Server 2008 is now available to MSDN and TechNet subscribers and will be available for evaluation download on Aug. 7, 2008. SQL Server 2008 Express and SQL Server Compact editions are available for free download today at http://www.microsoft.com/sqlserver.

As previously announced, pricing for SQL Server will not increase with SQL Server 2008.

More information is available at http://www.microsoft.com/presspass/presskits/sqlserver.

Recently I was giving training about the upgrade toolkit and the developer's toolkit and I noticed that not many people know about this undocumented feature of Dynamics NAV.

Sometimes, especially when doing upgrades in Dynamics NAV, you need to import a lot of objects into a database. Normally you then first export the objects into a .fob or .txt file and then you import them into the new database.

Did you know that there's another way to do this?

If you have an .fbk file of a database (backup file), then you can also use this .fbk file to import into another database.

If you import a .fbk file (File – Import, while the object designer is opened), then Dynamics NAV will look for all of the objects that are in the .fbk file, and will try to import those objects. Next Dynamics NAV will open the Import Worksheet, showing the objects in the .fbk file. Here you can decide what needs to happen with the objects during import (skip, replace, merge…) and when you click on OK the objects are imported, just as if you import a .fob file.

Nice, isn't it!

This is a question I got asked a few times the last few months, so why not write a little blog article about it.

An easy way to move user database files to a different location is by detaching, moving and attaching the user database.

(Of course, while you are doing this, people will not be able to work in the database. Make sure they logout of Dynamics NAV correctly.)

This database move can be done in SQL Server Management Studio (SSMS) so you don't have to type any code:

• Start SQL Server Management Studio
• Expand the server instance, expand Databases
• Right-click the database you want to move, and choose "Properties"
• In the Properties window, choose "Files" and write down the current file paths. Click "Cancel"
• Right-click the database again, and choose "Tasks - Detach..."
• Click "OK" in the next window
• Use Windows Explorer to move the data and log files (.mdf and .ldf) to the new location
• Right-click Databases, and choose "Attach..."
• In the "Attach databases" window, click "Add"
• In the "Locate database files" window, browse to the new location and select the .mdf file. Click "OK"
• In the details pane, verify that the new location is listed for both the .mdf and the .ldf file. Click "OK"
• In SQL Server Management Studio, choose "View - Refresh" and verify that your database is listed again under Databases

Alternatively you can back up the database, within Sql Server, and restore it, specifying a different location for the files on the options table or use the MOVE clause in the RESTORE command in Transact-SQL:

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

Keygroups have been available in Dynamics NAV for some time now, but since the release of Dynamics NAV 5.0 some new functions can be used to enable and/or disable these key groups via CAL code:

• [Ok :=] KEYGROUPENABLE(GroupName)

  Use this function to enable a key group.

• [Ok :=] KEYGROUPDISABLE(GroupName)

  Use this function to disable a key group.

• [Ok :=] KEYGROUPENABLED(GroupName)

  Use this function to check whether or not a specific key group is enabled.

When you have a look in the design of a table and open the list of Keys of that table you will notice that every Key has a KeyGroup property. You can see this property to specify the predefined key groups that a key belongs to. After you have assigned a key to one or more key groups, you can selectively activate or deactivate the key by enabling and disabling the key groups. In the past this was only possible by going into the Database Key Groups window.

To enable or disable a key group for sorting, click File, Database, Information and the Database Information window appears:

Then click Tables, Key Groups and the Database Key Groups window appears. You can then enable or disable a key group by clicking the appropriate button:

Unfortunately, it is very difficult to get an overview of the keys and tables that are affected when you enable or disable a key group. However, you can look at the design of any table and see the keys that have been defined for that table and whether or not the keys belong to a key group.

Adding a large number of keys to database tables decreases performance. However, by making the keys members of predefined key groups you can have the necessary keys defined and only activate them when they are going to be used.

And as from Dynamics NAV 5.0 we now have the possibility to do this in the CAL code.

This can be useful for reports that use certain keys but that are executed very rarely, for example only once a year. By only enabling these keys when they are needed you can avoid the performance overhead of maintaining them when they are not used.

Remarks:

Be careful with this. When you disable a key, it is no longer available and can also not be used in code. Disabling a key is like deleting it; the difference is that it is still in the list of keys, but disabled.

When working on Sql Server, this means that if that key had any SumIndexField(s) attached to it, the related SIFT tables will also be removed if you disable the key (and recreated when you enable the key).

Enabling a key, means that the index table has to be rebuild, and so this can cause performance issues and locking, especially when there are many records in the table.

This is not to be used as an alternative for not maintaining a key on Sql Server. The MaintainSqlIndex and MaintainSIFTIndex/SiftLevelsToMaintain properties are meant for that purpose.

Recently I had to debug some custom code I added in Dynamics NAV. Some object was throwing an error because of an overflow converting Text 60 to Text 30. Because in the code were the error occurred, many other objects were called, it was not so easy to see were the error originated from. But then I noticed a nice feature of the Dynamics NAV debugger that pointed me in the correct direction.

Let's make an example. Codeunit 365 is frequently used to format addresses when running reports. Let's say that when running a Sales Invoice something goes wrong when calling the FormatAddr function.

For example, someone has changed the table design of the Sales Invoice table and enlarged the 'Bill To Customer Name' from 50 to 100 characters. The FormatAddr function expects a parameter Name that can be maximum 90 characters in length. When printing an invoice for a customer with a name bigger than 90 characters this will result in an error.

Go to Tools and enable the debugger (Breakpoint on Triggers is not required for this example):

Then, run the report. You will notice that the debugger stops here (when the error is thrown):

The question now is, can we trace this back to the report and see via which objects and code the report called this function?

Yes, we can. In the debugger you have a special window for this:

The little yellow arrow shows you in what object you are debugging. BUT, here comes the great feature, you can double-click on the underlying objects, and then the debugger will jump to the code of that object.

The little Green arrow shows now where we are debugging, and you can directly see which code in what object was responsible for calling the FormatAddr function.

Even more usefull you can see the object number, name and the trigger/function…

And you can even go further back by double-clicking on the object(s) below this one!

How cool is that?

Since Dynamics NAV 4.0 SP1 a new database option, for Dynamics NAV on Sql Server, became available: Always Rowlock:

By default it is not enabled and by enabling it Dynamics NAV will send 'WITH ROWLOCK' hints towards Sql Server. Without it, we let SQL Server decide what kind of locks to use.

Rowlocks require memory to maintain, so some times, SQL Server will convert many rowlocks into one table lock when it thinks that it can use its memory better in other places. The ROWLOCK hint will prevent this.

If the ROWLOCK is not issued then SQL Server has the freedom to choose at what level it will lock: row, page or table.

• The up side of this is that it is a big reduction in lock maintenance and memory required for many rowlocks, which instead will be replaced with page locks è improved performance.
• The down-side is that page locking is not as fine-grained and therefore a user can be locking 'too much' data that can impact other users è reduced concurrency.

Forcing ROW Locking keeps the lock-granularity small; the probability of getting blocks is smaller. The disadvantage is that by forcing the small granularity, this could cause a high "pressure" on the master database: administering many Row-Locks is more "costly" than administering few e.g. Range-Locks etc...

If you have a high transaction volume, dealing with large result-sets, "Always Rowlock" could cause an overall decrease of performance if the master-db reacts too slowly due to the high number of lock-administrations.

Finally, "Always Rowlock" reduces blocking-conflicts (actually it is just disguising them) but could be at cost of the overall performance (which could be compensated by sufficient hardware resources).

So, if you activate "Always Rowlock", you may get fewer table locks, but SQL Server may need more memory. It's impossible to say in general, how it will affect each individual system. If you think you have quite a lot of memory, then the only way to see the effect is to turn it on, and see if the system works better. I'm afraid that I don't know of any better general rules, when to advice to turn "Always Rowlock" on or off.

Maybe you could share with us your own experiences with the Always Rowlock option?

On partnersource you can find a landing page dedicated to Microsoft Dynamics NAV and Microsoft SQL Server.

You can find all the knowledge and tools you need to learn more about running Microsoft Dynamics NAV on the Microsoft SQL 2005 platform..

It was launched long ago, but it is updated frequently.

Microsoft SQL Server 2008 Option for Microsoft Dynamics NAV 5.0 FACTSHEET
This month a factsheet about Microsoft SQL Server 2008 and Microsoft Dynamics NAV 5.0 has been released. You will need a partnersource login to view this factsheet.

The described features are related to:

• Efficient data management
• High availability
• Business analytics
• Security

A few weeks ago when I was delivering a Reporting Services training, I noticed that on some of students laptops, sometimes the Initialisation checkmark was grayed out in the Reporting Services Configuration Manager.

You can find it here:

This puzzled me, untill I found the reason.

If you are using the Standard edition of Reporting Services, the initialization pane is grayed out in Reporting Services Configuration Manager. You should still see the checkmark.

This is grayed out because scale out to a web farm is not available in this edition. If the Report Server Windows service is running, it is already initialized.

For more information, please review the information in this link for the full software requirements: http://msdn2.microsoft.com/en-us/library/ms143506.aspx

If you would like to install and Configure Reporting Services to Use a Non-Default Web Site, some usefull information can be found here: http://msdn2.microsoft.com/en-us/library/ms155921.aspx

Whenever a message (dialog box) is shown in Dynamics-NAV or an error occurs then you hear a beep sound. When giving a demo to a prospect or when working in Dynamics-NAV this can become very anoying. Especially when working with a Virtual PC this happens a lot.

A couple of weeks ago one of the students in a training I was delivering asked if it was possible to stop Dynamics-NAV from beeping. And yes, it is possible.

What you should know is that the beeping is in fact a service that can be stopped. There are 3 ways to achieve this.

1) Turn off the beep sound on a particular virtual machine:

• On the virtual machine, click Start, click Run, type regedit in the Open box, and then click OK.
• Locate, and then click the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Beep
• In the right pane, right-click Start, and then click Modify.
• In the Value data box, type 4 to turn off the beep sound, and then click OK.
• Quit Registry Editor, and then restart the virtual machine.

2) Turn off the Beep service:

• Type the following command at a command prompt, and then press ENTER:
  sc config beep start= disabled
• To turn the beep sound back on, type the sc config beep start= command together with one of the following startup types: boot, system, auto, demand

3) Stop the Beep service:

You can stop the Beep service by using the Net command. However, in this scenario, the service automatically restarts when you restart the virtual machine or when you restart the host computer.

• To stop the Beep service, type the following command at a command prompt, and then press ENTER: net stop beep
• To start the Beep service, type the following command, and then press ENTER: net start beep

A very interesting whitepaper has been released on Partnersource: Developing Solutions for Microsoft Dynamics NAV 6.0.

It describes in detail the changed in Dynamics-NAV 6.0:

• Architectural changes from a two- to a three-tier architecture, including a discussion of the new RoleTailored client and Microsoft Dynamics NAV Server with Web services support.
• Development improvements and benefits for Microsoft Dynamics-NAV 6.0, including the introduction of the new Page Designer, changes in Object Designer, and enhanced reporting using SQL Server Reporting Services.
• Functionality that needs to be redesigned from a previous version to work in Microsoft Dynamics-NAV 6.0.
• Step-by-step walkthroughs that demonstrate new development features, including creating and modifying pages and creating a report.
• Appendix with a list of elements that have changed in the three-tier architecture.