Discovering SQL Server: TempDB

TempDB is a very unique database. It is critically important but wiped out every time you restart SQL. SQL server does a lot of important work with the TempDB, but it's all temporary.  It is a scratch file if you will.

TempDB gets its own disks.  The faster the better for both reads and writes.  This file can get a lot of activity and the disk contention it creates will be noticeable.  Some query operations can spill out of ram into the TempDB and it's possible to sort indexes in there as well.

Use more than one TempDB if you have lots of cores in your server.  Lots of people have different ideas on how many files you should use for TempDB.  One rule of thumb is one file per 4 cores.  A few files is OK, but don’t go overboard with it.  The important detail is to manually size the files so they are all the same size.  SQL will use them more evenly when you do that.

Another good tip I picked up from one of my local SQL user groups is to make TempDB your default database instead of master for all users that don't have a more appropriate default.  The idea is that if you forget to change to the right database in management studio, your scripts will run in TempDB instead of Master.  So if you create a bunch of tables in TempDB, no big deal because they will clean themselves up.

Discovering SQL Server: Backups

It is very important to pay special attention to SQL backups.  SQL is not your average server and a little extra care is in order to make sure you are doing it correctly.  The database files have constant activity, so you can’t just ask Windows make a copy.
Here is a quick SQL command to get you started:

BACKUP DATABASE MyDatabase
                TO DISK '\\server\share\MyDatabase.bak'
                WITH BUFFERCOUNT=35

BACKUP LOG MyDatabase
                TODISK '\\server\share\MyDatabase.bak'
                WITH BUFFERCOUNT=35

This takes a fresh full backup of your database and a tail backup of your log file.  Make sure you are backing up your logs. If this is a production database, you should backup the log frequently.  I used a network path in my example because I want those backups off the server.

Now that you have your backups in a file on another server, use your favorite backup method to back them up.  Every environment is different, but I do full backups nightly and keep 7 most recent backups on the network share.  My transaction logs run every 15 minutes on databases that need backed up more often than daily.

There are several options you can use when running your backups.  I also add COMPRESSION and CHECKSUM along with the BUFFERCOUNT=35 option. The buffer count one is kind of a magic number that speeds up your backups.  It allows the backup process to stream more data from disk into ram as you save it to the network.

iScsi SendTarget issues with MD3620i and VMM

I have a small Hyper-V cluster with 3 Dell R610s and a MD3620i storage array using 10G iScsi. The event log on the MD3600 unit generates an informational event every 30 minutes.  This makes the log very hard to read and slow to load.

Here is the full event:

Event type: 180C

Description: iSCSI connection terminated unexpectedly

Event specific codes: 0/0/0

Event category: Internal

Component type: iSCSI Initiator

Component location: 

Logged by: RAID Controller Module in slot 0

As I was tracking this down, I started investigating the event log on my host servers.  I found several MSiSCSI 113 warnings every half hour:  

Log Name:      System

Source:        MSiSCSI

Event ID:      113

Task Category: None

Description:

iSCSI discovery via SendTargets failed with error code 0x00001068 to target portal *192.168.1.2 0003260 Root\ISCSIPRT\0000_0 .

The warning would repeat for every target portal IP address of the MD3600.

After a lot of digging on the internet, I discovered this forum post: SCVMM 2008 R2 - Host Refresh causes Event ID 113 MSiSCSI events on Hyper-V Cluster. VMM will run a refresh on the cluster every 30 min and that refresh generates those errors.  It looks like the iSCSI paths are checked and target discovery is ran.  The solution at the bottom of the thread by bellacotim resolved this issue for me.

The problem is that, even though not all iSCSI HBA instances can actually reach the target in question, the user had set up the Discovery Portal to issue iSCSI "Send Targets" along all possible iSCSI HBAs + the MSFT SW initator.  This is the default behavior if all one does is specify the specific initiator.

To properly configure discovery, do the following (assumes a fresh environment):

• Open the iSCSI Initiator GUI
• Select the Discovery Tab
• Click "Discovery Portal..." button to open the Discovery Target Portal dialog
• Enter the IP address (optionally TCP Port number) of the target's iSCSI portal
• Click "Advanced..." button to open the Advanced Settings dialog
• On the "Local Adapter:" pulldown, select a specific HBA instance you *know* can actually connect to the target.  Hint:  By inspecting the list of IPs for this HBA instance (see 7 below), one can gain this knowledge
• On the "Initiator IP:" pulldown, select the local address from which this HBA should connect from
• Click OK to close the Advanced Settings dialog
• Click OK to save your changes
• Repeat from (3) for all Initiator - Target combinations

I would perform these steps during a maintenance window. It gives a warning about disconnecting active sessions when removing existing discovery targets. 

The good news from what I can tell is that its only filling up event logs with clutter.  As far as I can tell, it is not causing any performance issues. It also looks like it shows up connecting to the other MD units when using iSCSI.  MD3000i MD3200i MD3220i MD3600i MD3620i