I locally host host databases from two vendors on two different SQL instances, but on the same network. These servers frequently communicate with each other (through joins and whatnot), so we have them configured as linked servers.
Currently, both databases use the SQL_Latin1_General_CP1_CI_AS collation. However, one of the databases will need to be changed to Latin1_General_CI_AS soon in order for our vendor to continue fully supporting it.
My expectation is that we could manage this at the linked server level by setting "Use Remote Collation" to false. However, I've read that we should expect performance to plummet, if we take that approach. Is that correct? Short of testing this in an internal development environment (which would prove difficult for a variety of reasons), is there a way for us to estimate/calculate the sort of performance hit we would experience?
Thanks for your feedback! Hopefully this question isn't too ambiguous. I've done a bit of research on the topic already but am hard time finding a concrete response.
