 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / December 2008Migrating SQL 2000 to SQL 2005, any risk I change db collation too?
Hello, I am restoring SQL 2000 databases to a SQL 2005 SP2 where the SQL server's collation is Latin1_General_CI_AS. The SQL 2000 database backups that I am restoring have their collation as SQL_Latin1_General_CP1_CI_AS. Other than running SQL 2005 Upgrade Advisor, I scripted out the SQL 2000 database, created an empty SQL 2005 database and then ran the generated scripts to catch any errors. Some of the errors were about: "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation" Ok, maybe when I create the blank dbs in SQL 2005 I can have their collations be SQL_Latin1_General_CP1_CI_AS and then run the SQL 2000 generated scripts, this would give me no errors related to "cannot resolve collation..." unless the T-SQL code was maybe using tempdb objects. In any case, is there any risk that after I restore these SQL 2000 databases I simply change the database collation to become Latin1_General_CI_AS so I standardize and avoid running into collation conversion errors? I found these links: http://www.examnotes.net/archive79-2002-8-54071.html http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305704& It seems there's little risk to change the collation from "SQL_Latin1_General_CP1_CI_AS" to "Latin1_General_CI_AS", is it pretty safe to change the collation, are there anything else I need to be concerned about? What does happen when I change the db's collation? SQL will change all text/ntext, char/nchar, varchar/nvarchar actual stored data to be stored using a different collation and then change the columns' collations afterwards? I believe I would also have to drop the objects like UDFs, SPs that have collation conversion issues before I can change the db's collation. After changing the collation I re-create the dropped objects. What's the worse that can happen, go wrong when changing the db's collation? Can I switch back the database back to "SQL_Latin1_General_CP1_CI_AS" from "Latin1_General_CI_AS" in case I ran into problems? Thank you > Hello, > [quoted text clipped - 52 lines] > > Thank you Hi Serge Just changing the database collation will not change the collation for existing columns so you may get conflicts if you join new tables to old ones as well as tempdb!! You need to go though each column and alter them as well. Depending on the size of database it may be easier to do the scripting side (taking the default collation) and then sucking the data across, although that may increase the risk!! John Hi John, > Just changing the database collation will not change the collation for > existing columns It's good to know this. I think I will need to evaluate my options and the work involved and decide what to do. Thanks > Hi John, > [quoted text clipped - 5 lines] > > Thanks I assume that you have not standardised on a given collation and the new instance has been set up wrong? What you could do is create a SQL 2000 instance with the new collation and move over to that before upgrading to SQL 2005. To actually change the columns collation is quite straight forward and the scripts to do this have been posted many times by myself and others. The issue you may have is that constraints on character columns will need to be dropped before changing collation. This is not such a big issue with PKs, Unique constraints... as you may want to rebuild indexes anyhow. What may be an issue is the space needed to do this and the growth you may see. At this point in time you should be considering moving to SQL 2008 to avoid another migration in the not too distant future. John > I assume that you have not standardised on a given collation and the new > instance has been set up wrong? No this is not the case. The person(s) who had installed the SQL 2005 boxes had chosen SQL 2005's default collation but probably having checked the "Accent Sensitive" check box as I believe that check box is not a default check box (I might be wrong). And I am getting rid of a SQL 2000 server by migrating the databases over, so I do want to keep the "new" SQL 2005 Latinxxx collation rather than use the old SQL_Latin... > What you could do is create a SQL 2000 instance with the new collation and > move over to that before upgrading to SQL 2005. To actually change the [quoted text clipped - 4 lines] > constraints... as you may want to rebuild indexes anyhow. What may be an > issue is the space needed to do this and the growth you may see. I had already taken a script from somewhere online that was building me the script to alter the collations. And I was writing the DROP INDEX statements for the ones I was getting errors for, altering the collation and then re-creating the indexes. Space is not an issue in this case as there's ample room. A couple of days ago I was searching again and I read someone saying to change the db's collation, so I thought about that, I ran the script and I got an error about object dependency. I dropped the function that I had previously seen as giving collation conversion issue, alter the database's collation without any error and then re-created the dropped object. So I had some concerns to reduce SQL 2005 migration issues, whether I only alter the collations on the columns affected (where I was getting conversion errors) or I'd simply do alter all the columns of the whole database, and most importantly wasn't sure if I could without any fears simply alter to a different collation and not break something in terms of data having changed. I guess other than the accent sorting matter, everything else should be Ok. So I will change the collation of all the columns and also the database. http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305704& > At this point in time you should be considering moving to SQL 2008 to > avoid another migration in the not too distant future. I have to convince others that it's safe to move these databases to compatibility levels of 90 so imagine that there are people who still want to wait for SP1 before using SQL 2008. My plan is to push SQL 2008 after I finish the migration. Thanks again >> I assume that you have not standardised on a given collation and the new >> instance has been set up wrong? [quoted text clipped - 58 lines] > > Thanks again Hi Both your collations Latin1_General_CI_AS and as SQL_Latin1_General_CP1_CI_AS are accent sensitive. You'll get Latin1_General_CI_AS on a system if you go through the installation on a new installation on Windows if you just keep pressing next. Another option would be to use the COLLATE clause in all statements that will conflict use character columns in their where or join clauses and/or when you create the temporary tables. John John You can mitigate the risk of changing the collation by not doing it to your production database until you have a working script on a test copy of the production database. If you change the collation on the database and all the varchar/nvarchar/text/ntext/char/nchar fields in the database, then add back in all the constraints and indexes and primary keys that you had to drop, you should be good. If you opt to go with adding COLLATE statements to all your SQL (which is an option) I would suggest you collate to Database_Default instead of a particular collation. If you do this, your database will be more portable to other servers with other collations. At the same time, if you're depending on another database for data, it's a good idea to include COLLATE Database_Default, especially if you don't have direct control of that other database's collation. If you're interfacing with a third-party database or don't know for certain what collation will be in use on the other database, COLLATE Database_Default is definitely something that should be considered. Copy that production database, work on a script to change everything, test the changes, then backup the production database again and apply the script, and then test in production. If you do that, your risk should be minimized. -Eric Isaacs >> I assume that you have not standardised on a given collation and the new >> instance has been set up wrong? [quoted text clipped - 58 lines] > > Thanks again Hi You may want to use something like Red Gates SQL Compare where you can compare the structure of each database ignoring collation. The databases should match if you set the options to ignore collation. john |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.