Archive for the ‘Virtual Machines’ Category

Interesting one today:

On one of our production machines, we recently added a new LUN to a SQL cluster. A task like this is a team effort. Sysadmins perform some steps and DBA carry out the remaining. In this article, the main focus is on covering the steps after the LUN is added to the OS & Sql Cluster by the sysadmins.  For context, we’ll start with high level steps before going into details.

Sysadmins steps

  1. Add new storage to the machine/OS as an available storage
  2. Format the available drive with appropriate settings (cluster size) and add it as a new drive
  3. Make drive available to the Cluster using “Add Disk” screen in FailOver Cluster Management tool.

DBAs steps

  1. Add available storage to Sql Cluster
  2. Configure dependency (and check the report before & after)
  3. Add data file on the new cluster storage drive

Here we’ll cover the DBA steps in detail:

Some of these steps were covered under a different recent article as part of dealing with an error message, but here we’ll cover it as a task by itself (which it is).

Add New Storage

Once sysadmins have made the new storage as an ‘available storage’ to OS Cluster, it needs to be added as a new storage location to the SQL Cluster.

In FailOver cluster manager, go to Sql Server Resource Group for this SQL Cluster and right click for detailed options and choose “Add Storage” (see image below)


Once successful, go to Storage\Disks under in FailOver Cluster Manager to confirm the availability. See image below:


Configure Dependency

Adding the storage is an important step, and equally important step is adding the new drive to Sql Cluster Dependency Chain. Dependency Chain informs Sql Sever “how to act”, when any resource in the Cluster becomes unavailable. Some resources automatically trigger cluster failover to other node; some resources do not. This decision is made based on the configurations in Dependency Chain.


Critical: Data drive/LUN that has database files is critical for optimal availability of the Sql Cluster. So, if it becomes unavailable, failing over to other available nodes is imperative to keep the cluster available.

Non-Critical: In some scenarios, Sql Server Agent is not considered as Critical. So if it stops for some reason, Cluster will make multiple attempts to start it on the same node, but may not necessarily cause failover.

This is a business decision. All these “response actions” will be configured in Cluster settings.

Now, check the dependency report (before); We can see that new drive exists in Cluster, but is not yet added to the Dependency Chain.


To Configure Dependency Chain, go to the Sql Server Resource Group under Roles in FailOver Cluster Manager. See the image below for clarity:

Then go to the bottom section for this Resource Group, where all the individual resources that are part of this Resource Group are displayed.

Under “Other Resources“, right click on Sql Server Resource and choose properties.

do As show


In the “Sql Server Properties” window, we can see the existing resources already added to dependency chain logic.


Now, go to the end of the logic list and choose “AND” for condition and pick the new Cluster Storage to be included. See image below for clarity:


After saving the settings, regenerate the Dependency Chain report. Now, we’ll see the new drive as part of the logic.


Add Database Data File to New Cluster Storage

Now, that the new drive is ready, we could easily add a new data file to the new location.

-- Add data file to new storage location
USE [master]
		  NAME 			= 	N'SampleDB_Data3'
		, FILENAME 		= 	N'U:\MSSQL\Data\SampleDB_Data3.NDF'
		, SIZE 			= 	3500 GB
		, FILEGROWTH 	= 	100 GB
		, MAXSIZE 		= 	3900 GB
Hope this helps,

Read Full Post »

Interesting one today:

Recently, on one of the lab machines, we had to rebuild SSRS machine on a new VM. After walking through the regular install and other configuration steps, when browser is opened and pointed to the SSRS URL, we get this error.

Error Message:

The report server cannot decrypt the symmetric key that is used to access sensitive
or encrypted data in a report server database. You must either restore a backup key
or delete all encrypted content.

This error message seems cryptic and familiar at the same time. After pacing back and forth for a few minutes near my desk, it dawned on me. This VM with pre-installed SSRS must have some encryption keys set up already, that need recreation or just deletion.


  1. Open Reporting Services Configuration Manager
  2. Connect to the SSRS engine on the VM
  3. Open Encryption Keys tab on the left
  4. Click on ‘Delete’ button to remove the keys (from previous set up)
  5. Now open browser with Administration privileges
    1. Open SSRS URL

Now the error message is resolved.


Note: Since this is a lab machine (and freshly minted VM), these aggressive steps are warranted. But in a production environment, you want to restore the encryption keys with the correct backup keys.

Hope this helps,

Read Full Post »

Quick one today:

Ran into an interesting version of the familiar error message. When installing Sql Server 2012 on a Windows Server 2012 R2, this error appeared.

Please note that, during pre-install checks, it just highlighted lack of .Net 3.5 it as a warning, but not as a failure. When installation is attempted, it fails asking for .Net 3.5.

TITLE: Microsoft SQL Server 2012 Setup

The following error has occurred:

Error while enabling Windows feature : NetFx3, Error Code : -2146498298 , 
Please try enabling Windows feature : NetFx3 from Windows management tools 
and then run setup again. For more information on how to enable Windows features, 
see http://go.microsoft.com/fwlink/?linkid=227143

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.5058.0&EvtType=0x681D636F%25401428%25401


It seems like a cryptic error message, but its just a different variation of the same old “Missing .Net 3.5” error message. To resolve follow these steps:

Step 1:

From Task Bar, open Server Manager. Go to Manage, Add Roles and Features.


Step 2:

In the Add Roles and Features Wizard, click through the initial screen; When you are in ‘Installation Type’ tab, choose ‘Role-based or Feature-based installation’.



Step 3:

Under Server Selection tab, choose the appropriate server name. If you are running it on the server, its name will be displayed in the Server Pool section.


Step 4:

Skip the next section (Server Roles). Installation of .Net 3.5 is under Features. In the Features section, click the check box to install .Net 3.5.


Step 5:

Components necessary to install .Net 3.5 are usually not readily available, so having access to Windows Server 2012 DVD or ISO would be necessary.


In the Confirm Installation Selections step, at the bottom of the screen, click on Specify an alternate source path link to specify the path to Windows Server 2012 ISO.

Once necessary binaries are identified, installation will progress to completion.

Then reattempt, Sql Server installation.


Hope this helps,

Read Full Post »

Quick one today:

Not often, but once in a while, we need to check what Sql Components are installed on a given virtual machine; so we could replicate (install the same components) on any dependent database virtual machines. Very rarely do we get opportunities to appreciate the multiple features made available through the Sql Installer. Microsoft provides awesome tools to achieve this fashionably.

Follow these steps:

Step 1:

To open the Sql Installer, go to

  • Start
  • Microsoft Sql Server
  • Configuration Tools
  • Sql Server Installation Center
Sql Server Installation Center

Sql Server Installation Center


Step 2:

Under Sql Server Installation Center, open Tools. Go to Installed Sql Server Features Discovery Report.


Sql Install Discovery Report

Sql Install Discovery Report


Step 3:

When you click on it, Sql Server Installation Center will generate the report.

Running Discovery Report

Running Discovery Report

Once the report completes, it shows the output in, an easy to use, HTML format in a browser. Sample report is attached below:

Installed Components (Discovery Report)

Installed Components (Discovery Report)

Note: The same report is saved as SqlDiscoveryReport.htm under Program Files\Microsoft SQL Server\…\Setup Bootstrap\Log\…


Hope this helps,

Read Full Post »

Quick one today:

Rarely, but every once in a while, we see this entry in our production virtual database servers. Initially it seems benign, but upon further investigation we learnt that this is a repeating issue and there is an explanation for it.

Error Message:

spid125     I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup

The same message could appear for all databases on this drive.


In short, this occurs when VSS backups are made through VDI.

Long Story:

Sql Server has a lot of APIs that enable independent software vendors to integrate Sql Server into their own products. Through these APIs, they carry out necessary functionality while maintaining the reliability, feature-functionality and performance of Sql Server. One such API interface is VDI (Virtual Device Interface); This works from within the VSS (Volume Shadow copy Service), through which 3rd party tools could perform backup & restore operations through BACKUPs using SNAPSHOT.

Internals of VSS in Sql Server (from Microsoft blog)

Internals of VSS in Sql Server (from Microsoft blog)

Using the VDI interface, when such command (BACKUP WITH SNAPSHOT) is executed, Sql Server needs to make sure that the backups are reliable and complete. Since data files are under constant lock by Sql Server with continuous updates going on, Sql Server has to temporarily cease all I/O into the data files to secure a volume snapshot.

Once that is complete, the same 3rd party tool tells Sql Server through VDI that I/O can now resume. This is usually for a short duration, but depending on the size of the file and the throughput of the drive, it could sometimes take longer.

In our environment, we see I/O freeze for a few seconds. This is not good. So we schedule these snapshot backups during daily scheduled down times.

2016-02-19 18:36:36.37 spid125 I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2016-02-19 18:36:47.91 spid125 I/O was resumed on database master. No user action is required.

Since this is internal Sql Server functionality that Microsoft controls, looks like they are working towards making it more efficient in future releases. Some Microsoft tools that rely on this are:

  • Microsoft Backup (ntbackup.exe)
  • Volume Shadow Copy (VSS)
  • Data Protection Manager (DPM)

Some 3rd party tools that use VSS to perform such backup and restore operations are:

  • Symantec Business Continuance Volume
  • Veritas Net Backup


Hope this helps,

Read Full Post »

Quick one today

A few months ago, in our lab we tried to enable SSH access to our ESXi hosts and these are the steps we followed.

  1. First, if you have access to the ESXi console, hit F2 and log in.
  2. From the System Customization screen, go to ‘Troubleshooting Options’, and hit <Enter>.
  3. In the ‘Troubleshooting Mode Options’ screen, you see a bunch of toggle options.
  4. Go to ‘Enable SSH’ and hit <Enter> (when you hit <Enter>, the option toggles to ‘Disable SSH’).
  5. Exit from each screen.


Hope this helps,

Read Full Post »

A few months ago, there was an interesting conversation that uncovered this recommendation for maximum & minimum server memory setting for VLDB instances. Memory is a crucial to any database servers; And it becomes more important for VLDBs. So it is important that we achieve optimal memory settings. As with anything else, the correct setting depends on several factors and it varies from instance to instance. Here we’ll cover general recommendation that could be used as baseline.

Usually database servers are dedicated machines. So, the only major application running on the OS is the Sql Server instance. So, we need to carve out memory for OS and Sql in a way that both run efficiently. For database server instances on VM or shared machines, memory configuration gets a bit more complicated; but this could used as a general guideline to start with and apply more relevant factors on top of it.

First, query your current settings:

--  Query the current memory allocation setting
FROM sys.configurations
WHERE name LIKE '%memory (MB)%'

For VLDB’s, generally the size of memory is larger than other database servers. As the server memory increases, the configuration changes. The general point is that we need to allocate as much memory to the Sql instance as possible. But this should not be at the cost of OS itself. So, we need to strike a right balance so the OS could run efficiently (without paging) while allocating as much memory to Sql instance as possible. To achieve this, we use the two settings available for each instance.

  • min server memory (MB)
  • max server memory (MB)
Total Server Memory For OS Minimum Memory for SQL Maximum Memory for SQL
1 – 4 GB 2 GB 2 GB 2 GB
4 – 8 GB 3 GB 3 GB 5 GB
8 – 12 GB 4 GB 3 GB 8 GB
12 – 16 GB 5 GB 3 GB 11 GB
16 – 24 GB 6 GB 7 GB 18 GB
24 – 32 GB 7 GB 7 GB 25 GB
32 – 40 GB 8 GB 7 GB 32 GB
40 – 48 GB 9 GB 7 GB 39 GB
48 – 56 GB 10 GB 7 GB 46 GB
56 – 64 GB 11 GB 7 GB 53 GB

For VLDBs with more than 64 GB, we need to consider more factors on top of the guidelines above. We’ll dive into it in a future post.

-- Configure Memory Settings 
EXEC sp_configure 'show advanced options', 1

EXEC sp_configure 'max server memory (MB)', 5120

EXEC sp_configure 'show advanced options', 0

Hope this helps,

Read Full Post »

Older Posts »