skip to Main Content

Index Fragmentation in SQL Azure, Who Knew!

I’ve been on my project for over a year and it has significantly grown as an application and in data during the year. It’s been nonstop new features. I’ve rarely gone back and refactored code. Last week I noticed some of the data heavy pages were loading slowly. At the worst case one view could take up to 30 seconds to load. 10 times over my maximum load time…

Call me naive, but I didn’t consider index fragmentation in SQL Azure. It’s the cloud! It’s suppose to be immune to premise issues… Apparently index fragmentation is also an issue in the cloud.

I found a couple of queries on an MSDN blog, that identify the fragmented indexes and then rebuilds them.

After running the first query to show index fragmentation I found some indexes with over 50 percent fragmentation. According to the article anything over 10% needs attention.

First Query Display Index Fragmentation

--Get the fragmentation percentage

SELECT
 DB_NAME() AS DBName
,OBJECT_NAME(ps.object_id) AS TableName
,i.name AS IndexName
,ips.index_type_desc
,ips.avg_fragmentation_in_percent
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
ORDER BY ps.object_id, ps.index_id

Second Query Rebuilds the Indexes

--Rebuild the indexes
DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
(
 SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
 FROM INFORMATION_SCHEMA.TABLES IST
 WHERE IST.TABLE_TYPE = 'BASE TABLE'
 )

 OPEN TableCursor
 FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0

 BEGIN
 PRINT('Rebuilding Indexes on ' + @TableName)
Begin Try
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
End Try
Begin Catch
 PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
 End Catch
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

Source

Proofing a Concept and Growing the Code

In a recent conversation, a friend mentioned he creates proof of concepts and then discards them after testing their viability. I’ve done the same in the past. This time it didn’t feel right. I cringed when he said he threw away to the code. Maybe my days as a business owner has turned me into a froogle goat, but it felt like he was throwing away value.

Why don’t we continue forward with a proof of concept?

Generally when I think of a proof of concept its hastily assembled. Many of the “best practices” are short-cutted if not downright ignored. The goal is to test the feasibility an idea. At some point you’ll realize if the solution will work. Then you’ll decide if it’s time to walk away from the idea and ditch the proof of concept or move forward with the idea. If you move forward with the idea, why not keep coding and turn the proof of concept into the real deal?

I’ll be honest here, it seems ridiculous that you’d create a solution and then throw it away just to create it again. That’s like poorly painting an entire house just to see if you like the color. “Yep, the color is good. Let’s paint the house for reals this time and this time we’ll do a good job.

There is another way. Evolve the code. Add in the missing infrastructure. This has the possibility of growing into a long term healthy solution.

Walking away from a proof of concept costs you value (time and money) that might otherwise be captured. Even if you don’t capture 100%, you’ll still be better off than just chucking everything and walking away. So next time, give it a try. See if you can morph a proof of concept into a sustainable project. I think you might be surprised at the end result.

Securing AngularJS with Claims

At some point an application needs authorization. This means different levels of access behave differently on a web site (or anything for that matter). It can be anything from seeing data to whole area’s that are not accessible by a group of users.

In non Single Page Applications (SPA), a claim or role is associated with data or an area of the application, either the user has this role or claim or he does not. In a SPA it’s the same, but with a huge disclaimer. A SPA is downloaded to the browser. At this point the browser has total control over the code. A nefarious person can change the code to do his bidding.

Because SPAs can’t be secured, authentication and authorization in a SPA is simply user experience. All meaningful security must be done on the web server. This article does not cover securing your API against attacks. I recommend watching a video from Pluralsight or reading a paper that addresses security for your server technology.

The intent of this article to show you how I added an authorization user experience to my Angular 1.x SPA.

Security Scopes

I have identified 3 areas of the UI that need authorization: Elements (HTML), Routes, and Data.

Just a reminder, securing a SPA is no substitute to securing the server. Permissions on the client is simply to keep the honest people honest and to provide the user with a good experience.

The 3 areas in detail:

Elements

You’ll need to hide specific HTML elements. It could be a label, a table with data, a button, or any element on the page.

Routes

You’ll want to hide entire routes. In certain cases you don’t want the user accessing a view. By securing the route a user can’t to navigate to the view. They instead will be shown a “You are not authorized to navigate to this view” message.

Data

Sometimes hiding the elements in the view is not enough. An astute user can simply view the source and see the hidden data in the HTML source or watch it stream to the browser. What we want is the data not to be retrieve in the first place..

Adding security is tricky. At first I tried constraining the access at the HTTP API (on the client). I quickly realized this wouldn’t work. A user might not have direct access to the data, but this doesn’t mean they don’t indirectly access to the data. At the HTTP API layer (usually one of the lowest in the application) we can’t tell the context of the call and therefore can’t apply security concerns to it.

Below I have provided coding samples:

Code

I created a service for the authorization checking code. This is the heart of the authorization. All authorization requests use this service to check if the user is authorized for the particular action.

angular.module('services')
    .service('AuthorizationContext',function(_, Session){

        this.authorizedExecution = function(key, action){

            //Looking for the claim key that was passed in. If it exists in the claim set, then execute the action.
            Session.claims(function(claims){
                var claim = findKey(key, claims);

                //If Claim was found then execute the call.
                //If it was not found, do nothing
                if(claim !== undefined){
                    action();
                }
            });
        };

        this.authorized = function(key, callback){
            //Looking for the claim key that was passed in. If it exists in the claim set, then execute the action.
            Session.claims(function(claims){
                var claim = findKey(key, claims);

                //If they don't have any security key, then move forward and authorization.
                var valid = claim !== undefined;
                callback(valid);
            });
        };

        //this.agencyViewKey = '401D91E7-6EA0-46B4-9A10-530E3483CE15';

        function findKey(key, claims){
            var claim = _.find(claims, function(item){
                return item.value === key;
            });

            return claim;
        }
    });

Authorize Directive

The authorize directive can be applied to any HTML element that you want to hide from users without a specific level of access. If the user has the access token as part of their claims they are allow to see the element. If they don’t it’s hidden from them.

angular.module(directives')
    .directive('authorize', ['$compile', 'AuthorizationContext', function($compile, AuthorizationContext) {
        return {
            restrict: 'A',
            replace: true,
            //can't have isolated the scope in a shared directive
            link:function ($scope, element, attributes) {

                var securityKey = attributes.authorize;
                AuthorizationContext.authorized(securityKey, function(authorized){
                    var el = angular.element(element);
                    el.attr('ng-show', authorized);

                    //remove the attribute, otherwise it creates an infinite loop.
                    el.removeAttr('authorize');
                    $compile(el)($scope);
                });
            }
        };
    }]);

Elements

I rely heavily on tabs in my application. I apply the authorize directive to the tab that I want to hide from users without the proper claims.

<tabset>
<tab ng-cloak heading="Users" authorize="{{allowUserManagement}}">
...html content
</tab>
</tabset>

Routes

I’m using the ui-router. Unfortunately for those who are not, I don’t have code for the out of the box AngularJS router.

In the $stateChangeStart I authenticate the route. This is the code in that event.

$rootScope.$on("$stateChangeStart", function(event, toState, toParams, fromState, fromParams){
   AuthenticationManager.authenticate(event, toState, toParams);
});

The function that authorizes the route. If it’s authorized, the route is allowed to continue. If it’s not authorized, a message is displayed to the user and they are directed to the home page.

function authorizedRoute(toState, location, toaster, breadCrumbs){
   if(toState.authorization !== undefined){
       AuthorizationContext.authorized(toState.authorization, function(authorized){
           if(!authorized){
               toaster.pop('error', 'Error', 'You are not authorized to view this page.');
               location.path("/search");
           } else {
               breadCrumbs();
           }
       });
   } else{
       breadCrumbs();
   }
}

In this router definition you’ll notice a property called ‘authorization’. If the user has this claim they are allowed to proceed.

angular.module('agency',
    [
        'ui.router',
        'services'
    ])
    .config(function config($stateProvider){
    $stateProvider.state( 'agency', {
        url: '/agency',
        controller: 'agency.index',
        templateUrl: 'agency/agency.tpl.html',
        authenticate: true,
        authorization:'401d91e7-6ea0-46b4-9a10-530e3483ce15',
        data:{ pageTitle: 'Agency' }
    });
});

Data

In some cases, you don’t want to make a request to the server for the data. If the user has the claim they’ll be allowed to make the request.

The above AuthorizationContext at beginning of the article show the code for authoriedExecution. Here you see it’s usage.

AuthorizationContext.authorizedExecution(Keys.authorization.allowUserManagement, function(){
    //execute code, if the loggedin user has rights.

                });

As I mentioned above, this is no substitute for securing the server. This code works for providing a wonder user experience.

5 Steps for Coding for the Next Developer

Most of us probably don’t think about the developer who will maintain our code. Until recently, I did not consider him either. I never intentionally wrote obtuse code, but I also never left any breadcrumbs.

Kent Beck on good programmers:

Any fool can write code that a computer can understand. Good programmers write code that humans can understand.

Douglas Crockford on good computer programs:

It all comes down to communication and the structures that you use in order to facilitate that communication. Human language and computer languages work very differently in many ways, but ultimately I judge a good computer program by it’s ability to communicate with a human who reads that program. So at that level, they’re not that different.

Discovering purpose and intent is difficult in the most well written code. Any breadcrumbs left by the author, comments, verbose naming and consistency, is immensely helpful to next developers.

I start by looking for patterns. Patterns can be found in many places including variables names, class layout and project structure. Once identified, patterns are insights into the previous developer’s intent and help in comprehending the code.

What is a pattern? A pattern is a repeatable solution to a recurring problem. Consider a door. When a space must allow people to enter and to leave and yet maintain isolation, the door pattern is implemented. Now this seems obvious, but at one point it wasn’t. Someone created the door pattern which included the door handle, the hinges and the placement of these components. Walk into any home and you can identify any door and it’s components. The styles and colors might be different, but the components are the same. Software is the same.

There are known software patterns to common software problems. In 1995, Design Patterns: Elements of Reusable Object-Oriented Software was published describing common software patterns. This book describes common problems encountered in most software application and offered an elegant way to solve these problems. Developers also create their own patterns while solving problems they routinely encounter. While they don’t publish a book, if you look close enough you can identify them.

Sometimes it’s difficult to identify the patterns. This makes grokking the code difficult. When you find yourself in this situation, inspect the code, see how it is used. Start a re-write. Ask yourself, how would you accomplish the same outcome. Often as you travel the thought process of an algorithm, you gain insight into the other developer’s implementation. Many of us have the inclination to re-write what we don’t understand. Resist this urge! The existing implementation is battle-tested and yours is not.

Some code is just vexing, reach out to a peer — a second set of eyes always helps. Walk the code together. You’ll be surprised what the two of you will find.

Here are 5 tips for leaving breadcrumbs for next developers

1. Patterns
Use known patterns, create your own patterns. Stick with a consistent paradigm throughout the code. For example, don’t have 3 approaches to data access.

2. Consistency
This is by far the most important aspect of coding. Nothing is more frustrating than finding inconsistent code. Consistency allows for assumptions. Each time a specific software pattern is encountered, it should be assumed it behaves similarly as other instances of the pattern.

Inconsistent code is a nightmare, imagine reading a book with every word meaning something different, including the same word in different places. You’d have to look up each word and expend large amounts of mental energy discovering the intent. It’s frustrating, tedious and painful. You’ll go crazy! Don’t do this to next developer.

3. Verbose Naming
This is your language. These are the words to your story. Weave them well.

This includes class names, method names, variable names, project names and property names.

Don’t:

if(monkey.HoursSinceLastMeal > 3)
{
    FeedMonkey();
}

Do:

int feedInterval = 3;

if(monkey.HoursSinceLastMeal > feedInterval)
{
    FeedMonkey();
}

The first example has 3 hard coded in the if statement. This code is syntactically correct, but the intent of the number 3 tells you nothing. Looking at the property it’s evaluated against, you can surmise that it’s really 3 hours. In reality we don’t know. We are making an assumption.

In the second example, we set 3 to a variable called ‘feedInterval’. The intent is clearly stated in the variable name. If it’s been 3 hours since the last meal, it’s time to feed the monkey. A side effect of setting the variable is we can now change the feed interval without changing the logic.

This is a contrived example, in a large piece of software this type of code is self documenting and will help the next developer understand the code.

4. Comments
Comments are a double edge sword. Too much commenting increases maintenance costs, not enough leaves developers unsure on how the code works. A general rule of thumb is to comment when the average developer will not understand the code. This happens when the assumptions are not obvious or the code is out of the ordinary.

5. Code Simple
In my professional opinion writing complex code is the biggest folly among developers.

Steve Jobs on simplicity:

Simple can be harder than complex: You have to work hard to get your thinking clean to make it simple. But it’s worth it in the end because once you get there, you can move mountains.

Complexity comes in many forms, some of which include: future proofing, overly complex implementations, too much abstraction, large classes and large methods.

For more on writing clean simple code, see Uncle Bob’s book Clean Code and Max Kanat-Alexander’s Code Simplicity

Closing

Reading code is hard. With a few simple steps you can ensure the next developer will grok your code.

Implementing Transparent Encryption with NHibernate Listeners (Interceptors)

Have you ever had to encrypt data in the database? In this post, I’ll explore how using nHibernate Listeners to encrypt and decrypt data coming from and going into your database. The cryptography will be transparent to your application.

Why would you want to do this? SQL Server has encryption baked into the product. That is true, but if you are moving to the cloud and want to use SQL Azure you’ll need some sort of cryptography strategy. SQL Azure does not support database encryption.

What is an nHibernate Listener? I think of a Listener as a piece of code that I can inject into specific extensibility points in the nHibernate persistence and data hydration lifecycle.

As of this writing the following extensibility points are available in nHibernate.

  • IAutoFlushEventListener
  • IDeleteEventListener
  • IDirtyCheckEventListener
  • IEvictEventListener
  • IFlushEntityEventListener
  • IFlushEventListener
  • IInitializeCollectionEventListener
  • ILoadEventListener
  • ILockEventListener
  • IMergeEventListener
  • IPersistEventListener
  • IPostCollectionRecreateEventListener
  • IPostCollectionRemoveEventListener
  • IPostCollectionUpdateEventListener
  • IPostDeleteEventListener
  • IPostInsertEventListener
  • IPostLoadEventListener
  • IPostUpdateEventListener
  • IPreCollectionRecreateEventListener
  • IPreCollectionRemoveEventListener
  • IPreCollectionUpdateEventListener
  • IPreDeleteEventListener
  • IPreInsertEventListener
  • IPreLoadEventListener
  • IPreUpdateEventListener
  • IRefreshEventListener
  • IReplicateEventListener
  • ISaveOrUpdateEventListener

The list is extensive.

To implement transparent cryptography, we need to find the right place to encrypt and decrypt the data. For encrypting the data we’ll use IPostInsertEventListener and IPostUpdateEventListener. With these events we’ll catch the new data and the updated data going into the database. For decrypting, we’ll use the IPreLoadEventListener.

For this demonstration we’ll be using DatabaseCryptography class for encrypting and decrypting. The cryptography implementation is not important for this article.

IPreLoadEventListener

public class PreLoadEventListener : IPreLoadEventListener
{
readonly DatabaseCryptography _crypto = new DatabaseCryptography();

///
/// Called when [pre load].
///

///The event. public void OnPreLoad(PreLoadEvent @event)
{
_crypto.DecryptProperty(@event.Entity, @event.Persister.PropertyNames, @event.State);
}
}

IPreInsertEventListener

public class PreInsertEventListener : IPreInsertEventListener
{
readonly DatabaseCryptography _crypto = new DatabaseCryptography();

///
/// Return true if the operation should be vetoed
///

///The event. /// true if XXXX, false otherwise.
public bool OnPreInsert(PreInsertEvent @event)
{
_crypto.EncryptProperties(@event.Entity, @event.State, @event.Persister.PropertyNames);

return false;
}
}

IPreUpdateEventListener

public class PreUpdateEventListener : IPreUpdateEventListener
{
readonly DatabaseCryptography _crypto = new DatabaseCryptography();

///
/// Return true if the operation should be vetoed
///

///The event. /// true if XXXX, false otherwise.
public bool OnPreUpdate(PreUpdateEvent @event)
{
_crypto.EncryptProperties(@event.Entity, @event.State, @event.Persister.PropertyNames);

return false;
}
}

It’s important to note that on both IPreUpdateEventListener and IPreInsertEventListener must return false, otherwise the insert/update event will be aborted.

Now that we have the Listeners implemented we need to register them with nHibernate. I am using FluentNHibernate so this will be different if you are using raw nHibernate.

SessionFactory

public class SessionFactory
{
///
/// Creates the session factory.
///

/// ISessionFactory.
public static ISessionFactory CreateSessionFactory()
{
return Fluently.Configure()

.Database(MsSqlConfiguration.MsSql2012
.ConnectionString(c =&gt; c
.FromConnectionStringWithKey("DefaultConnection")))

.Mappings(m =&gt; m.FluentMappings.AddFromAssemblyOf())
.ExposeConfiguration(s =&gt;
{
s.SetListener(ListenerType.PreUpdate, new PreUpdateEventListener());
s.SetListener(ListenerType.PreInsert, new PreInsertEventListener());
s.SetListener(ListenerType.PreLoad, new PreLoadEventListener());
})
.BuildConfiguration()
.BuildSessionFactory();
}

When decrypting and encrypting data at the application level it makes the data useless in the database. You’ll need to bring the data back into the application to read the values of the encrypted fields. We want to limit the fields that are encrypted and we only want to encrypt string values. Encrypting anything other that string values complicates things. There is nothing saying we can’t encrypt dates, but doing so will require the date field in the database to become a string(nvarchar or varchar) field, to hold the encrypted data, once we do this we lose the ability to operate on the date field from the database.

To identify which fields we want encrypted and decrypted I’ll use marker attributes.

Encrypt Attribute

public class EncryptAttribute : Attribute
{
}

Decrypted Attribute

public class DecryptAttribute : Attribute
{
}

To see the EncryptAttribute and the DecryptedAttribute in action we’ll take a peek into the DatabaseCryptography class.

DatabaseCryptography

public class DatabaseCryptography
{
    private readonly Crypto _crypto = ObjectFactory.GetInstance();

    ///
    /// Encrypts the properties.
    ///
    ///The entity. ///The state. ///The property names. 
    public void EncryptProperties(object entity, object[] state, string[] propertyNames)
    {
        Crypt(entity, propertyNames, s = &gt;
        _crypto.Encrypt(s),
        state)
        ;
    }

    ///
    /// Crypts the specified entity.
    ///

    ///
    ///The entity. ///The state. ///The property names. ///The crypt.
    private void Crypt(object entity, string[] propertyNames, Func&lt;string, string&gt; crypt, object[] state) where T : Attribute
    {
        if (entity != null)
        {
            var properties = entity.GetType().GetProperties();

            foreach (var info in properties)
            {
                var attributes = info.GetCustomAttributes(typeof (T), true);

                if (attributes.Any())
                {
                    var name = info.Name;
                    var count = 0;

                    foreach (var s in propertyNames)
                    {
                        if (string.Equals(s, name, StringComparison.InvariantCultureIgnoreCase))
                        {
                            var val = Convert.ToString(state[count]);
                            if (!string.IsNullOrEmpty(val))
                            {

                                val = crypt(val);
                                state[count] = val;
                            }

                            break;
                        }

                        count++;
                    }
                }
            }
        }
    }

    ///
    /// Decrypts the property.
    ///
    ///The entity. ///The state. ///The property names. 
    public void DecryptProperies(object entity, string[] propertyNames, object[] state)
    {
        Crypt(entity, propertyNames, s = &gt;
        _crypto.Decrypt(s),
        state)
        ;
    }

}

That’s it. Now the encryption and decryption of data will be transparent to the application and you can go on your merry way building the next Facebook.

Back To Top