SQL Prompt is amazing and it’s my favorite tool. It’s actually what many people want first from Redgate because it makes coding much easier. One of the most used features is the reformat feature (CTRL+K,Y), since formatting really matters to developers.
One of the neat features of SQL Prompt is that you can format just a portionof your script. That means if you’re working on a large script and want to reformat just a part of it, you can.
Here’s how:
As much as I liked the ability to quickly and easily build development and test databases with SQL Provision, I thought the dashboard of cloned databases was hideous. It left a lot to be desired, and frankly, the dark theme is annoying to me. Here’s my old dashboard.
I wasn’t alone, as various customers were asking about enhancements and additions. The team has been listening and I talked with them a few months ago during a meeting about possible ideas and designs. I saw an early mock up, and was hoping it would be released soon.
After coming back from vacation, I saw an update was available, so I applied it. After a few minutes, I saw this:
Once this was done, the page refreshed, and I saw the dashboard. I know, not much has changed, but look at the upper right part of the screen. There’s a blue box that says “Preview new dashboard”.
Once you click this, you get a new dashboard, which thankfully doesn’t use the dark theme. What’s nice is that I also get some information at the top of what my activity is. I can see the total clones and images, and the machines that are working or having issues.
I also have options for resorting the clones and images. I can change the sorting, which is set by the client, not the server. This means one person can see clones by instance, while another can see clones by image.
If I change this, you can see that I get a new view at the bottom.
There are more changes needed, and some coming. There is a feedback item when you switch to give feedback to the team, and I’d encourage you to do so. Certainly I think sizes or some calculation of total sizes for images and clones is needed. It would be nice to get filters for sizing, so I can also tell if someone is using a clone to do a lot of work and growing it’s size. One of the important things here is that you ought to not get to wedded to a particular clone. We want to rebuild these as needed.
I’d also like to see some way to link images to a source and perhaps group them so that I know how many copies I have of some database, like production. While I think we definitely need a couple of images at any time for rotation, we want to get control of our systems and limit the number.
If you have other feedback, let us know, and we’ll build a better dashboard together.
A customer had a question recently about using Data Masker for SQL Server and temporal (or system versioned) tables. I decided to make a quick demo that will show how this works.
This is a simple demo, using just a few changes, but the concepts are useful for larger systems.
In the demo database for Data Masker, there is a dbo.dm_customer table. I’m going to make this a temporal table with a few schema changes. First, I’ll add two columns that will be used to store the valid datetime ranges.
ALTER TABLE dbo.DM_CUSTOMER
ADD StartTime DATETIME2(3) GENERATED ALWAYS AS ROW START
DEFAULT GETUTCDATE(),
EndTime DATETIME2(3) GENERATED ALWAYS AS ROW END
DEFAULT CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999'),
PERIOD FOR SYSTEM_TIME(StartTime, EndTime);
GO
Next, I’ll enable system versioning, and specify the history table.
ALTER TABLE dbo.DM_CUSTOMER SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DM_Customer_History));
GO
At this point, let’s test. I’ll query the history table and show it’s empty.
Now, let’s make a couple changes.
UPDATE dbo.DM_CUSTOMER SET customer_firstname = 'Jillian' WHERE customer_id = 1000002
UPDATE dbo.DM_CUSTOMER SET customer_firstname = 'Sian' WHERE customer_id = 1000004
INSERT dbo.DM_CUSTOMER ( customer_id,customer_firstname,customer_lastname, customer_gender, customer_company_name,
customer_street_address, customer_region, customer_country, customer_email, customer_telephone,
customer_zipcode, credit_card_type_id, customer_credit_card_number)
VALUES
( '1000', 'Steve', 'Jhones', 'M', 'Redgate', '123 My St', 'CO', 'US',
'sjones@sqlservercentral.com', '30333333333', '80014', '1', '12`345566')
GO
UPDATE dbo.DM_CUSTOMER
SET customer_lastname = 'Jones' WHERE customer_id = 1000
UPDATE dbo.DM_CUSTOMER
SET customer_lastname = 'Jilly' WHERE customer_id = 1000002
UPDATE dbo.DM_CUSTOMER
SET customer_lastname = 'Jill' WHERE customer_id = 1000002
GO
We requery, and you can see there is data in the history table (at the bottom). All of my changes are captured in a sequence of copies of the row.
There are a few more rows in the history table, but they got cut off in the image. That isn’t that important. Just note that the names match up, for the most part, between the tables.
Now the interesting part. I have a masking set that’s similar to ones I’ve used in other articles. In this case, I’m changing names in two substitution rules. However, these rules are set for the dbo.dm_customer table. What about the dbo.dm_customer_history table?
To change this, I need to do a few things. First, I need a Command rule to disable system versioning. This is the way I disconnect the tables so that I can update data.
This has to take place before I change dbo.dm_customer, otherwise I’ll insert a bunch of new data in the history table. My ordering looks like this:
Once this is complete, I now need a Table-Table sync rule to ensure data in one table matches the other. When I create the rule, I need to have a way to join the two tables together. In this case, I use the customer_id column.
Note that I’m ensuring the names and credit card numbers match here. This will wreck my history table slightly, in that all of the rows for a customer_id will have the same value, but this does ensure I don’t have sensitive information leaking.
Once I’m done, I make this a child of the other rules. This ensures that the substitutions run on the main table, then are synchronized to the history table.
You’ll notice I also have another command rule at the bottom. The text for this rule is below, and this rule enabled the temporal link again. I’m assuming the application is somehow using this. If not, you can ignore this rule and leave the tables disconnected if that’s Ok in your application.
All I need to do now is run the rules. Let’s do that.
Once the rules are complete, let’s query the tables again. I’m only looking for those rows that were changed previously.
As you can see, the dbo.dm_customer (top) table and the dbo.dm_customer_history table (bottom) are synchronized. The multiple values I had for different versions of customer 1000002 are now all the same. I’ve lost history, but if this were sensitive information, that works out. I could have used random values instead for the history table, but here I can see linkages if I’m checking for similar data.
Trying to determine how to rewrite history for audit tables is hard. Data Masker can do pretty much anything you would like, but here we need to be sure that we have an idea of what we want to do.
I’ve presented a simple version of setting all history to the current value, which will work in some cases. If not, let me know what you need to see, and I’ll build you a scenario.
Data Masker for SQL Server is an amazing tool in the SQL Provision bundle. Combined with SQL Clone, you can deploy databases with masked data for dev and test environments in seconds. Give it a try today and see what you think.
As I work with SQL Provision, I keep finding new questions and concerns from clients and customers. Recently I had someone wonder if we could determine whether or not a database on which they were working was a SQL Clone cloned copy.
You can, and it’s easy to check. When SQL Clone creates a database, it will use the base image, and ensure there is an extended property set on the database itself.
The function sys.fn_listextendedproperty() is used to return the database extended properties. We can use the DEFAULT keyword for the various parameters, like this:
SELECT objtype ,
objname ,
name ,
value
FROM fn_listextendedproperty(DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
GO
This gives me an empty result set on a non-SQL Clone database if I have no extended properties set. If I had others set, I might get some result. For a database I’ve setup with SQL Provision, I’ll get this:
For the most part, I don’t care that I’m using a clone rather than a native SQL Server database, but there could be places I do care, and certainly I want to filter out this extended property from my version control system.
SQL Provision is a great tool for rapidly giving new environments to developers without the hassles of restoring copies and using lots of storage space. If you want to give it a try, download an evaluation today.
Voice of the DBA 