Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links



Search this Blog


Post Categories


June 2008 Blog Posts

SQL Server jobs on production instances

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 ...

posted @ Monday, June 30, 2008 9:46 AM | Feedback (18) | Filed Under [ SQL Server - Database Administration ]

Security Tools to help customers with SQL injection attacks

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...

posted @ Tuesday, June 24, 2008 1:53 PM | Feedback (1) |

How to transfer SQL logins between SQL Server 2005 instances

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. ...

posted @ Tuesday, June 24, 2008 9:16 AM | Feedback (6) |

How to get information about Windows users and groups using T-SQL

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' ] ...

posted @ Monday, June 23, 2008 9:36 AM | Feedback (0) |

How to delete backup and restore history from msdb

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...

posted @ Wednesday, June 18, 2008 2:12 PM | Feedback (2) |

Asynchronous Update Statistics

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...

posted @ Monday, June 16, 2008 10:37 AM | Feedback (1) |

Open Command Window Here

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...

posted @ Friday, June 13, 2008 12:19 PM | Feedback (1) | Filed Under [ Other ]

Idera's SQL admin toolset

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...

posted @ Wednesday, June 11, 2008 1:43 PM | Feedback (7) | Filed Under [ SQL Server - Database Administration ]

New RSS Feed

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...

posted @ Friday, June 06, 2008 11:53 AM | Feedback (5) | Filed Under [ Other ]

SQL Injection Attacks

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. 

posted @ Thursday, June 05, 2008 2:33 PM | Feedback (2) | Filed Under [ SQL Server - General ]

Powered by:
Powered By Subtext Powered By ASP.NET