Cloudns.net - October graphs and statistics

October has been really stable and there were just few outages, really little time and always just one of 7 servers. Therefore no service have been affected in any way. The only problem was PNS2 which had 4+ hours of outage in one period. That could be a bit problematic for those who changed their domain settings in same time as it would synchronize all servers except the PNS2. I like Cloundns.net improved stability and service a lot.

NS1

NS2

NS3

NS4

PNS1

PNS2

PNS3

Multiple values in one column (aka GROUP_CONCAT for T-SQL)

There are times when you need create SELECT which includes column with multiple values from different table. Apparently there are functions for that for other databases like MySQL or Oracle. MS SQL does not have similar feature so we have to create one ourselves.

There are various way to do this but first of all SQL tables. I have own structure already in place with some thousands of records (some might say not enough, but we will see clear speed difference anyway):

Table #1 - page
id int
id_hash nvarchar(40)
title nvarchar(100)


Typical data look like:
1    004AAEAE62F8B...   Title XYZ
2    00EAF730FC50B...   Title 123


Table #2 - page_modules
id int
id_parent nvarchar(40)
module_type nvarchar(20)
module_id nvarchar(40)


Typical data look like:
1    004AAEAE62F8B...   GALLERY      01D2255A09E....
2    004AAEAE62F8B...   ARTICLE      045A6F30D05....
3    00EAF730FC50B...   ARTICLE      068FC352B6B....


And now what we want to have:
1    004AAEAE62F8B...   Title XYZ   GALLERY|ARTICLE
2    00EAF730FC50B...   Title 123   ARTICLE      


This is just example what I want to have in my SELECT results. Original table #1 has over 15 fields and table #2 is pretty much same. Table #1 has around 1000 records, table #2 has around 3 times more because of modules linked to pages.

I have tried 3 different method how to get result I need. 


Method #1: Creating T-SQL function with COALESCE

CREATE FUNCTION [dbo].[GetModules]
(
    @parent nvarchar(40)
)
RETURNS varchar(max)
AS
BEGIN
    declare @output varchar(max)
    select @output = COALESCE(@output + ' ', '') + module_type
    from vwPagesModules
    where parent = @parent


    return @output
END


and test SELECT:

SELECT *, dbo.GetModules(id_hash) FROM vwPages AS p

                                10 runs average
Client processing time          98.2000
Total execution time            107.3000
Wait time on server replies 9.1000

Method #2: Creating SELECT within SELECT

select 
    *,
    modules = (
        select module_type + ' ' from vwPagesModules pm
        where pm.parent = p.id_hash
        for xml path('')
    )
from vwPages p
                                10 runs average
Client processing time          31.9000
Total execution time            46.2000
Wait time on server replies 14.3000

Method #3: Creating SELECT with CROSS APPLY


SELECT *, LEFT(module_type , LEN(module_type )) AS module_type
FROM vwPages AS p
CROSS APPLY
(
    SELECT module_type + ' '
    FROM vwPagesModules m
    WHERE m.parent = p.id_hash
    FOR XML PATH('')
) pre_trimmed (module_type)

                                10 runs average
Client processing time          32.3000
Total execution time            49.0000
Wait time on server replies 16.7000

These tests are by no means scientific tests, I would need more data and probably optimize tables a bit but I hope that it can quickly show you what methods can be used for this functionality and how it behaves. Personally I like method #1 because you dont have to write complicated Selects but it's the slowest of pack and I believe having 100 000 records the difference would be even bigger. Method #2 and #3 are pretty much same speed.

I am planning to repeat these tests on much larger database and also implement some WHERE filtering to see how these methods cope.

Finally, all tests have been done directly on MS SQL server machine locally, using MS SQL Server Management Studio.

Please leave comments with your opinions, ideas or just to say thanks.

New ElasticHosts beta control dashboard

ElasticHosts have released beta version of their new control dashboard. I have to say it is really good and many things I did not like before have improved.

This is quick comparison how the old and new dashboard looks.

You can immediately see that the new dashboard is much better optimized, virtual drives are put next to the actual machine and the space is used more effectively.

What the new beta dashboard brings is much better work with AJAX. You can change you VNC password on the fly, without need to switch off machine and then switch it on. Just click on VNC password box and put the password to active VNC or leave it empty to disable VNC service.

The same AJAX functionality work with static IP addresses. Whilst in old dashboard you had to email EH to setup reverse DNS records in you you can do it on your own:

Finally, server configuration screen is now a lot better, it is clean and easy to understand:

as compared to: 

Overall, I think people at ElasticHosts have done great job and I am glad to see EH to try to improve all the time. 

Finally, don't forget you can try EH free for 5 days. And if you decide to use their services long term and you found this blog to be useful you can mention my name to them. Maybe they will give me some free credit to do more testings. 

Cloudns.net service availability update

It has been long time since last Cloudns.net service availability update. So here you go, these are Icinga statistics for last 31 days for all Cloudns.net DNS servers:

DNS-cloudns-ns1	100.000% (100.000%)	0.000% (0.000%)	0.000% (0.000%)	0.000%
DNS-cloudns-ns2	96.517% (96.517%)	3.483% (3.483%)	0.000% (0.000%)	0.000%
DNS-cloudns-ns3	99.927% (99.927%)	0.073% (0.073%)	0.000% (0.000%)	0.000%
DNS-cloudns-ns4	100.000% (100.000%)	0.000% (0.000%)	0.000% (0.000%)	0.000%
DNS-cloudns-pns1	99.952% (99.952%)	0.048% (0.048%)	0.000% (0.000%)	0.000%
DNS-cloudns-pns2	100.000% (100.000%)	0.000% (0.000%)	0.000% (0.000%)	0.000%
DNS-cloudns-pns3	100.000% (100.000%)	0.000% (0.000%)	0.000% (0.000%)	0.000%

I hope that helps you with the decision whether to use or not Cloudns.net and whether to go with free servers or pay small additional fee for premium servers.

I have to say, I think they improved their service massively. We shall see how it goes in future.

Last 7 days of Cloudns service, NS1-4 and PNS1 + PNS2

Last 7 days of Cloudns service, NS1-4 and PNS1 + PNS2. No need to comment:

PNS1

PNS2

NS1

NS2

NS3

NS4

New control panel for ElasticHosts

I have been contacted by Anna from ElasticHosts and I have been given access to new (still beta) control panel for ElasticHosts accounts.

I have to say it looks much better and it is much easier to navigate through the virtual machines and its mounted drives. There might be some issues but I cant wait for this version to go live. It will make our life much easier.

Good job ElasticHosts.

ElasticHosts - weekly update and first SLA refund

First of all I'd like to show you last weeks EH availability graphs:

EH VM1

EH VM2

As you can see last 7 days it has been 100% working and functional. That's exactly the reason why I have chosen EH as our IaaS provider.

There are couple of things:

1. 22 minutes downtime 2 weeks ago have been refunded under SLA (We got around 6 pounds of credit for 22 minutes). They had problem with faulty switch and problems were 4x 5 minute periods. 
2. Be carefull when you create new VM, problem with their current control panel (which I honestly dont like, it might behave pretty but it is not very usable) is that it will allow you to create machine even if you dont have enough free resources. I made mistake when creating 3rd machine (debian) which I wanted to have only 500mhz cpu and 256 ram. I forgot to change sliders and kept default 2000mhz and 1024 ram and it  created this machine although I did not have enough free resources on my account.
   
   Immediately I got email that I need to increase my credit or in few hours all machine would be switched off. This is not good behaviour, by mistake I created unwanted machine and it immediately charged me. If I did not have SLA credit from EH on our account I would probably disabled all our machines by mistake. Not good.

That's all for now, if you want to have a good try free trial here.