0

I have a VM in Azure running a single SQL Server instance.

I also have recently setup Power BI to refresh from this source at 1am every morning. Unfortunately, this refresh is causing performance issues, where all queries/operations are timing out due to stress.

What are my options regarding a secondary DB for reporting purposes? Main requirements are ease of maintenance and cost (dont need anything enterprise level).

Things that come to mind:

  • Secondary DB on same VM. Use replication to mirror data
  • Another cheap VM. Use replication
  • Use sql server availability sets, connect to read only replica
  • SQL data warehouse

Can anyone provide some guidance, or ask questions that may help find my answer?

Thanks.

RPM1984
  • 72,246
  • 58
  • 225
  • 350

3 Answers3

0

I think Always ON availability group with secondary read-only replica will be best suited for your needs.

Building a separate DW for reporting purpose will be an overkill, as your reporting needs are satisfied from current database already, except for performance.

Transactional replication could be of help here. But, it also needs lot of knowledge on setup and maintenance.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
0

I can think of several options, but in general this sounds like a canonical OLTP vs. OLAP issue, or a call for data warehouse, but since you are on the budget, let's consider low cost options.

Assuming the databases are small (GBs not TBs), I would separate operational and reporting instances either to be on the same machine if it is a pretty beefy machine, or better have two VMs so you can manage capacity separately.

I would consider replication from one instance to another.

Edmon
  • 4,752
  • 4
  • 32
  • 42
0

Can you boost your VM resources during the period of the Power BI refresh only? That's one of the key benefits of Azure - you can scale up and down and save money. How long does the refresh take? Who is using your DB at 1am?

I guess for a VM it's difficult to do this so you'd need to migrate to SQL Azure rather than a VM

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91