Sql-server – How to copy Azure SQL database to on-premises SQL Server daily for reporting

azureazure-sql-databasereportingsql server

I have a database as a service SQL instance in Azure, I want to copy this database to a reporting server located in a virtual machine, and this is not SQL as a service, so my COPY statement won't work, this is what I have:

I'm tryin to copy from dbserver-prod to the server marked in the red rectangle

This is the statement I've used to copy from SQL AAS instances and has been working fine but now I get an error: :

CREATE DATABASE Database_Reporting AS COPY OF [dbserver-prod].[database];

My aim is to have prod database copied to this server once a day for reporting. Any thoughts on how this can be done will be very helpful.

Best Answer

You can't copy direct from AzureSQL to on-prem SQL (even on-prem SQL running in an Azure VM) that way.

To copy the database manually you need to export it via SSMS (or the relevant command line tool) or the Azure portal, transfer the resulting bacpak file to the target server and import it there using SSMS or the relevant command-line tools. If this is to be a regular thing (weekly or daily to provide a regularly updated reporting copy?) then you'll want to try automate that via powershell script run from task scheduler or similar.

Is there a reason that you want your extra copy in an on-prem SQL Server install instead of also being in AzureSQL? If not then you might consider changing that as CREATE AS COPY OF is likely to be significantly quicker than export+transfer+import.

Another option after creating your initial copy is to use SSIS to update it if it is easy to work out (from audit trail records, change tracking, etc.) what data has changed since the last update. This may be far more efficient as you only need to transfer new/changed data, though it relies on you keeping the database schema on the on-prem side identical to the production schema in AzureSQL. You may also wish to look into Azure Data Sync (currently in preview): https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data

Related Solutions

Sql-server – Does installing SQL Server Database Engine and Reporting Services in Azure require two virtual machines

To purely answer your question:

If you set up two different VMs one running the SQL Server and the other one hosting the SQL Server Reporting Services, you would be charged twice the per-hour price of a Virtual Machine, but only once for your the SQL Server License.

Best Practice

I guess the "best practice" answer would be to rely on SQL Azure and SQL Azure Reporting Services, since they provide licensing, installation, maintenance, redundancy, scalability and security built-in.

But since this doesn't seem to be an option to you, here is what I would do based on my experience:

Put the database that hosts the data the reports are built on in SQL Azure. There you can restrict access to the database to only other Windows Azure components (Websites, Hosted Services, VMs) or a given IP-Range
Setup the SQL Server Reporting Services on a Virtual Machine (not a Hosted Service), because Hosted Services lack data persistence
Host your web application on Azure Websites (they are for free at the moment). You can later switch to Hosted Services if you need more performance.

Hope this answer helps.

Sql-server – Create copy of Azure SQL Database using SQL Server Management Studio

Try reviewing DMVs sys.dm_database_copies and sys.dm_operations_status which are Azure SQL Database-only, and meant for reviewing progress on these type of operations eg

SELECT *
FROM sys.dm_operation_status

SELECT *
FROM sys.dm_database_copies

Review the state_desc and error_desc columns. Post the output here if required.

Best Answer

Related Solutions

Sql-server – Does installing SQL Server Database Engine and Reporting Services in Azure require two virtual machines

Sql-server – Create copy of Azure SQL Database using SQL Server Management Studio

Related Question