Our first practice will be loading a local SQL server database (on-premises) into Azure SQL DW. There are several things we need to think about the migrating.
- Technical approach. There are several ways to move data: SQL Server Integration Services, Data Factory, Data Warehouse Migration utility and Azure commands...
- Historical Loading and Incremental Loading. Consider the different way when loading incremental data.
- Security configuration. How to move the user/group accounts into Azure DW
- SQL Code migration. Even though this is SQL to SQL, there is still some problems need to be addressed. For example, limitation in Azure DW
No matter which approach we will be using, there are four major step we have to follow: Move Schema, Move Data and Move Code.
1) Move Schema. Move schema means we have to create the tables' schema before loading the data. Typically in SQL Server, you can generate tables' script from SSMS, or by using other 3rd party tools. Keep in mind that there are not all SQL Server objects will be needed in Azure DW, for example 'primary key'. In the generated scripts, please add the following statement to follow the 'Create Table' :
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
This will create the Distribution column (will discuss later) and Columnstore index.
2) Move Data. This is the core step. Moving data from on-premises SQL to Azure DW has two kinds of 'Moving': Historical Loading and Incremental Loading. In Data Ware housing methodology, the Historical Loading means one time loading or fresh loading, it is being used to load the entire on-premises data into Azure DW. In most of articles, it introduces the Historical Loading. Incremental Loading means we only load new records into Azure DW, this process should be happened based on scheduled frequencies. Both Incremental Loading and Historical Loading can use same technique, but may be not. I will cover this discussion later.
Move data by using SQL Server Integration Services
Probably this is easiest way to do both Historical Loading and Incremental Loading.