I am looking at upgrading some SQL 6.5 servers to SQL 2000. The 6.5 servers run a mixture of code page 850 & ISO 8890 and sort orders of 42 & 52.
We want to ensure all the 6.5 databases are migrated to 2000 BUT want to future proof the SQL 2000 databases by supporting unicode characters, as the SQL 2000 databases will be avialable world wide to all languages. That said, we want to ensure the existing table structures are preserved and run at 6.5 compatibility level. I am a little concerned as I am unsure if unicode support will alter table structures and require a re-write of the database schema or table structures.
Can someone help by giving me a straight forward explanation of the implications of my plans please?
Do the upgrade to 2000 and make sure that is ok...
when your happy it is then make a copy of the db(s) and change the char and varchar types to be nchar and nvarchar respectively.
Test and when your happy take the live server offline and backup the server (optionally restore it to another machine or have tested that your backups work) and then do the upgrade.
One thing at a time (and slowly) or your asking for trouble.
Unicode versions of chartacter fields are nchar and nvarchar which probably won't be available in 6.5 comapatability mode.
Using these fields will mean a change to the database structure.
If you want the database to be specific to a language then it's not too much trouble but if you want it multi-language you will have to include language versions of all the presentation data on separate tables.
To give true international support you will also have to consider the code pages for the sort order - i.e. you will need the alphabetical order for the language selected which will mean either coercing the columns in the query or having a database installed with the correct collation.