SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - SQL Data Warehouse to Azure SQL Data Warehouse


SSIS - SQL Data Warehouse to Azure SQL Data Warehouse

Author
Message
msbikk
msbikk
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 7
Hi,

I am not sure if I have come to the correct forum group. If not, please direct me to correct forum.

I am currently working on the idea to move my on-premise data warehouse to Azure SQL Data Warehouse.

There are SSIS packages which populate the on-premise data warehouse and would like to reuse the same SSIS packages to populate the Azure SQL Data Warehouse.

My thinking is its just a change of connection configuration to target from on-premise data warehouse to Azure SQL Data Warehouse.

Is this something feasible or am I ambitious in thinking that this is possible or are there alternate and easy solutions for this migration?

I just don't like the idea of sitting and recreating all the ssis packages again to populate the Azure SQL Data Warehouse.

Any help is appreciated.

Regards,
KK
Steve Jones
Steve Jones
SSC Guru
SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)SSC Guru (328K reputation)

Group: Administrators
Points: 328211 Visits: 20105
You should be able to do this, but it's possible that you are using some features or expecting something in the destination currently that doesn't work (or is changed) in Azure SQLDW. The best thing to do is try.

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-load-from-sql-server-with-integration-services

Note that there also is Data Platform Studio in beta to help you here. Microsoft has sponsored some of this and certainly some features will always be free. Disclosure: I work for Redgate
http://www.red-gate.com/products/azure-development/data-platform-studio/

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221084 Visits: 33551
Through Microsoft's own testing, SSIS is the worst mechanism for migrating data into Azure SQL Data Warehouse. I would recommend any other method.

DISCLAIMER: I also work for Redgate.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)

Group: General Forum Members
Points: 547621 Visits: 47738
msbikk (12/28/2016)
I am currently working on the idea to move my on-premise data warehouse to Azure SQL Data Warehouse.


Not directly answering the question, but Azure SQLDW is not just a 'big SQL server' (as I've heard too often), but is a distributed data warehouse, consisting of multiple servers behind the scenes.
It's not feature-identical with a normal SQL database (thought they're getting closer), and some database design changes will likely be needed to get a database working well on it.

Are you planning a proper test/poc process for this data warehouse?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


msbikk
msbikk
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 7
Happy New Year folks.
Gail, Hope you are well. It was nice meeting you in UK, 7 months back at InsideSQL.
At the moment this is a POC but hoping to work it out in favour of project, trying to evangelize Azure and Modern way of building data projects/estates. Smile

Below is the approach I'm engaging now to help a client.

Solution 1: Using Data Migration Utility to migrate Schema and Data into the Azure SQL Data Warehouse. Then onward continue using the existing SSIS packages to start loading the data.

In this approach there will be performance hit into the loading data warehouse but also there will be lot of time spent in clearing the incompatible issues of the Schema and Data Migration. There will work involved in updating the target configuration settings in the SSIS packages.

Solution 2:
Creating the new data routines using AZCopy to load data into Azure blob and then load data into the Data Warehouse using the Polybase.

Performance is good in this method and also its a modern approach to building data warehouse. This approach will have to involve creation of new data routines and the option to resuse existing ssis packages might be hindered.
Redgate is an option I could use but there is cost involved here I assume and I'm sure my client might not be interested on this but I will have a quite word and see.

Regards,
KK
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221084 Visits: 33551
AZCopy is how I would do it if you aren't going to use the Redgate utility.

As to cost, the Redgate utility as currently configured, is free.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search