SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

We have introduced the System Health Session in SQL 2008 in order to capture some critical Events to make post mortem analysis much easier. However in SQL 2008, by default the system_health session was only collected to a ring buffer and not persisted to disk. SQL 2012 truly provides more of a black box recorder…


How and Why to Enable Instant File Initialization

You may want to consider enabling Instant File Initialization (via SE_MANAGE_VOLUME_NAME a.k.a. "Perform volume maintenance tasks") for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but DOES NOT WORK FOR LOG FILE ALLOCATIONS. This is enabled for each instance via the "Perform volume maintenance tasks"…


Connection Pooling for the SQL Server DBA

  There are a handful of questions that DBAs get in the wild that aren’t necessarily under the jurisdiction of the typical DBA.  One of those aspects is connection pooling.  Far too often application teams, or network admins, or <insert non-DBA professional here> approach the DBA with questions regarding connection pooling, and whether or not…


SQL Server Resource Governor Monitoring reports

Resource Governor is an extremely cool and powerful feature that, when implemented, can manage the consumption of CPU and memory resources.  I will not be going into the details of Resource Governor, or even how to set it up as this is extensively documented in Books Online.  What I will be talking about today is…


An overview of High Availability and Disaster Recovery solutions available for SQL Server

With the more recent releases of SQL 2012 and Windows 2012, we are seeing the landscape of available options for SQL HA/DR specific architectures is improving significantly. Below is a summary of the high availability and disaster recovery solutions available for a SQL environment.   To begin, it is important to outline some key terminology….


Oops… I forgot to leave an empty SQL table partition, how can I split it with minimal IO impact?

One of the several advantages you get with partitioning a very large table is the ability to add or remove partitions instantaneously to help you with sliding window scenarios. However there are caveats if best practices aren’t followed. If the right most partition (in case of a RANGE LEFT partition) or the left most partition…


Sum of a SQL Analysis Services calculated measure within a Reporting Services report gives #Error

Summing a SQL Analysis Services calculated measure result within a Reporting Services report gives #Error but the same calculated measure output can be summed within Analysis Services perfectly fine. Environment: SQL Server 2008 Analysis Services and Reporting Services + SP1, Report builder 2.0 sp1. This should be applicable to all SQL 2008 and 2005  Reporting…


Performing transaction log backups using AlwaysOn Availability Group read-only secondary replicas-Part2

Describe the log backup process on a ROSR This is the second post in a planned series of articles relating to SQL Server transaction log backups of databases using a read-only secondary replica (ROSR).  Since the introduction of ROSRs in SQL Server 2012, administrators have been able to perform full database (copy only) backups as…

8