18 Replies Latest reply: Jul 20, 2015 12:47 PM by Ian Noble RSS

    MSsql zero copy clone databases

    David Andrew Newbie

      Hi All,

      I'm a bit new to Nimble and VMWare, but really need the database clones for our reporting services.

      What we are doing having the clone once a day and at worst a 15 minute window of downtime seems about right.

       

      I've read a bit around the clones and remounting the disks, but there doesn't seem to be a end to end solution at the moment for remounting them in SQL.

       

      What I would like to know is

      Has anyone done all of this?

      Any suggestions on best practice?

      Any scripts that people can share, both SQL and VM/Storage.

       

      Thanks

      Here is what I've read so far on automating the disk clone

      Using PowerShell to provision Nimble Zero Copy Clone to ESXi server

       

      details

      windows 2012r2

      MSSQL 2014(stanadard/enterprise etc TBA)

      on VMWare 5.5

       

      Daily Clones

        • Re: MSsql zero copy clone databases
          David Tan Adventurer

          Hi David,

           

          Does the clone get presented to the same sql server or a different one? How frequently does it get refreshed? What version and edition of sql are you running?

            • Re: MSsql zero copy clone databases
              David Andrew Newbie

              It is a different SQL server - it is dedicated to data mining, and once a day will be sufficient, however more is always better.

              The idea is the SQL server has it's own OS, and local database stores for the report templates and data cubes, based on the data which is a clone of the production systems.

                • Re: MSsql zero copy clone databases
                  David Tan Adventurer

                  What about the version/edition of SQL? Is there any data modification required on the reporting copy during its existence or purely read-only?

                    • Re: MSsql zero copy clone databases
                      David Andrew Newbie

                      SQL 2014 in the build process atm, I woud not envisage changing the data on the clone, instead the server would have a local Databases set which includes view linking to the cloned data.

                      Unless there is a better way, I really have not used this technology before so I'm happy to take suggestions, but it will mostly be used by our performance reporting group to create monthly reports for the hierarchy who have quotas and targets.

                       

                      So the reason I've stayed Vague is that I am willing to follow any reasonable Course of action

                        • Re: MSsql zero copy clone databases
                          David Tan Adventurer

                          Enterprise or Standard edition? If its enterprise edition it opens up some more options.

                            • Re: MSsql zero copy clone databases
                              David Andrew Newbie

                              I'm leaning to enterprise, so if this needs it then that will clinch the deal.

                              we will still go from 2 ent to one as part of this

                                • Re: MSsql zero copy clone databases
                                  David Tan Adventurer

                                  So one thing you can do in SQL Enterprise edition is to mirror the production database using database mirroring (probably async mode to reduce any impact to production) to the Reporting SQL server and create read-only point-in-time database snapshots which are point-in-time copies of the production database. Once mirroring is setup all that is needed is a TSQL SQL Agent job to re-create the snapshot on whatever schedule you need.

                                   

                                  Similarly - you could also use Always On to mirror the database to the Reporting server as a read-only replica which is always available. The difference this this option is the data in the replica is pretty much realtime, but you can also create point-in-time database snapshots from the replica.

                                   

                                  I would say the first is easier to setup as Always On will need clustering components and availability groups configured.

                                   

                                  Do either of these sound worth pursuing?

                                    • Re: MSsql zero copy clone databases
                                      David Andrew Newbie

                                      They are both options, and what I was working towards, until Nimble.

                                      One of the attractions of Nimble was the SQL zero sized clones, using the Nimble storage, which would be a zero sized copy.

                                      It's not that large 1/2 TB of data but the idea of not having any storage for it is very appealing.

                                      I'm also led to believe that it would then keep more of the data in the HOT pool so even quicker access.


                                      Like you I am however leaning towards using SQL tools and suffering the duplication

                                        • Re: MSsql zero copy clone databases
                                          David Tan Adventurer

                                          Nimble clones are definitely still an option and given the size of the database is still worth looking at. Each option has its pros and cons.

                                           

                                          The learning curve using and managing the SQL features might be another good reason to opt for Nimble Clones. Because I'm a DBA I tend to look at SQL options first

                                          The cost of Enterprise edition licenses might be another reason to opt for using Nimble Clones, although there are still some usable options with Standard Edition of SQL.

                                           

                                          The clones will be based off the production volumes so provided that data is already in cache it will be "HOT" and accessed faster. I'm not using any datastores on Nimble - its all iSCSI volumes directly attached - so I can't provide much advice on the VMWare layer. If you get to the point of having the cloned disks accessible to the guest it should just be a matter of running a kill/detach/attach SQL script to get the new database version accessible on the Reporting SQL Server.

                                            • Re: MSsql zero copy clone databases
                                              David Andrew Newbie

                                              Thanks a lot David.

                                              Great things to think about, and given I am the single person dependancy with the Nimble SQL experience I'm leaning towards SQL tools again.  At least then the ones who see the problem can fix it rather than a server admin trying to talk to app developers.

                                              BTW I am a server admin primarily resposible for SQL and hte closet we have to a DBA, however I really manipulate data.

                                               

                                              Thanks again I'm going to go away and try some things

                                            • Re: MSsql zero copy clone databases
                                              Paul Severini Newbie

                                              David,

                                               

                                              Did you eventually decide on a path for your project? Do you have any additional insight with the Nimble zero copy clone feature?

                                               

                                              Thanks.

                                          • Re: MSsql zero copy clone databases
                                            Jeff Taylor Newbie

                                            What I would suggest is if you have enterprise edition, create an availability group of your database(s) from your primary production database server to your reporting database server and then create a read-only instance on your reporting server using asynchronous connection between them. This way you report server can 'read' the production data in near real time and you don't have to move it. The data would be trickle copied between servers and be available. You could even switch to synchronous and the data would be there in real-time.

                                              • Re: MSsql zero copy clone databases
                                                JT Moree Wayfarer

                                                as someone else suggested: Using a slave replication server for the reporting services database works very well.  We have issues when heavy loads hit the primary database so I setup a second mysql server vm which replicates the data from primary in real time.  We only every run reporting services against that database.  Using clones on the nimble is going to be a less efficient and more error prone method to replicate data.

                                  • Re: MSsql zero copy clone databases
                                    Scout

                                    Here's a good video on doing it with clones - Nimble Storage Demo: SQL Server Recovery - YouTube

                                    • Re: MSsql zero copy clone databases
                                      David Andrew Newbie

                                      Thanks everyone

                                      This is really great.

                                      The Gist I'm getting is while it would work, it would not be of great benefit, be very complex, and is not really a mature way of implementing this.

                                       

                                      As the Single person dependency if we implement this I would prefer to use a tech that others can step in and take over while I'm on leave.

                                      Such as SQL replications.

                                      There is nothing worse than have the body shots interrupted by a poor replicating database.

                                      • Re: MSsql zero copy clone databases
                                        Paul Severini Newbie

                                        Is there anyone on this thread that has actual first hand experience using the zero copy clone feature with SQL Server Standard under vSphere? I see a ton of Q&A, but I'm looking for someone who can help identify the pros and cons of this type of solution using Nimble. We're currently looking to implement a read-only replica of our current production ERP database for a data warehousing solution. We would like to be able to refresh the data warehouse with production data on a minimum frequency of 4 times a day. Is anyone using a solution that falls outside the typical options that are already offered by Microsoft SQL Server (i.e. log shipping, mirroring, replication, always-on)? We would like to avoid the overhead and management of the application layer replication solutions if possible.

                                         

                                        Thanks for your help.