Azure Deployment Experience - Series 2 (NTFS vs ReFS) - Beware of failures in ReFS

After provisioning the Disks for VM, we normally tend to 'Allocate' space and create Logical Drives inside the VM.

ReFS seem to be a good bet in terms of being the latest and having the feature of auto-correcting in case of any failures. NTFS has been there for quite some years and ReFS is touted as next best technology for file storage & access.

It so happened that, one of the ReFS drives in one of our VMs got corrupted somehow and we could not recover the contents. Ten days effort of deploying an application for a demo, went for a complete mess. In NTFS, there is a mechanism to run CHKDSK and possibly recover the bad sectors. But in ReFS, running it is of no impact, as the system internally tries to do it by itself.

Finally, we had to recreate everything and from this incident onward, I started choosing NTFS for all drives that I create.

There is a way we could have possibly recovered the data had we taken a backup of either the content or the drive itself to a Storage Account. But being (a) new to Azure, (b) not knowing internals of how technology works, (c) when you are the sole person doing everything, (d) demo set up in a short time and (e) especially when we do these in small / start-up work places, these kind of processes may not be practically executed. 

So, if you choose ReFS, go ahead, but make sure you backup the contents as per the changes made. Else, choose NTFS. In either case, taking backup is a good practice. NTFS has ChkDsk way of possible recovery while ReFS does not seem to be proved (as in our case).

Azure Deployment Experience - Series 1 (VMs with MS-SQL)

There are a lot of videos and tutorials on Azure VMs with SQL and its deployment. I am not going to get into any of the existing content. 

The content is more about the high-level practical approaches if we were to go for Azure VMs which has to support Microsoft SQL. This is based on my current experience in deploying our solutions in Azure.

• First, if there is an Enterprise Agreement, there shall be a Pricing Sheet that will help you to know how much a VM would cost; remember that the cost will vary according to the configuration of VM along with the Edition of SQL.
• If you want a 'Test' or 'Development' server, create a minimal VM (say 2 core, 3.5 GB RAM or 4 Core,7GB RAM) and download SQL Express. SQL Express 2012 can support up to 10 GB Database size and you can have multiple databases and Express 2012 can support up to 4 Cores. 
• If not Production server, but require a server for replicating the Production, go for SQL Web Edition which costs much less compared to Standard / Enterprise.
• For Production, SQL Standard is recommended and a better choice as it will be required to configure Backups, Maintenance Jobs.
• Go for Enterprise, if we really require Always-On, Inline Memory Processing, Partition kind of features. The cost of Enterprise is nearly 4 times that of Standard.

I have not experimented with Azure SQL DB, as its a shared chunk from a bigger SQL but its not a dedicated SQL Instance except for the assured DTUs.

Importantly, create the VMs using the new portal and as part of ARM, so that all latest features of new portal are available to work with. It is not possible to migrate a VM from Classic to ARM at this point of time, without a down time.

MS SQL - Table Variables and #Temp Tables - Key Performance Factors

Having worked in ERP and Healthcare Information Systems, the bulk of the Business Logic is observed to be predominantly written in the Stored Procedures. This puts a heavy load in SQL Server (my work has been only with MS-SQL and anyway, it doesn't matter in this context). 

The scenario becomes complicated in Web Based programming where results are expected like Windows application. The Quick & Dirty mechanism employed by Programmers (across seniority) and even Designers to fetch mixed data contents is to use Temp tables.

Listing below the key performance factors to take into account, while designing the logic involving Temp Tables or Table variables. 

1. Temp tables must NOT be used for large records. A record size of 10 to 500 or even 1000 can be a decent number. More than the count, it also depends on the number of columns, data type as well.
2. Not ideal for concurrent scenario or real-time sites. Temp tables have to be created for each connection / session. This is going to be heavy additional load for SQL
3. Large Temp tables / Table variables can perform poorly in spite of Index availability. Temp tables allows to define more than one Index but more index does not help the performance.
4. Though the Temp tables / Table variables are internally cleaned, it is very unclear on when the memory gets actually released and used for other purpose. There is no such thing as GC that runs a clean-up job. SQL does it as part of its engine and its an additional load.
5. TempDB size growth is also a matter of concern. Its advisable to set "Max Degree of Parallelism" for heavy usage Enterprise scale application and create as many Temp DB Secondary files for the number of Max Degree.
6. Temp DB be preferably kept in SSD Drive or a separate drive, so that the I/O is better 
7. Allocate a large chunk in the auto-growth instead of smaller percentage or MB size, if there is going to be a load of Temp DB
8. Remember that, not only Temp Tables & Table variables are going to use Temp DB, but also the other regular query operations like Sorting, Hashing and a poorly written query is going to make more use of the TempDB.
9. If multiple databases are maintained in an server instance, and if they have logic written using Temp tables / Table Variables, remember that there is only TempDB to serve.
10. So, when they can be useful? - typically MIS reports or dynamic reports where data is aggregated from many tables and values are manipulated before sending out.
11. So, what to do for other cases? - one approach is to create Physical tables with a column to identity the SessionID, so that each Session's data can be maintained and then later deleted. Index on Physical tables will also help in query processing. Need to make sure, not to abuse this Table by populating 10000 records for each session and then trying to Delete. This will backfire.

SQL 2014 and its successor may be bringing in features like Inline Memory Processing, but then we have to remember that, most of the customers would prefer 'Standard' edition and these features are most likely going to be made available only in Enterprise Edition.