How to monitor your SQL Azure databases sizes

The Web edition of SQL Azure has a hard maximum limit of 5GB, once you hit that limit the server will stop accepting any writes and your application may well go down. It’s important to make sure you have an understanding of what sizes your databases are and how fast they are growing.

At NewOrbit we needed a way to view all our 80+ databases many of which many are dynamically created across multiple subscriptions. Unsurprisingly, it was Powershell to the rescue. There are plenty of weird things about Powershell, but it’s ability to work with XML is fantastic.

So first let’s get our configuration information so we can connect to Azure

	<Data>
	<Subscription>
	<Name>Company1</Name>
	<SubscriptionId>F412A38C-7C34-4763-83E9-001F3DA1E470</SubscriptionId>
	<Thumbprint>4FDS5A6DF1S56D1SA56D1SAD56SA1DS5A6D0S565</Thumbprint>
	<Cert>cert:\CurrentUser\My\</Cert>
	</Subscription>
	<Subscription>
	<Name>Company2</Name>
	<SubscriptionId>FB0BF5DD-F187-4A31-92CD-34DCAE1CFBD7</SubscriptionId>
	<Thumbprint>2DE3442SDSADF5651518161SDA156D1SA56DS0</Thumbprint>
	<Cert>cert:\CurrentUser\My\</Cert>
	</Subscription>
	</Data>

view raw

gistfile1.xml

hosted with ❤ by GitHub

Next, lets loop through our data setting the azure subscriptions. Powershell lets us just use a . notation to walk through nodes. We grab the data for our subscriptions and set our subscription using Set-AzureSubscription. This means that you will need to have the certificate installed on the machine that you are running the script from.

	[xml]$data = Get-Content -path "C:\temp\Subscription.xml"
	$Subs = $data.Data.Subscription
	foreach($Sub in $Subs)
	{
	$thumbprint = $Sub.Thumbprint
	$myCert = Get-Item ($Sub.Cert+$thumbprint)
	$subID = $sub.SubscriptionId
	Set-AzureSubscription -SubscriptionName $Sub.Name -SubscriptionId $subID -Certificate $myCert
	Select-AzureSubscription -SubscriptionName $Sub.Name
	$Servers = Get-AzureSqlDatabaseServer
	foreach($Server in $Servers)
	{
	getServerData $Server $Sub.Name
	}
	}

view raw

Azure Subscription Loop

hosted with ❤ by GitHub

And finally create a function which connects to the Azure Server and loops through the databases (except Master) calculating the percentage of the capacity used

	function GetServerData($Server,$Name)
	{
	$Databases = Get-AzureSqlDatabase -ServerName $Server.ServerName
	foreach($db in $Databases)
	{
	if($db.Name -ne "Master"){
	$PercentUsed = ($db.SizeMB / ($db.MaxSizeGb * 1024)) * 100
	}
	}
	}

view raw

Sql Server loop

hosted with ❤ by GitHub

Once you have access to the databases, there is plenty more you can do including check the database type (Web or Business) and even increasing the size and type  automatically using Set-AzureSqlDatabase

Next we will look at using Powershell dynamic objects to sort and export our data so we can find which databases are closest to the maximum capacity

 

 

Syncing Outlook 2010 and Google Calendar

I use Outlook 2010 at work and Google Calendar at home and on my phone and found after upgrading from Outlook 2007 that the sync tool that Google have only works with Outlook 2003/2007.  I decided to write my own Outlook 2010 VSTO add-in which will sync my entries every hour for the past couple month and three months in the future.

I have uploaded the inital code to github and you can find more details and get the source here http://github.com/MrKevHunter/Outlook2010GoogleCalendarSync

The add in allows you to specify your account details and the number of months of historical and future appointments to sync. When the add in loads for the first time you will be prompted by a settings box



After entering your details and clicking Save your details we be kept in the user.info file with the password encrypted. The add in will then sync your Calendar’s every time you login. I have plans to make it much less garish when I get chance.

You can make the application sync at any time by pressing the sync icon, and change the settings by clicking the settings icon

The code is pretty rough at the moment without enough error handling or unit tests, but that will change when I get time. My testing has found the synchronisation to work okay so far but there is no real logic on matching moved entries (yet), but as we know version 1 sucks anyway

Please don’t hold me responsible if it deletes all your entries, creates crazy new appointments or wipes your hard disk and if you find it useful let me know. Features and problems can be logged on github or in the comments here. I plan to write a bit more about how I wrote it and some of the technologies used (StructureMap, MSpec) when I get chance.

How to query your iTunes library

As much as I hate iTunes its pretty much a necessary evil if you have a iPod or iPhone. One of the biggest problems I have is when a track is moved, there is no way to automatically locate the track, its just marked as missing and there is not much you can do about it other than locate it manually.

I had a browse today and came across a suggestion on Digg for using the WSH and the COM components to find tracks in the library and delete them. I decided to port this over to c# so we could really query the library. Here’s how I did it.

In a Visual Studio project, add a reference to the iTunes type library then create a class and add a method with the following code

public IEnumerable<IITFileOrCDTrack> GetITunesTracks()
{
    foreach (IITFileOrCDTrack track in new iTunesAppClass().LibraryPlaylist.Tracks)
    {
        if (track.Kind == ITTrackKind.ITTrackKindFile)
        {
            yield return track;
        }
    }
}

we now have a query object holding the entire media library. This gives us the ability to find all the tracks in the iTunes library which are missing by writing a simple method such as

public IEnumerable<IITFileOrCDTrack> GetITunesTracksWithoutALocation()
{
    return from track in GetITunesTracks()
    where string.IsNullOrEmpty(track.Location)
    select track;
}

the track object has a delete method which means we can just call delete if we wanted to remove all the missing items from your media library.

This is a quick and easy way to delete lost items, but could also be used for deleting podcasts older than a certain period of time, and probably many other things, hope you find it useful. I can make the source available if anyone needs it.

A easier way to create stored procedures from a schema

Unfortunately, we don’t always get complete autonomy when writing software, the most typical example of this is having to use stored procedures for all data access. I am not going to bother with the whole debate about performance and security it’s a simple fact that many of us have to use sprocs at work despite our personal preference.

We used to use CSLA ( I just shuddered while I typed that) for our business objects but we are now evaluating other options, on a recent project I spent some time using stored procedures and LINQ2SQL to create a winforms solution and was pleasantly surprised by how easily the two co-existed. After a false start regarding relationships between entities we got the solution up and running completely using sprocs for all data access while still being able to use the built in LINQ2SQL Unit of work.

For the stored procedures however, I had forgotten how tedious it was to write simple data access code which could easily be generated, I evaluated a few different options (T4 templates etc) then chose to roll my own not because I suffer from Not Invented Here syndrome but as an excuse to learn some new technology, which segues us onto a new open source project i create Ubersprogen. while this is  possibly the stupidest names open source project in history. I needed a way of generating CRUD and select by primary key and foreign key which i could then use for my dbml file in linq, giving me much of the power of Linq2Sql while still keeping our DBA’s happy.

While its not going to change the world it has made my life easier and if your in a similar position, possibly yours too.

The elevator pitch

Ubersprogen will generate all the CRUD sprocs you will usually need from Sql Server 2005/2008  including dropping existing procs and headers if required. This is done using a templating engine called nvelocity from the castle project. This allows you to change the layout and logic (to a certain extent) of the sprocs as and when you need. It should be able to ensure the the sprocs follow your companies coding standards too.

A bit more detail

I used WPF for writing the front end, then LINQ2SQL for the data access and nvelocity for the templates, its not finished, but it is in a position where it will generate stored procedures from a db, so it seems about the right time to announce it to the world. It pretty much my attempts at WPF and I have attempted to implement MV-VM so there may well be some horrid anti patterns in there. I would hope that everyone should be able to make it work easily. The UI consists of one form.

Ubersprogen Ui

As you can probably gather it allows you to select which procs to generate, how many files to generate, whether to use NoCount and what isolation level. the gold key depicts a Primary Key and the silver key a Foreign Key.

you can download the binaries here and a link to the source is here

If you have any other ideas of functionality, please leave a comment. If you think its crap, let me know too!

The belated new year post

It’s time to try and get my blogging back on track, after a reasonably strong start ( I thought) it’s certainly tapered off in the last couple of months so lets start now by setting some late new years resolutions.

so here we go…

1. Blog more, realistically I don’t have as much time as I would like to keep this blog up to date, but I aim to make a post at least once a month.
2. Learn a scripting language, I think its time to learn powershell, although the syntax frankly looks completely mental although I thought that about regex…
3. Contribute – I have started an open source project ubersprogen which I am regretting giving such a stupid name but it’s letting me learn much more new stuff, more about it in a later post.
4. Read some technical books cover to cover. For too long i have been dipping  into books rather than reading them all. This one may be tough
5. Get more comfortable with WPF. From what I have seen so far I like although the curve is steep

I think that should be enough to keep me busy, although if i think of any more I will add them.

As a .Net developer what else should I be learning?

Finding the best target for refactoring

We are doing a lot of refactoring at the moment modifying systems to do with the PCI requirements of credit card data storage. Part of my job has been changing existing systems to comply with the requirements and we have taken advantage of this to make some tidy up some of the code base. The two ways I have been choosing my targets had been to use NDepend to find its targets using CQL (More about that in another post) and just simply looking at the number of using statements in the file.

Picking  the classes with the most using statements  is a useful way to find the best targets for refactoring.

Getting bitten by enums

One of the issues I have had to fix recently had me scratching my head, and promising myself be more careful when using enums in the future.

For the purposes of this example assume you have an employee class you may decide to store the gender an enum, it will restrict the choices the other developers have when creating or modifying an instance and provides intellisense. When you request an Employee object from your service layer you can then simply check the value of the enums to get the value.

In a nutshell the problem I had was that the information in the database was correct but was not setting the value of the enum. I spent a day of so working through the various layers of the application trying to find what was overwriting the value of the variable until I came to the realisation that the enum had in fact never been set.

What this led to was the system working as expected for a majority of the time and catching us out in a big way. In smaller systems this may not be an issue but when you have lots of layers in your application (too many perhaps?) its worth checking.

Creating Fake Credit Card Numbers in c#

I recently had to create a credit card number generator, nothing nefarious, just we needed unique valid credit card numbers after asking on StackOverflow and getting a less than stellar response (for the first time). I came across something on DarkCoding.Net which did exactly what I wanted but was in Java, I’ve ported it over to c# and I hope you find it useful

Update

I recently rewrote this code. removing a couple of bugs and making it a bit more c# and bit less Java

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;

namespace CreditCardNumberGenerator
{
	public class RandomCreditCardNumberGenerator
	{
		/*This is a port of the port of of the Javascript credit card number generator now in C#
		* by Kev Hunter https://kevhunter.wordpress.com
		* See the license below. Obviously, this is not a Javascript credit card number
		 generator. However, The following class is a port of a Javascript credit card
		 number generator.
		 @author robweber
		 Javascript credit card number generator Copyright (C) 2006 Graham King
		 graham@darkcoding.net
		 This program is free software; you can redistribute it and/or modify it
		 under the terms of the GNU General Public License as published by the
		 Free Software Foundation; either version 2 of the License, or (at your
		 option) any later version.
		 This program is distributed in the hope that it will be useful, but
		 WITHOUT ANY WARRANTY; without even the implied warranty of
		 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
		 Public License for more details.

		 You should have received a copy of the GNU General Public License along
		 with this program; if not, write to the Free Software Foundation, Inc.,
		 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
		 www.darkcoding.net
		*/


		public static string[] AMEX_PREFIX_LIST = new[] {"34", "37"};


		public static string[] DINERS_PREFIX_LIST = new[]
		                                            	{
		                                            		"300",
		                                            		"301", "302", "303", "36", "38"
		                                            	};


		public static string[] DISCOVER_PREFIX_LIST = new[] {"6011"};


		public static string[] ENROUTE_PREFIX_LIST = new[]
		                                             	{
		                                             		"2014",
		                                             		"2149"
		                                             	};


		public static string[] JCB_15_PREFIX_LIST = new[]
		                                            	{
		                                            		"2100",
		                                            		"1800"
		                                            	};


		public static string[] JCB_16_PREFIX_LIST = new[]
		                                            	{
		                                            		"3088",
		                                            		"3096", "3112", "3158", "3337", "3528"
		                                            	};


		public static string[] MASTERCARD_PREFIX_LIST = new[]
		                                                	{
		                                                		"51",
		                                                		"52", "53", "54", "55"
		                                                	};


		public static string[] VISA_PREFIX_LIST = new[]
		                                          	{
		                                          		"4539",
		                                          		"4556", "4916", "4532", "4929", "40240071", "4485", "4716", "4"
		                                          	};


		public static string[] VOYAGER_PREFIX_LIST = new[] {"8699"};

		/*
      'prefix' is the start of the 	CC number as a string, any number
		private of digits	'length' is the length of the CC number to generate.
	 * Typically 13 or	16
		*/
		private static string CreateFakeCreditCardNumber(string prefix, int length)
		{
			string ccnumber = prefix;

			while (ccnumber.Length < (length - 1))
			{
				double rnd = (new Random().NextDouble()*1.0f - 0f);

				ccnumber += Math.Floor(rnd*10);

				//sleep so we get a different seed

				Thread.Sleep(20);
			}


			// reverse number and convert to int
         var reversedCCnumberstring = ccnumber.ToCharArray().Reverse();

			var reversedCCnumberList = reversedCCnumberstring.Select(c => Convert.ToInt32(c.ToString()));

			// calculate sum

			int sum = 0;
			int pos = 0;
			int[] reversedCCnumber = reversedCCnumberList.ToArray();

			while (pos < length - 1)
			{
				int odd = reversedCCnumber[pos]*2;

				if (odd > 9)
					odd -= 9;

				sum += odd;

				if (pos != (length - 2))
					sum += reversedCCnumber[pos + 1];

				pos += 2;
			}

			// calculate check digit
			int checkdigit =
				Convert.ToInt32((Math.Floor((decimal) sum/10) + 1)*10 - sum)%10;

			ccnumber += checkdigit;

			return ccnumber;
		}


		public static IEnumerable<string> GetCreditCardNumbers(string[] prefixList, int length,
		                                          int howMany)
		{
			var result = new Stack<string>();

			for (int i = 0; i < howMany; i++)
			{
				int randomPrefix = new Random().Next(0, prefixList.Length - 1);
	
				if(randomPrefix>1)
				{
					randomPrefix--;
				}

				string ccnumber = prefixList[randomPrefix];

				result.Push(CreateFakeCreditCardNumber(ccnumber, length));
			}

			return result;
		}


		public static IEnumerable<string> GenerateMasterCardNumbers(int howMany)
		{
			return GetCreditCardNumbers(MASTERCARD_PREFIX_LIST, 16, howMany);
		}


		public static string GenerateMasterCardNumber()
		{
			return GetCreditCardNumbers(MASTERCARD_PREFIX_LIST, 16, 1).First();
		}

		public static bool IsValidCreditCardNumber(string creditCardNumber)
		{
			try
			{
				var reversedNumber = creditCardNumber.ToCharArray().Reverse();
				
				int mod10Count = 0;
				for (int i = 0; i < reversedNumber.Count(); i++)
				{
					int augend = Convert.ToInt32(reversedNumber.ElementAt(i).ToString());

					if (((i + 1)%2) == 0)
					{
						string productstring = (augend*2).ToString();
						augend = 0;
						for (int j = 0; j < productstring.Length; j++)
						{
							augend += Convert.ToInt32(productstring.ElementAt(j).ToString());
						}
					}
					mod10Count += augend;
				}

				if ((mod10Count%10) == 0)
				{
					return true;
				}
			}
			catch
			{
				return false;
			}
			return false;
		}
	}
}

The Joys Of Interviews

In last couple of weeks we have been interviewing new contractor candidates. The standard has been pretty good with a few notable exceptions;

1. If you say you find multi-threading easy yet do not know what the lock statement does I tend not to believe you are any good at it.
2. If you have six years experience in c# and describe your competence as excellent, you really should know the difference between abstract and sealed.

People who need to revise for a simple OO test make me shudder.

Hiring again

We’re hiring again at work, getting some contractors in to help us with the deliverables in the next three to six months. From the last time we were interviewing the standard seems to be significantly higher, which I imagine is due to not as many companies taking on contractors. I have heard about people despairing over the standard of .Net developers but I have noticed that many more seem to have an understanding of mocking, testing and the SOLID principles than even a year ago.

So many CV’s came in we changed our hiring process and asked sent the applicants a single method to improve maintainability and email us the results, the standard was pretty high (although the original code was really bad) but I think it will give us a good starting point to discuss with the candidates we get in for interview.

As someone involved in the hiring process we can now afford to be more selective, I have found with our current hiring process we have a reasonably simple multiple choice OO which as perhaps a little too much trivia and a very simple practical which I don’t think that this is enough to really understand how good the candidate is, I would rather that they thought more rationally about design and maintainability than knew their way around SqlCommand and the SqlDataReader.

For any candidate we interview, they should understand unit testing, IOC, separation of concerns, know why large classes and methods are wrong and expect to write code in the interview, not being able to write code under pressure is not a reasonable response. We won’t give them FizzBuzz but might ask them to solve a problem on the whiteboard.

Lastly, if I am interviewing you, one last piece of advice, don’t suggest that I use DataSet’s in my Domain Objects we have enough of that sort of architecture here already!

What’s your interview process and has it changed over the last couple of years?