- Posted by Carlos Diniz
- On May 7, 2018
- 0 Comments
- Change Collation, Collation, Database Collation, Instance Collation, SQLServer
Why does a DBA ask for details when requested to install or create a SQL Server Instance\ Database? One of those details is Collation. I’ve seen this a lot of times, when creating a database you don’t verify the default collation of the server and create databases with a collation you don’t want.
Problem: – applications with more than a database and different collations may result in wrong data comparison – server and user database with different collation may result in wrong data comparison – some applications have a certified collation – some languages have specific characters that required specific collation and may result in data error
Is there a easy way to fix it? Not that I know. You can use Collate all over your code, but in the end it will not work. To change the collation you will need application downtime. More work, as you and other teams will have to do correction, , validations, tests.
Solution: – create a new database with the right collation – copy all objects, configurations and permission from the old database to the new database – copy all data to the new database
There are a lot of ways to do it, be this is a simple draft how I did it: – generate database scripts from Management Studio, pay attention to all the options needed to get all triggers, indexes, partitions and other if needed by the database – create the new database with the correct collation using the script generated – disable all triggers and constraints on the new database – from this point forward you have to make sure no data is changed on the old database (set database read_only) – to copy data I chose to use BCP, why did I use BCP? I’ve used it in the past for other purposes and find it useful.
To get the list of the BCP commands for each table I use
USE OLD_DATABASE; GO SELECT 'bcp OLD_DATABASE.dbo.' + name + ' out C:\Collation\Export\data\db_' + name + '.bcp -SInstance_name -Usa -PSA_Password -n -E' FROM sysobjects WHERE xtype = 'U'; GO
Execute the result in command line or batch file and you’ll get a BCP file for each table with the data.
To import data to the new database I use the following code to generate the BPC commands
USE NEW_DATABASE; GO SELECT 'bcp NEW_DATABASE.dbo.' + name + ' in C:\Collation\Export\data\db_' + name + '.bcp -SInstance_name -Usa -PSA_Password -n -E -h "TABLOCK"' FROM sysobjects WHERE xtype = 'U'; GO
Execute the result in command line or batch file and data will start to be imported to the new database – enable all triggers and constraints – data validation is usually made by the application support or developers, I just make a simple row count from old and new database
SELECT TableName = so.name , [RowCount] = MAX(si.rows) FROM sys.sysobjects AS so , sys.sysindexes AS si WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name) GROUP BY so.name ORDER BY 2 DESC;
- open the new database to use
and its done!
Make sure that the application support team, developers and users are on boad with you so that if you have errors or any problem during or after collation change, any correction is made faster
All the information about BCP: https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017