The Heap™ – Consultancy ©®

Cade Roux

00:51

@EvanCarroll Depends if you call New Orleans temperate.

We are humid subtropical en.wikipedia.org/wiki/New_Orleans#Climate

Evan Carroll

01:07

I'm in Houston. It's cold af now

I'll be in New Orleans the end of the month =)

guess New Orleans is 10 deg warmer.

hot2use

06:42

Good Morning

George.Palacios

08:00

Morning

McNets

08:41

Morning

Tom V

Morning

vickey colors

10:58

-1

Q: #1215 - Cannot add foreign key constraint

I am using mysql , i have below tables, i am trying to relate two tables.... table 2 : So I tried ALTER TABLE orders ADD FOREIGN KEY (tracking_two) REFERENCES awbno(tracking_two); got error : #1215 - Cannot add foreign key constraint

mysql

can anyone help me on this ?

George.Palacios

@vickeycolors Hi - You'll likely need to add some more info before anyone can help.

@vickeycolors Table structure (CREATE TABLE) scripts at a minimum - the community also tends to not like screenshots

McNets

@vickeycolors it seems that some values doesn't match the foreign key.

George.Palacios

@vickeycolors I'd also say wait it out - your question has been up for less than 20 minutes.

McNets

@vickeycolors stackoverflow.com/questions/16969060/…

vickey colors

@George.Palacios Thanks , i will try to add more details....

@McNets Thanks for reply, i checked that link, but did't got any idea......

McNets

11:07

@vickeycolors Instead of your table images, add CREATE TABLE statement as @George.Palacios has pointed out, it will be more useful. Indeed, community don't like images and usually down-vote these questions.

vickey colors

@McNets Thanks, i will update.....

ypercubeᵀᴹ

11:23

@vickeycolors can you join this room? chat.stackexchange.com/rooms/86006/…

JzInqXc9Dg

15:05

@billinkc so, just trying to make sure i cover any even remotely related/potential option when im talking about getting a secondary copy of data

it definitely looks like mirroring is not what i want - that this is intended more for disaster recovery and, more to the point, creates a secondary that isn't in a state that can be used for reporting, etc

but im a little confused about AlwaysOn Availability groups. and/or clustering...

George.Palacios

AlwaysOn AG's have read-only routing which can be useful for reporting

If somewhat flimsy in implementation

JzInqXc9Dg

the only experience i really have with any kind of secondary db is via log shipping

and i would say that is fine... except when the log chain magically breaks (veeam backups causing it? we have no idea)

or the fact that the secondary goes thru the restore.

George.Palacios

Clustering wouldn't work for a readable secondary anyway

JzInqXc9Dg

so, im trying to find a better way. i think transactional replication is what i want.

but, im just trying to make sure there arent other good options as well

what "is" clustering?

George.Palacios

Leverages Windows Failover Cluster tech to effectively "start" SQL server on box 2 when box 1 dies

Seconds - minutes in terms of failover time unless you're looking at huge db's with a high recovery interva;

So it's always effectively ACTIVE - PASSIVE

JzInqXc9Dg

15:16

ah ok. so disaster recovery is its purpose

George.Palacios

ACTIVE-ACTIVE just means you have SQL Instance 1 running on box 1 and SQL Instance 2 running on box 2

No. HA is it's purpose

JzInqXc9Dg

clustering same as mirroring, or no ?

George.Palacios

No they're different techs

JzInqXc9Dg

i mean same concept and purpose tho?

George.Palacios

Availability Groups have kind of replaced mirroring

JzInqXc9Dg

15:17

oh ok

yea this all sounds like its really about disaster recovery

so, i really think i am wanting replication

George.Palacios

AG's are clever in that you can design them to satisfy both DR and HA

It'll be down to your individual use case

jadarnel27

Always On Failover Cluster Instances (SQL Server) - FCI is primarily for HA, though you can use it for DR as well (FYI @JzInqXc9Dg).

JzInqXc9Dg

ah ok

jadarnel27

The acronym density on that chat message was pretty high.

JzInqXc9Dg

hahahah

George.Palacios

15:19

I think you can get it higher.

JzInqXc9Dg

FCI was the only one i cant figure out

what is the goal of High availability?

George.Palacios

To ensure that data is always available and accessible

JzInqXc9Dg

ok, so... can i give you an example ?

George.Palacios

Clustering as an example can help with upgrades of the SQL Server components. You upgrade the secondary, fail over, upgrade the primary, fail over

JzInqXc9Dg

(of what i think this is about, and a problem i think it is meant for...)

George.Palacios

15:21

Go for it

JzInqXc9Dg

so .. we have a data warehouse. it is updated only once every day during an overnight process.

our users hate having to wait an entire day

so.. naturally i could just increase the frequency of that ETL process to make the DW data more up to date

but.. that ETL process takes more than an hour to run... then on top of that we need to process cubes, so we're looking at like 2-3 hours for everything to run

George.Palacios

Makes sense.

JzInqXc9Dg

what I want to do is have a second DW database where...let's say at noon, the ETL runs but it targets this second database

George.Palacios

Does the ETL process extract data from more than one table?

JzInqXc9Dg

and once the ETL is finished.. the primary DW and the newly updated secondary DW essentially "get swapped out"

billinkc

15:24

Optimizing your ETL and cube processing might be a lower cost than fumbling with replication

JzInqXc9Dg

I have wondered that too @billinkc

George.Palacios

^ those are my initial thoughts as well

JzInqXc9Dg

altho this isn't why im looking at replication

George.Palacios

If your ETL extracts data from more than one table, replication could cause you more problems. What if the process extracts data from table 1, update happens on table 2 which has an FK to table 1, then table 2 is extracted

jadarnel27

@JzInqXc9Dg Failover Cluster Instance =)

George.Palacios

15:25

Your relational integrity could be compromised.

JzInqXc9Dg

the ETL extracts from... dozens if not hundreds of tables

George.Palacios

I would suggest that replication is a road you'll go down that could potentially complicate the process even further.

In addition there's then the extra load on the primary systems

JzInqXc9Dg

well, youre talking about the example i just mentinoed right?

trying to DECREASE the "age" of my data in the datawarehouse?

George.Palacios

Yep

JzInqXc9Dg

oh ok, yea i could see that

but................. how do ppl do this?

billinkc

15:28

It seems like the first issue you'll need to overcome is the renamed server. Replication hasn't received much love from MS so just getting the environment sorted out would be a priority.

JzInqXc9Dg

hang on tho

George.Palacios

@billinkc that seems sensible regardless of whether replication will then be used or not.

JzInqXc9Dg

ive introduced two different problems to this chat haha

one is the fact that i want a copy of a database from a server in Europe to our server in the states.

(ive been thinking replication is what i want for this. and yes, that is where the renamed server comes into play)

the second problem is the fact that our data warehouse needs to get refreshed more frequently.

George.Palacios

That second one might be worth posting a question for

JzInqXc9Dg

and that problem I have no idea what to even do about

sure, i agree

George.Palacios

15:31

It's a very wide topic - you'll need to define your current process pretty well

JzInqXc9Dg

agreed

but on the renamed server thing.................. @billinkc, you mentioned yesterday it was sort of a nightmare for you to deal with

i mean... im still trying to understand the problem.

that server is already in production. ERP system, reporting, ETL, etc all already connected to it

should i assume ALL those things will break if i just run the commands to rename the server? it is odd, because everywhere i've seen so far - i don't see the original server name actually being used. it was only in the wizard for the replication server where i saw it. and when i do @@servername

so ... that makes me think running the commands will be harmless because all those connections might be using the new name of the server already.

what was it like for you? did you have to go back and reconnect a bunch of stuff after sorting this out on your server?

Farhan

Hello Room.

I'm trying to post my very first question on dba.SE but getting this error: You can only post once every 40 minutes.

I have not posted a question anywhere since yesterday

jadarnel27

@Farhan That limit is based on IP address, so you're probably getting rate-limited by someone else's posting.

> Users with < 125 rep, 40 minutes since their last question anywhere on the network (This applies to the user's IP address, not their account. If the user shares that IP with other users, they can be limited by the other user asking a question anywhere on the network.) - source

Farhan

@jadarnel27 What should I do then?

George.Palacios

Wait 40 minutes or gain 125 rep by answering questions basically.

jadarnel27

15:42

@Farhan Unfortunately, I think @George.Palacios is right. I don't know of any documented procedure for requesting an exception to that rate limit. And even if there were, it would probably take longer than 40 minutes to turn around.

Farhan

@jadarnel27 I don't mind waiting 40 minutes, but it will probably not solve the issue. I haven't asked a question previously and yet system assumed I did.

McNets

@Farhan dba.stackexchange.com/help/asking-rate-limited

jadarnel27

@McNets @Farhan is not hitting that specific rate limit (which is for poorly-received questions).

McNets

oh, sorry then

jadarnel27

@Farhan It could solve it! Especially if you are posting from your workplace or something, where many people could be connecting to Stack Exchange from the same IP address (behind some kind of corporate proxy).

Farhan

15:49

@jadarnel27 Yes, I'm at my workplace.

Paul White

8

A: "You can only post once every 40 minutes" on my first post

Bit of guesswork here Users with < 125 rep, 40 minutes since their last question anywhere on the network (This applies to the user's IP address, not their account. If the user shares that IP with other users, they can be limited by the other user asking a question anywhere on the networ...

just adding a meta reference (with lots of dupes) to @jadarnel27's observation

jadarnel27

16:06

Thanks, @Paul.

Farhan

16:17

Thanks @jadarnel27 @George.Palacios @McNets @PaulWhite I was just able to post my question.

Paul White

0

Q: Can SQL Server disable IntelliSense on SSMS?

I am using SSMS 2016 (ver: 13.0.15000.23) and connecting to SQL Server database 2016 (ver: 13.0.5081.1). IntelliSense is not working on my SSMS. I tried the following: Tools >> Options >> Text Editor >> Transact-SQL >> IntelliSense, and select Enable IntelliSense Tools >> Options >> Text Edit...

sql-server sql-server-2016 ssms intellisense

and there it is

Cade Roux

18:33

Anyone do any datawarehousing in healthcare?

In particular, I am reading up on this: healthcatalyst.com/late-binding-data-warehouse

Tom V

19:12

Agile data-warehouse yay

Cade Roux

So, just for the sake of argument, if you choose a star model and it's dominated by factless fact tables with lots of dimensions, is that a performance problem?

Like a cardiac study with very few scalar measures, but a lot of attributes like parts of the heart and all these basic flag attributes. So each study is a fact-light data point in a space with a high number of dimensions and obviously analytics is about finding areas of that space. Is a star-model in the DB really that big of a problem?

Assuming the data model is "correct". Is querying a star-schema that is like that generally not working to the star schema's advantages to the extent that you'd be better off with a different model?

And speaking of data models, is there any research which discusses proto-data modeling rules? Either before or since Codd. Because obviously you can have a normalized schema or a star-schema, but both are "correct" in the sense that there is no data loss, there is no ambiguity of the data association, but the data is arranged differently in the model and potentially duplicated.

Are there any more primitive rules before Codd where you can ask if a model (even an non-normal model) is "correct" in that sense?

Master Database

22:07

0

Q: Search broken? It's not returning results

why doesn't this show my latest answers with CREATE FUNCTION? https://dba.stackexchange.com/search?q=is%3Aanswer+user%3A2639+%22CREATE+FUNCTION It has three components, is:answer user:2639 (me) "CREATE FUNCTION" It only returns this answer, A: Are there any way to execute a query inside a...

support

Evan Carroll

how is mongodb still growing.

JzInqXc9Dg

22:44

idk much about it tbh

Transcript for

The Heap™ – Consultancy ©®