ADF debug options when the pipeline has dataflow activity
These two options will appear only when the pipeline has the dataflow activity. 1- use dataflow debug session 2- use activity runtime The difference between these two options is how the IR is used when running the debug/activity runtime sessions. Use dataflow debug session: This option allows you to debug a data flow in a…
How to create External table with different collation in synapse
We can’t create external tables with a different collation on the table level, collation is only applicable on the column level in the external tables. See the below example reference. USE [mydbname];GOCREATE EXTERNAL TABLE populationExternalTable([country_code] VARCHAR (5) COLLATE Latin1_General_100_CI_AI_SC_UTF8,[country_name] VARCHAR (100) COLLATE Latin1_General_100_CI_AI_SC_UTF8,[year] smallint,[population] bigint)WITH (LOCATION = ‘csv/population/population.csv’,DATA_SOURCE = sqlondemanddemo,FILE_FORMAT = QuotedCSVWithHeaderFormat); See this microsoft…
How to provide read or browse access to user on all models/cube in specific Analysis Service
For Azure analysis services, the permissions of the cubes are handled at the database level. Even if we add owner role assignment using IAM, it only lets users log on to the analysis server. But they can’t be able to see the underlying dbs/models.When a user spins up analysis services by default, it will add…
Self hosted integration runtime consuming memory and not releasing
Here is a problem statment: The self hosted integration IR that we are using for copy activity is using all of the memory on the machine on which IR is hosted. It is not releasing memory Here are few things to check If your memory and CPU are consistently high then it could be due…
Azure Synapse Analytics: Publishing error Error while publishing: Cannot read properties of undefined (reading ‘constructor’)
This is due to broken resources in collaberation branch which is causing the issue. To resolve this, re-sync Git and Live mode by disconnecting and reconnecting Git.
Dynamic Data sync between Azure synapse analytics to SQL server
We have a requirement to set up a pipeline in ADF that copy all tables of Azure synapse analytics to SQL server dynamically.It should select all tables dynamically from synapse analytics db and copy the same table at the destination SQL server. It should be set up as incremental load. This can be achieved by using…
How synapse handles duplicate records with Primary and Unique constraints
Having primary key and/or unique key allows dedicated SQL pool engine to generate an optimal execution plan for a query. but it does not guarantee uniqueness or perform record uniqueness checks during insert operations. users needs to make sure primary key column or a unique constraint column values are unique. In Synapse, PRIMARY KEY is…
Azure Synapse Error : Could not allocate a new page for database ‘tempdb’ because of insufficient disk space
While creating NonClustered Index in synapse dedicated SQL pool, getting the below error:Could not allocate a new page for database ‘tempdb’ because of insufficient disk space in filegroup ‘PRIMARY’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.…
azure sql server vs synapse dedicated sql pool
Azure Synapse Dedicated SQL Pool Azure Synapse Dedicated SQL Pool (Previously Azure SQL Data Warehouse), a massively parallel processing database similar to other columnar-based scale-out database technologies such as Snowflake, Amazon Redshift, and Google BigQuery. To the end-user it is much like a traditional SQL Server, however, behind the scenes it distributes the storage and…
How to Drop a Partition in Azure Synapse Dedicated SQL Pool
Dedicated SQL pool supports partition splitting, merging, and switching. Each of these functions is executed using the ALTER TABLE statement. To delete an existing partition, we need to use “Partition switching” when the table is not empty. If the table is empty, we can merge the empty partitioning using the Merge command. Demo: How to…