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.