SQL SERVER – SSIS – Get Started with the For Loop Container – Notes from the Field #113

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Get Started with the For Loop Container.

Tim Mitchell SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113Linchpin People are database coaches and wellness experts for a data driven world. In this 113th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to get started with the FOR LOOP Container.


SQL Server Integration Services is equipped with tasks and containers to make it easy to design and maintain the flow of ETL: which logic should be executed, when should it be executed, and how many times should it occur. Most SSIS developers are familiar with the sequence container and the For Each Loop container, which can be used to group together tasks and execute the same logic a discrete number of times. In addition to these, there is a lesser-known but still very useful container for controlling logic flow: the For Loop container.

Simply put, the For Loop container executes its ETL logic zero to n times. It has three functions that control how many times the loop will be executed:

  • InitExpression: Used for the initial setting of a starting variable (such as setting a counter variable to zero).
  • EvalExpression: This is the expression that evaluates whether the loop should continue. Of the three functions described here, this is the only one that requires a value – the others are optional.
  • AssignExpression: This allows the use of an assignment expression, such as incrementing a loop counter.

For those with a programming background, this look very much like a for() loop statement in the C-derived languages. Functionally, it works in the exact same way as the for() loop, by continuing to execute the contained logic as long as the control condition remains true. This helps to draw contrast between the For Each Loop and the For Loop in SSIS. The former is list-based, and will execute for every item in the list supplied to it. The latter is value-based, and will execute as long as the EvalExpression is true.

In fairness, most ETL loads lean toward the list-based approach, but there are valid cases where a value-based approach is necessary. Some of those include:

  • Processing a fixed subset of data
  • Sampling for test or validation purposes
  • Forcing a “wait state” until some milestone is reached
  • Allowing the loop logic to be executed for some specified amount of time

Configuring the For Loop Container

As noted above, the only value that is required for the For Loop container is the EvalExpression. A very simple For Loop configuration is shown below.

113 1 SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113

The above supplies only the required value – a value of true to the EvalExpression. However, this is a very poorly configured For Loop, because the loop will continue executing indefinitely! True will always be true, so there is no logical end to this loop.

A more practical design pattern would use either an initialization expression, an assignment expression, or possibly both, to constrain the number of iterations. A simple example of this is shown below. I set up an SSIS package variable, typed as an Integer and named @vLoopCounter, with a default value of 0. In the For Loop settings, I’ve used the EvalExpression to check to see if this value is less than 10, and I use the AssignExpression to increment the @vLoopContainer value by 1 for every iteration of the loop.

113 2 SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113

This example works, executing any logic contained in the For Loop exactly ten times. However, this pattern is very static. What if I want to increase the value expression to let the loop run more than 10 times? I’d need to open the package and modify it. Fortunately, the configurations expressions allow for the use of both variables and parameters. Below, the package has a couple of updates: an initial value of 1 is set for the @vLoopCounter variable, and the static comparison in EvalExpression is replaced by using the package parameter @pMaxLoops for the maximum number of loops.

113 3 SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113

In the example above, the number of maximum loops can be specified at runtime, making for a more dynamic pattern.

The examples above show only an iteration based on the number of times the loop has run. Keep in mind when using the For Loop container, this logic can be based on any statement we choose: whether a particular set of files exist, how long the For Loop has been running, how many records have been processed, or some other custom metric specified in the expression. Even with a small number of inputs controlling how many times the For Loop container will execute, the possible uses for this are many, and can be as complex as needed.

Conclusion

The For Loop container provides another way to execute repeating logic in SSIS. By using an approach similar to the for() loop in structured programming languages, the For Loop container adds more ETL flexibility through a value-based iterative pattern.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

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

SQL SERVER InMemory OLTP: Understanding Optimistic Multi-Version Concurrency Concepts

InMemory is an awesome concept and I have been fortunate to learn about the same from my friends in the industry over blogs. It holds deep concepts and is sometimes tough to understand how the building blocks come together. I am not the only one doing the exploration, but a number of you also do and pass those learnings to me via your interesting questions. Thanks to each one of you who take a moment to ask me some of these questions that shake the fundamentals and make my understanding stronger. In the same lines, one of the readers wrote back to me after reading the MSDN on InMemory OLTP, what does the concepts of “Optimistic multi-version concurrency” really mean. On first thought, it looks simple, but the question was very loaded.

This blog is very much inspired by this question and I thought to pen this down as a blog with examples to show how SQL Server handles the same. We will look at various simple examples to illustrate the behavior for easy understanding.

Creating our basic DB and Table

I will do the basic structure from previous posts on what the template would be. This is a simple script to start:

USE [master]
GO
-- Below statement works only from SQL Server 2016
-- Change this appropriately if you are running in prior versions
DROP DATABASE IF EXISTS  [InMem_OLTP]
GO
CREATE DATABASE [InMem_OLTP]
ON  PRIMARY
( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf' , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'InMem_OLTP_InMemory', FILENAME = N'C:\Data\InMem_OLTP_mopt' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf' , SIZE = 20480KB, FILEGROWTH = 10%)
GO

-- Creating the table for experimentations
USE [InMem_OLTP]
GO
CREATE TABLE dbo.SalesOrder_inmem
(
order_id INT IDENTITY NOT NULL,
order_date DATETIME NOT NULL,
order_status tinyint NOT NULL,
amount FLOAT NOT NULL,
CONSTRAINT PK_SalesOrderID PRIMARY KEY NONCLUSTERED HASH (order_id) WITH (BUCKET_COUNT = 10000)
)
WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO
INSERT INTO dbo.SalesOrder_inmem VALUES
('1/1/2010',1,1000),
(
'1/1/2011',1,2000),
(
'1/1/2012',1,3000),
(
'1/1/2013',1,4000),
(
'1/1/2014',1,5000)

Understanding Optimistic part

Let me try to break the question into multiple parts and we will create the scenario that will showcase the optimistic part. The steps I am going to take has been explained in the table below:

Steps Session 1 Session 2
1 BEGIN TRANSACTION
2 INSERT INTO InMemory Table
3 Read from InMemory Table
4 COMMIT TRANSACTION
5 Read from InMemory Table

As you can see above, this is will be done in two sessions windows and we will switch between sessions for the demo.
-- Session 1
BEGIN TRANSACTION
INSERT INTO
dbo.SalesOrder_inmem VALUES
('1/1/2015',1,6000)

-- Session 2
SELECT * FROM dbo.SalesOrder_inmem

 SQL SERVER InMemory OLTP: Understanding Optimistic Multi Version Concurrency Concepts

As you can see, we are still seeing only 5 records and the 6th record for which the transaction is still open is not visible. This will be the state in our session 2 infinitely till the transaction in Session 1 finishes. You can see SQL Server has made a version and made sure our query on Session 2 is not getting blocked. Since we have taken an optimistic concurrency, the Session 2 is still able to query.

Let us Commit on Session 1 and do the query again on Session 2.

-- Session 1
COMMIT

-- Session 2
SELECT * FROM dbo.SalesOrder_inmem

 SQL SERVER InMemory OLTP: Understanding Optimistic Multi Version Concurrency Concepts

Now you can see, without doing anything, the additional record now pops up automagically here now.

Understanding Multi-Versioning better

The multi-versioning is not clear yet and clearly. So to make that point, let me go ahead an update a record and see the different versions in our session.

Steps Session 1 Session 2
1 BEGIN TRANSACTION
2 UPDATE InMemory Table
3 Read from InMemory Table
4 COMMIT TRANSACTION
5 Read from InMemory Table

Let us create the transaction to update the InMemory table. This is achieved as below:

-- Scenario 2
-- Session 1
BEGIN TRANSACTION
UPDATE
dbo.SalesOrder_inmem WITH (SNAPSHOT)
SET amount = 10000
WHERE order_id = 6

-- Session 2
SELECT * FROM dbo.SalesOrder_inmem

 SQL SERVER InMemory OLTP: Understanding Optimistic Multi Version Concurrency Concepts

The value of 10000 is NOT visible to the Session 2 till the transaction is committed on Session 1. This shows SQL Server’s capability to maintain multiple version for the records in an InMemory objects much more clearly.

Hope you were able to get a clearer understanding of how InMemory tables work in an optimistic concurrency model. Do let me know if you learnt something new or if I have missed anything that you would like to have more clarity on.

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

SQL SERVER – dbForge Object Search for SQL Server

SSMS is a nice tool for database developers. However, it is far from being ideal, and this is because some routine operations in SSMS are extremely inconvenient to perform.

Very often, developers face the challenge of searching a database for all occurrences of an object, column, variable, or search simultaneously all the objects, in which a search value occurs. If you happen to solve a similar problem, then you must know that this problem is not the most trivial and Ctrl + F will not help here.

Of course, you can write a quick metadata query:

SELECT SCHEMA_NAME(o.[schema_id]), o.name, o.type_desc, m.[definition]
FROM sys.sql_modules m
JOIN sys.objects o ON m.[object_id] = o.[object_id]
WHERE [definition] LIKE '%PersonID%'

However, not all developers know metadata structure … and why waste time writing a query, if you already have an excellent free plug-in that helps to effectively search the wilds of DDL.

Though dbForge Object Search for SQL Server has been released not so long ago, it occupied the place of pride in my gentleman’s set.

This plug-in impresses me with the simplicity of use — type a text in the search box and click Enter:

devsearch01 SQL SERVER   dbForge Object Search for SQL Server

All the search results are displayed in a table that supports filtering. When you select a respective object, its DDL is displayed below… but not as a simple text. The plug-in has a convenient syntax highlighting.

If you need to restrict your search, you can configure the filtering by object type. For example, we can search only within stored procedures and triggers.

devsearch02 SQL SERVER   dbForge Object Search for SQL Server

Additional filters allow you to search much faster.

dbForge Object Search for SQL Server does not cache the information between the searches and directly accesses metadata. For me, it’s definitely a plus, especially when in active development and continuously updating database schema — you don’t need to constantly press Refresh cache to get the proper search results.

If necessary, you can do a search on multiple databases at the same time:

devsearch03 SQL SERVER   dbForge Object Search for SQL Server

The plug-in supports navigation. Simply select the context menu command Find in Database Explorer, and you will automatically jump to the found object:

devsearch04 SQL SERVER   dbForge Object Search for SQL Server

When working with this plug-in, I discovered some pleasant things. For example, previous search queries are saved in the search history:

devsearch05 SQL SERVER   dbForge Object Search for SQL Server

If we talk about the object search, doing it in SQL Server Management Studio is rather inconvenient. Queries dealing with this task are inefficient and require deep knowledge of the SQL Server system objects. By contrast, dbForge Object Search for SQL Server does the task brilliantly.

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

SQL SERVER – 2016 IF EXISTS Functions on SQL Azure Databases and More

The experimentations of working with the next release of SQL Server is always fun and when I wrote the blog SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause, lesser did I know such capabilities are already existing with Azure SQL DB. When I was presenting at one of the local usergroups, I told most of the capabilities come to Cloud first, get test and the very reason that it scales without problem on cloud gives one the confidence that it would work in our enterprise. One of the attendees asked, has all features first introduced to cloud only?

That got me curious and I started to hunt to find is the rollout always to cloud first. I thought let me experiment first and see. I took my DIE (DROP IF EXISTS) post to see if I can run it on SQLAzureDB to know if it worked seamlessly. First to my connection to the latest database I created.

 SQL SERVER   2016 IF EXISTS Functions on SQL Azure Databases and More

As I was running the CTP2.0 of SQL Server 2016, I wanted to see what version was running on cloud already. It was a surprise that the production was already running the latest bits. It was a pleasant surprise and I know by the time you read this article and try out – maybe Microsoft would have upgraded to the latest bits too. So is the scale and agility that cloud brings to the table.

Now coming back to the core, I was now getting a feeling that my DIE (DROP IF EXISTS) code will work now perfectly. I went ahead with the following code and it executed just fine.

DROP TABLE IF EXISTS my_test
GO
DROP PROCEDURE IF EXISTS my_test_procedure
GO

Now, it was time to search for something interesting that I can do on cloud that was not available on-premise SQL Server. So I went to the documentation to search for something interesting. I found a new function DATEDIFF_BIG() that was interesting. Currently the DATETIME functions that we use with SQL Server cannot give the high precision values. Let me take a typical value of – number of nanoseconds in a year?

SELECT DATEDIFF_BIG(nanosecond, '2015-1-1 00:00:00.0000000', '2016-1-1 00:00:00.0000000')
GO

This returns “31536000000000000” which is not available with SQL Server 2016 on-premise version currently. It was a great learning to see the rate at which innovations happen on cloud. If you run this on an on-premise SQL Server – you will get the following error today.

Msg 195, Level 15, State 10, Line 1
‘DATEDIFF_BIG’ is not a recognized built-in function name.

I am curious to know, how many times have you wanted the higher level of precision when working with DATEDIFF functions? What are some of your usecases for the same? I would surely like to learn some from you. Do let me know via the comments.

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

Interview Question of the Week #056 – How to fix Installation Failure – Rule “Setup account privileges” Failed in SQL Server

Sometimes in the interviews I observe that users are able to answer all the theoretical questions correct but when it is about doing practical they fail. A while ago, when I was helping a large organization with interview, suddenly their DBA reported that one of their server has installation error. We found this as a great opportunity to test a new candidate and gave him problem to solve. He was successfully able to solve the problem.

Question: How will you fix the installation failure error setup account privileges error in SQL Server?

setup rule 01 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Answer:

When I clicked on “failed” hyperlink, here is the message.

setup rule 02 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Here is the text of the message.

—————————
Rule Check Result
—————————
Rule “Setup account privileges” failed.
The account that is running SQL Server Setup does not have one or all of the following rights: the right to back up files and directories, the right to manage auditing and the security log and the right to debug programs. To continue, use an account with both of these rights. For more information, see http://msdn.microsoft.com/en-us/library/ms813696.aspx, http://msdn.microsoft.com/en-us/library/ms813959.aspx and http://msdn.microsoft.com/en-us/library/ms813847.aspx.
—————————
OK
—————————

I always look at setup logs to see the exact issue. I opened “SystemConfigurationCheck_Report.htm” and below is the failure.

setup rule 03 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Then I looked into “Detail.txt” and searched for “HasSecurityBackupAndDebugPrivilegesCheck” as shown in above screenshot. Failure is listed below.

(09) 2016-01-28 19:58:11 Slp: Initializing rule      : Setup account privileges
(09) 2016-01-28 19:58:11 Slp: Rule is will be executed  : True
(09) 2016-01-28 19:58:11 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.FacetPrivilegeCheck
(09) 2016-01-28 19:58:11 Slp: Rule ‘HasSecurityBackupAndDebugPrivilegesCheck’ Result: Running process has SeSecurity privilege, has SeBackup privilege and does not have SeDebug privilege.
(09) 2016-01-28 19:58:11 Slp: Evaluating rule        : HasSecurityBackupAndDebugPrivilegesCheck
(09) 2016-01-28 19:58:11 Slp: Rule running on machine: PINALVM1
(09) 2016-01-28 19:58:11 Slp: Rule evaluation done   : Failed

As highlighted above, the account which was running setup was missing second one.

SeSecurity: Manage auditing and the security log

SeDebug: Debug Programs

SeBackup: Back up files and directories

To see the “Local Security Policy”, we can go to Start > Run > SecPol.msc or open it from Administrative Tools.

setup rule 04 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

“Debug programs” doesn’t have any user. By default, “Administrators” group is part of it which was removed by me during some hardening. Once I added default account, I have to restart the computer to get forward from that error.

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

SQL SERVER – Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’

I do apply patches to my SQL instances as and when they are released by Microsoft. This is important because I always feel keeping the bits uptodate is essential because I don’t want to get infected as I travel quite a bit. Since I play a lot with my SQL Server, there are more chances that things are going to break sooner than your production server. Here is one such incident.

I was trying to apply patch to my SQL instance and it went fine. But then I was NOT able to start SQL Server service. Here are the messages from SQL ERRORLOG.

2016-01-24 06:14:40.63 spid7s      Error: 537, Severity: 16, State: 3.
2016-01-24 06:14:40.63 spid7s      Invalid length parameter passed to the LEFT or SUBSTRING function.
2016-01-24 06:14:40.63 spid7s      Error: 912, Severity: 21, State: 2.
2016-01-24 06:14:40.63 spid7s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 537, state 3, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2016-01-24 06:14:40.64 spid7s      Error: 3417, Severity: 21, State: 3.
2016-01-24 06:14:40.64 spid7s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2016-01-24 06:14:40.64 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

I knew that I need to use trace flag 902 to bypass script upgrade mode and fix something. It was challenging to find what to fix. So I started looking for ‘sqlagent100_msdb_upgrade.sql’ which was found under “Install” folder in “C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL”. I started SQL using trace flag 902 as below

NET START MSSQL$SQL2014 /T902

Refer: SQL SERVER – 2005 – Start Stop Restart SQL Server from Command Prompt

Then I was able to connect to SQL Server because the problem script didn’t run due to trace flag. I ran the script manually and found below piece of code failing.

DECLARE @dataDirName NVARCHAR(520)
SELECT @dataDirName = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM MASTER.sys.master_files
WHERE (name = N'master')

script upgrade 01 SQL SERVER    Script level upgrade for database master failed because upgrade step sqlagent100 msdb upgrade.sql

Now, we need to figure out what’s wrong and how to fix it. So, I modified query to get details.

SELECT  DB_NAME(database_id) 'DB name', name 'logical name', physical_name
FROM MASTER.sys.master_files
WHERE (name = N'master')

script upgrade 02 SQL SERVER    Script level upgrade for database master failed because upgrade step sqlagent100 msdb upgrade.sql

Now we can see a problem. The problem is because we have two rows and master.mdf doesn’t exist in second row. It is failing because a test database had filename set correctly but it’s logical name was set as master.

script upgrade 03 SQL SERVER    Script level upgrade for database master failed because upgrade step sqlagent100 msdb upgrade.sql

To fix the problem I corrected the logical name of the user database which had been incorrectly set as master. Once this was done then, I stopped SQL and started it normally (without trace flag 902) and it was able to start successfully.

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

SQL SERVER – 2016 Error Reasons: Msg 10778, Level 16 Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported

When I was writing the blog SQL SERVER 2016 – InMemory OLTP support for Foreign Key, it felt like a great addition to SQL Server. I felt there must be something that I might be missing as the obvious. I was not wrong in that feeling too. One of my blog readers pinged to say they were getting a strange error that Foreign Key was not supported. This is how I figured out what was going wrong.

Pinal: Hi buddy.

Reader: Thanks for the ping.

Pinal: Not an issue. It is my pleasure.

Reader: As you know, based on your last week blog. I was experimenting something.

Pinal: Go on, anything related to this blog is something I am all ears.

Reader: I am getting an error.

Pinal: Which Blog are we talking?

Reader: It is the OLTP support for Foreign keys.

Pinal: Oh that one. That was a simple blog. What is the error?

Reader: It say it is not supported.

Pinal: Are you sure you are on a SQL Server 2016 version when you are trying that? It wouldn’t work on a SQL Server 2014 edition.

Reader: I am on the latest SQL Server 2016 version. But I was experimenting and got this error.

Pinal: What sort of experiment? What error are we talking?

Reader: The error states: “Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.”

Pinal: No wonder. It is quite possible. Now I get it. You didn’t run the script as-is but changed one of them to Disk based tables.

The reader had changed the blog SQL SERVER 2016 – InMemory OLTP support for Foreign Key, and edited the portion of creation of table. It was as below:

CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
);
GO
CREATE TABLE ProductSales
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY NONCLUSTERED HASH (SalesID) WITH (BUCKET_COUNT = 10000),
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
)
WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

As you can see the Products table was made as normal Disk based table while the transactions table was made as InMemory OLTP table. I am sure this is the strategy for most of the users wherein they will be creating normal tables for their masters and using the InMemory capability for the transactions. At the moment we will receive the error:

Msg 10778, Level 16, State 0, Line 3
Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint or index. See previous errors.

I am sure the SQL Server product team is looking into this and maybe in subsequent versions there will be a provision for the same. But how important are these capabilities in your opinion? Do you want relationship management from InMemory and Disk based tables? Do let me know via the comments.

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

SQL SERVER – Fixing Annoying Network Binding Order Error – Notes from the Field #112

ryanadams SQL SERVER   Fixing Annoying Network Binding Order Error   Notes from the Field #112[Note from Pinal]: In this episode of the Notes from the Field series database expert Ryan Adams explains a very critical error user receive when binding network error. Ryan is one guy who spends more time with real world issues with SQL Server than anything else. He has mastered the art of resolving complex errors and document them so easily that one can’t find anywhere else. In this blog post Ryan addresses a very interesting error related to binding network error. Read the experience of  Ryan in her own words.


One of the more common errors I see when setting up a Windows cluster (usually in preparation for a SQL Server Failover Cluster Instance or a SQL Server Availability Group) is regarding an incorrect network binding order.  You will see this presented as a warning in the Cluster Validation Report.  There are actually two different errors that I have seen with the first being very common and the second being more rare.

Error 1

Rule “Network binding order” generated a warning.

The domain network is not the first bound network.  This will cause domain operations to run slowly and can cause timeouts that result in failures.  Use the Windows network advanced configuration to change the binding order.

What this error means is that the network card used to connect to your domain network is not at the top of the network binding order.  The fix for this is usually pretty easy because we just need to go into Control Panel…Network and Internet…Network Connections and make the change.  Once we get there we need to identify which NIC card is connected to the domain network and that can be seen in the “Network Category” column shown in the screen shot below.  You’ll notice that I have labeled my connections Public and Private and they both show “Domain Network”.  If you are configuring a multi-subnet cluster you will see the exact same thing, but if your cluster is on a single subnet the Private network connection will show “Public Network”.  So in a single subnet it’s the one labeled “Domain Network” that you are targeting and in a multi-subnet cluster it’s your Public connection you are targeting.

112 1 SQL SERVER   Fixing Annoying Network Binding Order Error   Notes from the Field #112

In order to change the network binding order we need to go into the advanced settings.  Starting in Windows 2008 this option is hidden.  If you don’t see it hit ALT on your keyboard and the Advanced option pointed out in the previous screen shot will appear.  We need to select that and then go to Advanced Settings.  You will now be presented with the Advanced Settings box shown below.  In the screen shot you will see that my Public network is the second in the binding order and we need to move it to the top by selecting it and hitting the up arrow.  Click OK and go run Cluster validation again to see if it is resolved.

112 2 SQL SERVER   Fixing Annoying Network Binding Order Error   Notes from the Field #112

There is a chance that either your domain network was already at the top of the binding order, or you ran Cluster Validation again and it failed with the same error.  If that is the case then you either have a ghost network card (can be caused by NIC changes or driver changes) or the Microsoft Failover Cluster Virtual Adapter is bound before the domain network.  These adapters are not show in the GUI, but can be found in the registry and other places like the ipconfig /all command.

Unfortunately the network binding order in the registry uses GUIDs instead of friendly names, so we’ll have to do some translating to find and move the domain network to the top.  The first thing we will do is go figure out what the GUID of the domain network NIC is by running the following command from a command prompt.

WMIC Nicconfig Get Description, SettingID

You’ll remember that I renamed my NIC cards to be called Public and Private, but that’s the friendly name and not what will be returned from WMIC.  WMIC returns what is in the “Device Name” column from the very first screen shot above.  In my case it is called “Intel(R) PRO/1000 MT Desktop Adapter”.  You can see this pointed out in the screen shot below where we can see the output of WMIC in the command window.  Note that the GUID starts with A7.

112 3 SQL SERVER   Fixing Annoying Network Binding Order Error   Notes from the Field #112

Now we just need to open Regedit and head to HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage\Bind.  That’s right, we’re getting down and dirty now so make sure you know what you are doing and above all else backup your registry before you make any changes.  Mistakes in the registry can be costly and destroy a system so proceed with caution because from here on out the responsibility lies solely with you…not me.  In the screen shot above you can see that my Public Domain Network is next to the last in the list and we need it to be at the top.  As an aside, I have also pointed out where the Microsoft Failover Cluster Virtual Adapter is located since I see this listed above the Public network from time to time.

The fix here is to cut the GUID for the Public Domain Network that starts with A7 and paste it at the top of the list.  Now we can go run Cluster Validation and life should be good unless you get the second error we’ll talk about now.

Error 2

Note that the error message is the same error you got above.  However, it’s a completely different issue.  So let’s say you verified the above and that the domain network is the first in the list, but the error persists.  Go Look in the following file and search for “IsDomainInCorrectBindOrder” to find the warning in the log file.

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\"Newest Log File Folder By Date"\Detail.txt

Here is a sample of the section you are looking for.

Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.NetworkBindingFacet
NetworkBindingFacet: Looking up network binding order.
NetworkBindingFacet: Network: ‘Production Team’ Device: ‘\Device\{0BF4D354-E6E9-480C-91CF-DC598282C4C1}’ Domain: ‘LITWARE.COM’ Adapter Id: ‘{0BF4D354-E6E9-480C-91CF-DC598282C4C1}’
NetworkBindingFacet: Network: ‘Local Area Connection’ Device: ‘\Device\{4DB91193-72F1-4713-A938-EB73F27CFEC8}’ Domain: ” Adapter Id: ‘{4DB91193-72F1-4713-A938-EB73F27CFEC8}’
NetworkBindingFacet: Network: ‘Heart Beat’ Device: ‘\Device\{5AC63784-8088-40F7-93C8-37F9CD03D445}’ Domain: ” Adapter Id: ‘{5AC63784-8088-40F7-93C8-37F9CD03D445}’
NetworkBindingFacet: Network: ‘BackUp Network’ Device: ‘\Device\{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}’ Domain: ” Adapter Id: ‘{52AEDCB0-9E8E-4243-9D5D-ED86E602DF23}’
IsDomainInCorrectBindOrder: The top network interface ‘Production Team’ is bound to domain ‘LITWARE.COM’ and the current domain is ‘CONTOSO.COM’.
Evaluating rule : IsDomainNetworkTopOfBindings
Rule running on machine: Server1
Rule evaluation done : Warning
Rule evaluation message: The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configuration to change the binding order.
Send result to channel: RulesEngineNotificationChannel

The issue here is that the server is joined to the LITWARE.COM domain, but the current domain is that of the currently logged in user which happens to be CONTOSO.COM.  Another way to say this is that the server is joined to the LITWARE.COM domain, but you logged with a user account from the CONTOSO.COM domain to create the cluster.  From a domain perspective these are completely different domains that have been trusted and it’s possible that they are in different forests too, but again they are trusted.  Technically this configuration is correct as the public/domain joined network is indeed at the top of the list.  You have two choices here.  You can safely ignore this warning or you can log out and back in with a user in the LITWARE.COM domain.

If you are looking for more information around Clustering, AlwaysOn Failover Clusters, or AlwaysOn Availability Groups you can visit my blog at http://www.ryanjadams.com/category/sql-server/alwayson-ag/ for more articles.  Setting up clusters to support SQL Server is complicated, but can yield great benefits if done correctly.  If you’re setting up a new cluster or interested in having us take a look at your current systems we would be happy to work with you.  You can find more information on the services we provide and contact us on our website http://www.linchpinpeople.com/.

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

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

SQL SERVER – Add failover cluster node fails with “number of cluster nodes supported for edition”

A lot of my blog readers contact me via comments and I try my best to reply to all of them. Sometimes issue is so interesting that I ask them to contact via email. This blog is result of such an interaction.

Asif (name anonymized) was trying to Add third node to SQL Server failover cluster instance (FCI). He was able to add second node without a problem. But when he was trying to add the third node, there seemed to be an error. FCI related errors and troubleshooting are sometimes complex and I try to keep such interactions on face-to-face troubleshooting only. Here I possibly felt there was something fundamentally wrong in the understanding. He also told that this is enterprise edition media. So, I asked him to share the screenshot and setup logs:

AddNode 01 SQL SERVER   Add failover cluster node fails with number of cluster nodes supported for edition

Here is the snip from the setup logs.

(14) 2016-01-10 15:44:24 Slp: Executing rules engine…
(14) 2016-01-10 15:44:24 Slp: Start rule execution, total number of rules loaded: 18
(14) 2016-01-10 15:44:24 Slp: Initializing rule      : Number of cluster nodes supported for edition
(14) 2016-01-10 15:44:24 Slp: Rule is will be executed  : True
(14) 2016-01-10 15:44:24 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.NumberOfNodesFacet
(14) 2016-01-10 15:44:24 Slp: Rule ‘Cluster_NumberOfNodes’ edition Standard allows 2 cluster nodes.
(14) 2016-01-10 15:44:24 Slp: Rule ‘Cluster_NumberOfNodes’ detected 2 cluster nodes.
(14) 2016-01-10 15:44:24 Slp: Evaluating rule        : Cluster_NumberOfNodes
(14) 2016-01-10 15:44:24 Slp: Rule running on machine: SQLNode03
(14) 2016-01-10 15:44:24 Slp: Rule evaluation done   : Failed
(14) 2016-01-10 15:44:24 Slp: Rule evaluation message: This SQL Server edition does not support the installed number of cluster nodes. To continue, remove nodes and then complete cluster installation.
(14) 2016-01-10 15:44:24 Slp: Send result to channel : RulesEngineNotificationChannel

I saw something familiar, where it said “Standard” – now that looked basic validation has failed. I have asked to run below query in SQL Server Management Studio

SELECT SERVERPROPERTY('Edition')

OR share the ERRORLOG

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Here is what we saw in ERRORLOG

2016-01-18 14:55:48.430 Server       Microsoft SQL Server 2014 – 12.0.2548.0 (X64)
Jun  8 2015 11:08:03
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 10240: )

As I was guessing earlier, it is a Standard edition which allows only 2 nodes in FCI. So, I asked him to contact to the application team and get media for enterprise edition. He upgraded Standard to Enterprise and then he was able to add node.

If we closely look at setup logs, its clearly mentioned there. Have you ever encountered any such simple errors in SQL Setup and logs have helped you?

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

SQL SERVER – Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Some errors when encountered take most of us for a spin. In this category the error related to “Timeout” surely falls. If you are a web developer and receive the same there are a hundred combinations why this can possibly happen. The web results can sometimes lead us in completely opposite direction because we have not analyzed the root cause for this. I sincerely urge everyone when working with generic errors, look for specific symptoms and then use the trick of eliminating one after the other before a final solution can be arrived.

This is one of the most common error which you would hear from web developers. Here is a typical error raised shown on website.

timeout 01 SQL SERVER   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +83
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader() +12
System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +332

It’s very clear message that the timeout duration specified for the operation is completed but actual work didn’t finish. The error can come because of two kind of timeouts:

  1. Query Timeout: The default value for web application or .NET application is generally 30 seconds. If any request is in progress and it couldn’t complete within the timeout period, we would again see an error.
  1. Connection Timeout: The default value of connection timeout is generally 15 seconds. Within this time if connection can’t be made, we would see the error.

Next obvious question is – what should we do if we see such error?

Query timeout: The reasons a command/query runs longer than expected is commonly due to blocking or the need for query/index tuning or both.  A quick way to check for blocking to to run sp_who2 while the query is running.  The BlkBy column will show the SPID of the blocking connection if the query is blocked.  For slow running query, you may want to check the execution plan to verify that the statement is touching the rows which are needed.  For example, if your intent is to Select a single row but you see a scan operator, that is a strong indication that you need to perform index or query tuning or need to update statistics.

Here is the over-simplified repro of query timeout done via SQL Server Management Studio (SSMS). The default value of query timeout in SSMS in 0 (which is infinite) Tools > Options in menu would open below window where the value can be set, if needed.

timeout 02 SQL SERVER   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

For demo purpose, I have changed it to 10 seconds. In one query window we would run below command

USE tempdb
GO
CREATE TABLE foo (i INT)
BEGIN TRAN
INSERT INTO
foo VALUES (1)
GO

In second query window, we can run below command

SELECT * FROM tempdb..foo

and we can see below after 10 seconds.

timeout 02 1 SQL SERVER   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

It is important to note the error number. It is not an SQL Server error but error by client. The query waited for 10 seconds because we caused blocking. If we don’t set timeout value in SSMS, they query would run forever because default timeout is zero.

Connection timeout: This I can’t reproduce by management studio query easily. On a slow network, you can easily reproduce it by reducing the connection timeout to a lower value. In SSMS. You need to click on Options on login page as shown below:

timeout 03 SQL SERVER   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

and change the value in Connection Properties tab.

timeout 04 SQL SERVER   Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

On the same screen we can also set query timeout (shown as execution time-out)

If you are seeing same errors in your web application, then you need to check configuration file (normally known as web.config file). The same parameters (query and connection timeout) can be set in connection string of the application as well.

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