Azure Deployment Experience - Series 4 - Getting SQL Stats for Azure SQL DB calculation

At my work-place, hosting our web-Application and Database in a VM (with Windows or Windows + SQL) has been the usual practice. Though we want to move to Azure SQL DB, there are quite a few factors which makes us step back, for the simple fear of "which DTU we actually need". 

After some searching, I came across this blog (Choosing Right Tier for Azure SQL DB), which explained the basics of Service tiers, business continuity options, importantly the DTU calculator (Azure SQL DB DTU Calculator). It was like all unknown or little known pieces put together.

I am not getting into how-to-do, as it is very clearly explained in the blog. I struggled while executing the Powershell script which collects the performance data in the VM-with-SQL server.
Listing them down, so that, it is helpful for someone or even it could be for me in future !

• Install the Windows Powershell ISE, run it as administrator, and open the Ps1 file (download from the blog)
• Check if the file needs to be 'Unblocked' to allow it to be run (as it is a downloaded file from other source). 
• The SQL Server name is the catch in the parameter list for the "Get -Counter". For a long time, I struggled and could not figure out how to give the name. 
• If you wish to provide the SQL Server which is installed as "default" instance, then provide the name as "'\SqlServer:Databases(*)\Log Bytes Flushed/sec".
• If you have SQL installed as a named instance, find out the account name under which the instance is running. This is NOT the SQL Instance name. In my case, I had given the instance name as 'Sql2016' but it was running under 'MSSQL$SQL2016'. This can be found easily either via Services.msc or 'Sql Configuration Manager'. The SQL server has to be specified as "\MSSQL`$SQL2016:Databases(KIDB)\Log Bytes Flushed/sec". Note the symbol ` before the $.

Azure Deployment Experience - Series 3 - Bizarre behaviour in ReFS drive

In my previous post, I described a failure in a ReFS drive. In spite of claiming to be a Resilient one (Re stands for Resilience), we could not recover from bad sectors or corrupted sectors and we had to abandon that drive. 

Wonder why the C and D drive of Azure VMs are still NTFS based and why not ReFS. Does it mean Microsoft itself are skeptical to roll out ReFS for C and D drives.

Yet another problem we faced in one of the ReFS drives in one of the VMs. Here it goes

• Took a Windows OS based VM (D2 series), allocated Disks and created two logical drives - F and G, both with ReFS.
• Hosted the application in F drive (Web based application over IIS).
• Any modification to any of the files in the Web Application folder does not effect in the application. E.g. aspx file modified with some changes. The date time gets updated, but in the application, it does not reflect
• Do a App Pool recycle (of that web app) and then it reflects.
• Hosted the application in C and D drives (NTFS) and the changes work immediately (which is the expected behaviour).
• Hosted the application in G drive (ReFS) and the same problem like the F drive
• Tried a few other VMs, which has a ReFS drive but it worked.
• Create a new logical NTFS drive, repeat the test and it works (w/o app. pool recycle).

I guess, ReFS is not really resilient or fool-proof, especially in Production or sensitive environments. One-off case has occurred at least twice in our case. In the above case, at least we are able to do a work-around.

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.