SQL Server 2016: What’s Going Away

  SQL Server 2016 is upon us, with all the much-anticipated hoopla and sexy new features people have been lusting over in the CTPs for months. It's always great to see the hype around a new release. But in the circle of (an application's) life, the arrival of new things often means others are going away for good. Let's pause for a moment and reflect upon the two features that, as of SQL Server 2016, are no longer with us: 32-bit SQL Server. SQL Server 2016 is 64-bit only. If for whatever reason you're running on a 32-bit architecture, sadly you're now…
Read More

Dropping Multiple Databases At Once

I'm already at peace with the fact that I'll never know all of SQL Server's secrets, but that doesn't stop me from being surprised every time I figure out something new. In this case, it's another "secret" hiding in plain sight (Books Online). It turns out that the [crayon-5d315880a561c915304503-i/] statement doesn't just have to drop one database. BOL shows that multiple databases can be specified when separated with commas. Let's see it in action. First, create 4 databases: [crayon-5d315880a5623881047756/] Now drop them: [crayon-5d315880a5626498251542/] Yep, all gone. But what if there's an error? Re-run the CREATE statements above, but now let's…
Read More

SQL Server Backup Terminology Part 1: Media Sets & Backup Sets

When backing up a database or transaction log, SQL Server needs to know two basic things: What information the backup should contain Where it's going to be written Many options exist to control the contents and behavior of a backup: whether it's full, differential, or a log, if it should be compressed, copy_only, encrypted, the list goes on and on. Most people will learn these options, understand their effects, and consider themselves to have mastered SQL Server backups. While they're not totally wrong, there's still a lot more to the story: where the backups are going. While it's incredibly simple to use…
Read More

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