SQL SERVER – Add Node in Cluster – Rule “Node and cluster edition match” failed

Every day I get many emails and blog comment with at least one new problem every day. Recently, one of my blog readers sent an email asking assistance. She already had two node Windows Cluster. Let us call them Node1 and Node2. SQL Server Clustered instance, was successfully installed on Node1. In SQL 2008 onwards, we need to run setup on Node2 and perform “AddNode” action by choosing below option.

While performing AddNode, there was a Rule Failure.

—————————
Rule Check Result
—————————

Rule “Node and cluster edition match” failed.

The edition of the new node does not match the edition of the cluster.

—————————
OK
—————————

I asked to share the setup logs from Node2 and while looking at them, I found below.

2015-03-10 12:00:22 Slp: Initializing rule      : Node and cluster edition match

2015-03-10 12:00:22 Slp: Rule applied features  : ALL

2015-03-10 12:00:22 Slp: Rule is will be executed  : True

2015-03-10 12:00:22 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.AddNodeEditionBlock

2015-03-10 12:00:22 Slp: The edition user specified is: ENTERPRISE

2015-03-10 12:00:22 Slp: The edition of the cluster instance: STANDARD

2015-03-10 12:00:22 Slp: Evaluating rule        : AddNodeEditionBlock

2015-03-10 12:00:22 Slp: Rule running on machine: Node2

2015-03-10 12:00:22 Slp: Rule evaluation done   : Failed

2015-03-10 12:00:22 Slp: Rule evaluation message: The edition of the new node does not match the edition of the cluster.

If you closely look at the highlighted lines, you should be able to understand the issue. In case you didn’t understand – here is the issue.

SQL Server 2008 R2, which is installed is of Edition “Standard” edition (shown as an edition of clustered instance). The media, which were getting used while trying to perform AddNode operation was of “Enterprise” edition (shown as user specified).

Solution of the issue is very straight forward. She was supposed to use the media of the same edition, which was installed on Node1. Here is the screen where we can see edition of media.

The edition of the media is decided by the product key provided in the previous screen.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Adding a Master Data Services to your Enterprise – Notes from the Field #072

[Note from Pinal]: This is a 72th episode of Notes from the Field series. Master Data Services is one of the most important but very little explored feature of SQL Server. If you have been reading this blog, when it is about BI, I always take help from LinchPin People who are BI experts. I requested Reeves from LinchiPin people to help me answer this unique question.

In this episode of the Notes from the Field series database expert Reeves Smith explains why one should add Master Data services to our enterprise. Read the experience of Reeves in his own words.


With all of the focus on big data, I want to remind everyone not to forget about data governance and master data management in general. While working on various data warehouse projects, I have found this area to be often overlooked. I want to stress that if you are building a data warehouse or a reporting system, then data governance should be one of the key components of almost any project.

I wanted to start off with a couple of posts to show how you could implement SQL Server Master Data Services in you enterprise. Tyler Gramm wrote a white paper “Implementing a Phased Approach to Master Data Management” (https://msdn.microsoft.com/en-us/library/ff626496.aspx) that outlines taking small steps to start your first master data management project. In this series I will outline some steps to help bring reference data in SQL Server Master Data Services instance to start you off on your way.

Let’s first define data governance to better understand where master data management fits within that methodology. Data governance can be defined as a complete process with the ability to manage the data assets within the enterprise. Data governance also defines how you manage the assets that have the greatest business value for your organization’s day-to-day operations.

Where does master data management fall within a data governance approach?  Master data management is one area within the data governance process that focuses on management of reference and master data. Master data management is combined with a fair amount of business process and I will leave the process aspect for another author. An example of a business process within master data management would be the decision on which data source is considered the truth when a discrepancy in master data arises between two source systems.

In this post I will discuss the why you would want to consider Microsoft SQL Server Master Data Services. If Microsoft SQL Server is in your environment, integration will easy and Excel provides a great interface for the data stewards.

Note: SQL Server 2012 Master Data Services and SQL Server 2014 Master Data Services are nearly identical.

Master data management is as much a business function as a technical one. Master data is managed within SQL Server and often integrated within Extract-Transform-Load (ETL) processes, but the actual management and oversight of the data should be performed by the business. This involvement is provided with help from roles like a data steward. A data steward can been seen as a representative of the data, while the technical resources serve as the caretakers of the data. Accepting these roles is one of the first hurdles to overcome with any master data management project. The business will control most of the direction within the project, and the technical team will be in charge of system integration and support.

Setting up a Master Data Services instance within SQL Server is a fairly straightforward process after you’ve installed all of the prerequisites with Microsoft Internet Information Services (IIS). Figure 1 shows the SQL Server 2012 Master Data Services Configuration Manager. See https://msdn.microsoft.com/en-us/library/ee633744.aspx for more details on how to install and configure the server and backend processes used within the Master Data Services process.

Figure 1 – Master Data Configuration Manager

Note: You may configure the web services portion of Master Data Services install on another machine. It does not need to be on the same machine as the SQL Server database. Multiple licenses could apply with this configuration, so check with a licensing professional.

After setting up Master Data Services, you will want to give data access to the business to enable the actual management of the data. This is best provided by the installation of the Master Data Services add-in for Excel, which you see in Figure 2. This add-in will give the business users with appropriate knowledge of the data direct access to work with and support master data from their favorite data tool, Excel.

Figure 2 – Master Data Services Add-in for Excel

Note: The add-in for Excel does not support some functions, such as model creation and hierarchy. You will have to manage the unsupported functions from the web site.

Getting Started

One of the best projects for beginning a master data management implementation is to start with managing reference data. Reference data exists in every environment and is frequently managed within Excel spreadsheets without database durability and/or version control.  Moving this data into Master Data Services from an Excel spreadsheet is a fairly straight forward task that can be accomplished with little risk.

What’s Next

In the coming post, we will walk through moving reference data from an excel worksheet to an entity within a Master Data Services model.

At a high level the steps that we will discuss in the following post will be:

  • Create a model to store the reference data (completed on the MDS website)
  • Format the reference data in Excel
  • Click Create Entity button on the Master Data tab within Excel and select your data

Hope this helps you get started.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Backup on mapped drive failing with error – Msg 3201, Level 16, State 1 – Operating system error 3(The system cannot find the path specified.)

We are so used to taking backups and it can sometimes give us new experiences. One of the common messages I see are around taking backups on a share. If you have found this blog while searching for error message then you may want to look at below blogs as well if you are NOT dealing with mapped drive.

SQL SERVER – Error: Fix: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.) – Part 2

This time I will explain why you are getting this error while taking backup on a mapped drive. Here is the situation:

  1. SQL Server Service running under domain account.
  2. You have already mapped the drive in Windows and can see that drive in Windows Explorer.
  3. You are not able to see the drive which is mapped. Below is the screenshot of screen where you choose backup file name. If you try to take backup from SQL Server Management Studio (SSMS) you are not able to see the drive. As we can see, SSMS is only showing local drive (C and D)
  1. Backup command is failing with below error.

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘Z:\master.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

If we try to get details about the drives visible from SQL Server by Windows “dir” command then we see below.

xp_cmdshell 'dir Z:'

output
———————————————–
The system cannot find the path specified.
NULL
(2 row(s) affected)

Above confirms that SQL is not able to see Z drive, even though it is mapped on Windows. In case you get below error

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 72
The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.

Then you need to execute below to enable xp_cmdshell as its disabled by default due to security reasons. (Please turn off again once you done with the work)

EXEC sp_configure 'advanced', 1
RECONFIGURE WITH override
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH override
GO

We are seeing “The system cannot find the path specified.” because SQL Server instance as it is running as a service. To fix the error, we need to map the drive via SQL Server. Here are the steps.

-- Map the drive via T-SQL so that SQL can see it.
EXEC xp_cmdshell 'net use Z: \\BigPinal\SharedFolder'
GO

Now we can check mapped drive via T-SQL.

xp_cmdshell 'dir Z:'

You may want to add more parameters for net use. Refer Net use documentation

Once this is done, we can see that drive in SSMS as shown below

… and as expected, backup command would not fail.

Have you ever faced this situation such weird situations? What were your resolution steps? Let me know as we can learn from each other.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Marking Filegroup as ReadOnly with SQL Server

My love for writing about ReadOnly databases have pushed me to explore more about this topic. Based on previous blogs, few readers did ask me that they heard about marking filegroups as ReadOnly and they wanted to know how this can be done. This blog post is in continuation to this journey where-in we will learn about the Readonly attribute.

TSQL Script to mark ReadOnly Filegroup

Let us create the database first.

CREATE DATABASE [ReadOnlyDB]
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N'ReadOnlyDB', FILENAME = N'C:\Temp\ReadOnlyDB.mdf' , SIZE = 4024KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ReadOnlyDB_log', FILENAME = N'C:\Temp\ReadOnlyDB_log.ldf' , SIZE = 20480KB , FILEGROWTH = 10%)
GO

Let us next create an Filegroup which will get marked as ReadOnly.

ALTER DATABASE ReadOnlyDB ADD FILEGROUP ReadOnlyDB_FG;
GO
ALTER DATABASE ReadOnlyDB ADD FILE (
name = ReadOnlyDB_FG,
FILENAME = 'c:\temp\ReadOnlyDB_FG')
TO FILEGROUP ReadOnlyDB_FG
GO

Next we would like to create a table on this specific filegroup. This will form the base for our experiment.

USE ReadOnlyDB
GO
-- Creating our table
CREATE TABLE tbl_SQLAuth (id INT, Longname CHAR(8000))
ON ReadOnlyDB_FG
GO

Let us start our experiment in marking filegroups as ReadOnly. One of the important learning here is to understand, we cannot mark our PRIMARY filegroup as readonly.

-- Mark the PRIMARY filegroup read-only
ALTER DATABASE ReadOnlyDB MODIFY FILEGROUP [PRIMARY] READ_ONLY;
GO

Msg 5047, Level 16, State 1, Line 29
Cannot change the READONLY property of the PRIMARY filegroup.

We will be presented with the above errors. Now, let us mark the other Filegroup next.

-- Mark the Other filegroup read-only
ALTER DATABASE ReadOnlyDB MODIFY FILEGROUP ReadOnlyDB_FG READ_ONLY;
GO

The filegroup property ‘READ_ONLY’ has been set.

Once this succeeds, we cannot do anything with the filegroup. Let us do few tasks to check the errors.

-- Create a table on the Read_Only Filegroup
-- Explicitly call the FG where the table will be created.
CREATE TABLE tbl_SQLAuth_1 (id INT, Longname CHAR(8000))
ON ReadOnlyDB_FG
GO

Msg 1924, Level 16, State 2, Line 3
Filegroup ‘ReadOnlyDB_FG’ is read-only.

As you can see, we cannot create any tables on this Filegroup. If we try to insert any values into an table which is attached to this Filegroup, we will be presented with an error too.

INSERT INTO tbl_SQLAuth VALUES (1, 'SQLAuth');

Msg 652, Level 16, State 1, Line 4
The index “” for table “dbo.tbl_SQLAuth” (RowsetId 72057594040549376) resides on a read-only filegroup (“ReadOnlyDB_FG”), which cannot be modified.

As you can see, the Read_Only Filegroups are functioning just like our ReadOnly Databases which have been explained in our previous blogs. The best part of Read_Only filegroups is that, we get an flexibility to lock just a part of database files when compared to marking the whole database.

-- Clean up time
USE MASTER
GO
DROP DATABASE ReadOnlyDB
GO

As I wrap up this blog, I would want to know how many of us out there have used Read_Only filegroups in our environments. What are those scenarios you made these changes? I would like to learn from your experience. Let me know via your comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Interesting Observation with Currency Symbols

The currency symbols like $ and £ are implicitly treated as money value in SQL Server

If you run the following code

SELECT $

SELECT £

You can see that both of the above statements return the value 0.00

It is also possible to use them in arithmetic calculations

SELECT $+5

returns 5.00

SELECT 45-£

returns 45.00

How many of you know this?

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #011 – Script to Convert List to Table and Table to List

Now today’s interview question is really more of daily routine task for many of the developer. 11 years ago, when I went to my very first interview call. My interview was scheduled at 6 PM in one of the very well known corporation. The corporation was known for its first initiatives for social media. When I reached there at 6 PM there were many candidates and we all got the same task to complete. The task was as follows:

Task 1) Write a script to convert a list to a table

Task 2) Write a script to convert the table to a list

We all had two hours of time to complete the task. There were quite a few developers who had completed the task, but I had failed the interview as I was not good with SQL Scripting. After I return from the interview, I had decided to write scripts for my personal learning. I did not want to fail the interviews in the future if I was ever going to get that task.

Here are the answers for both the tasks.

Answer for Task 1: SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP

Answer for Task 2: SQL SERVER – UDF – Function to Convert List to Table

The irony of the life is that I never got the same task again in future. Here is the question back to you – can you guess the name of the organization which was based out of California and one of the early social media giants?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SCRIPT – SQL Listening on Dynamic Port?

With every release of SQL Server, sometimes I need to revisit some of the old blogs to see how they have enhanced. This learning experience is an ongoing process and the best way I learn is by doing a search on the blog from time to time. Long ago I had written a blog post to find the port number on which SQL Server is listening: SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Recently one of my blog reader sent email asking below.

I read your blog which talks about reading ERRORLOG and finding the port. But is there any way to find if that port is fixed port or dynamic port?

I know that the port value is stored in registry hive of SQL Server and this is instance specific. Here is the registry key for SQL Server 2014 instance running on my laptop.

If SQL is configured to use Dynamic Ports then TcpDynamicPorts would have the port value. In case of fixed port (which I have on my SQL Instance) then we would see TcpPort value.

To get same information using T-SQL, I have written a small script which would help you. It would detect dynamic port and static port as well.

DECLARE     @ServerName SYSNAME
,@InstanceID NVARCHAR(128)
,
@InstanceName NVARCHAR(128)
,
@tcp_port NVARCHAR(10)
,
@InstanceKey NVARCHAR(255)
SELECT @ServerName = @@SERVERNAME
SELECT @InstanceName = ISNULL((CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128))), 'MSSQLSERVER')
EXECUTE xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
,@key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
,@value_name = @InstanceName
,@value = @InstanceID OUTPUT
SELECT @InstanceKey = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\' + @InstanceID + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
,@key = @InstanceKey
,@value_name = 'TcpDynamicPorts'
,@value = @tcp_port OUTPUT
IF @tcp_port IS NOT NULL
SELECT 'SQL Server (' + @InstanceName + ') uses dynamic tcp port: ' + CAST(@tcp_port AS NVARCHAR(128))
ELSE
BEGIN
EXEC
xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'
,@key = @InstanceKey
,@value_name = 'TcpPort'
,@value = @tcp_port OUTPUT
SELECT 'SQL Server (' + @InstanceName + ') on ' + @ServerName+ ' uses static tcp port: ' + CAST(@tcp_port AS NVARCHAR(128))
END
GO

Generating such scripts takes time and the best way to learn these is by sharing. So do you have any other script similar to this that is worth a share for other readers? Please share them via the comments section.

Reference: Pinal Dave (http://blog.sqlauthority.com)