question

g003p avatar image
g003p asked

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.
sql-server-2008-r2sql-serverred-gatesource-controlsan
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Normally I jump into try to answer development process questions because I love talking process. But, yours is primarily dependent on SAN snapshots and some cloud-based virtual servers that I'm not understanding, so I'll just vote it up and hope that others get what you're going for here.
1 Like 1 ·
Clarification: Cloud Based Virtual Servers... I was told that the "snapshot" could be used as if it was a server, so in essence I could connect to g00p3p_server and it would look to me as a SQL Server, while it was really just a snapshot pointing to the data in another true SQL Server. This means my environment wouldn't have to host all the data, but I could work isolated from that other environment.
0 Likes 0 ·
Yeah, I'm still back to "I don't know." You need a SAN expert or at least someone who has experience with this very particular bit of magic.
0 Likes 0 ·

1 Answer

· Write an Answer
Tim avatar image
Tim answered
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.
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think 10 TB is our capacity though, not actual usage. But let's plan for future growth!
1 Like 1 ·
Yes, depending on your Netapp device it supports up to 255 snaps of a single volume. The question now comes down to how you configure the environment for the snapshot. Are you taking a snapshot of the data and log luns and just presenting those to an already configured VM where you will have to go into SQL and attach the database, or is it configured in such a way that you take a snap of the entire SQL VM box and that process cleans up the VM so it can come back online? Sounds like you probably just want the SQL storage disks snapped. All of which is completely doable. You just need a good conversation with your storage person and possibly a tech from NetApp to make sure your storage folks configure things properly. Wether or not your storage provider wants to give your developers a button to make their own snapshots is a conversation where I would like to be a fly on the wall. If they give you that access I hope they also give you the rights to destroy all the old snapshots as well. :)
1 Like 1 ·
I am getting just a little out of my depth here but will over what advice I am comfortable with. Not working directly with Netapp before I am not fully aware of its internal workings. Most snapshots are very small as they are just pointers back to the production data. As the production data changes the bits the pointer is pointed to must be maintained for that "point in time" snapshot so over time your snapshot will grow, but modestly. How much data in a 10 TB environment is going to change in a month? Probably GB, not TB's. Add in multiple snapshots and that demand grows a bit but is much less than multiple 10TB copies of the data. I could argue both sides for cloning the entire VM verses just snaping the data and log lun. If you build a true dev server with different credentials then having to repeat that configuration process after every snapshot could be time consuming. Simply detaching the DB prior to the snap and reattaching is pretty straight forward, no need to have to deal with updaing master and msdb for anything related to the Dev environment.
1 Like 1 ·
In my shop I have gotten pretty lucky that we don't refresh data in dev all that often and when we do a simple operational restore is efficient enough. You have very solid questions and goals. I would highly recommend calling your vendor and explaining what you are wanting to do, I am sure they already have this as a canned solution they are providing for others. Let their experience and knowledge work for you.
1 Like 1 ·
Thank you, I've sent the posts as well as questions, and suggested target environment to them and will let them research further from there. Thanks again, this option seemed to be difficult to find documentation on, but perfect for our needs if it works as I envision. Cheers!
1 Like 1 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.