Sql-server – Using SQL Snapshot Replication to refresh TEST databases from PRODUCTION. Why not

replicationsnapshotsql server

What are the drawbacks and downsides?

We want to schedule automatic nightly refreshes of 20 databases, approximately 1GB each, from the PRODUCTION server to the TEST server. We're setting up SQL Server snapshot replication to publish snapshot updates from the PRODUCTION databases to TEST. Are there gotchas we should know about?

Best Answer

Your scenario is interesting :

  • Small databases approx 1GB in size.
  • Number of databases = 20
  • Refresh of the data occurs on nightly basis, so the data can be stale by one day.

Initial thought reveals that snapshot replication should be OK as you thought, but I would highly recommend to go for backup/restore method - safe, reliable and less overhead of maintaining.

Note that I am not saying that snapshot replication is a bad choice, but why not use a proven and reliable method - backup restore ? You can/should automate it (backup/restore) using either Powershell or T-SQL or SSIS.

Drawbacks of Snapshot replication :

  • You cannot drop/create tables, sp's, functions, that are marked for replication. Also, you cannot directly alter a table, you have to use system stored procedures sp_repldropcolumn and sp_repladdcolumn.
  • A standard snapshot will lock the tables during the BCP step to make sure you get a valid copy.
  • Any schema changes requires a new snapshot to be generated which is quiet resource intensive on the publisher side.
  • Additional overhead on the server occurs if you have a local distributor.
  • End up monitoring multiple services running on the server.
  • Dependency on SQL Server Agent.
  • Windows NT permissions on the snapshot folder.
  • Network bandwidth.
Related Question