How to Create a Replicated Database for an Online Client

Prerequisites:

Before starting you will need to Install the latest version of Power Shell, You will want the win-x64 version ( e.g., PowerShell-7.1.2-x64.msi. You can download this here: Install the latest version of PowerShell

Creating Resource Groups

1. Head to Portal.Azure.com and login with your Cobalt Credentials.

2. Click Create to Create a New Resource Group.

3. Search for Azure SQL in the marketplace

4. Click Create in the below screenshot

5. Select Create Database Server like the below screenshot

6. Create a new Database Server like the screenshot below. Keep this password in mind you will need it later.

7. Click into the resource once the server set up is complete then Click on create database and create a SQL Database. Database name will be a unique name created by you. You do not need to set a elastic pool unless specifically requested by the client.

8. Once that is complete. It is time to run the power shell scripts. Make sure you have downloaded the latest version of PowerShell and open it up as a administrator.

PowerShell Scripts

There is a screenshot that will show how how to get each of the unique identifiers below needed to run the actual PowerShell Script. The red text will be replaced by what you find using the below screenshots.

$subscriptionId = 'ContosoSubscriptionId'

$resourceGroupName = 'ContosoResourceGroup1'

$keyvaultName = 'ContosoKeyVault'

$secretName = 'ContosoDataExportSecret'

These two above will need to be filled out by your hand. You can put the Client's name instead of "Contoso"

$location = 'West US'

$connectionString = 'AzureSQLconnectionString'

Note: it will give you something like this.

Server=tcp:cobaltdemo.database.windows.net,1433;Initial Catalog=CobaltReplicatedSQLTest;Persist Security Info=False;User ID=demoadmin;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

You will have to fill out the User ID above and the password above when pasting into the PowerShell Script. You will need to use the password you used when setting up the database when you paste this into the PowerShell script.

$organizationIdList = 'ContosoSalesOrg1_id, ContosoSalesOrg2_id'

This will be in dynamics. Login into the Cilent's instance of Dyanmics and go to Settings> Customization> Developer resources.

$tenantId = 'tenantId'

Now that you have all of the information, copy and paste everything below into PowerShell (including the # ---- #, the last line should be Write-Host "Connection key vault URL is "$secret.id.TrimEnd($secret.Version)"). You will copy and paste the values you gathered above into the first part of the script where necessary.

SCRIPT START

# -------------------------------------------------------------------------------- #

# Provide the value for the following parameters before executing the script

$subscriptionId = '[Specifies the Azure subscription to which the Key Vault belongs.]'

$keyvaultName = '[Specifies the name of the Key Vault. If the Key Vault does not exist, the script will create one]'

$secretName = '[Specifies the name of the secret that is put into the Key Vault. The secret holds the destination database connection string.]'

$resourceGroupName = '[Specifies the Resource Group for the Key Vault.]'

$location = '[Specifies the Azure region where the Resource Group and Key Vault is placed.]'

$connectionString = '[Specifies the destination database connection string that would be placed as a secret in the Key Vault.]'

$organizationIdList = '[Specifies a comma separated list of all the CRM Organization Id which will be allowed to export data to the destination database.]'

$tenantId = '[Specifies the Azure Active Directory Tenant Id to which all the specified CRM Organizations belong to.]'

# -------------------------------------------------------------------------------- #

# Login to Azure account, select subscription and tenant Id

connect-azaccount -Tenant $tenantId -Subscription $subscriptionId

# Create new resource group if not exists.

$rgAvail = Get-AzResourceGroup -Name $resourceGroupName -Location $location -ErrorAction SilentlyContinue

if(!$rgAvail){

New-AzResourceGroup -Name $resourceGroupName -Location $location

}

# Create new key vault if not exists.

$kvAvail = Get-AzKeyVault -VaultName $keyvaultName -ResourceGroupName $resourceGroupName -ErrorAction SilentlyContinue

if(!$kvAvail){

New-AzKeyVault -VaultName $keyvaultName -ResourceGroupName $resourceGroupName -Location $location

# Wait few seconds for DNS entry to propagate

Start-Sleep -Seconds 15

}

# Create tags to store allowed set of Organizations.

$secretTags = @{}

foreach ($orgId in $organizationIdList.Split(',')) {

$secretTags.Add($orgId.Trim(), $tenantId)

}

# Add or update a secret to key vault.

$secretValue = ConvertTo-SecureString $connectionString -AsPlainText -Force

$secret = Set-azKeyVaultSecret -VaultName $keyvaultName -Name $secretName -SecretValue $secretValue -Tags $secretTags

# Authorize application to access key vault.

$servicePrincipal = 'b861dbcc-a7ef-4219-a005-0e4de4ea7dcf'

set-azkeyvaultaccesspolicy -VaultName $keyvaultName -ServicePrincipalName $servicePrincipal -PermissionsToSecrets get

# Display secret url.

Write-Host "Connection key vault URL is "$secret.id.TrimEnd($secret.Version)""

Post-Running PowerShell Script

1. The end of the script will spit out a URL that looks like this: https://cobaltuat37.vault.azure.net:443/secrets/COBALTUAT37REPLICATIONSECRET/. Copy that URL you will need it later on

2.  Verify a key vault was created in your resource group

Configure Firewall Settings

1. Once that is complete, you need to configure the firewall to ensure the key vault and database can talk to each other. To do this, open up the SQL database you created:

2.  Click ‘Set Server Firewall’

3. Change the below to Yes

4. Then, add the IP of the server that is hosting the web apps:

Installing the Data Export Solution

Some Clients will have this installed already. Make sure to check their instance of dynamics before attempting to download this.

1. Now you need to install the Data Export Solution. Go to ‘Settings’  Microsoft AppSource

2. Install the Data Export Service app

3. Once that is installed, to ‘Settings’  Data Export

4. Click ‘New’

5. You will be prompted to create the Data Export Profile. You should leave everything as is, but you will need the Key Vault URL. This is the URL that you copied from the end of the PowerShell script

The naming convention is Data_Export_ORGNAME. You can leave everything else as is. Do not add a prefix.

1. The Key Vault URL will look like: https://cobaltdemotestkeyvault.vault.azure.net:443/secrets/CobaltDemoTestDataExportSecret/

6. You will need to sync the entities the client requests to be synced. For a list of entities click here. If the Client didn't state specifically ask them if they would like every single entity or only specific ones.

Configure Service Configuration/Service Configuration Property.

1. Go to the settings record for the org

2. Click into the SQL configuration lookup field (create a new service configuration record if none exists):

3. Make sure the service configuration has the following properties:

1. Friendly Name = Replicated Database
2. Assembly Name = Cobalt.Adapters.Platform.Core
3. Class Name = Cobalt.Adapters.Platform.Core.SqlReader.SpecificSqlConfigurationService

1. Create the following service configuration properties:
   1. Name = ConvertReturnedTypeCodeToString
      1. Type = System.Boolean
      2. Value = True
   2. Name = Database
      1. Type = System.String
      2. Value = Database Name
         1. Ex: CobaltReplicatedSQLTest
   3. Name = Enabled
      1. Type = System.Boolean
      2. Value = True
   4. Name = Password
      1. Type = System.String
      2. Value = password used during set up
   5. Name = RetrieveMultipleEntities
      1. Type = System.String
      2. Value = account,cobalt_accountingexport,cobalt_accountingexportfiletransformation,cobalt_accountingintegrationbatch,cobalt_accountingintegrationbatchitem,cobalt_alert,cobalt_answer,cobalt_autonumber,cobalt_award,cobalt_batchdefinition,cobalt_cancelationpolicy,cobalt_certification,cobalt_certificationtype,cobalt_chapter,cobalt_chapteraffiliation,cobalt_chapterofficer,cobalt_chapterofficerposition,cobalt_chaptertype,cobalt_class,cobalt_classinstructor,cobalt_classlocation,cobalt_classregistration,cobalt_classregistrationfee,cobalt_cmsuserattribute,cobalt_cmsuseridentitydefinition,cobalt_cmsuserrole,cobalt_cobalt_classinstructor_cobalt_class,cobalt_cobalt_cmsuserrole_contact,cobalt_cobalt_continuingcertificationrequireme,cobalt_cobalt_duesitem_cobalt_chapter,cobalt_cobalt_initialcertificationrequirement_,cobalt_cobalt_tag_cobalt_class,cobalt_cobalt_tag_cobalt_course,cobalt_cobalt_tag_cobalt_meeting,cobalt_cobalt_tag_product,cobalt_cobalt_test_cobalt_examtype,cobalt_committee,cobalt_committeemembership,cobalt_committeenomination,cobalt_committeeposition,cobalt_committeetype,cobalt_continuingcertapprequirement,cobalt_continuingcertapptype,cobalt_continuingcertfee_secondaryproduct,cobalt_continuingcertificationapplication,cobalt_continuingcertificationfee,cobalt_continuingcertificationrequirement,cobalt_continuingeducationactivity,cobalt_continuingeducationprogram,cobalt_continuingeducationprovider,cobalt_country,cobalt_coupon,cobalt_couponredemption,cobalt_course,cobalt_courseprovider,cobalt_credit,cobalt_cronjob,cobalt_cronschedule,cobalt_degree,cobalt_degreeprogram,cobalt_donation,cobalt_duescycle,cobalt_duesgeneration,cobalt_duesgenerationbatch,cobalt_duesitem,cobalt_duesoption,cobalt_duesproduct,cobalt_duesproration,cobalt_duesschedule,cobalt_educationalinstitution,cobalt_election,cobalt_electionrequirement,cobalt_electionrequirementtype,cobalt_emailtemplate,cobalt_engagementactivitytype,cobalt_event,cobalt_eventdocument,cobalt_eventdocument_class,cobalt_eventdocument_meeting,cobalt_exam,cobalt_examtype,cobalt_form,cobalt_generalledgeraccount,cobalt_generalledgeraccountcategory,cobalt_initialcertapprequirement,cobalt_initialcertapptype,cobalt_initialcertfee_secondaryproduct,cobalt_initialcertificationapplication,cobalt_initialcertificationfee,cobalt_initialcertificationrequirement,cobalt_meeting,cobalt_meetingactivity,cobalt_meetingactivityregfee,cobalt_meetingactivityspeaker,cobalt_meetingbooth,cobalt_meetingcancelationfee,cobalt_meetingcancelationfee_meetingregfee,cobalt_meetingcancelfee_meetingactivityregfee,cobalt_meetingcompanionregistration,cobalt_meetingexhibitor,cobalt_meetingregistration,cobalt_meetingregistrationactivity,cobalt_meetingregistrationfee,cobalt_membership,cobalt_membershipapp_chapter,cobalt_membershipapplication,cobalt_membershipapplicationfee,cobalt_membershipapplicationrequirement,cobalt_membershiprequirement,cobalt_membershiprequirementprerequisite,cobalt_membertype,cobalt_ordersource,cobalt_page,cobalt_pagetext,cobalt_pricelevelevaluation,cobalt_productgeneralledgeraccount,cobalt_publication,cobalt_publicationfee,cobalt_question,cobalt_questionlogic,cobalt_questionmapping,cobalt_questionmappingstep,cobalt_registration,cobalt_section,cobalt_specialaccommodation,cobalt_specialaccommodation_initialcertapp,cobalt_stateprovince,cobalt_systemquery,cobalt_tag,cobalt_test,cobalt_testapplication,cobalt_testfee,cobalt_vote,cobalt_voterscorequestion,cobalt_votingscore,cobalt_webelement,cobalt_webelementfee,cobalt_webelementrequirement,cobalt_webelementtype,pricelevel,product,savedquery,systemuser,template,transactioncurrency,uom,uomschedule,userquery,workflow
   6. Name = RetrieveSingleEntities
      1. Type = System.String
      2. Value = cobalt_accountingexport,cobalt_accountingexportfiletransformation,cobalt_accountingintegrationbatch,cobalt_accountingintegrationbatchitem,cobalt_alert,cobalt_answer,cobalt_autonumber,cobalt_award,cobalt_batchdefinition,cobalt_cancelationpolicy,cobalt_certification,cobalt_certificationtype,cobalt_chapter,cobalt_chapteraffiliation,cobalt_chapterofficer,cobalt_chapterofficerposition,cobalt_chaptertype,cobalt_class,cobalt_classinstructor,cobalt_classlocation,cobalt_classregistration,cobalt_classregistrationfee,cobalt_cmsuserattribute,cobalt_cmsuseridentitydefinition,cobalt_cmsuserrole,cobalt_cobalt_classinstructor_cobalt_class,cobalt_cobalt_cmsuserrole_contact,cobalt_cobalt_continuingcertificationrequireme,cobalt_cobalt_duesitem_cobalt_chapter,cobalt_cobalt_initialcertificationrequirement_,cobalt_cobalt_tag_cobalt_class,cobalt_cobalt_tag_cobalt_course,cobalt_cobalt_tag_cobalt_meeting,cobalt_cobalt_tag_product,cobalt_cobalt_test_cobalt_examtype,cobalt_committee,cobalt_committeemembership,cobalt_committeenomination,cobalt_committeeposition,cobalt_committeetype,cobalt_continuingcertapptype,cobalt_continuingcertfee_secondaryproduct,cobalt_continuingcertificationfee,cobalt_continuingcertificationrequirement,cobalt_continuingeducationactivity,cobalt_continuingeducationprogram,cobalt_continuingeducationprovider,cobalt_country,cobalt_coupon,cobalt_couponredemption,cobalt_course,cobalt_courseprovider,cobalt_credit,cobalt_cronjob,cobalt_cronschedule,cobalt_degree,cobalt_degreeprogram,cobalt_donation,cobalt_duescycle,cobalt_duesgeneration,cobalt_duesgenerationbatch,cobalt_duesitem,cobalt_duesoption,cobalt_duesproduct,cobalt_duesproration,cobalt_duesschedule,cobalt_educationalinstitution,cobalt_election,cobalt_electionrequirement,cobalt_electionrequirementtype,cobalt_emailtemplate,cobalt_engagementactivitytype,cobalt_event,cobalt_eventdocument,cobalt_eventdocument_class,cobalt_eventdocument_meeting,cobalt_exam,cobalt_examtype,cobalt_form,cobalt_generalledgeraccount,cobalt_generalledgeraccountcategory,cobalt_initialcertapptype,cobalt_initialcertfee_secondaryproduct,cobalt_initialcertificationfee,cobalt_initialcertificationrequirement,cobalt_meeting,cobalt_meetingactivity,cobalt_meetingactivityregfee,cobalt_meetingactivityspeaker,cobalt_meetingbooth,cobalt_meetingcancelationfee,cobalt_meetingcancelationfee_meetingregfee,cobalt_meetingcancelfee_meetingactivityregfee,cobalt_meetingcompanionregistration,cobalt_meetingexhibitor,cobalt_meetingregistrationfee,cobalt_membership,cobalt_membershipapp_chapter,cobalt_membershipapplicationfee,cobalt_membershipapplicationrequirement,cobalt_membershiprequirement,cobalt_membershiprequirementprerequisite,cobalt_membertype,cobalt_ordersource,cobalt_page,cobalt_pagetext,cobalt_pricelevelevaluation,cobalt_productgeneralledgeraccount,cobalt_publication,cobalt_publicationfee,cobalt_question,cobalt_questionlogic,cobalt_questionmapping,cobalt_questionmappingstep,cobalt_registration,cobalt_section,cobalt_specialaccommodation,cobalt_specialaccommodation_initialcertapp,cobalt_stateprovince,cobalt_systemquery,cobalt_tag,cobalt_test,cobalt_testfee,cobalt_vote,cobalt_voterscorequestion,cobalt_votingscore,cobalt_webelement,cobalt_webelementfee,cobalt_webelementrequirement,cobalt_webelementtype,pricelevel,product,savedquery,systemuser,template,transactioncurrency,uom,uomschedule,userquery,workflow
   7. Name = ServerAddress
      1. Type = System.String
      2. Value = server address from set up
         1. Ex: cobaltdemo.database.windows.net
   8. Name = UserName
      1. Type = System.String
      2. Value = username from set up
         1. Ex: demoadmin

Verify Syncing

1. Once those entities/relationships are syncing correctly, you can verify the data is syncing correctly by connecting to Azure Data Studio:

2. Then, change the name of one of the records you are syncing to CRM (ex: a class name)

3. Right click on the table and click ‘Select top 1000’

4. Verify the name of the class you updated in CRM is also updated in Azure Data Studio

5. Now you need to test that our web apps are leveraging this database.

Verify Web Apps are Syncing

1. In CRM, go to Cobalt Settings
2. Open the SQL Connection Configuration

3. Open the Username service config

4. Add a random number to the UN and save

1. Do an iisreset
2. Navigate to a contact
3. Launch the membership application wizard (or any wizard)
4. Make sure you get the following error:

5. Change the username back to the correct value

6. IIS reset

7. Verify you no longer receive the error