SQL Server and SQL databases are here for quite a while, and there are many techniques and strategies available for the administrators to perform better in it. Many DBAs will not be aware of some of the top tips which will help you mainly in a production environment. Here, we will discuss some random tips which some of you may find informative and helpful in the job of an SQL Server production DBA.
#1. Forfiles utility to get rid of old backup files
Forfiles (forfiles.exe) utility comes pre-installed with Windows Server 2003. IT enables the administrators to perform batch file processing. So, DBAs can use this service in conjunction with the SQL Server Agent, which help delete the old database backups, which will eradicate dependencies on SQL Server maintenance, xp_cmdshell extended stored procedures or the VBScript objects.
#2. Using ‘ALTER USER’ to repair orphaned logins
From SQL Server 2005 SP2, the ALTER USER command of T-SQL’s is having a WITH LOGIN clause too. Choosing this can repair the orphaned logins by changing the user’s SID to security identifier of server login. It can improve not only SQL Server but Windows logins too. Orphaned users get created when a database gets restored from a different server, and that login is independently produced.
#3. Use the ‘sp_addsrvrolemember’ to take up sysadmin role
From SQL Server 2008, sysadmin role is not given to Windows Administrators by default. To cover this, you can initiate SQL Server instance in the single-user mode or maintenance mode and then run ‘sp_addsrvrolemember’ in the Sqlcmd utility stored procedure to add your login to the sysadmin role.
#4. Using PortQryUI for troubleshooting connectivity issues
As suggested by RemoteDBA.com experts, you can use the Microsoft’s PortQryUI to troubleshoot the TCP/IP connectivity issues. PortQryUI is an alternative to PortQry but has a GUI with many predefined services. One of these predefined ports group is meant for SQL Server, consisting of a TCP port 1433 and UDP port 1434. To check the ports, you can just enter IP address or the FQDN (Fully Qualified Domain Name) or target SQL. It is easy to download PortQryUI utility at “PortQryUI.”
#5. Try and use a unique strategy while running the DBCC CHECKDB against bigger databases
Databases tend to become larger day by, so the maintenance procedures like checking integrity with the DBCC CHECKDB command of T-SQL’s may take longer and longer. There are several potential solutions if the DBCC CHECKDB exceeds allocated maintenance period. One instant solution is to access backup to restore DB on a different server and then run the DBCC CHECKDB against the existing server. A second solution is to custom set database’s verify option to CHECKSUM and then run DBCC CHECKDB with PHYSICAL_ONLY option. This combination will make the DBCC CHECKDB run in lesser time, but will still catch the I/O subsystem.
#6. Use a Server Monitoring Tool
“DBA’s can leverage the power of SQL server monitoring tools to give you a clear overview of your database performance, and to troubleshoot any glaring performance issues. The major solutions all have very powerful capabilities.
We’d recommend SentryOne server monitoring, since they’ve been around for over 20 years and have some of the biggest brains in the SQL space behind them.
You can also use the T-SQL’s Server 2008 Center Management servers and local server groups to query multiple servers simultaneously. In an SQL Server 2008, you can also use local server group to connect to the servers frequently accessed quickly.
