For this months T-SQL Tuesday contribution I want to cover my Azure Synapse Link for SQL Server 2022 testing. Since we have been invited to discuss SQL Server 2022 this month.
Glenn Berry hosts this months T-SQL Tuesday. Glenn invites us to write about what we have been doing with SQL Server 2022.
You can read the original invitation by clicking on this link for the T-SQL Tuesday 154 invitation. Alternatively, you can click on the image below.
To quickly explain what this feature does. Azure Synapse Link for SQL Server 2022 allows you to replicate changes to your data stored in a SQL Server 2022 database to an Azure Synapse Analytics dedicated SQL Pool. As per the overview diagram below.
It recently came out of preview and is now generally available.
To help with some jargon I have included links for some the terms in this post, so enjoy.
Azure Synapse Link for SQL Server 2022 testing
By my own admission, these days I work with SQL Server a lot less than I did a decade ago. Due to the fact that I am currently a Lead BI & Analytics architect.
I must admit it has been an interesting experience going from supporting SQL Server databases for companies in the Fortune 500 list to my current role.
In reality, these days I do a lot more work with Azure Data Engineering and DevOps related services like Azure DevOps. Which is great, because I can still use some of my SQL Server knowledge with these products.
So, when the Azure Synapse Link for SQL Server 2022 feature was announced I wanted to test its capabilities. Because it gave me a chance to look into a feature linked to both SQL Server and Azure Synapse Analytics.
After doing some tests I published a post which covered my initial tests for Azure Synapse Link for SQL Server 2022.
Tipping point
Whilst writing that post I noticed something interesting. Which is that when you first setup Azure Synapse Link for SQL Server 2022 the initial files created are parquet files. However, additional files created afterwards are csv files.
Because I have always been interested in the internals of applications and services, I decided to dig a bit deeper into this. To see if there was a tipping point where parquet files would be created instead of csv files. Short answer appears to be not at the moment.
Even though you already know the answer, you can still read my post about these file tests. You can amuse yourself by seeing what happened when I thought it would be a good idea to insert one-hundred million rows into the SQL Server 2022 database.
I did look into best way to perform CI/CD for it as well. My recommended option at the moment is to perform CI/CD on the SQL Server 2022 database and select to recreate the tables in your link settings.
Final words
Well, I hope this post about testing Azure Synapse Link for SQL Server 2022 has interested some of you. Because it is an interesting feature to look into.
I want to thank Glenn for inviting us all to write about this. Because I think you are going to read some fantastic posts this month.
Of course, if you have any comments or queries about this post feel free to leave a comment.
Azure Synapse Link does sound like an interesting feature that might be an alternative to SQL Server CDC in some situations. Thanks for participating in T-SQL Tuesday this month!
[…] (Blog/Twitter) wrote a post called “T-SQL Tuesday 154 – Azure Synapse Link for SQL Server 2022 testing“. In his post, Kevin wrote about Azure Synapse Link for SQL Server 2022, which allows you to […]
Thank you for mentioning Parquet files, I have never heard about this format, and it is even more interesting for me than the described feature 😉 It’s fascinating, all of these data streams flowing here and there between the servers… Decades ago we have only main and backup stores, and it was not so huge, now everything is so different and weird. And requires next different and weird services. Nevertheless, worth to check it.
Gerard, you are welcome. I do recommend looking into Delta Lakes as well https://learn.microsoft.com/en-us/azure/databricks/delta/
[…] I mentioned in my T-SQL Tuesday 154 contribution, I work with SQL Server a lot less than I did a few years […]
[…] Others are looking at solutions that involves SQL Server along with other services. For instance, Azure Synapse Link for SQL Server 2022. Like in the below example from a previous T-SQL Tuesday post. […]