Introducing Your Favorite Snippet Tool

Snippets in Visual Studio and SQL Server Management Studio are a great help, and tremendous time savers. Unfortunately the con about VS and SSMS snippets are: They are tedious to create. I have a feeling, that makes it less appealing to use custom snippets, because it includes working with XML to create them, and VS or SSMS offers no help.

History

Well, a couple of days ago, I decided to make a snippet of some SQL, which I had realized, I had to write regularly in the future. I was pretty tired of writing this SQL, and then I remembered: To create a snippet you have to setup an XML document. Then I got really tired. In hope of an easy solution, I did search the web for an online snippet creator. All i did find was tools, which had to be downloaded. No offence, these downloadables are properly mighty fine, but nowadays I think a tool like a snippet creator should be online. Easy to reach, and not another downloaded tool to soil your computer.

Priorities can be strange some times, and I decided, that I would rather write an online tool myself, which could make snippets, than do another handmade snippet.

So here after a small coding marathon, I'll present to you:

YOUR FAVORITE SNIPPET TOOL(that is the name)

Link: www.snippettool.net

Enjoy!

FAQ

Q: Why is the link www.snippettool.net and not www.yourfavoritesnippettool.com when the tool name is Your Favorite Snippet Tool?

A: For your convenience. It is much easier, to remember www.snippettool.net and type it right. 

Q: The first release is version 0.8.0, is it production ready?

A: Yes. There is some extended features for VB, which will be there in a later release. Further I have some ideas for UI improvements. Also, until I have had some more feedback, it wouldn't be right to release it in version 1.0.0

Q: VSI packages are supported, what about VSIX packages?

A: VSIX does not by default, support snippets. Hacks must be applied to make VSIX work with snippets.

Q:The Visual Studio Content Installer does not install the VSI package to Visual Studio 2xxx, why?

A: That is because, Visual Studio Content Installer is a strange piece of software. Specially, if you have more versions of Visual Studio on your machine.

Q: I found a bug, what to do?

A: I will appreciate, if you would write to me about it. Contact information is at the button of the page. 

SSIS: An easy SCD optimization for dev and prod

The value of reading this post, depends on how you work with SSIS and how database nursing are handled in within your organization.

The optimization is a single index, but if you only nurse indexes in prod, you could waste a great time when developing SCDs in SSIS. The method is simple, when you now the nature of your SCD, then you can create an index right away, and reduce your development waiting time. Specially if you are testing with bigger volumes of data.

Let me show you

Let's say you have following table definitions, and you working in a SSIS project using Visual Studio:

-- Staging

CREATE TABLE Staging.Customers

(

CustomerId UNIQUEIDENTIFIER,

FistName NVARCHAR(200),

MiddleInitials NVARCHAR(200),

LastName NVARCHAR(200),

AccountId INT,

CreationDate DATETIME2

)

GO 

-- Dimension

CREATE TABLE dbo.dimCustomers

(

CustomerDwhKey INT IDENTITY(1,1),

[Current] BIT, 

CustomerId UNIQUEIDENTIFIER,

FistName NVARCHAR(200),

MiddleInitials NVARCHAR(200),

LastName NVARCHAR(200),

AccountId INT,

CreationDate DATETIME2

CONSTRAINT PK_CustomerId PRIMARY KEY CLUSTERED (CustomerDwhKey)

)

GO

You have a Data Flow, where you transfer data from Staging.Customers to the dimension dbo.dimCustomers using the built-in component Slowly Changing Dimension:

In our example setup CustomerId will be a so-called Business key, And Current will be the indicator for which row are current. It should also be noted, it is possible to have more than one Business keys.

We'll configure attributes as:

Now, the Slowly Changing Dimension component work in following way:

For each entity it recieves, it will search the dimension table for an entity with the same business keys(in plural!!!) and is flagged as current, or in plain SQL:

SELECT  attribute[, attribute] FROM dimension_table WHERE current_flag = true AND business_key = input_business_key[, business_key = input_business_key]

Or as it will look like in our example

SELECT AccountId, CreationDate, FirstName, MiddleInititals, LastName FROM dbo.dimCustomers WHERE [Current] = 1 AND CustomerId = some_key

Further, in case we have an historical change, the current entity in the dimension must be expired by setting Current = 0.

UPDATE dbo.dimCustomers SET [Current] = 0 WHERE [Current] = 1 AND CustomerId = some_key 

The solution

As you might have realize by now, we can improve performance tremendously by putting an index on the current flag and the business keys(again plural!!!). For each entity passing through the Slowly Changing Dimension component, the will be at least 1, but likely 2 searches in the dimension table. And the by knowing the business keys and current flag. and the nature of the Slowly Changing Dimension component, you can predict the index which will improve performance.

The index for our sample will be

CREATE NONCLUSTERED INDEX IX_Current_CustomerId ON dbo.dimCustomers

(

[Current],

CustomerId --- Remember to include each business key

)

Should index be a filtered? I'll let that be up to you.

Indexes, bulk and loading of dimensions

Some tend to drop indexes when loading dimenson, with the argument: Bulk loading is fastest without indexes, which SQL Server has to maintain while loading. This argument has to be revised when working with the Slowly Changing Dimension component.

Because the component searches the dimensions so heavily, (in general) it will be faster loading with indexes than without. If there is no indexes, each entity going through the component, will require at least one table scan, which is quite expensive, and gets more expensive as your dimension grows. 

That's all

Wrestling the Azure Storage REST API - Part 2

This post is about authorization HTTP header, used when making requests to Azure Storage API. There is some dependencies the previous part of this series, specially regarding the x-ms-date header field.

Authorization

The authorization field is expressed in this way:

Authorization="[SharedKey|SharedKeyLite] <AccountName>:<Signature>"

The authorization supports 2 schemes for calculating signatures, Shared Key or Shared Key Lite. The scheme you are using for authorization must be stated with either SharedKey or SharedKeyLite as the first thing in the header field.

The difference between the schemes are, Shared Key Lite is backward compatible with earlier versions of the Azure Storage API. I can't remember to have seen any example with Shared Key, I guess it is because it requires more effort to make it work.

Important!!! When using dates in authorization, these dates must be the same as x-ms-date, or else the authorisation will fail.

Shared key

Blob, Queue and File Storage signature is calculated one way, while Table Storage signature is calculated in another way. 

Blob, Queue and File Storage:

StringToSign = 

VERB + "\n" +
Content-Encoding + "\n" +
Content-Language + "\n" +
Content-Length + "\n" +
Content-MD5 + "\n" +
Content-Type + "\n" +
Date + "\n" +
If-Modified-Since + "\n" +
If-Match + "\n" +
If-None-Match + "\n" +
If-Unmodified-Since + "\n" +
Range + "\n" +
CanonicalizedHeaders +
CanonicalizedResource;

Table Storage:

StringToSign = 

VERB + "\n" +
Content-MD5 + "\n" +
Content-Type + "\n" +
Date + "\n" + 

CanonicalizedResource;

Shared key Lite

Like Shared Keys, there is a difference in calculating the keys depending on what kind of storage is used:

Blob, Queue and File Storage:

StringToSign =
VERB + "\n" +
Content-MD5 + "\n" +
Content-Type + "\n" +
Date + "\n" +
CanonicalizedHeaders +
CanonicalizedResource;

Table Storage:

StringToSign = 

Date + "\n" +
CanonicalizedResource

When comparing the 2 schemes, it begins to make sense why most chose to use Shared Key Lite

Which parameter must be filled, depends heavily on context. E.g. Date, while it must be set in every Table request, there is some Blob requests where it must not be set.

Canonicalized Headers

Just take all the header starting with x-ms-, sort them and concatenate them separated by \n.

Exmaple(taken from the Azure Storage documentation):

 x-ms-date:Sun, 20 Sep 2009 20:36:40 GMT\nx-ms-meta-m1:v1\nx-ms-meta-m2:v2\n

Canonicalized Resources

Canonicalized resources is form the following way:

Canonicalized resource = /account/resource

Example:

For this request

GET https://myaccount.table.core.windows.net/Tables HTTP/1.1

The canonicalized resource will be /myaccount/Tables

Query parameters must not be included. Unless you make following request(taken from documentation):

GET https://<account-name>.table.core.windows.net/?restype=service&comp=properties HTTP/1.1

Here the canonicalized resource will be /myaccount/?comp=properties

Calculating the signature

Here is the Azure Storage REST API documentation pretty weak. 2 things it misses are, when using HMAC you need to supply a key and a message. In context of making requests to the Azure Storage REST API, the key is either the Primary og Secondary key, which can be obtain from the Azure portal. The message is the StringToSign defined earlier in this post.

Also, the Primary and Secondary key which is found on the Azure portal are base64 encoded, you need to decode them, in order to be able to use them.

So what the documentation states as

Signature=Base64(HMAC-SHA256(UTF8(StringToSign))) 

Is in reality

Signature=Base64(HMAC-SHA256(UTF8(Debase64(key)),UTF8(StringToSign))) 

Where key is either the Primary or Secondary key.

And this is all for Authorization.

Wrestling the Azure Storage REST API - Part 1

Motivation

With Azure SDKs for a wide variety of programming languages, why should anybody want to learn about the Azure Storage REST API? 

Maybe there is no SDK for your favourite language, which was my case. Maybe the official SDK do not support the latest API version, which could mean it is not possible to communicate with JSON in Table Storage. Maybe you are just courios.

This blog post is based on my work on GoHaveAzureStorage, and hopefully you will also gain from challenges I have had the Azure Storage.

Request break down 

A REST call looks like this:

GET https://myaccount.table.core.windows.net/Tables HTTP/1.1

This request is used to get all tables for an storage account. There is 2 mandatory http header fields, and an additional optional which I recommend, which you must send for every request to make it work. They are 

• x-ms-date       - time for the request.
• x-ms-version  - Which API version is the request targeting
• authorization  - Which is a security digest

The first 2 header will be explained in this post, while the Authorization will be explained in part 2.

The URL

First the easy part. It is possible to use either HTTP or HTTPS, else it is more or less straight forward.

The x-ms-date header field

This field is used by Azure for validation and authorization. A valid request must be maximum 15 minutes old, and it must not be dated in the future. It can be expressed as:

current time =< x-ms-date < current time - 15

Pro tip

As it is close to impossible to be complete time synchronized with Azure, it is recommend to substract a few minutes from current time, when sending request.

One important last ting, Azure only understands time in RFC1123 format and GMT +0

If you have: Thu, 12 Feb 2015 21:16:45 UTC in a +1 time zone

It must converted to: Thu, 12 Feb 2015 20:16:45 GMT

The x-ms-version header field (Pro tip)

This an optional field, but you should prefer to set it, or else you will hit an earlier version of the Azure Storage API. you might experience challenges with JSON in table storage or with Shared Access Keys if not using the latest version.

The versions are defined as date, The date which the API version is released. I'm not sure wether this a good solution, because I find dates hard to remember after a while compared to versions. So I have to look up once in a while here: https://msdn.microsoft.com/en-us/library/azure/dd894041.aspx

Introducing GoHaveAzureStorage

Motivation

In a private project, I wanted to reach Azure Table Storage from some Go applications. My only issue was, I couldn't find a proper Go Azure Table Storage library. So when I got hold on the Azure Table Storage REST API, I decided to do a Go Azure Table Storage lib, and then I decided to do a full Azure Storage lib.

GoHaveAzureStorage

I have used the Microsoft Azure Storage API documentation, as inspiration for the library. With this approach, I hope people who is experienced with programming against Azure will find GoHaveAzureStorage just as easy to use.

A small sample:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

package main import ( "fmt" "gohaveazurestorage" ) //Either the primary or the secondary key found on Azure Portal var key = "PrimaryOrSecondaryKey" //Storage account name var account = "Account" func main() { // Create an instance of the lib goHaveAzureStorage := gohaveazurestorage.New(account, key) // From the lib instace, we can create multiple client instances tableStorage := goHaveAzureStorage.TableStorage() //Creating a table httpStatusCode := tableStorage.CreateTable("Table") if httpStatusCode != 201 { fmt.Println("Create table error") } }

For documentation and progress of the project:
https://github.com/ChristianHenrikReich/gohaveazurestorage

Go: Import cycle not allowed

Level: 1 where 1 is noob and 5 is totally awesome
System: Go

Among the programming languages I use, Go is one of my favorites. Intentionally Go lacks features such as generics, inheritance and some others features, which people might see as standards in a programming language. Some might see this as weaknesses in the language, and some might see it as strengths. In the end, the purpose is the keep the language simple, and to build software with less complex code.

A thing which can be challenging in Go, is object parent-child relations, where a child knows it's parent. Like this code below tries, and fails with an 'Import cycle not allowed' error:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

package child import "Parent" type Child struct { parent *Parent } func (child *Child) PrintParentMessage() { child.parent.PrintMessage() } func NewChild(parent *Parent) *Child { return &Child{parent: parent } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

package parent import ( "fmt" "child" ) type Parent struct { message string } func (parent *Parent) PrintMessage() { fmt.Println(parent.message) } func (parent *Parent) CreateNewChild() *child.Child { return child.NewChild(parent) } func NewParent() *Parent { return &Parent{message: "Hello World"} }

1 2 3 4 5 6 7 8 9 10 11

package main import ( "parent" ) func main() { p := parent.NewParent() c := p.CreateNewChild() c.PrintParentMessage() }

Cross-refering packages are not allowed in Go. The best thing would be, if Parent could keep track of the children. Then there would be no issues, and some might argue the code would be more clean. But the world is not perfect, and sometimes circumstances enforces design decisions like this.

To make things work, we can use the nice duck typing feature(interfaces) which Go supports. In general, it is good practice to use interfaces. It keeps the code de-couplet and flexible.

So if we make the interface IParent and use it, then everything works out:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

package child type IParent interface { PrintMessage() } type Child struct { parent IParent } func (child *Child) PrintParentMessage() { child.parent.PrintMessage() } func NewChild(parent IParent) *Child { return &Child{parent: parent } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

package parent import ( "fmt" "child" ) type Parent struct { message string } func (parent *Parent) PrintMessage() { fmt.Println(parent.message) } func (parent *Parent) CreateNewChild() *child.Child { return child.NewChild(child.IParent(parent)) } func NewParent() *Parent { return &Parent{message: "Hello World"} }

1 2 3 4 5 6 7 8 9 10 11

package main import ( "parent" ) func main() { p := parent.NewParent() c := p.CreateNewChild() c.PrintParentMessage() }

Now 'Hello World' is written to output, as intended. Of course, the interface can be placed in a 3rd package. As always, it depends...

Balancing resources in SQL Server

Level: 3 where 1 is noob and 5 is totally awesome
System: SQL Server

As software developer I believe in decoupling, and I'm certain that decoupling is just as important in database design as well. I really like having things in minimum chunks, and keeping them independent as possible. Independence and decoupling equals scalability. This post is based on a professional experience, which supports my belief.

At my work we are developing a billing platform, and this platform has several clients. Each client use our platform to bill a certain amount of customers. So this set the scene.

Some time ago, we had one database for each client in one instance, which is a easy pattern. But in SQL Server it can easily be an expensive anti-pattern, and it is certainly a pattern which is my last choice. The problem with this pattern, it is only effective, when the databases is equal regarding size and usage.

The buffer pool

The explanation is, there is only one buffer pool pr. instance. SQL Server is an in-memory database, but in most cases, there is not enough memory for a whole database in the memory. And if there is more databases in an instance, then it is almost certain there no room in the memory for all the bases. To solve this issue, SQL server has the buffer pool, which is a cache. When querying, SQL Server will get the result from the buffer pool. If it not available in the buffer pool, it reads from the storage to the buffer pool. If there if no room in the buffer pool, it will flush some of the buffer pool, to make room for the requested data. Everything has to go through the buffer pool.

The process of getting data from storage to the bufferpool, generates PAGEIOLATCH waits. A high count of PAGEIOLATCH waits can be a sign of much data is being loaded from disk to the buffer pool.

Let us say, we have 3 databases in one instance. They respectively supports 25.000, 150.000 and 300.000 customers. The sizes of the databases reflects the number of customer, because more customers generates more data. What will happen, is the database with the most usage, will push out the data from the other bases, out of the buffer pool. This will give the lesser bases more read from the disk, producing more PAGEIOLATCH wait, adding more latency to the lesser bases. Actually, the smallest databases will suffer the most.

The poor solution  

I'm tempted to call this, the poormans solution, but it can be very expensive. The solution is to extend the buffer pool, with more memory. But there is a physical and economical limit to this. SQL Server 2014 has a new feature called buffer pool extensions, where it is possible to extends the buffer with storage(preferable SSD drives). I guess extending the buffer pool, will only cache more of the database with most uses. Besides, we have tried this feature, and have yet to see some great results. I would really like to hear, if some has used this feature with success.

The better solution

A easy way to balance databases resourcing over an instance, is by using the resource governor feature in sql server. It works excellent, but it is really a doubled edged sword. Used wrong it can really downgrade performance. It would also require some nursing, which I prefer to keep to a minimum.

The best solution

I find the best solution is to have more instances. If I have 3 clients, I will assign an instance to each, make it thier domain. Each instance, should have hard assigned a certain amount of memory, and their databases would never be able to inflict each others performance. 

This was the solution we did end up with, and it gave us better performance overall.