'Creating a "copy on write" table clone in MSSQL 2019
We have a production app where 99% of the actual data resides in a single table, currently about 800GB in size. We create clones (copies) of the whole database for testing and staging purposes and currently exclude this table because otherwise each cloning process would take hours instead of seconds. However, this prevents us from performing certain tests in the staging environment (final tests with production data, after all unit tests were successful, before deployment) due to missing actual data.
Is it possible to create a copy-on-write clone of a database (or single table) quickly, like a hardlink or a shadow copy but which accepts modifications (and then creates copies of the modified rows/blocks/whatever on demand) in MSSQL?
If not, is it possible to create a readonly clone of this production table inside another database (staging) on the same (or a different) DB server so that the app "thinks" this table exists, but cannot modify it, without having to duplicate 800GB of data? This would at least allow us to perform read-only data tests. I read about "CREATE SYNONYM" but this seems not to allow preventing writes.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
