December 28, 2016 at 5:37 am
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
December 28, 2016 at 8:38 am
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.
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/
January 3, 2017 at 6:24 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2017 at 6:32 am
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
January 3, 2017 at 9:23 am
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. 🙂
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
January 3, 2017 at 9:57 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply