Seven Tips for SQL Server production environment DBA

Original article translated from: http://database.ctocio.com.cn/452/8976452.shtmlbecause of the high popularity of SQL Server, there are also a large number of auxiliary tools that make DBA feel dazzled, in order to avoid confusion in these tools heap, this article will provide some tool selection and usage skills to the DBAs who manage production systems to make their work easier.

　　1. Use the forfiles command to delete old database backup files

The forfiles command is a built-in command line tool in Windows starting from Windows Server 2003. It is mainly used for file batch processing, using SQL Server as a proxy job, and adding this tool, the old database backup files of SQL Server can be deleted. In the past, this work was generally dependent on the SQL server maintenance plan, xp_mongoshell extended storage process, or VBScript object. The following forfiles command deletes all. BAK files in the E: \ sqlbackup folder two days ago and two days ago.

Forfiles/P "E: \ sqlbackup"/M "*. Bak"/C "CMD/C del/Q @ path"/D-2

For detailed usage of forfiles, see its command line help instructions, or refer to Microsoft's official documentation: http://technet2.microsoft.com/WindowsServer/en/Library/9660fea1-65c7-48cf-b466-204ba159381e1033.mspx.

　　2. Use alter user with login to repair orphan Login

From SQL Server 2005 sp2, the alter USER command for the T-SQL contains the with login clause, which fixes orphan login by modifying the SID of the database user to the Security Identifier of the server login, it can repair both Windows and SQL Server logon functions. When the database is restored from another server, the login is independently created (not copied from other servers). At this time, the database is created as an orphan user. For details about alter user with login, refer to the blog article "SQL Server 2005: some new features in SP2" in Laurentiu cristofor ", for more information, see the alter USER command page in the SQL Server online ebook.

　　3. Use SP_ADDSRVROLEMEMBER to promote itself to the SysAdmin role.

In SQL Server 2005, by default, the Windows built-in Administrators Group does not grant it the SysAdmin role as the windows system administrator, you can talk about starting SQL Server to single-user mode (maintenance mode), then running the SP_ADDSRVROLEMEMBER system stored procedure in the sqlcmd command line environment, and adding your Windows login user to the SysAdmin role. For more information, see Raul Garcia's blog article "Disaster Recovery: What should I do when the SA account password in SQL Server 2005 is lost"

　　4. Use portqryui to troubleshoot the connection

To solve TCP/IP connection problems, you can choose the portqryui tool provided by Microsoft. This tool is as easy as portqry. In fact, it is a layer of coat outside portqry, and it has some built-in services, for example, a group of port scans, including UDP 1434 and TCP 1433 ports. Therefore, to check these ports, you only need to enter the IP address of the target SQL server instance or the Fully Qualified Domain Name (FQDN). If the port is accessible, portqryui will tell you that the port is being monitored. Otherwise, it will tell you that the port may be filtered out or not listened. Can be from http://www.microsoft.com/downloads/details. aspx? Familyid = 8355e537-1ea6-4569-aabb-f248f4bd91d0 & displaylang = EN download this useful tool.

　　5. Use different policies when running DBCC checkdb on large databases

As the database grows, it takes a long time to use DBCC checkdb command line tools such as the T-SQL to check database integrity. What if the execution of DBCC checkdb exceeds the assigned maintenance window period? Paul S. randal provides several methods in his blog (blog link: http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx), one solution is to use backup to restore the database on an independent server, then run DBCC checkdb on this server. Another solution is to set the page verification option for the database (from checksum to physical_only), and then run DBCC checkdb, in this way, the running time of DBCC checkdb is greatly reduced, but it will still affect the I/O subsystem and page corruption.

6. Use the import package option to deploy the SQL server integration service (SSIS) to the MSDB database.

SSIS has multiple deployment methods. The simplest method is to deploy SSIS to the MSDB database of SQL Server and connect to the integration service in SQL Server Management Studio (SSMs, under the storage package, right-click the MSDB folder and select import package. In the displayed dialog box, select the current position and name of the SSIS package, set the protection level based on the server storage and access control roles, and click OK. Manually copy all xml configuration files related to the SSIS package to the target server. If this package uses SQL server configuration, before importing, you may need to use the correct server name to update the connection string connecting to SQL Server. For more information, see the "how to use the integrated service import package" page in the SQL Server online ebook.

　　7. Use the SQL Server 2008 local server group and central management server to query multiple servers at the same time

In SQL Server 2008, you can use a local server group to quickly connect to the servers you frequently manage, or use a central management server to store the registration metadata of the servers, in this way, team members can use the same registration information, including SQL Server 2008, SQL Server 2005, and SQL Server 2000 servers. In SSMs, You can query the servers in the local server group or central management server, and combine the query results to enter the SSMs registered server window, right-click on a local server group or central management server and choose new query. In the query Editor, enter the T-SQL command and click run to get a result set containing two columns, the first column includes the name of each server, and the second column is the command output from the server. For more information, see "how to execute statements simultaneously on multiple servers (SQL server console)" in the SQL Server online ebook )".