Azure

ByHariharan Rajendran

Enable Azure Advisor

Microsoft Azure Team released one of the expected feature in azure.

It is like a personal assistant that helps us to follow best practices, efficient resource usage and optimize the deployments.

Check below to get the Azure Advisor,

Mostly it will be available in left side of the menu,

Azure

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If you are not able to identify then enable the favorite,

Azure1

Azure2

ByHariharan Rajendran

Query Editor (preview) in Azure Portal

Microsoft Azure added a new feature called “Query Editor (Preview)” for SQL database. It is a portal based query editor.

Now user can execute the SQL query directly in the portal itself instead of using SQL Server Management Studio (SSMS) to access the data.

Find a Query Editor in SQL Database

Query Editor (preview) is available under Tools in SQL Database.

QE1

Authorization Type

At present, it supports 2 authorization types.

  1. SQL Server authentication
  2. Active Directory password authentication

QE2

Steps to run a query

  1. Go to SQL Database in Azure
  2. Click Tools and choose “Query Editor (preview)”.
  3. Authenticate yourself by clicking “Login”. Pass the password of the SQL Server and click “Ok”.
  4. Once you are authenticated successfully, Type the script or import using “Open Query”.
  5. Click “Run”.

QE3

QE4

Open & Save Query

If you have SQL script file on your machine and want to run those scripts in portal Query Editor then you can use “Open query” option to import the .sql file.

In another case, if you want to store the written query in the editor then click “Save query”. SQL script file will be downloaded.

QE5

ByHariharan Rajendran

Access Azure Virtual Machine Applications Outside of Network

AzureVM

This article explains that how you can access virtual machine applications outside of network with the help of DNS.

For example,

Consider you have installed SQL Server on Azure Virtual machine and created few databases on the server. If you want to access those databases on your local machine, then you need to follow the below steps.

  • DNS Configuration in Azure Portal
  • Enable the TCP port with 1433 in Azure portal
  • Enable the TCP 1433 in virtual Machine
  • Create a SQL Server login and enable the mixed mode authentication

Once you have configured all the above steps, you can access your databases on another machine in SQL Server Management Studio.

Refer the below link to know in detail about the above steps.

https://social.technet.microsoft.com/wiki/contents/articles/36674.access-azure-vm-sql-server-outside-of-network.aspx

ByHariharan Rajendran

Azure PowerShell Commandlets Issues

As usual, I was trying to use PowerShell Command line tool to manage my azure services. I used to prefer PowerShell as we have more control on it. This time, I got an error message, I was not able to run any Azure commandlets but I was able to add my azure account.

The error message is bit tricky and to solve the issue, I just follow closed and re-opened but still I got the same error message. I found that I got something issue with my saved credentials (token).

Error message,

Get-AzureSubscription : Failed to refresh token. AADSTS90002 : No Service namespace named ‘ ‘ was not found in the data store.
Trace ID:XXXXXXXXXX
Correlation ID: XXXXXXXXXXXXXXXXXX
TimeStamp: 2015-09-12 13:24:477 : The remote server returned an error : (400) Bad Request.

image

I followed the below steps to solve the issue,

Step 1: Explored the following folder in your system,  C:\Users\{User Name}\AppData\Roaming\Windows Azure Powershell.

Note: AppData folder will be in hidden by default and replace {User Name} into your system name.

Step 2: I saw the below files inside the folder and Just deleted all the files.

image

Step 3: Closed the PowerShell tool and reopen again.

Step 4: Added my azure account, this time, I was able to run all commands successfully.

ByHariharan Rajendran

New feature in Azure Storage

As we know, Microsoft Azure team keep updating the features for all the services. The one of the latest and noticeable update in Azure Storage service is “Account kind”.

This new feature introduced new interesting capabilities which will help customers to decide the right storage access tier.

Before “Account kind”, we used to create a storage account by just specifying the values for Name, Deployment Model, Performance & Location, of course, resource name. There was no data category kind of structure. In this latest update, we can have same the previous feature or we can categories the storage account like Hot or Cool.

The available options under “Account kind”,

1. General Purpose

2. Blob Storage

When we select “Blob Storage”, it will add a new option called “Access tier” which has “Cool” & “Hot” (default).

We can categories our data like “High access data” & “Low access data”. It depends on how frequently, we accessing the data. It has some advantages as well.

If you choose “Cool”, it has lower storage costs and higher access and transaction costs. If “Hot”, it has higher storage costs, lower access and transaction costs.

For more information, please check the Microsoft Azure Documentation here.

image

 

image

ByHariharan Rajendran

Azure–Billing Alert Service

The most expected feature in Azure, available for preview.

Yes, we have been expected an alert for azure credit usage. It is available now. It is in the preview so it will not be available by default, you have to activate it by going through “Preview Feature”

Follow the below steps to activate it.

Step 1: Login with your azure subscription email, and visit the following page.

https://account.windowsazure.com/Subscriptions

Step 2: Look for “preview features” option on the top and select it.

image

Step 3: Search for “Billing Alert Service”, out of all the listed preview features. Click “try it now” link and this will make you in the queue. The Azure team will look for your request and will activate it for you. It may take at least one week to get activated.

image

Once this feature is active, you can able to set up an alert for your usage as like below.

The option will look like below,

image

We can create up to 5 alerts and at present only two alert types are available, “Billing Total” & “Monetary Credits”

Billing Total – An alert is sent when subscription spending exceeds the threshold.

Monetary Credit – An alert is sent when monetary credits drop below the limit.

image

image

ByHariharan Rajendran

Manage Azure Storage Account using Azure CLI

We can manage storage account through Azure Portal or PowerShell Scripts.

This article will explain you the commands that you need to use inside Azure CLI to manage storage account.

Download the Azure CLI in following link and choose respective installer package.

image

Install it on your machine. if you are working with Azure and if you had installed different tools to interact with Azure, you may have different command line tools in your machine. To find the recently installed one, just search for “Microsoft Azure Command Prompt”.

You will see a tool like below,

image

#First login into Azure using below command

azure login

You need to open the following link in your browser, https://aka.ms/devicelogin and provide the given code to authenticate.

If you have multiple subscription, then you can able to choose the default one.

#List out all the subscriptions

azure account list

#To set the default subscription

azure account set “name of your subscription”

#Create a storage account on the selected subscription

azure storage account create <storage account name>

Above script will ask you to provide two values, “Account Type” & “Location”. Just provide the numbers of the respective information.

image

To create a containers or all other storage services, we need to fix the created storage account details in environment variable so that when we run a further commands, it will consider the correct storage account. If you missed to set up environment variable then you will end up with following error.

image

Azure has command called “export” which is used to hold your storage connection string, just like a variable but it is not working as expected. If you try like below,

    export AZURE_STORAGE_ACCOUNT=<account_name>

You will get below error. So we can follow different approach to have environment variable.

‘export’ is not recognized as an internal or external command,
operable program or batch file.

 

#Get the connection string of storage account

azure storage account connectionstring show <storage account name>

#Set Azure Connection string as environment Variable

SET AZURE_STORAGE_CONNECTION_STRING=<connection string>

#Create a container

azure storage container create <container name>

image

#Upload the blobs
azure storage blob upload <Path>  <container name>  <blob name>

Example:
azure storage blob upload E:\Encryption.png logs myblob

#Download the blobs
azure storage blob download <container name>  <blob name>  <local path>

Example:

azure storage blob download logs myblob E:\log.png

#Delete blobs
azure storage blob delete <container name> <blob name>

Example:
azure storage blob delete logs myblog

image

ByHariharan Rajendran

Monitor Stretch in SQL Server 2016

Go through my previous article to enable the stretch for your database and table. This article is about how to monitor the stretch. You can monitor the stretch through GUI or DMVs.

We have below DMVs available to monitor the status of the stretch.

select * from sys.remote_data_archive_databases

 

The above script will provide the details about what is the database name in Azure used for stretch.

image

 

select * from sys.remote_data_archive_tables

 

This will tell you, what is the table name in azure used for stretch.

image

The above result has column called “filter_predicate”. It means, if apply any predicate function while configure the stretch then you can see that predicate function here. unfortunately, we don’t have option to apply the predicate function in GUI method. It is available when we enable stretch through T-SQL Script.

select * from sys.dm_db_rda_migration_status

This script will tell you about data migration. Data will start migrate from the table once stretch is enabled. Migration will start  from on-premises SQL Server to Azure. It is using batch process so it will migrate maximum of  4999 records in a batch. The batch id will be available in Azure table.

image

To see the live migration, we can use “SP_SPACEUSED”. By seeing the rows and size we can identify the data migration.

image

 

You can monitor the stretch using GUI,

image

image