jueves, 19 de mayo de 2011

SQLBCP2Azure: Transfiere datos eficientemente a SQL Azure facilmente

La herramienta SQLBCP2Azure es una herramienta de copia de datos eficiente entre SQL Server y SQL Server Azure y entre SQL Servers. Se trata de un reemplazo de la existente bcp.exe que viene con las herramientas cliente de SQL Server.

Los principales beneficios de utilizar esta herramienta son:

  • Ficheros de configuración persistentes ( xml )
  • Cifrado de passwords

Un ejemplo de configuración es el siguiente:

Create user table type
  1. <?xml version="1.0" encoding="utf-8" ?>
  2.             <SolidQBCP ContinueOnError ="False"
  3.            BatchSize="4096"
  4.            NotifyAfter="10000"
  5.            Hints="TABLOCK,CHECK_CONSTRAINTS"
  6.            TimeOut="0">
  7. <!--
  8.   ECB:             
  9.   How to encrypt a password:
  10.           1: SolidQ.BCP.exe -GenerateKeyEncryption -output c:\path\outputKey.txt
  11.           2: SolidQ.BCP.exe -EncryptPassword password -Key c:\path\outputKey.txt  -Output c:\path\outputEncryptedPassword.txt
  12.           3: Copy in the "Password" tag of this XML (at destination or source, depending of what password are you encrypting)
  13.             the exact output of de c:\path\outputEncryptedPassword.txt
  14.           4: Copy in the CDATA section, the exact value that you will find at c:\path\outputKey.txt
  15.              Obviously, the two passwords must be generated with the same outputKey.txt :)
  16.              
  17.   Hints Values:
  18.     - TABLOCK
  19.     - CHECK_CONSTRAINTS
  20.     - FIRE_TRIGGERS
  21.     - KEEP_IDENTITY
  22.     - KEEP_NULLS
  23.     - USEINTERNALTRANSACTIONS
  24.     
  25.   TimeOut:
  26.     Number of econds to the operation to complete after error
  27.     if 0, no timeout specified
  28.     
  29.     
  30. -->
  31.     <credentialsforce_column_mapping="true">
  32.     <source
  33.       SqlInstance ="10.1.1.99\sql2008r2"
  34.       DatabaseName ="DWrendimiento"
  35.       IntegratedSecurity="false"
  36.       User="sa"
  37.       IsPasswordEncrypted="false"
  38.             Password ="password"
  39.       UseQueryAsSource="true"
  40.       TableName="snapshots.performance_counter_values"
  41.       Query="select * from snapshots.performance_counter_values"
  42.       ColumnNames="performance_counter_instance_id,snapshot_id,collection_time,formatted_value,raw_value_first,raw_value_second"
  43.       />
  44.     
  45.     <destination
  46.       SqlInstance ="(local)\sql2008r2_2"
  47.             DatabaseName ="borrame"
  48.       IntegratedSecurity="true"
  49.       User="enriquecatala"
  50.       IsPasswordEncrypted="false"
  51.       Password ="n5SoiTel+ulzJuz83IUkBA=="
  52.       TableName="dbo.performance_counter_values"
  53.       ColumnNames="performance_counter_instance_id,snapshot_id2,collection_time,formatted_value,raw_value_first,raw_value_second2"
  54.       />
  55.     
  56.     <key>
  57.       <![CDATA[example: s&=79"7. D1_  XB-k6N7mja-5-$z2'1kx@*:z7R[V(] zx3=v-Pd|}gq?['\8Qs{i}1DypEG[+'0o7kf+h,Iz@{g2V+w="sjg9\o!H0&T8 y7C0gb*8#@QjC0*?$~C*reB>:NL,0hMF#/:!Kq-1=FW=VM0<3O&TvCQ t@GHd-4&I$]J4.ecS1A\@]]>
  58.     </key>
  59.   </credentials>
  60. </SolidQBCP>

 

La herramienta ha sido liberada en codeplex bajo licencia Microsoft Public License (Ms-PL).

Para más información http://bcp2sqlazure.codeplex.com/

No hay comentarios: