First off, the "SQL_" prefixed default collation for a new installation of SQL Server is bad, bad, bad! This is a legacy collation going back to the "dark ages", e.g., SQL Server 6.5, 7. The recommended collation is the Windows collation. To make matters worse, the "SQL_" collation is only the default in the U.S. (English (United States))! Ref: http://msdn.microsoft.com/en-us/library/ms143508.aspx You will see that, for most installations, the default is "Latin1_General_CI_AS".

So if you are starting a new instance of SQL Server, choose the proper, and not a legacy collation, during the instance creation.

That said, you still need to be able to operate in a mixed collation environment. There is no mandate that all databases must have the same collation in the instance. For example, you may acquire a 3rd party application that requires a case sensitive database collation. There is no reason that the database for that application cannot be installed and running in your case insensitive SQL Server instance.