I did not quite understand the second point. Sorry if I am a bit dense. If I cannot access Azure storage to run bcp utility, and I need to have an application to move the data from azure storage to SQL Azure, I really would not need to have bcp running on
the cloud. Am I missing something here?
I came across this
article that talks of loading data to SQL Azure. One of the scenarios presented here, is having data located inside Windows Azure and BCP is used a tool to load the data.
I am trying to find out more if Azure storage can be used along with bcp and if yes, how that could be achieved.
Thanks for the URLs you provided. I am evaluating DAC PAC too along with other options - SSIS, Data Transfer among others. Before zeroing in on any one of them, I want to understand how BCP can be used when the data source is in Azure and the target
database is SQL Azure.
I have not found any resource that provides information on this. Any help in this direction is appreciated.
To your initial question. I see no reason why you could not create a Windows Azure Worker Role (or a Windows Azure VMRole) that would execute your BCP scripts on a regular basis all within the Windows Azure environment. You certainly could get
the files from your Blob Storage and then execute a BCP script to take and load all of these into SQL Azure. As an alternative, you could create a similar application on-premises that would download files from Blob storage and then BCP them up to SQL
Azure. I am pretty sure this second alternative is not what you would want to do since it would be incredibly innefficient to download the files from Azure and then BCP them back up.
For the first option, it would be by far the most efficient, but also the most costly because you would need to pay for a small instance of Windows Azure.
BCP and openquery not supported by Azure , at least 1 year ago that was the case.
The feature is not enable , and I do not believe that sp_configure can run and configure it in Sql Azure.
Suggest , to add all the data in local database, with bcp , or whatever script or feature you want , and then create and upload your db to Azure.( you can use Management Studio to create the Upload Script.)
If database in Azure Already exists , then use the Sync feature to sync your data with the on premise db.
There are a lot of options available , I believe this is the easier way.