asked
Consolidate databases in a single sql serve box - SQL Server 2008 R2 - collate problems ?
Have to migrate dbs from SQL Servers 2005 EE 64 bit to a single sql server box with SQL Server 2008 R2 SP1 EE 64 Bits (cluster). The collates are: - Latin1_General_BIN – 12 dbs - Latin1_General_CI_AS -9 dbs - SQL_Latin1_General_CP1_CI_AS -3 dbs - SQL_Latin1_General_CP1_CI_AI – 43 dbs Questions : - Which collate I should choose for the sql server box ? - What are expected problems with remaining databases with different collates ? - What workaround can be done ?
Good call asking for advice prior to jumping into this in production. The biggest thing that will nab you is the tempdb issue that @thomasrushton mentioned. I see this more than cross database issues. With cross database issues most of your stored procedures or queries would have already taken into account the different collates on the servers they are on today. If your default collation on the instances of SQL on the other servers do not have the same collate as the consolidation server then you will lost likely run into issues with tempdb. This can also lead into potential issues with upgrades scripts in the future with your products. Time and time again I see this with third party applications who share a consolidation SQL server where the app database uses a different collation for the database. When conversion scripts are ran that create various temporary staging tables, the script will break on collation issues trying to join back to the database since tempdb and the user database are different collation. The work arounds are minor and easy to fix, it is just a pain to encounter.
You're likely to get problems when running cross-database queries. I would also be wary of using tempdb, but I'm being paranoid. Workaround? Heh. Testing, testing, testing to identify potential issues. And it's got to be full end-to-end stuff. When it doesn't work, look up the [COLLATE][1] modifier for T-SQL statements. And you should have a read of what @BrentO wrote about [the collation problem in conjunction with his sp_Blitz script][2]. That takes you to the [MS KB article on changing database collation][3]... Fortunately, SQL 2005 and later allows for a nice friendly [ALTER DATABASE][4] option instead, assuming you fit within certain constraints. Basically, the more of this stuff you can get done before you commit to anything, the better. [1]:
http://msdn.microsoft.com/en-us/library/ms184391.aspx [2]:
http://www.brentozar.com/blitz/database-server-collation-mismatch/ [3]:
http://support.microsoft.com/kb/325335 [4]:
http://msdn.microsoft.com/en-gb/library/ms174269(v=sql.90).aspx
One of the quickest areas that may bite you is queries that are written without consideration of case.
@Blackhawk-17 - good catch. @lumiga - what this means is that if you have a field named `objectID`, then a query against `ObjectID` won't find it. And it'll be a complete pain to figure that one out.
15 People are following this question.
