Temporary database write-lock without dropping connections

Question

We have prepared a piece of software that creates a clone of our production database (SQL Server) that contains only the most recent data. We would like to allow developers to instantly create a development environment on their computers.

The first step of the process involves copying table schemas and predefined data. This step takes around 7 seconds. Since this is a production database, users might be committing transactions during these 7 seconds. We would like to prevent the users of doing so, in order to avoid errors that might arise when creating the foreign key constraints at the next step of the cloning process.

We have examined the possibility of making the database temporarily READ_ONLY, but this would drop all connections to the database. This is not desirable because it causes a lot of side effects. We would like to know if there is a query that puts a write-lock on every table of the database, until we decide to manually release the lock. Ideally, we would like the user to just experience a delay on their request, rather that a connection drop.

We have used database backups in the past. The problem is the database size. At the moment it is 17 GB and constantly growing. Even after compression, We have at least 1GB to transfer through the internet. Our internet connection is slow, and offers about 0.5MB/sec upload speed, and this cannot be overcome. Additionally, we would not like to give away all our data.

Answer 1

I would go for backup and restore on the same instance, delete and clean up that restore database and then do a backup of that database. I don't know of a supported way to "lock" the database. One thing you could investigate is to configure snapshot replication and see (through a T-SQL trace) how the snapshot agent does exactly this. Possibly through some undocumented command. - Tibor Karaszi

Have a look at DBCC CLONEDATABASE. It copies statistics but no data, which might make sense with limited bandwidth. - Erik Darling.

---

If you need a consistent snapshot of the data while changes are being made, you could perform the copy inside a transaction running at the SNAPSHOT isolation level.

Answer 2

Since you can't afford to restore the database on the dev machines, what I would be doing is script out all the database objects and create an empty database on the dev machines. You can easly accomplish that by right clicking on the database on SSMS -> Tasks -> Generate script -> Script entire database and all database object.

Be aware that every time that the production database changes, you would have to do this process. If it's a growing app and in continous development, you should be looking for something that automates this process, but that's a whole another topic.

If it's an old in house application that rarely gets updated and you're part of a small team, you can probably bear with the process of updating manually or generating every time a new script.

Then, once the database is created on the dev machines, I would populate it only with the minimun data required for testing, probably with a SSIS package.

And that's the hard part: if your database has foreign keys, you'll have to build your package with some sort of loading order logic.

Otherwise, you could probably dump, let's say, top 1000 rows from every table and check if the application works properly. Be aware that if you want to avoid blocking in production, you should load data in small batches to avoid lock escalation or set the isolation level to Read Commited Snapshot Isolation (so that readers don't block writers).

Answer 3

Searching around I found a way of aqcuiring a shared lock on a specific table by using the template:

BEGIN TRAN SELECT 1 FROM TABLE WITH (TABLOCK, HOLDLOCK) COMMIT TRAN

With a little bit of experimantation, I managed to achieve what I wanted by making the following query:

DECLARE @TableLockListQueryPart VARCHAR(MAX)

SELECT  @TableLockListQueryPart = STUFF(( SELECT  ', ' + table_name + ' WITH (TABLOCK,HOLDLOCK) '
                FROM    ( SELECT 
                                    table_name
                          FROM      INFORMATION_SCHEMA.TABLES
                        ) x
              FOR
                XML PATH('')
              ), 1, 2, '') 

DECLARE @query VARCHAR(MAX)

SET @query='

BEGIN TRAN  
SELECT 1 FROM ' + @TableLockListQueryPart + ' WHERE 1=2 

--[Copy Table Queries go here]

COMMIT TRAN   
'
EXEC(@query)

Everything works fine but I noticed that by encapsulating the queries inside a transaction, the query execution time doubles. I wonder why this happens and if I can somehow eliminate this overhead.