This post is a quick summary for all Business Analytics related updates that I saw at PASS Summit’14:
1. Theme of the Keynote(s)/Session(s) seemed to be around educating the community about the benefits of the NEW(er) tools. I saw demos/material for cloud-based tools like SQL databases, Azure stream analytics, Azure DocumentDB, AzureHDInsight & Azure Machine learning. The core message was pretty clear: A data professional does two things – 1) Guards data OR 2) helps to generate Insights from Data – And they will need to keep up-to-date on the new tools to future-proof their career.
4. Azure Machine Learning adds a free-tier! You won’t need a credit-card/subscription to sign up for this.
5. I also saw sessions proposing new way of thinking about an architecture for “Self Service BI” and “Big Data” which might be worth following because since these are newer tools, it’s definitely worth considering an architecture that’s designed to make the most of the investments in these new tools. That’s it & I’ll leave you with a quote from James Phillips from Day 1’s keynote:
Data on its own is just a bucket of potential. People convert data to opportunities – James #sqlpass#summit14
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.
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
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:
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:
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:
[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:
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:
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.
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.
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)
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:
After clicking on scan, after few seconds, I got a link to access the report:
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:
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”
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.
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. So Let’s get in action. Fire up SQL server management studio 2012 RC0:
Go To Object Explorer, Source Database, Tasks, Deploy Database to SQL Azure:
You’ll see deploy database wizard, click on next.
Here, you’ll need to specify the connection to Target Database which is SQL Azure. So click on connect.
Enter SQL Azure credentials and click on connect:
Note that I changed the Edition of SQL Azure, you can specify Edition and Max Size based on your need. Now, click on next
Here it would validate the source database and target requirements. If they result in Success, you’ll get to click on next.
In the summary page, just review the settings and click on Finish:
And on successful completion, you’ll see a message:
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.
Based on the details given in the “show technical details” – you’ll have to refactor the source database (SQL server) and then try again.