Seven Tips for SQL Server Production DBAs

Because SQL Server has been around for quite a while, there are many tools and techniques available to help you use it—so many that you might not even be aware of them. Here are seven tips on how you might use some of the available tools and techniques to make your job as a SQL Server production DBA easier.

1. Use the Forfiles utility to delete old database backup files. The Forfiles utility (forfiles.exe) comes installed with Windows Server 2003 and later. Forfiles enables batch processing of files. In conjunction with a SQL Server Agent job, you can use this utility to delete old database backup files to remove dependencies on items such as SQL Server maintenance plans, the xp_cmdshell extended stored procedure, or VBScript objects. For example, the following command uses Forfiles to delete all .bak files that are two days old or older in the E:sqlbackup folder:

forfiles /p "e:sqlbackup" /m "*.bak"  /c "cmd /c del /Q @path" /d -2

Note that you would enter this command all on one line at the command prompt. For more information about this utility, see Microsoft's "Forfiles" web page.

2. Use ALTER USER WITH LOGIN to repair orphaned logins. Starting with SQL Server 2005 SP2, T-SQL's ALTER USER command includes the WITH LOGIN clause. This clause repairs orphaned logins by changing the database user's SID to the server login's security identifier. It repairs both Windows and SQL Server logins. Orphaned users are created, for example, when a database is restored from another server and the login was independently created (not copied from the other server). For more information about ALTER USER WITH LOGIN, see Laurentiu Cristofor's blog "SQL Server 2005: Some new security features in SP2" and the "ALTER USER" web page in SQL Server Books Online (BOL).

3. Use sp_addsrvrolemember to give yourself the sysadmin role. In SQL Server 2008, the Windows built-in Administrators group isn't given the sysadmin role by default. So, what happens if you inherit a SQL Server instance and you need to give yourself the sysadmin role? As a Windows Administrator, you can start the SQL Server instance in single-user mode (aka maintenance mode), then run the sp_addsrvrolemember system stored procedure in the Sqlcmd utility to add your Windows login to the sysadmin role. For more information about this technique, see Raul Garcia's blog "Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005".

4. Use the PortQryUI utility to troubleshoot connectivity problems. To troubleshoot TCP/IP connectivity problems, you can use Microsoft's PortQryUI utility. PortQryUI is the same as the venerable PortQry, except PortQryUI includes a GUI and some predefined services. A predefined service is a group of ports to scan. One of the predefined groups of ports is for SQL Server, which consists of UDP port 1434 and TCP port 1433. So, to check these ports, you simply enter the IP address or Fully Qualified Domain Name (FQDN) of the target SQL Server instance. Then, in the Service to Query drop-down box, select SQL Service and click the Query button. If the ports are OK, the utility will tell you that the ports are Listening. Otherwise, it will tell you the ports are being Filtered or are Not Listening. You can download the PortQryUI utility from the "PortQryUI - User Interface for the PortQry Command Line Port Scanner" web page.

5. Use a different strategy when running DBCC CHECKDB against large databases. As databases become larger, maintenance procedures such as checking database integrity with T-SQL's DBCC CHECKDB command take a lot longer. What can you do when DBCC CHECKDB exceeds the allocated maintenance window period? Paul S. Randal offers several potential solutions in his blog "CHECKDB From Every Angle: Consistency Checking Options for a VLDB". One solution is to use a backup to restore the database on a separate server and run DBCC CHECKDB against that server. Another solution is to set the database's page verify option to CHECKSUM and run DBCC CHECKDB with the PHYSICAL_ONLY option. With this combination, DBCC CHECKDB runs in significantly less time, yet it still catches I/O subsystem and page corruption.

6. Use the Import Package Option to deploy SQL Server Integration Services (SSIS) packages to the msdb database. SSIS has several deployment options. A simple method to deploy SSIS packages to SQL Server's msdb database is as follows. In SQL Server Management Studio (SSMS), connect to Integration Services. Under Stored Packages, right-click the MSDB folder and select Import Package. In the dialog box, specify the SSIS package's current location and name. Set the Protection Level to Rely on server storage and roles for access control, then click OK. Manually copy any XML config file related to the SSIS package to the target server. If the package uses SQL Server configurations, the package connection string to the SQL Server configuration might need to be updated with the correct server name before you import it. For more information, see the "How to: Import a Package Using Integration Services Service" web page in SQL Server BOL.

7. Use SQL Server 2008's local server groups and Central Management Servers to query multiple servers at the same time. In SQL Server 2008, you can use a local server group to quickly connect to servers that you frequently manage. You can also use a Central Management Server to store servers' registration metadata so that team members can use the same registrations. Both can contain SQL Server 2008, SQL Server 2005, and SQL Server 2000 servers. In SSMS, you can query the servers in either a local server group or Central Management Server and combine the servers' results. To do so, go SSMS's Registered Servers window, right-click the local server group or Central Management Server, and select New Query. In the Query Editor, type the T-SQL command you want to run and click Execute. You'll get one result set with two columns. The first column contains each server's name, and the command output from that server appears in the second column. For more information, see the "How to: Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio)" web page in SQL Server BOL.