SQL Saturday Ideas

I have volunteered at or helped run a bunch of SQL Saturdays at this point. I am saving a link to Andy Mallon’s Blog Post so I won’t lose it.

I like the idea of having water or coffee, if for no other reason than it keeps things simple and you won’t get complaints about the mix of sodas. Paring everything else down but leaving a really good quality lunch seems like a good plan.

SQL Server 2016 Reporting Services Accessibility

Last weekend I spent some time creating very basic tabular reports in SSRS 2016 to see how they handle accessibility, WCAG and Section 508 issues. It looks like things are largely unchanged since the first SQL 2008 Service Pack which introduced the AccessibleTablix property to the various render options (See this prior post). Now you will want to add the AccessibleTablix item to your HTML4, HTML5 and MHTML renderers. You will get tags linking the detail cells to the header but you will still be lacking a few of the items needed to pass a WCAG audit with flying colors (e.g. TH tags in the header row).

In my testing on the Chrome browser, the HTML 5 renderer seems to be the default used by SSRS. I assume the HTML 4 renderer still exists purely for backwards compatibility.

If you are wondering why Microsoft has not addressed accessibility better, it is probably a simple matter of priorities. When I ask people from other parts of the country (outside the DC region) about Section 508 they mostly have never heard of it. It’s similar with some of the security stuff we all have to deal with (e.g. FISMA).  Unless and until it becomes a financial problem for Microsoft (i.e. big sales are being lost because of this), we will not likely see this change. Accessibility is of great importance to anyone working with the US Federal or EU governments (including Microsoft), so I hope future versions will include improvements.

SQL Server 2016 Editions

Just a quick update now that we know that SQL Server 2016 is being released on June 1.

Joey Dantoni has helpfully parsed many pages of Microsoft verbiage to let us know that all the features I was playing with in my blog posts last year are going to be Standard Edition features. Many of you have been asking me which Edition you would need for temporal and dynamic data masking and I cynically assumed they would be Enterprise features. Microsoft has decided to be awesome and prove me wrong.

You may have heard that SQL Server 2016 finally adds some much needed updates to Reporting Services, including HTML 5 rendering (that’s right, the reports will now work on pretty much any device). But I am willing to bet that Section 508 and other accessibility needs were not addressed. I will post more on that as I learn more about the product.

What does the end of support for older .NET frameworks mean for SQL Server?

On January 12, 2016 support for many older versions of the .NET Framework ends.

Since the support policy links the .NET support to the OS, then if you are running a supported version of Windows older .NET Frameworks are supported by association. One would imagine that the RTM Windows Server 2016 will not be supporting .NET 3.5 SP1. With the installer for SQL 2016 requiring .NET 3.5 SP1, you might ask yourself, “how can Microsoft be requiring a piece of software that is reaching end of life?”

In the only place SQL Server is mentioned on this page, it says that “newer versions” of SQL Server were based on .NET 4. When I installed SQL 2016 on Windows Server 2016 today it required me to add the .NET 3.5 SP1 feature before the installation would proceed. This makes little sense to me, so if anyone can clue me in I will gladly update the information here. With IT shops likely launching a campaign to remove anything earlier than .NET 4.5.x from their networks soon we will need an official response from Microsoft on this.


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:

USE BackToTheFuture

FavoriteColor varchar(50) NOT NULL,

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

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:

USE Andrew

(SecretString varchar(50),
NotSoSecretString varchar(50)

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

DBCC TRACEON(209,219,-1)


SELECT * FROM MaskingDemo

GRANT SELECT ON MaskingDemo To Andrew

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

SELECT * FROM MaskingDemo; --Andrew can not see the secret

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

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

SELECT * FROM MaskingDemo;
REVERT; --now Andrew can see everything

DENY SELECT ON MaskingDemo TO Andrew

SELECT * FROM MaskingDemo;
REVERT; --Andrew sees nothing

SELECT 'xxxxxx' AS Secret, NotSoSecretString FROM MaskingDemo

GRANT SELECT ON vMaskingDemo TO Andrew

SELECT * FROM vMaskingDemo;
REVERT; --Column is masked

SELECT CAST(Secret AS nchar(50)), NotSoSecretString FROM vMaskingDemo;
REVERT; --Casting would do nothing

USE master

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.

Epic Life Quest

I am arriving somewhat late to the Epic Life Quest party, but I thought I would create one and I am starting kind of small with things that I think could reasonably be achieved within the next 6 months.

I have already done things and traveled places that many people would be jealous of. This is more about setting goals, and wow it’s much more fun to set your own personal and career goals than to be forced to do it at work for your performance review.

Career Goals

  • Achieve a 100 Reputation on a StackExchange site. I have a colleague with >6000. George Stocker has a billion. I should be able to get to 100, right? I have never gotten far because I need to focus on writing good, focused questions and clear answers. I can’t be like a politician and respond with a rehearsed speech that doesn’t actually answer the question.
  • Blog at least 12x this year. If only I could finish all those drafts, this would be done easily. As a working grad student I missed all of Lost doing school work in the spare bedroom. This goal would take far less time.
  • Attend 3 SQL Saturdays. I am planning to attend Richmond, Baltimore and Philadelphia in the next 6 months.

Health Goals

  • Lose 10% of my body weight. This is a repeatable milestone. At one point I weighed so much less than I do now that no one recognized me. Over the last few years I have gained all that weight back.
  • Exercise 3.5 hours every week. This is based on the idea that I should get 30 minutes a day of strenuous cardio, which is easy with a treadmill in my basement and a million episodes of Star Trek the Next Generation on Amazon Prime.

Financial Goals

  • Reduce my credit card debt to [REDACTED]. This is another repeatable thing. The 2013 government shutdown and a few job changes have left me in a hole that I would rather not be in. I realize I can’t dig my way out overnight, but eventually the redacted number will be a 0.

 Family Goals

  • Play a non-electronic game with my family every week. This is always so much fun and getting the whole family around the table for an hour after dinner should not be so hard.
  • Help Jennifer learn programming. My wife is looking to re-invent herself in a technology-oriented career after watching the traditional paper government publishing business seriously shrink in our current budget environment. She is attending PyCon next month and has already gone to a few code training events for women. There is even an Indie GoGo campaign to help her and others pay for the travel to Montreal.

Recent Achievements

Things I have already achieved in the last 6 months that have been super helpful:

  • I started using LastPass. I did this in response to the PASS password snafu and it has changed my life. So much peace of mind and now I look forward to creating yet another online account. Would you believe I already have 94 sites in there?
  • I signed up for CleanTalk on this blog.  It’s amazing how quickly the spam about Coach Bags accumulates if you leave WordPress alone.
  • Volunteered at SQL Saturday DC. I moved so many tables and my back hurt so much at the end of the day, but it felt good to be contributing to a great event. The obvious next step would be to present at a SQL Saturday (Look I wrote that down, are you happy now?).

Things to Keep Up

  • GTD and Inbox Zero, adding the higher altitudes so I can keep my sights on longer term goals
  • Using my ScanSnap more and eliminating more paper

Section 508 and SSRS

Section 508 is a US Government standard for making technology more accessible to people with disabilities. It’s a must for public-facing government websites, and for companies that do business with the federal government.

One of the trickiest things in accessibility is tables. Imagine you have a 12 column wide table that goes for 50 rows.  Someone using a screen reader to have that information read to them needs context for each of the columns in the subsequent rows. You have to supply that context through metadata, linking each cell back to the header.

Microsoft began supporting basic table accessibility in SSRS when it issued two patches to the HTML4 and MHTML rendering assemblies. On SQL 2008 R2, you have these patches at version 10.50.1753 and higher. On SQL 2012 & 2014 you are all set, just heed the following note.

Important Note: Accessible rendering is not done by default. You must modify the RsReportServer.config to set AccessibleTablix=true for each render option (e.g. HTML4 and MHTML). See the KB articles for more details on how to locate and modify the file. This will work for Report Manager if you’re using native mode, but it won’t work if you’re using the Report Viewer control in a custom .NET application. For custom .NET applications, you need set the option programatically:

this.rdlViewer.InteractiveDeviceInfos.Add("AccessibleTablix", "true");

I am not 100% sure how to set this option for SSRS SharePoint integrated mode, but I would be guess in that case the RsReportServer.config setting would be picked up there.

Here is a look at how the HTML renders differently with and without AccessibleTablix=true:

Regular HTML
Without AccessibleTablix set to true, no id or headers tags

Accessible HTML
With AccessibleTablix, headers tags in each cell link back the id of the header cell

While setting AccessibleTablix will give you the links between header and cell, it will not render the header row using <th> rather than <td>. Many 508 testing programs, such as the Wave Toolbar for Firefox, will ding you for that.

It appears that MSFT hasn’t really added much to SSRS since SQL 2008 R2 except for better support for Chrome and iOS. So SSRS HTML4 and MHTML renderings are pretty much the same in 2014 and 2012 as they were in 2008 R2.  Therefore it seems unlikely that SSRS HTML4 reports will ever be fully Section 508 compliant.

The Voluntary Product Accessibility Templates (VPATS) that Microsoft has created for the various SQL Server versions support the notion that SSRS Section 508 support is only provided at a basic, limited level.