BCP inside Azure

Question

Hello,

I need to transfer data to SQL Azure database. I am looking at BCP as a possible method. I am quite familiar with using BCP, when the data files are located on-premise. The questions I have are,

1. Can BCP be used from within Azure?

2. Can I have files in Azure storage and have BCP utility access them?

3. What are the pre-requisites to be able to use BCP within Azure? What are the components to be installed?

Thanks for the help

Regards

Vani

Answer 1

1. Yes, you can

2. Transfer of data between SQL Server instance and SQL Azure not supported

   Because BCP is a bundle utility for SQL Server(Azure) and can not access Azure Storage directly

   I think you need to develope proper application for transferring data from Azure Storage to SQL Server(Azure)

   You will need to look up StorageClient library
   http://msdn.microsoft.com/ko-kr/library/microsoft.windowsazure.storageclient.aspx

3. You only need excutable file (BCP)
If you need more detailed information, please follow the link below
http://msdn.microsoft.com/en-us/library/ms162802(SQL.105).aspx

I hope my answers helpful for you

Answer 2

Algorizm,

Thanks for the reply.

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. 

Any help is appreciated.

Regards

Vani

Answer 3

How about using DACPAC and SQL Azure import export

http://sqldacexamples.codeplex.com/wikipage?title=Import%20Export%20Service%20Client&referringTitle=Documentation

http://dacguy.wordpress.com/

---

-Sachin Sancheti

Answer 4

Sachin,

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.

Regards

Vani

Answer 5

Hi Vani,

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. 

---

Cotega - SQL Azure Monitoring

Answer 6

Thanks for the reply and detailed explanation.

Regards

Vani

I apologize for the late response. I was out of office for quite some time

Answer 7

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.

---

P. Velachoutakos