- Community Home
- >
- Storage
- >
- HPE Nimble Storage
- >
- Application Integration
- >
- Re: MSsql zero copy clone databases
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-09-2014 02:58 PM
тАО12-09-2014 02:58 PM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-10-2014 02:34 PM
тАО12-10-2014 02:34 PM
Re: MSsql zero copy clone databases
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-10-2014 03:09 PM
тАО12-10-2014 03:09 PM
Re: MSsql zero copy clone databases
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-10-2014 04:30 PM
тАО12-10-2014 04:30 PM
Re: MSsql zero copy clone databases
What about the version/edition of SQL? Is there any data modification required on the reporting copy during its existence or purely read-only?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-10-2014 04:36 PM
тАО12-10-2014 04:36 PM
Re: MSsql zero copy clone databases
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-10-2014 05:14 PM
тАО12-10-2014 05:14 PM
Re: MSsql zero copy clone databases
Enterprise or Standard edition? If its enterprise edition it opens up some more options.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-10-2014 05:18 PM
тАО12-10-2014 05:18 PM
Re: MSsql zero copy clone databases
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-10-2014 05:34 PM
тАО12-10-2014 05:34 PM
Re: MSsql zero copy clone databases
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-10-2014 06:00 PM
тАО12-10-2014 06:00 PM
Re: MSsql zero copy clone databases
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-10-2014 07:04 PM
тАО12-10-2014 07:04 PM
Re: MSsql zero copy clone databases
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-10-2014 08:38 PM
тАО12-10-2014 08:38 PM
Re: MSsql zero copy clone databases
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-11-2014 07:13 AM
тАО12-11-2014 07:13 AM
Re: MSsql zero copy clone databases
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-11-2014 08:08 AM
тАО12-11-2014 08:08 AM
Re: MSsql zero copy clone databases
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-11-2014 09:15 AM
тАО12-11-2014 09:15 AM
Re: MSsql zero copy clone databases
Here's a good video on doing it with clones - Nimble Storage Demo: SQL Server Recovery - YouTube
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-11-2014 09:42 AM
тАО12-11-2014 09:42 AM
Re: MSsql zero copy clone databases
Kevin, that doesn't look like it would really work for his needs. Also it looks to be decent amount of downtime/manual work unless it's something that can be automated to constantly refresh those LUNS on the other server constantly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-11-2014 01:35 PM
тАО12-11-2014 01:35 PM
SolutionThanks 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-16-2015 06:17 AM
тАО07-16-2015 06:17 AM
Re: MSsql zero copy clone databases
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-16-2015 06:21 AM
тАО07-16-2015 06:21 AM
Re: MSsql zero copy clone databases
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-20-2015 12:47 PM
тАО07-20-2015 12:47 PM