How do you reduce the network “latency” between application and SQL Azure?

Standard

I was at SQL Rally recently (10-11 may 2012) where I happened to have a nice talk about SQL Azure with a fellow attendee. They were considering porting their database (that supports one of their apps) to Microsoft’s cloud service. One of the concern they had was “How to reduce the network latency between SQL Azure and their App?” And Since I knew the solution, I shared it with that person. I am sharing it here so others can benefit too.

Now one of the first question that I asked the attendee was: Are you also porting your app along with the database to Azure?

Turns out, They were considering to host the app on Azure cloud too. So technically that’s called a “Code Near” scenario – And in this case, the application and the database both *should* reside in the same data-center. if you do so, the network latency between your app and the database is minimal.

Now, if you have your app on-premise and you are considering SQL Azure, then select the data-center location that has the minimal network latency between your app and SQL azure. Technically it’s called Code-Far scenario I have written about one of the ways you can do so, here’s the URL: Testing latency between client and SQL Azure via client statistics in SSMS

That’s about it for this post.

Official Resource: SQL Azure and Data access

what’s the role of a “Master” database in a SQL Azure world?

Standard

One of the question that pops up in the head of first-time SQL Azure user is “What is Master Database doing in my server”. They try to delete/drop it. That does not happen. Fun fact: I tried dropping Master database when I had got my hand on SQL Azure for the first time. Any-who what we speculate is that  “OMG! I paying for this SQL Azure Master database!” Wait…No, read this:

You are not charged for the SQL Azure Master Database.

Ok Good news, But why does this “read-only” master database exist?

Here is the information I have used that is available via SQL Azure Master Database:

  • It has SQL Azure usage metrics
  • It stores logins for a given SQL Azure LOGICAL server (To manage Server-Level security)
  • To view list of all databases via sys.databases
  • To create databases via TSQL. You do that by logging into master database first.

What do use SQL Azure master database for?

That’s about it for this post.

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

paras doshi blog on facebookparas doshi twitterparas doshi google plus

Reports hosted on SQL Azure reporting (preview) accessed via Windows Phone, iPhone and Android phones!

Standard

Here are screenshots of reports hosted on SQL Azure reporting preview accessed via Windows Phone, iPhone and Android Smartphone. I knew one can access reports using browser and so just wanted to test them on Windows Phone, iPhone and Android.

Note: Report is just for demo purpose. So I have kept it very simple.

Here are the screenshots:

1. Windows Phone:windows phone sql azure reporting

2. iPhone:iPhone SQL Azure reporting

3. Android Smartphone:
sql azure reporting android phone

Note: I have not tested it but I have heard reports on SQL Azure reporting are accessible via iPad too. 

Simran Jindal confirmed that It works great on iPad too!

Conclusion: These opens up a whole new world of possibilities for businesses. Business users would be able to get access to reports from anywhere in the world. Think of reports that are built on top of Data Marts hosted on cloud. They are kept in sync with Data that resides on-premise (locally). We have exciting times ahead!

Resource:
Getting Started Guide for Report Readers (SQL Azure Reporting)
Guidelines and Limitations for SQL Azure Reporting Preview

Worried about Securing SQL Azure Data? SQL Azure and “Trust Services” could help.

Standard

A question that comes among often when we talk about SQL Azure: How can we secure SQL Azure data? or In other words: Can we encrypt SQL Azure Data? And the answer to that is that SQL Azure currently does not support encrypting data. It’s done at the application level and one of SQL Azure labs: Trust Services is something that you should check out if you have such a requirement.

Stepping back for a moment, it’s common to have concerns about security with SQL Azure. It’s a good thing to investigate what you are getting into and so if you are investigating options to secure your SQL Azure data – then thumbs up from me!

Now, I would share my experience with Trust services when i get a chance to explore it, meanwhile you can read more about it here:

http://www.microsoft.com/en-us/sqlazurelabs/labs/trust-services.aspx

http://blogs.msdn.com/b/buckwoody/archive/2012/03/27/sql-azure-and-trust-services.aspx

Just note:

Communication between SQL Azure and application is always encrypted. read more here:
Security Guidelines and Limitations (SQL Azure Database)

SQL Azure Beginner’s TSQL Task: Creating a basic stored procedure

Standard

Being New at something is scary! In Tech field, One of the ways you could NOT be NEW at something is to kick the tire’s of that tech for a while. To that end, Here are the few tasks that you could perform if you are new to SQL Azure – Kick SQL Azure’s tires for a while – write few TSQL on SQL Azure – And NOT be NEW at it anymore. Go Learn!

In this Part 1 of N, I am going to create a stored procedure that accepts a parameter with SQL Azure.

So I have a table that lists firstname, lastname of the customer along with location details. Here’s what it looks like:

querying the sql azure view

Resource: A Tutorial on Installing a sample database on SQL Azure.

And just note that I running the queries on SQL Azure management portal and not on SSMS. You can do so in SSMS too. But for this blog-post – I used the SQL Azure management portal.

So now we want to write a Stored Procedure which gives this information when it is called; Not only that, we want that stored procedure to return information about customers from the country that’s specified by the person calling the procedure. And such a stored procedure would empower the person who is going to call the stored procedure to filter the customer list based on the country of their choice. So let’s go ahead and write the code:

[sourcecode language=”sql”]

create procedure saleslt.spcustomerlocation @countryname nvarchar(50)
as
select firstname,lastname,city,stateprovince,countryregion from customertable
where countryregion = @countryname

[/sourcecode]

Now once we have created the stored procedure, It can be called by running the following code:

[sourcecode language=”sql”]

exec saleslt.spcustomerlocation ‘United States’

[/sourcecode]

And it would give us list of customers in United States like shown below:

running the sql azure stored procedure

So that’s about it for this post.

And Let’s connect! I Look forward to Interacting with you on any of these people networks:

paras doshi blog on facebookparas doshi twitterparas doshi google plus

A nice slidedeck on SQL Azure reporting: #SQLPASS Business Intelligence Virtual Chapter Slides and References by Simran Jindal

Standard

recently I attended a nice SQL PASS Business Intelligence Virtual chapter’s presentation on “SQL Azure reporting” by Simran Jindal. For anyone interested in the slide-deck, re-blogging the post – please go to: http://simranjindal.wordpress.com/2012/03/08/sqlpass-business-intelligence-virtual-chapter-slides-and-references/ ; Also the post has few great resources, check them out if you are interested in learning about SQL Azure reporting.

I have written level 100 articles before and here are the links if you want to browse through the basics first:

What is SQL Azure reporting?

Getting started with SQL Azure reporting

Do tables in a SQL Azure Database need to have a primary key?

Standard

Answer: No.

Though SQL Azure does need that a table has a clustered index. So to this end, Let’s write some TSQL code and cement this concept in our brain. So as a part of the test, we would create a table with no primary key – However we would certainly create a clustered index on one of the column. So let’s get started:

OK, so here’s the TSQL code to create a table named ‘InternationalStudentList’:

[code language=”sql”]create table InternationalStudentList (StudentName varchar(30),HomeCountry varchar(30), DegreeProgram varchar(30))
go
[/code]

Now let’s insert some data into this table. Here’s the query:

[code language=”sql”]
insert into internationalstudentlist values(‘Paras Doshi’,’India’,’Masters in IT and Management’)
go
[/code]

If you run the above query – you would get the error:

Msg 40054, Level 16, State 1, Line 1
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

So we know that a table in SQL Azure requires a clustered index, right? So, Let’s create one!
Let’s assume that the InternationalStudentList would be queried a LOT to answer the question: “List all StudentName whose HomeCountry is X” – So based on this information let’s create a clustered index on HomeCountry Column. Here’s the TSQL code:

[code language=”sql”]
create clustered index cix_internationalstudentlist_homecountry ON
InternationalStudentList(HomeCountry)
go
[/code]

Once a clustered index is created, Try inserting data again. And you would see that a row would get successfully Inserted!
Run a Select Command to verify that:

SQL Azure Result of a SQL select command

And as you can see, we were able to insert a row in a table. Remember that this table did not have a primary key But we did create a clustered index. The Goal of the Post is achieved here, But just want to show you the Query Plan for a Query that looks like:

[code language=”sql”]
select StudentName,HomeCountry,DegreeProgram from InternationalStudentList
where HomeCountry = ‘India’
go
[/code]

sql azure clustered index seek

Note that I have run this queries on Management portal for SQL Azure, You can run it in SSMS too. But the goal is to show you that we have clustered index seek and that’s one of the way to tune queries. Not going into details in this post – And That’s about it for this post. Your feedback is welcome.

And Let’s connect! Here are few people networks that I am active on:

paras doshi blog on facebookparas doshi twitterparas doshi google plusparas doshi linkedin

I Look forward to Interacting with you!