collation issues

Question

collation issues

sqlfriend

SSC Guru

Points: 52781
More actions
May 27, 2014 at 1:03 pm

#293821

We have a SQL 2008 R2 database server, I will call it ServerA
Now we would like to migrate it 2012 on another new server ServerB.
Our standard here is using collation Latin1_General_CI_AS, so on the new server we intstalled this collation.
But NOT the microsoft default SQL_Latin1_General_CP1_CI_AS.
But on the old server ServerA it is using the default microsoft SQL_Latin1_General_CP1_CI_AS.
If I migrate the databases to the new server ServerA, but keep the old collation SQL_Latin1_General_CP1_CI_AS for those migrated databases.
will that work?
I know on new server tempdb will have our standard collation Latin1_General_CI_AS.
But change the database collation wil be a great pain - create the database, object , reimport data.
SO I would like to keep the old collation for these migrated databases, but going forward, when we creating new databases, it will follow our company standard collation.
Am I doing the right thing?
THanks,

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

collation is saved on a per-column basis...if you copy a database, the collation of individual columns stays at whatever it was, regardless of whether you change the Server or database default collation;

it sounds like you might need to change the individual columns as well.

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,

object_name(object_id) As ObjectName, * FROM sys.columns

WHERE collation_name is not null

AND collation_name <> 'SQL_Latin1_General_CP1_CI_AS'

and OBJECT_SCHEMA_NAME(object_id) <> 'sys'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks, lowell

Yes, if we need to convert the collation, I need recreate the database, recreate the tables on the new server, then import data.

I have 3 databases with a lot of objects and data in it.

It really is a pain.

So my question is can I keep the database as it is, not changing the collation,

that means it has a different collation in its tables, columns from server collation.

But goign forward for our new databases created on this server, it will have our company standard collation.

Will that work?

Thanks much

Lowell SSC Guru Points: 323518 More actions · Answer 3

yes it will work.

you might run into some issues in cross database queries , and maybe some queries that spool to tempdb, but you can easily keep your old databases in their existing collation, and new databases will use the defined server collation unless explicitly created or modified with a different collation.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

Yes, I did see issues before that happened on servers with different collations. Like you said

"to run into some issues in cross database queries , and maybe some queries that spool to tempdb"

I think that is cons that don't convert collations.

That needs a lot of fix too?

So frustrated to do a migration of this server, this is only old server that does not follow our standard.

Lowell SSC Guru Points: 323518 More actions · Answer 5

yeah the last time i had to fiddle with that situation, I had to edit any procedures which happen to also use temp tables;

i reviewed any joins and WHERE statements that were on char/varchar/nvarchar columns , and had to explicitly define the collation because the original database was in a different collation, and tempdb was not the same;

but reviewing and tweaking the procs was much easier than changing the collation of the database, and then confirming the application that used ti was not affected

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

Thanks that is helpful information to me, I will probably need to change the code in stored procedure then in the database collation, for the latter takes time, and the application we have has to run 24 hours all the time.

Thanks much for the help and ideas.

Chris Wooding SSCarpal Tunnel Points: 4558 More actions · Answer 7

I have just completed a project with exactly the same issue you had. We decided to go for the rebuild/reimport data option to get the database collations the same as the server. The main reason for this was that we have a lot of stored procedures that use temporary tables; because tempdb is in server collation, you either have to specify the collation on each column of the temporary tables or specify it as part of the Join when querying the data.

We generated a Visual Studio 2013 project for each database and made the changes there, then scripted the changes to build everything on the new server. We then used SQL Data Compare (from RedGate) to copy the data from the old server to the new one. If you go down this route, remember to set the Compatibility Level appropriately in the project if you want to take advantage of new 2008 features.

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

Thanks, Chris, I will probably go the same route if I found more stored procedures that use temp db or table.

A couple of questions for you,

When you generate the script in visual studio, anything need to pay attention to regarding the collation?

What collation are using or change to and from?

When using redgate data compare, does it take care of the order of inserting data because there are constraints and foreign keys on the tables

Thanks

Chris Wooding SSCarpal Tunnel Points: 4558 More actions · Answer 9

We had SQL_Latin1_General_CP1_CI_AS on our 2005 server and most of the existing databases and were moving to Latin1_General_CI_AS on the 2008 server.

In Visual Studio, I used the option to generate the projects from the existing databases. This will include your current collation. Change the collation at the project level and then search the project for any other specific mentions of collation (eg. temp tables, joins to other databases, etc.) and amend or remove them as necessary. You can publish the project directly to the new server or generate a script (my preferred method). The script should now only contain specific references to collation in any cases where you are deliberately using something non-standard.

The RedGate tool gives you the option to drop constraints, foreign keys, etc. during the implementation phase (also keep Identity or reseed) and then re-apply afterwards.

Note: obviously, you will be comparing your old database to the new empty one. The tool will generate a massive script of insert statements (one per existing record) so it will take some time on a large database - our largest table had about 13m rows and that database took 15-20 minutes.

sqlfriend SSC Guru Points: 52781 More actions · Answer 10

Thanks Chris, that is very helpful information.

I will use as a reference when I do my work.

Thanks

TomThomson SSC Guru Points: 104773 More actions · Answer 11

If you have no schema-bound objects which depend on the database default collation you could alter the database collation using ALTER DATABASE and then alter the column collatins for each table T with a column X of type VARCHAR(99) and using ALTER DATABASE ALTER COLUMN X VARCHAR(99) COLLATE Latin1_General_CI_AS to switch to the required collation? Of course it's an ALTER TABLR statement for each column that needs a new collation, but that may be better than recreating and repopulating the tables. If you have schema-bound objects though you are going to have to delete them before you can change teh database default collation, and then recreate them (and be warned that every check constraint counts as a schema-bound object, which - years ago - was my biggest problem doing a system-wide collation change) so that may mean it's easier to recreate everything and reimport the data rather than just trasferring the databases over and then changing collations.

Tom

sqlfriend SSC Guru Points: 52781 More actions · Answer 12

Thanks Tom for the recommendations. It is good to know from your experience.

I will note down and give it a try.