In my first blog-post about Striim, I showed you how to create an Initial Load from an on-premise database to an Azure SQL DB.
In this second blog post I will create an application which will stream Data Changes from a CDC-Enabled on-premise SQL Database to my Azure SQL Database.
The first step is to enable CDC (Chane Data Capture) on the on-premise SQL Server database.
CDC uses the SQL Server agent to record insert, update, and delete activity that applies to a table.
I will enable CDC on my Pokemon database and also on my table named Pokemons by executing the following script:
select is_cdc_enabled, * from sys.databases where name = 'Pokemon'
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Pokemons',@role_name = null
select is_tracked_by_cdc, * from sys.tables where name = 'Pokemons'
On Striim Platform, I will create a new app named StreamReplicationPok and select a Streaming Integration from SQL Server CDC to Azure SQL Database:
I name my new application:
I set my source database information:
Striim validates the connectivity to my instance and database, checks the metadata and the CDC permissions:
Same as last blog, I will now select my schema to move: dbo and after select my table to load from my schema: my Pokemons table.
After some validation I will now enter information to connect to my Azure SQL Database:
Once done the application is created and we can review our populated information for the source and target:
We can deploy the application and once done Start the App.
My application is now waiting that some DML commands are running against my source to replicate it on my target:
I will generate some insert, update and delete on my source table and check what I can see on the monitor progress dashboard:
I can visualize the DML I have executed against my source table and which have been replicated to my Azure SQL Database target.
More information are available by clicking the “View Detailed Metrics”:
My Azure SQL Database is now ready to accept user connections for Offload Operational Reporting with real-time data as it is continuously feeding with data modification from my source with no overhead on the primary.
We should also use the same process to migrate a database to the cloud without any downtime or very limited one.
It was very interesting to test Striim which I didn’t know and gave me the opportunity to discover a tool very similar in my opinion to Qlik Replicate.
I hope it can help you for a future migration or live data replication.