Running SQL Server Agent with a least privilege service account

Problem

For improved security Microsoft recommends the SQL Server Agent service account should not be a member of the local Administrators group. Being a member of the Administrator group, grants the account super-user privileges which therefore may expose you to more security vulnerabilities. By limiting access for the service accounts it will help you safeguard your system if individual services or processes are compromised.
Solution
As a best practice, SQL Server Agent service account rights should be kept as low as possible to prevent exposing your system to security risks. Making the SQL Server service account an administrator, at either a server level or a domain level, grants too many unneeded privileges and should never be done. Ideally, all the SQL Server services should run from a different account and each account should have exactly the privileges that it needs to do its job and no additional privileges.

During a new installation, SQL Server setup does not default the SQL Server engine service and SQL Server Agent service to any account. The account specification is a required step for these services. Using a local user or domain user that is not a Windows administrator is the best choice.

If the server that is running SQL Server is part of a domain and needs to access domain resources, such as file shares or uses linked server connections to other computers running SQL Server, a domain account should be used. If the server is not part of a domain, a local user that is not a Windows administrator is preferred.

The SQL Server Agent service account requires sysadmin privileges in the SQL Server instance that it is associated with. In this tip I have tried to put forth a solution by running SQL Server agent under group (SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER). This group has all the required privileges and is not part of the administrator group.

NOTE: When you install Microsoft SQL Server to run using a Microsoft Windows NT account, SQL Server sets various Windows user rights and permissions on certain files, folders, and registry keys for that account. If you later change the startup account for the SQL Server Agent service using SQL Server Configuration Manager, SQL Server automatically assigns all the required permissions and Windows user rights to the new account for you, so that you do not have to do anything else.

Steps to SQL Agent Account
STEP 1
Add the account under which you want to run the SQL Server agent service in the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group. (Right click my computer -> Manage -> Local Users and Groups -> Groups). Then find the group, right click on it and select Properties. This group is pre-configured with all the required permissions to run the SQL Agent service. Also, make sure the account you add to thsi group is not a member of the local administrator group. In this example I am adding "Agent test" to this group.


STEP 2
Change the log on account of the SQL Server Agent service in SQL Server Configuration Manager in SQL Server 2005. Use the account that you just added to the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group, in my case it is "Agent test". You may also consider doing it through services.msc, but it is recommended to do it using SQL Server Configuration Manager. The reason is, when you install SQL2005, a service master key for encryption is created. This key is then used to encrypt certificates and any other encryption keys. The service master key (SMK) is linked to the service account and changing this account can make the key invalid and then it can’t open the certificates anymore. When you change your service account through SQL Server Configuration Manager, SQL 2005 will take care of creating a SMK. By default, only members of the local administrators group can alter the service account, start, stop, pause, resume or restart a service.


STEP 3
Restart the SQL Server Agent service, so that the new account goes into affect.



STEP 4
You can check in SSMS that SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group is a member of sysadmin role. In SSMS, go to Security -> Sever Roles. Right click on "sysadmin" and select Properties to view the scrreen below. If it is not already there, follow the steps in step 4a.


STEP 4a - add login and role
If this group is not already part of the sysadmin role, follow these steps.

Add the group SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER as a Windows authenticated login in SQL Server


And then assign the sysadmin role to this login.




Now SQL Server agent is running under an account which is not a member of the local administrators group on the server.

Note: there is limitation for using multiserver administration when the SQL Server Agent service account is not a member of the local Administrators group. Enlisting target servers to a master server may fail with the following error message: "The enlist operation failed." To resolve this error, restart both the SQL Server and the SQL Server Agent services.

SQL Server Management Studio Keyboard Shortcuts

SQL Server Management Studio offers users two keyboard schemes. By default, it uses the Standard scheme, with keyboard shortcuts based on Microsoft Visual Studio. A second scheme, called SQL Server 2000, closely resembles the tools from SQL Server 2000, in particular the keyboard shortcuts from the Query Analyzer. In a few cases, SQL Server Management Studio cannot offer the keyboard shortcuts from Query Analyzer. To change the keyboard scheme or add additional keyboard shortcuts, on the Tools menu, click Options. Select the desired keyboard scheme on the Environment, Keyboard page.

Menu Activation Keyboard Shortcuts



Action
Standard
SQL Server 2000

Move to the SQL Server Management Studio menu bar
ALT
ALT

Activate the menu for a tool component
ALT+HYPHEN
ALT+HYPHEN

Display the context menu
SHIFT+F10
SHIFT+F10

Display the New File dialog box to create a file
CTRL+N
No equivalent

Display the New Project dialog box to create a new project
CTRL+SHIFT+N
CTRL+SHIFT+N

Display the Open File dialog box to open an existing file
CTRL+O
CTRL+SHIFT+INS

Display the Open Project dialog box to open an existing project
CTRL+SHIFT+O
No equivalent

Display the Add New Item dialog box to add a new file to the current project
CTRL+SHIFT+A
No equivalent

Display the Add Existing Item dialog box to add an existing file to the current project
ALT+SHIFT+A
No equivalent

Display the Query Designer
CTRL+SHIFT+Q
CTRL+SHIFT+Q

Close a menu or dialog box, canceling the action
ESC
ESC

SQL Server UDF to pad a string

Problem
Unlike other relational database management systems that shall remain nameless, SQL Server's underlying coding language, T/SQL, does not have a built-in function for padding string values. I recently took it upon myself to create my own and as you'll see I got a little carried away.

Solution
The task seems simple enough: create a user-defined function that will allow padding of a string value with a finite count of a desired character. What ended up as an experiment out of necessity became a slightly more-involved function once I decided that padding position should be customizable to meet the end user's needs.

While Transact-SQL (T/SQL) does not offer a comparable function similar to LPAD or RPAD available in other RDBMSs, the SQL Server Professional does have the REPLICATE() function that can be used to build a simple user-defined function that can be used to pad a string. Let's take a look at the REPLICATE() function and what it offers before moving on to the code for the custom padding function.

REPLICATE (string_expression ,integer_expression) will allow you to replicate a character string (the string_expression parameter the number of times consecutively per the integer_expression parameter).

A simple example of this function is presented below:

SELECT REPLICATE('ABCDE|', 3) ------------------ ABCDE|ABCDE|ABCDE| (1 row(s) affected)

Whereas the REPLICATE() function will allow you to return a string to a maximum size of 8000 bytes, the function I'll be creating will be based upon an output value of varchar(100). You will be able to modify this value to fit your needs, however I very rarely have a need to pad a string value greater than even 20 characters. I thought it worthwhile to create a single function for padding either to the left or right of the unpadded string. Then it became interesting, what if for some reason you wished to pad the center of the string? What about padding both the left and right sides of the string evenly? Whether you use those options or not, the functionality is there. The code below represents the function I've created.

CREATE FUNCTION [dbo].[usp_pad_string] ( @string_unpadded VARCHAR(100), @pad_char VARCHAR(1), @pad_count tinyint, @pad_pattern INT) RETURNS VARCHAR(100) AS BEGIN DECLARE @string_padded VARCHAR(100) SELECT @string_padded = CASE @pad_pattern WHEN 0 THEN REPLICATE(@pad_char, @pad_count) + @string_unpadded --pad left WHEN 1 THEN @string_unpadded + REPLICATE(@pad_char, @pad_count) --pad right WHEN 2 THEN --pad center LEFT(@string_unpadded, FLOOR(LEN(@string_unpadded)/2)) + REPLICATE(@pad_char, @pad_count) + RIGHT(@string_unpadded, LEN(@string_unpadded) - FLOOR(LEN(@string_unpadded)/2)) WHEN 3 THEN --pad edges REPLICATE(@pad_char, FLOOR(@pad_count/2)) + @string_unpadded + REPLICATE(@pad_char, @pad_count - FLOOR(@pad_count/2)) END RETURN @string_padded END

The function expects four parameters:

• @string_unpadded - the raw string value you wish to pad.
• @pad_char - the single character to pad the raw string.
• @pad_count - the amount of times to repeat the padding character
• @pad_pattern - an integer value that determines where to insert the pad character
  • 0 - all pad characters placed left of the raw string value (pad left)
  • 1 - all pad characters placed right of the raw string value (pad right)
  • 2 - all pad characters placed at the midpoint of the raw string value (pad center)
  • 3 - the raw string value will be centered between the pad characters (pad ends)

Notes

• If either of the length of the supplied @pad_count or @string_unpadded values are odd, centering will be affected. We will show that behavior in the samples presented below.
• Since the return value of the function is limited to 100 characters, the length of parameter corresponding to the unpadded string must be sized according to allow for padding. The length of the return value and this parameter are completely customizable to fit your needs.
• While the REPLICATE() function allows you to pad more than a single character, this function expects you'll only pad a single character.

Example 1 Evenly-Distributable Padding

--Even distribution possible --Pad Left SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 0) AS [padded string], '0 - pad LEFT' AS [pad pattern value]; --Pad Right SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 1) AS [padded string], '1 - pad RIGHT' AS [pad pattern value]; --Pad Center SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 2) AS [padded string], '2 - pad CENTER' AS [pad pattern value]; --Pad Edges SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 3) AS [padded string], '3 - pad EDGES' AS [pad pattern value];

The previous set of examples shows what results to expect when the parameters are evenly distributed. What happens though when this is not possible? This occurs under two situations: you are attempting to center-pad a raw string that has an odd number of characters or you're trying to pad an odd number of characters using the pad edges option (@pad_pattern = 3). I am not going to show you some method for splitting an odd string at a quantum level. Sorry, I am not all that brilliant as it is. Therefore I had to make a judgment call on how the behavior is going to occur using the structure of the three dimensions we have to work with here currently. The following examples will present how this function will behave.

Example 2 - Center-Pad an Odd-Length Raw String

--Pad Center SELECT '12345' AS [raw string], dbo.[usp_pad_string]('12345', 'X', 4, 2) AS [padded string], '2 - pad CENTER' AS [pad pattern value];

Example 3 - Center-Edges an Odd Number of Times

--Pad Edges SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 5, 3) AS [padded string], '3 - pad EDGES' AS [pad pattern value];

As you can see, the padding will be right-heavy in both cases. Though I doubt you'll have much use for center or edge-padding strings in Transact-SQL, you never really know.

Padding is a frequent need in returning string results to the end user of a RDBMS. This function allows you to do so as if the functionality existed inherently in T-SQL.

SQL Server Cursor Examples

Here's some Examples on SQL Server Cursor

Problem
In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft created them for a reason so they must have a place where they can be used in an efficient manner.

Solution
In some circles cursors are never used, in others they are a last resort and in other groups they are used regularly. In each of these camps they have different reasons for their stand on cursor usage. Regardless of your stand on cursors they probably have a place in particular circumstances and not in others. So it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor based processing is appropriate or not. To get started let's do the following:

• Look at an example cursor
• Break down the components of the cursor
• Provide additional cursor examples
• Analyze the pros and cons of cursor usage

Example Cursor

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

Cursor Components

Based on the example above, cursors include these components:

• DECLARE statements - Declare variables used in the code block
• SET\SELECT statements - Initialize the variables to a specific value
• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
  • NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
• OPEN statement - Open the cursor to begin data processing
• FETCH NEXT statements - Assign the specific values from the cursor to the variables
  • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement - Condition to begin and continue data processing
• BEGIN...END statement - Start and end of the code block
  • NOTE - Based on the data processing multiple BEGIN...END statements can be used
• Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement - Destroys the cursor

Additional Cursor Examples

In the example above backups are issued via a cursor, check out these other tips that leverage cursor based logic:

• Managing SQL Server Database Fragmentation
• SQL Server script to rebuild all indexes for all tables and all databases
• SQL Server Index Analysis Script for All Indexes on All Tables
• Standardize your SQL Server data with this text lookup and replace function
• Automating Transaction Log Backups for All SQL Server Databases
• Searching and finding a string value in all columns in a SQL Server table
• Scripting SQL Server Database Objects Using DMO (Distributed Management Objects)
• Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server
• Capacity Planning for SQL Server 2000 Database Storage
• Automate Restoration of Log Shipping Databases for Failover in SQL Server 2000
• Determining space used for each table in a SQL Server database
• Auditing Windows Groups from SQL Server
• SQL Server Find and Replace Values in All Tables and All Text Columns
• Easing the SQL Server Database Capacity Planning Burden
• Index Metadata and Statistics Update Date for SQL Server

Cursor Analysis

The analysis below is intended to serve as insight into various scenarios where cursor based logic may or may not be beneficial:

• Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions. Our team has run into a third party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence. Typically, SET based logic is more than feasible and cursors are rarely needed.
• Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed. However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values. We have had the same experience when needing to aggregate data for downstream processes, a cursor based approach was quick to develop and performed in an acceptable manner to meet the need.
• Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
• Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor based logic, but other system based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
• Large data sets - With large data sets you could run into any one or more of the following:
  • Cursor based logic may not scale to meet the processing needs.
  • With large set based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor based approach may meet the need.
  • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
  • If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
  • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
  • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a checkpoint or marking each row with the cursor. However, with a set based approach that may not be the case until an entire set of data is completed. As such, troubleshooting the row with the problem may be more difficult.

Cursor Alternatives

Below outlines alternatives to cursor based logic which could meet the same needs:

• Set based logic
• SQL Server Integration Services (SSIS) or Data Transformation Services (SSIS)
• WHILE loop
• COALSCE
  • The Many Uses of Coalesce in SQL Server
• sp_MSforeachdb
  • Run The Same SQL Command Against All SQL Server Databases
• sp_MSforeachtable
  • Here is a tip with example code - http://www.mssqltips.com/tip.asp?tip=1274
• CASE expression
  • Using the CASE expression instead of dynamic SQL in SQL Server
• Repeat a batch with the GO command
  • Executing a T-SQL batch multiple times using GO

SQL Server user defined function to convert MSDB integer value to time value

Problem
In a recent tip I outlined a process for converting a date, stored as an integer into a datetime data type. Date and time information for run history of SQL Server Agent jobs is stored within the msdb..sysjobshistory table as an integer data type, not as a datetime as one would expect. Most likely for at least two reasons:

• This structure is a legacy implementation from the earliest days of SQL Server
• The values are stored in separate run_date and run_time columns and until SQL Server 2008 there was not a time data type per se

As promised, this tip picks up where we left off. On converting the integer-typed run_time into a format that is more user friendly for presentation purposes.

We will still be using the same metadata repository for the SQL Server instances I administer. From the previous tip, you may remember that one of the metrics I track is based upon Job History success and failure. This information comes directly from the msdb..sysjobhistory table that resides upon each SQL Server instance.

Solution
Let's take a look again at a simple query against the msdb..sysjobshistory and msdb..sysjobs tables that hold the data we're interested in:

sysJobHistory and sysJobs Data

SELECT SJ.name, SJH.run_date, SJH.run_time FROM msdb.dbo.sysjobhistory SJH INNER JOIN msdb.dbo.sysjobs SJ ON SJH.job_id = SJ.job_id WHERE SJH.step_id = 0 ORDER BY SJ.name GO

We have already covered how to convert run_date (as integer) to a datetime data type that can then be used in date calculations such as DATEADD(), DATEDIFF(), DATENAME() or DATEPART(). The purpose of this tip is to convert the run_time value (stored as an integer data type) into a format that is more presentable for end users. This can be accomplished in two manners, both outlined below. The later is a standalone user-defined function (UDF) the second takes into consideration an additional UDF outlined recently in a tip on padding string values. Reliance on this second UDF reduces the amount of code necessary. I'll be presenting the actual query execution plan for a simple query using each process so you can determine which option is the best for your environment.

As a point of reference, the msdb..sysjobhistory.run_time values are stored as an integer, in the pattern of hhmmss. Unfortunately for us, since this is an integer value, single digit values do not include a preceding zero (9:00 am for example is stored as 900, midnight as simply 0).

Option One - Standalone UDF

Let's take a look at our first option to address this problem which is a standalone UDF.

Option One: The Standalone UDF

CREATE FUNCTION dbo.udf_convert_int_time_1 (@time_in INT) RETURNS VARCHAR(8) AS BEGIN DECLARE @time_out VARCHAR(8) SELECT @time_out = CASE LEN(@time_in) WHEN 6 THEN LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 3,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) WHEN 5 THEN '0' + LEFT(CAST(@time_in AS VARCHAR(6)),1) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 2,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) WHEN 4 THEN '00' + ':' + LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) ELSE '00:00:00' --midnight END --AS converted_time RETURN @time_out END GO

The function accepts a single parameter, the integer data type time value passed to it. Depending on the length of the parameter, the output value is formatted accordingly and the result is returned. Revising the first query in this tip to include the results of this UDF applied to each record is presented below along with it's associated output and actual execution plan.

Sample Execution

SELECT SJ.[name], SJH.[run_date], SJH.[run_time], dbo.udf_convert_int_time_1(SJH.[run_time]) AS run_time_1 FROM msdb.dbo.[sysjobhistory] SJH INNER JOIN [msdb].dbo.[sysjobs] SJ ON SJH.[job_id] = SJ.[job_id] WHERE SJH.[step_id] = 0 ORDER BY SJ.[name] GO

Option Two: Reliance on Padding of Input Variable

Recently, I published a tip on MSSQLTips.com on padding string values in Microsoft SQL Server. We will be using the UDF presented in that tip to simplify the code presented above. Afterwards we'll see what possible effect that has on performance by comparing the actual execution plan against the execution plan for the first iteration of the UDF shown above.

Option Two: Reliance on Padding of Input Variable

CREATE FUNCTION dbo.udf_convert_int_time_1 (@time_in INT) RETURNS VARCHAR(8) AS BEGIN DECLARE @time_out VARCHAR(8) SELECT @time_out = CASE LEN(@time_in) WHEN 6 THEN LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 3,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) WHEN 5 THEN '0' + LEFT(CAST(@time_in AS VARCHAR(6)),1) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 2,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) WHEN 4 THEN '00' + ':' + LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) ELSE '00:00:00' --midnight END --AS converted_time RETURN @time_out END GO

By utilizing the usp_pad_string() function we can eliminate the CASE code structure from the dbo.udf_convert_int_time_2 UDF. The padding function expects four parameters: the string value to pad, the padding character, the number of instances to apply the pad, and the padding placement. Please review the full structure of the usp_pad_string UDF in the original article. It will pad the integer value so that further processing can be consistently applied without concern for length. Running a comparable query to the one previously presented returns the following results and execution plan.

Sample Execution

SELECT SJ.[name], SJH.[run_date], SJH.[run_time], dbo.udf_convert_int_time_2(SJH.[run_time]) AS run_time_2 FROM msdb.dbo.[sysjobhistory] SJH INNER JOIN [msdb].dbo.[sysjobs] SJ ON SJH.[job_id] = SJ.[job_id] WHERE SJH.[step_id] = 0 ORDER BY SJ.[name] GO

The additional function call to the padding UDF has no apparent additional overhead on the execution of the query. Which option you choose would be up to you, dependent upon your preference for UDF reliance and embedding of UDFs. Ultimately this process is based upon conversion of time-of-day data into a presentable format. While the CONVERT() function is capable of converting string values to presentable formats when passed datetime values, there is no functionality for time-only values.

Deleting Data in SQL Server with TRUNCATE vs DELETE commands

Problem
There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although each achieves the same result, the methods employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use.

Solution
Deleting Data Using TRUNCATE TABLE

TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.

In addition, TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.
TRUNCATE TABLE cannot used be used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers. This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. If all table rows need to be deleted and there is a foreign key referencing the table, you must drop the index and recreate it. If a TRUNCATE TABLE statement is issued against a table that has foreign key references, the following error is returned:

Deleting Data Using DELETE FROM Statement

DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary. You can also specify a WHERE clause to narrow down the rows to be deleted. When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the empty pages requiring manual release using DBCC SHRINKDATABASE (db_name).
When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following statements can be used to achieve the same result as TRUNCATE TABLE:

• DELETE from "table_name"
• DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

SQL Server Clustering and Consolidation - Balancing Resources

Problem
In my SQL Server environment we initially clustered to support a consolidation effort in which we were focusing on elimination of approximately thirteen Microsoft SQL Server 2000 and 2005 instances. Along the way we had a mission critical application that was backed by SQL 2000 that was budgeted without hardware for supporting a dedicated SQL Server instance. We were forced into hosting this database on our relatively new SQL 2005 Enterprise cluster as a result. Our consolidation effort essentially ended on the day that decision was made. I've finally been able to make the case that this application needs a dedicated SQL environment. What started as a single database for supporting this product has morphed into an eight-database suite that, through design and support, requires more than its share of system (and Database Administrator) resources because of where it is hosted.

Just because you have disk space, plenty of memory, and abundant CPU does not mean you can pile databases onto an instance of Microsoft SQL Server; you need to pay attention to load balancing. In the case of a clustered environment I take it one-step-removed: a step I like to call node balancing.

Author's Note: This is part three of a continuing series on Microsoft SQL Server 2005 Clustering. Some of the terminology used in this tip and future clustering tips in the series were defined and explained in parts 1 and 2. While parts 1 and 2 are not pre-requisites for this tip it is advised that you also read those tips in this series (and any future tips as well!)

Solution

Node Balancing

Microsoft Clustering Services are not designed for load balancing, but rather failover in the event of hardware failure. Therefore when I play off the concept of load balancing what I am actually referring to is the process by which I make a determination of which databases to host on my various SQL Server instances. I base this determination on a number of factors:

• Relative importance of the application to the entity
• Amount of resources required by the database(s) that support this application
• Level of access required by the support staff (possibly Third-Party Vendor)
• Architectural considerations
• Collation requirements

Each one of these factors may determine how I formulate my architectural recommendations for hosting any particular database that is a candidate for clustering. However it is usually a combination of factors that determine why I may choose to host a database or set of databases on their own cluster, own active node in a cluster, or on a shared node with multiple non-related databases. I detail each of these factors next with recommendations based upon my experience for each.

Relative Importance of the Application

There are two distinct reasons for clustering Microsoft SQL Server: consolidation and fail-over. Consolidation will allow you to host multiple databases together on a single SQL instance up to a point where the resources of the server can no longer support additional database activity. Clustering for failover allows you to maintain an actively-accessible database instance in the event a node in the cluster encounters stress or damage and a shutdown occurs. Databases that are considered "mission critical" should be hosted in such a way that affords high-availability. For the purposes of this tip we will state that the solution we're recommending for that purpose is failover clustering. Conversely, databases that don't meet the requirements of being "mission critical" do not necessarily need to be available if a hardware failure is encountered. They may be able to be down for a short period of time, perhaps even for days depending upon their usage patterns. These databases may be hosted on the cluster only for the sake of consolidating multiple instances worth of databases on a more powerful clustered instance of SQL Server. More powerful? Certainly. If you're going to take advantage of clustering I strongly suggest that you do so based upon the 64-bit platform and with Enterprise Edition SQL Server. The limitations against Standard Edition SQL (no online indexing, limitation on only two nodes in a cluster) make it a less-expensive, yet less scalable solution.

For the basis of this tip I will break down the database classes, based upon criticality into three tiers. These are the classifications we use in our company and it works well for us (so far).

• Tier 1 - Your company's most-important databases. You may perhaps have only one of these - maybe as many as a half dozen. These are the systems that keep your company running or service your customers with their critical data requirements: databases providing data related to Patient Care, Online Banking, or Criminal History information would be candidates for this class of databases.
• Tier 2 - These are the databases that host data for applications that, while important, may allow some appreciable measure of downtime. Payroll , Educational Registration, Annual Employee Performance Review programs, and E-Commerce sites would fall into this category.
• Tier 3 - We all have plenty of these in our environments. These are the databases that get created because someone in accounting wanted to "upsize their Excel Database". These databases typically support department-level internal applications that run during business hours and can be down for periods of time with effect only on productivity of a select group on individuals.

Let's take care of the easy choice first: Tier 3 databases should be hosted together. There, that was easy. Seriously, we do not need to dwell on the Tier 3 databases, we need to focus our attention on answering the following questions:

• Should Tier 1 databases be hosted on a dedicated node in shared cluster or should they be hosted in their own active-passive cluster?

I've advocated both sides of this debate at different points in time. By placing the Tier 1 database on a dedicated node within a larger, shared cluster you still reap the benefits of clustering, with only a single additional server to manage. (So long as there is already a passive node in the cluster.) If you were to dedicate a complete cluster for the sole purpose of hosting this database you must add at least two new servers to your environment along with all the maintenance and administration that entails - not only for you, but for the multitude of teams and staff that are responsible for supporting a server in today's IT environments. One should also not forget the added costs associated with yet another passive node (hopefully) just sitting idle in your data center. Conversely, what happens if this database or one of the others in the cluster require a patch or configuration change at the server level? In a clustered architecture this requires all nodes in the cluster to be patched regardless of whether the database is currently being hosted on the server. You need to be prepared for a failover situation and this requires that all nodes in the cluster be identical in content and configuration from a software perspective. Therefore you may find yourself in a situation where a Tier 2 or Tier 3 database causes compatibility issues for a Tier 1 database in your shared cluster - even if the Tier 1 database is hosted on its own node.

Recommendation: If you have the funding, Tier 1 databases, those that are most-critical to the well being of your company and customers, should be hosted on their own cluster. Furthermore, it is recommended that you also provide additional redundancies to protect against media (disk) corruption on the SAN volumes assigned to the database via third-party SAN replication tools or by native SQL technologies such as replication, database mirroring, or log shipping.

• Should Tier 2 databases be hosted on the same node as Tier 3 databases or should they be on their own dedicated node in a shared cluster?

Just because a database may be classified as Tier 2 using the definitions I've presented does not make it unimportant. However, they seldom would require their own dedicated cluster unless you're company manufactures money or has worked out that whole alchemy process of turning rocks to gold. These databases would need to compete for resources equally against less-important (and possibly more intrusive databases) if hosted on the same node with Tier 3 databases so in most situations that architecture is not advised either. This is where you need to also take other factors such as resource requirements and activity trends into consideration in attempts of balancing a node for support of perhaps one or more Tier 2 databases.

Recommendation: if funding permits host Tier 2 databases on their own node in an shared cluster environment. If resources allow, and your benchmarking proves that multiple Tier 2 databases can co-exist on the same node, host those databases together. Considerations need to be taken for any database that is hosted on a shared Microsoft SQL Server instance. Failovers, maintenance, and downtimes will impact all databases on the instance.

Amount of Resources Required by The Database

I support hundreds of databases in my environment. We have very large databases that have very little activity. We have relatively small databases that have hundreds of concurrent static connections that remain open all day long. We have a little bit of everything in between. What may be the most consuming task for a Database Administrator is determining just what usage patterns each of your databases have. The process for collecting this information is outside the scope of this tip, but I'll make suggestions for resources at the end of this tip on how to determine load over time for a specific database. Once you've collected this data, the goal is to balance out the resource requirements across all your databases you plan on hosting on a given instance. What makes this process extremely difficult is that some items that impact resources greatly may be out of your control - even as the Database Administrator. A properly-indexed INSERT query may run in fractions of a second, however that same query, with inadequate indexes or out-of-date statistics, may run for minutes or worse. If this is a database that was developed in-house you may have the ability and rights to correct the issue by adding or altering the indexes. If this is a commercially-developed database your company purchased you may have the ability to make similar changes, but may be precluded from doing so by the nature of an existing support agreement with the vendor. There is also the impact of application life cycles. Your company comes to rely more (or perhaps less if the case may be) on a database over time. This means your initial performance monitoring results used to determine how and where to host a given database may become outdated. The information you collect over a sampling period may not be indicative of the database activity or impact later in your production environment.

This is the grayest of gray areas - a sliding scale of metadata - one of the leading causes of premature baldness, depression, and substance abuse by Database Administrators. Unfortunately it is also an important process in determining the hosting structure for your databases. I highly recommend rounding up whenever possible for the sole reason that it is better to have excess resources sitting idle on your database servers than finding yourself in need of resources when your databases need it most.

Recommendation: Use SQL Server Performance Monitor and Profiler to monitor activity against candidate databases for clustering to best determine high and low points in activity throughout a typical week in order to find databases that may be compatible for co-hosting on a shared SQL Server instance in your enterprise's cluster.

Level of Access Required by Support Staff/Vendor

So we move from difficult to easy. This really is a simple line-in-the-sand for most Database Administrators. On one side of the line: Database Role Rights. The other side of the line: Server Role Rights. If the support team (be they internal or third-party) is agreeable to having their permissions to a SQL instance capped at Database role rights (db-owner role rights or less) then this makes the database a candidate to be hosted on a shared SQL instance. It is also quite common that a support team would need to have certain role permissions in msdb to support any jobs associated with their database(s). I would still consider this a candidate for a shared database environment. However, if the vendor or support team mandates that they have any level of Server Role membership (System Administrator, Database Creator, Security Administrator, etc.) then they are going to be isolated to a dedicated SQL instance. The reason is that it is the primary responsibility of the Database Administrator to isolate access to only those individuals, teams, or entities that have legal, ethical, or commercial rights on any given database. If you allow a vendor for database X to have server-level permissions on a SQL instance they could conceivably affect every other database on that instance.

Recommendation: If you can not limit non-DBA access to database-only rights, and server-level permissions, role rights, or securables are required by the application support staff then the database(s) in question should be on a dedicated node within your cluster.

Architectural Considerations

This category exists for only a single question: is there a mandate, by either the structure of the application and database or by support agreements, that the application and the database must reside on the same physical server? If so, not only should you not look at clustering for this solution, but you should definitely plan to dedicate an instance to just the database(s) that support this application.

If the program architecture requires the database to reside on the same physical server as the application, web, or other non-SQL Server components then do not host the database within your cluster. Instead dedicate a standalone SQL instance for the database(s) and provide redundancy/high-availability by other means such as replication, database mirroring, or log shipping.

Collation Requirements

Granted, in SQL Server 2005, the collation settings for the databases can be different from the collations for the system databases. However, it is critical that you realize that there are implications when an vendor mandates their databases run under a specific non-standard collation. Typically the issue is tempdb. Think of all the processing that occurs in tempdb: this is where temporary objects are created for all databases such as temp tables, table variables and cursors. Work tables used by SQL Server to internally sort data are created in tempdb. Issues arise when tempdb is a different collation than the user database and results of computations may not match expectations. My suggestion when non-standard collations are specified is to ask if this is a requirement or a preference. If a requirement ask for specific reasons why this collation is required. (You'll be shocked how many times vendors will erroneously cite "That's what Microsoft's documentation states!") If a non-standard collation is required then you're looking at a dedicated instance of SQL Server. I recently had to rebuild a cluster node only because I did not use the default collation for SQL 2005, but rather selected the same criteria piecemeal and built my own collation. Since it did not match in name to "SQL_Latin1_CI_AS", scripts in a single database running alongside 40 other databases failed a validation check.

Recommendation: If a non-standard collation is required it does not mean you can not host the database in the existing cluster, however it may require you to dedicate an instance of SQL only for this database. You may be able to set the minimum and maximum memory requirements for this instance low enough to reside on an existing node with another SQL Server instance depending upon the required resources for the database in question. You may need to dedicate a node for the SQL instance if you find resource contention between this instance and any other instances running on the same cluster node. It is best to determine if the non-standard collation is a requirement or a request.