Skip to content

README.md

Latest commit

 

History

History
27 lines (23 loc) · 2.3 KB

README.md

File metadata and controls

27 lines (23 loc) · 2.3 KB

Project Setup

On-premises SQL Server Setup

  1. Download SQL Server and start in your local server
  2. Download SQL Server Management Studio (SSMS) and connect to locally hosted SQL Server database.
  3. Download any AdventureWorks backup database from here. I have considered AdventureWorksLT2016 data in this project.
  4. Load the data into SQL Server into using SSMS as desribed here. The database should look like below in SSMS: SSMS_DB_snapshot
  5. To access this database from Azure, we need to have a database user. Add an user to AdventureWorksLT2016 database by running the following commands in SSMS: 
    CREATE LOGIN ssmsuser WITH PASSWORD = 'password';
CREATE USER ssmsuser FOR LOGIN ssmsuser;
    Grant database reader (db_datareader) permission to the created under Security > Users > Properties > Membership in SSMS.

Azure Cloud Setup

  1. Create a resource group for the project: rg-db-migration-project

  2. Create the following services under the resource group: Azure Databricks Service, Data Factory (V2), Synapse Workspace, Key Vault, and a Data Lake storage account. Resource Group Snapshot

  3. Add the SQL Server database user credentials (step 5 of on-premises SQL Server Setup) to Key Vault.

    Note: If you encounter RBAC error while adding secrets, grant yourself IAM Role KeyVaultAdministrator under Access Control (IAM) of Azure Key Vault resource.

  4. Navigate to data lake resource and create the following containers under Data Storage:

    • bronze: to store raw ingested data
    • silver: to store enriched data
    • gold: to store final transformed data