Using SQL Snapshot Replication to refresh TEST databases from PRODUCTION. Why not?

Question

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?

Answer 1

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.

Answer 2

Advantages:

• can be used where production and test are different SQL Server versions
• process can be managed within SSMS and SQL Agent alone
• can be useful where some tables in test don't need to be refreshed. You can be selective with what to publish
• Your test database can have different logins/users to your production copy. With a backup/restore method you can setup logins in test to have the same SID's as production and maintain seperate passwords, but different users need to be re-added after the restore.

Disadvantages:

• Will need to setup a distribution database in production. Ideally on another server to minimise performance impact of generating snapshots. Generating snapshots is an intense process, if you have a server that needs to be available/online 24x7 this is not a good thing, even if the distribution db is not setup on the production db server.
• New database objects will need to be added to the publisher for them to replicate. Adding/dropping columns should work. But there's maintenance overhead here with having to adjust the publication in production if you want to have an exact copy.

My advice would be to go for the backup/restore method. You'll probably find your pulling about the same data over the network for a snapshot refresh vs. a backup. Yes you can compress snapshot files, but you can also compress backups. If your not adding/removing users in test too often & the same application logins are used in test then script out the logins from production to test to maintain the same SID's, this way you avoid the orphaned users problem. If there are additional users in test, then you can add these after you do a restore.

Answer 3

I think backup and restore would be the fastest way to get a new copy of the database from PRODUCTION to TEST. This can be automated easily.

I don't think replication is the best feature to use here in my opinion. Unless you only plan on replicating a subset of the data. In that case, Snapshot Replication may be a good fit.