1

I have a bunch of on-premise SQL databases that contain data related to HR. One database could be pure employee data, such as staff records, leaver records or recruitment records. Another database could be from a external provider of services, which would tell me what benefits my employees are utilising. A final example could be salary review data, where data is generated by the business each year by line managers submitting employee performance scores.

I'm currently exploring Azure as I want a cloud solution for my data. I need to store all of these datasets in a central location, with some standard governance applied. I also need to have this data available to applications such as Power BI for BI requirements and I also need machine learning capabilities for data science projects.

I currently use SSIS to get my data in SQL Server, however my research has suggested using Azure Data Factory, Azure Data Lake and Azure Synapse to manage my data.

I don't really know where to start, any guidance would be great! Thank you.

0

How frequently are the database changing and how much downtime can your business allow?

There are a multitude of non-realtime ways to move a snapshot of the databases such as:

  1. Full Database Backups

  2. BACPAC Files

  3. Scripting the Database (with Data)

  4. 3rd Party Synchronization Tools (RedGate's SQL Compare)

There's also a series of more realtime continuous synchronization options as well:

  1. Replication

  2. AlwaysOn Availability Groups

  3. Log Shipping

  4. SSIS

They each have their pros and cons, depending on your needs. If you need a one-time move and can schedule a maintenance window where the databases won't change before you cut-over, then the BACPAC file might be the simplest way to migrate into Azure. A tool like RedGate's SQL Compare is also very handy, especially if you think you'll need to move things more than once. If you need on-going synchronization to the cloud for a period of time before you can cut-over then AlwaysOn Availability Groups might be the simplest choice or Replication (Transactional or Snapshot depending on how frequently your data changes). But please read over each of them and decide on what best fits your use case.


Regarding your question in the comments of choosing a Data Lake vs using Synapse:

Neither of them are bound to specific file types, all of them can consume pretty much any file type, so think of the systems being file type agnostic.

The purpose of a Data Lake is to house varying structured data before it becomes structured and consumed elsewhere. E.g. you can have two Excel files with similar data but one is missing a few columns from the other and visa-versa, but both are easily digestible into the same Data Lake so that you can centralize the data into one place and eventually report off it. The Data Lake may be able to consume different data and file types more easily though.

Synapse is more structured like a database (or series of databases) so importing data into it requires a little bit different workflow. Since you're sticking to standardized data storage formats you should be fine to use Synapse to pull the data into it as well (so either system will work in terms of pulling the data in, Data Lakes are just more flexible in the varying structures of data it can house in one place). Synapse, being more structured than a Data Lake, means it's more ready to report off of upfront too. A Date Lake likely will require you to setup a series of process to transform and move the data around before you can report off it.

So it really comes down to how structured vs unstructured your data is, and how much ETL you want to develop into the entire workflow. But one system isn't necessarily better than the other, they just have slightly different purposes.

Data Lake for varying structures of data so more flexible for storing the data in one place, but more ETL work if needed to report off of, Synapse for more structured data so less flexible but more ready to report directly off of. My final thoughts are Data Lakes are good for data that's being consolidated from many sources to be reshipped to some consuming system, so basically just a centralized place of multiple different datasets as a staging ground before moved to its final consuming destination.

5
  • So my SQL databases are only used for BI reporting, analytics and soon to be ML. There is only a very small team using them and we only refresh our data monthly (maybe weekly in the future). So downtime isn't an issue. I suppose my question was more around, should I use blob storages, a data lake or synapse to keep my data after my ETL Jan 6 at 13:22
  • @PhilCollins Depending on how structured your data is, Synapse or Data Lake should be what you look into. Blob storage is not really great for reporting off of because it's just a single "box" of a bunch of serialized unstructured data.
    – J.D.
    Jan 6 at 14:31
  • Thanks @J.D., so my understanding of Data Lake is that it's a repository for practically any file type. I'm only going to be working with text based file types e.g. excel, csv or other DBs. Therefore, is Synapse a better solution and what does it offer more than just having a bunch of DBs on a sever ? thanks Jan 7 at 12:01
  • @PhilCollins Please see my updated answer to your last few questions. :)
    – J.D.
    Jan 7 at 13:02
  • 1
    Thanks so much! Jan 7 at 13:25
3

the first obvious solution could be the adoption of a SQL Server VM hosted on azure. You can move there databases and ssis packages in any traditional ways. This is an Azure Iaas solution and usually is the most expensive. You can reduce cost moving your on prem sql license wiht the "Bring Your own license" solution coming from software assurance license mobility option.

But you can also adopt Azure Paas solution using:

I suggest you the second one because it is almost equal to the a on prem sql server instance. But both laks of ssis solution.

You pointed right to Azure Data Factory to relace dtsx packages but you can also run the same dtsx packages inside azure data factory:

https://docs.microsoft.com/it-it/azure/data-factory/create-azure-ssis-integration-runtime

In the meantime you redisign the ssis packages in ADF you can eventually run them temporarly in a Azure VM.

For moving database to an Azure SQL Server Managed instance i suggest you to use DMA:

https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15

You can do a online or offline migration. Look at this:

https://azure.microsoft.com/en-us/resources/videos/data-migration-demo-using-azure-migrate-dma-and-dms/

Here you can find a free lab to test the procedure: https://github.com/microsoft/MCW-Migrating-SQL-databases-to-Azure

0

Another aspect is which of the Azure SQL flavors you should go for:

SQL Database: PaaS, least "DBA-work" for you to do. But least compatibility with On-Prem SQL Server. For instance Agent isn't there and no cross-database queries.

Managed Instance: You do get an instance with close to 100% compatibility for the db engine. MS takes care of the OS, SQL install, patching, backup etc.

VM in Azure. Obviously 100% compatibility since it is a VM with SQL Server installed. But no big difference from a management standpoint from managing it yourself. Except you don't deal with the HW and hypervisor.

SQL Database is probably the test bet if your applications work with it. Note also that there are flavors of SQL Database, like Serverless and Hyperscale.

So an early step is to read up on the details for these offerings so you can determine which of them suits you.

1
  • this is really helpful thank you. I do rely on SSIS, which has it's packages deployed on the server with Agent taking care of them on a schedule. So therefore, I still want that capability. I've read that data factory is something can do this in Azure? Jan 6 at 13:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.