welcome
play

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

Dec 09, 2022 •468 likes •967 views

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

  2. Welcome #sqlclone sqlclonefeedback@red-gate.com

  4. What problems do we face when provisioning databases? Grant Fritchey

  5. Problem: Realistic Data • Matching production volumes • Mirroring data distribution of real data • Repeatable testing • Accurate testing and evaluation

  6. Problem: Drive Space • Multiple teams and multiple branches • Realistic data • Isolated environments • 8x the amount of production data

  7. Problem: Shared Environments Multiple development teams • Multiple developers per team • Multiple branches of code • Main branch • Hot fixes • New Development • Testing teams for each •

  8. Problem: Regulatory Compliance Business regulations are increasing • Health Care • Banking • Insurance • Mens rea • International compliance (GDPR) • Cost of data breaches! •

  9. Problem: General Data Cleanliness It’s not just about regulations & jail… • Email • Customer info • Special needs for testing or business

  10. 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

  11. Alexa… clone my database

  13. How SQL Clone helps you solve these provisioning problems Steve Jones and Richard Macaskill

  14. What is SQL Clone “SQL Clone is a database provisioning tool that lets you create ‘full - sized’ copies of SQL Server databases and backups in in secon onds ds, using around 40MB MB of dis isk k space ce per clone.”

  15. 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

  16. Demo

  17. What can SQL Clone do for you?  Realistic data  More drive space  Isolated environments sqlclonefeedback@red-gate.com  Do more with the same you!  Fast small restores  Automate jobs  Self-service of clones

  19. Using PowerShell with SQL Clone to automate provisioning jobs Richard Macaskill and Steve Jones

  20. Example #1: Create an image for the latest backup, every night Day of the Week: Tuesday Wednesday Thursday Friday Monday Tuesday Daily Imag ages es: AW_20170321 AW_20170322 AW_20170324 AW_20170327 AW_20170328 AW_20170323 CloneA CloneB CloneC

  21. Demo

  22. Example #2: Create a clone for this feature branch on every workstation Image: Forex_20170327 (53GB) Clone: Clone: Clone: CloneA Forex-Hotfix-18554 Forex-Hotfix-18554 Forex-Hotfix-18554 CloneB (48MB) (48MB) (48MB) CloneC

  23. Example #2: Create a clone for this feature branch on every workstation SQL Clone Server http:\\cloneserver:14145 File Share \\filestore1\clone\ Dev-wks01 Dev-wks02 Dev-wks03

  24. Demo

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

  27. 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

  28. Competition

  30. Why are clones so small? The technology behind SQL Clone Chris Hurley

  31. Why are clones so small? 200GB 0GB 100MB 0MB

  32. Why are clones so small? 200GB 0GB 100MB 0MB Data Virtualization

  33. Data Virtualization .mdf df and .ldf 200GB 0GB Production 200GB 0GB 600GB 0GB QA 200GB 0GB Development

  34. Data Virtualization 200GB 0GB Production 200GB 0GB 600GB 0GB QA 200GB 0GB Development

  35. Data Virtualization 200GB 0GB Image 10GB GB 210GB 0GB QA 0GB Development

  36. Accessing data on clones Image Dev SELECT ([Name]) FROM [Customers]

  37. Accessing data on clones Image Dev INSERT INTO [Customers] ([Name]) VALUES(N'Dave')

  38. Accessing data on clones Image Dev SELECT ([Name]) FROM [Customers]

  39. Built on Microsoft technologies • Windows Virtual Disk Service • Windows Shadow Copy Service • SQL Server backup, restore, and attach

  40. Making an image Windows Image ge VHD Virtua ual l Hard Disk Servi vice ce

  41. Making an image from a database MDF LDF MDF LDF MDF LDF Windows Shadow Copy Servi vice ce

  42. Making an image from a database MDF LDF MDF LDF Image ge VHD

  43. Making an image from a backup MDF LDF Backup up Image ge VHD SQL Serve ver instanc ance

  44. Making a clone MDF LDF Image ge VHD \\fileser eserver ver\sha share re

  45. Making a clone MDF LDF Image ge VHD Clone e VHD Windows \\fileser eserver ver\sha share re Virtua ual l Hard Disk Servi vice ce

  46. Making a clone MDF LDF Image ge VHD Clone e VHD Windows \\fileser eserver ver\sha share re Virtua ual l Hard Disk Servi vice ce

  47. Making more clones Clone e VHD MDF LDF Image ge VHD \\fileser eserver ver\sha share re Clone e VHD

  48. 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

  49. 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

Recommend

More recommend