@HannahVernon But you gave me an idea for another problem I am facing when duplicating principals and db_roles. I might stick it in the repo for Admin Scripts and fiddle around.

Morning

Morning

Morning

Morning

Oooh, a shell with SQL-like syntax github.com/nushell/nushell

@Philᵀᴹ very interesting

appropriate name

@JohnK.N. cool!

@Philᵀᴹ coming soon - NoShell! All commands work .. eventually, but only on JSON files. :-D

Good morning

here's a fun "date" in json, "DateOfLastUpdate": "2021-06-11T14:50:21-4 America/New_York"- anyone have a reliable way of turning that into a datetime, datetime2, or datetimeoffset

clearly I'm going to have to truncate the spelled-out time zone - that's pretty easy, but I can't seem to see a format that accepts the -4 tacked onto the end of the time like that. I suppose I could insert a space before the offset, but that would assume the offset is always negative or positive, I suppose.

Looking at datetimeoffset you might have do more than that.

Offset comes in half hour steps too. (India, I think...)

JavaScript and dates has always been a disaster. When JSON arose, that made me dread dealing with dates in JSON-formatted data.

@JohnK.N. More than just India. And there are several 45min offset timezones too.

Ohio was notorious for some weird time zone stuff but I think most of that went away in the 2000s.

@JohnK.N. Canada has one, too.

Maybe AT TIME ZONE can help somewhat.

I was planning to use that to turn the time into a UTC value, but getting the date sorted out is havoc. For now, I'm just truncating the date into a separate date column, and the time as-is without timezone info into a separate time(0) column. I've filed a bug report to the producers of the REST API, but I'm not holding my breath on that getting action. All the other datetime values returned by the other APIs in the set return ISO8601 dates lol.

@HannahVernon maybe this can help

joshthecoder.com/2020/11/10/…

> the details turned out to be trickier than I expected

lol. Never has a truer sentence been uttered.

@HannahVernon yup

This is why I feel we need a standards organization for CS + databases. Like, implementing an API with that date format would result in revocation of your license.

I could get behind that. This particular set of APIs is terrible about consistency. You'd think someone would be managing that, but alas, no.

In other news, the ratio of the number of answers I've provided on this site vs the number of answers I remember providing on this site is constantly going up.

@HannahVernon You would think that, eventually, I'd adjust my expectations 😅

thought experiment: You have RCSI enabled, and you start rebuilding statistics on a large table using a full scan. Someone then truncates that table. Does the stats rebuild needlessly continue because it's running on a snapshot?

@JoshDarnell don't worry, you're not alone

can i get a straw poll of the room for if folks have a "maximum size" or any other back-of-envelope style criteria for "this database should be in a container"?

how big is the container? just kidding

I like my databases to be no bigger than a length of string.

i feel like i'm managing a shitload of hardware for low-concurrency, small-transaction-volume, sub-100MB databases and i've just never worked with "company critical" datasets this small before

@HannahVernon so wise

@PeterVandivier how critical is response time? I mean Azure SQL Database does a pretty good job with small databases, and you can run them serverless.

cloud is not an option - has to be on-prem

but 400GB of RAM x3 replicas for a 50MB database is... let's say "generous"

lol

just a little

let me guess, they're expecting lots of growth

idk it's just weird for me to have turned that corner and self-reflect and be like... "i need to pitch kubernetes"

@HannahVernon 😱 how'd you guess?

are they already virtualized? or is this running on bare metal?

lol the staging dataset is 2 orders of magnitude bigger than the prod data because of all the load testing

bare metal

oh wow.

but any containers would get dedicated hosts

If it was me, I'd bare-minimum want to run them on Hyper-V. Just for the pure convenience factor.

our storage admins have had a Bad Time with our virtualisation tech and DB IO

but also Windows does have containers built in too.

would that it were Windows... 🥺

i haven't used a GUI in production in literally years

i'm like tiny tim begging for an ssh tunnel

Seems like a good candidate for consolidation, rather than containers. Is there a reason that's off the table?

> Deploy containers on-premises by using Azure Stack with the AKS Engine (in preview with Linux containers) or Azure Stack with OpenShift. You can also set up Kubernetes yourself on Windows Server (see Kubernetes on Windows), and we're working on support for running Windows containers on RedHat OpenShift Container Platform as well. from this

Oh I guess it's probably the expected growth.

> everyone in the universe will be using our database soon

@JoshDarnell not really, i've just never pitched it because i'm the only "DB guy" and it's just taken me this long to shake off the habits of VLDB admin

@JoshDarnell lol yes, we're all waiting on that

honestly dev teams have asked me how i feel about containerised DBs before and the only good answer i've ever given is "well it's not my first choice..."

@PeterVandivier Ah, that makes a ton of sense.

but working with super-small-but-still-has-to-be-HA data for this long... even I can take a hint eventually

I guess putting DBs in containers is a lot more common with Postgres than SQL Server?

🤷‍♂️ possibly

I never hear of folks using SQL Server in containers expect the handful of MVPs constantly pushing it on Twitter haha.

honestly i was pitching SQLite for a while there for a number of our apps

@JoshDarnell lol there's a couple of vendor projects that swoop by my head twice a year or so that make it look like i'm gonna get to do SQL Server on linux and it just never quite goes the distance

@PeterVandivier That's a bummer.

I'm sure it would be fun for you to be a beta tester of SQL 2017 on Linux.

such is life

@JoshDarnell omg plz tho?

i really miss getting to do bleeding-edge work

had a nice 10 months or so there that one time

now i just read Forrest's blog and feel jealous :p

@PeterVandivier I was trying to make a joke about how no one uses SQL Server 2017 on Linux, so even though it's 4 years you'd be beta testing.

@PeterVandivier Sounds stressful :D

I'm currently about to lose my fucking mind if I can't figure out why Angular two way binding isn't working on this text input.

> Jokes explained at great length (JEAGL) please.

:p

@HannahVernon I got there eventually!

Or should I have gone to greater lengths?

yuuuup

:-D

don't pretend like you never logged into a SQL 2000 instance literally any time in the last decade. 2017 still has the new car smell

I run SQL Server 2017 on Linux.

ooh u fancy

granted it's not for anything production

and it's not really 2017 anymore, I upgrade it regularly lol

Hannah is a liar it seems.

among other things :-P

@PeterVandivier LOL okay, you're right.

@HannahVernon 😀

wow that is a giant pain

booooo

i have a personal TODO for myself to actually subscribe at all 'cause RHEL now gates package management behind a subscription with AppStream :'(

but that came up when i was able to avail myself of the training budget a few months ago so... i guess i'll see when the next time that window opens is

@PeterVandivier One container per host? That's... bizarre? Counterproductive anyway

@PeterVandivier Size is not a factor when you decide to containerise

@mustaccio i don't see why. it frees up otherwise beefy hardware to be used more appropriately. fwiw the cluster in question is all on single-jailed freebsd hosts anyway so the pattern is the same just... smaller

@mustaccio yea, but i'm not trying to mount my storage outside the container either and that seems to be the vibe when you have large DBs in containers... at least so far as i've heard, which is not far to be fair

hence why i'm spamming chat

why do you need a container if you have an entire host dedicated to just one database? Or do I misunderstand what you mean by "beefy hardware to be used more appropriately"?

as for "trying to mount my storage outside the container", it's the only way to make it persistent. Local container volumes are blank slates when the containers restart, and they do restart whether you want them or not.

containers are meant to be ephemeral, so a database absolutely must be on a separate mount mapped to something that survives a reboot.

i have jailed database instances clustered to other hosts. i can and have evicted a node, destroyed-and-rebuilt the jail, and rejoined the node to the cluster in DR scenarios. the data volume being wiped is part-and-parcel of the current model

this evening i'm thinking that sounds like a prime candidate for containerisation - specifically with storage volumes inside the container that runs the db service

what i'm looking for is a reason why that's a Bad Idea that i've missed

no, I'm not talking about DR. In Kubernetes the master can suddenly decide to move your pod to another worker node, which involves killing one and starting another. It takes a second or two, so application rarely notice, but you don't want to start rebuilding your database if that happens

and that could happen to all your db pods at once, so you won't even have anything to rebuild it from

alternately, it'd be delightful if i came to the internet to [find people who agreed with me] (xkcd link not found)

but i am as a babe in the woods here

teach me your containeréd ways

we'll get you straightened up allright

not necessarily k8s, docker is an option, or lxc. to be clear the only reason i'm not pitching freebsd running on a raspberry pi is that we're supposed to be moving away from freebsd anyway

docker or lxc is what's beneath K8s

what's a "jailed" database?

you should get your terminology right first

"jails" are like freebsd vms

ahh

but it's a single-vm-per-host model

i ran FreeBSD once

so... still bare-metal IMO

Kube is a means to share hardware resource between containers; if you have dedicated hardware for database instances you won't benefit from anything containers offer

if, on the other hand, you have 3 servers to run 300 database instance which are rarely active all at the same time, K8s is the ticket

or if your entire application stack is containerised

i have too much hardware running too little database. i want to give my hardware away

please take it

can it mine dogecoin?

probably not on the db hosts :p but i ain't sayin' anything else

😅