What's the best way to change the collation in a SQL 2005 database? Using the alter database from the NAV client or directly from SQL management studio?
I tried to do it from the NAV client, but approx. when it reach 69% complete then it stopped, as the transation log file could not grow more. The database was 18 GB and the log file had grown to 78 GB! (It was empty when I started)
Best regards,Erik P. Ernst - webmaster at dynamicsuser.net
How about doing a navision backup and create a new db with diff collation and restore the backup?
That also crossed my mind, but it takes quite a lot of time to restore an 18 GB database... And as our operation is basically 24/7 then I'm looking for a way to minimize the time as much as possible.
You could disable all the keys. and enable them after collation change. You can also change the recovery model to simple Simple if that helps.
Well recovery model was already changed to simple... And disabling / enabling keys is the main reason why restoring takes so much time...
As I have seen if I want to do the collation change from the SQL Management Studio, then I must first do it on the database level, and then for all individual tables in the database. Is that true?
yes, a db has a collation and then every table has its own collation.
It's a big sql statement to change all the tables collation, I would rather do it from navision.
The other option is to do it in a small db. Turn on SQL profiler and capture all the sql statements, then do them from enterprise manager in test environment on another db, then in prod.
If you just wanted to change the collation of the Master db you could do from within SQL but on the databases I recommend NAV backup and restore to an newly created db (with same collation in master for optimal performance). 20GB should't take more than 3-5 hours to restore having the right hardware (backup probably 1-2 hours) and performing the restore directly from the SQL server.
Well actually creating a NAV backup is not an option. A large number of different errors are coming up when trying to do a NAV backup! I think it's due to the fact that we are running a lot of different character sets in the database already.
Yes it is not possible to do navision backup of companies where record have been entered with other codepages if windows collation.
KR
Kim
Just another reason to change to SQL collation!
Well I better gonna add some more space for that tlog file!
Using a SQL collation does not avoid codepage mismatch issues.
Both SQL collations and Windows collations (the latter as single-byte, which NAV utilizes) depend on a selected code page which by default must match between the client and server when accessing a database.
The problem of having characters that were generated by a machine running a certain code page when doing a backup or export, then restoring/importing on a machine with a different code page is common to both collation types.
It is not even a "database collation" concept - merely a code page concept. It is the same if you edit a text file with an OEM editor using, e.g. code page 437, then give that to someone opening it with Notepad using ANSI 1252. Extended characters will be different.
Since NAV does not utilize either collation types as Unicode, you need to ensure that code pages are fixed, or use characters that do not suffer under conversion.
Adding a different drive with a 100GB transaction log file did the trick. The collation is not set to the same for both server and db! And it's possible to run the NAV backup again.
Dean McCrae: Since NAV does not utilize either collation types as Unicode, you need to ensure that code pages are fixed, or use characters that do not suffer under conversion.
Ok, how would you then setup the server/database to allow have both ex. Danish, Greek, Turkish, Thai and Chinese characters (but in different companies and different Citrix front ends)?
The question was partly answered in http://dynamicsuser.net/forums/t/20849.aspx. Please continue the debate about what collation to use in that thread. This thread was mainly a technical issue regarding basic task of chaning the collation.
I think Erik wanted to refer to this thread: http://dynamicsuser.net/forums/t/20679.aspx which is how it can be configured allowing different languages and codepages in one central db.