what are the best practices for Data retention in Azure Data Lake and Azure SQL Database

Question

Hello,

We are in the final phase of our project and would like to know the understanding for below questions.

1) What should be the data retention policy on Azure Data Lake Gen2 storage account. We would like to set up a retention policy which is applicable for all folders/subfolders and files inside the container. How we can do this?

2) How to apply retention policy only for few folders not all. How we can achieve this?

3) How frequently should be generate the .bacpac file of the azure SQL db and store it in Azure storage container? Is taking the backup and store in storage container a right approach? Any recommendation would be helpful.

4) How we can import the .bacpac file into UAT and production with an existing database?

Thanks,
Sachin

Answer 1

1) You can configure an Azure retention policy to move files from cool/hot tiers to archive tier based on the last accessed time attribute of a blob by using lifecycle management policies. Read here for examples. Below a policy example:

{  
  "enabled": true,  
  "name": "last-accessed-thirty-days-ago",  
  "type": "Lifecycle",  
  "definition": {  
    "actions": {  
      "baseBlob": {  
        "enableAutoTierToHotFromCool": true,  
        "tierToCool": {  
          "daysAfterLastAccessTimeGreaterThan": 30  
        }  
      }  
    },  
    "filters": {  
      "blobTypes": [  
        "blockBlob"  
      ],  
      "prefixMatch": [  
        "mylifecyclecontainer/log"  
      ]  
    }  
  }  
}  

Second question, you can apply filters to policies as shown here.

Third question, I personally save weekly backups of Azure SQL databases to Azure Storage Accounts as the retention period of automated backups is just 35 days. You can also consider using Azure Backups Long-Term Retention.

Fourth question, you can import a bacpac using Azure portal, sqlpackage tool and PowerShell as explained here, but you cannot make the import to replace an existing SQL Azure database. You will have to automate the drop of the database and then import from bacpac using the desired name for the database (the name of the database previously dropped).

$importRequest = New-AzSqlDatabaseImport -ResourceGroupName "<resourceGroupName>" `  
    -ServerName "<serverName>" -DatabaseName "<databaseName>" `  
    -DatabaseMaxSizeBytes "<databaseSizeInBytes>" -StorageKeyType "StorageAccessKey" `  
    -StorageKey $(Get-AzStorageAccountKey `  
        -ResourceGroupName "<resourceGroupName>" -StorageAccountName "<storageAccountName>").Value[0] `  
        -StorageUri "https://myStorageAccount.blob.core.windows.net/importsample/sample.bacpac" `  
        -Edition "Standard" -ServiceObjectiveName "P6" `  
        -AdministratorLogin "<userId>" `  
        -AdministratorLoginPassword $(ConvertTo-SecureString -String "<password>" -AsPlainText -Force)