Welcome #sqlclone sqlclonefeedback@red-gate.com What problems do - - PowerPoint PPT Presentation

Welcome

#sqlclone

sqlclonefeedback@red-gate.com

What problems do we face when provisioning databases?

Grant Fritchey

Problem: Realistic Data

• Matching production volumes
• Mirroring data distribution of real data
• Repeatable testing
• Accurate testing and evaluation

Problem: Drive Space

• Multiple teams and multiple branches
• Realistic data
• Isolated environments
• 8x the amount of production data

Problem: Shared Environments

• Multiple development teams
• Multiple developers per team
• Multiple branches of code
• Main branch
• Hot fixes
• New Development
• Testing teams for each

Problem: Regulatory Compliance

• Business regulations are increasing
• Health Care
• Banking
• Insurance
• Mens rea
• International compliance (GDPR)
• Cost of data breaches!

Problem: General Data Cleanliness

It’s not just about regulations & jail…

• Email
• Customer info
• Special needs for testing or business

Do more with the same you

• More development teams, more testing teams and

more projects

• Must meet more and growing list of requirements
• Same DBA team

sqlclonefeedback@red-gate.com

Alexa… clone my database

How SQL Clone helps you solve these provisioning problems

Steve Jones and Richard Macaskill

What is SQL Clone

“SQL Clone is a database provisioning tool that lets you create ‘full-sized’ copies

• f SQL Server databases and backups in

in secon

• nds

ds, using around 40MB MB of dis isk k space ce per clone.”

How does SQL Clone work?

• 1. Create a data image of a live SQL Server

database or backup

• 2. Create clones from your data image on

any machine that can access the file

Demo

What can SQL Clone do for you?

 Realistic data  More drive space  Isolated environments  Do more with the same you!

 Fast small restores  Automate jobs  Self-service of clones

sqlclonefeedback@red-gate.com

Using PowerShell with SQL Clone to automate provisioning jobs

Richard Macaskill and Steve Jones

Tuesday Wednesday Thursday Friday Monday Tuesday

AW_20170321 AW_20170322

AW_20170323

AW_20170324 AW_20170327 AW_20170328

Daily Imag ages es: Day of the Week:

CloneA CloneB CloneC

Example #1: Create an image for the latest backup, every night

Demo

Example #2: Create a clone for this feature branch on every workstation

Image: Forex_20170327 (53GB) Clone: Forex-Hotfix-18554 (48MB) Clone: Forex-Hotfix-18554 (48MB) Clone: Forex-Hotfix-18554 (48MB)

CloneA CloneB CloneC

Example #2: Create a clone for this feature branch on every workstation

SQL Clone Server http:\\cloneserver:14145 Dev-wks01 Dev-wks02 Dev-wks03 File Share \\filestore1\clone\

Demo

Learn More

• https://documentation.red-gate.com/
• Cmdlet reference
• Worked examples
• https://forums.red-gate.com/

What can SQL Clone do for you?

 Give your team their own local production-like database in seconds using a fraction of the disk space.  Allow developers to work in isolated environments without overwriting each others’ changes.  Test against realistic data and server settings to identify issues before they reach production.  Automate provisioning for testing on realistic data and scenarios.  Create clones during application debug to test code against realistic data.  Reduce time to analysis and fix issues in production faster.

sqlclonefeedback@red-gate.com

Competition

Why are clones so small?

The technology behind SQL Clone

Chris Hurley

Why are clones so small?

200GB 0GB 100MB 0MB

Why are clones so small?

200GB 0GB 100MB 0MB

Data Virtualization

Data Virtualization

200GB 0GB 200GB 0GB 200GB 0GB 600GB 0GB

.mdf df and .ldf

Production QA Development

Data Virtualization

200GB 0GB 200GB 0GB 200GB 0GB 600GB 0GB

Production QA Development

Data Virtualization

210GB 0GB 200GB 0GB 10GB GB 0GB

Image QA Development

Accessing data on clones

SELECT ([Name]) FROM [Customers]

Image Dev

Accessing data on clones

INSERT INTO [Customers] ([Name]) VALUES(N'Dave')

Image Dev

Accessing data on clones

SELECT ([Name]) FROM [Customers]

Image Dev

Built on Microsoft technologies

• Windows Virtual Disk Service
• Windows Shadow Copy Service
• SQL Server backup, restore, and attach

Making an image

Windows Virtua ual l Hard Disk Servi vice ce Image ge VHD

Making an image from a database

MDF LDF

Windows Shadow Copy Servi vice ce

MDF LDF MDF LDF

Making an image from a database

MDF LDF

Image ge VHD

MDF LDF

Making an image from a backup

Image ge VHD

MDF LDF

SQL Serve ver instanc ance Backup up

Making a clone

Image ge VHD

MDF LDF

\\fileser eserver ver\sha share re

Making a clone

Image ge VHD

MDF LDF

\\fileser eserver ver\sha share re Windows Virtua ual l Hard Disk Servi vice ce Clone e VHD

Making a clone

Image ge VHD

MDF LDF

\\fileser eserver ver\sha share re Windows Virtua ual l Hard Disk Servi vice ce Clone e VHD

Making more clones

Image ge VHD

MDF LDF

\\fileser eserver ver\sha share re Clone e VHD Clone e VHD

Things to remember

• Unchanged data is always read from the image
• Changing the database will make changes to the clone’s local

data files

• The image is never modified – every clone is isolated and is

safe to delete and reset

Summary

• Data virtualization is used to make clones so small
• SQL Server sees the clone database as being on a local disk, but only

changes are stored locally

• This is powered by the Windows Virtual Hard Disk service and either the

Shadow Copy Service or SQL Server backup restore sqlclonefeedback@red-gate.com