Archive | January, 2012

Please tell me I’m wrong

Go on. Please? I know noone likes to be shown they’re wrong. But being told you’ve got something wrong is great motivator to try and find out if you are. The quickest way to spur someone into really researching a subject than to point out a purported flaw in there knowlege. And it feels great when you find out you’re right, and even if you’re wrong, you know now the right answer.

This can be one of the best parts of a training course. Get 2 DBAs on a course (this is especially good if it’s a non Database course) and watch the sparks fly when there’s a difference of opinion on the wording of something. There’ll either be a great raging debate, or a lot of furtive googling while the instructor isn’t watching. Maybe even quoting of famous SQL bloggers or referring to obscure manufacturer briefing documents. Then at the next coffee break there’ll be a conversation about what was actually meant, arguments from experience, pleading that it was how the last version worked, arguing that it depends on OLTP vs OLAP/VLDB vs LBD/Sparc vs Itanium, makes difference if you’re on SAN rather than DAS, ad nauseum, ad infitum.

But the important thing is that one or more person is going to learn something, and that’s the most important thing. The minute you stop learning something you stop progressing as a DBA or IT professional, and you might as well resign yourself to doing the same thing for the next 30 years.

So please, tell me I’m wrong. Ill thank you for it in 20 years…………

 

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Granting developers truncate on Oracle tables

One of those occasional grants that you really wish you could grant. You have a large temporary table that the Devs are using for testing developments, but it’s not in a schema they control. They need to clean it down regularly, but you don’t want them blowing up loads of redo logging by using deletes. So letting them truncate the table sounds like the best idea.

Except in Oracle that’s a grant you can’t grant. As far as Oracle’s concerned truncating a table is the same as dropping a table. So you either need to grant the Developer ‘DROP ANY TABLE’ or ‘DBA’, neither of which are what you want to do as they grant them the ability to drop anything in the instance, or do even more damage.

So, the workaround? Well, create a stored procedure owned by the table owner which truncates the table, and grant

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Backups – where it all starts

Dear DBAs of the world,

We need to talk about backups. Ever wonder why every DBA blog for any RDBMS product bangs on about backups? (ok, you NoSQL guys can shut up at this point).

Because if you’ve got good, and I mean good

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Powershell script to build up restore commands for SQL Server

I often need to restore a standby server up to the latest production restore, either for testing or because I to get it ready for mirroring. As it’s on remote server there’s no easy list to click on in SQL Server Management Studio. So it’s time to look at the directory listing, and then play around with it in a text editor. With transaction logs every 5 minutes just getting close to completing the editing in time is a pain in the proverbial. So deciding to go the easy route (always my preference), here’s a powershell script that’ll build the statements for you:

$dbname=$args[0]
$folder=$args[1]
$start="restore database $dbname from disk='$folder"
$end="' with norecovery"

$holder = dir $folder *.bak
$tholder = dir $folder *.trn
"$start{0}$end" -f $holder[-1].name
$tholder | where {$_.lastwritetime -gt $holder[-1].lastwritetime} | foreach {"$start$_$end"}

Nothing too fancy there. I tend to keep more than a days worth of backups on disk if possible, so it looks in the specified folder for the oldest .bak file, and then lists all the .trn files created since then.

I normally just call it as:

./restore.ps1 ProdDB \\backupserver\backupshare$\proddb_backups > out.txt

If you wanted to be brave you could pipe the output into osql to avoid a cut ‘n’ paste, but I’m not usually that keen. For anything of an interesting size or backup frequency you’ll probably have to run it a couple of times to get up to date.

I’ve one in the works that talks to a SQL instance to get data file locations and the puts in the moves if needed. But it’s not quite ready for public eyes yet

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Matching windows cluster node ID to physical server

So you’ve built your Windows 2008 Cluster, and you did it sensibly with good logical names like

  • cluster-node-01
  • cluster-node-02
  • cluster-node-03

It’s been running quite happily, you’ve extended it and upgraded it. But now you’ve got a problem, a resource won’t start up when it’s moved to a new node. So you’ve followed the instructions to get the last 5 minutes of Cluster logs by running:

C:\>cluster log /gen /span:5

And you’ve found that the error is something to do with:

00000790.0000545c::2012/01/09-11:16:15.778 ERR   [RCM]s_RcmRpcGetResourceState: ERROR_CLUSTER_GROUP_MOVING(5908)' because of ''Cluster Disk 3' is owned by node 4, not 6.'

But wait a second, what’s this node 4 and 6 business? And which node is the one causing the problem?

The simplest way to find out which node is which is to simply drop to a cmd prompt on one of the nodes and:

C:\>cluster node /status
Listing status for all available nodes:

Node           Node ID Status
-------------- ------- ---------------------
cluster-node-04      1 Up
cluster-node-02      4 Up
cluster-node-01      5 Up
cluster-node-03      6 Up

C:\>

And there you have a nice little lookup table for you.

But why aren’t they aren’t the in nice logical order you installed them in? Well, remember that time you had to evict cluster-node-01 because of the main board fault and rebuilt it on new hardware? Or when you upgraded the hardware by adding in a new ‘better’ node and then removed/replaced the nodes 1 by 1.

Every time the Cluster sees a ‘new’ node it assigns it a shiny new Node number. So when you first built the cluster it was a nice correlation. But the good news is that as it’s going to change fairly infrequently it’s pretty easy to keep a track of it.

 

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

When is a disaster not a disaster?

When it’s a disaster recovery test.

The DR test I mentioned previously (Checked your Disaster Recovery plans recently?) didn’t go quite as smoothly as expected. Filestore, load balancing and SQL all fell over to the secondary site as expected with no problems. A quick sp_who2 showed sessions from the apps servers into the correct databases using the correct users, so everything was good then?

Well, all the underlying services looked good, but the web access components weren’t looking healthy. OK that may be an understatement, they were deader than a Norwegian Blue pining for it’s Fjords.

But we had a ‘tested‘ DR plan, so what happened?

Well, it turned out that we hadn’t had a ‘disaster’. Due to other work we didn’t fail the application servers over to the secondary site. So this meant they didn’t get a restart, which was an unwritten assumption in the DR plan.

But the applications were meant to follow the DB connections across weren’t they? And we had connection to the DBs at the secondary site didn’t we? Turns out there’d also been an upgrade that had split the service DB connections from the web DB connections which had previously been tied together. So the connections to the secondary DB were from the services, but the web connections hadn’t failed over as they’d not refreshed themselves. One reboot and we were back up and running, which had we had a full disaster we’d have got a as a freebie.

The upshot of this is that we had

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Checked your Disaster Recovery plans recently?

One of my first jobs in 2012 is to perform some Disaster Recovery testing for a production Service to prove to the Business that it will fail over the way they want it to.

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike