September 18, 2017 at 5:17 am
Morning All,
I am using DBCC CloneDatabase to provide developers with a shell of a database to dev and testing purposes.
Our usual dev and test database have grown silly with lots of test scenario from previous iterations that I thought its time to start a fresh.
So my question is there anything wrong with using CloneDatabase for this purpose?
Typically I:
CloneDatabase()
Import Static data from scripts (this is the minimal data that is needed to make the application work)
Update Statistics (I've read old statistics from production are carried over)
Then I backup/restore to the Dev or Test instance. This lets me rename the data files too.
Is there anything wrong with this? Anything I am overlooking?
Cheers
Alex
September 19, 2017 at 8:31 am
There is nothing wrong with this approach. More for safety that for performance, I suggest backing up the clonedatabase and copying it off the production server, before flipping it back to read-write, running the DML, and updating statistics.
By updating statistics you will be blowing away what I consider to be the main purpose of a clone, which is to present realistic costs (i.e. the clone's statistics) of a production database to developers, but that appears to be alright, given your need. You will also loose permissions and any personally identifiable information within the cloned statistics.
Ideally, I also believe cloning for your stated need should be done just once, with the goal being to establish a single, 'inalienable', source of truth in development. If the developers cannot tell you what they developed (which appears to be why you are needing to clone), you have a bigger problem, much like mine :). I currently have 500+ databases with the same problem (differing schema). I am fairly certain you don't want to end up in the same boat I inherited:). To get to the 'inalienable' part, DENY ALTER is my proposed solution, and that is a tough pill for my developers to swallow :). Luckily for me, they can (and now often do) create local SQL Servers and databases, whenever they want to work on schema.
September 19, 2017 at 8:47 am
Hiya, thanks for your reply.
Yes, this is indeed meant to be a one-off clone to set the new standard, a blank slate if you will. In this case I am not too bothered about statistics. The developers have had free reign (up until recently) where some changes didn't even make it to development. They either went straight onto a test server or worse fixed on the fly in production. Such rights have been revoked to production and test. Changes to test will be done as if it was a change to production.
All of the above were compounded by having 3rd party external developers doing 'stuff' changing bits and as a result the development database is over 500GB in size.
As the cavaliers have now left and their project binned, we are starting a fresh with a known good copy of the DB. Which sadly happens to be production only. We could mess about using SqlCompare as we have that onsite too but seeing as the DB is unnecessarily too big for development anyway and no performance work goes on there, I thought it would be quicker and cleaner to just start afresh. Noah-Stylee.
Cheers!
September 19, 2017 at 9:10 am
You say import static data, does this include transactional data for tables that developers need? Or is it just lookup data?
Otherwise, this is fine, though I'd set this as a process and refresh my environment on a periodic, not daily basis. Once I'd built a version of the db that developers can use, I'd make a backup and put this in a known location so developers can quickly spin up a new copy, and abandon their old one if needed. In fact, I'd get them used to this idea. Don't get too attached to a db and keep copies of your code in a VCS outside of here.
September 19, 2017 at 9:20 am
Just look up data.
As this database has had a few years of abuse, I am going to get a known good version, script out all the objects and make that the new standard in source control. As looking at some random objects now in source control and comparing to what's in the database they are massively out of date, so source control is of no use as it is.
On top of this sometimes we have to branch the DB for changes who development streams over lap but their release dates are massively different and having to create multiples of 500GB databases is not useful to us. We now have a blocker where there is not enough space for another copy, our dev server don't have 2TB of free space to fix 1 extra bug. It's all so silly.
September 19, 2017 at 9:49 am
Not to press, but this is why we built SQL Clone at Redgate. It allows developers to get a full copy of data with minimal space/time issues.
http://www.red-gate.com/products/dba/sql-clone/
Disclosure: I work for Redgate.
It's easy to get frustrated and get caught up in a quick solution. One thing I'd suggest is that you don't just clone db and add static data. Pick a few entities that represent the work and load those as well. Get a consistent set of data that developers can use. Starting by adding data is often easier than subtracting from the 500GB db. I might start with 3-4 entities and script a load from prod if you're doing the clone there.
You need a consistent set of objects, branching aside, so I'm not sure why having developers keep code in a VCS is an issue. Branching allows them to work separately. If your VCS is out of data, I agree that getting a known standard is a good idea, then let devs branch. I'd include a folder in the VCS for loading the sample data, and let devs modify this as they make changes in their branch, and merge those changes as well.
September 19, 2017 at 10:37 am
Absolutely. Agreed.
I am only providing a shell of a database so that they can decide what data they want to add in that represents a reasonable spread of data for testing.
The previous approach was to truncate the easy table (user audit tables) and then anonymise the rest. Leaving a massive database with the same blob data repeated 30 or so million times. Plus it took the best part of 5 hours to complete on the dev hardware.
Unfortunately, there are no more purchases this side of April. Though I suppose I can't moan too much just got some shiny new Compellent SAN and some StorSimples.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply