Finding SQL Server IP address and TCP port

Lots of blog posts talk about finding the IP address of the SQL Server instance. For most, this is about looking at sys.dm_exec_connections or selecting CONNECTIONPROPERTY(‘local_net_address’).

The problem with those techniquest is that they only work from a remote connection via TCP/IP.  Others talk about finding the IP address via xp_cmdshell and parsing the output of ‘ipconfig’.  The same goes for the TCP port, this is also usually found by the same methods, or by looking at the SQL Server service configuration in the registry.

I needed something the would always work, regardless of where the code was executed, and preferably secure and not too cluncky.

The following script will also use CONNECTIONPROPERTY, but will create a secondary connection via OPENROWSET to the local instance using TCP.  The key here is building the connection string containing ‘tcp:’ and the local servername @@servername.  The rest of the code is merely building dynamic SQL to get around OPENROWSET’s limitation of not being able to take variables as its parameters.

DECLARE @ip_address varchar(15)
DECLARE @tcp_port int 
DECLARE @connectionstring nvarchar(max) 
DECLARE @parm_definition nvarchar(max);
DECLARE @command nvarchar(max)
SET @connectionstring = N'Server=tcp:' + @@SERVERNAME + ';Trusted_Connection=yes;'
SET @parm_definition = N'@ip_address_OUT varchar(15) OUTPUT
 , @tcp_port_OUT int OUTPUT';
SET @command = N'SELECT @ip_address_OUT = a.local_net_address,
 @tcp_port_OUT = a.local_tcp_port
 FROM OPENROWSET(''SQLNCLI'', ''' + @connectionstring + ''', ''
 SELECT local_net_address = CONVERT(VARCHAR(15), CONNECTIONPROPERTY(''''local_net_address'''') )
 ,local_tcp_port = CONVERT(int, CONNECTIONPROPERTY(''''local_tcp_port'''') )
 '') as a'
EXEC SP_executeSQL @command
 , @parm_definition
 , @ip_address_OUT = @ip_address OUTPUT
 , @tcp_port_OUT = @tcp_port OUTPUT;

SELECT @ip_address, @tcp_port

I’d be interested to know if there are easier or more elegant ways, so leave a comment if you happen to know.

Have fun,

Bert.

Advertisements

Using “&” (Bitwise AND) operator in SQL Server

A quick example on using the “&” (Bitwise AND) operator in SQL Server.  This can be used to interpret status bits from an application. In this example I will use the status column from master..sysdatabases because it is easily available on SQL server so you don’t need to create any tables to test this example.

I’ve used 2 methods, but surely there will exist others…

First one uses a CASE statement, and is easy to read and understand:

SELECT 
 name,
 status, 
 CASE WHEN (status & 1 ) <> 0 THEN 1 ELSE 0 END as [autoclose],
 CASE WHEN (status & 4 ) <> 0 THEN 1 ELSE 0 END as [select into/bulkcopy] ,
 CASE WHEN (status & 8 ) <> 0 THEN 1 ELSE 0 END as [trunc. log on chkpt] ,
 CASE WHEN (status & 16 ) <> 0 THEN 1 ELSE 0 END as [torn page detection] ,
 CASE WHEN (status & 32 ) <> 0 THEN 1 ELSE 0 END as [loading] ,
 CASE WHEN (status & 64 ) <> 0 THEN 1 ELSE 0 END as [pre recovery] ,
 CASE WHEN (status & 128 ) <> 0 THEN 1 ELSE 0 END as [recovering] , 
 CASE WHEN (status & 256 ) <> 0 THEN 1 ELSE 0 END as [not recovered] ,
 CASE WHEN (status & 512 ) <> 0 THEN 1 ELSE 0 END as [offline] ,
 CASE WHEN (status & 1024 ) <> 0 THEN 1 ELSE 0 END as [read only] ,
 CASE WHEN (status & 2048 ) <> 0 THEN 1 ELSE 0 END as [dbo use only] ,
 CASE WHEN (status & 4096 ) <> 0 THEN 1 ELSE 0 END as [single user] ,
 CASE WHEN (status & 32768 ) <> 0 THEN 1 ELSE 0 END as [emergency mode] , 
 CASE WHEN (status & 4194304 ) <> 0 THEN 1 ELSE 0 END as [autoshrink] , 
 CASE WHEN (status & 1073741824 ) <> 0 THEN 1 ELSE 0 END as [cleanly shutdown] 
from master..sysdatabases

 

2nd one is more concise but less readable:

SELECT 
 name,
 status, 
 CONVERT(BIT, (status & 1 )) as [is_autoclose],
 CONVERT(BIT, (status & 4 )) as [is_select_into_bulkcopy] ,
 CONVERT(BIT, (status & 8 )) as [is_trunc_log_on_chkpt] ,
 CONVERT(BIT, (status & 16 )) as [is_torn_page_detection] ,
 CONVERT(BIT, (status & 32 )) as [is_loading] ,
 CONVERT(BIT, (status & 64 )) as [is_pre_recovery] ,
 CONVERT(BIT, (status & 128 )) as [is_recovering] , 
 CONVERT(BIT, (status & 256 )) as [is_not_recovered] ,
 CONVERT(BIT, (status & 512 )) as [is_offline] ,
 CONVERT(BIT, (status & 1024 )) as [is_read_only] ,
 CONVERT(BIT, (status & 2048 )) as [is_dbo_use_only] ,
 CONVERT(BIT, (status & 4096 )) as [is_single_user] ,
 CONVERT(BIT, (status & 32768 )) as [is_emergency_mode] , 
 CONVERT(BIT, (status & 4194304 )) as [is_autoshrink] , 
 CONVERT(BIT, (status & 1073741824 )) as [is_cleanly_shutdown] 
from master..sysdatabases



& (Bitwise AND) (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms174965.aspx

sysdatabases
http://technet.microsoft.com/en-us/library/aa260406(v=sql.80).aspx

Programatically create SSRS report snapshot

' Created by Bert Van Landeghem 16/01/2015
' How to use:
' -i scriptfile
' -s https://SSRS_server_url/reportserver
' -v REPORT_PATH="path to report for which to create a snapshot"
'
' Example command line:
' "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\rs.exe" -i C:\test\CreateReportSnapshot.rss -s http://reporting2012.localnet.be/ReportServer -v REPORT_PATH="/Lists/CentralPrint" 
'
' ReportingService2010 Methods (ReportService2010)
' [http://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010_methods.aspx]
Public Sub Main() 
 Try
 rs.Credentials = System.Net.CredentialCache.DefaultCredentials 
 
 Console.WriteLine("----------------------------------------------------------------")
 Console.WriteLine("Script to create report snapshot")
 Console.WriteLine("----------------------------------------------------------------")
 Console.WriteLine("Connected to server : {0}",rs.url) 
 Console.WriteLine("SSRS server version : {0}",rs.GetType()) 
 Console.WriteLine("Parameter REPORT_PATH : {0}",REPORT_PATH)
 Console.WriteLine("----------------------------------------------------------------")
 Console.WriteLine("")
 Console.WriteLine("----------------------------------------------------------------")
 Console.WriteLine("Listing report snapshots")
 Console.WriteLine("----------------------------------------------------------------")
 
 Dim Snapshot as ItemHistorySnapshot
for each Snapshot in rs.ListItemHistory(REPORT_PATH)
 Console.WriteLine("ID:{0} , CREATIONDATE:{1} , SIZE:{2}" , Snapshot.HistoryID, Snapshot.CreationDate, Snapshot.Size)
 next
 
 Console.WriteLine("")
 Console.WriteLine("----------------------------------------------------------------")
 Console.WriteLine("Creating new report snapshot")
 Console.WriteLine("----------------------------------------------------------------")
Dim Warnings As Warning()
 Dim Warning As Warning
Dim CreateItemHistorySnapshot_ReturnValue As String 
 CreateItemHistorySnapshot_ReturnValue = rs.CreateItemHistorySnapshot(REPORT_PATH, Warnings)
If Not (warnings Is Nothing) Then
 Console.WriteLine("Warnings occurred during creation of snapshot:")
 for each warning in Warnings 
 Console.WriteLine("WARNING: {0}",warning.tostring())
 Next
 
 else 
 Console.WriteLine("New Snapshot ID: {0}", CreateItemHistorySnapshot_ReturnValue)
 
 end if
Console.WriteLine("----------------------------------------------------------------")
 
 Catch e as Exception
 
 Console.WriteLine(e.ToString()) 
 
 End try
End Sub

Easy collection of system information from SQL Server in XML

Fairly easy, extensible and readable way to get system information from SQL Server for reporting without having to create a complex system.  The code in the Common Table Expressions (WITH…) are from the SQL Server DMV Diagnostics Queries by Glenn Berry.

 DROP VIEW server_config  
 GO  
 CREATE VIEW server_config (server_config) AS  
      WITH os_windows_info as  
      (  
           SELECT windows_release,   
                windows_service_pack_level,   
                windows_sku,   
                os_language_version  
           FROM sys.dm_os_windows_info   
      ),  
      os_system_info as   
      (  
           SELECT cpu_count AS [Logical_CPU_Count], scheduler_count, hyperthread_ratio ,  
                cpu_count/hyperthread_ratio AS [Physical_CPU_Count],   
                physical_memory_kb/1024 AS [Physical_Memory_MB], committed_kb/1024 AS [Committed_Memory_MB],  
                committed_target_kb/1024 AS [Committed_Target_Memory_MB],  
                max_workers_count, affinity_type_desc AS [Affinity_Type],   
                sqlserver_start_time, virtual_machine_type_desc  
           FROM sys.dm_os_sys_info WITH (NOLOCK)  
      )  
      SELECT  
           @@servername as instance_name,  
           getdate() as collection_date,  
           (SELECT * FROM os_system_info FOR XML path(''),TYPE) as 'hardware_info',  
           (SELECT * FROM os_windows_info FOR XML path(''),TYPE) as 'os_information'  
      FOR XML path(''),TYPE, ROOT('server_config')  
 GO  
 SELECT * FROM server_config   

The resulting XML in the will be like the one below, and can easily be parsed by Powershell or stored in a reporting database.

 <server_config>  
  <instance_name>*******\SQL2012DE</instance_name>  
  <collection_date>2015-01-09T14:26:23.510</collection_date>  
  <hardware_info>  
   <Logical_CPU_Count>4</Logical_CPU_Count>  
   <scheduler_count>4</scheduler_count>  
   <hyperthread_ratio>4</hyperthread_ratio>  
   <Physical_CPU_Count>1</Physical_CPU_Count>  
   <Physical_Memory_MB>8066</Physical_Memory_MB>  
   <Committed_Memory_MB>211</Committed_Memory_MB>  
   <Committed_Target_Memory_MB>2048</Committed_Target_Memory_MB>  
   <max_workers_count>512</max_workers_count>  
   <Affinity_Type>AUTO</Affinity_Type>  
   <sqlserver_start_time>2015-01-09T09:18:13.100</sqlserver_start_time>  
   <virtual_machine_type_desc>NONE</virtual_machine_type_desc>  
  </hardware_info>  
  <os_information>  
   <windows_release>6.1</windows_release>  
   <windows_service_pack_level>Service Pack 1</windows_service_pack_level>  
   <windows_sku>4</windows_sku>  
   <os_language_version>1033</os_language_version>  
  </os_information>  
 </server_config>  

Have fun!

Maintenance window boundary calculation

During the implementation of Ola Hallengren’s SQL Server Maintenance Solution, we had a requirement to be able to set different parameters at different times to be able to fit the work in the available maintenance window.

For example during the index maintenance part, we would set higher fragmentation thresholds during weekdays than during the weekend, since we have less time available.

Similarly, when running integrity checks, we use @PHYSICAL_ONLY=’Y’ during weekday maintenance windows, and @PHYSICAL_ONLY=’N’ during weekend maintenance windows.

The maintenace windows are not strictly aligned to the day boundaries: the weekend maintenance interval starts Friday at 20:00, and ends Sunday at 20:00. Jobs running at that time are allowed to finish, but the jobs starting after Sun 20:00 will receive the ‘weekday’ parameters.

To meet the requirements and to not add too much complexity, we use the number of minutes since the start of the week to determine the interval boundaries, so the check for which interval we’re in, is reduced to a simple datetime calculation.

In our particular case, Friday 20:00 to Sunday 20:00 means between 6960 and 9840

Example:

  1. Select the integer number of the minute at ‘2015-01-09 20:00:00.000’ (this Friday) after the start of that day’s week:

    SELECT DATEDIFF(MINUTE, dateadd(week, datediff(week, 0,’2015-01-09 20:00:00.000′), 0),’2015-01-09 20:00:00.000′ )

  2. Select the integer number of the current minute after the start of the current week:

    SELECT DATEDIFF(MINUTE, dateadd(week, datediff(week, 0,GETDATE()), 0),GETDATE() )

How we use it (simplified):

 DECLARE @Dynamic_FragmentationLevel1 int  
 DECLARE @Dynamic_FragmentationLevel2 int  
 SELECT @Dynamic_FragmentationLevel1 = CASE  
 WHEN DATEDIFF(MINUTE, dateadd(week, datediff(week, 0,GETDATE()), 0),GETDATE() )  
 BETWEEN 6960 AND 9840 THEN 5 ELSE 30 END,  
 @Dynamic_FragmentationLevel2 = CASE  
 WHEN DATEDIFF(MINUTE, dateadd(week, datediff(week, 0,GETDATE()), 0),GETDATE() )  
 BETWEEN 6960 AND 9840 THEN 30 ELSE 50 END  
 EXECUTE DBA_Tools.dbo.[IndexOptimize]  
 @Databases = 'All_DATABASES'  
 ,@FragmentationLevel1 = @Dynamic_FragmentationLevel1  
 ,@FragmentationLevel2 = @Dynamic_FragmentationLevel2  
 ,@LogToTable = 'Y'  

Reference table of some values:

Mon BETWEEN 0 AND 1439
Tue BETWEEN 1440 AND 2879
Wed BETWEEN 2880 AND 4319
Thu BETWEEN 4320 AND 5759
Fri BETWEEN 5760 AND 7199
Sat BETWEEN 7200 AND 8639
Sun BETWEEN 8640 AND 10079
friday 20:00 to sunday 20:00 – BETWEEN 6960 AND 9840