Azure Synapse dedicated SQL pool vs. Azure SQL vs SQL
Hello, currently we operate on-premises PostgreSQL DB that we use as our data warehouse. I would like to set up a data warehouse in Azure, but the Azure portal is bit confusing, as there are multiple options: 1) in Azure Synapse, there is dedicated SQL pool (formerly labeled as Azure Data Warehouse) Then, outside Azure Synapse there are two additional options: 2) Azure SQL database ( /BrowseResource/resourceType/Microsoft.Sql%2Fazuresql ) and 3) "SQL database" (/BrowseResource/resourceType/Microsoft.Sql%2Fservers%2Fdatabases) https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/data-warehousing describes that Azure SQL (#2 above) uses symmetric multiprocessing (SMP) while "Azure Synapse Analytics" (#1) above uses massively parallel processing (MPP). My data needs are not so vast to utilize the MPP. Thus it seems I should be considering #2, i.e. outside the Synapse Analytics. Azure SQL (#2) above further branches into "Single Database", "Elastic pool" and "Single instance managed DB". I am guessing that for my scenario, "Azure SQL - Single Database" is the best option. If I choose this, will I be able to use this storage in Azure Synapse Analytics? Furthermore, to use Azure Data Factory (ADF) - if I set up "Azure SQL - Single Database", should I be aiming to use Data Factory within Azure Synapse Analytics, or outside it (i.e. use ADF in Azure portal)?Restore database across servers(Azure SQL Database and Azure SQL managed instance)- Azure Automation
In this article, we consider the scenario where customers would like to restore their Azure SQL database and managed database from one Azure SQL server to another Azure SQL server, for example for development reasons. The article will provide you with the steps to achieve this by automating the job using Azure AutomationAzure SQL Database or SQL Managed Instance Database used data space is much larger than expected
In this article we consider the scenario where the used size of an Azure SQL Database or SQL Managed Instance Database is much larger than expected when compared with the actual number of records in the tables and how to resolve it.Monitor SQL database size increase
Hi I want to be able to monitor how the size of my database increases over time. I have created a SQL server VM and have wired up the Log Analytics and set it to capture the SQL performance counter called "SQLServer:Databases(*)\Data Files(s) Size (KB)". The data is being captured because when I run the following query I get results. Perf | where ObjectName =="SQLServer:Databases" and CounterName == "Data File(s) Size (KB)" and InstanceName == "Jason_DB" | project TimeGenerated, CounterName, CounterValue I have been running a SQL Job over night on the database to insert two rows into a table every 5 min but I'm only seeing the database size of "8,192"! The chart is linear and show no "Data file" size increase! Is there something wrong with my query or do I not understand the SQL performance counter in collecting? Current query Perf | where ObjectName =="SQLServer:Databases" and CounterName == "Data File(s) Size (KB)" and InstanceName == "Jason_DB" | project TimeGenerated, CounterName, CounterValue | summarize avg(CounterValue) by CounterName, bin(TimeGenerated, 5m) | render timechart Rendered chartLearning from Expertise#10: Why no restore point available?!
Introduction In today's blog article, we will try to address and clarify some points on how Azure SQL DB and Managed Instance Point in Time Restore (PiTR) works, especially when it comes to failover group and Geo-replication (Azure SQL DB), and we are going to provide answers to common queries. Use case: On some occasions, after the failover is initiated, the current Primary DR will start a new backup chain from that point and old backups are available on the current secondary DR. If we want to restore the backups which exists in Secondary it will not allow us to perform, apparently restore cannot be initiated on the Primary as the backup is not available. Also, sometimes we observe in secondary DR for few databases, PitR restore point is available and for few databases it shows “no restore point available” Common Questions. Do we expect to lose the PITR ability after failover? Why are the backups still listed and available on the new secondary if they are not usable? Why do some databases show the restore point and why for few databases it shows “no restore point available”? Here is the scenario: Day 1 - > Geo-Replication/ Failover group setup, Primary in North Europe and Secondary in West Europe, PITR backup retention is set to 7 days. Day 2 -> Customer initiated failover, now Primary is in West Europe and secondary is in North Europe Day 4 -> Customer would like to restore the database to the state of Day1 which is possible as 7 days retention was set to PITR backups, and Day1 is 3 days back from current point in time As we are running backups only for the Primary DR, the new Primary in West Europe does not have the requested PITR backup (Day 1) The needed backup is “visible” on the backups listed for current secondary in North Europe The customer cannot restore to requested PITR as The restore cannot be initiated on the secondary as this is not possible for Secondary DR. The restore cannot be initiated on the primary as the backup is not there. Also, sometimes you will be seeing available PITR backups on Failover secondary server databases and for some databases it shows as “No restore point available” For the databases where the restore point is available, they can’t be able to restore it. The restore cannot be initiated on the secondary as this is not possible for Secondary DR. The restore cannot be initiated on the primary as the backup is not there. Clarifications In case the database was recently created, the first full backup might be still in the queue, so it might be a promising idea that you wait for some time before checking again. Failover groups is a disaster recovery feature that allows our customers to recover multiple related databases in a secondary region, after a catastrophic failure or other unplanned event that results in full or partial loss of service’s availability in the primary region. Database backups are an essential part of any business continuity and disaster recovery strategy because they protect your data from accidental corruption or deletion. It is important for us to understand that we are not losing any backups, in case the current region was not the primary at the desired point in time where your restoring point is holding, we will need to failover to the other region and make it as Primary, then you should be able to restore it. Backups are only taken on the Primary server, there can be several justifications why we can see PitR on some DBs and not for others on the Secondary Server. For the Databases that have PitR it is most probably because there was a failover that caused the Primary to become Secondary, as we have already had backups taken on the old primary which is secondary now. The DBs that do not have PitR They could have been provisioned after the last failover happened, so the backups are only available on the primary server Or they were added to the failover group after the last failover that will also cause the backups to be taken on the primary It should be fair to say that we will likely “fail back” once availability is restored in the original primary region because we might have picked that region for a reason. Note: - In case no geo-failover happened nor the database was recently created, and you still see “no restore point available,” please contact Microsoft support. Alternatives: you can perform Export operation on the secondary and import it on Primary DR. You can leverage geo redundant backup and Geo-restore, taking into consideration that Geo-restore is the most basic disaster-recovery solution available in SQL Database and SQL MI. It relies on automatically created geo-replicated backups with a recovery point objective (RPO) up to 1 hour and an estimated recovery time of up to 12 hours. Note: Azure SQL Managed Instance does not currently support exporting a database to a BACPAC file using the Azure portal or Azure PowerShell. To export a managed instance into a BACPAC file, use SQL Server Management Studio (SSMS) or SQLPackage. References: Restore a database from a backup - Azure SQL Database & SQL Managed Instance | Microsoft Docs Automatic, geo-redundant backups - Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs Azure SQL Database Point in Time Restore | Azure Blog and Updates | Microsoft Azure Azure SQL Database Geo-Restore | Azure Blog and Updates | Microsoft Azure Disclaimer Please note that the products and options presented in this article are subject to change. This article reflects the database backup options available for Azure SQL database and Azure SQL managed instance in June 2022. Closing remarks We hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below. Raviteja Devarakonda (Author) Ahmed Mahmoud (Co-Author)Failure during copy from blob to sql db using ADF
Hello, I get this error when using Azure Data Factory for copying from blob to azure SQL DB:- Database operation failed. Error message from database execution : ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.. I am able to connect to my DB using SSMS and I have enabled firewall to connect to Azure services. Has anyone faced this error when using ADF / know how to fix it? Thanks in advance.Azure SQL Database now offers zone redundant Premium databases and elastic pools
Azure SQL Database Premium tier supports multiple redundant replicas for each database that are automatically provisioned in the same datacenter within a region. This design leverages the SQL Server AlwaysON technology and provides resilience to server failures with 99.99% availability SLA and RPO=0. With the introduction of https://azure.microsoft.com/en-us/updates/azure-availability-zones/, we are happy to announce that the SQL Database now offers built-in support of the Availability Zones in its Premium service tier. By placing the individual database replicas to different availability zones, it makes the Premium databases resilient to the much larger set of failures, including catastrophic datacenter outages. The built-in support of Availability Zones further enhances the High Availability (HA) solutions in Azure SQL Database. For more information see https://docs.microsoft.com/en-us/azure/sql-database/sql-database-high-availability. To take advantage of this capability, you simply select the zone redundant option during the database or elastic pool creation. You can also enable it for existing databases or pools. If the availability zones are supported in the region where your database or pool is deployed, Azure SQL will automatically reconfigure it without any downtime. Read more about it in the https://azure.microsoft.com/en-us/blog/azure-sql-database-now-offers-zone-redundant-premium-databases-and-elastic-pools/.
SSIS Problem Downloading from Azure Blob
Hi All, I'm thrilled to be working on an SSIS project that will (hopefully) get deployed to Data Factory. While getting my SSIS job setup, I added a step to download files from Azure Blob Storage. After adding a Azure Blob Download task, in the control flow, and entering in the storage configuration (test worked successfully), I get the following errors: SSIS package "C:\....\Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Error: 0xC001000E at Package: The connection "{27A6966C-8398-4C75-84C3-6007B96EEED4}" is not found. This error is thrown by Connections collection when the specific connection element is not found. Error: 0xC001000E at Package: The connection "{27A6966C-8398-4C75-84C3-6007B96EEED4}" is not found. This error is thrown by Connections collection when the specific connection element is not found. Error: 0xC001000E at Package: The connection "{27A6966C-8398-4C75-84C3-6007B96EEED4}" is not found. This error is thrown by Connections collection when the specific connection element is not found. Error: 0xC0024102 at Azure Blob Download Task: The Validate method on the task failed, and returned error code 0x80131500 (The connection "{27A6966C-8398-4C75-84C3-6007B96EEED4}" is not found. This error is thrown by Connections collection when the specific connection element is not found. ). The Validate method must succeed and indicate the result using an "out" parameter. Error: 0xC0024107 at Azure Blob Download Task: There were errors during task validation. Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (10) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "C:\....Package.dtsx" finished: Failure. The program '[18968] DtsDebugHost.exe: DTS' has exited with code 0 (0x0). I have confirmed that the GUID here is indeed pointing to the Azure Blob Connection. I'm not sure how to fix, or to get more information on the problem. For what it's worth, this error pops up very soon after launching in Debug mode - only some pre-validation runs (successfully) prior to this error. Any help is appreciated. Thanks!
