MySQL – How to Generate Random Number

In MySQL, UUID() function returns Universal Unique Identifier that generates 36 characters long value which is 5 part hexadecimal numbers. If you want to create random password, you can make use of this function.

SELECT UUID() ;

Returns the string 85aeb064-8f73-11e5-85ef-02fcc4101968 (Note that this is random, when you execute you will get different value). As the total length is 36, you can make use of the result to get a random password with any length.

Suppose you want to generate 8 character length, you can generate it using

SELECT LEFT(UUID(),8) random_password ;

If you want to generate 10 character length, you can generate it using

SELECT RIGHT(UUID(),10) random_password ;

This way you can generate a random password. As UUID () returns the hexadecimal number with five parts separated by hyphen, your word will have numbers, alphabets and hyphen depends on the length you use.

There can be many ways of doing this, but this is the simplest one.

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

SQL SERVER – Install Error – The /UIMode setting cannot be used in conjunction with /Q or /QS

Doing repetitive tasks is something everyone hates. I am sure many of us have resorted to ingenious techniques to figure out doing the task efficiently. There is no standard tool or technique that everyone uses. Recently I was asked to install a SQL instance with same configuration on 10 servers. Who would like to log in to all those servers, run setup.exe and go through the same setup wizard? Luckily, the SQL Server setup allows to create a configuration file. The very thought of extracting it as configuration is something that made me jump out of my seat.

UIMode 01 SQL SERVER   Install Error   The /UIMode setting cannot be used in conjunction with /Q or /QS

I copied the file from above location and moved to a second server on C drive. This was the easy part because I know the exact replica of configurations are going to flow to the new servers. Then I ran the below command:

setup.exe /CONFIGURATIONFILE=”C:\ConfigurationFile.ini” /QS /IACCEPTSQLSERVERLICENSETERMS

This is a silent install that I am trying to do. The above command failed with below error which was strange to me:

The following error occurred:
The /UIMode setting cannot be used in conjunction with /Q or /QS.

Error result: -2054422500
Result facility code: 1420
Result error code: 28
Please review the summary.txt log for further details

I looked into the file and found below line.

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
UIMODE=”Normal”

I have added ; before that so that it is commented (you can remove that line also) and after that setup went fine and I was able to install it to nine servers in very short time. I also found that I can run setup remotely using PowerShell below command:

Invoke-Command -ComputerName xxx -ScriptBlock
{D:\SQL2012\setup.exe /ConfigurationFile=”D:\SQL2012\ConfigurationFile.ini”}

Have you ever used configuration file in your environments? What has been some of the automation techniques you used for installing softwares? Do let me know via comments below.

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

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)