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

Questa voce è stata pubblicata in SQL Server e contrassegnata con . Contrassegna il permalink.

3 risposte a Migrare un database modificando la collation

  1. Omar scrive:

    questo articolo mi è stato utile per risolvere un problema riscontrato durante l’upgrade di un database, e sento il dovere di postare le modifiche che ho fatto:
    Ho dovuto modificare il codice per:
    – poterlo utilizzare in ambiente SQL SERVER 2000.
    – per due database di nomi diversi sullo stesso server.
    – penso che gli 8000 caratteri massimali per il tipo varchar non bastano per contenere tutti i comandi bcp, e non si possono definire variabili di tipo testo in sql server. Per questo problema ho dovuto:
    1- inizializzare la variabile @tmp a stringa vuota e non a ritorno a capo di windows.
    2- dopo aver eseguito questo script spostare manualmente i comandi dentro il CDATA.

    use MyDatabase
    DECLARE @srv_source varchar(50)
    DECLARE @srv_destination varchar(50)
    DECLARE @dbname_source varchar(50)
    DECLARE @dbname_destination varchar(50)
    DECLARE @dbuser varchar(50)
    DECLARE @dbpass varchar(50)
    DECLARE @tmp varchar(8000)
    SELECT @dbname_source = db_name(), @dbname_destination = ‘MyDatabase2′,
    @tmp =”, @srv_source = ‘SOURCESRV’,
    @srv_destination = ‘DESTSRV’,@dbuser = ‘sa’,@dbpass = ”

    select ‘bcp ‘+@dbname_source+’.’+QUOTENAME(USER_NAME(so.uid))+ ‘.’+so.name +
    ‘ out ‘+so.name+’.dat -c -U’+@dbuser+’ -p’+@dbpass+’ -q -S ‘+ @srv_source + char(13)+char(10) +
    ‘bcp ‘+@dbname_destination+’.’+QUOTENAME(USER_NAME(so.uid))+ ‘.’+so.name +
    ‘ in ‘+so.name+’.dat -c -U’+@dbuser+’ -p’+@dbpass+’ -E -q -S ‘+ @srv_destination + char(13)+char(10)
    from sysobjects so where so.type =’U’

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

  2. omar scrive:

    buongiorno,
    ho riscontrato dei problemi con lo script generato ieri:
    1- ho modificato la variabile d’ambiente path perchè esiste uno strumento bcp (lo stesso nome!) per sybase listato in questa variabile prima del bcp di MsSql2000.
    2- ho tolto Quotename() perchè il parametro -q di bcp svolge la stessa funzione.

    Per quanto riguarda la procedura ho dovuto:
    1- generare due script: uno con la struttura del database (senza collation,indici, trigger, chiavi), e un altro che contiene soltanto gli indici, trigger, chiavi e vincoli CHECK.
    2- eseguire lo script con la struttura del database.
    3- eseguire i comandi bcp per popolare il database.
    4- eseguire lo script con indici e chiavi.

    Spero che queste informazioni siano giusti e ringrazio Mauro perchè queso articolo mi è stato di grande aiuto

  3. Mauro Dalfreddo scrive:

    Sono benvenuti tutti i contributi, atti a perfezionare lo script o estendere le funzionalità. Ovviamente non potendo testarli tutti, non posso garantirne il funzionamento.

    Ricordo comunque che, come riportato nei link in fondo all’articolo, per quanto riguarda i riferimenti a BCP conviene consultare http://msdn.microsoft.com/en-us/library/ms162802.aspx.

    Tra i parametri offerti dall’utility ci sono -n e -N (per la codifica Unicode) per l’utilizzo del formato nativo di SQL Server, sicuramente più conveniente nella migrazione tra istanze SQL simili.
    Inoltre porre attenzione al parametro -k (keep Nulls) ed al parametro -V per la backward compatibility.

Lascia una risposta

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *


*

È possibile utilizzare questi tag ed attributi XHTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>