0

I am trying to determine the best way to replicate a SQL Server database from its production environment to another server for reporting purposes.

A cloud-based software application we use is based upon a SQL Server database. The vendor has given us access to their schema, and we build our own queries and views for reporting purposes. A full backup of the production database is currently taken each night and sent via Secure FTP to us, at which point we restore the backup to our reporting server. This process is not reliable as often the SFTP transfer fails.

I am looking for an alternative solution, perhaps along the lines of Log Shipping or Database Mirroring. This is not a high-availability exercise, just looking for the best way to get a read-only copy of the database to drive reports.

Any suggestions would be very welcome.

wildermind
  • 13
  • 1
  • 4
  • Which version of SQL Server, which edition, Std or Ent? Are there any network issues (assuming there are, from "SFTP failing often")? How up-to-date the data should be for reporting purposes? – dean Mar 23 '17 at 12:13

1 Answers1

0

We are using AlwaysOn as a high-availability solution, but an added benefit is that we can use the secondary server as a read-only, near-realtime reporting database server.

Since you already mentioned Log Shipping and Database Mirroring, this is also a viable solution for you. It's basically a pretty similar technology and you can use asynchronous synchronisation to reduce waits on the primary server. Also, you would not use the automatic fail-over functionality, but take advantage of the rather straightforward configuration and usage of AlwaysOn technology.

Erwin Dockx
  • 273
  • 1
  • 8
  • You might consider mentioning that both async transfer and readable replicas are ent edition only. – dean Mar 23 '17 at 12:17
  • It depends, Basic Availability Groups are supported by the [SQL Server 2016 Standard Edition](https://msdn.microsoft.com/en-us/library/mt614935.aspx). I was just adding to the solutions @wildermind already found. – Erwin Dockx Mar 23 '17 at 12:43
  • It doesn't depend, there are no readable replicas in std and there is no async transfer in std (where std == BAG). – dean Mar 23 '17 at 12:55
  • Thanks all. I'm going to explore the database replication route a little further. Much appreciated. – wildermind Mar 23 '17 at 13:49