Start and Stop Azure Virtual Machine

-> Requirement is to start and stop Azure Virtual Machine as per given schedule. I will be using “Tasks (preview)” within Azure Virtual machine to perform this. This feature utilizes Logic Apps behind the scene to accomplish the tasks.

Stop Virtual Machine using option “Tasks (preview)” – Option 1

-> From Azure portal, open the required virtual machine.

-> Select “Tasks (preview)” under “Automation” and click “Add”,

-> Select “Power off Virtual Machine” and click on “Next : Authentication”,

-> Click on Create and Sign in with the required credentials,

-> You will see below screen once signed in,

-> I have provided a schedule in such a way that the Virtual Machine will be stopped every day at 9:00 PM.

-> Click Create.

Stop Virtual Machine using option “Auto-shutdown” – Option 2

-> From Azure portal, open the required virtual machine.

-> Select “Auto-shutdown” under “Operations”. Click on Save once appropriate details are provided as in below screenshot,

-> You can use either Option 1 or 2 as defined above to automatically stop your Azure Virtual Machine.

Start Virtual Machine using option “Tasks (preview)”

-> From Azure portal, open the required virtual machine.

This image has an empty alt attribute; its file name is image-17-1024x250.png

-> Select “Tasks (preview)” under “Automation” and click “Add”,

-> Select “Start Virtual Machine” and click on “Next : Authentication”.

-> Connections are all connected already, you may have to create it if this is done for the first time,

-> I have created a schedule in such a way that the virtual machine will be started on every weekday from Monday to Friday at 8:45 AM.

-> Lets try opening “Jbswiki-Startup” in Logic Apps and check,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

The database ‘jbswiki’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

-> Application fails with below error when trying to connect to Azure SQL database,

Msg 40544, Level 17, State 2, Line 10
The database ‘jbswiki’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

-> Executing below query on “jbswiki” database to check the used and free space on the database,

if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%' 
SELECT [name], fileid, filename, [size]/128.0 AS 'Total Size in MB', 
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', 
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB', 
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used' 
FROM sysfiles 
else
SELECT @@servername as 'ServerName',db_name() as DBName,[name], file_id, physical_name, [size]/128 AS 
'Total Size in MB', 
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', 
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB', 
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used' 
FROM sys.database_files
go

-> From the screenshot above, it is clear that the database “jbswiki” is full.

-> Lets try a simple insert statement and check the behaviour,

-> Login to Azure portal and select the database jbswiki. On “Overview” tab you will see that the database is full,

-> Click on “Compute + storage” under Settings. Change the “Data Max Size” to an appropriate value. In my case I have changed from 100 to 500 MB. Click Apply,

-> Application connections to database jbswiki started working fine after above change.

-> Below is the view of “Overview” tab after the change,

-> Lets try an insert and check if it is working,

->It worked fine this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Connect to SQL Server with PowerShell and Azure key Vault

-> Requirement will be to query an Azure SQL Database via PowerShell and display the results. We will not list the SQL Server Instance, Username and password as clear text in PowerShell script, instead we use the secret from Azure Key Vault to achieve this.

Create an Azure SQL Database

-> Complete tabs “Networking”, “Security”, “Additional settings” and “Tags” as appropriate.

Create an Azure Key Vault

-> Complete tabs “Access policy”, “Networking” and “Tags” as appropriate.

-> Get into Azure Key Vault “sqlcon-key-vault-eastus”. Click on “Secrets” and then “Generate/Import”,

-> Value as mentioned in above screenshot is as below,

Server=tcp:jbdw.database.windows.net,1433;Initial Catalog=prod-jbsqldb01;Persist Security Info=False;User ID=<username>;Password=<Password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

-> Click Create.

Get the required Data using PowerShell

##Script to display database names available## 
## Version 1.0 
##Author : Vivek Janakiraman## 
##cls ##Clearing Screen 

Connect-AzAccount

##Key in the correct Subscription below##
Set-AzContext -Subscription "00000000-0000-0000-0000-000000000000"

$keyvault = "sqlcon-key-vault-eastus" ##Provide the Azure key Vault name##
$ConnectionString = "prod-jbsqldb01-connection-string" ##Provide the the name of the secret created within Azure key Vault##


$Vault = get-azkeyvault -VaultName $keyvault
$Secret = get-azkeyvaultsecret -Name $ConnectionString -VaultName $keyvault
$SecretValue = $Secret.SecretValue 
$ConnectionString = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecretValue))

$tsqlcommand = "select name from sys.databases"; 

#SQL Connection - connection to SQL server 
$sqlConnection = new-object System.Data.SqlClient.SqlConnection; 
$sqlConnection.ConnectionString = $connectionString; 

#SQL Command - set up the SQL call 
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand; 
$sqlCommand.Connection = $sqlConnection; 
$sqlCommand.CommandText = $tsqlcommand; 

#SQL Adapter - get the results using the SQL Command 
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter 
$sqlAdapter.SelectCommand = $sqlCommand 
$dataSet = new-object System.Data.Dataset 
$recordCount = $sqlAdapter.Fill($dataSet) 

#Close SQL Connection 
$sqlConnection.Close(); 

#Get single table from dataset 
$data = $dataSet.Tables[0] 
$data

-> Below is the output,

-> There is a possibility of below error while executing the script,

Exception calling "SecureStringToBSTR" with "1" argument(s): "Value cannot be null.
Parameter name: s"
At C:\temp\Connection_Template_Working.ps1:16 char:1
+ $ConnectionString = [System.Runtime.InteropServices.Marshal]::PtrToSt ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException
 
Exception setting "ConnectionString": "Format of the initialization string does not conform to specification starting at index 0."
At C:\temp\Connection_Template_Working.ps1:22 char:1
+ $sqlConnection.ConnectionString = $connectionString;
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting
 
Exception calling "Fill" with "1" argument(s): "The ConnectionString property has not been initialized."
At C:\temp\Connection_Template_Working.ps1:33 char:1
+ $recordCount = $sqlAdapter.Fill($dataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

Exception calling “SecureStringToBSTR” with “1” argument(s): “Value cannot be null.

-> This error occurs when parameter $ConnectionString is wrong. It should be same as what is seen in below screenshot in Azure key Vault,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.