Brain teaser

0x57656C6C2D646F6E6520666F7220666967757
2696E67206F75742074686520736F6C7574696F
6E2E20546869732077617320746F2064656D6F6
E73747261746520746861742062696E61727920
6973206A757374206F6E65206F66206D616E792
07761797320746F2073746F726520646174612E
2054686520616E73776572206973203432203A29

xp_readerrorlog Msg 22004

When using xp_readerrorlog you may receive the error message:
 
Msg 22004, Level 12, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter Type

 
image1
Pre-SQL Server 2012 this does work.
image2
From SQL Server 2012 onwards you require the N prefix:
image3

SQL Server Product Key

Have you installed SQL Server and wondered how the product key appears to magically populate without you doing anything e.g.
 
image1
 
This info is obtained from the DefaultSetup.ini file on the installation media and the product key doesn’t auto populate if the file doesn’t exist.
 
image2

Changes to SQL Server configuration options

Continuing on from the previous post on sys.databases, DBAs may/may not have noticed the adds/removes/tweaks occurring behind the scenes as each version changes.
 
A nice one of note is the allowing the backup checksums by default from SQL Server 2014.
 
Below is a breakdown of sys.configurations changes since SQL Server 2005.
 
SQL Server 2008

Setting Change
Web Assistant Procedures Removed
access check cache bucket count Added
access check cache quota Added
affinity64 mask Added
affinity64 I/O mask Added
backup compression default Added
EKM provider enabled Added
filestream access level Added
optimize for ad hoc workloads Added
max text repl size (B) Maximum value changed from 0 to -1
min server memory (MB) Default value changed from 8 to 16

 
SQL Server 2008 R2

Setting Change
max degree of parallelism Maximum value changed from 64 to 1024

SQL Server 2012

Setting Change
awe enabled Removed
SQL Mail XPs Removed
contained database authentication Added
EKM provider enabled Is dynamic changed from 0 to 1
max degree of parallelism Maximum value changed from 1024 to 32767
max server memory (MB) Minimum value changed from 16 to 128
max worker threads Is dynamic changed from 0 to 1
max worker threads Maximum value changed from 32767 to 65535
remote login timeout (s) Default value changed from 20 to 10

 
SQL Server 2014

Setting Change
backup checksum default Added

 
SQL Server 2016

Setting Change
allow polybase export Added
automatic soft-NUMA disabled Added
external scripts enabled Added
hadoop connectivity Added
polybase network encryption Added
remote data archive Added

 

SQL Server vNext (CTP 1.3)
No changes at this time.

Continual Evolution of SQL Server sys.databases

With each version of SQL Server the list of new features grows and keeping pace with them as a DBA can be a challenge.
 
As an example, the addition of new columns in sys.databases since SQL Server 2005 has been slowly climbing:
 

SQL Server 2008/R2
is_cdc_enabled
is_encrypted
is_honor_broker_priority_on

 

SQL Server 2012
replica_id
group_database_id
default_language_lcid
default_language_name
default_fulltext_language_lcid
default_fulltext_language_name
is_nested_triggers_on
is_transform_noise_words_on
two_digit_year_cutoff
containment
containment_desc
target_recovery_time_in_seconds

 

SQL Server 2014
is_auto_create_stats_incremental_on
is_query_store_on
resource_pool_id
delayed_durability
delayed_durability_desc

is_memory_optimized_elevate_to_snapshot_on

 

SQL Server 2016
is_federation_member
is_remote_data_archive_enabled
is_mixed_page_allocation_on

 

SQL Server vNext (CTP 1.3)
is_temporal_history_retention_enabled

TSQL Date Time Formats

Extracting date time formats can be a challenge.

Below are some common formats which I see used regularly:

TSQL Format
SELECT CONVERT(char(8),GETDATE(),112) yyyymmdd
SELECT CONVERT(char(8),GETDATE(),108) hh:mm:ss
SELECT REPLACE(CONVERT(char(8),GETDATE(),108),':','') hhmmss
SELECT CONVERT(char(23), GETDATE(), 121) yyyy-mm-dd hh:mm:ss.fff
SELECT CONVERT(char(19), GETDATE(), 121) yyyy-mm-dd hh:mm:ss
SELECT CONVERT(char(10), GETDATE(), 121) yyyy-mm-dd
SELECT REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GETDATE(), 121),'-',''),':',''),' ','_') yyyymmdd_hhmmss
SELECT REPLACE(CONVERT(varchar(19), GETDATE(), 121),'-','') yyyymmdd hh:mm:ss

Discover Unicode Characters Available in SQL Server

The following query will help you look for that obscure report character, or possibly just stir your curiosity as to what is available via TSQL.

SET NOCOUNT ON;
-- table variable to hold results
DECLARE @t TABLE (code_number int,code_character nvarchar(3));
-- variable for numbers to check
DECLARE @i int;
--insert into @t
SET @i = 1;
WHILE @i < 66000
BEGIN
INSERT INTO @t (code_number,code_character)
SELECT @i, nchar(@i);
SET @i = @i + 1;
END
--get the results
SELECT * FROM @t 
WHERE 1=1
AND code_character IS NOT NULL
AND code_character <> '';

image1

SQL Server Premium Assurance

With Extended support for SQL Server 2008/R2 ending on 9th July 2017 Microsoft has announced a new Premium Assurance support offering.

The Premium Assurance will allow for Security updates and bulletins rated ‘critical’ and ‘important’ for an additional 6 years after Extended support ends.

image
(Source: Microsoft)

Eligible SQL Server products currently are:

  • SQL Server 2008/R2 Standard, Datacenter, and Enterprise
  • SQL Server 2012 Standard and Enterprise
  • SQL Server 2014 Standard and Enterprise
  • SQL Server 2016 Standard and Enterprise

    Key notes include:

  • SQL Server Premium Assurance is purchased as an Add-ons for an ‘Active’ Software Assurance on the SQL Server.
  • SQL Server Premium Assurance offering must be purchased for each SQL Server to be covered before the version of SQL Server goes out of support i.e to support SQL Server 2008 version, you’ll have upto June 2019 to purchase the offering.
  • Software Assurance must have been purchased using one of the four licensing programs and enrollments: Enterprise Agreement, Enterprise Agreement Subscription, Enrollment for Education Solutions, and Server and Cloud Enrollment. If not, then Premium Assurance will not be an option for you.
  • Existing Client Access Licenses (CALs) must be covered by an active Software Assurance license, but SQL Server Premium Assurance Add-on licenses for CALs are not required.
  • You may reduce licenses or drop the offering completely at the time of your Software Assurance renewal only.
  • When you purchase Premium Assurance dictates what you’ll pay as shown in the below table:

    image1
    (Source: Microsoft)

    Is it worth it? Possibly for shops that can’t move from SQL Server 2008/R2 and require piece of mind and/or to meet regulatory compliance.

What’s up with SQL Server 2016 Evaluation Edition?

I’m not sure if you have noticed, but when you install SQL Server 2016 Evaluation Edition you are offered to install one of the following free editions – Evaluation, Developer or Express.

image1

This raises the question why would you want to use Evaluation Edition?

  • Evaluation Edition has a 180 day expiration while Developer Edition has no expiration:

image2

  • Developer Edition has the same supported version upgrade paths as Evaluation Edition:

image3

Ref: https://msdn.microsoft.com/en-nz/library/ms143393.aspx

  • As per Developer Edition, the Evaluation Edition licensing indicates that Production workloads are not permitted:

image4

 

Now there may be a good reason(s) to use Evaluation Edition instead of Developer Edition to get you going, but I just can’t think of any at this stage.

 

Don’t ask why ‘SQL Server 2016 Business Intelligence’ is showing as an option :) Possibly it will be revived in some form as an upgrade path for existing SQL Server 2014 Business Intelligence users.