The Heap™ – Consultancy ©® - 2018-01-23 (page 1 of 2)

I hope she made it out

Joe Obbish

I'm so confused

It's a lovely place. You suck.

"You Suck." <--- See, Paul has met me!

1:51 AM

See the starboard for details

@SeanGallardy-Microsoft I haven't met you and I also know that

maybe I'm a psychic too

Excellent!

Anyway it's good the see The Heap making as much sense as usual this afternoon

"afternoon"

This guy; amirite

1:52 AM

$localTOD

Joe Obbish

you guys don't make any sense

I'm leaving

This is a good read: Table Partitioning Best Practices by Dan Guzman

@JoeObbish if anything, we are making too much sense

@PaulWhite that was a weekly link ages ago

I am deeply saddened

@sp_BlitzErik Was it? I just encountered it on main

It probably had a stupid click-bait title in the newsletter so I ignored it on principle

Still better than the backup a 12TB database in 16 seconds thing I had to sit through today

1:58 AM

@PaulWhite Was it really a backup?

aka "SAN magic"

@SeanGallardy-Microsoft Not to my way of thinking, no

@PaulWhite that's what I thought.

"So can I roll forward?", No.

@SeanGallardy-Microsoft But it was SQL Cat (or Azure Cat ... whatever)

Ah, the azure backup

That's not san magic

And being Azure it comes with 100,000 impenetrable gotchas

2:00 AM

Can't make it too easy for you

@SeanGallardy-Microsoft Still a snapshot magic thing right?

mmmm, I guess you can think of ti that way

If I were sufficiently interested, I'd go read up on it, but I spent 15 minutes reading the thing that didn't make the mechanism clear, so.

basically it's half san magic half cloud magic

Intel Urges OEMs and End Users To Stop Deploying Spectre Patch As It May 'Introduce Higher Than Expected Reboots'

2

2:03 AM

Assuming you're talking about File-snapshot backups for Azure

How to back up a 12 TB database in 16 seconds: https://blogs.msdn.microsoft.com/sqlcat/2018/01/22/sql-server-vldb-in-azure-dba-tasks-made-simple/

Tweeted by MSSQLCAT on January 22, 2018 at 10:33 PM

@SeanGallardy-Microsoft So is there a fully separate copy of the data or not?

Nope

SNA MAGIC <3 @PaulWhite

Not a backup then

unless you count the fact that azure storage has 3 separate copies

I preferred SNA magic

2:05 AM

then yes

I used to live right by SNA

Nice

John Wayne Airport

John Wayne Airport (IATA: SNA, ICAO: KSNA, FAA LID: SNA) is an international airport in Orange County, California, United States, with its mailing address in the city of Santa Ana, hence the IATA airport code. The entrance to the airport is off MacArthur Blvd in Irvine, the city that borders the airport on the north and east. Newport Beach and Costa Mesa form the southern and western boundaries along with a small unincorporated area along the Corona del Mar (73) Freeway. Santa Ana is just north, not actually touching the airport. Originally named Orange County Airport, the county Board of Supervisors...

That's a great name for an airport

Code should be GUN or something though

lol

It has so many different names, ugh

In case you wanted more reading on the file based snapshot: docs.microsoft.com/en-us/sql/relational-databases/…

I'd love to see what the bill is for 35TB of azure premium storage for backups though

Significant, I would think.

2:15 AM

So like... 8 hours of Paul White time?

Luckily Azure CAT have Erik's black Amex.

@SeanGallardy-Microsoft don't be ridiculous....that would cost a fortune

@Lamak Ridiculous is my cousin, nice guy but a bit odd..

@SeanGallardy-Microsoft If it would cost ~$800 then yes.

I think Erik's blamex card could handle that

2:20 AM

@SeanGallardy-Microsoft I remember that heapers got a discount

Ha ha blamex

@Lamak A negative discount in some cases

@Lamak ;)

@SeanGallardy-Microsoft Is there somewhere that details which recovery scenarios file-based snapshots are, and are not, suitable for?

I guess I could use a search engine

Pretty much just under the considerations and limitaitons area

spelling mistakes included

free of charge

I suppose the recovery considerations are a bit different when the primary storage is in Azure and has n distributed copies already.

2:24 AM

true, especially depending on region

The big ones (It hink) are

The caveats at the end of the Azure CAT article are significant though

•The backup file itself cannot be stored using premium storage.

@PaulWhite I don't have that much juice!

When using file-snapshot backups, you cannot perform an Online Restore.

@sp_BlitzErik Black spray paint

2:25 AM

If I did I'd just buy you a new laptop 🤗

When using SQL Server file-snapshot backup, additional charges will be incurred as data changes.

@PaulWhite that would ruin the metal finish

Hmpf

The last one is more housekeeping, but if you're looking at costs it does make it seem a bit heavy handed

It's things like this (aside from the $$$ and size, performance) that worry me:

I'll come bug you all tomorrow - unless you time travel like Paul, then it would be later today.

2:27 AM

> In particular, for databases using availability groups, we cannot guarantee that the different storage accounts behind each availability replica are on different storage scale units. Using the same storage scale unit for more than one replica creates a single point of failure.

Agreed

@sp_BlitzErik And for all Heap regulars :)

@PaulWhite except lamak

Except Evan. He'd get a Raspberry Pi running some obscure flavour of Linux and MySQL.

@sp_BlitzErik Sure, he'd get a printer.

I mean EXCEPT LAMAK

2:29 AM

EXCEPT ALL LAMAK

When I win Lotto, I'll fly you all to the Coromandel for a laptop gifting party.

Or maybe Queenstown.

75F here today.

I might treat myself to a Eskimo Pie

Do you have Eskimos?

Just their pies

But then we have penguins living in forests so

I do like penguins

Perhaps I'll come visit on my own

Few people have a bad word to say about penguins

Meet a nice girl penguin

2:36 AM

You never know

@PaulWhite SQUAAAAAAWK

How rude

Crikey NYC is a whole different world snopes.com/transgender-pronouns-fine-nyc

I'm identifying as a penguin today

What should I call you? Mr Flippers?

2:53 AM

My preferred pronoun is obviously "pie".

That's an odd pronoun for a penguin with sandwich dreams.

3:07 AM

I'm being oppressed!

@PaulWhite yeah yeah open a Connect item bub

7:10 AM

Good morning everybody.

Glad you all dislike the snapshot technology.

I'm currently fighting an Intellisnap implementation of a SAN vendor who states that backing up an Oracle Database is a breeze.

To be honest: It is a breeze. The SAN creates the snapshot and you can roll back to there. That's it.

Well that should read: That's all

No Point-In-Time-Recovery.

I'm reluctant to implement it site wide for Oracle and SQL Server, and would rather have it eliminated altogether.

Will have to persuade the powers-that-be to have it kicked out (Roger Waters - RADIO KAOS)

7:29 AM

@hot2use Any chance of running a DR test?

Auditors asking about backups?

and good morning

We've been doing multiple tests the whole last week and this week. And all it does is fail when trying to restore past the snapshot.

@hot2use I like SQL Server to disk backups only. Anything else can go to Fucking in Austria unless I have someone's signature in blood that restores are no longer my responsibility

@hot2use And what do management say?

They haven't been informed (YET)

What are they hearing, and from whom?

At the old place we had native SQL-Server and Oracle RMAN backups to disk. Never had an issue.

7:33 AM

I've removed HPE Data Protector Agent from my SQL boxes and use disk based now

@hot2use Of course

"What are they hearing, and from whom?" -> is some project monkey is reporting differently to what the techies are saying?

The team manager of the server team responsible for the backup environment is the guy selling the system as the Eierlegende Wollmichsau.

Then he takes responsibility for backups. How can you restore from non-existent files?

Unless it's snapshotting the MDFs etc?

That's the funny thing. The VMware snaphsots are stored in the msdb database as copy_only and is_snapshot so they don't interfere with the regular "normal" (albeit, still with 3rd party solution) backups. The other intelligent snapshots are however a copy of the disk blocks at the time the snapshot. They are not stored in the msdb and will only restore to that point.

With Oracle the system is shutdown immediate and then a block level snapshot is performed. Hmmmm. Not nice....

@gbn block level thingy

Andy K

evening and morning

8:04 AM

@hot2use You need a production demo at 10:00 then try a restore to 14:00. Then watch prod go down and can't restore later

8:46 AM

Morning

Tom V

9:04 AM

@hot2use You sure? If it's logged in msdb I'd guess its a backup using their application aware thing?

Don't you see backup to virtual=guid somewhere in the error log?

@sp_BlitzErik It's a shitshow, check the replies on this thread lkml.iu.edu/hypermail/linux/kernel/1801.2/04373.html

9:49 AM

@TomV The VMware snapshot is SQL VSS Writer compatible. The intellisense snapshot is block level (and not in msdb).

Andy K

question

how would you monitor a sql server db in the point of view of a windows sys admin e.g are there tools for that?

@AndyK Nagios with plugins, Red Gate SQL Monitor, to name 2

Most monitoring tools have SQL Server plugins if you already have a corporate standard

10:05 AM

@AndyK paessler.com/…

There is a free edition

For 100 sensors

Halfway there wohoo

Andy K

cheers guys

10:29 AM

@AndyK exchange.nagios.org/directory/Plugins/Databases/SQLServer

File-Snapshot Backups for Database Files in Azure

Picture 1. Point 5. Should possibly read: A snapshot of the NDF file is also taken.

@McNets dbfiddle.uk/…

@SeanGallardy-Microsoft hmm....

Always use the second parameter with OBJECT_ID (and use the N prefix to specify Unicode as well)

@PaulWhite yes, you're right, thank you

> Deleting the base blob will invalidate the backup set and you are prevented from dropping a blob that contains file-snapshots

cummulative storage?

That's going to cost a bit.

10:41 AM

8 hours ago, by Sean Gallardy - Microsoft

I'd love to see what the bill is for 35TB of azure premium storage for backups though

running away screaming - SQL Server DBA confronted with file snapshots

@MortezaMalvandi not at the moment. It's fairly simple, though, you could LEGO it together quickly, I believe. Look into postgresql.org/docs/current/static/functions-json.html, too. — dezso 10 secs ago

read: my hourly rates would be too much for you

11:26 AM

> Funding for the federal government expired on January 19th.

11:47 AM

@hot2use Unless you can make it someone else's problem. Especially the idiot who overrides you.

I once got a development team manager demoted to business analyst for ignoring one of my change requests (stop using plain text passwords and SQL logins and using prod for test)

A tester on his team took down the trading platform... I was covered

@dezso I thought that was over already?

A: Is there any (hidden) built-in function on MS-SQL to unquote object names?

12:14 PM

@PaulWhite ...nice answer

3

Is there any hidden built-in function that removes brackets using T-SQL? No, not using T-SQL. OBJECT_ID is an intrinsic function. It is implemented directly in the SQL Server executable code, not in T-SQL; and it does not call any T-SQL when invoked. At runtime, the object id is obtained v...

yes

It's more of an addition to the other two. I couldn't see a neat way to edit the info in, so it is separate.

Aaron and David have the useful, practical advice as usual :)

@hot2use Thanks. By the way, "is parsename the inverse of quotename" might make a better separate Q & A than tangential comment discussion.

Just a suggestion. I didn't remove it because it is valid feedback on an element of David's answer.

Possibly.

I haven't thought about it. I think I'd need to see some clearer examples, but I suspect you have a good point.

"Inverse" is probably not the best word, but I can't think of a better term offhand.

Honestly I'm just happy to be writing answers today rather than dealing with vandalism and spam :)

12:30 PM

@PaulWhite just wanted to check the tsunami alert, and that page warned about it

ConcernedOfTunbridgeWells

@dezso Huh? Which page? I linked to:

1 hour ago, by dezso

> Funding for the federal government expired on January 19th.

@gbn I've had a few managers that I'd like to see demoted to a tier-1 support analyst so they can spend some time in a job where they have to deliver something to acceptance criteria that they can't manipulate their way out of.

@PaulWhite tsunami.gov it has a warning and a link to commerce.gov/news/the-commerce-blog, where the above quote can be seen

12:51 PM

OK