Azure Synapse Analytics (Azure SQL Data Warehouse) vs Azure SQL Database

• Quick to return data. Queries in a SQL data warehouse architecture tend to return data much more quickly than a OLTP setup. Especially with columnar indexes.
• Ability to manage extremely large SQL tables. Our databases contain billions of records. This would be unwieldy without a proper SQL datawarehouse
• Backup and replication. Because we're already using SQL, moving the data to a datawarehouse makes it easier to manage as our users are already familiar with SQL.

Read full review

• Maintenance is always an issue, so using a cloud solution saves a lot of trouble.
• On premise solutions always suffer from fragmented implementations here and there, where several "dba's" keep track of security and maintenance. With a cloud database it's much easier to keep a central overview.
• Security options in SQL database are next level... data masking, hiding sensitive data where always neglected on premise, whereas you'll get this automatically in the cloud.

Read full review

• Integrating external 3rd party data sources is very easy in Snowflake and it’s missing in Azure Synapse
• Master data services and data quality services are missing in Azure Synapse. They are useful features present in on Orem Sql server
• Resource usage reports (top 10 expensive queries, most frequently run queries, etc) are a feature that can be added in Azure Synapse. It’s present in an on-prem SQL server. DMVs are there but viewing it visually as a report is more helpful.

Read full review

• One needs to be aware that some T-SQL features are simply not available.
• The programmatic access to server, trace flags, hardware from within Azure SQL Database is taken away (for a good reason).
• No SQL Agent so your jobs need to be orchestrated differently.
• The maximum concurrent logins maybe an unexpected problem.
• Sudden disconnects.
• The developers and admin must study the capacity and tier usage limits https://docs.microsoft.com/en-us/azure/azure-subscription-service-limits otherwise some errors or even transaction aborts never seen before can occur.
• Only one Latin Collation choice.
• There is no way to debug T-SQL ( a big drawback in my point of view).

Read full review

• Azure Synapse has predominately reduced the time being used for processing data being extracted from multiple sources
• Ability to integrate multiple data sources and create a single dataset that could be used for further analytics to find KPIs

Read full review

• Perfect for small and medium databases, being very cost effective.
• As a Platform as a Service, there is no concern about patches, upgrades and end of life.
• Be aware of security and network capabilities. The service cannot run in the VNET as Azure Virtual Machines do.

Read full review