SQL Server to Vertica Migration

Don_Draper

1)  Are there any accelerators for SQL Server 2012 to Vertica migration?
2)  What is the best way to Import a SQL Server 2012 Table into Vertica.  Is Export SQL Server table to delimited FIle and use the COPY Command the only quickest option?

Massimo_Loporch

With pleasure and successful we used Talend DI to migrate from ms-sqlserver to Vertical (VertcaBulkExec component)

Don_Draper

Thanks Massimo.  I suppose you used Talend for data migration.  How did you deal with the data structures (Tables, Views, Stored Procedures, Functions,...).

Massimo_Loporch

Talend can create table in Vertica with primary key and structure of the source table (disable automatic component projection creation) ... for functions you must rewrite it, for views you con create a projection, for stored procedure you must write UDX ....   see vertica doc for details .... at the end adop Vertica is the best choise I have ever made.

Navin_C

Hi ,
Other then the above solutions, if you dont want to go with TALEND ETL
2. The best way to migrate a table :
export table from SQL server--> land the files to Vertica Server--> run copy command
Before running the copy command deploy the ddl into vertica. 

For all this you create simple bash scripts and automate the process.

Don_Draper

Thanks Navin.  That is what I am doing currently.  Using bcp to export the table as flat file and using COPY LOCAL (as we cannot store in Vertica Server) to load it in the table.

Don_Draper

Navin, how did you handle the SQL Server Stored Procs?

Navin_C

Best way to do this:
Replicate the same logic in External procedure (.sh files)
and schedule it with your Linux scheduler

Hope this helps

DarinAfni

Don - did you have to install the client drivers and vsql to the storage server location that you're copying from?  I'm trying to do the same thing - and I also cannot store the multi TB flat files on the Vertica cluster.  I'm struggling to figure out how to COPY from LOCAL in my environment.

Don_Draper

Yes, I used an app server (:Linux) where I installed both SQL Server and Vertica drivers.  Using BCP I got the SQL Server Tables as Flat Files and used Vertica COPY LOCAL to load into Vertica.  Yes, it is common that you cannot stage the load files in the Vertica node.

Rupak_Rajguru

question on sql stored procedures:

 

from this thread i understand whether the stored proc can be converted to a .sh file or an function. My requirement is integrate the stored procedure to a UI ( like asp.net or WPF) because my stored procedure is returning a data set.  What is the best approach, can i call a vertica function through ADO.net ?

 

I am using the vertica ADO.net connector by the way .