Azure PASS VC Next meeting: Kung Fu Migration to Windows Azure SQL Database

Azure PASS VC’s next meeting:

Kung Fu Migration to Windows Azure SQL Database

Speaker: Scott Klein, Technical Evangelist Microsoft

Summary: As cloud computing becomes more popular and cloud-based solutions the norm rather than the fringe, the need to efficiently migrate your database is crucial. This demo-filled session will discuss the tips and tricks, methods and strategies for migrating your on-premises SQL Server databases to Windows Azure SQL Database, AKA SQL Azure. Focusing primarily on SQL Server Data Tools and the DAC Framework, this session will focus on how these tools can make you a kung-fu migration master.

About Scott: Scott Klein is a Corporate Technical Evangelist for Microsoft focusing on Windows Azure SQL Database (AKA SQL Azure) and related cloud-ready data services. His entire career has been built around SQL Server, working with SQL Server since the 4.2 days. Prior to Microsoft he was a SQL Server MVP for several years, then followed that up by being one of the first 4 SQL Azure MVPs. Scott is the author of over ½ dozen books for both WROX and APress, including Pro SQL Azure. He can be found talking about Windows Azure SQL Database and database scalability and performance at events large and small wherever he can get people to listen, such as SQL Saturday events, local SQL Server user groups, and TechEd.

Details at http://azure.sqlpass.org/

Download the calendar file: http://www.sqlpass.org/iCal.aspx?EventID=245

How to Join Azure PASS VC’s?

If you want to stay updated on meeting announcements, please consider registering on PASS’s website and Joining our VC:

If you do not have a SQLPASS account:

a. Go to http://www.sqlpass.org/RegisterforSQLPASS.aspx

b. Fill up the required information and register

Now, After successful login/registration – Go to http://www.sqlpass.org/MYPASS.aspx

a. switch to MyChapters section

b. Now under virtual chapters, you would see a list of virtual chapters. Join the one’s you are interested in!

my PASS my Chapter Azure VC

I look forward to seeing you at next Azure PASS VC’s meeting!

Hadoop on Azure’s Javascript Interactive Console has basic graphing functions:

The Hadoop on Azure’s Javascript console has basic graphing functions: Bar, Line & Chart. I think this is great becuase it gives an opportunity to visualize data that’s in HDFS directly from the Interactive Javascript Console! Here’s a screenshot:

hadoop on azure bar and line graph javascript

In the console, I ran the help(“graph”) command to see how I can use this function:
Draw a graph of data
graph.bar(data, options) Bar graph
graph.line(data, options) Line graph
graph.pie(data, options) Pie chart

Parameters
data (array) Array of data objects
options (object) Options object, with
x (string) Property to use for x-axis values
y (string) Property to use for y-axis values
title (string) Graph title
orientation (number) x-axis label orientation in degrees
tickInterval (number) x-axis tick interval

Conclusion:

In this blog-post, I posted that Hadoop on Azure’s Javascript Interactive Console has basic graphing functions.

Related articles:

we now have 3 (three) options to run SQL server on CLOUD.

Following the announcements at “Meet Windows Azure” event, we now have three options to run SQL Server on CLOUD; They are:

1. SQL Azure which is now called Windows Azure SQL Database

2. SQL Server on Windows Azure VM Roles (Nice addition, in my opinion!)

3. SQL Server on Amazon Web Services RDS

And apart from these options,

if you can fire up a VM on cloud and decide to run SQL Server on it – that’s also SQL Server on CLOUD.

Update 22 June 2012:

Naveen commented about running SQL Server on Amazon EC2.

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

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:


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

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


exec saleslt.spcustomerlocation 'United States'

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

Great to see neck to neck price competition between Azure and AWS

[I am referring to price reduction announcements by Azure and AWS in Early March 2012]

This week, Microsoft announced price reduction for Windows Azure compute and storage. Read more here – And even AWS announced price reduction too. Read more here. So it’s great to see neck to neck competition between Azure and AWS. And this is what is great about an economy that is not “monopolized”. To sustain, both Azure and AWS need to compete PLUS they compete with other cloud vendors that are out there too. Hopefully, other cloud vendors would also come up with an aggressive pricing strategy. Also, such competition among vendors would spur innovation and we would see “more features” coming out their factories more often than before (Yay!). And guess what, Who’s the winner? WE the CUSTOMERS!

Also, I noticed a pattern in both announcements that said “We are glad to pass along the savings to customer”. This seems to pointing to the fact that since cloud computing adoption has increased and cloud vendors benefit from Economies at scale – They pass the savings to the customers. And this acts as a catalyst for more adoption!

This seems like an awesome circle:

cycle of cloud vendor price reduction and adoption

I understand that CLOUD ADOPTION is just not triggered by “price reduction”. That’s not what i mean here. But price reduction can sure act as catalyst. Hopefully, in not so distant future, the cloud prices would be so lucrative that setting up private data-centers would be a “thing of past” (unless you are governed by laws to have your own data-center or some other policy that restricts you from cloud adoption).

And I am also not comparing the price reduction “directly” because it would be like comparing Apples and Oranges. But what I hoped to point out was that we as customers would see more price reduction, more features, better experience because of the neck to neck competition among cloud vendors.

And That’s about it for this post. Your feedback is welcome!

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 plusparas doshi linkedin

SQL Azure got a new pricing model!

Summary First:
1. New 100 MB Database Option
2. Significant Price reduction for Database sizes greater than 1 GB!
3. Note the Changes in Billing increments

The new pricing model is:

Database Size Price Per Database Per Month
0 to 100 MB (Web) Flat $5.00
Greater than 100 MB to 1 GB (Web) Flat $9.99
Greater than 1 GB to 10 GB (Web/Business) $9.99 for first GB, $4.00 for each additional GB
Greater than 10 GB to 50 GB (Business) $45.99 for first 10 GB, $2.00 for each additional GB
Great than 50 GB to 150 GB (Business) $125.99 for first 50 GB, $1.00 for each additional GB

Previous pricing model was:

Database Size Price Per Database Per Month
0 to 1 GB (Web) $9.99
Greater than 1 GB to 5 GB (Web) $49.95
Greater than 1 GB to 10 GB (Business) $99.99
Greater than 10 GB to 50 GB (Business) $99.99 for first 10 GB, $99.99 for each increment of 10 GB
Greater than 50 GB to 150 GB (Business) No Charge

FYI: Max DB size of Web Edition: 5 GB; Business Edition: 150 GB.

News source: http://blogs.msdn.com/b/windowsazure/archive/2012/02/14/announcing-reduced-pricing-on-sql-azure-and-new-100mb-database-option.aspx

Review your SQL Azure security issues and Attack surface, Check out “SQL Azure security services”

There’s an interesting SQL Azure lab in public preview called “SQL Azure security services”. It let’s you review security issues and Attack surface of your SQL Azure database. To give you a taste of the usefulness of this service, following is information that you’ll find for specified database(s)/server:

- A List of security issues. For e.g.: A particular User Name has read permissions on ALL  tables/views

- List of Usernames in a database along with database permission for each user

- List of Roles along with it’s members

- List of all user-created database objects

 

Useful? Interesting? continue reading to know more..

In this blog-post, first I would give a step by step tutorial on how to start using it and then walk you through a report that I generated for a sample database on my SQL Azure server.

This is the home page of “SQL Azure security services”: http://www.microsoft.com/en-us/sqlazurelabs/labs/sqlazuresecurityservices.aspx .To get started, you can watch a video tutorial here but if you prefer to read, let’s gets started:

First go to https://labs.web.sqlsecurity.azure.com/scanner.html ; This is the place to access SQL Azure security services. Bookmark (save) it!

Now here you’ll be asked to login using SQL Azure credentials. I am going to login as service administrator. you’ll need to login using credentials that has access to databases in a server and select permission on tables for databases that you want to inspect.

1

Click on next and here you would be asked to select between scanning the complete server vs scanning individual databases. I am going to scan a complete server (which has just one database)

2

Ideally, if you are scanning the entire server then storing the html report in an Azure storage account is the way to got but I just have one database in the server that I specified and so I know it would not take long, so I opted for HTML output to browser:

 

3

After clicking on scan, after few seconds, I got a link to access the report:

image

And I would open that report (it’s in HTML format) in the browser.

The first tab is “Security issues” and it would look like:

image

here , you can drill down a particular issue and view Description along with recommended mitigation.

So basically, what the above report is saying me is that I have a username indiawebdev (which I purposefully created for demoing) which has select permission on all tables/views in the sample database AdventureWorksLTAZ2008R2.

And it suggests me to grant minimum set of permission on various objects.

Now let’s go to the second tab “Attack Surface”

image

Quick note: Since I had opted to “scan complete server” earlier, I am getting the server level information like Logins, server roles, databases but if I opted for “scan individual database” then the server information would not be provided.

Any-who let’s drill-down a little bit see what information is made available to us:

For my Database AdventureWorksLTAZ2008R2, I can see a list of usernames along with login name, type, database permissions. So with the help of this information we can review all usernames and see if there are any “unwanted” users. And also it let’s us review the permission granted to each username at db level.

Also, it lists all roles along with it’s members and this information can be used to verify that a member is granted the minimum level of permission.

7

 

It also lists all user-created database objects along with schema/type, owner information.

Now from server information, we can use the information in the Logins information to find “unwanted” logins.

It also lists the server roles by Name along with it members so here we can see if members belong to desired roles.

And it also lists Databases with it’s database owner.

So that was about it on exploring the report. Check it out! Review Attack surface of your SQL Azure server and become aware of the security issues.

Thus, in this blog post, We learned about “SQL Azure security services” in SQL Azure labs. We also saw a sample report and explored few parts of it. And I hope you got an overview of what SQL Azure security services has to offer as of today.

SQL server 2012 introduces a straightforward way to migrate to SQL Azure (via SSMS)

SQL server 2012 introduces a straightforward way to migrate to SQL Azure via SSMS. So Let’s get in action. Fire up SQL server management studio 2012 RC0:

image

Go To Object Explorer, Source Database, Tasks, Deploy Database to SQL Azure:

2

You’ll see deploy database wizard, click on next.

image

Here, you’ll need to specify the connection to Target Database which is SQL Azure. So click on connect.

4

Enter SQL Azure credentials and click on connect:

5

Note that I changed the Edition of SQL Azure, you can specify Edition and Max Size based on your need. Now, click on next

6

Here it would validate the source database and target requirements. If they result in Success, you’ll get to click on next.

image

In the summary page, just review the settings and click on Finish:

And on successful completion, you’ll see a message:

image

So that’s about it. your SQL Server database is migrated to SQL Azure!

At the end, Just few notes:

1. Under the hood, it uses BACPAC.

2. If there is Feature/TSQL incompatibility between source (SQL server) and Target (SQL server), you will get error in the validation step. In such cases, click on error, it would open a wizard, here click on technical details to know more.

error

Based on the details given in the “show technical details” – you’ll have to refactor the source database (SQL server) and then try again.

3. There are other alternatives to migrate to SQL Azure, check those out: Summary – SQL server to SQL Azure migration

10 Beginner Level Windows Azure Interview Question and Answers (Q&A)

In 2011, I had written a Guest post on Pinal Sir’s blog about SQL Azure Interview Q&A – It was fun, so this time around I put together a 10 beginner level Windows Azure Questions.

They are on BeyondRelational.com and apart from Azure Questions, you would also find Interview Q&A’s on SQL server, .Net, etc..

Here are those 10 interview questions, click on the question to read its answer:

What is cloud computing?

What are the three main components of Windows Azure Platform?

Which are three types of roles in Azure compute?

what is web role in Windows Azure?

what is worker role in Windows Azure?

what is VM role in Windows Azure?

Apart from.Net framework, Name other three language/framework that can be used to develop Windows Azure applications?

Storage Emulator?

Compute Emulator?

What do you mean by Windows Azure Fabric Controller?

And one more..

Currently, How would you categorize Windows Azure?

That’s about it!

[cloud 101] Is cloud secure?

I got this question recently and I thought of converting them into a “cloud 101″ blog posts. So here they are:

Q: “Banking – with its high security needs and strict regulations – was always considered to be one of the last industries to accept cloud-computing”. So is cloud computing insecure?”

A: I would say – Cloud is secure. At least companies like Amazon (AWS), Microsoft, Google have advanced security mechanisms in place – In most cases, Cloud is more secure than private data-centers. And some banks do not move to Cloud (or cannot move to cloud) because of other reasons. And most of them fall on the legal side of things. Strict regulations (government regulations) as you mentioned.

And later at one point, we discussed about banks keeping parts of the data/app on-premise and moving other parts to cloud. That’s called hybrid cloud. What they do is that since they cannot move “sensitive” data to cloud because of government regulation and/or business policies – they move things that are permissible by law/business policies to cloud.

And we also discussed about what are the disadvantages of moving to cloud? I realized: Most of the perceived “disadvantages” are actually MYTHS. so at one point I felt the need to clear myths and then talk about other topics. It was fun though!

Resource:

Cloud Security Alliance