MS SQL 2008 R2 - How to use a production database through dev/test environments or how to copy a SQL schema and all its data to another

Question

We currently have 3 Windows Server 2008 R2 VM identically build for Sage x3 v6.5, one for production/formation/budget database, a second one for QA (qualification) and a third one for dev & tests. This makes a total of 6 databases, Microsoft SQL Server 2008 R2 with standard licences. We would like to improve our process in order to always have fresh production data in dev, test and qualification (and potentially review, at the same time, the budget and training databases locations, which are also copies of the production scheme).

As I'm not a database specialist (currently an infrastructure engineer apprentice), let alone Microsoft SQL Server... I come to ask you for advice and best practices. Our infrastructure being rather modern, robust and spacious, we don't have any storage capacity or performance problems but, in the interest of optimization, I would like to avoid, as much as possible, having to duplicate 6 times the production VM (about 800 Go) or the production database as it is more or less the case actually...

So is there a way to use the production database from another environment but to redirect the writings of those environments to another location, specific to each environment (another shema or somewhere else...) to keep production database clean as it should be, exactly as a VM snapshot does physically (redirect on write) ? If not, I think I will have to copy the database 6 times (don't hesitate to tell me if I'm wrong)... How could I proceed in SQL 2008 R2 because it's hard to find how to copy tables (and their data) from a specific schema to another database schema on another server and to automate it. And, afterwards, is it possible to do differential copy (each night for example) to reduce the process duration.

Thanks a lot for all your answers :)

Sincerely yours, Tigerblue77

Answer 1

Consider also replication. You can replicate only your tables so that you don't override any stored procedures or functions that the developers are working on. You can set up a publisher on production and 2 subscribers: one on TST and one on DEV.

It's almost real time and very suitable for scenarios like yours. I always try to go for replication instead of anything else because in a stable environment you set it up and forget it's there while saving network bandwidth and unnecessary data copy. (with backup and restore you basically [copy prod], [delete tst/dev], [paste prod in dev/tst] - 90% the same data everyday)

Downsides:

• it's a bit of trouble until you learn to work with it
• it will take some network bandwidth during the day to send your transactions from the publisher (prod in your case) to the subscriber (or subscribers in your case: dev and tst).
• it will eat up a lot of network bandwidth, SQL Server memory and disk I/O when you initially set it up and this will repeat if and every time you need to reinitialize the replication. You can also set up replication from backup and that goes faster when you initially set it up and if you ever reinitialize.

More documentation about replication here.

Answer 2

Consider tools like:

A. Red Gate SQL Clone, which is designed for scenarios like this.
It will use following components:
1. You will restore single backup of the production database within the tool.
2. Tool will create virtual files for databases in all your environments and create a differential logs for each of them.
3. You will create separate database for each environment using virtual files. At the beginning storage will be used only for single backup. As you modify copies, each environment will keep differential log.

B. Use features of your storage array or VM environment.
You can create single volume on the SAN array and then create multiple copies with differential logs. About details you need to talk with your SAN vendor.

Answer 3

If data from the previous day or from the early morning would do, then a BACKUP/RESTORE cycle is your best bet. Basically, you back up the Production DBs, then restore those to the locations that you need the updated data in.

Note that doing this to certain environments every day will break your processes. Restoring daily to Dev, for example, will overwrite any new tables / alterations / performance tweaks that you may be making in the DB (NOTE: I do not know if SAGE x3 allows user changes to DB structures, or adding additional tables, views, or indexes for performance / reporting purposes.)

Any data set up for testing would also be overwritten by the restored databases.

Finally, you may have to re-link or re-add some users to the databases after restoring, depending on your configuration on the Dev / QA systems. There are scripts available on the net for this purpose.

While I agree that updating your SQL Server to a later, supported version is important, this may depend on what version of Sage x3 you are running - for example, v6.3 does not seem to be validated to anything later than SQL Server 2012 - and your ability to upgrade may be constrained by actual budgetary considerations. Since this is true, you should fully specify your environment (SAGE x3 Version, Windows version, etc.) and specify if you are in a position to upgrade SAGE, SQL Server, both, or neither.