22 Jun 2016
By Bob Pusateri

SQL Server 2016: What’s Going Away

SQL Server
  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
03 Dec 2015
By Bob Pusateri

Dropping Multiple Databases At Once

SQL Server, T-SQL Syntax
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
19 Nov 2015
By Bob Pusateri

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

Backup, SQL Server
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
08 May 2015
By Bob Pusateri

Who’s In Your Fixed Server Roles?

Security, SQL Server
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