Test-NetConnection sqlserver.database.windows.net -port 1433
More Troubleshooting
Run > wf.msc > Ensure Outbound Ports are not blocked – especially 1433 port
Month: February 2020
Multiple Ways to Deploy Files to Azure App Services
In this post we can see multiple ways to deploy files to Azure App Services.
1. Deploy through Visual Studio
Right click on project > Publish > Select App Service
2. Deploy through CICD
Configure Azure Devops > Checkin Trigger > Deploy through CICD Pipeline
3. Deploy through FTP
Go to Azure Portal > App Service > Deployment Center > FTP > Configure User Credentials
4. More Manual Options
Azure Create Key Vault, Certificate & Associate using PowerShell
Following PowerShell Script will perform the following:
- Create Azure Key Vault
- Create Certificate
- Create Azure App Registration
- Associate Certificate to App Registration
- Display the Thumbprint
PowerShell Scripts
Clear
# Set Variables
$KeyVault = “NewKeyVaultMar2020”
$ResourceGroup = “jp-resource-group”
$location = “East US”
$PfxFilePath = ‘YourCertificate.pfx’
$CerFilePath = ‘C:\Certificates\YourCertificate.cer’
$DNSName = ‘yourdns.yourdomain.com’
$Password = ‘Password$$1!”‘
$StoreLocation = ‘CurrentUser’
$CertBeginDate = Get-Date
$CertExpiryDate = $CertBeginDate.AddYears(1)
$UniqueName = New-Guid
$UniqueName -replace’-‘, ”
$UniqueName
$URL = ‘http://’ + $UniqueName
#Print
$URL
# Connect to Azure
Connect-AzureRmAccount
# Create Key Vault
New-AzureRmKeyVault -Name $KeyVault -ResourceGroupName $ResourceGroup -Location $location
# Create Secret
$SecretValue = ConvertTo-SecureString $Password -AsPlainText -Force
$Secret = Set-AzureKeyVaultSecret -VaultName $KeyVault -Name ‘SQLPassword’ -SecretValue $SecretValue
(get-azurekeyvaultsecret -vaultName $KeyVault -name “SQLPassword”).SecretValueText
# Create Certificate
$SecStringPw = ConvertTo-SecureString -String $Password -Force -AsPlainText
$Cert = New-SelfSignedCertificate -DnsName $DNSName -CertStoreLocation “cert:\$StoreLocation\My” -NotBefore $CertBeginDate -NotAfter $CertExpiryDate -KeySpec Signature
Export-PfxCertificate -cert $Cert -FilePath $PFXFilePath -Password $SecStringPw
Export-Certificate -cert $Cert -FilePath $CerFilePath
$x509 = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2
$x509.Import($CerFilePath)
$credValue = [System.Convert]::ToBase64String($x509.GetRawCertData())
$adapp = New-AzureRmADApplication -DisplayName “Your Web Application” -HomePage $URL -IdentifierUris $URL -CertValue $credValue -StartDate $x509.NotBefore -EndDate $x509.NotAfter
$sp = New-AzureRmADServicePrincipal -ApplicationId $adapp.ApplicationId
Set-AzureRmKeyVaultAccessPolicy -VaultName $KeyVault -ServicePrincipalName $URL -PermissionsToSecrets get,list,set,delete,backup,restore,recover,purge -ResourceGroupName $ResourceGroup
#Print Thumbprint
$x509.Thumbprint
Execution
Open PowerShell ISE in Administrative Mode
Change the Key Vault Name to a New Unique One
Change the Resource Group Name to yours
Run the PowerShell
Enter Login Information when Prompted
Validation
Once successfully executed you can see the following:
- Key Vault
- Azure App Registration
- Certificate
How to Replace strings in Multiple Files?
Get-ChildItem -Filter *.* -Recurse | where {-not $_.psiscontainer} | Foreach-Object {
$c = ($_ | Get-Content)
If ($c | Select-String -Pattern ‘SOURCE-STRING’) {
$c = $c -creplace ‘SOURCE-STRING’, ‘TARGET-STRING’
[IO.File]::WriteAllText($_.FullName, ($c -join “`r`n”)) }
}
It works for subfolders as well.
Understanding Azure Durable Functions
In this post I would like to share a simple Azure Durable Function which will perform Simple Return Value along with some Timer Delays.
Function Replays
As you are aware Functions undergo multiple Replays which may require aligning your code as well.
context.IsReplaying property says whether it is a replay.
The Code
The code ensures there is a 2 minute delay between sections.
namespace DurableFunctionTest
{
public static class Function1
{
[FunctionName(“Function1_HttpStart”)]
public static async Task<HttpResponseMessage> HttpStart(
[HttpTrigger(AuthorizationLevel.Anonymous, “get”, “post”)]HttpRequestMessage req,
[OrchestrationClient]DurableOrchestrationClient starter,
ILogger log)
{
// Function input comes from the request content.
string instanceId = await starter.StartNewAsync(“Function1”, null);
log.LogInformation($”Started orchestration with ID = ‘{instanceId}’.”);
return starter.CreateCheckStatusResponse(req, instanceId);
}
[FunctionName(“Function1”)]
public static async Task<List<string>> RunOrchestrator(
[OrchestrationTrigger] DurableOrchestrationContext context)
{
var outputs = new List<string>();
outputs.Add(await context.CallActivityAsync<string>(“Function1_Hello”, “London”));
return outputs;
}
[FunctionName(“Function1_Hello”)]
public static string SayHello([ActivityTrigger] string name, ILogger log)
{
log.LogInformation($”step 1 {DateTime.Now.ToLongTimeString()}”);
Thread.Sleep(2 * 60 * 1000); // 2min
log.LogInformation($”step 2 {DateTime.Now.ToLongTimeString()}”);
Thread.Sleep(2 * 60 * 1000); // 2min
log.LogInformation($”step 3 {DateTime.Now.ToLongTimeString()}”);
Thread.Sleep(2 * 60 * 1000); // 2min
log.LogInformation($”step 4 {DateTime.Now.ToLongTimeString()}”);
Thread.Sleep(2 * 60 * 1000); // 2min
return $”Hello {name} !”;
}
}
}
The Output
2/10/2020 6:38:10 PM] 352ff6a310684f6eba4274bb81a54220: Function ‘Function1_Hello (Activity)’ started. IsReplay: False. Input: (40 bytes). State: Started. HubName: DurableFunctionsHub. AppName: . SlotName: . ExtensionVersion: 1.8.2. SequenceNumber: 9.
[2/10/2020 6:38:10 PM] Executing ‘Function1_Hello’ (Reason=”, Id=7f616c57-f299-4f9c-91ec-75feaa180893)
[2/10/2020 6:38:10 PM] step 1 1:38:10 PM
[2/10/2020 6:38:13 PM] Host lock lease acquired by instance ID ‘000000000000000000000000FE3C5631’.
[2/10/2020 6:40:08 PM] step 2 1:40:08 PM
[2/10/2020 6:40:10 PM] step 2 1:40:10 PM
[2/10/2020 6:40:10 PM] step 2 1:40:10 PM
[2/10/2020 6:42:08 PM] step 3 1:42:08 PM
[2/10/2020 6:42:10 PM] step 3 1:42:10 PM
[2/10/2020 6:42:10 PM] step 3 1:42:10 PM
[2/10/2020 6:44:08 PM] step 4 1:44:08 PM
[2/10/2020 6:44:10 PM] step 4 1:44:10 PM
[2/10/2020 6:44:10 PM] step 4 1:44:10 PM
[2/10/2020 6:46:08 PM] Executed ‘Function1_Hello’ (Succeeded, Id=02c78620-a573-4ffa-9f62-0aac2333ceaf)
The Inference
As you can see, although the Function replayed multiple times, but the restart was not loosing any time, it was accurate enough to replay from the past exit point.
Notes
Break your code into multiple chunks
Ensure transactions are protecting the Atomicity.
SQL Server–Compare Performance of INT, GUID, Sequential GUID
In this post we can compare the performance of ID as INT, GUID & Sequential GUID
Advantages of GUID Columns as Primary Key
- Makes them Globally Unique
- Can backup/restore/sync to another DB without breaking Primary Keys & Foreign Keys
- No Performance Impact (proven here)
Note
Sequential GUID preferred over GUID to avoid pagination issues
PROOF – No Performance Issues on GUID
Create Table – INT Primary AutoIncrement
CREATE TABLE [dbo].[IDTest_INT](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NOT NULL,
CONSTRAINT [PK_IDTest_INT] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
Create Table – GUID Primary –
CREATE TABLE [dbo].[IDTest_GUID](
[ID] uniqueidentifier DEFAULT NEWID(),
[Name] [varchar](100) NOT NULL,
CONSTRAINT [PK_IDTest_GUID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
Create Table – GUID Primary – Sequential GUID
CREATE TABLE [dbo].[IDTest_SequentialGUID](
[ID] uniqueidentifier DEFAULT NEWSEQUENTIALID(),
[Name] [varchar](100) NOT NULL,
CONSTRAINT [PK_IDTest_SequentialGUID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
INSERT TEST – 1 LAKH RECORDS
INT 13 seconds
GUID 12 seconds
Sequential GUID 11 seconds
INFERENCE No Impact on Performance for Insert
Here are the queries for same:
declare @count int
select @count = 1
while @count <= 100000
begin
insert into IDTest_INT (name) values(‘Name’ + STR(@count))
insert into IDTest_GUID (name) values(‘Name’ + STR(@count))
insert into IDTest_SequentialGUID (name) values(‘Name’ + STR(@count))
select @count = @count + 1
end
SELECT TEST – ON ID
INT 0seconds
GUID 0seconds
Sequential GUID 0seconds
Here are the queries for same:
SELECT * FROM IDTest_INT WHERE ID = 1000
SELECT * FROM IDTest_GUID WHERE ID = ‘1EDE341C-7692-4D7C-A99D-000C387337DE’
SELECT * FROM IDTest_SequentialGUID WHERE ID = ’84a4d912-5448-ea11-8740-48f17ffd0966′
INFERENCE No Impact on Performance for Select
More Test
Conducted the Test with 20 Lakh records & Full Scan shown NO DIFFERENCE as well.
Summary
Performance of INT vs GUID vs SEQUENTIAL GUID on Primary Key are same.
Azure Data Factory–Pipeline Execution Error
While working with Azure Data Factory, I encountered this error.
System.AggregateException: One or more errors occurred. (One or more errors occurred. The client ‘guid-8621-4dc9-a185-e5b694dba8e2’ with object id ‘guid-8621-4dc9-a185-e5b694dba8e2’ does not have authorization to perform action ‘Microsoft.DataFactory/factories/pipelines/createRun/action’ over scope ‘/subscriptions/guid-0e64-43e0-9083-23d28be9fb18/resourceGroups/APP/providers/Microsoft.DataFactory/factories/pipelines/SynTranslation’ or the scope is invalid.
The Problem
Your enterprise application / resource group / subscription do not have permission ‘Microsoft.DataFactory/factories/pipelines/createRun/action’
The Solution
Go to Azure > Active Directory > Enterprise Applications > Find your application > Copy the Object ID
Run the PowerShell Command
New-AzureRmRoleAssignment -RoleDefinitionName “Data Factory Contributor” –ObjectId <yourobjectid>
Now retry & it should work.
Note: You can use the Azure CloudShell window from the Top Bar to perform the PowerShell Cmdlet Execution.
