So long MCSE Server Infrastructure

As a sign of the times and the ever increasing cloud push, the MCSE: Server Infrastructure qualification has now been replaced with MCSE: Cloud Platform and Infrastructure.
 
Extract from MCSE: Server Infrastructure qualification link:
 
This certification will be withdrawn on 31 March 2017. When the certification is withdrawn, all individuals whose transcripts list this certification as active on or after 26 September 2016 will retain the active status.

T-SQL returning something from nothing

Occasionally you may need to return a value even if nothing exists.
 
In this very basic example, suppose you have an empty table i.e.

SELECT id FROM table1;

image1
Now say, you need to return the value 1 to the application if there is no data.
 
There is a multitude of ways of achieving this, and here is one:

SELECT COALESCE((SELECT id FROM table1),1) AS id;

image2

Perform Log backup for all non-simple recovery databases to NUL & then shrink the Log files

You sure can come across some interesting requests as a DBA.
 
In this case the request was to quickly perform a log backup on a Dev server for each database (50+) so a log file shrink to free disk space could be performed.
 
Some of the scenario included:

  • Point in-time recovery was not necessary
  • They didn’t want to break Always On
  • The Dev server had insufficient space for the log backups

The following did the trick to backup the log files:

DECLARE @sqlcmd nvarchar(MAX);
DECLARE @db nvarchar(256);
DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
 FROM sys.databases
 WHERE 1=1
 AND state_desc = 'ONLINE'
 AND is_read_only = 0
 AND recovery_model_desc <> 'SIMPLE'
ORDER BY name
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @db
WHILE @@FETCH_STATUS = 0 
BEGIN
 SET @sqlcmd = 'BACKUP LOG [' + @db + '] TO DISK = ''NUL'';'  
 EXEC (@sqlcmd )
 FETCH NEXT FROM item_cursor INTO @db
END
CLOSE item_cursor;  
DEALLOCATE item_cursor;

Next to reclaim the log file space the following was run:

DECLARE @shrink nvarchar(MAX);
DECLARE @db nvarchar(256);
DECLARE @file nvarchar(256);
DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
 SELECT DB_NAME(a.database_id),a.name
 FROM sys.master_files a
 WHERE 1=1
 AND a.type_desc = 'LOG'
 AND a.is_read_only <> 1
 AND a.database_id IN (SELECT b.database_id FROM sys.databases b WHERE b.state_desc = 'ONLINE' AND b.is_read_only <> 1)
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @db,@file
WHILE @@FETCH_STATUS = 0 
BEGIN
 SET @shrink = 'USE [' + @db + ']; DBCC SHRINKFILE(N''' + @file + ''',1) WITH NO_INFOMSGS;'  
 EXEC (@shrink)
 FETCH NEXT FROM item_cursor INTO @db,@file
END
CLOSE item_cursor;  
DEALLOCATE item_cursor;

Linux event logs file location

As a Windows DBA, the Windows Logs is the place to go for reviewing Application/System/Security events.
 
In the Linux world, the location is /var/log
image1
Once in the log folder you can list the available files using ls and to read a file you can use less filename i.e. less syslog
 
When using less to view a file, pressing h will show the many options available to you for scrolling, searching, exiting etc the file.
image2
Useful less commands include:
g – go to the start of the file
G – go to the end of the file
Spacebar – scroll a full screen
Enter – scroll line by line
/searchpattern – search forward for the pattern i.e. /fail
?searchpattern – search backwards for the pattern i.e. ?fail
q – exist less

SQL Server 2012 Mainstream Support Ending reminder

Time sure does fly and just a reminder Mainstream Support for SQL Server 2012 ends in just over 3 months on 11th July 2017.
 
Ideally any new SQL Server installs should be the newest, or as close to the newest version of SQL Server as possible, to get the maximum RoE.
 
With SQL Server 2016 SP1, a lot of Enterprise features are now included in the other editions, so is a great option. Something to be aware of though is that Microsoft is now requiring Software Assurance to be acquired for use of a DR instance. The use of one DR instance was usually included by default in earlier versions.

Task Manager alternative for Linux

Task Manager is extremely useful for Windows DBAs, however in the Linux world the use of GUIs may be few and far between.
 
To your rescue is the top command
 
Simply open a command/terminal prompt and run top
image
It may appear to be very basic, but the power is in the options available via simple key presses.
 
Pressing h on the keyboard will show the options available to you.
image2
So if for example you pressed c you would now see the full command/path information:
image3
To show CPU/Memory info simply press m
image4
To change the refresh rate press d and enter a new refresh rate
image6
If you need to kill a problem PID, you can use k
image5
Try out the various available options to get a real insight into the Linux server activity and press q once finished to close it.

Does the Windows firewall slow down SQL Server connectivity?

Discussions on whether to enable or to not enable the Windows firewall on internal SQL Severs can get quite heated.
 
This may require further testing, but initial testing shows that there is very little difference between having the Windows firewall enabled or not.
 
In a real simple test, a connection was attempted 30 times to a remote SQL Server with the Windows firewall enabled and another 30 times with the Windows firewall disabled.
 
The test consisted of a batch file recording the start/end times of the following command:
 

sqlcmd -S instancename -E -Q "SELECT @@SERVERNAME;"

 
Variations are expected due to SQL Server workloads, but never the less the times averaged out. Having the Windows firewall enabled was slightly faster with an average of 39.7 milliseconds compared to 40.8 milliseconds, which I was not expecting.
 
Below shows the milliseconds taken between the start/end of each test with the Windows firewall on and off:
image1

MSDTC firewall config on a SQL server

If you’ve worked with BizTalk, you’ll know that configuring the Windows firewall can prove to be challenging when it comes to MSDTC.
 
The below Inbound Rules are known to work on the SQL server and are configured using wf.msc:
 
1) New Rule -> Custom -> All programs -> Protocol type (TCP), Local port (RPC Dynamic Ports), Remote port (All Ports)
-> Next -> Next -> Next -> Domain -> RPC_Dynamic_Ports -> Finish
 
2) New Rule -> Custom -> All programs -> Protocol type (TCP), Local port (RPC Endpoint Mapper), Remote port (All Ports)
-> Next -> Next -> Next -> Domain -> RPC_Endpoint_Mapper -> Finish
 
Also, check the following service states:

  • COM+ System Application (Manual)
  • System Event Notification Service (Auto)
  • Distributed Transaction Coordinartor (Auto)