Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Consolidation and Collation Settings Expand / Collapse
Author
Message
Posted Friday, August 31, 2012 2:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:38 AM
Points: 30, Visits: 704
Hi,
I have just been told to assist in a consolidation exercise where a number of our databases from different servers are moving to just 1 server (SQL Server 2008r2). The default server collation for the new server will be SQL_Latin1_General_CP1_CI_AS. A number of the databases have a collation setting of Latin1_General_CI_AS though. What is the best way to change the collation settings for these databases? A lot of the information I have already researched seems to be for SQL Server 2000 or contradicts each other.
Thanks,
George
Post #1352643
Posted Friday, August 31, 2012 2:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 7:49 AM
Points: 92, Visits: 104
Hey,

It depends on how you're migrating the databases. If you are attaching them or restoring from backup they will still hold their collations. If you need to change them you can do so from SSMS. Right click the DB, Properties then Options. But if these databases are connected to applications they will likely have this custom collation for a reason & you shouldn't change it.

The instance collation is there for new databases & your system databases.

If you do have databases with a different collation than the instance you can encounter problems when using the tempdb for example
IE. If you create a temp table, it will take on the system database collation. If you then compare some column in that temp table with a column in one of your databases you can encounter a "collation conflict" error.

There are ways around it by specifying collation when creating the temp table but its something to be aware of at least.
Post #1352648
Posted Wednesday, September 5, 2012 2:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:38 AM
Points: 30, Visits: 704
Hi,

Thanks for your assistance. It has been decided that we will set up two different instances instead using a different collation for each. I also had a plan put together using your advice and some scripts I had researched. It looks like this will come in useful for a future date.

Thanks again,
George
Post #1354378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse