0

I have been working in on-premises SQL Server since 1999 and am now trying to engage with Azure SQL.

I have identified one of our SQL DBs that we want to lift into Azure for reporting. (We will be connecting Tableau Cloud and have already verified that this is best for us using sample data in Azure SQL compared against Tableau Bridge installed on our SQL Server.)

We want to daily take a SQL DB backup and restore it in Azure. (We may increase the frequency later)

From what I am gleaning, an on-premises SQL DB is not restored directly to an Azure SQL Server.

However, such a backup can be moved into Azure Storage using PowerShell. (We may have to turn on an Azure Storage resource with some cost associated with it)

Then, I am trying to learn about restoring that DB. I see in Microsoft documentation about Azure SQL restore, that "You can't overwrite an existing database during restore." This is not what I am used to in my on-premises SQL background. So, maybe some more complicated choreography is necessary such as restoring the DB with a new name and then deleting the old one and renaming the new?

I see we may have another option using Log Replay Service if we instead use a Azure SQL Managed Instance, which means we would have more Azure SQL management responsibilities.

Does anyone have experience to share on either approach that could assist me in firming up a plan?

Thank you

Tom Schulte
  • 419
  • 1
  • 6
  • 20
  • Have you considered setting up an AG with a readable secondary in Azure for reporting? – Stu May 03 '23 at 22:48
  • Thank you, I am going to read about that and consider more in ["Offload read-only workload to secondary replica of an Always On availability group"](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-2017). – Tom Schulte May 03 '23 at 22:53
  • Although only tangentially related to your question, you should consider performing backups of [on-prem SQL Servers to Azure Blob Storage](https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver16&tabs=SSMS) - whether for this task, or to enable off-site backups – Anthony Norwood May 04 '23 at 04:19
  • I haven't used this, but it may be another option. I saw this when reading the "[What's new](https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/doc-changes-updates-release-notes-whats-new?view=azuresql#general-availability-ga)" for SQL Server Managed instances. In April they implemented a [Managed Instance Link feature from SQL Server 2016 and 2019](https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-feature-overview?view=azuresql) but it may not have flowed through to other documentation yet. – seanb May 05 '23 at 16:19

1 Answers1

1

For Azure SQL Database your best bet is Replication to Azure SQL Database from on-prem to Azure. In Managed Instance or SQL Server on Azure VMs you can additionally restore backups from on-prem.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thank you David. I am going with replication to an Azure SQL Database I found, with the MS documentation, [this blog post](https://www.sqlservercentral.com/blogs/configuring-a-subscription-to-an-azure-sql) helpful in setting it up. – Tom Schulte May 07 '23 at 15:15