Microsoft Mumbai Tech Community meeting on 31 Jan 2015

A community is a social unit that shares common values and what better than technology as a binding force. We are very excited to announce the launch of the Microsoft Mumbai Tech Community. Our goal as part of this community is to:

 

  1. Knowledge sharing by our experts here at Microsoft
  2. Create awareness about new product and services offerings and features
  3. Build a cohesive community

In line with this aspiration, We are kicking off the Microsoft Mumbai Tech Community with 2 expert sessions. If you are in Mumbai, India, please let my colleague Mahendraprasad know (details below) that you will be attending!

 

Date 31 Jan 2015
Timing 10AM – 1PM
Address Microsoft Corporation India Pvt. Ltd.
Training rooms Oak / Juniper
4th Floor, ‘Windsor’,
off CST Road, Kalina,
Santacruz (East)
Mumbai, 400098
Contact MADUBEY at microsoft dot com

Here is what our team is planning to talk about:

 

Presenter Narendra Angane
Schedule 10:00 till 11:15 AM
Topic Introduction to Power BI
Abstract

Power BI for Office 365 is a self-service business intelligence (BI) solution delivered through Excel and Office 365 that provides information workers with data analysis and visualization capabilities to identify deeper business insights about their data. Will see how we can use the below components for data analysis and visualization:

  • Power Query
  • Power Map
  • Power View
  • Q&A

Presenter Mahendraprasad Dubey
Schedule 11:30 AM till 12:45 PM
Topic Evangelizing Data Collection Strategy
Abstract

Data collection has been nightmare. Several time either we can’t collect data as it has performance impact on server or we miss data that needs to be analyzed. Data collection has been made simpler. Let’s discover different way of collecting data.

  • XEvent Metadata
  • Extended Events Tutorial
  • Finding Your Events
  • Actions and Targets
  • Demo
  • Q&A

We are looking forward to hosting you at this event. Do RSVP your participation to MADUBEY at microsoft dot com.

Advertisements

NUL or NULL?

Ever since the old MS-DOS days, the Windows OS family has supported a special ‘null device’ – NUL. Notice the single ‘L’. This is a ‘reserved’ device identifier which you can use as a path. The special thing about this null device (NUL) is that it essentially swallows whatever is sent to it.

NUL and SQL

So what does this have to do with let’s say SQL? Many of us use the null device to test raw backup throughput. The correct way to do this is to specify DISK = ‘NUL’ in the backup command. In that case, you will see an entry similar to the below in the error log, and no physical destination file will be created:

2014-09-20 21:47:43.890 Backup       Database backed up. Database: master, creation date(time): 2014/09/18(16:33:26), pages dumped: 459, first LSN: 234:304:73, last LSN: 234:352:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘nul’}). This is an informational message only. No user action is required.

Now, in some cases, you might accidentally or unknowingly type in NULL (with the double L) instead of NUL. What that does is actually end up with a physical file called NULL Smile You can see proof of that here:

2014-09-20 21:47:03.480 Backup       Database backed up. Database: master, creation date(time): 2014/09/18(16:33:26), pages dumped: 460, first LSN: 234:208:128, last LSN: 234:280:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQL14\MSSQL\Backup\null’}). This is an informational message only. No user action is required.

Conclusion

So if you are using the NUL device in your tests, make sure you spell it as NUL (without the double L!)

Have a good weekend!

SQL 2012 Setup Rules – The ‘Missing Reference’

Troubleshooting SQL Server setup can be a ‘dark science’ at times. There is overall documentation on where to look, and some interesting articles on the Support website. But other times, you might be left clueless on the root cause of failures, or why you are receiving a specific error message.

Situation

For example, one of my customers was adding a node (running Windows 2012 and SQL 2012 SP1) to an existing cluster. They were receiving a specific message when adding the node: “You have selected a feature that is not supported on Windows Server Core. The supported features on Windows Server Core are: Database Engine Services, SQL Server Replication, Full-Text and Semantic Extractions for Search, Analysis Services, Client Tools Connectivity, Integration Services, and SQL Client Connectivity SDK.”

With a bit of probing we understood what was going on: the second node (this was part of a test setup) was setup as a Windows Core setup (no GUI) while the first node was a full edition. Now, we do NOT recommend such mixed setups but given that this was a test setup, we wanted to unblock them and skip the rule. But which rule? There is no documentation on the rule name (which is what the SkipRules parameter takes.)

The Ask

With a bit of looking in the log files, we found that the relevant rule name is ServerCoreBlockUnsupportedFeaturesCheck. But the question from my customer was two-fold:

  • Can we get a list of all the rules, proactively so that we can ensure some basic things are out of the way before we even start running setup?
  • In the worst case scenario, if we do need to skip a rule, which rule name should that be?

It turns out that in SQL 2008 R2, we have a good list of all the rules, neatly categorized into their groups. However, we do not have an updated version of that for SQL Server 2012.

The Answer

If we look at the C:Program Files (x86)Microsoft SQL Server110Setup BootstrapLog<timestamp>DatastorePackage.xml file you will find that it is a rich source of information on all the rules and their rule groups which would run during setup. Not all rules are always applicable; some are specific to scenarios like Failover Cluster installation, Upgrade etc.

But that in itself is not enough. For looking at more descriptive information on the rules, you have to look at the SystemConfigurationCheck_Report.htm file described in KB article 955396. The rule ‘name’ mentioned therein would be the one you would, for example, pass to /SkipRules.

I’ve also listed all the rules evaluated in SQL 2012 in an Excel sheet – I hope that this list will make up for the lack of official documentation (SQL 2008 R2 was the last time this kind of list was made available as I mentioned previously.) The spreadsheet is quite self-explanatory, and I’ve included a screen shot of an extract here:

image

Hope this helps you somehow! If you can share your experiences or have additional questions please do let me know!

Database size limitation in SQL Express

I would generally not blog about this kind of topic, except that my customer recently talked to me about it. According to his tests, he seemed to find that the total size limitation across all databases was 10GB. That did not sound correct to me, because it is quite clearly documented that the 10GB limit is per database.

I quickly tested and found out that the documentation is correct. It is only when an individual database is attempted to be created with a data file beyond the size limit of 10GB, that you get the error:

Msg 1827, Level 16, State 4, Server SOMECOMPUTERSQLEXPRESS, Line 1
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.

To summarize:

  1. 10GB is the per-database, data-file size limit
  2. If you have more than one data file in your SQL Express database, the size limit is applied to the total size across all such data files
  3. Log files are not subject to this limit

Managed Service Accounts (MSA) and SQL 2012: Practical Tips

One of the most common dilemmas for SQL Server administrators is whether they should use AD based domain users as the service accounts, or can they leverage the inbuilt accounts like Network Service etc. If your SQL Server instance is never going to participate in any cross-server contexts (such as availability Groups, Linked Servers, Log Shipping etc.) then you may very well be happy to use one of the inbuilt accounts or even better, in SQL 2012, the special ‘Virtual Account’ feature.

While these options relieve you from the overhead of periodically changing and syncing the service account password, they do impose the machine boundary and cause problems when you want to ‘jump’ across instances. So the classic solution has been to grit one’s teeth and ask for a domain user which will then be configured as the service account. However this brings the hassle of periodic password maintenance, and more importantly that causes downtime.

OR you can configure the SQL 2012 standalone instance to utilize the new Managed Service Accounts feature in Windows 2008 R2 and above. To do this, you follow the steps below.

Setup the MSA in Active Directory

First, create a new MSA in the AD using the PowerShell cmdlet. To do this, there are some simple considerations documented here, but the most important ones are to be an domain administrator, to be on Windows 2008 R2 or above and have the right PowerShell modules installed (Remote Server Administration Tools has a AD PowerShell module which you must install.)

Import-Module ActiveDirectory

New-ADServiceAccount –Name TestSQLMSA -Enabled $true

Next, associate the above MSA with the computer you wish to use it on. Note that a MSA in itself can only be used on one destination computer at a time. In this case, I will specify my lab computer named W2K8R2CN3.

Add-ADComputerServiceAccount -Identity W2K8R2CN3 -ServiceAccount TestSQLMSA

Install the MSA on the target server

Once this is done, you switch to the W2K8R2CN3 computer (which just happens to be a Windows 2008 R2 Core installation) and ‘install’ the MSA on the computer. Now, if you are on Windows 2008 R2 Core installation, to use the ActiveDirectory cmdlets you must effectively install the RSAT-AD-PowerShell feature, but directly using DISM:

DISM /online /enable-feature /featurename=ActiveDirectory-PowerShell

Once that is done, you can easily associate the MSA on the target computer:

Install-ADServiceAccount TestSQLMSA

Important: To do this correctly, you must be a domain administrator. If you run the Install-ADServiceAccount cmdlet and you are not a domain admin, it silently exits, but later when you try to change the service account and start SQL, you will receive an error:

“Error 1069: The service did not start due to a logon failure” and HRESULT 0x8007042d.

If you get those errors, have a domain admin logon to the target server and re-run Install-ADServiceAccount for you.

Change the SQL Service account

Last but not the least: you use SQL Configuration Manager to execute the service account change. An important note is that when specifying the ‘user name’ for the MSA, you must fully qualify it with domain name, and have a trailing $ sign as well. If you do not specify the $ you will receive the error message:

“Invalid parameter [0x80041008]”

As an example, in my test setup, here is how Configuration Manager looks:

image

Supportability questions

MSA and older SQL versions: Though you may be able to configure and / or use MSA with SQL 2008 R2 or older versions, it is officially not tested. If you notice, the documentation for SQL 2008 R2 service accounts is noticeably silent on this topic.

Group Managed Service Accounts (GMSA): As of Feb 2014, Group Managed Service Accounts are NOT officially supported with SQL 2012 Failover Clustered Instances. This is under review by the Product Group for future releases.

References

Please leave your questions, comments below! I’d be very happy to hear from you. Till next time, bye!

Top Support Solutions for SQL Server

One of the teams within the Customer Support organization at Microsoft has been busy aggregating information about the most common and useful solutions for many products, including SQL Server. You can view their work at this blog post.

The categories they cover in that post include:

  • Performance
  • Failover Clustering
  • Crashes and Exceptions
  • Backup & Restore
  • Connectivity

It is a good collection of key articles in one place. I encourage you to bookmark it!

‘Cannot resolve the collation conflict’ error message with temp tables

Scenario

Today my customer told me that he is facing an error message ‘Cannot resolve the collation conflict’ when executing an JOIN between two tables: a base table and a temporary table. They had recently created the database afresh from scripts and were facing this issue since. On the existing ‘good’ server the problem did not occur.

The only obvious difference was that the ‘good’ database and ‘bad’ database differed in collations.

It is a fairly common problem and one which has a simple solution, but it does need to be handled in T-SQL code to ensure that it works in all possible scenarios. Here is a simple reproduction of the issue.

Walkthrough

Firstly, let us check the SQL instance collation:

select SERVERPROPERTY(‘Collation’)
go

Case 1: Temporary tables without any specific column level collation specifier

It tells us that the instance is at the default: SQL_Latin1_General_CP1_CI_AS. Next, let us simulate the ‘bad’ database, which had a different collation from the instance collation, and create a table in that database.

CREATE DATABASE Ukrainian COLLATE SQL_Ukrainian_CP1251_CS_AS
GO

USE Ukrainian
GO

CREATE TABLE MyTab
(
    someval VARCHAR(50)
)
GO

SELECT collation_name
FROM sys.columns
WHERE name = ‘someval’
and object_id = object_id(‘MyTab’)
GO

As you can see, the column inherits the database collation, namely SQL_Ukrainian_CP1251_CS_AS. Next, let us simulate the actual issue:

CREATE TABLE #something
(
    somevaltemp varchar(50)
)
GO

SELECT *
FROM MyTab JOIN #something
ON someval = somevaltemp
GO

The error is reproduced:

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Ukrainian_CP1251_CS_AS" in the equal to operation.

This is interesting because tables created in TEMPDB inherit the collation of TEMPDB unless the collation is explicitly specified. A quick check from sys.columns in TEMPDB will confirm it:

SELECT collation_name, name
FROM tempdb.sys.columns
WHERE name = ‘somevaltemp’
GO

The result is the same as the instance / TEMPDB collation: SQL_Latin1_General_CP1_CI_AS.

Case 2: Temporary tables with collation specified for the column

Next, let us try to create the temporary table with a collation value identical to the database from which it is created:

CREATE TABLE #somethingexplicit
(
    somevaltempexplicit varchar(50) COLLATE DATABASE_DEFAULT
)
GO

We can also confirm the collation of the newly created column:

SELECT collation_name, name
FROM tempdb.sys.columns
WHERE name = ‘somevaltempexplicit’
GO

As you will see it is now SQL_Ukrainian_CP1251_CS_AS. Let us attempt the JOIN again this time against the new temporary table:

SELECT *
FROM MyTab JOIN #somethingexplicit
ON someval = somevaltempexplicit
GO

You will see that it succeeds without any issues.

Case 3: Temporary tables generated by a SELECT…INTO

Let us try a final test, this time with a SELECT INTO a temporary table:

SELECT someval AS somenewvalinto
INTO #tempselectinto
FROM MyTab

SELECT collation_name, name
FROM tempdb.sys.columns
WHERE name = ‘somenewvalinto’
GO

As you can predict, the columns of the temporary table inherit the collation from the base table which we are selecting from. So the collation is retained as SQL_Ukrainian_CP1251_CS_AS.

SELECT *
FROM MyTab JOIN #tempselectinto
ON someval = somenewvalinto
GO

The JOIN also succeeds in the final case.

Conclusion

If you are creating a temporary table from your database code, and if your database happens to use an explicit collation, it is your DUTY to specify the collation in the CREATE TABLE call for the temporary table. You can either explicitly specify the collation value or use DATABASE_DEFAULT clause.