Who’s In Your Fixed Server Roles?

It's 10PM*. Do you know who your sysadmin role members are? SQL Server's fixed server roles control incredibly broad permissions across your entire instance. For this reason, membership in these roles should be granted carefully and reviewed often. Fortunately, reviewing role membership is extremely easy, so you can always answer the above question better than Homer. The Easy Way: SSMS GUI In the SSMS Object Explorer, open the "Security" folder for an instance, and then open "Server Roles". You'll see it displays the 9 fixed server roles. Double-clicking on any role brings up a dialog that shows all its members: If you hate typing,…
Read More

Why I Hate Row Compression

This post is part of T-SQL Tuesday #52, which is being hosted this month by Michael J. Swart (@MJSwart). Michael is asking us to argue against a popular opinion, and I'm more than happy to do so, as this is a belief that I've kept to myself for quite a while. SQL Server's row compression feature can be an amazing tool. Not only is it lightweight on CPU usage, especially when compared to page compression, but it can save a significant amount of disk space as well. Your data also remains compressed while in the buffer pool, meaning more rows can be stored…
Read More

Auditing Database Snapshots: Free Lunch!

The ability to keep track of what's being executed on a SQL Server instance, or to create a read-only copy of a database at a specific point in time can be very valuable if required by the solution you are developing. But a lesser-known fact about SQL Auditing and Database Snapshots is that not only do they work well on their own, but they also play nicely together. The Setup Let's say you work for that awesome company AdventureWorks, which embraces open data so much that you can download their corporate database on CodePlex here. That database contains some super-sensitive payroll…
Read More

Wow – I’m an Author!

I'm very proud to have contributed a chapter to Tribal SQL, which is now available for purchase at Amazon and finer bookstores everywhere. This is the first book I've had any part in writing, something I never thought I would do. I always hated English and literature classes in school - if it wasn't math, hard science, or architectural history, I wanted no part of it. I credit the SQL community and blogging for slowly getting me excited about the written word. Getting this book from idea to print was not a sprint but a marathon. While I'm sure there was plenty going on behind…
Read More

A Dynamic Duo: Data Compression and Backup Compression

When I'm presenting on SQL Server data compression, I frequently get asked if data compression and backup compression can be used together. I keep meaning to put together a blog post with a demo so I have something to direct people towards for further reading. So here we are - let's give it the old college try and find out! Compression Primer Methods for compressing data fall into 2 categories: lossy and lossless. Lossy compression reduces the size of data by strategically removing parts of it that are unlikely to be missed. This data is lost forever, hence the term…
Read More