TIL: The VSS Writer

I’m working on a presentation for July involving security, and got to the section in which I discussed turning of unneeded services.  I noticed the VSS Writer and realized, I didn’t actually know what it did…

One trip to MSDN later, I learned that the Volume Shadow Copy Service (VSS) writer allows third-party applications to snapshot and copy SQL Server data and log files while those files are in use.  If this service is off, the data and log files are locked and the only way you can take a backup is through SQL Server full, differential, and transaction log backups.  If the service is on, third-party tools can make real-time copies of these files.

My inclination is to turn this service off and rely on SQL Server’s internal backup strategy, as it’s a known quantity and having two services to perform such a similar action increases your system’s surface area.

TIL: SQL Server 2003

I’m starting off my Today I Learned series with something I didn’t actually learn today.  Fantastic start…

We’re looking for database engineers (check out the posting if you’re interested!).  Today I reviewed one candidate’s resume, and I noticed “SQL Server 2003” on it.

There is no SQL Server 2003.  SQL Server 2003 was a pirated copy of SQL Server 2000 with a different splash screen.  If you put knowledge of SQL Server 2003 on your resume, you are telling me one of four things:

  1. You worked at a company which used pirated software.  Furthermore, you either didn’t know or didn’t care, neither of which is the type of thing you want to broadcast during an interview for a job focused around that product.
  2. You have confused SQL Server versions with Windows Server versions.
  3. You are grabbing bad information from the Internet and passing it off as your own experience.
  4. You aren’t proofreading your resume.

In short, if you put SQL Server 2003 on your resume, it is a negative signal.  I’ll bring it up during the interview, just in case some unscrupulous recruiter “fixed up” your resume, but if you’re the one who put it in there, that’s a huge black mark in my book.

Reorganize Columnstore Indexes

I have created a script to help figure out when to reorganize columnstore indexes in SQL Server 2016.

Note that this script requires SQL Server 2016 (or later) because the database engine team made some great changes to columnstore indexes, allowing us to use REORGANIZE to clear out deleted rows and compact row groups together, as well as its previous job of marking open delta stores as available for compression.

The code is available as a Gist for now, at least until I decide what to do with it.  Comments are welcome, especially if I’m missing a major reorganize condition.

Incidentally, with CTP 3.3, I’ve noticed that there’s no reason to run index rebuilds over index reorganizations, at least in my environment.  It’s possible that there are plenty of environments in which it makes sense to do regular rebuilds, but I’ve noticed reorganization to be more efficient in terms of compacting row groups together, and it’s an online operation to boot.

Cross-Database Memory Optimized Queries Still Not Available

In SQL Server 2014, we learned that memory-optimized tables cannot join to tables outside the current database.  I wanted to see if this had changed in SQL Server 2016 CTP 3.2.

CREATE TABLE [dbo].[MOTest]
(
	MOTestID BIGINT IDENTITY(1,1) NOT NULL,
	ProductSubcategoryName VARCHAR(50) NOT NULL,
	CONSTRAINT [PK_MOTest] PRIMARY KEY NONCLUSTERED
	(
		MOTestID ASC
	)
) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

INSERT into dbo.MOTest
(
	ProductSubcategoryName
)
VALUES
('Mountain Bikes'),
('Road Bikes'),
('Touring Bikes'),
('Cranksets'),
('Chains'),
('Something Else');

SELECT TOP (100)
	*
FROM AdventureWorks2014.Production.ProductSubcategory ps
	INNER JOIN dbo.MOTest m
		ON m.ProductSubcategoryName = ps.Name;

My result:

(6 row(s) affected)
Msg 41317, Level 16, State 5, Line 24
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
In other words, we still cannot cross databases with memory-optimized tables.

New Talk Planned

I’m introducing a new talk for 2016:  Securing SQL Server.  Here’s my abstract:

A default SQL Server installation is reasonably secure, but “reasonably secure” doesn’t cut it anymore in an era in which one bad line of code, one week password, or one open port can result in your customer database ending up on Pastebin.  In this talk, we will look at different methods of securing a SQL Server instance, from venerable (principle of least privilege, Transparent Data Encryption) to novel (Always Encrypted, row-level security).  These tools and techniques will show us ways for developers, database administrators, and network specialists to work together to secure corporate assets and prevent CISOs from ending up on the nightly news.

My plan is to hit on a few topics, introduce some resources to learn more about the topic, and move on.  The goal is to provide an overview of some of the tools and technologies available, including:

  • Applying the principle of least privilege to SQL Server logins, users, and roles.
  • Implementing Transparent Data Encryption.
  • Implementing Always Encrypted in SQL Server 2016.
  • Implementing row-based security, both in SQL Server 2016 and prior to 2016.
  • Using network segmentation to limit access to SQL Server instances.
  • Writing good, parameterized SQL to prevent against SQL injection.
  • Reducing the SQL Server surface area.

There are entire books on SQL Server security, so a one-hour talk can’t cover everything.  Nonetheless, my intent with this talk is to give everybody who attends at least one new resource.

Checking SSRS Report Usage

At work, I recently was asked whether some reports were still in use. These are SQL Server Reporting Services reports, so fortunately there’s an easy way to find that out. Go to the SQL Server instance hosting Reporting Services and go to the ReportingServices database. Then, the following query will get reports run over the past week:

SELECT
	el.ReportPath,
	SUM(CASE WHEN el.RequestType = 'Interactive' THEN 1 ELSE 0 END) AS Interactive,
	SUM(CASE WHEN el.RequestType = 'Subscription' THEN 1 ELSE 0 END) AS Subscription
FROM dbo.ExecutionLog2 el
WHERE
	el.TimeStart > DATEADD(WEEK, -1, GETUTCDATE())
GROUP BY
	el.ReportPath
ORDER BY
	el.ReportPath;

This also breaks out interactive report requests versus subscriptions to see if people are seeking out these reports or if you’re pushing them.