Interconnectivity

[Tech]
So I'm on this project to move a (big!!) database to a new home. Upgrade the hardware and RDBMS too, but mainly split up into a more logically planned set of databases (plural), instead of schemas all within one database.
Sounds easy, doesn't it - especially to data-ninjas like all you out there reading this.
Yeah, well, not so much.
The thing that everyone looks at is size - if it's huge (tens of terabytes or more, say) then it must be difficult.
But that's not really so. It's kinda like logistics - once you've done it once then you know how, so doing it again is easy ... it's the organising of doing in 10 billion times at the same time that takes skill.
In this case it isn't the size of the data that makes things hard - it would be as hard with 1 MB or 1 GB or 1 TB or 1 EB of data - but it's the interconnectivity between objects that's the killer.
Not when you move something in a lump - that's when the size becomes a pain - but when you move things relative to each other - that's when things get really awkward!
You see, if you have two things sitting next to each other, so to speak, is (let's say) Schema_a, then one can refer to the other directly by just the name - like this:
select Field_a, Field_b from Table_a
If you move Table_a to another Schema (Schema_b, say), then you need to say
select Field_a, Field_b from Schema_b.Table_a
to distinguish it from the version that stayed home, back in Schema_a. Then you take it to another database in the same instance
select Field_a, Field_b from Database_b.Schema_b_Table_a

and then move it off to another server .....

select Field_a, Field_b from Server_b.Database_b.Schema_b_Table_a

All this seems very simple - and it is. However, there's a certain amount of work involved to change from the first to the fourth example:
1. Find where the external reference is
2. Open the code for editing
3. Edit the code (correctly!)
4. Save and close the code (compiling it as needed)
Which also is simple, albeit taking a little longer than just realising that it needs to be done.

In fact, the difficult pieces are not the ones in that list above, but these:
1. Locating all the places where the code will have to be changed, missing none.
2. Doing it all within the inevitably unrealistic time-constraints imposed on you by people who agreed to the time scheduling without knowing what had to be done.

So the first thing you do is write some code. This code finds every piece of code that refers to any of the objects that's going to move. That could easily be just a few, but if you're working with a database that's grown slowly over the last ten years or so, it's quite possible to number in the thousands.

Then the number of connections ........... what you would call the interconnectivity. Just how many of the queries in your code depend on information that's suddenly going to be in a new place? Will it be hundreds (lucky you!), or will it be tens of thousands (unlucky you!).

Either way, upping your Courage Vitamin might be good .

Good luck!

I'm counting out time, hoping it goes like I planned it

Title courtesy of Genesis ("The Lamb Lies Down On Broadway").
[Tech Stuff]
A somewhat strange title, you might think, but I just had the task of re-writing a couple of T-SQL functions that calculate times. Numbers of days in an interval, to be more precise. Someone had either written or copied a solution that was kind-of working; I needed the real right answers.

Here's the version for calculating weekdays inclusive between two dates. The "inclusive" means that if you're asking "how many days from Tuesday to Thursday" you really mean "how many days work from the instant that Tuesday begins to the very last moment in Thursday - all 72 hours of it. It may be a little difficult to read - use [Ctrl] & [+] to make it bigger in your browser - but I didn't want to get lots of line-wraps.


ALTER function [dbo].[fnCalcWeekdaysInPeriod]
( 
@daStartDate date, 
@daEndDate date 
) 
returns int 
as
/*
select dbo.fnCalcWeekdaysInPeriod('9/29/2012', '9/30/2012')
*/
Begin 
-- ---------------------------------
if @daStartDate > @daEndDate return 0
declare @intDays int, @intWeeks int, @intResDays int
-- ---------------------------------
-- We're only interested in Mon - through -  Fri, 
-- so adjust boundary weekends out of the calculation
-- Push the start date on two for Sat, one for Sun.
-- Pull the end date back two for Sun, one for Sat.
-- ---------------------------------
if (datepart(dw, @daStartDate) = 7) set @daStartDate = dateadd(d, 2, @daStartDate)
if (datepart(dw, @daStartDate) = 1) set @daStartDate = dateadd(d, 1, @daStartDate)
if (datepart(dw, @daEndDate) = 1) set @daEndDate = dateadd(d, -2, @daEndDate)
if (datepart(dw, @daEndDate) = 7)  set @daEndDate = dateadd(d, -1, @daEndDate)
-- ---------------------------------
-- Get the days  --  plus one to make the count inclusive.
-- ---------------------------------
set @intDays = datediff(d, @daStartDate, @daEndDate) + 1
-- ---------------------------------
-- If negative then start date has met end date - which shouldn't happen unless they're very close
-- ---------------------------------
if @intDays < 1 set @intDays = 0
-- ---------------------------------
-- Find out how many weekends there are between the dates
-- ---------------------------------
set @intWeeks = cast((cast(@intDays as float) / 7) as int)
-- ---------------------------------
-- Subtract 2 for every internal weekend
-- ---------------------------------
set @intResDays = @intDays - (@intWeeks * 2)
-- ---------------------------------
return abs(@intResDays)
End

If anyone can find a problem with it then I'd be happy to buy the first few people a beer each! The next post will be for the same thing but for work days - i.e. the days people work after taking company holidays off. It depends on this, though, so this needs to be correct!

--------------------------------

On a more personal note, my cousin Edward now has a berth on the Queen Mary II. Congratulations and best wishes to him!

Go here for information on the ship.

Go here for booking information (shameless plug!).

--------------------------

[Food]
My darling wife and I collaborated on dinner last night. This isn't a cause for a blog in and of itself, but it turned out more successfully than we'd expected. It was a very simple meal - chicken breast, couscous, and some sauce, with a few veggies - but it turned out to be very nice indeed, and pretty low-effort.

Ingredients

Two (or more!) chicken breasts	Chicken broth
Dried Basil and Bay Leaf, crushed up	Salt and Pepper
Spring Onion, chopped small	Celery, chopped fairly small
CousCous	Cream Cheese
Roast Garlic Jelly	Butter

Instructions
1. Clean the chicken breasts well, trimming off any fat you don't want to serve.
2. Lay each breast on a piece of aluminium foil big enough to fold up loosely around the whole breast.
3. Turn up the foil around the edges  and pour in some broth, enough  to reach about a centimetre
    up the breast.
4. Strew the breast with the herbs.
5. Fold up the foil over the breasts to close them in, put them all on a baking tray, and bake
    for 35-40 minutes at between 380F and 400F.
6. For the sauce, take about 10 oz of cheese, about the same of the roast garlic jelly, and some
    chicken broth to help thin it out. Put it all into a saucepan and whisk gently to mix while heating through.
7. For the couscous, heat some butter (maybe a quarter of a stick) in a pot, then pour in about 3/4 cup of
    courcous per person. Stir while still heating, so that the couscous is a little fried in butter. When you can
    hear the mixture start to sizzle a little, add a cup of chicken broth and stir well. Stir in some more celery
    and spring onion, and any aromatics that catch your eye - such as a little Rosemary or Thyme for
    flavouring to your taste.

Eat well!

TTFN

--------------------------