Overview:
skillcertlabs is an online training provider.
Current Environment:
The company currently has Microsoft SQL databases that are split into different categories or tiers. Some of the databases are used by Internal users, some by external partners and external distributions.
Below is the list of applications, tiers and their individual requirements
- Applications – Tier – Replication – Comments
- Internal skillcertlab – 1 – Yes – NULL
- Internal skillcertlab – 2 – Using SQL Data Sync – NULL
- Internal Partner – 3 – Yes – Data is replicated to the Partner
- External skillcertlab – 4,5,6 – Yes
- External Partner – 7,8 – No – This is a Partner managed database
- Internal Distribution and Sales – 9 – Yes, but only when the data is ingested at one of the branch offices – Data is ingested from skillcertlab branch offices
- External Distribution and Sales – 10 – Yes, but only once the data is ingested at the skillcertlab main office – Data is ingested from multiple sources
Below are the current requirements of the company:
- The databases in Tier 3, Tier 6 to 8 must use a database density on the same server and Elastic pools in cost effective manner
- The Applications must have access to data from internal and external sources whilst ensuring data is encrypted at rest and in transit
- The databases in Tier 3, Tier 6 to 8 must have a recovery strategy for in case whenever the server goes offline
- The Tier 1 applications must have their databases stored on Premium P2 tier
- The Tier 1 applications must have their databases stored on Standard S4 tier
- Data will be migrated from the on-premise databases to Azure SQL Databases using Azure Data Factory. The pipeline must support continued data movement and migration.
- The Application access for Tier 7 and 8 must be restricted to the database only
- For Tier 4 and Tier 5 databases, the backup strategy must include the following
- Transactional log backup every hour
- Differential backup every day
- Full backup every week
- Backup strategies must be in place for all standalone Azure SQL databases using methods available with Azure SQL databases.
- Tier 1 database must implement the following data masking logic
- For Data type skillcertlabA – Mask 4 or less string data type characters
- For Data type skillcertlabB – Expose the first letter and mask the domain
- For Data type skillcertlabC – Mask everything except characters at the beginning and the end
- All certificates and keys are internally managed in on-premise data stores
- For Tier 2 databases, if there are any conflicts between the data transfer from on-premise, preference should be given to on-premise data.
- Monitoring must be setup on every database
- Applications with Tiers 6 through 8 must ensure that unexpected resource storage usage is immediately reported to IT data engineers.
- Azure SQL Data warehouse would be used to gather data from multiple internal and external databases.
- The Azure SQL Data warehouse must be optimized to use data from its cache
- The below metrics must be available when it comes to the cache
- Metric skillcertlabA – Low cache hit %, high cache usage %
- Metric skillcertlabB – Low cache hit %, low cache usage %
- Metric skillcertlabC – high cache hit %, high cache usage %
- The reporting data for external partners must be stored in Azure storage. The data should be made available during regular business hours in connecting regions.
- The reporting for Tier 9 needs to be moved to Event Hubs.
- The reporting for Tier 10 needs to be moved to Azure Blobs.
The following issues have been identified in the setup:
- The External partners have control over the data formats, types and schemas
- For External based clients, the queries can’t be changed or optimized
- The database development staff are familiar with T-SQL language
- Because of the size and amount of data, some applications and reporting features are not performing at SLA levels.
The data for the external applications needs to be encrypted at rest. You decide to implement the following steps
Use the Always Encrypted Wizard in SQL Server Management Studio
Select the column that needs to be encrypted
Set the encryption type to Randomized
Configure the master key to be used from the Windows Certificate Store
Confirm the configuration and deploy the solution
Would these steps fulfil the requirement?
Select 1 option(s):