Is There a (Good) Solution for SQL Server HA @ Azure?

Comment: I do not see Windows Azure SQL Database as a feasible solution for a firm that expects its business to scale. The reason is simple: You can not use a component in your system that its replacement will require a long downtime (yes, we are talking about hours if you will have a significant database size). The only way to migrate from Windows Azure SQL Database is to export its data and import it on a regular instance, and it's not acceptable when you have a significant traffic.

High Availability
The requirement for high availability is common: you don't want downtime, as downtime mean less business and it hurts your business image. 

The Azure Catch
Azure SQL Server VM is just like having a SQL Server on a regular VM. 
VM maintenance includes two layers: 1) maintaining the VM (installing patches, hardening...) and 2) doing the same to the host underneath.  
A common large size private and public cloud operation usually include an auto fail over, so when a host is having maintenance or unfortunately fails, the system automatically migrate the running VMs to another host(s) w/o stopping them. You could find this behavior in VMWare VMotion and at Amazon EC2 that runs over XEN.
Well... this is not the case at Microsoft Azure. When  Microsoft updates its hosts, don't expect your instances to be available (and yes the downtime may take dozens of minutes and it is not controlled by you). This is an acceptable practice when dealing with web and application servers (place several instances behind a LB and use a queue mechanism to deal with it). However, it is not good one when you deal with databases it's can be a major issue.

The Solution: Have a Master-Master Configuration
As you may understood, a master-slave solution is not acceptable in this case, and therefore, you will need to avoid Log Shipping (although it can be used various other scenarios).
Therefore, we were left with two solutions:

Mirroring:
This was a solution for HA architectures.
However "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead."

http://technet.microsoft.com/en-us/library/ms189852.aspx

AlwaysOn Availability Groups
This solution is described by MS as the "enterprise-level alternative to database mirroring. Introduced in SQL Server 2012".
However, "The non-RFC-compliant DHCP service in Windows Azure can cause the creation of certain WSFC cluster configurations to fail, due to the cluster network name being assigned a duplicate IP address (the same IP address as one of the cluster nodes). This is an issue when you implement AlwaysOn Availability Groups, which depends on the WSFC feature."
http://technet.microsoft.com/en-us/library/hh510230.aspx

The Second Catch
According to our analysis it seems that both Mirroring (end of life) and AlwaysOn (severe bugs due to DHCP) are not recommended, so we actually left w/o good HA solution, and therefore with no good MS data store solution for the Azure environment.
We tried to get answers from Microsoft stuff, but we did not get good ones.

Bottom Line
When evaluating Azure as a cloud platform for your needs, you should consider your data solution and how it fits your needs. In this case you may need to consider some open source solutions such as MySQL, Cassandra and MongoDB on a Linux VM, instead of going the MS default stack.

Keep Performing,
Moshe Kaplan

Do You Need More Disk Space for the MySQL Server at Amazon Web Services (AWS/EC2)?

If you deal with large databases, you will probably need more than the provisioned storage of the default Amazon AMI (8GB).


UPDATE: Since in many cases you may need to swap between the current disk and a new larger one, I've added in green comments regarding this case.

The following 10 steps guide will help you expand your disk meet your business needs:

1. Create a new storage volume at the Amazon console (or using AWS API). Make sure it is big enough to accommodate storage, log files, bin files and future growth. Notice! verify the new drive is in the same availability zone as the target instance.
2. Write down the mount name you chosen. For example: /dev/sdf.
3. Mount the disk to your system according to Chris Dean post:
1. sudo mkfs.ext4 /dev/sdf where /dev/sdf is the mount name you chose.
4. Shutdown the MySQL daemon: sudo /etc/init.d/mysql stop
5. Unmount the current disk umount /dev/sdf
6. Move your current MySQL data directory to a temporary directory: sudo mv /var/lib/mysql  /var/lib/mysql.old or mount the previous disk to mysql.old by creating a new folder and changing the /etc/fstab as described bellow.
7. Recreate the MySQL data directory and provide the needed permissions:
1. sudo mkdir /var/lib/mysql
2. sudo chown mysql:mysql  /var/lib/mysql
8. Mount the new disk to the new directory:
1. Add another line to /etc/fstab (sudo vi /etc/fstab): /dev/sdf /var/lib/mysql ext4 defaults 1 2
2. Mount the disk: sudo mount /var/lib/mysql
3. Verify everything is Okay with df
9. Move the files from the temporary directory back to the new data directory sudo mv /var/lib/mysql.old/* /var/lib/mysql
10. Remove the old directory: sudo rmdir /var/lib/mysql.old and unmount the old disk umount /dev/sdf
11. Restart you MySQL: sudo /etc/init.d/mysql start

Note:
xfs today is the new kid in block w/ better performance w/ SSD disks and already stable.
Use the following commands if you choose it:
sudo apt-get install -y xfsprogs
sudo mkfs.xfs -f /dev/xvdf
vi /etc/fstab
/dev/xvdf /var/lib/mysql/ xfs defaults 0 2

Bottom Line

A combination of cloud knowledge, Linux sysadmin, MySQL expertise and careful work will help you go through the scale and growth challenges.

Keep Performing,

Moshe Kaplan

When Agile Met Cloud Computing

My mate Alon Nativ just referred me to JetBrains newly introduced TeamCity feature: integration of their Continues Integration (CI) platform with Amazon EC2. This new feature enables on demand instances during the CI process.

What is it good for?
CI tends to have non linear usage behavior: Minor use in the beginning of the project, and massive use in the end of the day and just before releasing the new version.
During these peak hours TeamCity can launch on demand instances, running the build and the tests and shut down the instances when it ends.

 
Bottom Line
It is a fine example of how development teams can keep their infrastructure costs as low as possible, while maintaining maximum flexibility.

Keep Performing,
Moshe Kaplan

Load Balacing Support in Dynamic Environments

Hi,

The Mission
These days we face a challenging task: designing a very large system of scalable instances. Each of these instances may be in a different geographic location, and many of them are on demand instances that are  being started and shutdown instantly.
Another requirement in this system that a given client will be direct to a defined instance, due to system restriction (a round robin is not an option is this case).

One Step Further
Since the number of IP addresses in the internet is limited, we would like to use as few as possible Public IP addresses. This can be done using a load balancer or a proxy.
In the current state we would like to avoid using hardware load balancers in order to keep initial fixed costs minimal, but we may consider to use them in the future.

Is Amazon Cloud Load Balancer Service (AWS) is an Option?

AWS EC2 instances is a feasible option for on demand instances hosing. However, AWS charges $0.025 per a single load balancing rule per hour (+traffic). Therefore, it can be used, but for a large number of rules (>7) or high traffic, better solutions can be found in the market.

So What Can Be Done?
We left with software load balancers. The major ones Apache mod_proxy and HAProxy. Supporting large number of instances behind the load balancer can be done in one of the following two options:

1. Pre register a large number of DNS addresses (sub domain) and associate them with the load balacner IP. The load balancer will simply redirect the request to the defined instance, based on the a simple rule in the load balancer. For example: http://instN.foofarm.com  Pros: simple. Cons: not fully dynamic, requires additional DNS registration once in a while to keep up with the application growth.
   
2. Performing ProxyPass in the Load balancer: Every request will include in its path an instance identification for example: http://foofarm.com/instN/. This method does not require mass DNS declarations, but it requires specific definitions in the load balancers that may be more CPU consuming. In Apache the definition is pretty trivial, however, this product is less scalable from HAProxy. In HAProxy the task can be done as well based on a two phases: switching to the server and rewrite the URI:
   In order to switch to the server, you have to use ACLs to match the path,
   then a use_backend directive to select a server farm ("backend"). Your
   farm may very well support only one server if you want.
   
   Then in this "backend", you can use a rewrite rule ("reqrep") to replace
   the request line.
   
   This would basically look like this :
   
   frontend xxx
          acl path_mirror_foo path_beg /mirror/foo/
          use_backend bk_66 if path_mirror_foo
   
   backend bk_66
          reqrep ^([^: ]*\ )/instN/\(.*\)  \1/\2
          balance roundrobin
          server srv66 192.88.77.66:80

However, Willy Tarreau, HAProxy author who kindly provided this hint for me, recommends that you avoid the second part (rewriting) because :

 1) it requires good regex skills which sometimes makes the configs hard
    to maintain for other people

 2) rewriting URIs in applications is the worst ever thing to do, because
    they never know where they are mapped, and regularly emit wrong links
    and wrong Location headers during redirections.

Willy Tarreau also advices that the best thing to do clearly is to correctly configure your application to be able to respond with the real, original URI. Remapping it can be used
as a transitional setup in order to ease a graceful switchover, though. Bottom line: Pros: No DNS configuration and fully scalable solution, with no dependence on DNS replication. Cons: CPU Consuming and error prone declarations.

So, What to Choose?
The answer is based on your needs, and your believe in your people regex capabilities. We made our choice.

Keep Performing,
Moshe Kaplan. Performance Expert.

Cloud Computing and Windows 2008

Hi,

It was a long time since the last post... more working, less talking....

Pain
This time we'll talk about a real pain in the Microsoft camp: why Windows 2008 is rarely supported by cloud providers such as Amazon AWS/EC2, AppNexus and Rackspace Cloud?

One of the few exceptions is Microsoft Azure which is still in CTP and is using a modified Windows 2008 version.

New features, New challenges...
Windows 2008 which has a lot of new features such as safer service shutdown and kernel transaction manager also includes a new activation method (which is probably familiar to those of you who use Vista).

According to my conversation with a leading cloud provider, this new activation method prevents them from generating images with predefined keys. Therefore, they cannot provide on demand Windows 2008 instances. The cloud providers are currently in advanced negotiations with Microsoft and provide only Windows 2003 instances.

The Question
Are there hidden causes behind this Microsoft restriction? or will a solution will be found before Azure RTM? Only time will tell...

Keep Performing,
Moshe Kaplan. RockeTier - The Performance and Cloud Experts.

IGT Hosting Amazon AWS Hands-on workshop

Hi,

Great News,
After all the Amazon meetups, the IGT is going to host Amazon AWS Hands-on workshop,
It's a great opportunity to meet Simone Brunozzi, Amazon Web Services Evangelist - Europe, and have real life hands on experience as well as asking questions.

Date: Mar 3, 2009 10:00 13:00

Location: IGT Office, Maskit 4, 5th Floor, Hertzelia

Organizer: IGT

The preliminary agenda:
10:00 - 10:30 Intro to Amazon AWS
10:30 - 12:00 Hands-on AWS Workshop
12.00 - 13:00 AWS Q&A

Best,
Moshe Kaplan. RockeTier. The Performance Experts.

Secure your Amazon EC2 Database

Hi,

An important issue that must be addressed in every system, and for sure if it is an internet based, is security. When we build high scalable systems, we face this issue time after time.

Therefore, when you install a new system in Amazon EC2, with a MySQL in it, you should consider how to secure and restrict the access to the system and the database.

Amazon provides set of solutions in order to support these needs:
1. Firewall: Amazon EC2 provides a firewall solution. This inbound firewall is configured in a default deny mode and you must explicitly open any ports to allow inbound traffic. The traffic may be restricted by protocol, by service port, as well as by source IP address (individual IP or CIDR block). More can be found here.
2. Security Groups: Security group is the easiest way to aggregate several EC2 servers based on rule, access needs and functionality: Database, application, web, etc (if you are familiar with VLAN segments and FW legs, it's very similar indeed).
Every server is associate at start up with a security group, so access to this server is restricted based on the group definitions. This can be done by defining FW rules between Amazon EC2 security groups and between a security group and the world (for example HTTP may be enabled from the world to the web servers security groups, while the database will enable access in 3306 to the application servers security group and your company static IP)
3. Anti DDoS, IP spoofing and other issues are addressed here

So how should I start?
1. Define you servers roles and the security groups. Tip: usually it should be the same as your AMI
2. Define the FW rules between the groups and the logical rules between each other, the world, your secured office connection (static IP is recommanded) and every other relevant 3rd party.
3. Define the security groups in Amazon (see ec2-...-group RTFM)
4. Define you FW rules. see more here to learn the exact syntax and in the ec2-authorize RTFM

Best,
Moshe. RockeTier, the performance experts