April 5, 2017 at 9:45 am
To restore Production to the Staging server, I would want to consider the time required to do this plus the disk space needed. Also, getting all these moving parts working adds some complexity to the ETL process. However, doing a restore of Production DBs on the Staging server does confirm you have working backups. But in this case you need to decide how you will deal with the inevitable random backup failure, because it will happen.
This should be compared to the requirements for taking a SQL Snapshot of the Production databases, and reading from the read-only snapshot on the Production server. In taking a Snapshot, I would want to review advice on the web about impact on performance (low but roughly proportional to update rate), and amount of disk space likely to be needed if the snapshot lasts for (say) 12 or 24 hours. Snapshots are available on Standard Edition with SQL2016 SP1, anything lower than this level means EE only.
For most SME businesses, Snapshot is probably all you need. For a larger business that can justify AGs and read-only secondaries, then using a dedicated async secondary on the Staging server might be a better approach. You should consider risk as well as cost in recommending your solution.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply