The Challenge

As developers, we are up against odds that push us to make trade-offs in order to go into production on time. More often than not, it’s a race where security becomes an afterthought.

Securing Azure SQL Databases

Security mechanisms come in many flavors. It is a requirement that needs to be defined and implemented on day 1. These rituals (policies and practices), must become natural in your application life cycle management. Consider these as a starting point from which you can develop your own security practices.

• Do not use the default user for development, testing or for deployments
  • Create a user specifically for deployments (can perform schema alterations)
  • Create a user on a per application basis (cannot alter schema and has limited write access)
  • Create a user for support investigations (this should be read-only)
  • Create individual accounts for members of DevOps who will need to act upon the database. (these accounts should have limited write access)
• Reference data should be read-only (immutable versions) and should only be updated through deployments. This type of data can be stored in NoSQL data services to augment the overall scalability of your application.
• Enable Auditing for Azure SQL Database, this feature will give you deep insight in how the database is manipulated and about how it is used.
• Use SQL Database Projects to design, build, version and deploy
• Use schemas to segregate tenants, reference data, activity data and resouce (shared) data.
• Use schemas to keep track of ownership chaining
• Encrypt connection string passwords at rest
• Use strong passwords
• Set Trusted_Connection=False in the connection string. This forces server certificate validation
• Set Encrypt=True in the connection string to force the client to use SSL
• Ensure that you are covered against SQL Injection
• SQL Database Firewall rules should block everything except the consuming applications
  • SQL Database Firewall rules should be set on both the Server and Database

Rebuilding SQL Database Indexes

A few months ago I wrote a blog post titled “Don’t Forget About Index Maintenance on Azure SQL Database“. Since then, Microsoft Azure SQL Database has changed a lot. We aren’t as concerned about the size of the database anymore, because databases can reach 500 GB in size. Take a moment to think about that number. 500GB is a lot of data! Before you get excited and move on to more important things, ask yourself this question, does all that data really belong in my SQL Database? Put some thought into it, you may be surprised by the answers you come up with.

Steps Towards Optimized Azure SQL Databases

Azure SQL Database is a high density multi-tenant database service, whose performance is not guaranteed due to its nature. Thus, it’s imperative that we pay attention to details. This post is all about reviewing the structure and performance of existing Azure SQL Databases by giving you insights into what you should be looking for.

Prior to making any changes to your application, you should look into taking control of your database’s life cycle management by employing tools like the Microsoft SQL Server Data Tools. These tools allow you to target specific platforms like Azure SQL Database, which is very handy because it allows you to identify unsupported features before you deploy. Furthermore, they provide IntelliSense and make it possible to manage database versions by storing scripts in source control solutions like Microsoft Team Foundation Server.

Creating NONCLUSTERED INDEXES on Massive Tables in Azure SQL Database

There are times on Azure SQL Database when tables get to a certain size and that trying to create indexes results in timeouts.

A few months ago when I started to get these famous timeouts, I had reached 10 million records and I felt like I was looking for a needle in a hay stack!

This blog post is all about creating NONCLUSTERED INDEXES, I will try to go over best practices and reasons to keep in mind when you use them in Azure SQL Database.

Building Clustered Indexes on Non-Primary Key Columns in Azure SQL Database

Azure SQL Database doesn’t support Heap Tables. Thus creating tables in Azure SQL Database requires Clustered Indexes. By default Azure SQL Database creates the Clustered Index on the table’s Primary Key.

CREATE TABLE [dbo].[TestTable] (
    [Id]        UNIQUEIDENTIFIER NOT NULL,
    [FirstName] NVARCHAR (10)    NOT NULL,
    [LastName]  NVARCHAR (10)    NOT NULL,
    [Type]      INT              NOT NULL,
    [Timestamp] ROWVERSION       NOT NULL,
    PRIMARY KEY (Id));

Primary Keys often make good Clustered Indexes because data is regularly accessed through them. But there are times when the Primary Key just doesn’t cut it. Fortunately, you can define a Clustered Index on other columns.

CREATE TABLE [dbo].[TestTable] (
    [Id]        UNIQUEIDENTIFIER NOT NULL,
    [FirstName] NVARCHAR (10)    NOT NULL,
    [LastName]  NVARCHAR (10)    NOT NULL,
    [Type]      INT              NOT NULL,
    [Timestamp] ROWVERSION       NOT NULL,
    PRIMARY KEY NONCLUSTERED (Id));
CREATE CLUSTERED INDEX IX_TestTable on TestTable (FirstName, LastName);

Changing a clustered index in Azure SQL Database is possible but it comes with some constraints. Failing to satisfy the existing  constraints enforce by the index will result in “The new index definition does not match the constraint being enforced by the existing index”.

CREATE CLUSTERED INDEX IX_TestTable
ON TestTable ([Type],[FirstName],[LastName])
WITH (DROP_EXISTING=ON)
ON [Primary]

Identify Missing Indexes in Your Windows Azure SQL Database

Windows Azure SQL Database is a Cloud flavored version of SQL Server. As of today (June 29^th 2013), it isn’t 100% equivalent with the SQL Server SKUs that we are used to working with. Microsoft is continually adding new features to Windows Azure SQL Database so don’t get discouraged just yet. In my opinion, many of the "limitations" that are present in Windows Azure SQL Database are actually a good thing, because they force us as developers to use best practices in order to use it properly.

One of these limitations, is that we cannot profile the database by using the SQL Server Profile.

The teams at Microsoft have built an SQL Database Manager that can be access from your Windows Azure Management Portal ( http://manage.windowsazure.com ). Using this tool you can find missing indexes by browsing through the query statistics.

As you can imagine, this can take some time. Consequently, I set out to find a better way to find missing indexes without wasting a lot of time.

I found the following query on the sys.dm_db_missing_index_details (Transact-SQL) MSDN page.
This is an example script to see missing indexes as used by Microsoft Customer Support.

SELECT CONVERT (varchar, getdate(), 126) AS runtime,
       mig.index_group_handle,
       mid.index_handle,
       CONVERT (decimal (28,1),
        migs.avg_total_user_cost *
        migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans))
        AS improvement_measure,
       'CREATE INDEX missing_index_' +
       CONVERT (varchar, mig.index_group_handle) +
       '_' +
       CONVERT (varchar, mid.index_handle) +
       ' ON ' +
       mid.statement +
       ' (' + ISNULL (mid.equality_columns,'') +
       CASE WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL
        THEN ','
        ELSE ''
        END + ISNULL (mid.inequality_columns, '') +
        ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')',
                '') AS create_index_statement,
        migs.*,
    mid.database_id, mid.[object_id]
    FROM sys.dm_db_missing_index_groups mig
    INNER JOIN sys.dm_db_missing_index_group_stats migs
        ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details mid
        ON mig.index_handle = mid.index_handle
    WHERE CONVERT (decimal (28,1),
                   migs.avg_total_user_cost *
               migs.avg_user_impact *
              (migs.user_seeks + migs.user_scans)) > 10
    ORDER BY migs.avg_total_user_cost *
             migs.avg_user_impact *
         (migs.user_seeks + migs.user_scans) DESC

Listing Windows Azure SQL Database User Table Sizes & Row Counts

In an attempt to identify anomalies cause by modifications to my Cloud Services, I decided to monitor the size of the tables I created in Windows Azure SQL Database. And since I have a rough estimate of the number of records that each table is supposed to contain, I can raise alerts when they start growing out of control.

The following query produces a list of non-system tables along with their size in gigabytes and the number of records they contain.

SELECT    
      o.name AS [table_name],
      sum(p.reserved_page_count) * 8.0 / 1024 / 1024 AS [size_in_gb],
      p.row_count AS [records]
FROM   
      sys.dm_db_partition_stats AS p,
      sys.objects AS o
WHERE    
      p.object_id = o.object_id
      AND o.is_ms_shipped = 0
      
GROUP BY o.name , p.row_count
ORDER BY o.name DESC

Identifying the Top 20 Slowest Queries Being Executed Against a Windows Azure SQL Database

Today I started writing a service whose goal is to monitor my Windows Azure SQL Database in order to spot problems as soon as possible. Going through the Dynamic Management Views and Functions (Transact-SQL) documentation I came up with the following query to a list the 20 queries that consume the most Average CPU Time.

SELECT TOP 20 total_worker_time/execution_count AS [avg_cpu_time],
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
      ((CASE WHEN statement_end_offset = –1
         THEN
            (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
         ELSE
            statement_end_offset
         END)
      – statement_start_offset) / 2+1))  AS sql_statement,
    execution_count,
    plan_generation_num,
    last_execution_time,   
    total_worker_time,
    last_worker_time,
    min_worker_time,
    max_worker_time,
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,  
    max_physical_reads,  
    total_logical_writes,
    last_logical_writes,
    min_logical_writes,
    max_logical_writes,
  s1.sql_handle
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
WHERE s2.objectid is null
ORDER BY  (total_worker_time/execution_count) DESC;

Using the results from this query observe the impact that our new code has on the overall database performance. The service sends out email notifications to the development team if the average execution time of queries is greater than a predefined threshold. Consequently, the team is able to tackle performance issues early in the development life cycle.

Query Level Caching For Entity Framework And Windows Azure SQL Database Including Transient Fault Handling

Caching with Entity Framework has always been a challenge. Out of the box Entity Framework doesn’t have second level caching support. Even though, many open source solutions like the Scalable Object Persistence (SOP) Framework exist, I decided to implement a query level cache that uses the Transient Fault Handling Application Block to execute retry policies to transparently handle transient faults.

Keep in mind that transient faults are normal and that its not a question of if they will occur, it’s really a question of when they will occur. SQL Database instances are continuously being shifted around to prevent the service’s performance from degrading.

The Database fluent API I created executes reusable queries. Reusable queries greatly simplify the application’s design by encapsulating query logic in named concepts. They are like Stored Procedures in SQL Server, where team members can discover functionality by reading file names from the query folders. More details about reusable queries can be found in the reference section at the bottom of this post.

The code from this Post is part of the Brisebois.WindowsAzure NuGet Package

To install Brisebois.WindowsAzure, run the following command in the Package Manager Console

PM> Install-Package Brisebois.WindowsAzure

Get more details about the Nuget Package.

Scaling Windows Azure SQL Database to Your Operational Needs

Windows Azure SQL Database does not warn us when we are close to the Database MAXSIZE. If you are like me, you probably don’t want to pay for stuff you don’t use and your probably don’t want your application to stop responding. The only way to effectively accomplish this, is to monitor your Windows Azure SQL Databases on a regular basis.

This has happened to me quite a few times so far… and its just so irritating!