Changing Server Collation

I found out the the SQL db my predecessor set up as a fail over (test) db on a seperate server has the wrong collation. I am being told by my DMS provider that it needs to be changed in order for their program to run. It is SQL2012 running on Server2012. They said I need to reinstall sql server. I following article on how to do it without reinstall. Has anyone done this? Is there a better or easier way? Any advice will be appreciated. Thank you.

Do you want to change the collation on the whole SQL Instance or just one database only?

I was told the whole instance needs to be changed for their program.

first you will have to take Backup ... in case something goes wrong

What are all the things effected by collation ..
Identify these
R these in any way relevant or is it ok to go ahead and change the collation

hope this helps :slight_smile: :+

What is the collation of the main server, the non test server?
Are there any other databases on this test instance not used by the DMS server?

If they are asking you to change only the test server, I would go for a fresh new instance install with the proper collation. the other route is just too painful. depends on how much stuff you got going on the test server.

The collation of the live server is SQL_Latin1_General_CP1_CI_AS the collation of the test server is Latin1_General_CS_AS_KS_WS. Only db on test is the DMS.

I have done neither. Part of getting this corrected is I want to upgrade the test from SQL2012 to a newer version. We are at the minimum. If I upgrade can I change the collation then?

you got a few options

  1. uninstall current version and install SQL2012
  2. install SQL2012 as a new instance

I personally do not like upgrade route unless that is what you will be doing in another environment , I don't see the purpose of that upgrade exercise especially if you only have one test DMS