Tuesday, August 19, 2014

The Inevitable

I received this little gem attached to my toll statement.

In the history of man made disasters, things usually tend to be inevitable, if you don't actually do anything about it.

Queensland Government 2014 : Doing nothing about improving public transport to let the congestion happen.

Friday, June 6, 2014

Source controlling your database, with VS2012 and SSDT

Source control for code is awesome, but when it comes to databases, I've never done it full.  Sure, there's dumps to SQL files, and extracts, but it all seems just a bit too manual.

So, I've decided to try my luck at a proper database project for my MS SQL database in Visual Studio 2012.  The MS SQL Server flavour is 2008 R2.

The current situation is 3 databases that I need to apply changes to, at various times.

My first database is the development database, located on a shared server, and connected via Windows Authentication.

The second database is the test database, located on the test server. Whilst I can connect to the machine via a VPN, the database still has Windows Authentication, and physically connecting to the database from my development machine is not an option.

And the third database is the production database. Also using Windows Authentication, and unreachable from my desktop, in any database connectivity sense.

At the time of deciding to have this database project, I was near the end of the development cycle, so pretty much all database changes I wanted to make were already applied.  Though in planning for a new release, I always take a copy of the database from the previous release and rename it for the new release.  So I had an older version to work with.

So I created a new SQL Server Database Project.  Then I imported from the older database.  At this point, I committed the project source to Git, so show the state of the database before all my current release changes have been applied.

Then I performed a Schema Compare with the new release, making sure to set the new database as the source, and the project as the target.  I then did an Update, and committed that to Git as well.  Nothing unusual so far.

The next challenge is to work out what changes I should require to upgrade the test database.  Since the database project also imports security objects, such as logins, I want to make sure none of my development logins make it to the test and production databases.  The database project tools and doco seem geared for when you can connect to the database to do a Publish, but it's not an option in this case.

The next best thing is to jump on the test system, and generate a .dacpac file from the test database, and bring that back to my development machine.  From there, I can (hopefully) perform a Schema Compare and generate an SQL file that I can run on the the test server to get it up to speed.

But no.  That would seem to not be an option for me at the moment.  When I generate a dacpac file from SSMS 2008 R2, and try to use this in a Schema Compare, I get an Origin.xml file missing error. This post suggests that MS might fix the issue, but also says that you need to upgrade to SSMS 2012 to be able to generate v3 of the dacpac, the only version that will work as intended.

Unfortunately, my test system is only running 2008 R2 on Window 2008 Datacenter, and SSMS 2012 requires Windows Server 2008 Datacenter SP1.  On the original post, I have quizzed the answer that suggested a fix to see if they were going to fix SSDT to handle older dacpacs that don't have an Origin.xml, or the less convenient option of forcing everyone to patch their OS being being able to install the latest SSMS.

So, for now, I will just do a Schema Compare between the older release database, the current database, and generate an SQL file that can be run on the test server and production server.  In my manually maintained SQL upgrade scripts, I have some alter statements that introduce not null fields with no default, and also have code to populate those fields.  I think I may have to run my manual scripts anyway, but at least I can extract the Stored Procedure code from the generated SQL to upgrade all of those, instead of manually visiting each one, doing a modify, copy and paste, and making sure permissions are appropriately applied.

Thursday, May 8, 2014

Moving House Sucks

Sometime at the start of 2013, we decided that the house we lived in was no longer suiting our needs, and that we would need to find a bigger place.  So we starting fixing the house up for prepare for sale.

In mid 2013, we still had quite a lot of work to complete on the house, before we felt it would be ready to put on the market and fetch the best price.  My wife also entered the work force, but was working on the other side of the city.  Driving late at night, on the highway, with the young'un was inviting disaster.

So after a year of work on the house, including painting and landscaping, we were finally ready to sell.  All the stuff we really didn't need for a while went into portable storage.  In December 2013, the house went on sale, and a good offer was made from that first weekend.  So we took it, and settled in mid January 2014.

Then the hunt was on to find a place on the north side.  When we settled, we moved. We moved to the in laws.  Not ideal, but was better that finding a short rental.

After a few months of searching, we finally found a place at the right price.  The house was actually one of the first places we saw in January, however the price was out of our league.  We had notices that the estate agents were putting the prices at $50K over what we'd other consider.  How could we tell? Because we'd look at the place, then after two or three weeks of being on the market, it would drop by $50K.  Same too, with the place we ended up getting.  It actually dropped $65K before getting to a price where we could put an offer on it.  Then after negotiations, it worked out that the price was $50K below the original asking price.

Anyway, here we are.  All moved in, but still moving in.  I probably won't consider us fully moved in until there are no more boxes to be unpacked, and both cars can fit in the double garage.  Once that's done, then we can also finishing painting the inside of the house.  It's just gloss enamel skirtings and a top coat for the tiled area, but it feels like "too hard" basket, at the moment, especially with the dog and toddler, who are unlikely to let me get on with it while they're awake.  At least I can put one of them outside or in the sleeping crate.  What to do with the dog? What to do, what to do...

Over the course of the last year, with the house selling and the house buying and the raising of the toddler, this blog has been neglected.  Yep, it's kind of one of those posts.  Not the post were I promising to blog more frequently, just the "hey, still here doing stuff" kind.

We also had a little family loss last year.  Our cockatiel, Missy Moo, or Moo Moo, as we would call her, developed a heart condition that was severely impacting her quality of life.  We had to put her down. It was quite sad. Addison still remembers Moo Moo, and has taken a special liking to the cockatiel at her day care.  She's one of the few, if not the only, toddler that will let the cockatiel sit on her shoulder while she walks around and pets it.

We also had a bit of a scare with the dog soon after.  A few scans and a very expensive vet bill later, the cardiologist determined that Astro had a diaphragm flutter that would present itself in situations of stress.  And that stress was the stress of moving, and living with in laws.  Now he has a doggy door, so he can let himself in and out as he pleases, and he hasn't vomited for quite some time.

So, that's been the year. Back later.