Work

June 2008 Blog Posts

The most important thing that a Database Administrator does is backups. To automate them, we schedule them as jobs. But what other jobs are important on SQL Server instances?
Here are the jobs that I have on every SQL Server 2005 production instance:
Name
...

Microsoft has released three security tools to help customers with SQL injection attacks.
The tools are:
UrlScan version 3.0 Beta, a security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process. By blocking specific HTTP requests, the UrlScan helps prevent potentially harmful requests.
Microsoft Source Code Analyzer for SQL Injection Community Technology Preview (June 2008), a tool that can be used to detect ASP code susceptible to SQL injection attacks.
Scrawlr, a free scanner, developed by HP Web Security Research Group in conjunction...

There are many ways to transfer SQL logins between SQL Server instances. Here are a few of them:
sp_help_revlogin
Transfer Logins Task in a DTS package
Transfer Logins Task in an SSIS package
SQL Server Magazine method
Manually (like that's even a consideration for an experienced DBA)
In an ideal world, an application will connect to a SQL Server instance using one account (or perhaps a couple extra) and then "authenticate" individual users via application code and a SQL table. ...

Every now and then, someone will ask in the SQLTeam forums how to find out what Windows users are members of a particular Windows group so that they can find out who has access to their SQL Server. The question doesn't get asked often, but I can never remember what the answer is unless I google it. Now I'll be able to find the answer here. SQL Server provides an extended stored procedure, xp_logininfo, to get information about Windows users and groups. Syntax: xp_logininfo [ [ @acctname = ] 'account_name' ] ...

Have you ever wondered why your msdb database is unusually large? Did you know that SQL Server keeps historical information about every backup and restore operation in the msdb database? It is recommended that you purge this backup and restore historical information on a scheduled basis. Microsoft provides sp_delete_backuphistory to do the purge. In SQL Server 2000, the stored procedure ran very slow as it cursored through the data. As a result of the slowness, I wrote my own purge stored procedure. I blogged about this a few years back. When we upgraded our systems to SQL Server...

For the past several months, we have been overwhelmed with performance issues on one particular system. We know what is causing it, but it is going to take time to modify the code, test the changes, and then deploy to production. In the meantime, we made several configuration changes that helped performance. Two of the changes were adding data files to the tempdb database and enabling the asynchronous option of automatic statistics updating (AUTO_UPDATE_STATISTICS_ASYNC). I covered our tempdb change in a previous blog. In it, I described how to optimize tempdb and also provided a script to add...

Most of you probably already have done this, but just in case you haven't and find yourself frequently using cmd to run commands such as sqlcmd, you will find this registry "hack" useful. I use sqlcmd on a weekly, if not daily, basis. I'm constantly switching directories to where my scripts are. Rather than switching directories in cmd, I can instead use the "Open Command Window Here" option in Windows Explorer. To get this option on your machine, save the below as a reg file and then double-click on it: Windows Registry Editor...

We are required to start auditing our SQL Servers for various data points. We did not want to write a custom tool if at all possible, so we started looking for third-party products. We found Idera's SQL admin toolset. It doesn't collect all of the information that we want to audit, but it's a great start.
Excerpt from their site about the tool:
Are you a DBA or developer tired of spending countless hours on routine administrative and troubleshooting tasks, like figuring out why a user can't connect, or moving databases, or producing reports for your boss to show that the backups...

Attention Loyal Readers,
I've updated my RSS feed to use FeedBurner. If you haven't already done so, please update your link to this one: http://feeds.feedburner.com/RamblingsOfADba
If you don't update your feed, you will be redirected to the new one. However, I won't know how many loyal readers I have out there. I could be persuaded to write more if I knew how big my audience is.
For those of you who read my blog through my main blog page or through the SQLTeam main blog page, I'd recommend getting a blog reader to make it easier to read blogs. I use Google Reader.
If...

There's a lot of information out there on how to avoid SQL injection attacks, but I wanted to point you to this blog due to the recent increase in such attacks.
Thank you, Buck Woody, for bringing this to our (SQL Server MVPs) attention.