I need to migrate our SCCM database from an older SQL 2005 server to a new 2005 server with a different collation. The new server hosts a number of OPS type databases and as such, the collation cannot be changed. The research I have done thus far tells me a few things:
1) The collation of the SCCM db and TempDB must be the same making it impossible to just detach/attach the db.
2) the collation of the existing SCCM db cannot be changed without significant work (and possibly not at all)
3) While a named instance could be added to the new server with a different collation, SCCM works best when installed into the default instance.
4) it is best to start from scratch, and re-install SCCM.
I would like to do this with the least number of points of failure. Currently, my thinking is to re-install SCCM, pointing the system to store the data on the new server, therby create a new SCCM db with the proper collation. Following this step, importthe data from the existing db into the new one.
Can anyone suggest the best way of going about this.
P.S. the old collation is Latin1_General_CI_AS and the new is SQL_Latin1_General_CI_AS