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

Azure Production Issues, Not a nice thing to share with your friends...

Last night I got a call from a close friend.
"Our production SQL Server VM at Azure is down, and we cannot provide service to clients".
A short analysis issued that we are in a deep trouble:

1. The server status was: Stopped (Failed to start).
2. Repeated tries to start the server resulted in Starting... and back to the fail message.
3. Changing instance configuration as proposed in various forums and blogs was resulted in the same fail message.
4. Microsoft claims that everything is Okay with its data centers.
5. Checking the Azure storage container found out that the specific VHD disk was not updated since the server failure.

Since getting back to cold backup would cause losing too much data, we had to restore somehow the failed server.

The chances were against us. Yet, lucky us, we could do that by restoring the database files from the VHD (VM disk) file that was available at Azure storage. 

How to Recover from the Stopped (Failed to start) VM Machine?

1. Start a new instance in the same availability set (that way you can continue using the same DNS name, instead of also deploying a new version of the app servers).
2. Attach a new large disk to the instance (the failed server disk was 127GB, make sure the allocated disk is larger).
3. Start the new machine.
4. Format the disk as a new drive.
5. Get to your Azure account and download the VHD file from Azure storage. Make sure you download it to the right disk. We found out that the download process takes several hours even when the blob storage is in the same data center as the VM.
6. Mount the VHD file you downloaded as a new disk.
7. Extract the database and log files from the new disk and attach them to the new SQL Server instance.

Other recommendations:

1. Keep your backup files updated and in a safe place.
2. Keep your database data and log files out of the system disk, so you could easily attach them to other servers.

Bottom Line

When the going gets tough, the tough get going

Keep Performing,

Moshe Kaplan

Tracing SQL Server Performance and Stability Issues

The following two undocumented stored procedures will do a miracle to your efforts to trace issues in SQL Server:

1. EXEC sp_readerrorlog;
2. DBCC LOG('DATABASE_NAME', 3);

The first can be used to find failures, backup procedures and other goodies in the database log. The second can be used to trace heavy UPDATE, DELETE and INSERT transactions in the transaction log of a specific database.

Keep Performing,

Moshe Kaplan

Sharding is Now COTS

I wrote here a lot in the past regarding SQL Server and MySQL sharding. I wanted to update you that ScaleBase, which is led by the industry veterans Liran Zelkha and Doron Levari announced that ScaleBase 1.0 is now available. 
Scalebase provides a fine solution to MySQL sharding by implementing an SQL load balancer in front of your servers. 
This product is available as a service and as a downloadable product. ScaleBase 1.0 supports:

1. Read/Write splitting
2. Transparent Sharding
3. High Availability

If you need a proper solution for a large size MySQL database, yet you don't have the time or mind to implement sharding by yourselves, you should take a look at their product.


Keep Performing,
Moshe Kaplan 

Sharding Again

Click on the post title to read the full post and the comments.


It was a long time since we last discussed Sharding. 
Yesterday the Twitter sharding case study was presented at the Metacafe Knowledge sharing meetup by Gidi Meir Morris. Therefore, I think it is a good reason to refresh our minds.


Hey! Where Are All These Whales?
Twitter turned its web common architecture (that caused it many problems) based on Ruby on Rails web layer and MySQL into a 3 layer system that includes:

1. Web application server called Flapps.
2. Sharding middleware called Gizzard. This layer takes care of database requests and sends them to the target shard. It takes care of replication as well.
3. Old good (not so scalable) MySQL in a horizontal sharding mode.

But hey, it is better taking a look in this great Prezi presentation (use the More>Full Screen to better watch the presentation):

A bird's nest:A primer on FlockDB & Gizzard on Prezi

Bottom Line
Highly scalable databases are becoming commodity. In the Era of Cassandra and Gizzard, Google is starting to lose its competitive edge and high priced Oracle, IBM and Microsoft database are no longer a must for a startup. Will it affect these companies' bottom lines? Only time will tell.


Keep Performing,
Moshe Kaplan

Microsoft is Catching Up with the Internet Industry

Click on the title to view the full post, comments and add your own comment
For a long time I blame Microsoft for not really understanding the Internet and cloud Industry basic need: very large systems that can handle billions of daily transactions with a very low revenue per transaction. Now, after admitting its failure in the mobile industry (see Kin's death earlier this week), Microsoft finally reveals AppFabric after a very long time (I would like to thank my colleague Alon Biran for notifying me regarding it).

What is AppFabric?
The roots of AppFabric are located in the "Velocity" project that was developed in the SQL Server group (a year and a half ago I wrote a Knol about). This project was aimed to provide a key-value store (super hash) that will be used for two main objectives:

1. Instant in memory storage data store (database replacement).
2. Shared web session repository that will enable instant failure between web servers (the only Microsoft in house solution till now was using SQL Server as a shared repository)

After being in various CTP and beta versions for almost two years, AppFabric is finally out as part of the Windows platform rather than being a part of the SQL Server product. You may take a look at my colleague David Chappell's AppFabric white paper for complete set of features and scenarios.

Who are the Current Players?
The major players in the market these days are open source (Every each of the products has its own unique features, please feel free to comment on this post regarding it):

1. Memcached: the #1 product in the market. Its roots are in 2003. It is an open source and Linux based product. Yet, Memcached has .Net client API as well as windows porting of the product itself.
2. SharedCache: a native C# open source product that is a Memcached like.
3. StateServer: commercial native windows product by ScaleOut Software.
4. NCache Express: another commercial native windows product by Alachisoft.

Since AppFabric is provided for "free" as part of the Windows platform, the commercial native .Net products may be hurt from the Microsoft move. However, they may gain more users due to better publicity for this kind of solutions in the Microsoft development community,

Why Did It Take So Long?
It seems that Microsoft had a major cannibalization issue: If you provide a good key-value store then you may reduce the databases size and usage intensity. Thus, the number of high cost database licenses will be significant lower and the bottom line will be smaller. Oracle has the same problem with Coherence that is now part of the Fusion platform.
Taking this product into RTM is a true Microsoft understanding that if it really want to take a significant role in the Internet and cloud industry, it will have to sacrifice more than just few cents in its financial reports bottom line.

Bottom Line
These are very good news that Microsoft is finally provide a solution for a basic need of the Internet and cloud industry. However, while Microsoft was catching up with the Industry 2003 news, new requirements were raised including MapReduce, BigTable and other NoSQL advanced features. How much time will it take this time? Probably only Steve Ballmer has the answer.

Keep Performing,
Moshe Kaplan

SQL Server NOLOCK: Should I use or should I not?

Well, the simple answer is NO!

What is NOLOCK?
NOLOCKS enables you to make a SELECT statement while avoiding current locks on the tables by other statements such as DELETE and UPDATE.

Why should you use NOLOCK?
Well, the answer is simple, you have locks in the database, users in your website receive exceptions and errors instead of answers, and your boss is getting nervous. The simple way is just place an extra WITH(NOLOCK) and things seems to be OK:

SELECT field_name FROM table_name WITH(NOLOCK)

Why should you avoid NOLOCK?
If your database suffers from locks, avoiding these performance issues now, will result in larger problems in the future. Your database is a key feature in your architecture, and your should take care of him and not avoid the problems.
Moreover, using NOLOCK does not gurrentee that your users will receive updated and current data which may sensitive when financial or sensitive data is getting into place.

Keep Performing,
Moshe Kaplan.