Login Triggers, Data File Defrags, and More
Edited by Nancy Michell
Setting the Service Account
Q In SQL Server™ 2000, I used to set the service account for the SQL Server Engine and Agent using the Services applet in Administrative Tools. Now I hear that in SQL Server 2005 I'm supposed to use the Configuration Manager tool. Why can't I just keep using the Windows tools?
A SQL Server 2005 is built to be more secure than previous versions. In many shops, users would just set internal accounts, such as LocalSystem, to run SQL Server. But these accounts often either have more or fewer rights and permissions under Windows® than they need. You should create a Windows account with no elevated privileges to run the SQL Server 2005 Engine and Agent services. If you select these accounts with the Configuration Manager, they will automatically be granted the proper rights and permissions in both SQL Server and the operating system. If you use the Windows tools to manage the SQL Server services, you might not grant the proper rights, or you may grant too many.
For more details, see the tip Changing the Service Account.
Who's Logging onto My Server?
Q I want to know who is logging onto my server and when. I also want to restrict some specific users to certain time periods and would like to know how to fire a trace to track down user activity. Is any of this possible?
A Yes, you can do all of these things with SQL Server 2005 if you have Service Pack 2 installed.
SQL Server 2005 allows you to create login triggers that can fire a T-SQL or stored procedure in response to a LOGON event. You can use a login trigger to audit and control users by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for specific logins. Note that the event is fired only after a login is successfully authenticated, but just before the user session is actually established. Therefore, all messages originating from inside the trigger (such as messages or errors) from the PRINT statement are sent to the SQL Server error log. If the authentication happens to fail for a login, then the Logon triggers are not fired.
The following example shows how you can create a login trigger and send a message to the SQL Server error log as soon as any user logs in:
ALTER TRIGGER Ops_Login
ON ALL SERVER
PRINT SUSER_SNAME() + ' has just logged in to ' + LTRIM(@@ServerName) + ' SQL Server at '+LTRIM(getdate())
To view all the triggers set at the server level, use the following query:
SELECT * FROM sys.server_triggers;
Defragging Best Practices
Q What's the best way to fix data file fragmentation in SQL Server? If we use the defragmentation tools in Windows, they treat the SQL data file as a whole and will not defragment it granularly.
A You could back up the database and then restore it. If the space exists for a contiguous file, the database should then be written contiguously. That said, it is normally not worth the downtime to try to defrag the physical files. Typically there isn't much external fragmentation anyway. It's more helpful to regularly reindex your data to reduce the internal fragmentation as much as possible. This will maximize the effectiveness of the read-aheads and the amount of data that can be buffered.
The most important factors for efficient disk I/O are making sure the disk alignment and RAID configuration is correct, scaling your disk arrays to properly handle the I/O load, and maintaining proper layout of the Log, Data, TempDB, and backup files. If you avoid using auto-grow and auto-shrink as your primary method for sizing data files, you'll reduce the number of volume-level file fragments created. For instance, performing 10 auto-grows of 500MB each would probably add 10 new physical file fragments. In contrast, a single manual grow of 5GB will add only one.
Thanks to the following Microsoft IT pros for their technical expertise: Cary Gottesman, Saleem Hakani, Trayce Jordan, Peter Kalbach, Al Noel, Uttam Parui, Amber Sitko, and Buck Woody.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited