SQL Server Transparent Database Encryption (TDE)

Overview of TDE with some details on major administrative issues. Many people who’ve played around with TDE seem to have had trouble with restoring a TDE database on an alternate server, and the confusion seems to stem primarily from the deep encryption heirarchy for TDE. It’s not too hard, however, once you realize that you…

1

Alert On Low Disk Space, Including Mount Points

A common task for many database administrators (DBAs) is to set up alert emails to notify themselves when free disk space falls below a certain threshold (e.g. 10%). Before SQL Server ran on Windows clusters that included mount-points, there were a number of methods for checking free disk space, but most didn’t report on the…

6

SQL Server and PowerShell Security

Sometime back, I heard that Microsoft was going to start using PowerShell scripts to monitor and optionally enforce security standards in SQL Server configurations, such as in the Microsoft Security Compliance Manager (SCM). I knew little about PowerShell, but right away I had a serious concern about whether or not requiring the use of PowerShell…

0

File-Sizing

One of the primary areas of responsibility for DBA’s is maintenance, and one of the primary maintenance tasks is file-size management. First I’ll present a list of the file-sizing tasks with short explanations for each one, then some background information, and then some T-SQL to give you the file sizes for all data and log…

0

Defragmenting Indexes

Last update to script 1Nov2011. Maintaining indexes is a primary duty of Database Administrators, because appropriate indexes are critical to maintaining high performance in any relational database, and proper maintenance is required to avoid excessive fragmentation. In case that’s not clear enough, let me emphasize: index fragmentation can dramatically degrade performance. While the Maintenance Plan…

0

Agent Schedules

The SQL Server Management Studio has a “Job Activity Monitor” which provides a lot of useful information about scheduled jobs. However, it does not show the schedule type or a schedule description, nor does it clearly distinguish  between the job being enabled vs. the schedule being enabled. The query in the attached file provides that…

0

SQL Server Runaway Transaction Logs

When SQL Server is used without a qualified DBA, runaway transaction logs are by far the most common problem. This behavior is because the default configuration of SQL Server is intended to support production databases, and production databases should normally have their transaction logs backed up on a regular basis. If the transaction logs are regularly backed up,…

0

Enabling Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) is a feature added in SQL Server 2008 which allows you to encrypt an entire database at a time. A major potential benefit is that when TDE is enabled, all backups are automatically encrypted, which may be worthwhile if you have concerns that copies of the backup files may end up…

0

Public Not Granted Server Permissions

The pre-defined policies that come with SQL Server 2008 include one called “Public Not Granted Server Permissions”. Running this policy in evaluation mode on a default installation will show a non-compliance failure due to 5 server-level permissions that are assigned to the public role during installation. Removal of these 5 permissions will make your server…

3

Remove Public and Guest Permissions

You can’t get rid of the “public” role and by default in SQL Server 2005 and 2008 many objects have permissions granted to public. For those reasons, you might expect that those permissions are required for SQL Server to function correctly, but you’d be wrong. In fact, you have to wipe them out to comply…

13