Step by Step guide to Export a SQL Azure Database to Azure storage via Import and Export CTP << Link to SolidQ blog

Standard

Import and Export (CTP) is an interesting SQL Azure feature that allows us to Export a SQL Azure database in form of a bacpac to Azure storage. And think of bacpac as the “zipped” version of schema and data in your database. It also allows us to import the bacpac to a SQL Azure database….Read more on SolidQ Blog: Step by Step guide to Export a SQL Azure Database to Azure storage via Import and Export CTP

How to make the best use of your SQL Azure Free Trial?

Standard

Let’s say you have signed up for a SQL Azure free trial and it allows you 1 web edition database of Max Size 1 GB for a Month. You want to make the best use of this resource to get the maximum out of it, don’t you? No worries – In this blog post, I’ll list tips and tricks which would help you get the maximum out of that 1 GB SQL Azure database in a given month.

One option is to install Adventure Works LT sample database for a month – you try connecting SSMS, run queries, create DB objects, run stored procedures and you realize that it is SQL server in cloud (SQL server vs SQL Azure) – And you used your 1 GB SQL Azure DB free trial for a month, fair enough – you did learn a few things and that’s good. Now, let me propose a slight change that will allow you to try features such as SQL Federations and SQL Data SYNC and still not go above the “free” usage limit.

Before I do that – it is important to understand this:

When you are given a 1 GB Web edition SQL Azure DB free trial for a month – it effectively means that you have “30” 1 GB web edition SQL Azure DB whose validity is one Day and this is so because – the SQL Azure billing is “on per day” basis.

image

Image Source: Getting started with SQL Azure – Part 3: Provisioning and Billing Model of SQL Azure << Paras Doshi

Note: The billing cycle is based on UTC format and not your “local” Timezone.

Now let’s take this one step further:

This billing model allows you to create “30” 1 GB web edition DB for 1 day – and you have exhausted your free trial cap.

Alternatively, this allows you to create “15” 1 GB web edition DB for 2 day – and you have exhausted your free trial cap.

Alternatively, this allows you to create “10” 1 GB web edition DB for 3 day – and you have exhausted your free trial cap.

……

And I hope – you got my point that you can spread those “30” 1 GB web edition DB in many different combinations.

Now, here is one way you can go about o spread your cap of “30” 1 GB web edition SQL Azure DB:

Number of Days
[Col1]
FeatureDetailsNumber of 1 GB web edition DB consumed per day
[Col4]
Consumption
[Col1 * Col4]
5– learn basics– connect via SSMS
– run TSQL commands
– Explore Management studio
– SQL Azure firewall
15/30
2– Data SYNCLearn cloud to cloud synchronization using 1 Hub SQL Azure DB and 2 Member SQL Azure DB311/30
2– Data SYNCLearn Enterprise to cloud synchronization using 1 hub SQL server DB and 1 SQL Azure DB113/30
2– Azure reportingLearn SQL Azure reporting115/30
1– COPY TSQL command-copy a SQL Azure DB using COPY command217/30
1-SQL Azure Import/ExportExport a SQL Azure DB to Azure storage blob and then import it back to a new SQL Azure DB219/30
1– SQL server to SQL Azure migrationtry various migration tools like SSIS, BCP, SQL Azure migration wizard to migrate SQL server DB’s to SQL Azure322/30
1– SQL Azure federationsWhen you SPLIT a SQL Azure DB, it will perform it ONLINE. that means a single split command equals two create db command and one drop command and so for that day – you are charged for three db’s. Read more here527/30
3– Anything you may want to learnyou have “3” db’s to learn any other feature130/30

Now, this is not the optimum plan but it does give an idea to plan your free trial usage. I’ll tell you why the above plan is not optimum. For instance, when you try COPY command – you have two databases to play with for the rest of the day and you can do bunch of stuff with it. Other being, Once you have migrated the local SQL server db’s to cloud – you have those db’s to play with for the rest of the day. (Again, a billing day is counted based on UTC format. be aware!)

So yes. This is it! you can tweak the above plan based on your need and time you may have. feel free to post your suggestions in the comment section.

Summary of Just Learned Tips that i have submitted for year 2011 at BeyondRelational.com site

Standard

Just learned is a section in the BeyondRelational site that is meant for members to post a nuggets (short) of knowledge. I like this section – and so time to time i open the section and browse through the posts and once in a while, contribute. Here is the list of tips that i submitted this year:

1. [SSIS] Execution of an SSIS package that has Excel Desti/Source in 64bit SQL server will throw an error and Workaround for this

2. In windows server 2008 R2, IE asks you to add each website you visit to allowed site list. I learned how to override it for my demo machine.

3. Just learned how to allow file downloads in IE in Windows server 08 R2

4. SQL Server PowerPivot for SharePoint not available with standalone sharepoint

5. SQL Azure’s next service release will take the first step towards having common feature set between cloud and SQL server Denali!

6. Deploying azure web roles just got faster with “Windows Azure Accelerator for Web Roles”

7. Windows Azure toolkit for social games – makes developing social games on windows azure platform just got easier and quicker

8. General – Lorem ipsum has no meaning and just used this phrase to redirect attention

9.[Azure] a MSDN subscriber gets an alert email in case the Azure usage crosses the allowed usage

10. Press F11 on IE and Firefox to view web pages in full screen mode

11. never let your domain name expire

12. [Azure] One can map (unfriendly) azure storage domain to a custom domain name (Easily)

13. The new recognition system on MSDN, Technet and Expression profile is powered by windows Azure!

14. Posting code in wordpress.com

15. [Azure] Charges dropped for ingress (inbound traffic)

16. [Azure] One can combine a web role and a worker role

17. SQL Azure – currently, Maximum 6 servers are supported per subscription

18. Azure will charge you until you delete your hosted service

19. Atleast 2 instances of a role are required to meet the Azure SLA requirements

20. a DMV [sys.dm_db_partition_stats] in SQL Azure to calculate size of DB

Thank you for the Gift: Windows Phone and Klout!

Standard

Windows Phone just ran one of the best klout perk ever! So the way it works is that based on the score on Klout (which is a service that measures your online influence) – some people including me were invited to a Windows Phone Launch Party and they were also gifted a Windows Phone.

Here’s my Gift:

Klout Perk: Windows Phone!

And I was eligible for this perk as i am (as per klout) an influencer on Microsoft Technologies (in Dallas, Texas area).

So,

Thank you Windows phone for a great party and gifting me a phone that i like a lot!

Part 10/10 of “Getting started with SQL Azure” series is live!

Standard

Part 1: We defined SQL Azure and discussed advantages of SQL Azure

Part 2: We created an Azure account and created our very first SQL Azure database

Part 3: We discussed about the provisioning and the billing model of SQL Azure

Part 4: We discussed the SQL Azure architecture

Part 5: We discussed the SQL Azure security model

Part 6: We discussed how to migrate Databases to SQL Azure

Part 7: We discussed how to improve performance of SQL Azure DB and options for planning backup and restore strategies.

Part 8: We discussed administrative tasks related to SQL Azure.

Part 9: we discussed about Developing SQL Azure applications.

Part 10 A: We discussed about SQL Azure Data SYNC and SQL Azure reporting

Part 10 B: Conclusion

Upload a file to windows azure blob via “data transfer” tool

Standard

In earlier blog post, we talked about how to upload excel file to SQL Azure via the Data transfer tool found in SQL Azure labs. In this blog post, we will see how to upload an excel file to windows azure blob via this tool codenamed Data Transfer. So let’s get started.

1. Go to https://web.datatransfer.azure.com and select the second option – Windows Azure Blob:

image

2. Provide the credentials of your storage account (I checked the URL, it has https) – And provide account name in the format.blob.core.windows.net , container name and if the container of that name does not exist than it would be created, and the key which you can find from the storage accounts in Azure management portal. click on next.

image

3. now point the tool to the file you wish to upload and click on Import.

image

4. So that’s it Done! you will find the files you uploaded on My Data > Blobs Tab.

image

And you can view the file through My Data tab. (excel file will be downloaded and viewed via excel on your machine)

Note:

And during my experiments, I also tried uploading a .txt and .xml files. It works too!

image

 

Overall, Data Transfer makes our life easier by providing a web interface to upload data to SQL Azure and Windows Azure blobs.

Upload CSV and excel files to SQL Azure via “Data Transfer” tool

Standard

[UPDATE 9/12/2012: The URL’s that point to the service does not seem to work. The service may be out of “lab phase” and hopefully we would see this integrated in Portal or as an Independent service.]

Microsoft recently announced a tool (which is in CTP) called “Data Transfer” on SQL Azure Labs. It let’s you transfer CSV and excel data to SQL Azure or Windows Azure blob. I think this service would make our task easier than before. Earlier, We had the option to use SQL server integration services (SSIS) or BCP to transfer excel files or CSV files to SQL Azure. It was straightforward too but what if we had the tool in which you just point it to your excel/csv file and it would do try to do the rest. wouldn’t that be easier? Yes, so Data Transfer aims to achieve exactly that. To test drive Data Transfer tool go to https://web.datatransfer.azure.com/

In this blog post, I will show you how to upload an excel file to SQL Azure via this tool:

1. After you have registered yourselves (via a Live ID) on https://web.datatransfer.azure.com/ – you will see

image

2. In my case, since I wish to transfer an excel file to SQL Azure, I am going to go with first option i.e. Microsoft SQL Azure

3. In the next step, provide the credentials of a SQL Azure DB (that exists already)

image 2

You have the option of saving a connection. And the drop down that you see in the above image will let you see all your saved connection.

4. In the next step, point to your file and select appropriate options via the Advanced options which has the File Encoding, Column Delimiter, Row delimiter and Text Qualifier options. So this are useful if you had a flat file with {tab} instead of {,} to separate column values. In my case, I am going to leave the advanced options to their default values. Point to the excel file and since my first row has column names – I am going to check the box that says “column names in the first data row” and then click on analyze.

image

5. if you had not checked the “Edit table defaults” box, then Done! you can view the status of the process from the My Data tab. It’s this easy.

If you checked the Edit table defaults, the next page would allow you to edit the data-type. The power of this tool resides in the fact that it guesses the data-type and assigns it to the data that we had specified. And if you want to over-ride the data-type chosen by the tool, you can do so here at this page:

BTW: I find this amazing!

Edit table defaults

6. Click on save and the data is ready to be uploaded. it’s that simple. And after the upload, you can view the details here on the “my data” page:

image

7. You can log into the SQL Azure manage portal and browse the tables. And after upload, the link to “manage” these tables will also be available in the “my data” page.

image

Conclusion:

As you imagine, the process is simple and “Data Transfer” tool makes it easier to upload CSV/Excel file to SQL Azure. And it also let’s you upload a file to Azure blob storage account. Give it a spin! URL:  https://web.datatransfer.azure.com/

And learn more about it here: http://www.microsoft.com/en-us/sqlazurelabs/labs/datatransfer.aspx

part 9 of “Getting started with SQL Azure” series is live!

Standard

Aim of “Getting started with SQL Azure” series is to offer you a set of brief articles that could act as a Launchpad for your to-be wonderful journey of exploring Microsoft’s cloud based database solution i.e. SQL Azure.

In part 9, I have discussed about developing SQL Azure applications with following subtopics:

– Partially Supported and not supported TSQL statements

– SQL Server features not supported by SQL Azure

– Tools that have support to connect to SQL Azure:

– How can clients or Applications connect to SQL Azure?

 

URL: http://beyondrelational.com/blogs/parasdoshi/archive/2011/12/05/getting-started-with-sql-azure-part-9-developing-sql-azure-applications-lt-lt-paras-doshi.aspx

 

Summary:

Part 1: We defined SQL Azure and discussed advantages of SQL Azure

Part 2: We created an Azure account and created our very first SQL Azure database

Part 3: We discussed about the provisioning and the billing model of SQL Azure

part4: We discussed the SQL Azure architecture

part 5: we discussed the SQL Azure security model

part 6: We discussed how to migrate databases to SQL Azure.

Part 7: We discussed how to improve performance and backup options in SQL Azure

Part 8: we discussed about Administrative tasks with SQL Azure.