« first day (3328 days earlier)      last day (1543 days later) » 

Paul White 9
Paul White 9
4:15 AM
 
 
3 hours later…
John aka hot2use
John aka hot2use
7:40 AM
Morning
 
Andriy M
Andriy M
Morning
 
McNets
McNets
Morning
 
 
2 hours later…
Peter Vandivier
Peter Vandivier
10:01 AM
@ypercubeᵀᴹ i sent a tentative apologetic-toned email asking about time off for the conference and got an email back saying "oh yea, i meant to talk to you about that. here's the paperwork for full reimbursement fo you to go". clearly i've been traumatised by previous jobs and have not yet fully embraced how awesome my new managers are
3
never actually worked at a place with a training budget and the will to spend it before
 
John aka hot2use
John aka hot2use
@PeterVandivier Nice
 
McNets
McNets
@PeterVandivier that sounds good
 
Peter Vandivier
Peter Vandivier
it's both pleasant and disorienting
felt the need to share :p
 
ypercubeᵀᴹ
ypercubeᵀᴹ
10:27 AM
Nice. I've been to Postgres in London 3 times.
Once paid by job, 2nd got time off but paid myself and 3rd, in the new job, no compensation as we didn't use Postgres.
I may get it this year as we have a new app that uses Postgres ;)
 
Peter Vandivier
Peter Vandivier
i've literally never been to a technical conference before
thought about doing PSDayUK back in the fall but it felt like a bit of a slap in the face to the guys i work with since we don't actually use powershell for anything and they pretty chronically beg me to stop doing all my POCs in powershell
 
Michael Green
Michael Green
@PeterVandivier Never ever? You know you have to dress up, right? Like clown shoes and stuff.
 
Peter Vandivier
Peter Vandivier
10:43 AM
@MichaelGreen oh man, there's etiquette? will my mime outfit work? or does it have to be a proper colorful clown?
 
ypercubeᵀᴹ
ypercubeᵀᴹ
There is sqlbits as well. First week of April in London. sqlbits.com
 
Peter Vandivier
Peter Vandivier
@ypercubeᵀᴹ almost got to go to that 2 years ago (had tix and everything), but that's when i was at the super toxic job and my line manager nuked it last minute
 
ypercubeᵀᴹ
ypercubeᵀᴹ
Just registered for Saturday (free, baby ;)
 
Michael Green
Michael Green
@PeterVandivier Mime will be fine. It makes the Q&A sessions more .. um .. perplexing perhaps, but I'm sure you'll manage.
 
Peter Vandivier
Peter Vandivier
@MichaelGreen please come to my pre-con. i'll be explaining how network latency affects distributed transactions in the language of interpretive dance
 
Michael Green
Michael Green
10:50 AM
@PeterVandivier I've been practicing
 
Peter Vandivier
Peter Vandivier
 
Michael Green
Michael Green
11:05 AM
@PeterVandivier That made me chuckle. God bless the internet!
 
 
4 hours later…
Cade Roux
Cade Roux
3:00 PM
How much credence should I give to the percentages in plan explorer? That big bit of data (the clustered index scan) is reading 3.6GB, but plan explorer doesn't say it's taking much of the query.
 
Andriy M
Andriy M
Is that CPU percentage or I/O percentage or the combined one? Also, I think PE shows the %'s based on the points given in the plan. And the plan is probably generated by SQL Server itself, so...
 
Cade Roux
Cade Roux
3:31 PM
That's combined CPU/IO, actual plan, cumulative cost, line width by data size
 
billinkc
billinkc
4:19 PM
@MikeTheLiar Are you averse to using a Script Component as your source?
 
Evan Carroll
Evan Carroll
4:29 PM
0
Q: What kind of complications would I face downgrading from MySQL 8 to MySQL 5?

Evan CarrollLet's assume I wanted to downgrade to from MySQL 8 to MySQL 5.7. I know this isn't supported, but what kind of complications am I likely to encounter. The docs say, The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you backup your d...

mysql mysql-5.7 mysqldump mysql-8.0 downgrade
 
Cade Roux
Cade Roux
And I found this pretty whack - the two statements in the batch had estimates one way and then actual duration was completely the other way...
 
MikeTheLiar
MikeTheLiar
@billinkc not in the slightest
We already have a script component that is supposed to do a bunch of raw-data preprocessing - it's just that this particular piece of data is already null when it gets there
 
billinkc
billinkc
4:58 PM
@MikeTheLiar An older approach I used and seemed to work well for wrangling the weird shaped data we had
An unfortunate byproduct of the current formatting if you copy/paste is the line numbers come with it. I can find actual code somewhere if you want it and/or I put it up in github or the like.
But I'm likely to forget this by the time I get home so remind me in 8ish hours ;)
Oh and I'd likely revise it to use the epplus library which is an wrapper for openxml. Oh and probably could have dome some fun linq in there but the core concept is "use the ace/ole drivers and read everything in as an object and then coerce columns to the expected type"
 
Cade Roux
Cade Roux
Semi-relatedly: github.com/dfinke/ImportExcel/issues/685 I just always expect some wasted time for unexpected Excel issues when dealing with even the simplest use cases.
And this gem which hasn't been fixed for YEARS: excel.uservoice.com/forums/274580-excel-for-the-web/suggestions/…
To be fair, the Import-Excel issue is because they do a TryConvert or something to turn a string into a number and it's not very smart about locale possibilities.
 
Tim
Tim
5:37 PM
I was wondering if someone can explain what migration is used for, and how it is done manually?
-3
Q: How are migrations done?

TimDatabase System Concepts says The basic scheme is to dump the entire contents of the database to stable storage periodically—say, once per day. For example, we may dump the database to one or more magnetic tapes. If a failure occurs that results in the loss of physical database blocks, ...

migration schema-migration
 
McNets
McNets
@Tim do you need to migrate a DB and you have no idea on how to do it, or is some kind of homework?
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@Tim there are quite a lot different procedures that may use the term migration. That's problem 1.
What do you mean with "migration"? ;)
 
MikeTheLiar
MikeTheLiar
5:52 PM
Are you suggesting databases migrate?
 
Max Vernon
Max Vernon
I wonder what the air speed of a unladen migrating database is.
 
Cade Roux
Cade Roux
With or without NOLOCK?
 
Max Vernon
Max Vernon
I don't know. aaaaahhhhhhggggggg
 
MikeTheLiar
MikeTheLiar
@MaxVernon depends on how annoyed I am and what floor I'm on when I throw it out the window
 
Max Vernon
Max Vernon
@MikeTheLiar lol
"when bits fly"
 
MikeTheLiar
MikeTheLiar
5:56 PM
"Run away!"
 
Max Vernon
Max Vernon
I wonder if we should have been more welcoming.
 
mustaccio
mustaccio
All migrant databases are welcome here
 
MikeTheLiar
MikeTheLiar
Unfortunately our round-trip time tanks in the colder months because the database flies to Mexico until spring
 
Cade Roux
Cade Roux
6:17 PM
@Tim it's just too broad a topic. Some people migrate databases between versions of the same database engine product (like SQL Server upgrades), some people migrate between sites/modalities (on premise<->cloud), some might migrate just to new hardware/configuration (server<->cluster), some people migrate between database engine products (SQL Server<->Oracle), some migrate between products built on top of databases (SAP<->Dynamics).
 
Max Vernon
Max Vernon
indeed. some people even migrate with a database.
 
Cade Roux
Cade Roux
In any case, the migration may or may not involve all kinds of things, between physically moving data, mapping data from disparate systems, changing schemas, changing code, changing connectivity. Just a ton of possibilities could be involved. Or relatively nothing, something that might be called "migrating a SQL DB to the cloud", might be as simple as backing up a SQL 2012 database and restoring it on SQL Azure in one afternoon or that situation might be a multi-yearproject.
 
mustaccio
mustaccio
In Soviet Russia, databases migrate you.
 
Max Vernon
Max Vernon
lol
 
mustaccio
mustaccio
I'm gonna get a migraine from all this migration that's going on
 
Cade Roux
Cade Roux
6:32 PM
In another amazing oversight on Microsoft's part: microsoftteams.uservoice.com/forums/555103-public/suggestions/…
I've moved to week start on Monday myself because of lack of Google/Microsoft integration, so I need to see both home and work calendars side by side, and for home calendars it is easier to see Saturday and Sunday together in the same week, so I like to have them both start on Monday (even though I don't put anything in my work calendar on the weekends)
 
 
1 hour later…
James
James
7:34 PM
@MikeTheLiar do it in python instead:
import os
import zipfile
import pandas as pd
from sqlalchemy import create_engine
import urllib
from datetime import date

conn_str = (
r'Driver=ODBC Driver 13 for SQL Server;'
r'Server=CoolServer;'
r'Database=SexyDB;'
r'Trusted_Connection=Yes;')

quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))

sourcefile = "C:\\Users\\MJames21\\Documents\\HuntingtonHospital Cancellations.xlsx"
 
Andriy M
Andriy M
@mustaccio That's why I dislike doing it so much, unless I'm merely helping my grateful colleagues.
 
mustaccio
mustaccio
"Migration from DBMS Vendor X to DBMS Vendor Y with zero downtime" was part of my job description for few years, I have a special place in my heart for the practice.
 
 
1 hour later…
Tim
Tim
9:03 PM
Is this counted as migration
alter table ... add ...
insert table ... values ...
does migration always create a new database from the original one?
Is modifying the original database not migration?
 
Max Vernon
Max Vernon
@Tim I wouldn't consider that migration. To me migration implies movement... i.e. the database being migrated is either moved or copied somewhere else.
 
ypercubeᵀᴹ
ypercubeᵀᴹ
But many people do use the term for exactly that. Modifying the db (creating or altering tables, adding columns, constraint, etc)
it's usually called then "schema migration"
 
Tim
Tim
Are changing schema of a database and migration of a database irrelevant to each other?
 
ypercubeᵀᴹ
ypercubeᵀᴹ
It's not a very correct use of the term to be honest but it is not uncommon
 
Tim
Tim
two different concepts?
they can happen together, but they are different concepts?
 
ypercubeᵀᴹ
ypercubeᵀᴹ
9:13 PM
Not 2 but many different concepts.
That's why it is usually confusing to use the term migration.
 
Tim
Tim
let's focus on concepts more than what they are called. define terms before using them.
 
ypercubeᵀᴹ
ypercubeᵀᴹ
- migration of a database (the data) to a different installation, but same DBMS
- migration of a database (the data) to a different installation, same DBMS but different version
- migration of a database (the data) to a different installation in a different DBMS, often involves schema changes
- update of the database installation to a different version
- schema changes of a database
and possibly other things that can be called migrations
 
mustaccio
mustaccio
We seem to be migrating in circles. Tim, would you like to provide some background for you asking these questions? What's your ultimate goal? If you just want to find all the various definitions of the word "migration", a dictionary might help.
 
Tim
Tim
My goal is simply to understand what migration of database means and how it is done
 
ypercubeᵀᴹ
ypercubeᵀᴹ
well, you have some answers above.
 
Tim
Tim
9:23 PM
how is it done in each case?
 
ypercubeᵀᴹ
ypercubeᵀᴹ
very carefully ;)
4
 
Tim
Tim
how
thanks
 
ypercubeᵀᴹ
ypercubeᵀᴹ
Seriously, it's like asking "how is food prepared". Too broad.
 
Tim
Tim
- how to do migration of a database (the data) to a different installation, but same DBMS
- how to do migration of a database (the data) to a different installation, same DBMS but different version
- how to do migration of a database (the data) to a different installation in a different DBMS, often involves schema changes
- how to do update of the database installation to a different version
- how to do schema changes of a database
I hope I am specific now
 
billinkc
billinkc
How do I cook rice?
 
Tim
Tim
9:26 PM
Unfortunately that is not my question
 
billinkc
billinkc
But it is analogous. It depends. Sushi rice is prepared differently from risotto from stir fry from brown rice from basmati
Converting an Oracle datetime to SQL Server is different than postgres
 
ypercubeᵀᴹ
ypercubeᵀᴹ
Several books could be written on this topic.
And I have to watch Picard episode 4 now ;)
 
Tim
Tim
Let's focus on ideas not tooling
what can't be written in a book?
everything can be summarized in a few sentences, if you know the idea
 
billinkc
billinkc
Is rice part of Greek food or are they more pasta/orzo culture?
 
Tim
Tim
let's focus on database migration
 
billinkc
billinkc
9:29 PM
Summary of database migration is be careful. Optimizations in one platform may or may not exist in another. Standards are guidelines but no one platform has implemented them all in a consistent manner
The room is sincere in this is an exceptionally broad topic
Migrating a database from one server to another can be as simple as picking it up and copying the bits over
It can be staggeringly complex if it's very large and has millions of operations a second
Version changes, speaking to SQL Server specifically, 2008/R2 to 2012 resulted in some very better and some very worse query plans being generated
 
ypercubeᵀᴹ
ypercubeᵀᴹ
I have been writing an answer on how UPDATE is different between DBMS for 3 months now. (albeit slowly, only during weekends). Haven't yet started on multi-table updates (updates with joins or views I mean) !
 
billinkc
billinkc
Vendor documentation though, will tell you what steps are required and what cutting edges are sharp in new versions
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@billinkc and that's only half a migration! ;)
JEAGL: moving from version 10.5 to 11
 
billinkc
billinkc
Data movement between platforms - make note of where you're at in the system, export everything and then import into new system. Test, review migration, etc. Time passes, extract everything that's new/changed since last migration and then import. Lather/rinse/repeat until you cut over
 
Max Vernon
Max Vernon
@billinkc I thought you only cooked chicken.
 
billinkc
billinkc
9:37 PM
@MaxVernon WHY DO YOU THINK I WAS ASKING HOW TO COOK RICE
 
Max Vernon
Max Vernon
@billinkc hah!
 
billinkc
billinkc
Seems Greece is both rice and pasta culture
 
Max Vernon
Max Vernon
I find cooking rice to be quite easy, but apparently many people find it very hard to get right. I use basmati rice, and soak it for 10 minutes, then drain the water. Then add very carefully measured water at a ratio of 1.45:1 water-to-rice. Then cook on high until the rice begins to boil, then cook on the lowest heat for about 3 minutes, then let it sit for 10 minutes. easy peasy.
but of course, all that falls prey to a great many It Depends™ things, such as your elevation, the age of the rice, the size of the dish, etc, etc.
 
billinkc
billinkc
I read that as the volume of rice to be cooked increases, the amount of water required does not scale equally.
 
Max Vernon
Max Vernon
in fact, I think there have probably been entire books written on how to cook rice The Right Way™.
 
billinkc
billinkc
9:42 PM
And of course, all of those caveats
 
Max Vernon
Max Vernon
so, perhaps it'd be easier to migrate a database :-D
 
Peter Vandivier
Peter Vandivier
I have a database of rice recipes I’d like to migrate, please tell me how. Thanks
2
 
Max Vernon
Max Vernon
@billinkc yah, as you get more rice you need every so slightly less water, I've found. it's weird.
 
billinkc
billinkc
I think supper tonight will have some risotto and who really cares beyond that? Certainly not me
 
Max Vernon
Max Vernon
@PeterVandivier NO! NOW GO AWAY! lol
 
billinkc
billinkc
9:44 PM
@PeterVandivier Follow mise-en-place and it'll all be fine
 
Peter Vandivier
Peter Vandivier
Is mise-en-place 3NF ?
 
billinkc
billinkc
By Codd I hope so
 
Andriy M
Andriy M
@PeterVandivier Do you mean you want to migrate from the database to cooking rice?
 
Peter Vandivier
Peter Vandivier
@AndriyM I really, honestly do
 
Andriy M
Andriy M
I haven't heard of anyone who's done that and returned alive
 
Max Vernon
Max Vernon
9:47 PM
wow Tim really destroyed this place today.
 
Peter Vandivier
Peter Vandivier
Like a ricesteroid from space
He came in like a riceting ball
 
mustaccio
mustaccio
on the other hand, our collection of recipes for database migration turned out to be meagre
 
Tim
Tim
seriously? It seems to me many here are very bored
I simply want to know about a database question
 
mustaccio
mustaccio
It's always Friday in the Heap™
3
especially on Fridays
2
 
Peter Vandivier
Peter Vandivier
I’m not bored, just drunk
And now hungry
For Rice
 
Max Vernon
Max Vernon
9:49 PM
@Tim oh Tim. It's just your questions are absurdly broad. We tried and tried to explain that migrations are tough, but it just sounds like you're looking for a one liner.
 
Tim
Tim
I just like to learn
 
Peter Vandivier
Peter Vandivier
I like to eat
 
Tim
Tim
I don't like to hear word salad
I like salad, but I can't afford it
 
Peter Vandivier
Peter Vandivier
You can put rice on salad
You’re an adult
 
Tim
Tim
I can't afford any food
 
Peter Vandivier
Peter Vandivier
9:50 PM
I assume
(Are you a child)
 
ypercubeᵀᴹ
ypercubeᵀᴹ
salad is good. Greece is sald country by the way. Not rice or pasta ;)
 
Tim
Tim
I don't like to hear word salad. for example, not answerable, too board
 
billinkc
billinkc
Then what is the answer to your question?
 
Tim
Tim
need a book, like cooking rice
 
Max Vernon
Max Vernon
@mustaccio oh no.
 
Peter Vandivier
Peter Vandivier
9:51 PM
Flagged
 
Max Vernon
Max Vernon
^^^ 9 years ago this week
 
Peter Vandivier
Peter Vandivier
😂
 
mustaccio
mustaccio
 
billinkc
billinkc
And that covers a migration from a specific platform to another. 2 of the 4 giants of the industry (Db2 and mysql being the others) but there's a countless number of sizable installed databases to consider
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@billinkc you forgot Postgres
 
Max Vernon
Max Vernon
9:53 PM
@James - you're just in time to join the party - perhaps you can explain database migrations to @Tim
 
mustaccio
mustaccio
here's another, a bit longer: redbooks.ibm.com/abstracts/sg247736.html
 
billinkc
billinkc
But migrating Oracle 9 to SQL Server 7 is very different animal than moving Oracle vCurrent to SQL Server 2019. Even more so than a terrestrial platform to a cloud based architecture
 
Peter Vandivier
Peter Vandivier
 
Max Vernon
Max Vernon
@mustaccio because 300 pages isn't long enough, I presume
 
billinkc
billinkc
I assumed mysql had a larger install base than postgres
I mean, open connection to database. Insert one row to table, disconnect. What happens is very different between sql server and oracle
 
Max Vernon
Max Vernon
9:56 PM
I wonder what it's like to migrate a NoSQL database to a real DBMS once you figure out that your company is gonna die an 'orrible death if you don't.
 
billinkc
billinkc
SQL Serer by default will commit that transaction whereas Oracle will roll it back
 
Max Vernon
Max Vernon
@billinkc are you really sure you want that row inserted? really really sure? really really really sure?
 
billinkc
billinkc
I had a coworker that worked with a networked database - so not even relational. Covering that would be far from a 1 line answer and filled with so much "word salad" you'd exhaust the dictionary in several languages
 
James
James
yikes...
 
Peter Vandivier
Peter Vandivier
@MaxVernon working through a mongo to Postgres migration now. Will let you know how it goes
 
ypercubeᵀᴹ
ypercubeᵀᴹ
9:58 PM
@MaxVernon I will be able to answer your question in a few months / years ;)
 
billinkc
billinkc
@MaxVernon Yeah I want that data inserted. I really , really really want
 
James
James
@Tim you need to install SQL Server (brent ozar has a good check list) then restore your databases to the new server, then reconfigure your applications to talk to the new server. it's easy peasy, really.
 
mustaccio
mustaccio
@billinkc You must be really committed to it
 
Max Vernon
Max Vernon
@billinkc then you must commit, no matter how much you don't like to explicitly commit.
@James genius
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@James will you JEAGL that?
 
Tim
Tim
10:00 PM
@PeterVandivier how did you migrate from mongo to postgres?
 
ypercubeᵀᴹ
ypercubeᵀᴹ
Please answer: very carefully
2
 
Max Vernon
Max Vernon
yah, @PeterVandivier give us a one-liner for that
 
Peter Vandivier
Peter Vandivier
@ypercubeᵀᴹ this guy gets it
 
James
James
related
 
mustaccio
mustaccio
sudo rm -rf /; shutdown -h now is a one-liner I'd use
it's always easier to start from scratch
 
Max Vernon
Max Vernon
10:02 PM
@mustaccio very effective
 
billinkc
billinkc
To summarize, while the room might be bored/drunk/hungry it's populated with database professionals with a broad and deep experience with databases spanning the productional gamut. When we offer both point specific analogies and general strokes, we do know something about which we speak
2
 
Peter Vandivier
Peter Vandivier
EAGL’d
 
Max Vernon
Max Vernon
@billinkc I hope you're not expecting us to star that
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@mustaccio and you don't even need that rm -rf /. It's Mongo after all
 
billinkc
billinkc
Nah, I only want to be known for chicken
4
 
Peter Vandivier
Peter Vandivier
10:03 PM
El pollo
 
Max Vernon
Max Vernon
that's star-worthy
 
Tim
Tim
@James do you mean first dump the original database to a file? Is the file a database internal file, a filesystem file, or a SQL script file?
 
Peter Vandivier
Peter Vandivier
 
James
James
it's sql server, right? you want to dump to a .bak file, same as you do for your nightly backups
 
Tim
Tim
May I ask what is in the .bak file?
2
 
billinkc
billinkc
10:04 PM
Unless you're restoring to Azure SQL DB, then you need to dump to a bacpac
 
mustaccio
mustaccio
@Tim Arroz con pollo
3
 
Max Vernon
Max Vernon
@Tim that's pinned so future folks can understand where we're at in this conversation.
 
James
James
@Tim lol it's a copy of the database
 
Peter Vandivier
Peter Vandivier
@mustaccio 😂
 
billinkc
billinkc
@mustaccio I laughed quite loudly at this
 
Tim
Tim
10:06 PM
@James SQL statements? binary content of the database which is only understood by the DBMS?
 
billinkc
billinkc
I award you full marks in Internet for the day.
There are third party products that understand .bak files
 
James
James
Magic runes. if you look at them, you die youtube.com/watch?v=ashgP4YMdJw
 
Andriy M
Andriy M
@Tim Binary content
 
Peter Vandivier
Peter Vandivier
@AndriyM false. It’s magic runes
 
ypercubeᵀᴹ
ypercubeᵀᴹ
binary content = magic runes
 
Andriy M
Andriy M
10:08 PM
I thought we agreed to keep it a secret
 
Peter Vandivier
Peter Vandivier
Well you don’t understand them until after you wear the sorting hat and get assigned to a house
 
Andriy M
Andriy M
I've been to the sorting house, they never assigned me to a hat there
 
Peter Vandivier
Peter Vandivier
The sorting house is very different in former soviet bloc states I’ve heard 😳
 
Andriy M
Andriy M
They had different hats too
 
billinkc
billinkc
In former soviet bloc states, you sort the houses
 
Peter Vandivier
Peter Vandivier
10:11 PM
@AndriyM Were they fuzzy?
 
Andriy M
Andriy M
@PeterVandivier They were. But you should've seen their hats!
 
Peter Vandivier
Peter Vandivier
Stahhhhp 🤣
 
Tim
Tim
I was wondering here dba.stackexchange.com/a/259706, how to "create a dump with DDL statements"? Is the content of the dump binary or SQL statements? What are the "DDL statements"?
In postgres, the commands to create a dump is pg_dump
it is not a SQL statemnt
I think that it is the same case for sql server, etc
Or do I misunderstand what "create a dump with DDL statements" means?
 
Andriy M
Andriy M
SQL Server actually has a Transact-SQL statement for that: BACKUP DATABASE
 
billinkc
billinkc
The backup generated from SQL Server will contain both data, structure and security
 
Max Vernon
Max Vernon
10:16 PM
@AndriyM they should have stayed with DUMP database
much funnier
 
Peter Vandivier
Peter Vandivier
💩db
 
Andriy M
Andriy M
@MaxVernon I didn't know they had migrated from that in the first place
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@Tim means a dump that contains DDL statements.
 
Max Vernon
Max Vernon
Yah, Sybase is dump
 
billinkc
billinkc
The references in the question about creating statements are not a native "thing" for SQL Server. There are tools that will script out the insert statements but for any non-trivial database, that's gonna be an inefficient means of moving data about
 
Max Vernon
Max Vernon
10:18 PM
Look at you being helpful
 
Peter Vandivier
Peter Vandivier
I’m gonna migrate to unconsciousness, g̶o̶o̶d̶ ̶n̶i̶g̶h̶t̶ good morning all
 
James
James
@Tim if you have to adhere to GDPR, you should back up to .txt files, anyways. Otherwise you can't Find And Replace Text (FART) their info out.
 
billinkc
billinkc
But yes, you should get a transactionally consistent copy of your database taken as often as is dictated by recovery point and recovery time objectives
Uncle Ben's rice is helpful but low quality
 
Max Vernon
Max Vernon
@billinkc true dat
 
Tim
Tim
Is it correct that database migration is essentially dump the original database into a file (either binary or SQL statements as its content) under one DBMS installation, and then create a new database from the file under another DBMS installation?
 
Max Vernon
Max Vernon
10:23 PM
No
Unless you want it to be.
 
Tim
Tim
I assumed no version change or DBMS change in the two installations
migration == dump + restore?
When the DBMS installations have different versions, what to do now?
 
James
James
@tim more or less. you're migrating your DB from one server to another. easiest way is a backup and restore. Things get weird when you need to reconfigure your applications.
 
Max Vernon
Max Vernon
@Tim That could be one very simple way of doing a migration.
 
Tim
Tim
@James when do you need to reconfigure your applications?
 
Max Vernon
Max Vernon
@Tim That could also just be done for development purposes
 
James
James
10:27 PM
depends on the applicaiton, normally after you've restored the DB
 
Max Vernon
Max Vernon
@Tim when they no longer work
 
James
James
^
 
Tim
Tim
what do you need to configure in your applications?
 
Andriy M
Andriy M
@MaxVernon My thoughts exactly
 
mustaccio
mustaccio
After Tim is done with us, we can publish this chat as a universal guide for migration of anything to anything else.
 
Tim
Tim
10:28 PM
I am trying to relate configuring applications to database migration
 
Andriy M
Andriy M
@Tim You need to configure whatever became broken as the result of the migration
@mustaccio Or, if that doesn't work, for cooking rice
 
Tim
Tim
That makes sense.
Assume migration == dump + restore
When the DBMS installations have different versions, what to do differently?
When the DBMS installations are different DBMSes, what to do differently?
 
James
James
for realsies though it depends on the vendor
you can set compatibility levels to be the same and hope for the best
 
Tim
Tim
Is it correct that still first dump but dump to SQL statement scripts not binary file
and modify the SQL script to work on the destination DBMS installation?
 
James
James
you really, really want to make a backup as a .bak, then restore it
 
Tim
Tim
10:32 PM
Thanks for reminding. Backup is necessary, but not part of migration, if I am correct
 
James
James
it's something you (or someone at your work) should be doing already, every day
you're not correct, a backup is a neccesary part of a migration
 
Andriy M
Andriy M
To clarify, a backup is necessary, and it's also a necessary part of a migration
 
James
James
yes
 
Tim
Tim
May I redirect your attention to my questions
3 mins ago, by Tim
Is it correct that still first dump but dump to SQL statement scripts not binary file
3 mins ago, by Tim
and modify the SQL script to work on the destination DBMS installation?
when the source and destination DBMS installations have different versions or even different DBMSes?
 
Andriy M
Andriy M
There can't be a correct answer to those questions. Different database products prefer different methods. You really need to start talking about a specific DBMS if you want less vague answers.
 
Tim
Tim
10:35 PM
I am focusing on idea, not tooling, at the moment
I guess the ideas are the same across DBMSes
 
Andriy M
Andriy M
Well, different vendors have different approaches to that idea
 
James
James
I don't want to asnwer the second question, because that's not a migration, that's etl, and no, i'm not explaining it to you lol
then the first question doesn't mean anything to me. Scripts are normally considered disposable - or part of your documents folder, if you care to back that up
 
Andriy M
Andriy M
For instance, I'm working with SQL Server and I haven't even once migrated an entire database using a SQL statement dump.
 
billinkc
billinkc
Empty a pub glass of water using a 5mL pipette into another pub glass of a different shape. Boring but easy
Now, empty Lake Michigan or the Baltic Sea using the same approach
Sure, it's the same concept but no, not the same at all
 
Tim
Tim
 
Andriy M
Andriy M
10:39 PM
Not any more. Oops.
 
James
James
@Tim you should pick up some of these classes, if you're not just trolling us brentozar.com/training
 
Tim
Tim
I am not trolling, but right now, I am not going to take that class
My confusions about migration come from several occasions in the past
Thanks for all the help. Sorry for any discomfort and entertainment
I hope that I have made your boring Friday afternoon pass a little quicker
 
James
James
lol, take a look at brent's classes - they are really good
i think all your questions are covered in this onehttps://www.brentozar.com/product/fundamentals-of-database-administration/
 
Andriy M
Andriy M
I'm actually in the first hour of Saturday, but my Friday was good, thank you very much.
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@James does that post include Postgres and Mongo tips? ;)
 

« first day (3328 days earlier)      last day (1543 days later) »