Saturday, April 13, 2013

Tier 3 Storage NAS

There is at least one truth in enterprise technology -- storage requirements will increase exponentially.

Most datacenters jumped on the virtualization bandwagon years ago, and while the premise of server consolidation allows for efficiency, it also creates new storage growth patterns.  Virtual and physical machines need to be backed up in accordance with service level agreements.  Anybody that has worked with recovery point objectives knows that it is certainly more comforting to rely on a disk array than tape repositories.  Tapes are expensive, fail frequently, and are cumbersome to store.

There are tons of enterprise grade tier 3 storage arrays on the market, but the objective of this series is to present a proposal for building an in-house solution that meets and exceeds the "out-of-the-box" solutions.  In the series we will discuss the storage proposal, configuration, performance benchmarking, and maintenance.


Architect, develop and deploy a scalable and cost-effective backup storage solution.


Generally, a tier 3 storage system is comprised of multiple inexpensive disk arrays. The arrays should be
highly scalable, but not necessarily highly available. Redundancy within the array should allow for data
protection in the event of multiple drive failures.

Present Environment:

Currently, we use an Exagrid system as a backup repository for both headquarters and disaster
recovery sites. Backups are first written to the primary Exagrid array and subsequently replicated to the
disaster recovery site. Exagrid's architecture premise is based on a grid of "full servers" rather than a controller/shelf configuration.  They use post-process deduplication.  The grid architecture allows you to scale out by simply purchasing an additonal Exagrid unit and adding to the pool.

To read more about the Exagrid solution, check out their site here.

Proposed Environment:

The proposed solution is to build two identical tier 3 storage arrays – one for headquarters and one for
disaster recovery. Each array will be comprised of a single controller and multiple drive shelves.  Initially we will configure one drive shelf for each array. The drive shelf will hold  45 drives, and provide roughly 80TBs of usable storage in a RAID6 configuration. As capacity requirements increase, additional drive shelves can be purchased and daisy chained to the existing configurations without the need for additional controllers (up to 4 shelves per controller).

Actual Hardware Specs:

Controller (2):

- Supermicro SuperServer 6027R-TDARF - 8x SATA - 8x DDR3 - 740W Redundant
- 2 x Six-Core Intel® Xeon® Processor E5-2620 2.00GHz 15MB Cache (95W)
- 8 x 4GB PC3-12800 1600MHz DDR3 ECC Registered DIMM
- 2 x Intel 520 Series Cherryville SSDSC2CW180A3K5 2.5" 180GB SATA III MLC Internal Solid State Drive (SSD)
- LSI SAS 9211-8i SAS 6Gb/s PCIe 2.0 8-Port Host Bus Adapter
- LSI SAS 9205-8e SAS 6Gb/s PCIe 2.0 8-Port Host Bus Adapter
- No Windows Operating System (Hardware Warranty Only, No Software Support)
- Three Year Warranty with Advanced Parts Replacement

JBOD (2):

- Supermicro SuperChassis 847E26-RJBOD1 - 4U - 45 x 3.5" SAS - Dual Expander - 1400W Redundant
- 45 x 2.0TB SAS 2.0 (6.0Gbps) 7200RPM - 3.5" - Western Digital RE SAS WD2001FYYG 
- Supermicro System Cabinet Front Bezel MCP-210-84601-0B (Black)
- Quick-Release Rackmount Mounting Rails - 26.5" to 36.4" (Included) (MCP-290-00057-0N)
- 0.68M External SAS Cable (SFF-8088 to SFF-8088)
- Thinkmate® Three Year Warranty with Advanced Parts Replacement

Hardware Comparison:

So how do the two hardware solutions match up?  Here's the best part -- both hardware vendors are identical.  Exagrid sources SuperMicro components for all of their systems.  Don't believe me?  Take the faceplate off of your Exagrid unit and look at the chassis and drive caddies -- classic SuperMicro.  So if the quality of the hardware is really the same then it boils down to horsepower.  As far as horsepower goes, our standard EX10000E Exagrid has two quad core, middle grade, Intel processors and 8GBs of RAM -- certainly nothing to write home about.  Our new solution has two 6 cores Intel processors and 32GB of RAM, additionally we will be running two Intel 520 Cherryville SSDs for caching purposes.  In theory the hardware vote is going to the home built solution - later this week, I will get some real performance numbers.

Tomorrow I will introduce our OS of choice for the new backup repository and speak generally about our functionality requirements.

Thursday, June 7, 2012

Database and Transaction Log Backup Tracking

So from time to time I've had the need to track when the last backups were completed for each database on a particular SQL Server instance.  Luckily, SQL Server tracks all of this information for us in the msdb.dbo.backupset table.  To gather the last backup date/time for each backup type for each database we can use a script like the following:

 case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100), 
 MAX(b.backup_finish_date)) end AS last_backup_finish_date, case when b.type = 'I' then 'Incremental' 
 when b.type = 'D' then 'Full' else 'Transaction Log' end as Type
INTO #BackupSet
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON = b.database_name AND b.type in ( 'D', 'L', 'I')
 WHERE db.database_id NOT IN (2) 
 and b.recovery_model is not null
GROUP BY, type
ORDER BY type, name asc

The script above will return the last backup date for each full, incremental, and transaction log backup for each database on the SQL Server instance.

Pretty sweet script - I have to give a shout out to THE MAK for the original idea for the script.  The script was pulled from an article called:

Top 10 Transact-SQL Statements a SQL Server DBA Should Know

 Now that we have the base data set we can do with it what we please.  I have a stored procedure setup that sends our group an email if the backup window for each respective backup type is expired.  For example, the script below looks for full backups that haven't completed in over a week, then sends an email notification with the effected databases:

exec msdb.dbo.sp_send_dbmail  @profile_name='SQL email profile', @recipients= '[email protected]', @subject= 'Last Full Database Backup Exceeds 7 Days', 
@Execute_query_database= 'master',
@query_attachment_filename='Last Full Database Backup Exceeds 7 Days.txt',
@query = 'SELECT, 
case when MAX(b.backup_finish_date) is NULL then ''No Backup'' else convert(varchar(100), 
 MAX(b.backup_finish_date)) end AS last_backup_finish_date, case when b.type = ''I'' then ''Incremental'' 
 when b.type = ''D'' then ''Full'' else ''Transaction Log'' end as Type
INTO #BackupSet
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON = b.database_name AND b.type in ( ''D'', ''L'', ''I'')
 WHERE db.database_id NOT IN (2) 
 and b.recovery_model is not null
GROUP BY, type
ORDER BY type, name asc

SELECT FROM #BackupSet b
INNER JOIN sys.databases d on =
WHERE  recovery_model in (1,3)
AND b.Type = ''Full''
AND DATEDIFF(hh, last_backup_finish_date, getdate()) > 168'


Anyways...just a fun little way to make your life a little easier.

Wednesday, June 6, 2012

Auto Update Statistics Asynchronously

Since Microsoft SQL 2000 the auto update statistics property has been set to a default value of TRUE.  For all practical purposes this value shouldn't be changed.  By default, when a query is executed and the query optimizer recognizes old statistics on a table or column, the statistics are updated before the query executes.  This results in the query receiving the benefits of the updated statistics, but can cause some query performance degradation as the query waits for the update statistics job to finish.

Starting with Microsoft SQL 2005 we have the ability to manage how the query optimizer updates statistics using the AUTO_UPDATE_STATISTICS_ASYNC property.  When this value is set to TRUE, SQL server runs the necessary update statistics job in a background process.  This means your queries don't have to wait for updated statistics before executing.  The converse side to this property is that your queries don't receive the benefits of the update statistics as the update stats process runs after the query execution (in a manner that doesn't cause resource contention). The following script will show the current statistics settings for each database

SELECT name AS "Database Name", is_auto_create_stats_on AS "Auto Create Stats",
  is_auto_update_stats_on AS "Auto Update Stats",
  is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases

If we need to alter the update statistics settings for a particular database we can use the following commands:


These changes can also be made from the SSMS GUI as shown below:

Changing this setting to TRUE may allow faster query execution, but it should be mentioned that it also impacts your ability to place the database in SINGLE_USER_MODE.  Since SQL server is running a background process to update statistics asynchronously there is no way to go into single-user mode -- if you need these feature then AUTO_UPDATE_STATISTICS_ASYNC must be set to FALSE.

For more information on asynchronous statistics check out these sources:
Microsoft Technet article on Statistics

Monday, June 4, 2012

Dynamic Management Views and Resolving Missing Indexes

Recently I was working on an application experiencing poor database performance. Obviously, there are tons of possible causes of performance degradation, but in this case I found that the issue seemed to be related to queries issued on particular tables and columns.

 The first thing I did was take a look at the top locking queries for the respective database using SSMS standard reports.

 After reviewing the results of the top locking queries, I reviewed the most expensive queries for the database using SSMS activity manager:

Now that we have an idea of what queries are causing issues we should run a trace of the suspect processes to confirm that we see the expected resource contention.  After confirming the queries that are causing the issues we can take a look at some of Microsoft's SQL dynamic management views. More specifically we can look at the sys.dm_db_missing_index_details view.  I like to use this view in conjunction with the sys.dm_db_missing_index_group_stats view.  These views are particularly useful when researching high locking, slow performing queries that appear to be well written.  I normally use a query that sorts the most expensive missing indexes to the top so we can address these first:

SELECT TOP 10 statement AS [database.scheme.table], column_id, 
 column_name, column_usage, migs.user_seeks, migs.user_scans,
 migs.last_user_seek, migs.avg_total_user_cost,
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig
 ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS migs
 ON mig.index_group_handle=migs.group_handle
WHERE statement like '%xyz%' -- replace xyz with database name
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) desc

After identifying the expensive missing indexes you can either manually create the missing indexes as described in this tech note from Microsoft or encapsulate the create statement in a cursor and define the indexes dynamically.

Anyways, after creating the new indexes I re-ran the poorly performing processes and see if performance improved -- and it did.  One side note, if you don't see a huge performance increase initially run an update statistics job.

I hope this information will help some of you in your tuning endeavors -- as always I welcome any comments.

Wednesday, April 11, 2012

Using Linked Servers to Query Active Directory

So today I had the opportunity to work on setting up a linked Active Directory server as well as using Dynamic SQL to query said linked server.

The goal was to use a 2008 SQL instance to query our AD server for all users in different organizational units (OU).  The result set would then be used to update various tables in the destination database on the local SQL instance.

First we need to setup a Linked Server.  This can be completed through scripting or handled by the native GUI (SSMS).  I chose to script the linked server - as it is fairly straight forward:

-- 'adsi' is the default name for an AD connection
exec sp_addlinkedserver

/* Set the parameters to the following values
'sqlUser' to the username of local sql server user
'domainName\userName' this should be an AD account with appropriate permissions 
(Domain Admins)
'domainUserPassword' to the password of the domain account
 exec sp_addlinkedsrvlogin

After the Linked server is setup we can do a quick query to make sure the server responds and we have the necessary rights to search through AD. Be sure to choose a small OU (less than 1000 records) or SQL will return the following error:

Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "YourProvider" for linked server "YourLinkedServer"

 I chose to do something like this -- you can substitute the appropriate OU values in for your organization:

select samAccountName, GivenName, sn, legacyExchangeDN, email
from openquery(ADSI,
 'select samAccountName, GivenName, sn, legacyExchangeDN, email
 from ''LDAP://OU=Guests,OU=Test,OU=TestCompany,DC=Company,DC=com''
 where objectClass = ''User''')

You should see a result set with some basic information.  At this point we have completed our goal of being able to successfully query a linked AD server from a local SQL instance.  Here's the problem...we only queried a small OU and we hard coded the specific OU to query.  What if you need to query multiple OUs.  We could just simplify our LDAP search and only use DC=Company,DC=com which will return all OUs under that domain.  If we try this approach, and have over 1000 records in Active Directory we will receive the error message mentioned above.

In my case I needed to query all of the "Users" OUs in our forest. The individual record set for each OU is well below the 1000 record limit, so I decided to write a query that would select the different organizational units into a temp table and then use a cursor to cycle through the LDAP query for each OU.

The query looks something like this -- (please copy the code into SSMS for accurate string/literal shading)
--Grabs all 'User' OU's and stores them in a table to be used later by the cursor
SELECT Name, ADsPath
into #ADOUs
  'SELECT  Name, ADsPath
   FROM ''LDAP://DC=Company,DC=COM''
   WHERE objectCategory=''organizationalUnit''')
and Name like 'Users'

order by ADsPath

--Select * from #ADOUs

Create Table #ADInfo
(SamAccountName varchar(50),
GivenName varchar(100),
LastName varchar(100),
LegacyExchangeDN varchar(250),
PrimaryEmail varchar(250))

Declare @Path varchar(250),
        @Person varchar(25),
        @User    varchar(25),
        @SQL    varchar(max)
Select @Person='Person',
        @User ='User'
    Select ADsPath from #ADOUs
Open ADPath
While 1=1
        FETCH ADPath into @Path
        IF @@FETCH_STATUS <> 0
        Set @SQL = 'SELECT  samAccountName,givenName, sn, legacyExchangeDN, mail
                FROM ''''' + @Path+'''''
                WHERE objectClass=''''Person''''
                AND objectClass = ''''User'''''
                --Select @SQL

        Set @SQL=('INSERT #ADInfo SELECT samAccountName, givenName, sn,legacyExchangeDN, mail
                FROM OPENQUERY(ADSI, '''+@SQL +''') WHERE givenName IS NOT NULL')
    --Select @SQL
        Exec (@SQL)
Close ADPath
Deallocate ADPath

Select * from #ADInfo

This solution will work as long as your individual OUs are under the 1000 record limit.  If you are dealing with larger OUs you may want to check out this solution which creates an object and then sets the page size so that SQL can page through the AD data correctly.

I welcome any thoughts or suggestions.