It takes about 4-5 clicks and a sagely amount of patience to do this. In the middle of a debug both can be hard to come by. I usually recommend the "Rule of three" for automating something.

If you had to do the exact same task the third time, automate it. - master jatlh, 'oH.

So cooked up this little PowerShell script. For tl;dr crowd, you can go get it here.

If you are still reading lets dig in. The script does some nifty things. First it gets your public IP. I found this cool no-nonsense HTTP endpoint at IfConfig.me. Hat-tip for a great name for the tool. Exactly does what it says. Returns a simple text response in body with your publicly visible IP. Here's a fiddler trace showing all the gory details on the wire.

Then it checks if the given firewall rule name already exists, if it does that one is deleted and a new one is created. I needed this as I constantly need to update the settings and there are few of us devs using the same DB and all have their own firewall rules.

One thing to note is I have removed the PowerShell Confirm dialog from the Remove-AzureRmSqlServerFirewallRule commandlet. I know what I am doing and didn't need the pesky prompts. If you are worried of accidentally removing a rule remove the -Force parameter from the command. This script also ignores errors if any.

Next up, making sure this scripts gets called from a batch file whose icon sits happily in your taskbar. But that's for another post.

Ref:
Remove-AzureRmSqlServerFirewallRule
New-AzureRmSqlServerFirewallRule

One of the major costs over the past year or so for us has been SQL Azure (Yeah Azure Database or whatever MSFT decides to call it next, I am old, I am sticking to SQL Azure). The new portal does help some in identifying rogue queries. It shows

TOP 5 queries per CPU consumption (Aggregation type: sum) Last 24 hrs.

This is great, however it may not be enough for all the needs. Fret not, the stuff the chart is generated with is called Dynamic Management Views or DMV for short. These are super powerful and can reveal a lot about your database and server performance.

A little warning from Microsoft before we move ahead.

Dynamic management views and functions return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. For example, in future releases of SQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM dynamicmanagementview_name in production code because the number of columns returned might change and break your application.

With that out of the way lets dig into some queries. I've written them to include only the TOP (n), if you are reading this, I assume you are already aware and will replace the < n > with the desired number.

Run the following queries against the database you want to troubleshoot.

Queries that were executed the maximum number of times

-- Get Queries that were executed the maximum number of times
SELECT TOP (<n>)  
    dmExecQueryStats.last_execution_time AS [TimeStamp], 
    dmExecSQLText.TEXT AS [QueryText], 
    dmExecQueryStats.execution_count AS [ExecutionCount], 
    dateadd(s, convert(bigint, dmExecQueryStats.total_worker_time) / 1000, convert(time, '00:00:00')) AS [Total CPU Time Used], 
    dateadd(s, convert(bigint, dmExecQueryStats.total_elapsed_time) / 1000, convert(time, '00:00:00')) AS [Total Elapsed Time]
FROM sys.dm_exec_query_stats AS dmExecQueryStats  
CROSS APPLY sys.dm_exec_sql_text(dmExecQueryStats.sql_handle) AS dmExecSQLText  
ORDER BY dmExecQueryStats.execution_count DESC  

Queries that took maximum time to execute

-- Get Queries that took maximum time to execute
SELECT TOP (<n>)  
    dmExecQueryStats.last_execution_time AS [TimeStamp], 
    dmExecSQLText.TEXT AS [QueryText], 
    dmExecQueryStats.execution_count AS [ExecutionCount], 
    dateadd(s, convert(bigint, dmExecQueryStats.total_worker_time) / 1000, convert(time, '00:00:00')) AS [Total CPU Time Used], 
    dateadd(s, convert(bigint, dmExecQueryStats.total_elapsed_time) / 1000, convert(time, '00:00:00')) AS [Total Elapsed Time]
FROM sys.dm_exec_query_stats AS dmExecQueryStats  
CROSS APPLY sys.dm_exec_sql_text(dmExecQueryStats.sql_handle) AS dmExecSQLText  
ORDER BY dmExecQueryStats.total_elapsed_time DESC  

Queries that consumed the maximum CPU

-- Get Queries that utilized maximum CPU time
SELECT TOP (<n>)  
    dmExecQueryStats.last_execution_time AS [TimeStamp], 
    dmExecSQLText.TEXT AS [QueryText], 
    dmExecQueryStats.execution_count AS [ExecutionCount], 
    dateadd(s, convert(bigint, dmExecQueryStats.total_worker_time) / 1000, convert(time, '00:00:00')) AS [Total CPU Time Used], 
    dateadd(s, convert(bigint, dmExecQueryStats.total_elapsed_time) / 1000, convert(time, '00:00:00')) AS [Total Elapsed Time]
FROM sys.dm_exec_query_stats AS dmExecQueryStats  
CROSS APPLY sys.dm_exec_sql_text(dmExecQueryStats.sql_handle) AS dmExecSQLText  
ORDER BY dmExecQueryStats.total_worker_time DESC  

Queries executed recently

-- Get Queries executed recently 
SELECT TOP (<n>)  
    dmExecQueryStats.last_execution_time AS [TimeStamp], 
    dmExecSQLText.TEXT AS [QueryText], 
    dmExecQueryStats.execution_count AS [ExecutionCount], 
    dateadd(s, convert(bigint, dmExecQueryStats.total_worker_time) / 1000, convert(time, '00:00:00')) AS [Total CPU Time Used], 
    dateadd(s, convert(bigint, dmExecQueryStats.total_elapsed_time) / 1000, convert(time, '00:00:00')) AS [Total Elapsed Time]
FROM sys.dm_exec_query_stats AS dmExecQueryStats  
CROSS APPLY sys.dm_exec_sql_text(dmExecQueryStats.sql_handle) AS dmExecSQLText  
ORDER BY dmExecQueryStats.last_execution_time  DESC  

These are the ones I used, there are far too many DMVs to cover in a blog post, however these should give a fair idea about how to use them.

In telecommunications, a communications protocol is a system of rules that allow two or more entities of a communications system to transmit information via any kind of variation of a physical quantity. These are the rules or standard that defines the syntax, semantics and synchronization of communication and possible error recovery methods. Protocols may be implemented by hardware, software, or a combination of both.

Source: Wikipedia (https://en.wikipedia.org/wiki/Communications_protocol)

We web developers thrive on HTTP, another protocol. For a long time we were content with GET and POST. Especially in the Microsoft world. Other HTTP verbs were unheard of. Come ASPNET MVC the paradigm changed and we were forced to become "HTTP Aware". With the adoption of REST (REpresentational State Transfer) and ASPNET WebAPI even more so. However the protocol is not just one way communication and the way HTTP Verbs like GET, PUT, POST, DELETE... are meant for the client to tell the server what it needs to do, similarly the server will have to respond in a predictable matter. In HTTP we do this with the HTTP Status codes. 404 anyone? Of course there are request and response headers in the protocol, but lets reserve that for another discussion.

HTTP Status Codes

The first digit of the Status-Code defines the class of response. The last two digits do not have any categorization role. There are 5 values for the first digit
1xx: Informational - Request received, continuing process
2xx: Success - The action was successfully received, understood, and accepted
3xx: Redirection - Further action must be taken in order to complete the request
4xx: Client Error - The request contains bad syntax or cannot be fulfilled
5xx: Server Error - The server failed to fulfill an apparently valid request

Source: IETF (https://tools.ietf.org/html/rfc2616#section-6.1.1)

Picking the right response code however is tricky and is a series of long winding decisions. I am often confused making those decisions and end up making wrong choices. So to keep matters simple I started looking for a simple decision tree to aid in the decision making process. I found quite a few but they were too complex to decipher and scroll through, so decided to bite the bullet and draw one in Visio. The png below is for reference and the VSDX is available here.

For the sake of full disclosure, this is not a ultimate guide. I have omitted the verbs TRACE, used for remote application-layer loopbacks and CONNECT, used by proxies that can dynamically switch to being a tunnel. Both of which I have no business coding as of today or in near future.

The way I am working through this file right now, is when I have a question about a response code, I look up the Verb being requested, and follow the decision tree from there. Hope this helps you too.

Further reading:

• HTTP General Headers
• HTTP Entity Headers
• HTTP Response Headers

PS: Very important response code is also not included in the diagramHTTP 418. Sorry for being a HTTP 418

So a few searches later landed at this awesome page on MSDN (https://msdn.microsoft.com/en-us/library/ms190324.aspx). And managed to put together this little script. There could be better ways of doing this, but this one worked for our needs.

Happy to see how you are doing it

-- Run this against the database from which you want the row counts
-- Ref https://msdn.microsoft.com/en-us/library/ms190324.aspx
SELECT  
      QUOTENAME(SCHEMA_NAME(sysObjects.schema_id)) + '.' + QUOTENAME(sysObjects.name) AS [Table]
      , SUM(sysPartitions.Rows) AS [Rows]
FROM  
      sys.objects AS sysObjects
      INNER JOIN sys.partitions AS sysPartitions
            ON sysObjects.object_id = sysPartitions.object_id
WHERE  
      sysObjects.type = 'U' -- U = Table (user-defined) (https://msdn.microsoft.com/en-us/library/ms190324.aspx)
      AND sysObjects.is_ms_shipped = 0x0 --Object is created by an internal SQL Server component. Ignore these objects from the query
      AND index_id < 2 -- We need all Heap (0) and Clustered (1) indices
GROUP BY  
      sysObjects.schema_id, sysObjects.name
ORDER BY [Table]  
GO  

Also available at a gist here (https://gist.github.com/sarangbk/01549440ef73e8729b19baf0608c08c8) for some reason Gist editor is not happy to syntax highlight any SQL code today so not including the embed here.

Enter the Ghost

Finally I almost decided to go Medium, but this excellent blog post by @troyhunt convinced me otherwise. So off I go to Ghost.org to signup and sadly they've changed their pricing since. The 8$ a month was real tempting but at 14$ a month I have my reservations. The simplicity of Ghost was still remarkable and I already had it running on a machine at home. What nailed it for me finally was this thing called "Deploy to Azure".

Which automagically deploys stuff to an Azure website, using Azure Resource Manager templates. It's another DevOpsy shiny new thing which I will try to cover in another post. In simple terms it provisions and configures the Azure Website for you and deploys the code there.

That brings us to this potentially bad decision of hosting Ghost on my own. This is forked from Felix Riesberg's Ghost-Azure repo, so a big thanks is due to the dude. This repo is a good example of how and why to use the Deploy to Azure button.

Custom Domain

I have moved my domains to DNSimple and it really is dead simple. Azure website is available as a one-click service along many others and it took less than a minute to setup the ALIAS and CNAME to awverify records. After that its a quick step in the Azure Portal to tell azure that you want to use this domain, and telling ghost you will be using this domain. This can be done at your blog's /ghost/settings/navigation/ page, and voila!

Syntax Highlighting

I am currently trying out Prism.js for syntax highlighting. The installation is a bit of work, uploading one js and one css file to the theme's directory and editing the theme file to link those js and css files.

I hope to write a better todo for this, but there already is plenty of help at Ghost.org. Happy blogging.

Getting Started

Ghost uses something called Markdown for writing. Essentially, it's a shorthand way to manage your post formatting as you write!

Writing in Markdown is really easy. In the left hand panel of Ghost, you simply write as you normally would. Where appropriate, you can use shortcuts to style your content. For example, a list:

• Item number one
• Item number two
  • A nested item
• A final item

or with numbers!

1. Remember to buy some milk
2. Drink the milk
3. Tweet that I remembered to buy the milk, and drank it

Links

Want to link to a source? No problem. If you paste in a URL, like http://ghost.org - it'll automatically be linked up. But if you want to customise your anchor text, you can do that too! Here's a link to the Ghost website. Neat.

What about Images?

Images work too! Already know the URL of the image you want to include in your article? Simply paste it in like this to make it show up:

Not sure which image you want to use yet? That's ok too. Leave yourself a descriptive placeholder and keep writing. Come back later and drag and drop the image in to upload:

Quoting

Sometimes a link isn't enough, you want to quote someone on what they've said. Perhaps you've started using a new blogging platform and feel the sudden urge to share their slogan? A quote might be just the way to do it!

Ghost - Just a blogging platform

Working with Code

Got a streak of geek? We've got you covered there, too. You can write inline <code> blocks really easily with back ticks. Want to show off something more comprehensive? 4 spaces of indentation gets you there.

.awesome-thing {
    display: block;
    width: 100%;
}

Ready for a Break?

Throw 3 or more dashes down on any new line and you've got yourself a fancy new divider. Aw yeah.

Advanced Usage

There's one fantastic secret about Markdown. If you want, you can write plain old HTML and it'll still work! Very flexible.

That should be enough to get you started. Have fun - and let us know what you think :)