Saving Table Space Quick And Dirty


declare @tables table (name varchar(max), ID int identity(1,1), cnt int, size int)

declare @i int, @count int, @name varchar(max), @sql varchar(max)

insert into @tables

(name)

select

TABLE_SCHEMA + '.' + TABLE_NAME

from INFORMATION_SCHEMA.tables

where TABLE_TYPE='base table'

select @count=count(*) from @tables

set @i=1

while @i<=@count

begin

create table #temp

(

name varchar(max),

rows varchar(max),

reserved varchar(max),

data varchar(max),

index_size varchar(max),

unused varchar(max)

)

select @name=name from @tables where ID=@i

insert into #temp

(

name, rows, reserved, data, index_size, unused

)

exec sp_spaceused @name

update @tables

set size=left(data,len(data)-3),

cnt=rows

from #temp a

cross join @tables b

where b.id=@i

drop table #temp

set @i=@i+1

end

select *,

(size*1.0)/cnt as Ratio

from @tables

where cnt>0

order by (size*1.0)/cnt desc

Less Than Dot – Blog – Saving Table Space Quick And Dirty.

Deploying database migrations in .NET using FluentMigrator, TeamCity and Octopus Deploy

A while back I wanted to setup database migrations on a .NET project I was working on. I had previously been using Roundhouse but I have to be honest, I didn’t like it.

Too much ‘Powershell-foo’ and a reliance on the way you named your scripts, plus it didn’t work flawlessly with a group of developers and source control. And don’t even dare forget to mark your script as ‘Build Action – Content’, because the whole walls of Jericho come tumbling down if you don’t.

I wanted a replacement that worked for me. After a bit of research I came across FluentMigrator.net. At first I couldn’t grok it. I felt a bit like some of the stuff I’d seen in Ruby Migrations demos. I’d also used Subsonic (also has migrations), but there were a were niggly questions I had, namely:

  1. How to work with an existing (mature) database?
  2. How to deploy the migrations to production?
  3. How to manage a dependency on .NetTiers (historical yuck)?

I posted the question on StackOverflow but it never got any love, nor any responses. Anyway, I managed to solve this problem, and this blog post documents my path through to the solution.

Most of my problems were to do with a lack of understanding of how FluentMigrator.net actually work. the Github page outlines what FluentMigrator is quite well:

Fluent Migrator is a migration framework for .NET much like Ruby Migrations. Migrations are a structured way to alter your database schema and are an alternative to creating lots of sql scripts that have to be run manually by every developer involved. Migrations solve the problem of evolving a database schema for multiple databases (for example, the developer’s local database, the test database and the production database). Database schema changes are described in classes written in C# that can be checked into version control.

The wiki is missing a short overview of how it works though. So I’ll have a stab at outlining it here:

FluentMigrator allows developers to create up and down migration scripts using a ‘fluent’ interface in C#, which is a language most C# developers are familiar with! Most basic SQL commands, such as those to create or update schema are supported. Example would be the creation or alteration of a table, or adding an index, or deleting a foreign key. It supports more complex schema and data changes through embedded or inline scripts. The NuGet package includes an executable called migrate.exe, which runs against your compiled assembly. It scans through your assembly for scripts to run, orders them by the migration id, checks which ones have already been run in that database (it looks at a table in the database to see which ones have already run) and then runs each migration in turn until that database is upgraded or downgraded to the correct version (as required). Migrate.exe takes a number of command line parameters, which allow you to set things like the database connection string and the assembly to run against.

I’ll pick out my questions from Stackoverflow and answer them myself one-by-one.

What is the right way to import an existing database schema?

I couldn’t find a ‘right way’, but I did find a way that worked for me! I made the following decisions:

  1. I setup my first ‘iteration’ as an empty database. The reasoning for this is that I can always migrate back down to nothing.
  2. I scripted off the entire database as a baseline. I included all tables, procs, constraints, views, indexes, etc. I setup my first iteration as that baseline. I chose the CREATE option without DROP. This will be my migration up.
  3. I ran the same script dump but choose DROP only. This will be my migration down.

The baseline migration just has to use the `EmbeddedScript` method to execute the attached script (I organised the scripts into iteration folders as well).

[Tags(Environments.DEV, Environments.TIERS, Environments.CI, Environments.TEST)]
[Migration(201403061552)]
public class Baseline : Migration
{
   public override void Up()
   {
     this.Execute.EmbeddedScript("BaselineUp.sql");
   }

   public override void Down()
   {
     this.Execute.EmbeddedScript("BaselineDown.sql");
   }
}

My project now looks like this:

Capture

For each ‘sprint’ (Agile) I create a new iteration. It helps to keep track of which migrations are to be expected in the following or preceding releases.

Baseline database solved…

How to deal with .NetTiers

Ok, this was somewhat of a challenge. I created a specific .NetTiers database which I would use to run the .NetTiers code generation. In FluentMigrator you can ‘tag’ migrations. I decided to tag based on environments. Hence I have a ‘tiers’ tag as well as tags for ‘dev’, ‘test’, ‘uat’, ‘prod’, etc. How these get run will follow later.

When making schema changes I create the migration and use the tag ‘tiers’ to focus on the .NetTiers schema change. I then run migrate.exe out of Visual Studio external tools using that specific tag as a parameter. The app.config database connection that matches my machine name will be the database connection used, so I point it at the tiers database. Now my migrate up has run my .NetTiers source database is ready. I can now run the .NetTiers Codesmith code generation tool to produce the new DLLs.

Note: If you are using a build server such as TeamCity then you can simply check that migration code change in to your VCS and then the build trigger can automatically run the .NetTiers build (you need CodeSmith on the build server though).

You can then replace the current .NetTiers DLLs with the new ones either automtically if you have the build server generating them, or manually if you run Codesmith generator manually.

.NetTiers solved…

What is the right way to deploy migrations to a production environment?

I am using Octopus Deploy and to be perfectly honest, if you are deploying .NET applications, especially to multiple servers, this should be your absolute go-to-tool for doing so!

I won’t go into the details of Octopus Deploy, but at a basic level you can hook TeamCity and Octopus deploy together. OD provide two items to get you going.

  1. A program called Octopack that wraps up your application as a NuGet package.
  2. A TeamCity plugin that makes TeamCity build the NuGet package and offer it as an artifact exposed on a NuGet feed.

Octopus Deploy then consumes that NuGet feed and can deploy those packages to the endpoint servers. Part of this deployment process is running a PreDeploy and PostDeploy Powershell script. In here is where I am going to run the migrate.exe application with my specific tags:

function Main ()
{
   Write-Host ("Running migration " + $OctopusEnvironmentName)
   Invoke-Expression "& `"$OctopusOriginalPackageDirectoryPath\Migrate.exe`" --provider sqlserver2008 --tag $OctopusEnvironmentName --a Database.Migrations.dll"
   Write-host("Migration finished " + $OctopusEnvironmentName)
}

Notably, my `$OctopusEnvironmentName` match my tags. Therefore each environment deployment will match the correct database migration target. You can just run the database migrations project as a step in a OD project. You simply select the `Database.Migrations` project name (which is the name of my project) from the NuGet feed server.

Deployment solved…

RIP Robin Williams

Usually when celebrities die it means little to me, but today’s news concerning the death of Robin Williams was something different. As a child I watched Robin Williams with my Dad. He showed me Mork and Mindy and funny standup scenes, which were probably more adult themed than I should have watched.

I remember the brilliant films he was in, serious ones like Good Morning Vietnam,  Good Will Hunting and Dead Poet Society and the more light humoured ones like Mrs Doubtfire and Patch Adams.

It is so ironic that such a funny man, that made so many people laugh, was so sad inside. I hope his family are left to grieve by themselves without the media interfering.

Thank you for the laughs Mr Williams. You were a genius.

SOLVED – apache2: Could not reliably determine the server’s fully qualified domain name

This has been annoying me for a while. I have a Digital Ocean VPS (droplet) running Ubuntu. This provides me with some email accounts and a couple of WordPress sites.

I’ve been receiving this error for a few weeks since I setup VirtualMin:

AH00558: apache2: Could not reliably determine the server's 
fully qualified domain name, using blah.com. Set the 'ServerName' 
directive globally to suppress this message

N.B. For this demo my VirtualMin installation is installed on panel.virtualmin-domain.com and the machine name is set to panel.

The usual answer to this appeared to be that apache2 needs the appropriate ServerName configured. I checked through each apache2 domain conf file, and all had an appropriate ServerName correctly set in the conf. To check this do the following:

sudo nano /etc/apache2/sites-available/
ls

Your available VirtualMin sites should be listed, i.e.

000-default.conf
default-ssl.conf
domain.com.conf

Each conf file should have the appropriate ServerName entry. For example the 000-default.conf should look like this:

ServerName panel.virtualmin-domain.com

To open and edit the file you can use nano:

sudo nano /etc/apache2/sites-available/000-default.conf

If it doesn’t exist then enter the name and restart apache2:

sudo /etc/init.d/apache2 restart

If you still get the same error (like I did) something else is mis-configured. For me the problem lay in my hosts configuration.

To check your hosts configuration type:

sudo nano /etc/hosts

It should look like this:

127.0.0.1 localhost
0.0.0.0 panel.virtualmin-domain.com panel

Where 0.0.0.0 is your Digital Ocean external IP address. Mine was mis-configured:

127.0.0.1 localhost
127.0.0.1 panel.virtualmin-domain.com panel # this extra line was causing the problem
0.0.0.0 panel.virtualmin-domain.com panel

I removed the offending line, rest the hostname and restarted apache2:

sudo hostname -F /etc/hostname
sudo /etc/init.d/apache2 restart

The result is now nice and error free:

root@panel:/etc/apache2/sites-available# /etc/init.d/apache2 restart
 * Restarting web server apache2                                         [ OK ]

Job done.

Data recovery on a MAC HFS journalled external drive

A friend of mine recently asked me whether I had any experience of data recovery. Her WD Elements external hard drive was no longer mountable and she had years of data stored on it. A data recovery company was asking for 800 EUR to fix it.

After a bit of Googling, I came up with two tools. The first was PhotoRec and the second was TeskDisk. I downloaded the Ultimate Boot CD, which is essentially a bunch of LINUX tools that run off a bootable CD. Both PhotoRec and TeskDisk are available on that disk.

Ultimate Boo CD v5.3 boot up screen

I first ran PhotoRec. and it successfully started to extract her files off the disk. Since PhotoRec was working so well I started to realise that the disk was probably not that corrupted, so I killed the PhotoRec process.

My gut feeling was that the partitions were still there, but the MBR was corrupted, probably because the disk had been pulled out of the machine without being unmounted first.

TestDisk isn’t that obvious but I battled through with it. First you choose to [Create] a new log file.

teskdisk – create new log file

Then you choose the physical disk that you want to work with. I choose based on the disk size since I knew it was 1000GB (i.e. 1TB). It was labelled /dev/sda.

The first thing you have to do is to select a partition type. I knew this disk had been setup on an older Mac, so I choose [EFI GPT].

TeskDisk – Choose a partition type. I chose EFI GPT

I then select to [Analyse]

It found two partitions. One EFI and the other was Mac HFS. You can move between the two partitions. The EFI one had the [P] option to ‘list files’.  The Mac HFS one didn’t. I chose the [Deeper Search] option and it started to scan the drive in more detail.

The process was so slow on this 1TB drive that i left it run overnight. This process ended up listing what seemed like hundreds on Mac HFS partitions. That seemed strange. I then Googled around and realised that the HFS system is “journalled”, meaning that the file system stores versions of your data. Each partition was a slightly different version of the same data. I came across a post that suggested I could use the ‘pdisk’ utility to rebuild all of those partitions, but it meant naming and sizing all of those (what seemed like) hundreds of partitions. The lazy hacker in me said no. There must be a better option.

I researched further and came across Revision of Case Study: Repair mac filesystem. I followed through the [Advanced] section [Superblock] and noted that I also had the error “Sectors are not identical”. I selected [Backup BS], which then seemed to fix the issue.

I then asked my friend to plugin the drive into her Mac. It still didn’t mount, but using the built-in Mac Disk Utility app, it could now see the drive! Result.

However, the volume/partition was greyed out. The right hand panel noted that the volume needed to be repaired. We clicked on repair and the Disk Utility went through a series of checks that were output in the Disk Utility output panel. Finally we had a green light and the disk popped up on the desktop.

It was fixed!

How I stay calm, by people with very stressful jobs

What I do is install underwater gas and oil wells. The whole job involves stress, from getting in a helicopter to fly out to a ship 300km north of Shetland, to getting on the dive ship itself. There’s the pre-saturation medical, and then I go into a 2.5m x 7m chamber for a month. I’ll be in there with 11 other divers, working in teams of three. You go up and down to the sea bed in a submersible decompression chamber, basically a diving bell, that’s lowered to 20m above the sea bed. Then two of you get out of a little hole in the bell and you’re “locked out”, as we call it, for six hours in the pitch black and off to do your work with all sorts of marine life. I’ve been doing this for 20 years. Usually I work one month and then have two months off.

There’s no room for arguments in this environment. You need to be very tolerant of other people because you’re living in such close proximity. You also need to accept the fact that if it goes wrong, you’re probably not going to get out alive. You need to go in with your eyes open. There are deaths. We lost a 32-year-old diver a couple of years ago who had a three-week-old daughter. It happens, so you need to be aware of the risks.

Interesting read. Not a job for me!

Source: http://www.theguardian.com/lifeandstyle/2014/mar/01/how-stay-calm-by-people-stressful-jobs

Jill Catley: https://www.flickr.com/photos/26235325@N05/

The golden rules of encryption for developers

It is easier to provide the list of things that are worth worrying about than it is to list the things that are safe. There are a lot of as-yet unbroken ciphers and constructions. So, here are the things to avoid:

* Block ciphers in the default mode (“ECB”).

* The Dual_EC random number generator, which virtually nobody uses anyways. You weren’t going to accidentally end up using it. Or, for that matter, any other PKRNG (random numbers produced by public key algorithms).

* RSA with 1024 bit moduli (or below); RSA-2048 is your starting point. Conventional DH at similar key sizes will be an issue too, but there’s a “means/motive/opportunity” issue for RSA-1024 given its prevalence.

* MD4, MD5, and SHA1 aren’t backdoored, but are broken or weak. But: all three are survivable in HMAC (don’t use them, though). SHA2 is your best all-around hashing bet right now.

* The NIST P- curves. There’s no evidence to suggest they’re backdoored, but (a) the rationale behind their generation is questionable and (b) they have other annoying properties.

So far as I can tell, you are now fully briefed on the “distrusted” crypto.

Don’t build your own crypto. Use PGP for data at rest, TLS for data in motion, and NaCl for the rare in-between cases.

Source @tptacek: https://news.ycombinator.com/user?id=tptacek

Photo credit: Jill Catley via Flickr.

Ben Powell is Microsoft .NET developer providing innovative solutions to common business to business integration problems. He has worked on projects for companies such as Dell Computer Corp, Visteon, British Gas, BP Amoco and Aviva Plc. He originates from Wales and now lives in Germany. He finds it odd to speak about himself in the third person.