I very recently had the pleasure of being interviewed by Mohammad Darab. He asked some very insightful questions, and I’m so grateful he decided to ask me!
You can read the full interview here.
He also has a very impressive list of interviews on his site. See the entire list here!
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 out of luck – 2014 is the end of the road. On the bright side, there’s probably some new hardware in your future!
Compatibility Level 90. If you’re using compatibility level for backwards compatibility, the oldest available version in SQL Server 2016 is 100, which corresponds to SQL Server 2008. Compatibility level 90, SQL Server 2005, is no longer an option.
Hopefully these changes didn’t catch anyone off guard. To help better prepare for the removal of features in future versions, Microsoft maintains a list of deprecated features in the next version of SQL Server. Here’s a few highlights of what’s most likely going away in SQL Server vNext:
Backup/Restore WITH PASSWORD. This one has been bad news for a long time. Stop using it years ago! If you didn’t, stop using it TODAY :)
Encryption with the RC4 or RC4_128 Algorithms. Better options have been available for a while, hopefully you’re using them for any new development at this point.
Remote Servers. Architect them out. If you really can’t, use linked servers instead.
SET ROWCOUNT. I doubt this one will ever really go away, but it’s nice to dream. The TOP keyword has been available for quite some time now.
HOLDLOCK table hint (without parenthesis). This one’s easy, put HOLDLOCK in parenthesis. Like this: (HOLDLOCK). See? It’s easy! Start doing it!
Database Safety Bear says: “Don’t delay, start planning for deprecated features TODAY!”
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 DROP DATABASE 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:
|
1 2 3 4 |
CREATE DATABASE DB1; CREATE DATABASE DB2; CREATE DATABASE DB3; CREATE DATABASE DB5; |
Now drop them:
|
1 |
DROP DATABASE DB1, DB2, DB3, DB5; |
Yep, all gone.
But what if there’s an error? Re-run the CREATE statements above, but now let’s drop 5 databases instead of 4. DB4 doesn’t exist (much like Terminal 4 at Chicago’s O’Hare Airport).
|
1 |
DROP DATABASE DB1, DB2, DB3, DB4, DB5; |
The above statement will throw an error that it is unable to drop DB4 because that database doesn’t exist, but the other 4 databases are dropped without incident. The same will happen if a user is connected to one of the databases: that one will remain, but all others will be dropped.
So there you have it: you can drop multiple databases with a single statement in SQL Server. (According to Books Online this does not work in Windows Azure SQL Database.) It’s amazing the things you can learn from reading documentation!
When backing up a database or transaction log, SQL Server needs to know two basic things:
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 the TO DISK option and write the backup out to one or more files, there are a lot of possible controls over how and where a backup is written out to tape or disk. This post, along with a few others, will discuss those features.
Whatever our backup contains, it needs to go somewhere. That somewhere is called a media set. The concept of a media set may be unfamiliar because their creation is handled automatically when taking a backup. A media set consists of one or more devices of a single type. Currently, that means:
Media sets cannot contain more than one type of device, so creating a media set that utilizes both a file and a tape is not possible.
Backing up a database will create the necessary media set (if it doesn’t already exist) and then write the backup to the media set. Technically the backup being written to the media set is known as a backup set.
But enough talk, let’s try some examples and see how this all works:
First, let’s clear out the backup history so the results of these examples are more apparent. The sp_delete_backuphistory procedure takes a date parameter and will delete all records older than that date. Passing in the current date means everything will be cleared out. As with all my code examples, this should only be executed on a test machine, and never in production environments!
|
1 2 3 |
DECLARE @Oldest_Date DATETIME; SET @Oldest_Date = SYSDATETIME(); EXEC msdb.dbo.sp_delete_backuphistory @Oldest_Date; |
Now, let’s create a user database and back it up to a single file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE [tempdb]; GO IF DB_ID('DemoDB') IS NOT NULL BEGIN ALTER DATABASE [DemoDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [DemoDB]; END GO CREATE DATABASE [DemoDB] GO ALTER DATABASE [DemoDB] SET RECOVERY SIMPLE GO BACKUP DATABASE [DemoDB] TO DISK = 'C:\Temp\DemoDB1.bak'; |
Creating this backup automatically created a media set. SQL Server maintains a table of media sets in the msdb database. To view it, run the following:
|
1 |
SELECT * FROM msdb.dbo.backupmediaset; |
(click to see all columns)
Looking at the results, there’s not a whole lot to a media set. It’s mostly identifiers and a few properties. There’s nothing in there at all about the backup itself; the media set is merely a container that holds backups. Information about the backup is stored as part of the backup set, which has its own table in msdb. The following query displays information about the backup set. Fill in the media_set_id value from the media set query above before running it.
|
1 2 3 |
SELECT b.* FROM msdb.dbo.backupset b INNER JOIN msdb.dbo.backupmediaset m ON b.media_set_id = m.media_set_id WHERE m.media_set_id = <enter media_set_id here>; |
(click to see all columns)
The backup set contains lots of information about the backup and state of the database when it was taken, including the backup’s duration both in terms of time and the log sequence number. Much of this same information can be found in the backup header, which can be queried using the RESTORE HEADERONLY command:
|
1 2 |
RESTORE HEADERONLY FROM DISK = 'C:\Temp\DemoDB1.bak'; |
(click to see all columns)
A media set is effectively a container, and containers that can only hold one of something are pretty boring. To make sure media sets aren’t boring, Microsoft gave them the ability to contain multiple backup sets. Now let’s take another backup of the same database, and write it out to the same file as before.
|
1 2 |
BACKUP DATABASE [DemoDB] TO DISK = 'C:\Temp\DemoDB1.bak'; |
But what happened to the first backup we took? Nothing at all, actually – it’s still there. The media set (backup file) now has a second backup set containing the second backup.
This can be observed by either querying the backupset table or the backup header. Two entries will now be shown.
|
1 2 3 |
SELECT b.* FROM msdb.dbo.backupset b INNER JOIN msdb.dbo.backupmediaset m ON b.media_set_id = m.media_set_id WHERE m.media_set_id = <enter media_set_id here>; |
(click to see all columns)
It’s also completely possible for backup sets of different databases to be contained in the same media set. To prove this, let’s add a backup of the master database to our existing media set.
|
1 2 |
BACKUP DATABASE [master] TO DISK = 'C:\Temp\DemoDB1.bak'; |
The backup set query from above will now show the media set contains three backups: 2 of DemoDB and 1 of master.
(click to see all columns)
With multiple backup sets being contained within a media set, things get a little bit trickier. It’s now that much easier to lose multiple backups when only a single file needs to be deleted, lost, or damaged. Restores are also a bit more work, because if the media set contains multiple backup sets, you’ll need to specify which one is being restored. This is done by using the RESTORE statement’s FILE option (which adds to the confusion because in this case, “file” really refers to the backup set.)
To restore the 2nd backup taken of the DemoDB database into a new database called DemoDB_2, the syntax would be as follows:
|
1 2 3 4 5 |
RESTORE DATABASE [DemoDB_2] FROM DISK = 'C:\Temp\DemoDB1.bak' WITH FILE = 2, MOVE 'DemoDB' TO 'C:\Temp\DemoDB_2.mdf', MOVE 'DemoDB_log' TO 'C:\Temp\DemoDB_2.ldf'; |
If you don’t specify which FILE to restore, it defaults to FILE = 1, the first backup set in the media set. The MOVE options tell SQL Server to restore the database files to a new location, rather than attempting to overwrite the existing DemoDB files (and failing).
And then there’s compression. If you look above to the query against
msdb.dbo.backupmediaset, you’ll see that the result set contains a column called
is_compressed. SQL Server native backup compression occurs at the media set level. All backups in a media set must have the same level of compression, so the entire media set will contain backup sets that are all compressed, or all uncompressed. Try adding a compressed backup of DemoDB to our existing (uncompressed) media set and you’ll be greeted with the following error.
|
1 2 3 |
BACKUP DATABASE [DemoDB] TO DISK = 'C:\Temp\DemoDB1.bak' WITH COMPRESSION; |
As this error leads us to believe, there are many backup options that can control the behavior of backup sets and media sets. My next post in this series will discuss and explain them.
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.
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, then this method is for you. Otherwise, you’ll probably like
In SQL Server, where there’s a will, there’s a way to do something in T-SQL. And it will probably be better/faster/cheaper too. Here’s the query I use to check server role memberships:
|
1 2 3 4 5 6 7 8 |
SELECT rp.name AS Server_Role, mp.name AS Role_Member, mp.type_desc AS Member_Type, mp.is_disabled AS Login_Is_Disabled FROM sys.server_role_members srm INNER JOIN sys.server_principals rp ON srm.role_principal_id = rp.principal_id INNER JOIN sys.server_principals mp ON srm.member_principal_id = mp.principal_id; |
On my test server here at home, it returns the following results:
You can see the 4 columns it gives us: the name of the server-level role, the name of the login, what type of login it is, and whether or not the login is disabled. The SSMS role member dialog shown above gives no indication if a login is disabled or not, so I find that column especially handy in the query.
It’s important to note that this query returns members of all server-level roles at once. This example shows only the members of the sysadmin role because it’s the only one with members on this instance.
Ask anyone familiar with SQL Server what the most dangerous role is, and they’ll probably say “sysadmin.” While sysadmin has the keys to the kingdom in the sense that its members can do absolutely anything on the server, there is something securityadmin has that sysadmin doesn’t: secrecy.
Members of the securityadmin role can manage any login on the server, including membership in server level roles. This means someone with securityadmin rights can:
If you’re only looking at members of the sysadmin role, you will be none the wiser. When checking server-level roles, remember that securityadmin is every bit as powerful and dangerous as sysadmin. It just might save your bacon!
*It’s always 10PM somewhere