Test-NetConnection sqlserver.database.windows.net -port 1433
More Troubleshooting
Run > wf.msc > Ensure Outbound Ports are not blocked – especially 1433 port
Strategy with Speed ensures Success!
In this post we can see multiple ways to deploy files to Azure App Services.
Right click on project > Publish > Select App Service
Configure Azure Devops > Checkin Trigger > Deploy through CICD Pipeline
Go to Azure Portal > App Service > Deployment Center > FTP > Configure User Credentials
Following PowerShell Script will perform the following:
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
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
Once successfully executed you can see the following:
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.
In this post I would like to share a simple Azure Durable Function which will perform Simple Return Value along with some Timer Delays.
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 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} !”;
}
}
}
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)
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.
Break your code into multiple chunks
Ensure transactions are protecting the Atomicity.
In this post we can compare the performance of ID as INT, GUID & Sequential GUID
Note
Sequential GUID preferred over GUID to avoid pagination issues
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 [dbo].[IDTest_GUID](
[ID] uniqueidentifier DEFAULT NEWID(),
[Name] [varchar](100) NOT NULL,
CONSTRAINT [PK_IDTest_GUID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
CREATE TABLE [dbo].[IDTest_SequentialGUID](
[ID] uniqueidentifier DEFAULT NEWSEQUENTIALID(),
[Name] [varchar](100) NOT NULL,
CONSTRAINT [PK_IDTest_SequentialGUID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
INT 13 seconds
GUID 12 seconds
Sequential GUID 11 seconds
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
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′
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.
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.
Your enterprise application / resource group / subscription do not have permission ‘Microsoft.DataFactory/factories/pipelines/createRun/action’
Go to Azure > Active Directory > Enterprise Applications > Find your application > Copy the Object ID
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.