Web Content Display Web Content Display

Breadcrumb Breadcrumb

Analytics Blogs

Back

Moving Your Data Warehouse from SQL Server to the Cloud!

Jose Hernandez
Posted 10/22/20

In this blog, I would like to discuss moving your data warehouse from SQL Server (or any on-premise RDBMS) to the cloud, more specifically, to Azure. Before I do that, I would like to lay down a foundation by acknowledging the modern data warehouse.

 

data warehouse must support more use cases than ever before.  Since many of these uses cases are a result of the Big Data phenomenon, we must redefine the data warehouse and I will refer to this as the modern data warehouse. I am working on a new blog that talks about the modern data warehouse, but for the purpose of this blog I will highlight the relevant characteristics of the modern data warehouse. The modern data warehouse must: 

  • Be deployed in the cloud 
  • Must support structured, semi-structured and unstructured data (or at least two of these) 
  • Incorporates a data lake 
  • Includes ETL (extract transform & load) as well as ELT (extract, load & transform) data pipelines 
  • Supports descriptive, predictive and prescriptive analytics

 

Given this definition of the modern data warehouse, an obvious starting place for organizations with a data warehouse is to move that on-premise data warehouse to the Azure cloud.  This sets the stage for the modern data warehouse.  I will illustrate the example and provide Azure technology labels...

 

 

Notice the diagram is limited to traditional data warehouse use cases, but lines up with what is needed for the modern data warehouse.  In essence we are talking about a lift & shift.  My scenario is based on Microsoft technologies, but I will address other technologies as well.

 

The first item we address is which Azure database technology should we consider?  For the purpose of a data warehouse we consider Azure SQL and Azure Synapse.

 

Azure has lots of technologies, which one for a modern data warehouse?

 

Azure has many options that can be incorporated into the modern data warehouse, for our discussion we focus on the options for the traditional data warehouse; SQL (think SQL Server) and Azure Synapse (think MPP).  Azure SQL is very similar to your on-premise SQL Server deployment, the big difference is that you don’t deal with hardware and infrastructureAzure Synapse is a limitless analytics service that not only handles your traditional data warehouse use cases (with a massive performance boost) but also handles modern Big Data use cases. 

So how do you determine where to jump to first? Remember, my scenario is for data warehousing, so that said, there are a few questions you can ask yourself to determine this: 

  • Do have more than a terabyte of data in your data warehouse? 
  • Do I plan to run analytics on substantial amounts of data? 
  • Will I need to scale compute and storage (in the near term)?

If your answer to one or more of these is “yes”. Then consider Azure Synapse. If not, you can use Azure SQL.  I’ve made it sound simple, and I admit, there are more variables to consider.  I simply want to set your frame of mind.  To dive into the other variables, you should reach out to Dunn Solutions and we can discuss.

 

If you are currently using SQL Server, then moving to Azure SQL requires very little changes to your data model and implementation.  If you end up moving to Azure Synapse, there are several design changes that need to be made.  While they are not difficult to understand, those changes will take time and that is something Dunn Solutions can certainly help you with.

 

So, let’s plan the migration!

 

Now that you have decided to migrate your data warehouse to Azure and start your modern data warehouse platform, it’s important to plan the migration.  A primary goal of planning is to ensure that your data, schemas, and ETL code are compatible with Azure SQL (especially if moving from a non-SQL Server data warehouse) or Azure Synapse.  There will be compatibility differences that you will need to work around.  Also consider that the initial load of your modern data warehouse will take time.  Taking time to plan will save time when you actually do it.

 

If you have determined that your modern data warehouse will start with Azure Synapse, you will have to implement design changes to take full advantage of the high query performance that Synapse is designed to provide.  Designs for data warehouses that scale are different that traditional data warehouses designs.  While some of these can be made after migration, making design changes during the migration can save time later.

 

Let’s do it!

 

So, the plans are set … let’s do it!  Do what?  Migrate your schemas, migrate your code then migrate your data.  This is where the action is, this is where Dunn Solutions consultants will take care of things for you.  DIY is a great concept, but this migration from on-premise to the cloud is a one-time thing for your organization.  There is little value in dealing with this on your own and learning from your own experiences and challenges.  Take my advice, Dunn Solutions does this kind of work for a living, so we can do the heavy lifting. You and your organization do what you do, and Dunn Solutions’ goal is to get you set up to do what you do as quickly as possible.

 

Benefits to building your modern data warehouse

 

You certainly can move your on-premise data warehouse to the cloud simply to move the infrastructure to the cloud.  There is value in doing this just by being in the cloud (see You can start your big data project tomorrow).  But this is a great opportunity for your organization to build its modern data warehouse.  I will be writing a blog about the modern data warehouse, but until then, if you would like to discuss the benefits of migrating your on-premise data warehouse to Azure, please contact Dunn Solutions Group and we can explore the benefits and options together. 

 

 

 

  •