The Heap™ – Consultancy ©®

12:32 AM

I know it's terrible of me, but I just cannot resist things like this. dba.stackexchange.com/questions/13709/…

2:05 AM

That's pretty harsh (at least it comes across that way). Not wrong, just harsh. (That said, I agree)

Also I think I owe someone a beer. I forgot to do the setup for a deployment properly, so if the deployment had gone through I would've completely broken a test environment (as in "restore from a production backup" kind of broken)

The thing that stopped me: a developer missing a comma in a .sql file

Simon Righarts

4:18 AM

Whee, first ever "ran DDL in the wrong test environment" moment

managed to unmunge it in about five minutes, must be some kind of record

Aaron Bertrand

In a former life, I prevented those embarrassments in production using a DDL trigger. When I knew I wanted to make an update to production, I temporarily disabled the trigger. If I fat-fingered it, I got a fun error message from my future self (kind of like Dwight from the future).

Of course DDL triggers are after triggers, not instead of triggers, so they would rollback whatever I did, not prevent it. If it was a create index that took 10 hours, it would probably take at least 10 hours to roll back, too. :-\

Simon Righarts

4:43 AM

What we have here is all releases are managed through a set of shell scripts, and those shell scripts look for server passwords in a specific folder

so as long as we only put production passwords in that folder when we're doing stuff to production, we generally can't break production

I say "generally" because we still have SA rights on our production server accounts - but any significant change will be done as a release

Aaron Bertrand

I wasn't trying to protect from planned things going against the wrong environment. I was protecting from unplanned things where you run an ad hoc query through Management Studio after right-clicking the wrong server registration.

We like to be perfectly planned and accurate all the time, but we're still human.

ErikE

I like to put CREATE DATABASE Trash/GO/USE Trash/GO/ at the top of my long sql files. :)

Thus preventing runaway F5s

as long as the sql file, of course, doesn't have its own USE statements or 3- or 4-part naming :(

But actually, I've been getting more cautious and putting DELETE and UPDATE lines behind single-line comments

and using transactions with the COMMIT in comments, too

Aaron Bertrand

I changed my new query template to have a BEGIN TRANSACTION and then commented rollback/commit lines.

ErikE

5:10 AM

@Aaron: why does your name regularly change back and forth between just first name and first + last? Different names on different stackexchange sites?

gbn

7:35 AM

'morning all

gbn

7:46 AM

VtC? dba.stackexchange.com/questions/13701/…

Need one more: dba.stackexchange.com/questions/12669/…

Ditto: dba.stackexchange.com/questions/12373/…

Ditto: dba.stackexchange.com/questions/11192/…

You can see Qs with votes here: dba.stackexchange.com/review

gbn

8:11 AM

@RickNZ: Hi

RickNZ

hiya

saw your post about my perfcounters question

Simon Righarts

Another kiwi spotted. We'll have enough to launch a takeover of this room soon

gbn

I was looking for an oldl answer of mine somewhere

And found your SF post. The SF mods will migrate it here, so you'll have 2 Qs

That'll be, what, 4 of you?

RickNZ

as long as one of them results in an answer that works, that sounds fine

gbn

@RickNZ The SF one will show us what has already failed :-)

I added what i know too on teh SF one

RickNZ

8:14 AM

ok

gbn

I see your comment now. The migration will show what you've already tried.

RickNZ

@Simon personally, I think I'd prefer a good beer to a takeover

where are you located?

ErikE

@gbn I'll counter your " 'morning" with a " 'night"!

RickNZ

@gbn sure. hopefully the old q won't discourage answers for the new one

gbn

@ErikE lordy. Another Kiwi?

RickNZ

8:17 AM

close to a dupe, I guess

gbn

@RickNZ not here.

We have an infestation

RickNZ

9:17pm here in Nelson

gbn

@RickNZ Exactly 12 hours diff then (Malta)

RickNZ

Ah, Malta. I've had some good ham radio contacts there.

Simon Righarts

@RickNZ they actually have computers in Nelson these days? ;)

(Not that I can really talk, originally from Dunedin, now in Wellington)

gbn

8:32 AM

Just looking at a map of NZ. Dunedin is old gaelic for "arse end of nowhere" IIRC

Simon Righarts

please it's a lovely town

... as long as you don't visit in the middle of winter

(when the 'middle of winter' is defined as from February to December)

gbn

I lived up in Inverness-Aberdeen for 13 years.

I'm not one of these southern shandy drinkers

@RickNZ: migrated dba.stackexchange.com/q/13733/630

Phil

9:47 AM

Greetings fellow Earthlings

RickNZ

@gbn OK

gbn

@Phil 'lo

RickNZ

@SimonRigharts No (good) computers (or jobs) here, but being the sunniest place in NZ makes up for it. I've heard Dunedin is nice, esp. if you like to see cars and other large objects encased in ice. Do you ever see the sun in Wellington?

Simon Righarts

10:03 AM

@RickNZ Oh please it hasn't even snowed in Dunedin for a couple of years. Also ... sunlight? what's that?

Phil

Heh. I went snowboarding in Wanaka a few years ago & it worst the worst skiing season for years :/

gbn

here or programmers? stackoverflow.com/questions/9410588/…

ConcernedOfTunbridgeWells

10:34 AM

@BenBrocka What's @Aarthi been practicing on?

@gbn Muhahahahahahaha

@SimonRigharts You know, the yuppies in their lifestyle blocks have to keep online somehow :)

@gbn Billy Conolly has a great line about Dunedin and Scots. See if you can find a video of his 'World tour of New Zealand series.'

Aaron Bertrand

12:43 PM

@ErikE I'm not sure what you mean. My username is my full name on all sites. Where do you see it as just first name?

gbn

@AaronBertrand On the left

sometimes it shortens

Alex Poole

@gbn Just depends on the size of your message, doesn't it? It the message area isn't tall enough to show the full name and rep it shortens it. Avatar size changes too.

gbn

@AlexPoole I saw what ErikE was on about. The post above his from Aaron shows just Aaron earlier. Whatever.

Alex Poole

Yes, should have replied to ErikE really. Or Aaron.

Aaron Bertrand

1:04 PM

Oh I have no idea, that is not of my doing.

gbn

1:32 PM

@JNK: bollocks. Lost 50 rep with that SO deletion

JNK

sorry!

but you voted too

gbn

@JNK I know. Doh! I never thought about it, even after this long on SO

JNK

i really dont think you'll notice 50 rep

gbn

@JNK I know. I just wondered what happens so checked /reputation

JNK

1:55 PM

i normally gain rep from deleted stuff

since i get back the rep i spent on downvotes

Ben Brocka

2:06 PM

@AlexPoole yea, they expand as you add more lines. Only shortens your name when it's long and there's not enough space--and you have spaces in your name (ahem @ConcernedOfTunbridgeWells)

ConcernedOfTunbridgeWells

@BenBrocka I don't think my ID actually has any spaces in it, although I have seen is shortened here in chat on a few occasions. - maybe the system uses some heuristic to break it on embedded capitals.

Ben Brocka

Oh, sure enough, "ConcernedOfTu"

ConcernedOfTunbridgeWells

@BenBrocka Do you suppose @Aarthi got the job of doing the Vox Populi badges? ;-}

Ben Brocka

Dunno, but they fixed that pretty fast. Was probably just a derpy sproc though

ConcernedOfTunbridgeWells

I imagine so.

Ben Brocka

3:31 PM

FFFFFUUUUUU, why do so many people not get subqueries?

Aarthi

What's a subquery? :P

DTest

it is a query that needs to use public transit

Ben Brocka

Subway sells them for $5

ConcernedOfTunbridgeWells

@Aarthi select a.foo, a.bar from (select foo, bar from xyz) a

The bit in bold is a subquery - obviously it can be more complex than the example, and they come in a couple of different flavours. You can join to subqueries and use them in WHERE IN and WHERE EXISTS type clauses. Correlated subqueries are where you use data from the outer query in the subquery.

e.g. select a.foo, a.bar from xyz a where exists (select 1 from wibble w where w.bar = a.bar and wibble.foo > a.foo)

Note the w.bar = a.bar inheriting data from the parent query.

Ben Brocka

3:50 PM

Our last programmer thought it would be a good idea to run one query, iterate through the results, concatenate them into a string and use that string as an in phrase instead of using a subquery that has the same results

ConcernedOfTunbridgeWells

@Aarthi - any the wiser?

Rachel

4:26 PM

Quick question... does anyone know if this answer will also move other sql objects such as logins, permissions, jobs, clr functions, etc?

9

A: A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

If you cannot take the databases offline, tne you need to do backup/restore. I would suggest the following: Install SQL 2008 on the new box, using the same file structure as the old box for MDF and LDF files. Take backups of all databases on the old box. Restore master from the old box to the n...

Or should I create a new question about that?

My issue is we have ummmm maybe 20 databases on 2 servers, and we want to move the 2 servers to newer, virtualized boxes. A lot of these databases have special permissions, and most are interconnected in some way.

Ben Brocka

Backup should copy all of those things, right?

I know for a fact that Export does NOT copy anything but data and columns

Rachel

And I really don't want to re-write about 50 jobs, 20 logins w/ different permissions on different databases, re-create a handful of CLR functions, etc unless necessary

Ben Brocka

we've had some issues with restoring and permissinos though, when one of our prod databases is restored to the development server, one of my logins keeps getting orphaned

Rachel

Hrrmm I thought backup/restore only restored the databases. I didn't think that included logins, permissions, jobs, etc

JNK

There 2 levels of permissions

There's DB level permissions and server level

a restore will get whatever is at the DB level but NOT at the server level

server level stuff is stored in the master db but you really shouldn't muck about with that one

To see if it's in the DB or server, you can expand the object tree in SSMS:

Database->Security

There's users, roles, schemas, etc

Rachel

4:36 PM

Hrrm so to transfer everything I'd have to move the master database over to the new server? Or would I be better off re-creating the objects?

JNK

do NOT move the master

don't touch it

evar

Rachel

But the top voted answer there says to restore the master db from the old box to the new box

DTest

lol

JNK

do you want EVERYTHING?

Rachel

yes

JNK

4:37 PM

this is onto an empty instance of SQL server?

gbn

@Rachel as long as you run sp_dropserver, sp:addserver

Rachel

We are changing our production sql servers to virtualized instances, so absolutely everything needs to be moved over

JNK

ok

then that is fine

I thought it was a partial xfer thing

gbn

2 caveats:

JNK

I need these 10 DBs and all the security stuff added to an existing server or something similar

gbn

4:38 PM

1. stackoverflow.com/a/755703/27535

2. If you backup/restore msdb then the jobserver is orphaned.

Rachel

I'm not kidding about the # of databases either... we have 41 on one server and 10 on the 2nd one

Sure some of those are test, but over half are production databases >.<

DTest

mixing test and production...?

gbn

@Rachel You can literally copy all MDFs and LDFs if same version (inc and service pack, hotfix) and paths

Rachel

@Gbn Are you saying that sp_dropserver/sp_addserver will fix any naming issues caused by moving the instance to a box with a new name?

gbn

@Rachel yeah, fxies @@SERVERNAME

Rachel

4:41 PM

@DTest Sadly, yes. Originally we only had one sql box

gbn

but jobs get bollixed

trying to find Q about it

Rachel

I really don't want to have to re-create all the jobs, users ,and permissions. There are quite a few different logins/permission sets too

gbn

@Rachel you can restore master to get logins back

and server level permissions

However, for jobs (in msdb database) se this dba.stackexchange.com/questions/12388/…

Last time I did this, it was SQL Server 2000 and I changed system tables.

Ben Brocka

@JNK In my case I think it's a server level object...but only one of my DB users gets orphaned like this, when restoring other databases the security contexts are identical. I always have to run sp_autofix_users, any idea why that would be?

gbn

Not tried since SS2005+

Rachel

4:44 PM

I see, so logins and server permissions are in master, db permissions are with the individual databases, and jobs are in msdb?

gbn

@BenBrocka This login is a SQL login?

@Rachel Yes

JNK

@BenBrocka thats a gbn question methinks

Rachel

I'm pretty sure the login for all jobs is a network login w/ windows authentication, so hopefullly that will transfer smoothly :)

gbn

@Rachel if you port all databases (incl master) it will just come up

DTest

SQL Server folks...is mirroring different than replication? :D

gbn

4:45 PM

@DTest very much so

DTest

neat

Rachel

Thank you so much for your answers :)

gbn

feck all in common kind covers it

Rachel

We're not planning on doing the move for a while, but I'm starting to document stuff that needs done or what we'll want to look out for

Ben Brocka

@gbn yeah

gbn

4:46 PM

@Rachel While you're there, consider this a bare metal DR practice

@BenBrocka SIDs mismatch

Rachel

Do you know what database contains linked server references?

gbn

Check outr CREATE LOGIN

It has a SID switch

@Rachel in master, sys.servers?

Rachel

Oh duh, thanks :)

gbn

@Rachel working from memory there, just checking

yep, msdn.microsoft.com/en-us/library/ms178530.aspx

Rachel

It would make sense :)

gbn

4:48 PM

@Rachel not all of it does :-)

@BenBrocka So can recreate login with same SID. Also can port password as a hash

I used to have a SQL 2k script to extract logins, but not for SS2005+

anyway, ciao ciao folks. end of my working day

Rachel

Lucky, halfway through mine. Although that means lunchtime is near!

gbn

I did start a tad earlier of course, your time

Ben Brocka

Looks like there's no way to ALTER it then?

DTest

2

Q: What are the primary differences between NuoDB and MySQL?

Since NuoDB can be used as a storage engine for MySQL, what are the primary differences between using the NuoDB as a server and using MySQL as the server? In addition, recently I've received a recommendation about this new sql database that looks intended to become a very good solution for more ...

there's 2 Barry MORRIS's

Ben Brocka

One was created today, the other last december...

DTest

5:04 PM

yeah i think this is my fault tbh. I rejected his edit with his signature without checking the comments @JackDouglas made in december to disclose his affiliation

Ben Brocka

Ohh, I get it

But now we don't get double the Barry Morris

DTest

hehe

Jack Douglas

@DTest Thanks for flagging that - it's good Barry has registered with the site

DTest

@JackDouglas I don't dispute that!

also...why does my profile no longer display flag weight :(

Ben Brocka

it doesn't exist anymore, only helpful flags

DTest

5:15 PM

75

Q: What happened to flag weight?

I just noticed that my profile on Programmers no longer shows my flag weight. Instead I see the number of helpful flags. At the same time, the definition for the Marshal badge has been changed to "500 helpful flags". Does that mean there is no more flag weight? All that counts now is the number ...

support flag-weight helpful-flags

Ben Brocka

their pseudologarythmic scale for flag weight was kinda...ridiculous

DTest

just saw :)

lol "Raised 500 helpful flags" is marshal badge

that's going to take a while

also deputy is now 80 flags, and i think i'm well below that. guess they don't take badges away retroactively

Ben Brocka

The badges are actually more lenient than before I think, unless they boosted deputy

marshal used to be more insane because flag weight increases had diminishing returns

DTest

they boosted deputy, not sure about marshall

DTest

5:33 PM

in Fail: Vote to ellipsis, 8 secs ago, by DTest

http://dba.stackexchange.com/questions/13784/recovering-mysql-database-from-files duplicate of http://dba.stackexchange.com/questions/13760/getting-recovered-db-files-to-work-on-rebuilt-server

Ben Brocka

@DTest I thought you guys abandoned that chat room idea

DTest

they probably did, but i'm keeping it alive!

Ben Brocka

best meta tag ever

meta.stackoverflow.com/questions/tagged/conspiracy-theory

DTest

5:48 PM

hah

ErikE

6:05 PM

@gbn I'm not a kiwi. I was going to bed right when you were saying good morning.

DTest

interesting... I flagged dba.stackexchange.com/questions/13753/… to be moved, but it wasn't migrated (and the flag was deemed helpful)...

those crazy moderators! :)

Aaron Bertrand

Do people just randomly up-vote bad questions to strive toward some badge?

I'm seeing more and more bad, poorly worded and unclear questions getting up-voted, even if there are multiple comments seeking more clarity because the question doesn't make sense.

DTest

What, there's no badge for #upvoted bad questions? Dang :(

Aaron Bertrand

Well there are badges for hitting your vote cap and there is a badge for some number of votes and some % of them being on questions. I can't see any other reason for this behavior.

DTest

there actually is the vox populi badge, yeah

the upvotes on questions that have comments requesting clarity baffle me...i can see upvoting a question because it seems good to a person who doesn't know the specific technology being asked

Aaron Bertrand

6:16 PM

Here's the SO example that made me curious, but I see this junk all the time. Who up-voted it, other than a drive-by that didn't even read the question? stackoverflow.com/questions/9418540/…

DTest

haha

some might people see formatting and say 'this guy knows how to use SO, upvote!'

Aaron Bertrand

6:57 PM

The worst part is the guy has asked 58 questions, accepted a little over half, and has never cast a single vote on any question or answer.

Ben Brocka

7:27 PM

@AaronBertrand It seems to me there's a fair chance a negatively voted post will get a pity upvote

Aaron Bertrand

In this case it went from 0 to 1 while the dialog was going on.

And long before I down-voted.

If I had already down-voted, and then it went +1, I would get that.

Ben Brocka

@AaronBertrand help vampire...yet he almost has as much rep as I do on SO =/

SO seems mostly about volume not quality though

Oh well, I threw him another downvote

Aaron Bertrand

Well I wasn't fishing for down-votes, especially now that I edited his question so that it made sense. Just baffled by the initial up-vote.

Aaron Bertrand

8:13 PM

Why do people have the "if it ain't broke, don't fix it" attitude about service packs? Some guy is running 2005 RTM and seeing weird behavior. And is adamant that it has nothing to do with the service pack. Ok, <shrug>

Usually the attitude is the other way around: they would never run an RTM version, and wait for the first service pack before touching it.

Simon Righarts

8:24 PM

Morning gents

DTest

8:43 PM

afternoon simon!

Simon Righarts

Heh. coming up 10AM Friday morning here. thank god it's friday because yesterday was just fuuaaargh

DTest

so...you're from the future?!

Ben Brocka

HE'S A WITCH

Simon Righarts

Future You whispers: "You are going to do something you'll regret terribly"

Mark Storey-Smith

@AaronBertrand Dunning-Kruger at work

Aaron Bertrand

8:58 PM

It's possible, but why wouldn't the clueless also be up-voting both good and bad answers?

I guess up-votes could be for any reason. "Wow, you wrote such a bad question, I spent five minutes trying to figure out what the hell you were talking about. Kudos to you! +1!"

Ben Brocka

I just +1 everyone with certain letters at the start of their name

it really annoys people who's names start with A

Simon Righarts

I never upvote people whose names start with B. Sorry Ben :D

DTest

I'd downvote future me, but I'll never run into him

JNK

9:15 PM

i had to come share this

I just noticed an old post got bumped and looked for the new answer on it:

-1

A: Can I get better performance using a JOIN or using EXISTS?

exists is always faster than any other alternative.

Aaron Bertrand

I know this guy. Back from NNTP days. He could be bad news, to put it nicely.

JNK

really?

Aaron Bertrand

Oh my gosh. Think Celko with weapons.

JNK

I am confused when someone uses "always" when referring to SQL

and I have had a ton of instances where EXISTS was much slower than an inner join

Will EXISTS use a merge join?

Aaron Bertrand

I'd hate to state the obvious, but it depends. :-)

JNK

9:19 PM

lol

I should have seen it coming

Aaron Bertrand

Though I haven't seen many cases where EXISTS is slower.

JNK

it's like googling "recursion"

Aaron Bertrand

In fact I can't recall any, but I'm sure I could cook one up if pressed.

JNK

The ones I have typically are when I am JOINing on two clustered indexes

The INNER JOIN is a lot faster than the EXISTS when both sides of the predicate are clustered index keys

at least in my databases here

Aaron Bertrand

A very well-respected member of the SQL community said to me once (and I just re-checked my e-mail archives to be sure I quote correctly) "That guy is a psychopath and with any luck is rotting in a federal prison." He would argue and argue points that didn't make any sense, make physical threats, bring up the H word...

I wasn't privy to all of the conversations but he earned quite a reputation as a royal fuckwit not to be messed with. I'm surprised he's lasted a year on SO without bannination. Maybe he's had kids and mellowed out a bit.

JNK

9:23 PM

H word? Rhymes with "Pitler"?

Aaron Bertrand

That's the one

Ding! Ding! Tell him what he's won, Bob!

JNK

what's that law called...

Godwin's law

Godwin's law (also known as Godwin's Rule of Nazi Analogies or Godwin's Law of Nazi Analogies) is a humorous observation made by Mike Godwin in 1990 In other words, Godwin observed that, given enough time, in any online discussion—regardless of topic or scope—someone inevitably criticizes some point made in the discussion by comparing it to beliefs held by Hitler and the Nazis. Godwin's law is often cited in online discussions as a deterrent against the use of arguments in the widespread Reductio ad Hitlerum form. The rule does not make any statement about whether any particul...

Aaron Bertrand

Sadly when I go back to find conversations from those days (which pre-date Google's dominance), I find that when Google bought deja (who had an NNTP archiving service called dejanews) a large portion of history has been thrown in some toilet somewhere.

JNK

he says he worked for MS

Aaron Bertrand

So for some of these things you just have to take legend at face value.

JNK

9:26 PM

It's funny how things like that happen

Aaron Bertrand

I believe it. I bet he didn't last long there. He doesn't seem to fit their culture.

JNK

You always hear that once something is on the internet it can never be erased

the flip side is "unless you want to see it"

help.lockergnome.com/office2/Aaron-Kempf--ftopict801359.html

Aaron Bertrand

Well I have searched to no end at various points trying to find discussions I know I've had and I know were once archived online, but they have vanished. Not everything from back then, but a lot. Some was leeched by these other sites like bigresource but they're much better at forum content than NNTP.

JNK

did you check the wayback machine?

archive.org/web/web.php

@AaronBertrand JINX

I was adding my A with a list of keywords and you edited yours to put em in

Mark Storey-Smith

9:48 PM

@AaronBertrand Ryan & Jason got us up and running in 10 minutes. I'd like to say otherwise but there was a touch of FUE on my part.

Aaron Bertrand

@MarkStoreySmith good to hear! I knew they would sort you out. Our team is pretty good.

@JNK yeah I should have left the original list. I was actually editing for other reasons (see new answer which shows some ideas for 2012).

Sorry, not new, updated.

JNK

yeah I just upvoted the expanded A

Mark Storey-Smith

Any TFS/VS wizards in?

Aaron Bertrand

<ducks> not in this house

Simon Righarts

10:24 PM

God damn. Some people.

0

Q: I'm going to delete a .LDF file (please don't shout at me :)

I can make sure my app is in a consistent state. I can rollback all the uncompleted transactions if any (just in case) it's ok I can DETACH the database What do I need the log file for after that? I mean, I've heard all the mumblings like "OH, NO! DON'T DO THAT, it will corrupt your database ...

sql-server backup delete transaction-log

I did like the "may as well ask about taking brakes off a car" analogy. Going to reuse that one I think

Ben Brocka

10:48 PM

@SimonRigharts Yeah, that was...yeah

Simon Righarts

@BenBrocka I think half of the problem is calling it a 'log' file, if we instead called it a 'transaction write-ahead store' or something people wouldn't confuse it with history logs or whatever they're thinking when they go "oh hey, log file I can delete that. Wait why isn't my database working?!? runs to SO to ask"

ConcernedOfTunbridgeWells

11:53 PM

.

Mark Storey-Smith

@ConcernedOfTunbridgeWells DVD in the post today chap

ConcernedOfTunbridgeWells

@MarkStoreySmith Wunderbar

Mark Storey-Smith

I've been manhandling a SP2010 install today (for TFS integration)... its a big ol' bastard of a product

Transcript for

The Heap™ – Consultancy ©®