>>Is SQL Server installation case sensitive or not?
>
>It is not. But, this is about a Text field (it hasn't been converted yet) and a cast is necessary for the replace.

Try Unicode literals

UPDATE EmailTemplate SET Subject_E=CAST(REPLACE(CAST(UPPER(Subject_E) AS NVarchar(MAX)),N'[OldMemberTableName]',N'[Member]') AS NText)