Yesterday I republished an editorial from 2014 for the holiday. The topic was production subsets of data, which has been something that many data professionals have struggled with for years. Many of us have built scripts to delete, change, obfuscate, or alter production restores as a way of providing useful, but manageable development database sets. Or maybe it's just some of us. I'm sure more than a few of us have given up on this task and just restored production databases in entirety to test and development systems.
I changed over my career to become a fan of additively building a known dataset rather than deleting extra data. I advocate adding rows from production (properly masked/obfuscated) and maintaining this set over time as requirements change. However, this isn't without it's own administrative headaches. I think it's easier, but this does require commitment from everyone to keep going over time. It's certainly better than each developer adding their own 10 rows of data to a table for testing.
A year ago, Redgate released SQL Clone, designed to solve some of these issues. Once an image is created, new databases for test and development and be provisioned in seconds. I found this to be an amazing product that really changes how I develop against databases, though it does require me to stop getting caught up trying to undo changes or manage a single database. Instead, I need to ensure I am saving code to version control and then build the habit to drop and rebuild a baseline database.
As we've worked on SQL Clone, I've found that there are lots of companies that offer similar ways of virtualizing your data, giving you access to large, production scale systems in seconds. Data masking, obfuscation, and more are features, with some vendors requiring specific hardware. Others, like Red Gate, have software add-ons (Data Masker). All of these products cost money, which can be an issue for many organizations, but I'm glad that this technology is growing and advancing. With GDPR and other draft legislation, many of us need to take better care of our data and build more secure architectures.
Containers are another interesting way to virtualiza data, though they don't solve the scale issues. If you can work with a smaller data set, and maintain that, then containers might provide a fantastic way for you to learn to build, teardown, and rebuild databases in seconds.
The world of databases hasn't changed a lot in some ways across my career, but in others, I'm amazed. Data virtualization is one of these areas, and if you haven't trialed the technology, maybe you should give it a whirl this year.
The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.
NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps
Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. Download your free trial
The industry standard for comparing and deploying SQL Server database schemas
Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial
If you have a database backup of a Transparent Data Encryption (TDE) enabled database, the database backup will contain encrypted data. Because the database backup contains encrypted data you can’t just restore it to any instance. You can only restore the database backup to an instance that contains the same certificate used to originally encrypt the database. More »
Redgate has committed to hosting a free virtual event in every quarter of 2018, and will be kicking this off on February 28 with a livestream themed around data privacy and protection. The agenda has now been released, so you can see who will be presenting, what they will be presenting, and how you can tune in to watch. More »
In the first part of this series, we looked at where to find out more information about GDPR in the... More »
Question of the Day
Today's Question (by Steve Jones):
I want to set some session key-value pairs using sp_set_session_context. Since this might be used in a number of places in my application, I'm concerned that developers might set too many pairs. What is the maximum amount of data I can store with this feature?
Think you know the answer? Click here, and find out if you are right.
We keep track of your score to give you bragging rights against your peers.
This question is worth
1 point in this category: SESSION_CONTEXT().
We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the
This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage.
Yesterday's Question of the Day
(by Steve Jones):
If I run this code, what is returned?
Answer: an integer
@@OPTIONS returns a binary value that represents a bitmap of your SET options. You can convert this to binary to see a representation fo the options..
This script generates a health status of each db in an AG (and list of DBs not in AG) and uses an IF/THEN block to check if the synchronization_state_desc is healthy. Feel free to insert in the part marked INSERT BACKUP CODE HERE your preferred backup scripts, or trusted third party ones such as Ola Hallengren's. Please note, this is very rough, and does not check for other criteria such as the DB being online, and what kind of backup (Full, Diff, Tlog) is required, but hopefully this will be usefuI.
Ideally, this script and modifications can be wrapped in a proc that is deployed to all servers, both standalone and those in HA's, as a job. In theory, if you want, you can backup the transaction logs on the secondary, and full on primary. This would require a bit of a tweak with the IF/THEN block.
Line chart from table containing static values
- Hello everyone!
I have a SSRS 2016 report, which contains a dataset that produces a basic table as follows:
Department 2014 2015 2016 2017
======== ==== ==== ==== ====
Department A ...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.