Status of SQL Server Agent

SQL Server Agent is a Microsoft Windows service that executes scheduled jobs and DBAs need to ensure the Agent is running. There are several methods to know the status of the SQL Server Agent.

1) Check if the SQL Server Agent is running in Services.

    Control panel --> Windows Administrative Tasks --> Services

2) In SSMS (SQL Server Management Studio), go to Object Explorer, scroll down to find "SQL Server Agent". Right-click and check the properties.

3) By executing the following query

SELECT [servicename], [status_desc]
FROM   sys.dm_server_services
WHERE  [servicename] LIKE N'%SQL Server Agent%';

4) Also, another query to see if there are any processes in "sysprocesses" system table.

SELECT * FROM sysprocesses
WHERE program_name LIKE N'SQLAgent%'

5) Another query to find out if SQL Server Agent's status

EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent';
GO

6) Open the Microsoft SQL Server Configuration Manager from Configuration Tools and see the status of SQL Agent service. 

 

Please do let me know if you find any other method.

Server Principals Login Create and Modification Date

The certificate-based logins that start and end with double hash marks are stated below -

##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_PolicySigningCertificate##
##MS_SmoExtendedSigningCertificate##
##MS_AgentSigningCertificate##

By default, they are Windows-based logins and are enabled. If you want to look into both windows and SQL based logins, query against sys.server_principals as mentioned below.

USE master
GO
SELECT * FROM sys.server_principals
WHERE  name LIKE '%##%'

If you look into sys.server_principals for the certificate-based logins, you might confuse with the "create date" and "modify date" of those logins. The logins were created when SQL Server is installed however the dates are different than the installation date.

These logins will be dropped and recreated during the 'script level upgrade' which usually takes place after cumulative updates/patches or an unexpected restart of the active SQL node.

There won't be any change in the dates (since no logins will be recreated) when the services are restarted or routine failover takes place.

If you need more information on Server Principals, please click here.

Certificate Based Server Logins - SQL Server

The SQL and Windows logins information will be available in sys.server_principals along with the logins that were created from certificates when SQL Server is installed. However, these certificate-based logins are for internal system use only and will not be used to login to the system.

As per the documentation, these principal accounts do not have passwords that can be changed by administrators as they are based on certificates issued to Microsoft.

Use the below query to identify those logins.

SELECT * FROM sys.server_principals
WHERE  name LIKE '%##%'
              AND is_disabled = 0

If you want to know all the logins that have double hash marks (##), use the below query.

If you see there are 8 principals that are created by SQL Server for both SQL and Windows logins and based on configuration SQL based logins were disabled. As I stated earlier, these logins cannot be used to login to SQL Server instance since they do not have access. Please check below -

USE master
GO

SELECT a.name, a.is_disabled, b.hasaccess FROM sys.server_principals a
JOIN sys.syslogins b ON a.name = b.name
WHERE a.name LIKE '%##%'

The login ##MS_AgentSigningCertificate## is not disabled and has access to the system however one cannot impersonate a login mapped to a certificate/asymmetric key. 

These certificates are either be used as permission containers or to encrypt the data. If you want to see what permissions they have, use the below query-

SELECT a.name principal_name, b.class_desc, b.permission_name
FROM sys.server_permissions b
JOIN sys.server_principals a ON b.grantee_principal_id = a.principal_id
WHERE name LIKE '%Certificate%'
ORDER BY a.name

Please note that these are created by the system, and it should be not be deleted.

Have you ever observed the creation date and modify date for these certificate-based logins?

Click here to know more.

Difference between Local File System vs HDFS

In an operating system, file system is the strategy that will be used to keep track of files on a disk. It has its own method to organize the files on the disk or partition. 

HDFS will be deployed on top of the existing Operating system to bring its own file system method. This way, the system will have two different file systems at a time which we call "local file system" and HDFS.

There is a difference between the Local File System and the Hadoop Distributed File System (HDFS) and the difference is mainly because of the block size. 

The block size is 4 KB both in Windows and Unix local file systems. But the block size in Hadoop HDFS is 64 MB in the initial version and in later versions, it is 128 MB which is configurable. This impacts the disk seek. For a large file, there will be multiple disk-seeks in local file system due to its 4KB block size. Since HDFS maintains higher block allocation, the data will be read sequentially after every individual seek. 

Large files will be split into multiple chunks automatically, distributed and stored across various slave machines (aka. nodes) in HDFS. In the local file system, the files will be saved the way they are.

The machine with the local file system is physically a single unit. HDFS is logically a single unit.

These are the differences I found over a period of time. If you find any more, please do share with me. 

Hijri Date in SQL Server - with Islamic Month Name

This article focuses on converting the Gregorian date into an Islamic date along with Islamic month name in textual format. In SQL Server there are two methods to convert the Gregorian date to Islamic date, however, for Islamic month name we need to depend on our own code.

Either you can write a function or a stored procedure or you can simply put the month names in your query's conditional clauses.

First, let me show you the methods, SQL Server supports -

SELECT GETDATE() AS [GregorianDate],

       CONVERT(VARCHAR(23),GETDATE(),131) AS [Hijri date]

GO

SELECT GETDATE() AS [GregorianDate], 

       FORMAT(GETDATE(),'yyyy-MM-dd hh:mm:ss','ar') AS [Hijri date]

GO

In case you need to display the Islamic date in text format like "11 al-Muḥarram 1442" -

SELECT

CONVERT(VARCHAR(2), DATEPART(DAY, FORMAT(GETDATE(),'yyyy-MM-dd','ar'))) + ' ' +

CONVERT(NVARCHAR(30), CASE (DATEPART(MONTH, FORMAT(GETDATE(),'yyyy-MM-dd','ar')))

    WHEN 1 THEN N'al-Muḥarram'

    WHEN 2 THEN N'Ṣafar'

    WHEN 3 THEN N'Rabīʿ al-ʾAwwal'

    WHEN 4 THEN N'Rabīʿ ath-Thānī'

    WHEN 5 THEN N'Jumādā al-ʾAwwal'

    WHEN 6 THEN N'Jumādā ath-Thāniyah'

    WHEN 7 THEN N'Rajab'

    WHEN 8 THEN N'Shaʿbān'

    WHEN 9 THEN N'Ramaḍān'

    WHEN 10 THEN N'Shawwāl'

    WHEN 11 THEN N'Zū al-Qaʿdah'

    WHEN 12 THEN N'Zū al-Ḥijjah'

    END) + ' ' +

CONVERT(VARCHAR(4), DATEPART(YEAR, FORMAT(GETDATE(),'yyyy-MM-dd','ar')))

GO

Do let me know if you find any other way. 

Moving a table from one Hive database to another

This article is to let you know how to move a table from one database to another in Hive.

CREATE TABLE TestDB.emp 
AS
SELECT * FROM dbTest.emp;

The above statement will create a table (named "emp") in the database in which you want to move the table and import the data from the old one. You can keep your old table in the old database or you can simply drop it.

If you do not want to use the "create" statement, then you have a choice to alter the table location as mentioned below.

ALTER TABLE TestDB.emp RENAME TO dbTest.emp;

Hope it helps..!!

XML in SQL Server - Part-2

In the previous part, we learned how to use the default modes and how we can change the shapes of the XML model using the same available modes. We will learn how to read the XML file that is generated by SQL Server.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData

FOR XML RAW;

Once you execute your query, you will see the results in the result-pane, which can be saved as CSV file, however, it shouldn’t be the right way to save XML files that generated by SQL Server.

Click on the result from the result-pane. The entire XML document gets opened in a new window. Go to File and select “Save As”. It will prompt you the file-directory to save the file based on your choice.

Once the file is saved, open a new query window and type the below command –

SELECT CAST(XMLData AS XML)
FROM OPENROWSET(
       BULK 'D:\MyDocuments\blog posts\XML-SQLServer\test.xml'

       , SINGLE_BLOB) AS X(XMLData)

Please do let me know if you need more clarification.

XML in SQL Server

This is my second post on XML after 10 years. I was contented with the earlier post however I experienced more over these years and learned many things which I wanted to share with the current and future generations.

Let me begin with the basics.

XML is a file extension for an Extensible Markup Language (XML) represents information in a hierarchical (tree) structure in a simple text format. An XML document consists of XML elements and all elements in an XML document can contain sub-elements, text, or attributes. The hierarchical structure represented by an XML document starts at the root element and branches to the lowest level of elements.

There are several modes that change the shape of XML format while generating it through SQL Server using FOR XML clause.

Instead of reinventing the wheel, I would quote the MSDN information here for the descriptions with the examples I prepared.

Here is the data for the tryout.

VideoID VideoDesc vcVideoLocation
842735 SQL D:\Partition23\20200101\73899mpart
842736 Java   D:\Partition23\20200101\74900mpart
842738 XML D:\Partition23\20200101\74901mpart
842739 Hive D:\Partition23\20200101\74901mpart
842740 Sqoop D:\Partition23\20200101\74900mpart
842742 Impala D:\Partition23\20200101\73899mpart
842743 SQL D:\Partition23\20200101\74900mpart
842744 Hadoop D:\Partition23\20200104\74791mpart
842745 SQL D:\Partition23\20200101\74901mpart
842746 Hive D:\Partition23\20200101\73899mpart
842747 Sqoop D:\Partition23\20200104\74791mpart
842748 Impala D:\Partition23\20200101\74900mpart
842749 Spark D:\Partition23\20200101\74900mpart
842750 HBase D:\Partition23\20200101\73899mpart
842751 Scala D:\Partition23\20200104\74791mpart
842752 Hive D:\Partition23\20200104\74791mpart
842753 SQL D:\Partition23\20200101\74901mpart
842756 Impala D:\Partition23\20200101\74901mpart

SQL Code:

CREATE TABLE VideoData(VideoID BIGINT, VideoDesc VARCHAR(10), vcVideoLocation NVARCHAR(MAX))

INSERT INTO VideoData(VideoID, VideoDesc, vcVideoLocation) VALUES
(842735,'SQL','D:\Partition23\20200101\73899mpart'),
(842736,'Java','D:\Partition23\20200101\74900mpart'),(842738,'XML','D:\Partition23\20200101\74901mpart'),(842739,'Hive','D:\Partition23\20200101\74901mpart'),(842740,'Sqoop','D:\Partition23\20200101\74900mpart'),(842742,'Impala','D:\Partition23\20200101\73899mpart'),(842743,'SQL','D:\Partition23\20200101\74900mpart'),(842744,'Hadoop','D:\Partition23\20200104\74791mpart'),(842745,'SQL','D:\Partition23\20200101\74901mpart'),(842746,'Hive','D:\Partition23\20200101\73899mpart'),(842747,'Sqoop','D:\Partition23\20200104\74791mpart'),(842748,'Impala','D:\Partition23\20200101\74900mpart'),(842749,'Spark','D:\Partition23\20200101\74900mpart'),(842750,'HBase','D:\Partition23\20200101\73899mpart'),(842751,'Scala','D:\Partition23\20200104\74791mpart'),(842752,'Hive','D:\Partition23\20200104\74791mpart'),(842753,'SQL','D:\Partition23\20200101\74901mpart'),(842756,'Impala','D:\Partition23\20200101\74901mpart')

XML RAW:

Each row in the result set is taken as one element with your columns being the attributes. 

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML RAW;

If you look at the output, each row from the table is represented as a single element in XML and is also represented by the keyword <row>. As stated in the beginning, the elements can contain sub-elements, text or attributes; here the table’s columns are being treated as attributes. What if the keyword ELEMENTS mentioned after FOR XML RAW? Let’s check it out. 

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML RAW, ELEMENTS;

Now, each row element has column names as sub-elements. 

FOR XML RAW and FOR XML RAW, ELEMENTS are returning a shape that is slightly different.

XML AUTO:

Returns query results in a simple, nested XML tree. Each table in the FROM clause for which at least one column is listed in the SELECT clause is represented as an XML element. 

Each row of the table will be represented as an element by table name.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML AUTO; 

Let’s add “ELEMENTS” to it and see how the output changes.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML AUTO, ELEMENTS;

Table name as an element for each row and each element has column names as sub-elements

 

XML PATH:

Provides a simpler way to mix elements and attributes, and to introduce additional nesting for representing complex properties. Table columns are passed as child elements.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML PATH;

If you look at the outcome of “FOR XML RAW, ELEMENTS” and “FOR XML PATH” there is no difference at all. Both are returning the same elements and sub-elements. 

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML PATH, ELEMENTS; 

Again, the output is as same as “FOR XML RAW, ELEMENTS” and “FOR XML PATH”.

Look at the below example -

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData
FOR XML PATH('VideoInfo');

User can replace the default keyword “row” with any desired information. In the above example, since the data is related to Videos, I mentioned “VideoInfo” for the readability. It solves no other purpose.

Synopsis: RAW will return the table’s row as an element and column names being the attributes. AUTO will return the table’s name as an element and the element contains the entire row and the column names are the attributes. PATH returns the row as element and columns as child or sub-elements.

Now let’s go back to the data; the table contains video information such as on which topic the video is created and where the video is located. There is more than one video on the same subject and there are several videos on the same location. 

We can create different shapes apart from the above by using the existing modes, either clustered/grouped by topic of the video or by the location, similar to bitmap fashion. Look at the below to understand easily.

SELECT vcVideoLocation AS '@path', (
              SELECT VideoID AS [index], VideoDesc FROM VideoData t1
              WHERE t1.vcVideoLocation = t2.vcVideoLocation
              FOR XML PATH('VideoInfo'),TYPE
                                                          )
              FROM (
                      SELECT DISTINCT vcVideoLocation FROM VideoData) t2
                      FOR XML PATH('vLocation'), ROOT('data') 

“data” is the main element for the whole table data, the location is sub-element; and in each location, there are several videos that are being treated as attributes within the user-defined sub-element “VideoInfo”. Look at the results below.

In the above example, the data is grouped by “location”. Similarly, we can shape it by grouping by “Subject” (i.e. Video Description).

SELECT VideoDesc AS '@Desc', (
              SELECT VideoID AS [index], vcVideoLocation FROM VideoData t1
              WHERE t1.VideoDesc = t2.VideoDesc
              FOR XML PATH('VideoInfo'),TYPE
                                                  )
              FROM (
                      SELECT DISTINCT VideoDesc FROM VideoData) t2
                      FOR XML PATH('VideoType'), ROOT('data')

Output: 

Hope you find this article helpful.