Configuring SQL Server Maintenance Plan

I may not have explicitly mentioned earlier, but all the examples, and the content that I write generally applies to SQL Server 2008. In most cases it will be true for SQL Server 2005 as well, but not always. For example, in one of the blog about compressing data during backup, I discussed "WITH COMPRESSION" command to use during backup. This command was only introduced with SQL Server 2008.

Today, we will review another facility available in SQL 2008 (also available in SQL 2005, but some of the features may not be the same) called Maintenance Plan. Maintenance Plan allows you to setup certain maintenance tasks by using SQL Management Studio with ease. This comes specially handy when you have multiple databases on the same server and need to setup maintenance plan such as nightly backup for all the databases at once.

1. Connect to your SQL instance via Management Studio and go to Management > Maintenance Plans.

2. Right click on Maintenance Plan and either select New Maintenance Plan or Maintenance Plan Wizard. Maintenance Plan Wizard is quite powerful and walks you through selecting available maintenance plans and configure it. In this example, we will use the first option and setup our own maintenance plan. Select "New Maintenance Plan" and name your plan appropriately, which will open design surface and a list of available maintenance plan.

3. Drag one or more tasks that you want to configure in this maintenance plan. We will configure two tasks - database backup and check database integrity task.

Check Database Integrity

It is often a good idea to check the database integrity immediately before or after the backup.

Drag the task on the design surface and then right click > Edit to configure your databases. The resulting UI will allow you to select one or more databases. You can also check all databases (this is helpful if you are going to add databases in future and don't want to keep adding new databases to this list, alternatively if you have too many databases, you may want to configure few databases in one task).

Check the "Ignore databases where state is not online" to only perform integrity check when database is online. Click on OK to save the change. 

From the design surface top menu, click on the calendar icon to setup the schedule for this task. Name your schedule and setup appropriate schedule. Clock on OK to save and close the schedule window. 

Configure Database Backup

Repeat the same steps and drag the database backup task and configure it. Again right click on the task and configure your options. Here you can configure whether you want full, differential or transaction log backup, select one or more databases and define the location where you want to save the backup files. There is also an option called "Verify Backup Integrity" this shouldn't be confused with Database Integrity task which we configure in above step.

In this example, we want to ensure the integrity check is performed first before the database backup. Right click on either of the two task and select "Add Precedence Constraint" from the context menu and select the precedence you want.

That's all there is to it. Once you save your plan, SQL Server will automatically create a SQL Agent job to run your plan at the scheduled time.

If I had database mail setup, I can also add a third task "Notify Operator Task" as the last step which can send a notification email in the event of the task failure. See my previous post to learn about configure your SQL Server to send emails.

Thank you.

Database Normalization - Second Normal Form

In previous post we discussed the concept of Normalization and First Normal Form. Today, we will review Second Normal Form. You may want to refresh previous post before continuing on.

Second Normal Form (2NF)

2NF takes this concept  a bit further. This rule states that any data that is in multiple rows of the same table should be moved to a new table and the two tables should be joined via a foreign key. Basically, the idea is to reduce data redundancy by extracting redundant data and moving it elsewhere. Let's imagine you have a customers table that has the following columns

Customers

• CustomerID
• FirstName
• LastName
• Address
• City
• State
• PostalCode
• Country

Sure, you will have more than one customer from the same city, even more from the same state and definitely more from the same country. You may possibly have many customers from the same postal (zip) code. As you enter customers in this table, you are duplicating all this data i.e. City, State, PostalCode, Country etc. This table is not in 2NF. To make it 2NF compliant, create a new table and store City, State, PostalCode, Country. Let's call this table Addresses. This table has the following columns

Addresses

• City
• State
• PostalCode
• Country

What columns do you think could be redundant here? Sure PostalCode won't be, but what about City? A city could have multiple postal codes? Definitely a State can have multiple cities and Country will have multiple states. A good schema for all the tables may be the following...

Customers

• CustomerID
• StreetAddress
• CityID

CityAddress

• CityID
• City
• PostalCode
• StateID
• CountryID

States

• StateID
• State

Countries

• CountryID
• Country

Notice, one table resulted in 4 different tables. It is definitely more complex but more flexible. You can even pre-populate States and Countries in advance.

This schema also satisfies second rule of 2NF i.e. the related tables should be related by foreign key. CityID is a foreign key in Customers, StateID, CountryID are foreign keys in CityAddress table.

We will discuss 3NF in future post.

Thank you.

Query Execution - Determining CPU and I/O Usage

In previous post we discussed SQL Query Execution plan and how you can anlayze the execution plan, determine the steps that take the longest time to run and possibly optimize your query. We will take this process further and discuss disk access and CPU time and how you can analyze it.

As you already know, the data is stored on physical disks and every time you run a query, the data is either retrieved from the disk or saved to the disk. To reduce the read time from disk, SQL Server caches data. When data is read from the cache, it is called logical read; when it is read from the physical drive, it is physical read. Disk I/O operations are probably slowest of all operations and depend largely on the hard drive speed, latency etc.

So, how do you determine what resources are being used by your query and how you may be able to minimize the resource usage, increasing the performance.

SQL Server provides two commands "SET STATISTICS IO ON" and SET STATISTICS TIME ON" that you can turn on to determine the I/O and CPU usage when you run a query.

I recently downloaded and installed AdventureWorks database for SQL Server 2008 r2. You can download and install it in your test environment to test these examples. Here is the download link.

USE AdventureWorksDW
GO
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SELECT * FROM DimCustomer

When you turn on I/O and TIME statistics and execute the query like we did above, you will see something like this under messages tab

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(18484 row(s) affected)
Table 'DimCustomer'. Scan count 1, logical reads 1002, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 951 ms.

This is my test bed with no load, but these numbers will be different in production environment and will vary depending on the load on the server.

Let's understand what these numbers mean...

1. SET STATISTICS TIME ON


There are two numbers here -

SQL Server Parse and Compile time:- These numbers tell us how much CPU time it took to parse your    query and compile it and also the time elapsed since your query started running. Stored Procedures are generally compiled and subsequent executions run the previously compiled version. As such, 1st run of a stored procedure will have some CPU time here, but subsequent runs should show CPU time = 0 or very negligible.

SQL Server Execution Times: - CPU time is the time it took for your query to execute and should be relatively constant, regardless of whether your server is busy or not. The elapsed time will change depending on the server load. If CPU time is really long, then you know you've got a problem and should be looking to fine tune the query.

2. SET STATISTICS IO ON
There are several bits of information here, some of which may be helpful if you are looking to fine tune your query. Let's review them.

Scan Count - This is an indicator telling you how many times the table(s) that are part of your query were scanned. If your query only accesses one table, the scan count should be one. If you have joins, then there should generally be one scan per table like the example below...

SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SELECT * FROM SalesLT.Customer C INNER JOIN 
SalesLT.CustomerAddress A ON C.CustomerID=A.CustomerID

Here I am joining two tables and I should expect two scan counts, one for each table and indeed that is the case...

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 3 ms.

(417 row(s) affected)
Table 'CustomerAddress'. Scan count 1, logical reads 6, physical reads 1, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 191 ms.

If your query is scanning the same table more than once, you probably should take a closer look at your query.

Logical Reads
This is probably the most crucial piece of information. As you probably know, SQL Server only reads the data from its cache and it also reads the data in 8K pages. Thus, each logical read reads 8K data page and the number of logical reads determine how many data pages SQL must read to serve up the query. More the number of reads, greater the execution time and resource consumption. Logical reads are usually constant when a query is run multiple times provided it returns the same result set each time.

Physical Reads
As I mentioned earlier, SQL Server only reads the data form its cache. But before the data can be read from the cache, it must be read from physical disk and moved to cache. This process is called physical read. When SQL Server starts executing a query, it first checks to see if data pages it needs are present in data cache. If not, then SQL Server retrieves the data from the disk, one data page per read (8K page) into the data cache. Physical read is lot more slower but if you fine tune your query to reduce the number of logical reads, you will also reduce the number of physical reads.Also, having more memory on your server will mean more data can be cached, hence less physical reads.

Read Ahead Reads
SQL Server tries to read the data from physical drive ahead of time to ensure that the data is available in cache before your query needs it. Your query may or may not use this data.

Let's consider an example:
Suppose when you execute a query, SQL Server determines that you need 20 data pages. It then checks to see how many of these 20 data pages are in cache. Let's assume 4 data pages were in cache. SQL Server while reading the data pages from cache, will also start reading data ahead of time from physical drive. Let's now assume that it reads ahead 15 pages. Now a total of 19 pages are in cache, 1 is still missing. SQL Server will then read the missing data page as physical read and move it to cache. Once all the datapages are in cache, SQL Server will process your query.

Query Tuning is a big topic and there are several important aspects of query tuning. CPU and I/O information is just one piece of information that you can use to determine your query's resource usage and possibly fine tune it.

Thank you.

Managing TempDB database to manage performance

Recently I came across a situation where SQL Server would perform remarkably well for couple of days and then become sluggish to virtually non-responsive. A simple reboot of the server would resolve the issue for the next few days.

Although there were many issues with this database, one thing that jumped out was how the TempDB was configured. First let's discuss TempDB and the crucial role it plays in RDBMS.

TempDB is just like any other database with some caveats. It is globally available to all the users and is used for temporary or transitory storage of data and objects. For example, when you create temporary tables, temporary stored procedures, variables or cursors, they all are stored in TempDB. When you create a cursor for example, you can provide a STATIC hint which will copy the content in TempDB and then read from their.

SQL Server also uses TempDB to store internal objects. If you are using row versioning or snapshot isolation level, the versions are copied into TempDB. Work tables that are creating during sorting etc. are also stored in TempDB. Online indexing operations also store temporary resulset in TempDB database.

Needless to say, TempDB is your work horse and is extremely crucial for SQL Server to perform at optimal level. Everytime you restart your SQL Server, TempDB is automatically created using a clean copy. Any temporarily stored objects are dropped. You also cannot perform backup and restore operations on TempDB.

Now back to the problem this particular SQL Server was experiencing.

• Initial size of the TempDB data file (mdf) was set to 8 MB (default) and it was set to autogrow by 10%. In a high transaction environment this will result in TempDB trying to autogrow too often and too little. First time growth will be 10% of 8 MB, rougly 800 K. Since the growth increments are so small, once TempDB reaches 8 MB limit, it will be autgrowing often, resulting in data fragmentation. Once you restart the server, TempDB will be reset and start from scratch and repeat the same process again once it reaches 8 MB.
• TempDB was on the same disk that had all other databases. While this in itself shouldn't be a problem, but imagine 50 or so databases (as was the case here) all set to autogrow by 10% and all fighting for disk space, resulting in a lot of disk fragementation.

As I stated before, there were obviously other issues with this environment, but today we will mainly focus on TempDB.

One of the ways you could alleviate problem like this is by allocating a dedicated hard-drive for TempDB if possible. Start off with sufficient initial size based on your environment. This will vary from environment to environment. Allocate too much space and you are practically wasting space, allocate too little and TempDB is trying to grow more frequently.

In a high transaction environment, you can also distribute the data load into more than one data files. You can create secondary data files (it is recommended to use .ndf extension) and even distribute secondary data files on multiple hard drives (although drive latency may come into play here, so this is generally not recommended).

To change the initial size or autogrowth ratio (10% is sufficient if you start off with big enough initial size) or to add secondary data files, right click on TempDB > Properties > Files via Management Studio and then click on Add button to create additional files.

When adding a file, you can define file location, initial size etc.  You can also move TempDB files to a different location if you are running out of space on the hard drive or if you want to separate them from other databases. Remember, the current files will not move until you restart SQL Server, although new data/logs will be written at the new locaiton.

Moving TempDB files

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempDB, FILENAME = '{new location}\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{new location}\templog.ldf');
GO

TempDB plays a big part in SQL Server performance and keeping your TempDB healthy is one of the crucial ways you can keep your SQL Server performing as expected.

Thank you

FTP Files using SQL Server

In previous post we discussed BCP command and how it can be used to generate a csv file to a specified location. Today, we will see how you can FTP file created via BCP command to a given FTP Server. If you haven't done so, I suggest you review the previous post as we will build on that.

Note, this nice little stored procedure was originally developed by Nigel Rivett, although I have modified it a bit to better explain it here.

Previously, we created a file called "payroll.csv" in C:\Temp\Folder. Today, we will FTP this file to an FTP Server.

Lets create a stored procedure which will accept FTP parameters, file to FTP and then use xp_cmdshell to ftp the file.

CREATE PROCEDURE [dbo].[sp_FTP]
    @ServerName      varchar(50) ,
    @UserName        varchar(50) ,
    @Password        varchar(50) ,
    @FilePath        varchar(255) ,
    @FileName        varchar(255) ,
    @SourcePath      varchar(255) ,
    @SourceFile      varchar(255) ,
    @WorkingDir      varchar(255),
    @CommandFile     varchar(50)
AS

DECLARE @SQL varchar(1000)

--Since we want to echo the output, we need to replace some special characters
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

SELECT @SQL= 'echo ' + 'open ' + @ServerName + ' > ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + @UserName + '>> ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + @Password + '>> ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FilePath + @FileName + ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + 'quit' + ' >> ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @cmd = 'ftp -s:' + @WorkingDir + @CommandFile

CREATE TABLE #tempTable (ID INT IDENTITY(1,1), [Command] varchar(1000))
insert #tempTable
exec master..xp_cmdshell @SQL

select ID, ouputtmp = [Command] from #tempTable

@CommandFile parameter accepts a text file that will have the FTP command to execute

For example

open myftp.ftp.com
testuser
testpass
put c:\temp\payroll.csv payroll.csv
quit

Remember, xp_cmdshell is disabled by default. You can enable it by running the following script.

RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

You can call this stored procedure in this way...

EXEC sp_FTP
    @ServerName = 'myftp.ftp.com' ,
    @UserName   = 'testuser' ,
    @Password   = 'testpass' ,
    @FilePath   = '' ,
    @FileName   = 'payroll.csv' ,
    @SourcePath = 'C:\temp\' ,
    @SourceFile = 'payroll.csv' ,
    @WorkingDir = 'C:\temp\',
    @CommandFile = 'FTPCommand.txt'

Although not ideal, this is a nice little feature that you can use via xp_cmdshell to FTP files.

Thank you.

SQL Server Locking Hints

                                                                SQL Server Locks - Part I, Part II, Part III, Part IV

We have been discussing locking mechanism in SQL Server since last three posts. In previous posts we covered locks, deadlocks and isolation levels. Today we will discuss ways to control how SQL Server locks the data by passing locking hints.

Generally leaving SQL Server engine to handle locks works as expected, but there are circumstances where you may want to use locking hints to lock or not lock certain transaction.

Locking Hints

• HOLDLOCK- When you pass this hint to your select queries, shared locks are held until the entire transaction completes instead of releasing the lock as soon as the read is complete and the required lock (table, row or page) is no longer needed. HOLDLOCK is same as SERIALIZABLE isolation level.
• NOLOCK - When you pass this hint, SQL doesn't issue shared locks for reading data and doesn't honor exclusive locks. In other words, if there is an exclusive lock on row, page or table, query with NOLOCK hint will still execute instead of waiting for exclusive lock to be released. With this hint, dirty reads are possible. This hint only applies to SELECT statements.
• PAGLOCK - Forces SQL Server to issue page lock instead of a default table lock that may be taken.
• READCOMMITTED -Same as READ COMMITTED isolation level, i.e. only read committed data.
• READPAST - When reading data, if a row is exclusively locked, read past that row, i.e. any locked rows are skipped. Beware, your results may not have all the data if you use this hint. Also, you can only use this hint if isolation level is set at READ COMMITTED. It also only applies to SELECT.
• READUNCOMMITTED - Same as NOLOCK hint.
• REPEATABLEREAD - Same as REPEATABLE READ isolation level, i.e. once a shared lock is acquired, it isn't released until the entire transaction completes. 
• ROWLOCK - Forces SQL to issue row level locks instead of page and table level locks.
• SERIALIZABLE - Same as SERIALIZABLE isolation level and same as HOLDLOCK. All transactions are blocked until the current transaction completes.
• TABLOCK - Forces SQL to issue a table lock instead of row or page level lock. This lock is only held until the executing statement completes. However if you are running under SERIALIZABLE isolation level or also pass HOLDLOCK, the lock will be held until the end of the transaction.
• TABLOCKX - Issues an exclusive lock on the table, preventing others from reading or updating the table. 
• UPDLOCK - This is a special lock used when you are updating data. When you are updating data, data must be first read and then updated. Ordinarily when data is read, a shared lock is issued which will then be converted to exclusive lock to update the data. But what if a transaction acquires a shared lock and another transaction acquires an exclusive lock on the same data at the same time? The data just read could get out of sync. To prevent this, SQL Server issues an update lock instead of shared lock. This allows other transactions to read the data while this transaction with UPDLOCK is reading the data but no other transaction can obtain an exclusive lock on this data. The UPDLOCK is then converted to exclusive lock to update the data. This ensures data integrity since you can be sure that the data hasn't been updated since this transaction read it.
• XLOCK - A exclusive lock which will be held until the end of the transaction. You can specify it with PAGLOCK or TABLOCK, indicating whether to exclusively lock page or a table.

Let's see a lock in action...

SELECT FirstName, LastName FROM CUSTOMERS WITH (NOLOCK)

SELECT FirstName,LastName FROM CUSTOMERS WITH (ROWLOCK)

Hope this helps you understand how you can use various hints available to you. This was a long series but hopefully it will be a good refresher to understand how locking mechanism works in SQL Server.

Thank you.

SQL Server Deadlocks

                                                                   SQL Server Locks - Part I, Part II, Part III, Part IV

Deadlocks
In previous post we discussed locking mechanism in SQL Server and what are different types of locks and how you can control default locking mechanism by using various hints with your queries.

Today, we will discuss deadlocks. Locking is a necessity for any DBMS to work in a multi-user environment and to maintain data consistency. Deadlocks occur when two transactions are waiting on each other to release the lock. Eventually SQL Server chooses one of the transaction as victim and kills it.

Let's explain this using an example -

Suppose you have a Customers table and you run the following queries in two separate transactions.

Transaction A
--statement 1
SELECT * FROM Customers WHERE Customer ID = 1
--statement 2
UPDATE Customers SET EmailAddress='123@mail.com' WHERE CustomerID = 2

Transaction B
--statement 1
SELECT * FROM Customers WHERE Customer ID = 2
--statement 2
UPDATE Customers SET EmailAddress='123@mail.com' WHERE CustomerID = 1

Let's assume one user calls a routine that executes transaction A and another use calls a routine that executes transaction B concurrently.

Transaction A will issue a shared lock while executing statement 1 on row 1 and transaction 2 will issue a shared lock while executing statement 1 on row 2.

Now  transaction  A will request an exclusive lock on row 2, while transaction B will request an exclusive lock on row 1.

Transaction A cannot complete because it is waiting on transaction B while transaction B cannot complete because it waiting on transaction A.

This condition is also called cyclic dependency. SQL Server engine deadlock monitor periodically checks for cyclic dependency and when it detects a dependency, it chooses one of the transaction as a victim and terminates it. The transaction chosen as a deadlock victim will terminate but the other transaction will complete successfully.

Remember, deadlocking is different than blocking. Blocking occurs when a transaction requests a lock on a resource which is exclusively locked by another transaction, in that case the requesting transaction will wait for the lock to be released.

Minimizing Deadlocks
Deadlocks are a fact of life, but you can minimize them by following some basic steps...

• Objects should be accessed in the same order
• All concurrent transactions should access the objects in the same order. For example, If there are two tables - Orders and Order Details, all concurrent transactions should request locks in the same order - For example first on Orders table and then on Order Details table. If all lock requests flow in the same direction, the subsequent requests will be blocked until a lock is released from the previous request, but deadlock should not occur.
• Avoid prompting for User input when a transaction is running
• A transaction should be completed as fast as possible so that the locks are acquired and released as quickly as possible. Requiring user input delays the transaction, hence resources will be locked for longer period of time.
• Keep Transactions Short and in one batch
• Keeping transactions short and in one batch will minimize resources, resulting in faster transaction completion.
• Use a lower Isolation Level
• We will discuss isolation levels in more detail in subsequent post but lower the isolation level, less locking. For example if you implement read committed isolation level, it allows a transaction to read data previously read by another transaction without waiting for that transaction to complete.
• Set READ_COMMITTED_SNAPSHOT to ON
• When this database option is ON, a transaction that is running under read committed isolation level uses row versioning instead of shared lock during read operations. Row versioning does not acquire shared lock for read operations. You must also set ALLOW_SNAPSHOT_ISOLATION to ON in addition to read committed snapshot for it to work.
• Use bound connections
• If your application opens two or more connections, you can bound them together such that one is a primary connection while other is a secondary connection. Under this scenario, any locks that are acquired by the primary connection are treated as they are also acquired by the secondary connection and vice versa.

This concludes our topic for today. There are some other aspects of locking such as providing locking hints with your queries or setting up different isolation levels. We will discuss them in subsequent posts.

Thank you.

Database Mail - Send Emails from SQL Server

Today, we will discuss how to configure and send emails using SQL Server 2008. Although the setup process is more or less same in both SQL 2005 and SQL 2008. Prior to SQL Server 2005, the database mail was called SQL Mail and lacked a few features that Microsoft introduced in database Mail.

Before you can send emails using SQL Server, you must first create a profile to use when sending emails. Below are step by step instructions on configuring database mail using management studio.

1. Expand Management and right click on Database Mail

2. Click on Next

3. Select the first option "Set up Database Mail by performing the following tasks".

4. Enter a profile name and click on Add. Note, since you won't have a profile setup, you will see the screen in step 5 below.

5. This is where you will enter the SMTP server information. In theory, if you are running IIS on the Database Server, you can configure IIS - SMTP Mail (IIS 7.x no longer have this feature, although there are still ways to configure it for now, but I expect this to go away soon). Whether you should run IIS on database server or not is a whole another argument.

Alternatively, you can configure SMTP server on another machine in your network and use it to send emails from both SQL Server via database mail and also from other applications.

6. Click on OK and click on Next, which will show you the following screen. You can leave defaults in most cases, but you can change the file attachments that you want to prohibit here.

7. Click on Next and click on Finish. Now Database mail is configured and ready to be used. You can right click on Database Mail again and click on Send Test Email to send a test email.

Before you send test email, make sure SQL Server is configured to send emails. Run the following script.


sp_CONFIGURE 'Show Advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs',1
GO
RECONFIGURE
GO


Before you can use the database mail to send emails or alerts from SQL Agent jobs, you need to add operators.

8. Right click on Operators from the Management Studio.

9. Setup one or more Email Operators

10. Now you are ready to configure a SQL agent job to send an alert or a notification email based on an action.  I will not go through creating a SQL agent job in this post, but let's assume you have a job that you want to send you an alert or an email when it fails. First, we will configure an alert. Right click the job and click on properties. Click on Alerts.

11. You can pick an operator that you created below and the type of alert you want to send. Response and Options allows you to configure additional options.

12. Now lets setup Notification. Click on OK from the Alerts screen to go back to the main screen and click on Notifications. Here you should be able to select the email or page that you want to send and the condition along with the operator you want to send it to.

Click on OK, and you have configured database mail to work with your SQL agent job.

You can also use "sp_send_dbmail" to send an email using SQL script

USE msdb
GO
EXEC sp_send_dbmail @profile_name='YourProfile', @recipients='youremail@email.com',
@subject='Test',@Body='Test Email'

SQL Server stores emails in the following system tables in msdb database. You can run select statements on these tables to check if an email has been successfully processed or failed.

sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems.

sysmail_mailitems has the initial emails and when an email is sent or failed, the sent_status flag is updated and also the email is copied into the appropriate table.

You can also check the log of all the emails in sysmail_log table.

Database Mail is a powerful feature and you can use it to ensure you are getting notified when a critical job didn't execute as it should have.

As always, your comments are welcome.

Transaction Log Shipping

Recently I had to setup log shipping for our production environment. Although, this wasn’t entirely new for me, nonetheless this was a good refresher. I decided to go ahead and note the steps involved in configuring transaction log shipping and various available options.

Points to Ponder

Some of you may disagree but I feel these are important considerations, especially in your production environment where high availability is important.

In our environment, we have about 100 databases. There are two clustered environments and we host half databases on one cluster and the other half on the other cluster. Each cluster has 2 SQL Servers.

Our initial thought was to go ahead and setup log shipping in a way that the secondary server for the databases on one cluster will be the other cluster and vice-versa. It just didn’t feel right. Take for example, if your cluster 1 goes down then all load will end up on cluster 2 which may not be feasible or desirable.

So, we decided to setup a third environment just to act as a secondary cluster for both clusters. It may be overkill for some, but it certainly was a worthwhile investment for us.

What is Log Shipping?

Transaction log shipping is basically a way to automatically backup transaction logs, copy them to the secondary server and restore them in the database on the secondary server.

You can actually set it up yourself using SQL server agent and using the following steps…

         Create a SQL Agent Job to backup transaction logs for a specific database.

         As a second step in the same SQL Agent Job create an execute Xp_CmdShell to copy the transaction log files to the secondary server.

         Create a SQL Agent Job on the secondary server to restore the log files to a copy of the same database on the secondary server.  The database on the secondary server obviously has to be in “Restoring” mode.

But SQL Server takes care of all that for you. Using SQL Management Studio, you can simply walk through the steps to setup log shipping in just a few minutes.

Using SQL Management Studio to Setup Log Shipping

Below is a step by step process on how to setup log shipping for a specific database.

Note: Your database recovery model must be full or bulk logged in order to configure transaction log shipping.

         Right click on Database > Properties

         Select Transaction Log Shipping

o  Check enable this as a primary database in a log shipping configuration and then click on Backup Settings.

         Define the network path where you want to backup the transaction log. If the backup path is on the same server, also type the physical path, otherwise leave it blank. You can change settings such as delete files older than 72 hours and alert settings, but generally defaults are good. If you are short on space, you may want to lower the 72 hour threshold.

         You can change the schedule by clicking on the Schedule button. 15 minute default is generally good.

         Click OK and then from the main window click on “Add” button to configure secondary server. Click on connect to connect to the secondary server.

   The three tabs allow you to setup secondary database information and restore location. 
       There are three ways to setup a database on the secondary server.

•             Manually copy the primary database and restore on the secondary server. Remember to keep in Restoring mode.
•       Chose from one of two options on the first tab (see above) to restore the database to the secondary server.

  Second tab allows you to configure the restore location

       You can change the restore schedule by clicking on the Schedule button but default work just as well.

       On the last tab, you can set database recovery mode and alert settings.

      Click on OK and you have configured the transaction log shipping.

      You can optionally setup the Monitor Server, which allows you to monitor your transaction log jobs remotely. This is only useful when you have a separate monitor server, because history and status of the backup log operation is stored on the primary server and that of the restore operation is stored at the secondary server. Remote monitoring server allows the history and status of both copy and restore operations on the remote server. You can also configure alerts to fire if alert service fails.

      That’s all there is to it. One issue that I have seen is the permissions issue. Make sure the user under which SQL agent runs has the read/write permissions on both primary and secondary servers.

Creating Indexes using T-SQL

In my previous post, we discussed creating indexes using SQL Management Studio. You can achieve the same results using SQL Scripts.

Using T-SQL

1.  Create a Non-Clustered, Non-Unique Index

/****** Object:  Index [idx_Test]    Script Date: 02/05/2012 12:56:50 ******/
CREATE NONCLUSTERED INDEX [idx_Test] ON [dbo].[DTA_input]
(
[SessionID] ASC,
[GlobalSessionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

2. Create a Unique Non-Clustered Index

/****** Object:  Index [idx_Test]    Script Date: 02/05/2012 12:59:41 ******/
CREATE UNIQUE NONCLUSTERED INDEX [idx_Test] ON [dbo].[DTA_input]
(
[SessionID] ASC,
[GlobalSessionID] ASC
)WITH (PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

HINTS / CLAUSE DEFINITIONS

PAD_INDEX
This option specifies whether you want to leave some space in each node (also referred to as page) for future inserts/updates. This is only useful when you specify a fill factor, because it uses the % specified in fill factor. Default is OFF.

STATISTICS_NORECOMPUTE
This flag determines whether index statistics are automatically recomputed. If you set it to ON, your SQL Query Optmizer may not be able to pick the optimal execution plan for any queries using this table.

SORT_IN_TEMPDB
Specifies that during index build/rebuild, intermediate sort results will be stored in tempDB. If your tempDB is on a different disk(s) than your production DB, it may reduce the time needed to create an index.

IGNORE_DUP_KEY
As I discussed in one of my previous post, when this option is ON and an attempt is made to insert a duplicate key, server issues a warning and ignores the duplicate row. If this option is OFF, server issues an error message and rolls back the entire INSERT. This clause can only be turned on if you have specified UNIQUE clause in your index.

DROP_EXISTING
This clause signals the SQL Server to drop and rebuild the pre-existing index with the same name. When you drop a clustered index, all non-clustered indexes must be rebuilt because they contain pointers to clustered index keys. This clause is extremely useful when dropping a clustered index on the table that also has non-clustered indexes. The non-clustered indexes are rebuilt only once and only if the keys are different.

ONLINE
This clause needs some explanation. When this clause is ON, it means database can be online i.e. being used for other processes while Index is being built or rebuilt. In other words, Index operations do not need exclusive lock. Default is OFF meaning indexing operation requires exclusive lock on the table. This was a nice enhancement in SQL 2005. Prior versions required exclusive lock for index operations. Some of columns such as VARCHAR(MAX) cannot be indexed while online.

ALLOW_ROW_LOCKS
This clause specifies whether data row is locked when performing operations on the indexed keys. When performing OLTP operations, it is a good practice to leave turn this ON.

ALLOW_PAGE_LOCKS
This clause determines whether the entire data page will be locked during index operations.

If both clauses are OFF, SQL Engine will not lock data page or data rows instead entire table will be locked during the operation.

Generally, it is a good idea to leave the defaults alone unless you have a very good reason to change them.

Thank you and as always, your comments are welcome!