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!

Advertisements

Data Quality Services (DQS) and Failover Cluster instances

One of my customers made the observation that it is not possible to uncheck the Data Quality Services component in the Feature Selection screen in SQL Server 2012 setup. The interesting thing was that she clearly recalled that earlier it was possible to select it (the default was unchecked), and we wondered what changed that it now mandated the selection of the DQS installation:

image

FYI, she was now running SQL Server 2012 with the Product Update enabled using the command line switches /Action=InstallFailoverCluster /UpdateSource=<path to SP1 EXE>. This means the setup support files are those from SP1.

Analysis

It then came to our attention that in CU1 there was a fix released to ensure DQS compatibility with failover cluster. The issue is documented in KB article 2674817.

Now as per the KB, there is no way to apply the fix (other than a re-install of the FCI Sad smile) to an already-installed FCI which had the DQS components previously selected (but which were otherwise not working due to the issue described in the KB.) So to prevent users from running into the same problem if they were ever to use DQS after the FCI installation, the product team decided that post CU1 the selection of DQS is mandatory.

The other angle on this is, what if you are never planning to use DQS, is this selection going to add services, databases or files which are unused? The good news is that the impact is not much; the selection of DQS in the above feature list just drops a DQSInstaller.exe into the SQL BINN folder, which then has to be (optionally) executed to actually complete the DQS server installation. So, if you never run the DQSInstaller.exe, it will not create the DQS databases on the FCI.

Conclusion

To summarize, though the feature cannot be unchecked, in reality it does not add too much to the footprint of the installed FCI. Hope this helps!

SQL 2014 In-Memory OLTP ‘Hekaton’: training videos and white papers

Personally, the feature I am most excited about in SQL Server 2014 is the In-Memory OLTP (codenamed ‘Hekaton’) feature. Since SQL Server CTP1 was released, a lot of my interactions with colleagues and customers have revolved around this new feature and the one question everyone has is, how do I get started? Well, here’s a readiness roadmap I have put together to help you!

Videos

Sunil Agarwal, Principal Program Manager from the SQL Server product team, has been one of the key people steering the work towards its eventual public unveiling in CTP1. I’ve been fortunate enough to interact with Sunil on a few occasions and I marvel at his passion and energy. He recently presented three sessions at TechEd 2013 North America, which are now available online, complete with slides and video!

A related set of deep sessions were delivered by Jos de Bruijn (also a key member of the Hekaton PM team) at TechEd Europe 2013. Jos is a PhD and his technical depth and passion to share information is clearly evident – take a look for yourself!

 

In-Memory OLTP Code Samples

Here are some useful samples from MSDN.

 

General Reading

 

Research Papers

All the inside scoops from the team which made it happen!

If you find more resources which you would like to share, please post a comment or contact me through this blog to let me know, I will gladly link them up in this roadmap!

The Top 10 Issues uncovered by the SQL Server Risk Assessment Program (SQLRAP)

Firstly – Happy New Year, everyone! In my job as a Principal Premier Field Engineer at Microsoft Services, I am a regional lead for a proactive risk identification program called the SQLRAP. For over 7 years now we have evolved this from a manually conducted ‘Health Check’ offering to a highly automated and comprehensive Risk Assessment platform. I have the honor of sharing the top 10 most common patterns (a.k.a. Issues) we uncover as part of the program – you can read more about this at our MSPFE team blog.

Extra T-SQL checks to complement SQL Upgrade Advisor

Background

More and more customers are looking to upgrade to SQL Server 2012, which offers some exciting features like AlwaysOn Availability Groups, ColumnStore Indexes and much more. As part of my job in the Microsoft Premier Field Engineering team, we are called upon by customers to assess their readiness to upgrade to the new version.

Normally, when we perform an upgrade readiness assessment, we also review the T-SQL code that the customer is running. This is done to uncover breaking changes and other patterns which might cause poor performance or such behavioral change post the upgrade. For breaking changes, we normally recommend customers to use the publicly available SQL Server Upgrade Advisor (SSUA) tool. However, not all breaking changes can be detected by the current SSUA toolset.

Stuff outside of SSUA scope

Here are some examples of T-SQL breaking changes which cannot be uncovered by SSUA. Some of these are boundary scenarios, but I feel it is important to list them so that you are aware of the issues. Also, some of these are known to the SSUA team but they would not incorporate checks for them in SSUA due to their ship timeline and priority ratings.

Issue Conflicting locking hints
Description A specific issue in SQL Server 2000 caused locking hints in a FROM clause for an UPDATE statement, to be ignored. While this bug was fixed in SQL 2005, it potentially gave rise to upgrade issues if the locking hint in the FROM clause was different from the locking hint for the UPDATE source table.
Example See this Connect bug for an example.
Recommendation If at all used, locking hints need to be consistent between the table source and the FROM clause of the UPDATE.

 

Issue Objects in the FROM clause must have distinct exposed names
Description In SQL 2000, identically named tables from different databases did not require an alias
Example This code will work in compatibility mode 80 while it will break on compatibility 90 and above:

select *
from DB1.dbo.sometab
join DB2.dbo.sometab
on DB1.dbo.sometab.name = DB2.dbo.name

Recommendation It is generally a good practice in any case to alias all tables in a JOIN. That is the recommended solution in this case as well.
Additional Reading You can read more about this at Nacho’s blog.

 

Issue Subqueries are not supported in GROUP BY clause
Description The GROUP BY clause cannot contain a subquery in an expression that is used for the group by list. In earlier versions of SQL Server, this was allowed. In SQL Server 2008, error 144 is returned.
Recommendation Be aware of this breaking change and revise the query logic accordingly.
Additional Reading There are some Connect articles which describe this. Read them here, here and here.

 

Issue SETUSER statement usage
Description SETUSER may not be supported in a future release of SQL Server.
Recommendation We recommend that you use EXECUTE AS instead.

 

Issue ORDER BY clauses in views
Description The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.
Recommendation Specify the ORDER BY clause only in the outermost query and not inside views.

 

Issue FASTFIRSTROW table hint usage
Description The usage of FASTFIRSTROW as a table hint has been disallowed in SQL 2012. We recommend that hints be used only as a last resort by experienced developers and database administrators.
Recommendation You can evaluate the query hint OPTION (FAST 1) instead.

 

Issue COMPUTE clause is not allowed in database compatibility 110
Description The COMPUTE clause generates totals that appear as additional summary columns at the end of the result set. However, this clause is no longer supported in SQL Server 2012.
Recommendation Use the ROLLUP operator instead.
Additional Info A Connect request has been logged to include this check in SSUA. Vote for it!

 

Conclusion

So, now you know the most common patterns which you need to check for in addition to what SSUA already uncovers. However let’s say you have a huge T-SQL code base and cannot do this manually. Our PFE team are working on some offerings: SQL Server Upgrade Assessment and also a T-SQL Patterns and Practices Review, which among other things will also cover the above deprecation / upgrade checks. If you need more information, you can contact me for more details.

Also if you have any comments, additional cases which are not detected by SSUA, please leave a feedback item in the Comments section below. I read each one of them!

Where can I download SQL 2012 Upgrade Advisor?

This post is a really quick one… Let’s say you are planning to check your SQL Server and T-SQL code for any potential breaking changes in SQL 2012. So you use Upgrade Advisor. Let’s say you want to download it, rather than get it from the installation media (the MSI can be found under ServersredistUpgrade Advisor path.)

If you monkey around with your favorite search engine (mine is Bing!) you may not find a download link which directly says ‘SQL 2012 Upgrade Advisor’. That is because the link to download SQL UA is actually under the SQL 2012 Feature Pack. Once you get to that page, look for SQLUA.MSI. Select the one as per the architecture of the machine where you are going to execute the tool (x86 or x64.) 

You may also need to install the Windows Installer 4.5 and the .NET Framework 4.0 if they are not already installed. Then install the SQLUA.MSI file… and you are done!

AlwaysOn Availability Groups Connectivity Cheat Sheet

Today we were discussing the AlwaysOn connectivity options, and the various combinations therein of Readable Secondary setting and the connection strings became very complex. So I have summarized all the possible outcomes in the pivot tables below.

For convenience, we have two tables. The first table covers the cases when the Primary Replica is set to allow all types of connections (i.e. those with and without the ApplicationIntent=ReadOnly attribute.)

Case 1: Primary Replica allows ALL connections

Readable Secondary Setting

 

Additional Connection Parameters

Initial Database

No

Yes

Read-intent

 

Through Listener

None

Non-AG

Primary

Primary

Primary

ReadOnly

Primary

Primary

Primary

None

AG

Primary

Primary

Primary

ReadOnly

Not allowed (5)

Secondary

Secondary

Direct to Primary

None

Not applicable / don’t care

Primary

Primary

Primary

ReadOnly

Primary

Primary

Primary

Direct to Secondary

None

Not allowed (4)

Secondary

Not allowed (1)

ReadOnly

Not allowed (4)

Secondary

Secondary

The second table covers the cases when the Primary Replica is set to allow Read/Write (i.e. those without the ReadOnly attribute.)

Case 2: Primary Replica allows only Read/Write connections

Readable Secondary Setting

 

Additional Connection Parameters

Initial Database

No

Yes

Read-intent

 

 

Through Listener

None

Non-AG

Primary

Primary

Primary

ReadOnly

Not allowed (2)

Not allowed (2)

Not allowed (2)

None

AG

Primary

Primary

Primary

ReadOnly

Not Allowed (3)

Secondary

Secondary

Direct to Primary

None

Not applicable / don’t care

Primary

Primary

Primary

ReadOnly

Not allowed (2)

Not allowed (2)

Not allowed (2)

Direct to Secondary

None

Not allowed (4)

Secondary

Not allowed (1)

ReadOnly

Not allowed (4)

Secondary

Secondary

For some cases, the access to the database is not allowed (either at connection time or at run time, when the database is tried to be accessed) with the following error messages:

1. Error message: The target database (‘salesdb’) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

2. Error message: The target database (‘SalesDb’) is in an availability group and currently does not allow read only connections. For more information about application intent, see SQL Server Books Online.

3. Error message: Unable to access the ‘SalesDB’ database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.

4. Error message: The target database, ‘salesdb’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online

5. Error message: Unable to access the ‘SalesDB’ database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.

This table should help you predict how the connectivity will behave in each of these combinations, and therefore setup your Availability Group replica properties correctly.

More information:

I hope you enjoyed this post as well. If you would like to see more, please leave a comment!