Virtualization with SQL server. Think before you start.

Sometimes I got the question if it is useful to virtualize your SQL Servers. This is a valid question. There are a lot of reasons to virtualize your server park. So why not your SQL Servers. I have some tips when you plan to start virtualization with SQL Server:

• Check if your SQL Server version is supported by Microsoft on the Virtualization platform you are going to use. Read this official web log for Microsoft PSS SQL. This blog will explain the support policies from Microsoft regarding SQL Server running in a hardware virtualization environment.
• Do you have experience with virtualization with other applications? If not start with some 'non business critical' applications.
• Read the best practices of SQL 2008 on Hyper-V.
• With the use of virtualization, a new dimension of hardware capacity is introduced: The configuration of the resources of the Hyper-V machine. I will explain with one example:
  
  Assume 4 servers are virtualized on one physical box with 2 processors and Hyper-V installed. Within Hyper-V you have configured that each guest operating system is allowed to use a maximum of 25% of the CPU power of the Hyper-V machine. The first guest operating system has a CPU utilization of 100% and the other 3 guest operating systems have a CPU utilization of 0%. The physical machine will have a CPU utilization of 25%. In the first guest operating system you can make the conclusion that you have a CPU bottleneck. However this is maybe caused by a wrong Hyper-V configuration. Does the other 3 guest operating system really need 25% of the CPU power of the Hyper-V machine? 
  

  Beside the regular performance counters of your guest operating system, you need to monitor Hyper-V performance to get a true measure of resource usage. 

• Convince yourself that you are able to find the performance bottleneck in a non virtualized SQL Server environment. If not, improve your skills to find the performance bottleneck before implementing virtualization.
• Realize that virtualization will make your server configuration more complex.

Exact DashBoard Actie: Simple and Fast

As you can see in the title of my blog, I like the principle Keep it Simple and Fast. A product with a lot of features will getting very complex. A small amount of features will result in a simple application. This week my company introduced a nice solution: The Exact Dashboard. It is a straight forward installation which will create a dashboard. This dashboard contains nice reports to give you a better insight in the financial situation of your company.

Have a look to it, to experience my principle: Keep It Simple and Fast.

Triggers in SaaS accounting solution Exact Online

In Conor Cunningham's blog on SQL Server he asks companies who has experiences with Stored Procedures and Triggers. Therefor I want to share some of our experiences with the way we have implemented triggers. My company Exact Software has builded Exact Online which is a Software as a Service accounting solution. Currently this solution, with more than 6000 subscriptions, is available in the Netherlands and Belgium. Exact Online is builded on the latest .NET Framework. All data is stored in multiple SQL 2005 databases. We make use of triggers to update the balance table in which we store the Balance and Profit & Loss sheet. This balance table is based on the financial transactions created by the users.

Why did we choose to use triggers to update the balance table?
Advantages:

1. Consistency between transaction table and balance table.
2. High performance due to the efficiency of SQL Server triggers
3. Apart from the triggers themselves, no additional code is needed to keep the balance table up to date.

Disadvantages:

1. Chance of locking errors when 2 transactions update different transaction records, which related to the same balance record.

Implementation issues:

1. Define 3 triggers: One for insert, one for update and one for delete. One trigger for update, insert and delete resulted in our solution to locking errors.
2. Start every trigger with the statement IF @@ROWCOUNT = 0 RETURN. This is done for performance reasons, if no inserts, updates or deletes took place at all, do not do anything.
3. To limit locking situations:
   
   1) Keep database transactions as short as possible.
   2) Do not delete the balance record if all underlying transactions are deleted. In most situations this balance record will be used when new transactions are created.
4. In the past we have used indexed views, however because of performance reasons we moved to the trigger solution.

We are using the triggers, in the Exact Online solution, on the balance table for more than 3 years. Till today  we are very enthousiastics about the performance and consistency of this implementation.  According the statistics of SQL 2005 the triggers are fired 7 times per second on every database.

Compress content is a nice example of an application builded from a technical perspective instead of a user perspective.

This weekend I encountered a nice example of my previous blog: The difference between solutions from a technical perspective and from a user perspective.

I started the laptop of my father. It took 20 minutes to start. He told me that this was happen since a while. He had no idea what had happend. Every morning he started his laptop before he took a shower. After the shower his laptop was 'ready' to logon. I thought he had a virus or something like that. After a while, looking why it tooks so long to start, I found the root cause.

Compressed files in the Windows and Program Files directory.

All files which are needed to load the operating system and to start his applications where compressed. After decompressing these files, the PC started within 3 minutes. Still long but much better as it was. I can't wait for Windows 7 to start really fast.

How did it happen?
In the past the C: drive was almost out if diskspace, however his D drive was empty. (50 Gb available). The operating system had suggested to compress data. From a technical perspective this is correct. You need diskspace. However, a suggestion to move data from the C: drive to the D: drive would be much better. If data should be compressed on the C: drive the application should only compress user data instead of operating system files or program files.

Conclusion: Compress data can be useful but should not be used on all files on the disk. With a little bit more logic in the application a user friendly compress application can be made which will add value for a user. You can't expect from a user that he knows what the impact is of compressing operation system of program files.