SQL 2005 2008 collation issue

I've been working on SQL server 2005 for a few years and i'd never come across collation issues. However i installed SQL 2008 on a virtual machine and tried to sync my 2005 database with the new 2008 server using Redgate SQL compare. It failed complaing about the collation on the 2 servers being different.

After a quick bit of research i discovered my 2005 is using Latin1_General_CI_AS and the new 2008 installation is using SQL_Latin1_General_CP1_CI_AS.

Why is 2008 using something different to 2005? Both times i've just done a default install. Secondly what should i do to get them the same, which collation is 开发者_StackOverflow社区the correct one?

---

I do not know what criteria is used for the MS SQL Server installer to select default collation.

Collation of a server does not have to be the same as the collation of a database on that server. Collation of a database does not have to be the same as the collation of table columns in the database. You can set the collation of the database when you create one. Default it will get the same collation as the server.

Here is a post about how you can change the collation of a database including changing the collation of columns. Changing SQL Server Database sorting with a reference to here http://support.microsoft.com/kb/325335.

Here is a starting point if you need to change the default collation of a server http://msdn.microsoft.com/en-us/library/ms179254.aspx.

I do not know what is necessary for Redgate SQL Compare, but I guess it only needs the database collation to be the same. So if you are replicating a db to a new server you probably only need to create the new db with the same collation as the one you already have and leave the default collation of the server as is.