Feeds:
Posts
Comments

Archive for the ‘Operating System’ 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)

sqlcluster_addnewstorage_to_os_cluster

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

sqlcluster_addnewdrive

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.

Example:

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.

SqlCluster_DependencyReport.PNG

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

sqlcluster_addnewstorage_add_to_dependency

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

SqlCluster_Dependency_Before.PNG

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:

SqlCluster_Dependency_After.PNG

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

sqlcluster_dependencyreport_after

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]
GO
ALTER DATABASE [SampleDB]
ADD FILE
	(
		  NAME 			= 	N'SampleDB_Data3'
		, FILENAME 		= 	N'U:\MSSQL\Data\SampleDB_Data3.NDF'
		, SIZE 			= 	3500 GB
		, FILEGROWTH 	= 	100 GB
		, MAXSIZE 		= 	3900 GB
	)
TO FILEGROUP [PRIMARY]
GO
Hope this helps,
_Sqltimes

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
  • OPENROWSET
  • OPENQUERY
  • OPENDATASOURCE
  • RPC (Remote Procedure Calls)
  • Ones with
    • BEGIN DISTRIBUTED TRANSACTION
  • 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,
_Sqltimes

Read Full Post »

Quick one today:

In a previous post, we covered one of the techniques used to generate text version of cluster Log files using command prompt. Today, we’ll cover another technique, a more common one going forward; Using PowerShell.

Context:

In Windows Server 2012, looks like the cluster.exe command prompt interface is not installed by default, when you install FailOver Cluster.

failovercluster_commandlineinterface

PowerShell:

So, we’ll use PowerShell cmdlets to generate these cluster logs.

#
#  Generate cluster log from SampleCluster and save in temp folder.
#
Get-ClusterLog -Cluster SampleCluster -Destination "c:\temp\"

When you run in PowerShell window, the response looks something like this:

powershell_clusterlog

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

When we install Sql Server software on a Windows machine, sometimes, we need to install Service Packs (SP); Either SP1 or SP2 or SP3, etc. As service packs are cumulative, it helps to keep it down to a two step process. In some scenarios, this two step process is not posible due to version conflicts that do not allow RTM Sql Server versions on later versions of OS.

SlipStream allows us to combine these two steps into one. Combine RTM version with ServicePacks (SP) and install them together.

Benefits:

  • Reduce or remove need for multiple restarts
  • Reduce install duration times
  • Avoid version conflicts mentioned above (RTM Sql Server versions may not work on later versions of OS)

Preparatory Steps:

  • Install any prerequisites like .Net 3.5
  • Install Windows Installer 4.5
  • Download and extract SP file to a local drive a.k.a. C:\Temp\SP1\
  • Run the Service Pack (SP) first to install Setup & Support files. This prevents any surprises when actual install is performed.

SlipStream Install:

  • Open Command prompt as Administrator
  • Go to the location where install DVD is located.
  • Use this command to run install in SlipStream mode.
    • Setup.exe /PCUSource=C:\Temp\SP1
  • After a second or two, the Sql Installer Wizard opens
    • Walk through the normal install steps.
  • When you get to the “Ready to Install” screen, it indicates that SlipStream is engaged during this install (see in the image below).
SlipStream SqlCluster Install

SlipStream SqlCluster Install

  • Perform restart if you need to.

Please note that this is just one of the techniques. On Microsoft Support, they have other options detailed with troubleshooting techniques.

Hope this helps,
_Sqltimes

Read Full Post »

In our lab machines, sometimes quick clean up activities become necessary; They occur frequently before and after some large batch testing scripts. Such situations include activities like:

  1. Reducing size of either log or data file
  2. Emptying transactional log file
  3. Deleting transactional log file

Note: Please be advised that such operations are not recommended on a production database. These will result in unpredictable and sometimes reduced performance.

In recenlt posts, we’ve convered the use of SHRINKFILE in different scenarios:

Important Points to keep in mind:

  • SHRINK operation could be stopped at anytime without losing the work completed thus far. It retains the progress made (re-allocations)
  • Shrinking data or log file does not require single-user mode on the database. Other user activity could be running in parallel without any interference with SHRINK work.
  • SHRINK process could be delayed due to blocking from other user activity, so if possible, perform SHRINK operation when there is lesser traffic.
  • SHRINK operation is a single threaded operation, that methodically works through each data block. So it is time consuming.
  • SHRINK one file at a time (rather than in parallel)

 

Following are the steps we follow:

Reducing Size of Log or Data File

In lab environment, to reduce the size of a bloated log or data file, we implement a version of the following steps:

Step 1:

  • Before freeing up any space back to Operating sytem, we need to adjust the way space is occupied by all the database pages.
  • Sql Server will reallocate all used pages from the end of the physical file to earlier portions.
  • This allows end of the physical file to be freed up.
--
-- SHRINK the data file down to 1 GB (reallocation)
--
USE [SampleDB]
GO
DBCC SHRINKFILE (N'Sample_Data2' , 1024) -- Reduce it to 1 GB
GO

Step 2:

  • Once reallocation or adjustment is complete, we could issue TRUNCATEONLY option to free up that space back to Operating System.
  • This is when we see that the physical file reducing in size.
--
-- Release space back to OS
--
USE [SampleDB]
GO
DBCC SHRINKFILE (N'Sample_Data2', TRUNCATEONLY)
GO

Emptying Transactional Log File

In lab environment, to empty entire transactional log file, we implement a version of the following steps:

--
-- To remove secondary log file, first we need to empty it. Then remove it
--
DBCC SHRINKFILE (SampleDB_log2, EMPTYFILE)
GO

Deleting Transactional Log File

In lab environment, to delete a transactional log file, we implement a version of the following steps:

--
-- To remove secondary log file, first we need to empty it. Then remove it
--
DBCC SHRINKFILE (SampleDB_log2, EMPTYFILE)
GO

ALTER DATABASE SampleDB
REMOVE FILE SampleDB_log2
GO

For more details, please refer to BoL

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

In one of the previous posts, we saw one of the techniques to query available free space in transactional log files. Today, we’ll see another technique to gather the same metric from one place for both data file & transactional log file.

We rely on sysfiles system catalog.


--
-- Free space in data files
--
SELECT  name AS [FileName]
      , size*8.0/(1024*1024) AS [Total_File_Size_GB]
      , FILEPROPERTY(name, 'SPACEUSED')*8.0/(1024*1024) AS [Used_GB]
      , (size*8.0/(1024*1024)) - (FILEPROPERTY(name, 'SPACEUSED')*8.0/(1024*1024)) AS [FreeSpace_GB]
FROM sysfiles
GO

--
-- Using DBCC command for Log files
--
DBCC SQLPERF(LOGSPACE)
GO
Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Its rare, but there sometimes we do run into opportunities to perform edition upgrade on Sql Server environments. Following steps present a walk through of the edition upgrade process.

Working on this task provided another opportunity to appreciate the great value Sql Server installer provides; It is really a value-packed tool (see previous post on a different feature).

Some points to keep in mind:

  • We can perform edition upgrade, but edition downgrade is not possible.
    • Ex: cannot go from Enterprise to Standard.
  • In my experiments, Sql Server restart was not necessary, but it is better to restart Sql Server (and all Sql Services) to allow the new features to kick-in.

Step 1:

Open Sql Server installation wizard. Go to Maintenance tab and click on Edition Upgrade.

Edition Upgrade Step 1

Edition Upgrade Step 1

Step 2:

When you click on Edition Upgrade, it opens up the “Upgrade the Edition of Sql Server 2012” wizard. First, it performs checks to make sure ‘Support Files’ are available. In the next screen, enter the product key to validate the upgrade.

Edition Upgrade Step 2

Edition Upgrade Step 2

Step 3:

As you walk through the wizard, select the instance that you want to upgrade. In this case, DEFAULT instance is being upgraded. All components (shared) related to edition upgrade will also be upgraded.

Edition Upgrade Step 3

Edition Upgrade Step 3

Step 4:

Upon clicking next, it runs through checks to make sure all the rules are in compliance. The next “Ready to upgrade” screen lists all the components that will be upgraded. Review the list and make sure they match your goals.

Edition Upgrade Step 4

Edition Upgrade Step 4

Step 5:

After completing upgrade, it displays the status of upgrade for each component in the “Complete” screen.

Edition Upgrade Step 5

Edition Upgrade Step 5

 

Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »