This isn’t yet integrated into SQL SQL Clone, but the feedback we’ve gathered so far indicates that fast, secure, easy data-masking should be integral to SQL Clone. When creating the data image, you can use SQL Data Generator to generate random (but realistic-looking) data on top of the sensitive data. You can mask this data using Redgate’s SQL Data Generator. ![]() A query against a clone behaves identically as a query against ProdData:īut what if your original database has sensitive data? For instance, you may have a CreditCardNumber column in your Customers table which you want to hide from QA and developers. They can be trivially created and destroyed as you wish. They can be read and written without touching the original database or other clones. These test databases are effectively sandboxes. SQL Clone is using Microsoft’s virtual-disk technology to just store the differences between the test database and the data image I created earlier. In the database properties, you can see that SQL Server believes ProdData_Test01 is just a normal 12Gb database. Note that they appear in the Object Explorer alongside the original ProdData database. Here I have created three test databases: ProdData_Test01, ProdData_Test02 and ProdData_Test03. Each takes about 5 seconds to create, and uses 44Mb of hard-disk space, nothing like the 12Gb original! Even if your production database is 500Gb, SQL Clone will still only take 5 seconds and use tens of megabytes for a clone. Now I can click New Clone and create a test database. Once completed, we have a data image snapshot ready to clone. This is an expensive operation time-wise and storage-wise, but it’s a one-off. This snapshot has to make a copy of the existing database, which takes 2 minutes and uses 12Gb. I click ‘New Snapshot’ to create an image of ProdData. I go into SSMS, and select my database in the Object Explorer: I want the ten testers in my team to each have a copy of the production database, but I don’t want to spend ages restoring databases or use loads of disk space. These clone databases take only a few seconds to create and occupy about 50Mb each, but have the same schema and data as the original. With SQL Clone, you can take a ‘data image’ of a database, then rapidly create an unlimited number of clone databases. Today I’d like to talk about a new technology we’re working on at Redgate – with the working-title SQL Clone. There’s also the data sensitivity angle – you probably want to restrict developers and testers from having unlimited access to production backups – a leak like this caused the recent Patreon hack. Also, testing is often destructive, so you’ll need to keep re-restoring for all your testers. Restoring takes lots of time and disk space, particularly if you have to provision many copies. The data is now perfectly realistic, but you still have problems. ![]() If the customer’s address is just a random string, rather than a valid address, then this feature cannot be tested.ģ) Restore a backup of production. For instance, your application may have a feature where it shows a Google Map of a customer’s address. Also, this data will not be realistic or meaningful. ![]() The data quantity is realistic, but generating this database will take a long time and still requires the full disk storage requirements. And it will never be big enough for load-testing.Ģ) Test against automatically-generated data, e.g. ![]() Hand-crafting test data is fiddly, tedious and incredibly slow, with the data rarely resembling real data. But the average production database is between 100Gb and 1TB, and often locked down for data privacy reasons.ġ) Test against a hand-crafted test database. The reality is that DBAs, developers and testers benefit immensely from access to production-like databases for testing application changes, testing schema changes, and testing ad-hoc queries. It seems that the desire to have realistic data will for many trump the desire to keep sensitive data out of development and test environments. Many of these respondents concede that they should be removing sensitive data, but aren’t. In a recent survey run on community site SQL Server Central, 58% of respondents admitted that their test database is an exact copy of their production database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |