Archive for the ‘Technical Documentation’ 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 »

Quick one today:

Earlier today, we ran into this error in our lab environment with replication. Two different errors, on the same environment, due to similar sequence of events.

Msg 20584, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 67
Cannot drop server because it is used as a Subscriber to remote Publisher in replication.

Cannot drop server because it is used as a distributor in replication

This type of errors are rare, but one of these three options below might provide some relief.

  1. Reset Dist flag
  2. Force drop distributor
  3. Manually remove Publishers (from Distributor properties)

Also, see the notes in the bottom section (points to keep in mind)

Reset Dist Flag for Distributor Instance

Sometimes, the dist flag for Distributor stays set, when not supposed to. So manually resetting it helps.

--  Reset Distributor flag
EXEC master.dbo.sp_ServerOption   @server = 'REPLDist'
				, @OptName = 'dist'
				, @OptValue = 'false'

Force Drop Distributor

One of the last options is to ‘force’ drop distributor. This allows removing distributor database without running through the proper checks. This step is a high risk, high reward option; Since all checks were previously performed and all components of replication are removed, except distributor, this step might provide relief.

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

Manually Remove Publishers

Usually, the above two steps will resolve the issue. But in rare occasions, the issue persists, like in our lab. For those stubborn situation, this technique has given some relief.

Go to Replication >> Distributor Properties >> Go to Publishers (tab)

Now, un-check each Publisher and save. Important to note that, this needs to be done for one publisher at a time.


Points to keep in mind:

Sometimes, just the one of these steps might be enough to resolve the issue; Sometimes, it might take two of the steps. In our lab, we ended up needing all the 3 steps. the first step is easy to implement.
The second and third steps might need repeated attempts. Something like, try Step 2, if it fails, then try step 3; If Step 3 succeeds, then go back to Step 2. Once Step 2 is successful, the distributor is fully cleared and cleaned.
Hope this helps,

Read Full Post »

Quick one today:

Years ago, there was a popular comment that Sql Server did not wide variety of functions; Once of them is the much needed IsNumeric functionality. Though such a function existed, there was a case where it resulted in incorrect results. So there were several custom functions like IsReallyNumeric or IsInteger, etc (my personal DBA library also had such custom functions)

Now, there is a powerful function that does more than just IsNumeric capability. It is called TRY_PARSE. Let’s take an example:

-- Sample code for TRY_PARSE
	, TRY_PARSE('123456' AS INT)		AS [INT_Test2]
	, TRY_PARSE('ABCD-01-01' AS DATETIME)	AS [DateTime_Test1]
	, TRY_PARSE('2016-01-01' AS DATETIME)	AS [DateTime_Test2]


This function takes input in VARCHAR/NVARCHAR format and attempts to convert into the requested format. If the result is successful, the value is returned in requested data type. If not, NULL is returned. This makes it very useful in data processing logic flows; And makes it more useful function than the legacy (IsNumeric) functions.

Hope this helps,

Read Full Post »

Interesting one today:

Setting up a new SSRS machine on one of the virtual machines in our lab, we ran into this interesting error after walking through the initial steps of install & configuration. Turns out, this is a common error after fresh installs. Following is the error message:

The permissions granted to user are insufficient for performing this operation. 
(rsAccessDenied) Get Online Help

Following steps have helped resolve it.


SSRS service runs under the security of a user account; My guess is that this account needs permissions to access the content on the SSRS site & folders. So we need to carry out two steps to assign proper permissions.

  1. Folder Permissions
  2. Site Permissions
  3. Trusted Site Settings

Folder Permissions

Step 1:

Open browser with Administrative Privileges and point it to Reports Manager URL. Go to ‘Folder Settings’.

ssrs_permissions_error_foldersettings_1 Step 2:

In the properties page, go to the Security tab and click on ‘New Role Assignment


Step 3:

In the new page, add the user account for ‘Group or User Name’ field. Then assign ‘Content Manager‘ permissions to the user account. And hit okay to save the settings.


Site Settings

Step 1:

Use the same browse (with administrative permissions), to go to Site Settings on the right.


Step 2:

In the settings page, go to Security tab and click on New Role Assignment.


Step 3:

In the new page, add the windows user to the ‘Group or user name’; Assign System Administrator role and hit OK.


Trusted Site Settings

Go to Internet Explorer, Tools -> Internet Options -> Security tab > Click on Trusted Sites. In the new window, enter the URL for Report Manager and close.

Now you can open browser and open Report Manager without any security warning.



Once the permissions are set, close the browser and re-open it without administrative permissions. Going forward, it would work without needing to open browser with elevated permissions.

For more information, please refer to this BoL article.

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 »

Interesting one today:

MSDTC is one of the popular software components that is on all Windows systems. It is one of the Windows  Operating System components that Sql Server relies on it to perform some crucial tasks (when needed).

What does it do?

MSDTC, Microsoft Distributed Transaction Coordinator, is essentially, as name suggests, a coordinator/manager to handle transactions that are distributed over multiple machines. Let’s say we start a transaction, where one of the steps includes querying data from a different Sql Server instance on a different physical machine; MSDTC comes into action with these specific tasks that need transaction coordination across different physical machines. It executes the section of code that is supposed to run on remote machines and brings back the results to local Sql instance. In this process, if any issue were to occur, on the remote machine that results in rollback, MSDTC makes sure the original transaction on this machine also rolls-back safely.

How does it do?

MSDTC comes with necessary Operating System controls and memory structures to carry out these operations independent of the Sql Instances, while keeping integrity of the transaction across the multiple physical Sql machines a.k.a. the complete two-phase distributed commit protocol and the recovery of distributed transactions.

Where does Sql Server use it?

The key point here is that these need to be Sql Instances on different physical machines. Queries that request data across different instances on the same physical box do not go through MSDTC.

MSDTC is used by query activities like

  • Linked Servers
  • RPC (Remote Procedure Calls)
  • Ones with
  • etc…

So, every time we run SQL queries that utilize above techniques, they rely on MSDTC to carry out operation while maintaining transaction integrity.

Who else uses it?

MSDTC is an Operating System resource that is used by applications other than Sql Server, to perform any distributed transaction activities; Like eXtended Architecture applications.

Is MSDTC required?

MSDTC is not required for Sql Server installation or operation. If you are only going to use Database Engine, then it is not required or used. If your Sql uses any of the above mentioned query techniques (Linked Server, OPENQUERY, etc), or SSIS or Workstation Components then MSDTC is required.

If you are installing only the Database Engine, the MSDTC cluster resource is not required. If you are installing the Database Engine and SSIS, Workstation Components, or if you will use distributed transactions, you must install MSDTC. Note that MSDTC is not required for Analysis Services-only instances.

What about Sql Cluster?

Same rules as above apply to Sql Clusters as well with one additional rule. If you have two instances on the same machine (that are clustered across different physical machines), then you’ll need MSDTC. Since the Cluster could failover to remote machine at anytime.

Let’s take an example:

Let’s say Instance1 is on physical machines A & B, with B as active node. Instance2 is on machines B & C, with B as active node. A query going from Instance1 to Instance2 will need MSDTC (even if both the instances are active on the same physical machine B at that given point in time.).

This is because, there is no guarantee that they will remain on the same physical machine at any given time; They might failover to other machines, resulting in instances being on physically different machines. So MSDTC is required (when distributed operations are performed).

Also the recent Sql Server versions do not required MSDTC during Sql Server installations.

Other points in a Clustered Environment

We could have multiple instances of MSDTC as different clustered resource (along with default MSDTC resource).

In scenario with multiple MSDTC, we could configure each Sql Cluster resource to have a dedicated MSDTC instance. If such mapping does not exist, it automatically falls back to use the default MSDTC resource.

Hope this helps,

Read Full Post »

Interesting one today:


In replication, as we all know, data/records from Publisher are copied to all Subscribers. This is carried out as Transactions and Commands from Publisher to each Subscriber. Let’s say application INSERTs 10 new rows into the database (Publisher) as part of some application transaction; Now the LogReader reads Transactional Log file and takes a note of these 10 records; Brings their details to Distributor.

Behind the Scenes:

From Distributor, they are transferred to each Subscriber by the Distribution Agent. In actuality, the Distribution Agent executes these INSERT commands at each subscriber; Upon success, it proceeds to execute the subsequent commands until all Subscribers are up to date.

Here is the beauty:

In Replication, the result of INSERT activity by the application, is carried out as “1 transaction with 10 commands” (one INSERT command for each record, all encompassing in one transaction). When we look closely, each INSERT command is carried out by Replication Stored Procedures located in each Subscriber (they are created as part of replication setup). Distributor will have entries with these stored procedures with appropriate parameters. Now Distribution agent will execute them at each subscriber.

Distributor will have entries like:

{CALL [dbo].[mtx_rplins_SampleTable] (758374661,1,N'SomeName1')}
{CALL [dbo].[mtx_rplins_SampleTable] (758374662,4,N'SomeName2')}
{CALL [dbo].[mtx_rplins_SampleTable] (758374663,5,N'SomeName3')}




Now here is the question that we’ll discuss today. Why do we have different replication stored procedures calls? Not different procedures, but different procedure calls. We have dedicated replication stored procedures for each table; Each to carry out INSERT, UPDATE and DELETE. Like

  • sp_MSins_ : Handles INSERT operations for this table
  • sp_MSupd_ : Handles UPDATE operations for this table
  • sp_MSdel_ : Handles DELETE  operations for this table

But why do we have different procedure calls?

In the above image, we see entries like {CALL […..]}. There other procedure calls like CALL, SCALL, XCALL, MCALL, VCALL. What are these for?

  • CALL
    • This is the most popular or default call used in Replication.
    • It could be used for INSERT or UPDATE or DELETE
    • This is used for UPDATEs only.
    • Used for UPDATEs and DELETEs
    • Used for UPDATEs only
    • For internal use only.
    • Also used for updatable subscriptions


This is the default option used for all activities a.k.a. UPDATE/DELETE/INSERT. As seen in the picture above, CALL will have a list of values provided for all columns of the table.


Looks like some procedures are used exclusively for certain actions; Like MCALL & SCALL are dedicated for UPDATEs. But there is some difference between these too. SCALL only sends the columns that need updating; Where as MCALL sends all columns of the table (including the ones that are not changing).


Sends data for both before and after values for all columns of the table involved in UPDATE or DELETE. Obviously, in UPDATE, there are values for both before and after; But in DELETE, the after values is empty.

For more details, please refer to this excellent article.

Hope this helps,

Read Full Post »

Older Posts »