February 10, 2016 at 9:39 pm
Hi Guys ,
I am migrating database from server A to server B . The collation in Server A is SQL_latin1_general_CP1_CI_AS and server B is
Latin1_General_CI_AS
So the database from server A has collation SQL_latin1_general_CP1_CI_AS.
I need to consolidate them in 1 server so that’s why I migrate the database.
My question :
Will it become a problem in the future ? is it difficult to change database collation?
PS: I will not join database taken from server A with the existing databases in Server B
Any feedback are much appreciated
Thank you
February 11, 2016 at 5:17 am
WhiteLotus (2/10/2016)
Hi Guys ,I am migrating database from server A to server B . The collation in Server A is SQL_latin1_general_CP1_CI_AS and server B is
Latin1_General_CI_AS
So the database from server A has collation SQL_latin1_general_CP1_CI_AS.
I need to consolidate them in 1 server so that’s why I migrate the database.
My question :
Will it become a problem in the future ? is it difficult to change database collation?
YES/YES
PS: I will not join database taken from server A with the existing databases in Server B
Any feedback are much appreciated
Thank you
The tempdb does also have a collation, the collation of the tempdb can only be the same to one of the databases you have got. Although there are 'workarounds' in general differences in collation orders are a pain in the a??.
To my knowledge it is not easy to change the collation of a database.
I did this once, because of a install with the 'wrong' collation.
It took me a few days to create a (fairly) generic script(s) to solve this problem.
And it took some hours process all tables.
Global way I did go about this.
1. Create a script which will restore relations/indexes and some more.
2. Strip the database of relations/indexes and some more.
3. Change the collation of each (character) column of each table.
4. Recreate relatons/indexes and some more.
I never have understood why MS does not deliver a change collation function/help/procedure.
Could be that in the 'newer' versions of SQL-server there is something for this.
Please keep us informed, what you decided and how you managed this problem.
Ben
February 12, 2016 at 1:41 am
Although with my collation change, I did it within the database, probably the next time I would test if copying all the data to a database with a 'correct' collation would be fast enough and use that.
I did have a look in the code I used before, see some notes below.
If anybody has a better (alternate) method, please inform us !
Succes,
Ben
Alltogether I performed the 'conversion' in 8 steps.
step 0.
Generate the script to create complete database. Remove all creation of the tables.
(This script is used in step 7).
Step 1.
Alter the database collation.
Step 2.
Check a number of occurences. (No changes in thet database).
Step 3.
Create a table which registers the DOMAIN_USAGE.
(Where are the user defined types used).
Step 4.
Removing triggers, constriants, indexes etc.
a. ('F','TR') sysobjects
b. ('C', 'PK', 'UQ', 'D' ) sysobjects
c. sysindexes.name like 'IX_%'
Step 5.
Chance all the datatypes to the correct collation.
Step 6.
Restore all DOMAIN_USAGE fields (use the table created in step 3).
Step 7.
Run the script prepared in Step 0.
The Process workt, but it was not all happy flow. (This was a number of years back, and a much older version of SQL-server)
So now I would be inclined to Create a 'new' database with the correct collation and move all data to that database. (Probably removing the indexes before and reinstalling them after the move).
February 14, 2016 at 8:10 pm
Hi…Thanks so much for the reply . Really appreciate your effort !
OK I feel that’s a lot things to do ( many steps ) . I would prefer to go for Create a 'new' database with the correct collation and move all data to that database ( it looks faster and simpler )
Can I use this steps :
•Create new database with the correct collation
•Right click on the database you want to copy
•'Tasks' > 'Export Data'
•Next, Next
•Choose the database to copy the tables to
•Mark 'Copy data from one or more tables or views'
•Choose the tables you want to copy
•Finish
Any steps that I miss out ? or can I use Backup – Restore ?
Many thanks mate
Cheers
February 15, 2016 at 6:06 am
WhiteLotus (2/14/2016)
Any steps that I miss out ? or can I use Backup – Restore ?
No you can't.
Within the management studio there is a possibility to copy a database.
But I do not know if you can solve this collation problem with that tool.
And there is always to 'trouble' with the Identity Insert. (If you have identities in your tables).
For your other questions, I am sorry, but haven't tried that myself, so am not in a position to advise you on that.
Ben
(Offcourse you should try your solution first in a non production environment on a copy of the production situation.)
(Offcourse you should make appropriate backups of your database(s) before doing a large operation like this one.)
February 17, 2016 at 4:28 pm
Hi , Btw I still have a feeling that I don’t need to change the collation of Database which is migrated from Server A to Server B . Because in Server B that database will stand independently and HAS NO RELATION AT ALL with the other existing databases .
SO Do you still consider it’s worth it to change the COLLATION as the effort is not small ?
February 17, 2016 at 4:38 pm
As has already been stated by Ben, when you create temporary tables, they take on the collation of tempdb by default, which could cause you some issues.
There are ways to work with this, but you need to be aware of the issue.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 17, 2016 at 4:43 pm
Changing the collation is a huge effort in my opinion.So if it is not needed, don't do it.
If there is not 'connection' with other databases, there is no need.
There is still a likelyhood that temp tables are needed, but with some care collation problems can be avoided or solved.
Ben
February 17, 2016 at 6:01 pm
Thanks for the reply guys .. Appreciate it
Regarding Tempdb, I don’t create it anymore . So it’s already there.
I only migrated 1 database .
So do you still see any issue ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply