Application Integration
1748265 Members
4063 Online
108760 Solutions
New Discussion юеВ

Re: MSsql zero copy clone databases

 
SOLVED
Go to solution
Fos
Occasional Advisor

MSsql zero copy clone databases

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

18 REPLIES 18
david_tan2
Valued Contributor

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?

Fos
Occasional Advisor

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.

david_tan2
Valued Contributor

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?

Fos
Occasional Advisor

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

david_tan2
Valued Contributor

Re: MSsql zero copy clone databases

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

Fos
Occasional Advisor

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

david_tan2
Valued Contributor

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?

Fos
Occasional Advisor

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

david_tan2
Valued Contributor

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.