Wednesday, December 26, 2018

Azure Resource Explorer in Azure Data Studio

Using Azure Resource Explorer in Azure Data Studio you can now manage Azure SQL Server, Azure SQL database, and Azure SQL Managed Instance resources.
1. Open “Azure Data Studio” and an Azure icon appears in the left menu bar. Click the icon to open “Azure Resource Explorer”.
2. Sign into Azure
3. Click the “Add an Account” button and you will be shown with the Device Code and URL and click on “Copy and Open” button and it will open the URL and Type the Device Code.
4. Pick the Azure account to sign in
5. Once signed in, the below window will be shown.
6. Close the above browser window and go back to Azure Data Studio to see the below screenshot.
7. Expand the connection to the see the list of subscriptions and SQL Services under them.
8. Finally you can also add Firewall rule to allow access to the Azure SQL Server.

Azure Data Studio

Azure Data Studio is a cross-platform database tool for data professionals using the Microsoft family of on-premises and cloud data platforms on Windows, MacOS, and Linux. It was previously called as “SQL Operations Studio”. It has been engineered keeping Data Platform user in mind. Azure Data Studio has been designed to focus on commonly used functionality with any additional experiences made available as extensions.

Microsoft informs that “The vision of the product is to create a unified experience across heterogenous data sources regardless of their form or location: structured or unstructured, on-premises or cloud. Azure Data Studio currently offers built-in support for SQL Server on-premises and on the cloud and Azure SQL Database, along with preview support for Azure SQL Managed Instance, Azure SQL Data Warehouse and SQL Server 2019 Big Data Clusters.”

Use Azure Data Studio if you:
  • Need to run on macOS or Linux
  • Are connecting to a SQL Server 2019 big data cluster
  • Spend most of your time editing or executing queries
  • Need the ability to quickly chart and visualize result sets
  • Can execute most administrative tasks via the integrated terminal using sqlcmd or PowerShell
  • Have minimal need for wizard experiences
  • Do not need to do deep administrative configuration
Use SQL Server Management Studio if you:
  • Spend most of your time on database administration tasks
  • Are doing deep administrative configuration
  • Are doing security management, including user management, vulnerability assessment, and configuration of security features
  • Make use of the Reports for SQL Server Query Store
  • Need to make use of performance tuning advisors and dashboards
  • Are doing import/export of DACPACs
  • Need access to Registered Servers and want to control SQL Server services on Windows
Getting Started with Azure Data Studio

Download Azure Data Studio

1. Open Azure Data Studio
2. To connect to SQL Server, select
  • Connection Type: Microsoft SQL Server
  • Server: [Your SQL Server Instance name]
  • Authentication Type: [Windows Authentication/SQL Login]
  • User name: [username, if SQL Login]
  • Password: [password, if SQL Login]
  • Database: [choose the Database name you would like to connect]
  • Server Group: [Group servers under common heading]
3. Set any Advanced Properties if required.
4. Click connect and you can see a screenshot similar to below.
Technorati Tags: ,,,

Tuesday, March 06, 2018

Creating a Basic bot using Bot Service

In the earlier post we looked at how to create a bot using Visual Studio 2017, today we will see how to create a bot using Bot Service. Bot Service provides core components for building bots. This includes the Bot Builder SDK for developing the bots and Bot Framework for connecting the bots to the channels.
Bot Service provides 5 templates as shown below. In this post we can see how to create a Bot using the Basic Template and C# on Azure Portal.
Basic Template
Select the Web App Bot
Provide the following details for the Web Bot and Select the Basic Template and click “Create”
Once created you see see the endpoint for the BasicBotExample as shown below
You can also download the sample solution and start customising it.

Now the Basic Bot has been created successfully and let us check that by Testing it in Web Chat.

Introduction to Bots and Microsoft Bot Framework

bot_thumbWhat are Bots?
Bots are designed to solve common business problems using a natural, conversation-style approach combined with Machine Learning for Advanced Intelligence.
What are Chatbots?
A chatbot is a service, powered by rules and sometimes artificial intelligence, interacting with via a chat interface. (Skype, Microsoft Teams, Facebook messenger, Slack, Telegram)
Microsoft Bot Framework
The Microsoft Bot Framework enables organisations to build and deploy intelligent bots and provides tools to build and connect intelligent bots that interact naturally wherever the users are talking, from text/sms to Skype, Slack, Office 365 mail and other popular services.
The Bot Framework provides tools to easily solve these problems and more for developers e.g., automatic translation to more than 30 languages, user and conversation state management, debugging tools, an embeddable web chat control and a way for users to discover, try, and add bots to the conversation experiences they love.
The Bot Framework has a number of components including the Bot Connector, Bot Builder SDK, and the Bot Directory.
Creating your first Bot using Visual Studio 2017
Make sure the following pre-requisites are downloaded and installed successfully.
a) Bot Framework Emulator (
b) Download the Bot Application Template from and download the file and copy it to the Visual Studio Templates folder for C# (The location of the Templates folder can be identified using the below screenshot)
Creating your first Bot
  • Open Visual Studio 2017 as Administrator and click on “New Project” and select “Bot Application”
  • The Bot Application Project creates a fully functional bot named “Echo Bot”
  • Press F5 to confirm that the “Echo Bot” application runs successfully and you see the following screenshot.
  • Now let us invoke the EchoBotApplication endpoint using the Microsoft Botframework Emulator.
  • Just click on the “Connect” button and start typing the message “Welcome to your first Bot Application” and the bot will respond with the no. of characters of the input text.
  • Click on the message sent and you can see the corresponding JSON that was sent to the Bot and the same way you can see the JSON for the response also.
  • Now you have created your first bot.
Let us see how can we create more real world bots in subsequent blog posts.

Sunday, March 04, 2018

Azure Data and Analytics Platform

I am planning to write a series of blog posts to go through the services provided by Microsoft Azure Data and Analytics Platform in detail.Azure-Data-and-Analytics-Platform

Azure SQL Database Cloud Database as a Service. Azure SQL Database is a relational database-as-a service using the Microsoft SQL Server Engine. SQL Database is a high-performance, reliable, and secure database to build data-driven applications
SQL Server in Azure VM SQL Server on Azure virtual machines enables to use full versions of SQL Server in the Cloud without having to manage any on-premises hardware. SQL Server VMs also simplify licensing costs when you pay as you go.
Cosmos DB Azure Cosmos DB is Microsoft's globally distributed, multi-model database. Azure Cosmos DB enables to elastically and independently scale throughput and storage across any number of Azure's geographic regions. It offers throughput, latency, availability, and consistency guarantees with comprehensive Service Level Agreements (SLAs)
Blob Storage Azure Blob storage is a service that stores unstructured data in the cloud as objects/blobs. Blob storage can store any type of text or binary data, such as a document, media file, or application installer. Blob storage is also referred to as object storage
Table Storage Azure Table storage is a service that stores structured NoSQL data in the cloud, providing a key/attribute store with a schemaless design. Because Table storage is schemaless, it's easy to adapt the data as the needs of the application evolve.
Redis Cache Azure Redis Cache is a distributed, managed cache that helps to build highly scalable and responsive applications by providing super-fast access to the data.
Azure HDInsight Azure HDInsight is a cloud distribution of the Hadoop components from the Hortonworks Data Platform (HDP). Azure HDInsight makes it easy, fast, and cost-effective to process massive amounts of data.  With popular open-source frameworks such as Hadoop, Spark, Hive, LLAP, Kafka, Storm, R, and more, a broad range of scenarios such as extract, transform, and load (ETL), data warehousing, machine learning, and IoT can be enabled.
Azure SQL Data Warehouse SQL Data Warehouse is a cloud-based Enterprise Data Warehouse (EDW) that leverages Massively Parallel Processing (MPP) to quickly run complex queries across petabytes of data. Use SQL Data Warehouse as a key component of a big data solution. Import big data into SQL Data Warehouse with simple PolyBase T-SQL queries, and then use the power of MPP to run high-performance analytics
Azure Data Lake Store Azure Data Lake Store is an enterprise-wide hyper-scale repository for big data analytic workloads. Azure Data Lake enables to capture data of any size, type, and ingestion speed in one single place for operational and exploratory analytics.
Azure Data Lake Store can be accessed from Hadoop (available with HDInsight cluster) using the WebHDFS-compatible REST APIs. It is specifically designed to enable analytics on the stored data and is tuned for performance for data analytics scenarios. Out of the box, it includes all the enterprise-grade capabilities—security, manageability, scalability, reliability, and availability—essential for real-world enterprise use cases.
Azure Data Factory Azure Data Factory is a cloud-based data integration service that allows you to create data-driven workflows in the cloud for orchestrating and automating data movement and data transformation
Azure Data Catalog Azure Data Catalog is a fully managed cloud service whose users can discover the data sources they need and understand the data sources they find. At the same time, Data Catalog helps organizations get more value from their existing investments
Azure Search Azure Search is a search-as-a-service cloud solution that gives developers APIs and tools for adding a rich search experience over your content in web, mobile, and enterprise applications
Azure Event Hub Azure Event Hubs is a highly scalable data streaming platform and event ingestion service, capable of receiving and processing millions of events per second. Event Hubs can process and store events, data, or telemetry produced by distributed software and devices. Data sent to an event hub can be transformed and stored using any real-time analytics provider or batching/storage adapters. With the ability to provide publish-subscribe capabilities with low latency and at massive scale, Event Hubs serves as the "on ramp" for Big Data.
Azure Databricks Azure Databricks is an Apache Spark-based analytics platform optimized for the Microsoft Azure cloud services platform. Designed with the founders of Apache Spark, Databricks is integrated with Azure to provide one-click setup, streamlined workflows, and an interactive workspace that enables collaboration between data scientists, data engineers, and business analysts.
Azure Stream Analytics Azure Stream Analytics is a managed event-processing engine set up real-time analytic computations on streaming data. The data can come from devices, sensors, web sites, social media feeds, applications, infrastructure systems, and more.
Azure Data Lake Analytics Azure Data Lake Analytics is an on-demand analytics job service that simplifies big data. Instead of deploying, configuring, and tuning hardware, you write queries to transform the data and extract valuable insights. The analytics service can handle jobs of any scale instantly by setting the dial for how much power you need.
The analytics service supports includes U-SQL, a language that unifies the benefits of SQL with the power of imperative code.
Azure Machine Learning Azure Machine Learning is a cloud predictive analytics service that makes it possible to quickly create and deploy predictive models as analytics solutions. Azure Machine Learning not only provides tools to model predictive analytics, but also provides a fully managed service that can be used to deploy the predictive models as ready-to-consume web services.
Cognitive Services Microsoft Cognitive Services are a set of APIs, SDKs, and services available to developers to make their applications more intelligent by adding features such as facial recognition, speech recognition, and language understanding.
Bot Framework Azure Bot Service speeds up development by providing an integrated environment that's purpose-built for bot development with the Microsoft Bot Framework connectors and BotBuilder SDKs
Cortana Cortana is your truly personal digital assistant. Cortana is designed to help you get things done. Ready on day one to provide answers and complete basic tasks, Cortana learns over time to become more useful every day. Count on Cortana to stay on top of reminders and work across your devices.
Power BI Power BI is a business analytics cloud service provided by Microsoft. It provides interactive visualizations with self-service business intelligence capabilities, where end users can create reports and dashboards by themselves, without having to depend on information technology staff or database administrators.
Technorati Tags:

Thursday, January 04, 2018

Handy Azure Resources

These resources have been really handy for me when working on Azure projects.

Azure Services Reference


Cloud Adoption Framework

Well Architected Framework

Azure Architecture and Design Patterns

Azure Data Centers and Service Availability

The service availability matrix was really handy to understand which services are available in which regions so we can plan accordingly.

Azure Speed Test

Allows you to check the latency from your IP location to the Azure Data Center.

Azure Tools

Technorati Tags: ,,

Azure Tools – Azure Storage Explorer

Azure Storage Explorer is a free stand alone application that is available for Windows, Linux or Mac and connect to the storage account on Azure and to upload, download and manage blobs, queues, tables and Cosmos DB entities.

Download Azure Storage Explorer

Install the Azure Storage Explorer and connect to your Azure account as below.

Add an Azure Account. Options available in the Drop Down List are Azure, Azure China, Azure Germany, Azure US Government, Create Azure Stack environment.


Provide your credentials to connect to your Azure Account


Once connected, you can now see your Storage accounts as shown below


Navigate your storage account and do appropriate action you need to perform on them.


Great tool to just navigate your Azure Storage locally without the need of using the Azure Portal. I use the Blob storage to store my SQL bacpac files and this tool is quite handy to download them or perform other actions.

Technorati Tags:


ChocolateyChocolatey is an open source Package Manager for Windows. Package Managers are used for installing and managing multiple programs at the same time in an unattended manner. Chocolatey provides Universal approach for Software Management. Chocolatey uses PowerShell. Chocolatey comes from the community repository and it is open source and secure. It supports offline installation of software programs.

Install Chocolatey (Free version) from


Run the following command in an command prompt opened in Administrator mode

@"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString(''))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"


Packages on Chocolatey -

To install software use the below commands

choco install googlechrome –y (for Google Chrome)


choco install firefox –y (for Firefox)

choco install git –y (for Git)

Chocolatey is really great to provision Dev environments with common tools with great automation.

Technorati Tags:

Monday, January 01, 2018

Querying the SSRS 2017 REST API using Fiddler

Fiddler is a tool to test the API Request/Response messages. Let us see how can we use Fiddler to send and retrieve messages from the SSRS 2017 REST API.

URL format for the REST API - http://<ReportServer>/Reports/api/v2.0/<endpointname>

e.g. http://<ReportServer>/Reports/api/v2.0/MobileReports to get the list of all Mobile Reports from the SSRS Portal.

Swagger Documentation -

Note: The SSRS 2017 REST API is OData compliant and if you need to learn about OData queries you can try them from here. (

OData Tutorial -

a) Download Fiddler, install and open it.

b) Click on the Composer Tab and type the URL to get the list of MobileReports uploaded to the SSRS Portal.


c) Click on Options tab and check the “Automatically Authenticate” option.


d) Go back to Parsed tab and click the Execute button and you can see the HTTP Status code as 200 as shown below.


Note: If you see 401 error then you need to go back and check whether you have done the Step c correctly.

e) Finally you can see the JSON output under Inspectors > JSON tab


Technorati Tags: ,,,