How I Started

I am writing this as a satisfied customer of Jen and Sean McCown’s DBA Roadmap*. They have included tracks describing how they got their start and it made me think it would be good to share my own story.

Our family got a Commodore 128 for Christmas in 1985 and I had been exposed to computers at school starting in 2nd or 3rd grade. In high school I took two semesters of real college classes on Digital Logic Design and Assembly Language. But I did not choose to study CS or MIS  in college. I got two degrees in Environmental Science and German.

In spite of this, I had jobs during college that set me up for a career in IT .  I spent each summer working at my dad’s company doing a lot of IT tasks under the title of “Clerical Specialist.” I worked in Tech Support for an ISP and as the web editor of my college newspaper. I also worked in the Geography department helping the State Climatologist. One of the things I had done was create an admittedly kludgy way of parsing the data from our rooftop weather station and putting it on the World Wide Web.

Based on those experiences I landed a job at  right out of college for a startup that presented weather data from proprietary hardware on the internet.  I moved to the suburbs of Washington, DC for that job and I am still here 17 summers later.  I was working for the help-desk but got involved in Classic ASP and SQL Server 6.5 development after just a few months.

Since the company was just starting out, we had consultants who weren’t quite ready to give up their day jobs. These guys would come in and work for us after they had already finished 8 hours somewhere else. One of those consultants taught me the history of database systems and drilled into my head that the job of everyone involved with the database is to protect the data. He was also not at all shy about emailing book authors or picking up the phone to call someone from a forum who could help him with his issue.

* The DBA Roadmap is on sale this summer at 50% off using the Coupon Code Half2015

SQL 2016 Temporal Tables

Another interesting SQL 2016 feature is temporal tables. When I first had this concept explained to me I thought they had a lot in common with Slowly Changing Dimensions, and they do. There is already support for temporal tracking in PostgreSQL and Oracle, and SQL 2016 has it now as of CTP 2.2. Here is some stuff I did just now using this new feature:

CREATE DATABASE BackToTheFuture
USE BackToTheFuture

CREATE TABLE BackToTheFuture
(
ID int NOT NULL PRIMARY KEY CLUSTERED,
FavoriteColor varchar(50) NOT NULL,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

INSERT INTO BackToTheFuture VALUES (1, 'Green')
UPDATE BackToTheFuture SET FavoriteColor = 'Blue' WHERE ID = 1

--unfortunately these times are going to have to be hard coded when this demo runs again
--I tried and failed to make the dates dynamic

SELECT * FROM BackToTheFuture --ok so its just blue
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/11/15 22:16' --nothing I hadnt inserted yet
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/11/15 22:17' --wait, what?
SELECT *, SysStartTime, SysEndTime FROM BackToTheFuture --oh look time is in GMT
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/12/15 02:14' --when I still liked green
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/12/15 02:15' --after I started liking blue
USE master
DROP DATABASE BackToTheFuture

SQL 2016 Dynamic Data Masking

One of the features I am excited about in SQL 2016 is Dynamic Data Masking, but after a few minutes using it I have to say proceed with caution here.  As Rodney Landrum points out, all you have to work around the masking is cast the column to nchar. That means on top of masking you would also want to control access to your data through a proc or view. If I need to do that, couldn’t I just do the masking on my own with a proc or view? Anyway, here are my own noodlings with this new capability:


CREATE DATABASE Andrew
USE Andrew

CREATE TABLE MaskingDemo
(SecretString varchar(50),
NotSoSecretString varchar(50)
)

INSERT INTO MaskingDemo VALUES ('Secret','Not a secret')

DBCC TRACEON(209,219,-1)

ALTER TABLE MaskingDemo ALTER COLUMN SecretString ADD MASKED WITH (FUNCTION='default()')

SELECT * FROM MaskingDemo

CREATE USER Andrew WITHOUT LOGIN
GRANT SELECT ON MaskingDemo To Andrew

SELECT * FROM MaskingDemo; --sa can see the secret

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo; --Andrew can not see the secret
REVERT;

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo WHERE SecretString='secret'; --But Andrew can go fishing for secrets
REVERT;

EXECUTE AS USER = 'Andrew';
SELECT *, CAST(SecretString AS nchar(50)) FROM MaskingDemo;
REVERT; --oh crap, Andrew can see the secret

--what if i want Andrew to see the secret
GRANT UNMASK TO Andrew

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo;
REVERT; --now Andrew can see everything

DENY SELECT ON MaskingDemo TO Andrew

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo;
REVERT; --Andrew sees nothing

CREATE VIEW vMaskingDemo AS
SELECT 'xxxxxx' AS Secret, NotSoSecretString FROM MaskingDemo

GRANT SELECT ON vMaskingDemo TO Andrew

EXECUTE AS USER = 'Andrew';
SELECT * FROM vMaskingDemo;
REVERT; --Column is masked

EXECUTE AS USER = 'Andrew';
SELECT CAST(Secret AS nchar(50)), NotSoSecretString FROM vMaskingDemo;
REVERT; --Casting would do nothing

USE master
DROP DATABASE Andrew

CTP 2.3 Updates (9/13/15)

The trace flag is no longer required as Masking is turned on by default. The trace flag will now actual disable it if you run it. Also, the issue where the CAST will displaying the masked data has been fixed.