Using SAN Disk Snapshots to provide cloud based developer sandbox with full functionality?
I'm researching options to implement source control and continual integration methodology into our current environment. I'm very new to this, so my research has led me to this possible idea. Goals: 1. Full replica of our production server to work on (much of our work is historical based and we are hybrids between DBA/SQL Dev's. In our enviroment, moving towards "test data" or partial data is just not really going to be feasible. No compliance issues right now would be affected) 2. Avoid shared dev enviroment if possible to truly allow dev's to experiment and test affect of changes in entire enviroment. 3. Avoid local sandox. Our databases are interdependent, we have linked servers. My days of work on setting up a local empty schema based sandbox were not successful. 4. Make the setup as easy as possible for the dev team to avoid long setup's and issues for them, or when adding new members Current Idea (correct me or expand if you feel it won't work please!) FYI- All SQL Servers are running in virtual machines now. Snapshot in all references is referring to SAN SNAPSHOTS, not SQL backup snapshots 1. LIVE - Live production enviroment 2. Copy of Live Production enviroment made to seperate server/machine. This would allow snapshots being used to point to a copy of production data, instead of being on the same server and pointing to actual prod data. *is this step required with virtual machines? 3. BUILD/STAGING - reset each night to be a snapshot of the live production 4. DEV - Each developer would have their own sandbox, cloud based. It would be a unique instance of the snapshot taken of the production enviroment. This would be around 25 virtual machines being used by dev's right now, each of these using a snapshot. They'd have full access to all data, but we wouldn't be duplicating terabytes of data. 5. Their box wouldn't refresh each day. *possible to allow them to trigger the san snapshot to move to the latests, or build script/program to let them trigger this on their own? 6. Use Red Gate SQL Source Control plugin to centralize dev work in central TFS repository. Only commit changes you want automatically going to BUILD/STAGING, which would be triggered by team city or just Red Gate SQL Compare running every few minutes to push committed changes through to STAGING. 7. Dev's could stay in sync with changes by using the "GET LATEST" tab in Red Gate SQL Source Control. The only thing a san snapshot refresh would be needed for would be to get latest data from production, but this wouldn't really be needed daily. This way they don't lose their work. Potential issues I'm confused on: - Can dev's/DBA trigger their own san snapshot forward so they could pick the lastest version if they had no outstanding uncommitted work? - If they did a refresh to newer snapshot, is there a way without committing to repository that their progressive changes could easily be tracked without have source control on dev machine and on build machine? Any way to separate "SHELVED" vs "READY" items? Red Gate SQL Source control doesn't offer that yet. - BIG ISSUE: Does the snapshot grow exponentially as work is done... Can all dev's not refresh for 1-2 weeks and the snapshots not grow tremendously? I've read that SQL Snapshot's grow tremendously, and I'm not sure how Netapp handles. Some articles on VM snapshots claimed not to even leave live longer than 24 hours. I'm really not sure how this works, especially when we are testing ETL type processes, or historical table updates that can result in large amounts of records being changed.
What you are failing to mention is how much data are you trying to make a snapshot of, how often is that data changing, does your SAN allow for multiple copies of the snapshot. Is your production SAN local that you are trying build a dev in the cloud for or is production already in the cloud? I have know shops that would use SAN snapshops to present storage to another set of devices for validation, testing, qa, etc. I have know organizations to use a clone of a VM in another isolated environment to simulate a true copy for validation. "Gets tricky if you clone a production box and it comes up on the network with same name, SID, etc" Right now I don't want to dig to much deeper until knowing if your production data is local or in the cloud. That changes the solution or guidance majorly.