Month: July 2011

Top two ways you can screw up the established SQL Azure connection from SSMS 2008 R2 running locally

Standard

I am able to connect to SQL Azure database through SSMS (SQL server Management studio) 2008 R2 running on my machine. What I want to do is to tinker with the configuration and create a situation where I am not able to connect to SQL Azure DB through SSMS 2008 R2. Simple, eh?! Destructive – oh yeah!

Now that I know I can connect to SQL Azure through SSMS, let’s tinker it so that I am not able to connect to SQL Azure.

image

First thing we are going to do is block our very own machine through the great wall of china  SQL Azure Firewall. So connect to the Master database and let’s see the firewall rules:

image

you can see that, my SQL Azure DB allows connection from all possible IP ranges. FYI: this is my test account. In big bad world, it’s not a recommended practice. be careful. Anyways, since we do not want ourselves to get connected to the SQL Azure DB, we will go and delete the firewall rule named “all” that allowed IP range 0.0.0.0 to 255.255.255.255 to connect to SQL Azure DB.

so I ran this command on my Master database via principal server login:

exec sp_delete_firewall_rule N’all’;
go

Now, try connecting to the SQL Azure DB again. you will get an error message: “cannot connect to [randomstring].database.windows.net”

image

So remember:

image

Now I enable “ALL IPs” to access the server and here I try reconnecting to SQL Azure:

image

It runs! Now, let’s do some other type of tinkering and see what are the other ways in which we can spoil our connection to SQL Azure DB from SSMS.

Open, SQL server configuration manager –> SQL server network configuration  –> Disable TCP/IP.

When you alter the status from “enabled” to “disabled”, you are then asked to restart the SQL server. Please do so!

Now, try reconnecting to the database – and thou shall be greeted with an error:

image

Note that to connect to SQL Azure DB, we should make sure that port 1433 (TCP IP) is open for outbound access

Thus remember:

image

There’s couple of more ways:

TURN OFF YOUR INTERNET CONNECTION. Just kidding!

And Uninstall the latest version of “recommended” SSMS and install the SSMS from pre-R2 era. Just kidding again!

 

Note:

*THE STUNTS ARE PERFORMED BY EXPERTS – PLEASE DO NOT TRY THIS AT HOME PRODUCTION ENVIRONMENT*

 

BTW the goal of the article was to show you that to connect to SQL Azure, you should have

1) The recommended version of SSMS

2) Correct SQL Azure firewall rule settings

3) TCP/IP enabled on local machine

 

Related Posts:

http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connectivity-troubleshooting-guide.aspx

http://parasdoshi.com/2011/07/06/adding-deleting-updating-the-sql-azure-firewall-rules-via-ssms/

SQL Azure Firewall: An unique security paradigm of SQL Azure << Paras Doshi

An article about Windows Azure Data Market and open government data initiative << SolidQ Journal article

Standard

image

Image Source: http://www.whitehouse.gov/open

if USA president dedicates his energy and attention to a particular initiative then it’s worth noting about it, isn’t it?! Here i am referring to Open government data initiative (OGDI). The idea is to make data (Information) publicly available. Simple concept, Eh?! but it has it’s own set of challenges. One of the challenge is a centralized vault where information can live. To address this issue, Microsoft has a wonderful initiative called Windows Azure Data market which promotes sharing public data via cloud. Windows Azure Data Market  is a “market” to trade data; yes, you read it right! it is a place where information/data-set/data is just a click away albeit some data may cost money – after all it’s a Market! There are lots of “free + valuable” data sets too! check it out: http://www.microsoft.com/windowsazure/marketplace/

To know more about Microsoft’s Windows Azure Data market and how open government data initiative can affect us positively and change our lives for better. read: Give Us Your Data! Windows Azure  DataMarket and the Open Government Data Initiative by Miguel Lopez, SolidQ

Read SolidQ Journal: http://www.solidq.com/sqj

Migrating Data from SQL server to SQL Azure using SSIS

Standard

I have written a BlogSpot on migrating data from SQL server to SQL Azure using SSIS on my BeyondRelational Blog. URL: How to use SQL server Integration services (SSIS) to migrate data from SQL server to SQL Azure << Paras Doshi

SQL Azure July 2011 service update has been announced!

Standard

wow! SQL Azure is evolving fast! year 2010 saw FIVE service updates! and in 2011, we saw a service update in may 2011 and the July 2011 is almost here!

image

 

So, what’s new in SQL Azure  in July 2011 SU?

1. SQL Azure co-administrator support has been added

2. Creating firewall rule for server with IP detect has been added

3. There has been an Engine upgrade and that means increased performance and scalability!

4. Enhanced spatial data types support

 

This Update is also takes a first step to include features of upcoming SQL server Denali. Read official announcement and do not miss  to upgrade SQL server management studio; URL: http://blogs.msdn.com/b/windowsazure/archive/2011/07/13/announcing-sql-azure-july-2011-service-release.aspx 

 

PS:  I help manage http://beyondrelational.com/whatisnew/sqlserver/azure.aspx which is aimed at keeping a tab on new SQL Azure updates. check it out!

it’s story time!

Standard

Once upon a time, there was a geek. He loved playing with new technology. And one day he decided to play with Windows Azure, Microsoft’s cloud computing platform. He was happy as he was learning new things and clearly (as the picture shows) was very excited!

 

image

 

After few days, he got a credit card bill and he was surprised (Shocked rather) to see an entry that said 150$ and further inspection showed that it corresponds to Windows Azure subscription.

image

 

No wonder, he was mad! he cursed the service and was in no mood to investigate what had really happened. hurriedly, he posted defamatory comments on Forums (Plus Twitter Plus  Facebook plus other forums plus wait….Circles).

image

 

After a while, he calmed down and opened the MOCP (Microsoft online customer portal) site  to view details regarding his bill. he realized that he was charged money for a Azure web role. 

Now, he went to the Azure Management portal and saw that he had already stopped the role. Now, he was confused that why was he still charged? later, after taking his case to support guys plus few helpful comments in Windows Azure Forums, he figured that he was charged since he had not stopped the role. To stop being charged, he needed to delete the deployed service.

he realized that even though, he had stopped the service, the service was still on Azure machines and the compute resources that were assigned to his service could not be assigned to some other subscriber and so he was being charged. So he learned a very important lesson that day:

 

image

 

*********STORY ENDS****************

Based on some (real life) questions asked regarding this issue on Windows Azure forums. I have also seen people first cursing the service to no end without understanding what really happened. Later, when they get the refund (in legitimate cases) they apologize. In the process, they manage to harm their reputation and embarrass Microsoft in the process. I just wish they knew that all they had to do was delete the service when no longer required. That’s it !

A MSDN subscriber get’s an alert message if the Windows Azure platform usage exceeds the base (not chargeable) consumption

Standard

I have subscribed to Azure account using the MSDN subscription offer. Recently, I got an email that said that my Azure usage exceeds the base consumption. So as soon as possible, I deleted the role and thus saved few bucks! Thank you for having such amazing alert mechanism.

Screenshot of the email:

azuremsdnemail

SQL Azure Provisioning model

Standard

image

 

-> An account per one LIVE ID can be provisioned

-> An account can have multiple subscriptions

-> Maximum 6 SQL Azure server per single subscription can be provisioned

-> 150 SQL Azure databases can be created under a single server. Note that it is a soft limit. Have a look at: http://social.msdn.microsoft.com/Forums/en-US/windowsazurepurchasing/thread/40c99af7-c87f-4936-94d7-b777d7fe5a0b/

 

Related Posts:

BOL: SQL Azure Provisioning Model

Now a two star member of MSDN Windows Azure Platform Forums

Standard

Just got an extra badge on WAP MSDN Forum!

MSDN Profile: http://social.msdn.microsoft.com/profile/paras%20doshi/?type=forum

It’s one of the best technical forums that provides everyone an excellent platform to learn and share! For me, It has been a couple of great months. I remember asking a few SQL Azure based questions in April 2011 and then i found myself answering questions that were invariably based on SQL Azure and Azure in May – June 2011 and yes, i hope to continue!

On 5th July 2011:

image

Creating Co-administrator’s for managing SQL Azure databases and servers through Azure management portal

Standard

I have written a blog post in my BeyondRelational.com Blog about how to add a co admin to manage SQL Azure databases and servers through Azure management portal. Read:

Creating Co-administrator’s for managing SQL Azure databases and servers through Azure management portal << Paras Doshi

My article on “Tuning SQL Azure database – Part 1” got published in SolidQ Journal (July 2011) << Paras Doshi

Standard

My article on “Tuning SQL Azure database – part 1” got published in SolidQ journal July 2011 Issue. The editorial of the Journal written by Fernando G. Guerrero [Global CEO, SolidQ] summed up the article by following words: "Even if cloud computing hides some back-end complexity from us, databases still need to be properly designed and queries properly written. To this end, Paras Doshi explains how to tune SQL Azure databases".

LINKs:

Read : Tuning SQL Azure Database – Part 1 by Paras Doshi 

Download: Tuning SQL Azure Database – part 1 by Paras Doshi

Download: SolidQ Journal July 2011