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.
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.