Migrare un database modificando la collation

Mi sono dedicato ultimamente alla migrazione di alcuni database da un’istanza di SQL Server 2005 ad un’altra; come spesso succede, mi sono trovato di fronte al tedioso problema del cambiamento di collation, nel caso specifico da SQL_Latin1_General_CP1_CI_AS al Latin1_General_CI_AS. Se si utilizzano le funzionalità di backup e restore del database sulla nuova istanza, purtroppo ci si porta dietro la vecchia collation.

Esistono in internet degli script (ne viene riportato sotto uno stralcio) che modificano la collation degli oggetti contenuti nel DB (tabelle, colonne, viste, ecc.) e infine quella del DB stesso (utilizzando iterativamente dapprima ALTER TABLE tablename ALTER COLUMN columnname COLLATE default, ed infine ALTER DATABASE databasename COLLATE default). Funzionano in casi semplici, ma ci sono seri problemi quando la struttura del DB non è banale, ovvero sono presenti anche oggetti complessi (funzioni, foreign keys, trigger, indici, librerie, ecc. con le relative dipendenze), che devono essere droppati e ricreati.

La soluzione più semplice per migrare un database senza mantenere la collation originale da SERVER1 a SERVER2 è quella che descrivo nel seguito; i passi sono 3:

1. scriptare il DB (e tutti gli oggetti contenuti) senza generare le collation sulla prima istanza.
2. eseguire tale script sulla seconda istanza
3. esportare ed importare i dati di tutte le tabelle con l’utility BCP

I primi due passi sono banali. Per automatizzare il terzo, ho sviluppato il seguente script: in funzione del database MyDatabase scelto, l’obiettivo è quello di generate tutte le istruzioni BCP, una per ogni tabella (nel relativo schema) con i corretti parametri.

use MyDatabase
DECLARE @srv_source varchar(50)
DECLARE @srv_destination varchar(50)
DECLARE @dbname varchar(50)
DECLARE @tmp varchar(max)
SELECT @dbname = db_name(), @tmp =char(13)+char(10),
           @srv_source = 'SERVER1', @srv_destination = 'SERVER2'

select @tmp = @tmp + 'bcp '+@dbname+'.'+schema_name(so.schema_id)+ '.'+so.name +
' out '+so.name+'.dat -c -T -q -S '+ @srv_source + char(13)+char(10) +
'bcp '+@dbname+'.'+schema_name(so.schema_id)+ '.'+so.name +
' in '+so.name+'.dat -c -T -E -q -S '+ @srv_destination + char(13)+char(10)
from sys.objects so where so.type ='U'

SELECT  1 AS TAG, 0 AS PARENT, @tmp
            as [SqlCommand!1!!cdata]
FOR XML
EXPLICIT, xmldata

• Sostituire MyDatabase col nome del database effettivo.
• Eseguire lo script col Management Studio: viene prodotto come unico risultato un campo XML; al suo interno, internamente al tag <SqlCommand> si troveranno tutte le istruzioni BCP.
• Inserire tali istruzioni in un file .cmd (es. migrazione.cmd) appena creato. Spostare il file su SERVER2.
• Eseguire il batch con un’utenza Windows avente opportuni diritti  sui database di entrambe le istanze SQL Server. Nel caso si voglia utilizzare l’autenticazione SQL, basta sostituire nello script il parametro “-T” con le opzioni “-U login -P password”

Per esempio eseguendo lo script TSQL, viene generato il seguente output:

<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" 
xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="SqlCommand" content="mixed" model="open"></ElementType>
</Schema>
<SqlCommand xmlns="x-schema:#Schema1"><![CDATA[
bcp MYDATABASE.dbo.Software out Software.dat -c -T -q -S SERVER1
bcp MYDATABASE.dbo.Software in Software.dat -c -T -E -q -S SERVER2

bcp MYDATABASE.dbo.Ambiti out Ambiti.dat -c -T -q -S SERVER1
bcp MYDATABASE.dbo.Ambiti in Ambiti.dat -c -T -E -q -S SERVER2
bcp MYDATABASE.dbo.Fornitori out Fornitori.dat -c -T -q -S SERVER1
bcp MYDATABASE.dbo.Fornitori in Fornitori.dat -c -T -E -q -S SERVER2
]]></SqlCommand>

Nota: l’utility BCP viene qui utilizzata per esportare i dati in file plain/text. Nella fase di importazione i valori dei campi identity vengono mantenuti (-E) e il relativo seme viene riallineato correttamente; i check constraints, i triggers, le foreign keys, nella fase di importazione vengono temporaneamente disabilitati (per default)

Giusto per soddisfare la curiosità, riporto un breve script utilizzato per generare le istruzioni TSQL per modificare le collation di tutte le colonne di tutte le tabelle.

USE MyDatabase
GO
SET NOCOUNT ON
/*Find database default*/
DECLARE @name sysname,
@Collate VARCHAR(50)
SET @name =DB_NAME(7)
-- These props only available if db not shutdown
SET @Collate =
(SELECT CONVERT (sysname, DATABASEPROPERTYEX (@name,'Collation')))
SELECT 'ALTER TABLE '+ TABLE_NAME +' ALTER COLUMN '+ COLUMN_NAME
+' '+ DATA_TYPE +'('+CAST(CHARACTER_MAXIMUM_LENGTH ASVARCHAR(10))+') '
+'COLLATE '+ @Collate +  (
CASE IS_NULLABLE
WHEN 'NO' THEN' NOT NULL'
WHEN 'YES' THEN' NULL'
END
)
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME <>'dtproperties'
AND COLLATION_NAME NOT LIKE @Collate
ORDER BY COLUMN_NAME

Riferimenti:

http://support.microsoft.com/kb/325335/en-us

http://msdn.microsoft.com/en-us/library/ms162802.aspx

http://www.sqlserverspecialists.co.uk/blog/_archives/2007/10/31/3326039.html