Windows Azure Blog

Hello guys….

When you think of hosting SQL server to windows Azure (cloud solution); following needs to be taken care about configuring the storage

Now coming back to your question about storage, Azure Storage includes three services: Blob storage, Table storage, and Queue storage. These services are included in every storage account. Storage costs are based on four factors: storage capacity, replication scheme, storage transactions, and data egress. Storage capacity refers to how much of your storage account allotment you are using to store data. The cost of simply storing your data is determined by how much data you are storing, and how it is replicated. Transactions refer to all read and write operations to Azure Storage. Data egress refers to data transferred out of an Azure region. When the data in your storage account is accessed by an application that is not running in the same region, whether that application is a cloud service or some other type of application, then you are charged for data egress.

Data in your storage account is replicated to ensure durability that is also highly available, meeting the Azure Storage SLA  even in the face of transient hardware failures to maintain the SLA promised. Azure Storage is deployed in 15 regions around the world and also includes support for replicating data between regions. If you choose Locally Redundant Storage (LRS) , we maintains three copies of your data. The storage is replicated three times within a single facility in a single region and protects your data from normal hardware failures, but not from the failure of a single facility.

Geo Redundant Storage(GRS) is enabled for your storage account by default when you create it and it maintains six copies of your data. Your data is replicated three times within the primary region, and is also replicated three times in a secondary region hundreds of miles away from the primary region, providing the highest level of durability. If the primary region fails your Azure Storage will failover to the secondary storage.

So as expected Locally Redundant Storage will be cheaper in terms of cost, Some reasons why one may choose LRS over GRS :-

• Applications that store data which can be easily reconstructed may choose to not geo replicate data not only for cost but also because they get higher throughput for the storage account. LRS accounts get 10 Gibps ingress and 15 Gibps egress as compared to 5 Gibps ingress and 10 Gibps egress for a GRS account.
• Some customers want their data only replicated within a single region due to application’s data governance requirements and they would go with LRS.
• Some applications may have built their own geo replication strategy and not require geo replication to be managed by Windows Azure Storage service.

While installing the following needs to be taken care

• SQL installation on local redundant data drive
• Always select the VM with SQL instance from Azure
• Download the GP directly from the customer/partner portal to the Azure VM.
• VM instance with minimum A3 model should be there (for 10 user access)

When we created a SQL instance on Windows Azure, Latency at its peek or in other word “SQL server damn slow”…

No partners can give you a practical answer to why this issue occurs; only a technical team from Microsoft with lot of troubleshoot; which i want to share with you guys out there

During the trouble shoot,

While restoring a database of 6GB size (VM server was on Geo Redundant data disk at the time of test); following process took more time

13251770              PREEMPTIVE_OS_WRITEFILEGATHER

13251768              ASYNC_IO_COMPLETION

So the obvious question arises what are these Wait Types:-

1. PREEMPTIVE_OS_WRITEFILEGATHER :- This indicates the Auto Growth event , but in our case this wait occurs as we require allocation of new space.

2. ASYNC_IO_COMPLETION:- This wait type is used to indicate a worker is waiting on an asynchronous I/O operation to complete not associated with database pages. Few examples of where this wait type is used is to create files associated with a CREATE DATABASE and for “zeroing” out a transaction log file during log creation or growth.

When we perform a RESTORE of a DATABASE , the following phases are included:-

• Firstly we copy data, logs and index from backup to the database files
• Then we take logged transactions and apply to data for recovery point roll forward followed by we take the uncommitted transaction and rollback them to make the database in a consistent state.

Now during restore we will attempt to recreate the full size of the LDF file and it has to zero out the same. And this is called the instant file initialization process of the data\log files that fills the files with zeroes. This is a OS security feature and is designed to avoid revealing information from previously deleted files. The PREEMPTIVE_OS_WRITEFILEGATHER wait comes from here. Please read more here.

WHAT WE TRIED was

Grant the SQL Server Service the Privilege “Performance Volume Maintenance (SE_MANAGE_VOLUME_NAME)” Task through secpol.msc, SQL Server will skip the zero-initialization of the data files (RESTART SQL SERVER IS A MUST).

What happens under the hood is SQL Server with this permission can call the Win32 API “SetFileValidData(). As the msdn documentation says it

The  SetFileValidData  function allows you to avoid filling data with zeros when writing nonsequentially to a file. The function makes the data in the file valid without writing to the file. As a result, although some performance gain may be realized, existing data on disk from previously existing files can inadvertently become available to unintended readers.”

So under the Server security policy, we need to add the users/group who does the SQL script operation on the mentioned arrows below

Once we done the above settings and change the GRS to LRS; SQL performance is getting better.

Will update you as and when any information that may help the community; when it comes to Azure SQL practice.

Enjoy!!!