sql server – Page 2

ADS: TSQL Checker

Azure Data Studio has quite a few extensions that are very useful. Over the past six to eight months I have completed a number of posts on quite a few of these extensions. So far one of my favorite extension is the PoorSQL Formatter. It provides great assistance in the formatting of Transaction SQL code. What it doesn’t do, is help identify code that is not in line with best practices. This is where the TSQL Checker comes into play.

This extension was developed by Daniel Janick. Daniel is a Microsoft MVP from the Austin, TX area. He can be found at @SQLSME on Twitter.

You might be wondering exactly what does this extension check. According to the extension documentation this is what it checks:

“TSQL checks for hints, select * from options”

Of course “SELECT *” is a very well known problem in query design. Hints and options can also create significant performance issues as well. What exactly is a hint? According to Microsoft’s documentation a hint is something that will over ride the execution plan that the query optimizer might select for a query. Here is a link to Microsoft’s documentation on hints. Hints will act on the four DML statements, SELECT, INSERT, UPDATE and DELETE. Hints should only be used as a last resort and only by experienced developers or DBAs.

While this extension is still in preview mode, it can still be very useful. To install it you will click the “Install” button, green button below. This will appear when you are looking at the documentation of the extension. When you do, you will be asked to open the GitHub sight for this extension.

When you click open, you will be taken to the GitHub site for this extension. You will want to download the tsqlchecker-0.0.1.vsix file. It is highlighted below. When saving it, save it in a location where it will not be overwritten.

To install this extension, simply go to the File menu and then go to “Install extension from Vsix file”. While some extensions require a restart of Azure Data Studio, this extension does not.

The purpose of this extension is to help you identify code that is not inline with best practices. In order to do so, the extension needs to mark the offending code in some manner. The extension will do this one of three ways.

1. Highlighting the code with a box:
2. Highlighting the code in yellow:
3. Highlighting the code in red:

Let’s take a look at some of the code that the TSQL Checker will flag.

Note: This code was take from the Microsoft documentation. This really isn’t a post on what these things do, just how TSQL Checker flags them. Refer to the Microsoft documentation for more information on these.

SELECT *

MAXDOP

FORCESEEK

FORCESCAN

LOOP JOIN

MERGE JOIN

OPTIMIZE FOR

HASH JOIN

For all of these warnings, the developer has also added a popup that will provide additional information. When you float the cursor over the warning you will see somethings similar as you is in the image below.

I really like this extension. It reminds us of code that is less than ideal and should be reviewed. Remember, few if any of these hints are recommended for production use. If you run into a situation that you think these are part of the solution, just do your homework first. Research and see if there is a better way to address the problem.

Thanks for visiting my blog!!!

ADS: DB Snapshot Creator

Like many Azure Data Studio extensions, DB Snapshot Creator is designed to bring functionality into ADS that is not present by default. This extension was developed by Sean Price. As the name suggests, this extension can be used to easily create database snapshots. Before going too deep into this extension, let’s take a quick moment to go over what a snapshot is.

First of all don’t think of it as a backup. Think of it as a read-only copy of the database. As pages are modified for the first time in the source database, SQL Server will move the original unmodified pages to the snapshot. This process will allow users to see that snapshot that is a mirror of the database when the snapshot was created. You might be asking, why should I use a snapshot? There are a number of reasons, but the biggest in my mind is the ability to use the snapshot for reporting purposes.

What if an 8-K page is modified twice? SQL Server will only copy the page to the snapshot the first time it is modified. After that, the page will not be copied again.

In Microsoft’s documentation, found here, right at the beginning of the document MS makes one very important point.

“Database snapshots are unrelated to snapshot backups, snapshot isolation of transactions, or snapshot replication.”

To find if there are any snapshots you can use the code below. This code will not only tell you if snapshots exist, it will also return what database it is associated with and when it was created.

SELECT a.name AS ‘Snapshot Name’
, a.create_date AS ‘Snapshot Create Date’
, b.name AS ‘Source Database’
, b.create_date AS ‘Database Create Date’
FROM sys.databases a
INNER JOIN sys.databases b
ON a.source_database_id = b.database_id

This is what the results will look like.

Microsoft has some good documentation on Snapshots. Go here to read it.

How to Create the Snapshot in SSMS

This is pretty easy to do. Many times if you want to do something with a database you can right click on the database and easily perform the desired task. This could be completing a backup, shrink the database and restore the database. One task that is not present is Create Snapshot. Given this, you will need to use TSQL to create the snapshot. Note the file extension, it is not MDF or NDF.

CREATE DATABASE [AdventureWorks2014_Snapshot_02_27_2020_01] ON

(NAME = [AdventureWorks2014_Data],

FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Snapshot_02_27_2020_01.ss’)

AS SNAPSHOT OF [AdventureWorks2014];

Below is a screenshot of the files in the file system. As you can see, the file for the snapshot has a different extension and there is not a corresponding .ldf file.

How to Create the Snapshot in Azure Data Studio

The option to use TSQL is always present, even in Azure Data Studio. And just as in SSMS, when you right click on the database, Create Snapshot is not an option. This is where the DB Snapshot Creator extension can help. Something to keep in mind as we go over this extension, it is still in preview mode. Neither the documentation in Azure Data Studio nor the GitHub site have a great deal of documentation on this. However, there is an FAQ that answers three questions. These questions are below and were taken directly from the extension documentation.

To install the extension you will first need to download the .vsix file. When viewing the extension the in the Extension Marketplace, you can click the green Install button. This will take you to the Github where you can download the file.

Make sure the place this file in a location where it will not be deleted. Once downloaded, you can go to the File menu and to “Install Extension from VSIX package”.

Then simply find the file and it will install. Now when you right click on a database you will see a new menu item to create the snapshot.

By default, when you click the above menu item Azure Data Studio will write the code to create the extension, but not actually create it. The CREATE DATABASE statement earlier in this post was created by this extension. This code is also below.

CREATE DATABASE [AdventureWorks2014_Snapshot_02_27_2020_01] ON

(NAME = [AdventureWorks2014_Data],

FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Snapshot_02_27_2020_01.ss’)

AS SNAPSHOT OF [AdventureWorks2014];

However, if you would like to skip the step of having the code written, you can go to preferences and uncheck the box below.

While in SSMS, the snapshots are located in a folder as seen below.

In Azure Data Studio, the snapshots are located at the same level as the rest of the databases.

This extension makes the creation of a database snapshot very easy! And like all other extensions, it has a great price. FREE!!

Thanks for visiting my blog!!

How to Capture Failed Logins using Extended Events

As many of you know, a data breach is a big deal. Not only do they place your customers at risk, a data breach can also have a very negative impact on the reputation of the company you work for. As a DBA, I teach my students that we are the last line of defense against a breach. I feel that an important part of protecting the data is to investigate failed logins. However, in order to investigate them, we need to find the failed logins first. You may be asking, if investigating failed logins is important, how did I find them? There are few methods we can use, including reading the log, SQL Server Audit and using Extended Events. We will cover each of these including how to set it up and read the data.

This will be a three part series covering each of the methods to capture failed logins.

Find Failed Logins Using Error Log

Find Failed Logins Using SQL Server Audit

Find Failed Logins Using Extended Events

Extended Events

So far we have covered two methods of capturing failed logins in previous posts. Both are viable options, depending on what you are attempting to accomplish. The third option above is the first that will also work in an Azure SQL DB. This third option is Extended Events. Unlike SQL Server Audit, there is NOT a failed login event. We will need to use the Error_Reported event instead.

When looking at the image of the error log below, you can see the error number, severity and state of a failed login. These numbers will be critical when setting up the Failed Login Extended Event session.

This is the code needed to create the Extended Event session. Although, there are several targets that can be used for storage, I think that the file is the best target to use. You will need to change the path to one that is suitable for your environment. Notice the WHERE clause, the Error, severity and state all match the above image.

CREATE EVENT SESSION [FailedLogins] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username)
WHERE ([severity]=(14) AND [error_number]=(18456) AND [state]>(1)))
ADD TARGET package0.event_file(SET filename=N’C:\temp\FailedLogins.xel’)

Like the audits, the Extended Event session will need to be enabled before it can collect the desired data. To start a session, simply right click on the session and go to “Start Session”. By looking at the session in SSMS, you can easily tell of the session is enabled or not.

Once the session is started, SQL Server will create a .xel file in the destination location. Once you go to the location, you will notice something a bit unusual about the file names. Look at the huge number at the end. I have looked and looked and the only thing I can find is that this number is the number of seconds since some date in the 1600’s, but not sure if that is true or not.

Because this is an XML based document, it would be logical to think we can read the data we could simply open the document it in Notepad as you will see below. Well, that is not the case. If you try to open one of these files with Notepad, it will look something like this. Not exactly useful.

Well, if I can’t open the file with Notepad, how can I read the data. With Extended Events there are two methods. The first one is the easiest. In SQL Server Management Studio, navigate to the Extended Events and find the session. Then right click on the target and go to “View Target Data…”. Just as below.

When you view target data, it will look similar to below.

Once you are here you can view the data, sort it and even group it. One additional task you can do that I would like to mention is the ability to export the data. While Extended Events does not allow for the direct input of the data to a SQL Server database table, it be exported into a table using SSMS. Now that it is in a table the data is easier to analyze.

When viewing Extended Event data, a new menu item will appear in SSMS, Extended Events. At the bottom of this menu are the menu items that can be used to export the data. There are three options, .XEL file, Table or CSV file.

While view target data as described above is very useful, it would be something that would be very difficult to automate in any way, especially if you would like to move that data collected to a table. This is where the sys.fn_xe_file_target_read_file function comes into play. This function works very much like the sys.fn_get_audit_file function used to read SQL Audit files.

Below is the code you could use to read the XML based extended event file. Using the example we used earlier posts looking for failed logins with the SA account, this bit of code does the same.

This code breaks the reading of the data into a few steps. The first step pull the data into a temporary table with a column that has a datatype of XML. Then the second select statement breaks the XML into more readable columns that will allow us to use more traditional TSQL to read the data.

SELECT FailedLoginData = CONVERT(XML, event_data)
INTO #FailedLogin
FROM sys.fn_xe_file_target_read_file(N’C:\temp\FailedLogin*.xel’, NULL, NULL, NULL);

SELECT
EventDate = FailedLoginData.value(N'(event/@timestamp)[1]’, N’datetime’),
Message = FailedLoginData.value(N'(event/data[@name=”message”]/value)[1]’, N’varchar(100)’)

FROM #FailedLogin
WHERE FailedLoginData.value(N'(event/data[@name=”message”]/value)[1]’, N’varchar(100)’) LIKE ‘Login failed for user ”sa”%’
ORDER BY Eventdate DESC

DROP TABLE #FailedLogin

Of course you will need to update the path to the .XEL file to the path for your environment. When you run the above query, the image below shows what your data set should look like.

Now you can take the query and modify it so you can build some type of alerting.

Hopefully this can help you find failed logins.

Thanks for visiting by blog!!

How to Capture Failed Logins using SQL Server Audit

This will be a three part series covering each of the methods to capture failed logins.

Find Failed Logins Using Error Log

Find Failed Logins Using SQL Server Audit

Find Failed Logins Using Extended Events

SQL Server Audit

While using the Error log to capture failed logins is configured by default, using SQL Server Audit is not and will need to be configured. Before we get too deep into how to configure audit, let’s go over a few of the basics.

SQL Server audit is broken up into two parts, Audit and Audit Specification. The Audit can be summarized as the place where are we are going to store the data. While the Audit Specification can be viewed as what we want to capture and to be stored in the Audit location.

To set up an audit…the first thing we need to set up in the Audit itself. This can be found under Security.

To create an audit all you need to do is right click on Audits and go to New Audit. When you do you will see something similar to what is below.

While this is not a post about how to create an audit there are few properties that I think are important to cover.

The first property is “On Audit Log on Failure”. Depending on your environment and audit requirements, you may need to shut the server down if the audit is not working properly. That is what this property is intended to do. As you can see there are three options. You will just need to set it to something that meets the needs of your company.

The next property is “Audit Destination”. As expected, this is where the data collected will be stored. You have three options here: file, Security Log and Application log. You will then need to pick a local of the files, assuming you picked File for the destination. Next you need pick the max number of files and files size. As well as a few other properties that are pretty straight forward. Once you have set everything, click OK and now you have your audit.

What you don’t have is the Audit Specification. There are two locations an Audit Specification can be set up. One is at the server level and the other is at the database level. In this case because we are looking for failed logins, we will need to utilize the server level audit specification.

The create an Audit Specification, simply right click on Audit Specification just below the Audit item.

You will probably want to provide a more appropriate name and you will need to pick the Audit from the drop down box. Because of this, the Audit must be created before the Audit Specification.

Which brings us to the final piece of the pie so to speak. We need to identify what data we want to capture. For the purposes of this blog post, we are only looking for failed logins. Under the “Audit Action Type” you will need to pick “Failed_Login_Group”. Once you click OK…both the audit and audit specification have been created. You will need to check them though. When both an Audit and an Audit Specification are first created they are disabled. You will need to enable them by right clicking on them.

Once both the Audit and Audit Specification have been created. Before they will start collecting data, they will both need to be enabled. You can easily see in the image below how to tell if the Audit or Audit Specification are enabled or disabled.

Now to read the data we have collected. There are two methods that can be used to read audit data. The easiest was is to use SSMS and right click on the audit. Make sure you clicking on the Audit and not the Audit Specification. Remember, the data is stored in the Audit and the Audit Specification is used to identify what you want to capture.

When you right click on the Audit and go to “View Audit Logs”, you will see something like this.

This is very much like the other log viewers we use on a regular basis, job history and Error logs. The data can be filtered, searched and exported.

This is a great way to view the audit data, however what if I want to automate that process so I can build alerts around it. Well can’t really do that using the above method. However, there is a function, sys.fn_get_audit_file, that you can use. Here is a link to Microsoft’s documentation, click here.

If you look at the documentation you will see that there are three arguments for this function. However for our purposes, we really only need to use the first one. In the sample below you will see that the we are indeed only using the first argument. The other two we just put the word Default in. As far as the filename, you can put a specific file name, however, if there is a file rollover, the code will not work. Notice I placed the start of the file name and then and asterisk. In this case we will read from all files in the C:\Test location that start with “FailedLogin” and have a file extension of .sqlaudit.

sys.fn_get_audit_file (‘C:\Test\FailedLogin*.sqlaudit’,default,default)

Below is a more complete sample script. While the function returns many more columns, these are the ones I think we need. Notice the where clause is looking for an action_is that equals LGIF. This will be useful if you use the same audit to collect more than just failed logins.

SELECT event_time
, action_id
, succeeded
, server_principal_name
, server_instance_name
, statement
, file_name
, application_name
FROM sys.fn_get_audit_file (‘C:\Test\FailedLogin*.sqlaudit’, default, default)
WHERE action_id = ‘LGIF’
ORDER BY event_time DESC

The code below looks for failed logins using the SA account for the past hour.

SELECT server_principal_name
, COUNT(server_principal_name) AS ‘NumberOfFailedLogins’
FROM sys.fn_get_audit_file (‘C:\Test\FailedLogin*.sqlaudit’,default,default)
WHERE action_id = ‘LGIF’
AND server_principal_name = ‘sa’
AND event_time > DATEADD(HOUR, -1, GETDATE())
GROUP BY server_principal_name

Of course you will need to change the path to the file that matches yours. If you are using an Azure SQLDB, audit as defined above is not an option.

Thanks for visiting my blog!!

How do I Capture Failed Logins Using SQL Server Log

This will be a three part series covering each of the methods to capture failed logins.

Find Failed Logins Using Error Log

Find Failed Logins Using SQL Server Audit

Find Failed Logins Using Extended Events

SQL Server Logs

SQL Server logs is a good place to document failed logins. However, in order to utilize logs there is a server setting that must be configured properly. That settings is the Login auditing setting. This can be found on the server properties as seen below.

There are four options for this setting. The descriptions for each of these settings is very self explanatory. The default is “Failed Logins only”. Before changing this you should probably think about how much data will be added to the log. If too much data is added, that could make finding other entries a bit more challenging. If you change this settings, you will also need to restart the SQL Server service for it to take effect.

When you are capturing the failed logins, the entry in the log will look something like this.

There is some useful information here. First of all, the login name and client. This identifies the who and host the account came from. If the client is something other than the local machine, you will see an IP address there. This IP address should be looked at carefully and if possible, try to ping it to see what would come back. When you ping the IP address, if you add the “-a” switch, you will get the host name returned.

This can obviously be used regardless of the method of collecting failed logins. Another thing to consider if choosing to go this route, is the number of logs to keep.

If you right click on “SQL Server Logs” and go to Configure, you will see the screen below.

As you can see, there are two settings, Log Files count and Log file size. Both of these should be configured in a way that will allow SQL Server to keep the log data you need.

To read the data there are a few methods you can use. You could manually review the logs every day. This can be very time consuming if you have a large number of servers you need to check.

Another option is to use xp_ReadErrorLog. These extended stored procedure can be used to read two types of logs, error logs and agent logs. Since this is a post about tracking failed logins, we will only take a look at the error logs.

While this extended stored procedure has 7 parameters, for the purpose of this post we won’t be using them all. Below is a list of the parameters. The parameters in blue text are what we will use. However, as you can see, we can narrow the results even more by use the second string parameter and the two date parameters.

1. 1. Value of error log file you want to read: 0 = current, 1 = first archive log, 2 = second archive and so on….
  2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
  3. Search string 1: String one you want to search for
  4. Search string 2: This is also a string search criteria, however can be used to narrow down the search results more.
  5. Search from start time
  6. Search to end time
  7. Sort order for results: N’asc’ = ascending, N’desc’ = descending

To read the most current log we can execute the code below…

EXEC master.dbo.xp_readerrorlog 0, 1, ‘login fail’

Did you execute it? Probably a better question is…Did it work? The answer is no, it did not work.

Msg 22004, Level 12, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter Type

Completion time: 2020-02-18T19:34:16.3226538-06:00

In the SQL Server world we are used to using a single quote to identify a string. However, for this procedure when we use single quotes for the string, we get the above error. This is an easy fix, use double quotes instead of single quotes as in the code sample below.

EXEC master.dbo.xp_readerrorlog 0, 1, “login fail”

You can also add the letter N just before the first single quote.

EXEC master.dbo.xp_readerrorlog 0, 1, N’login fail’

When you execute either one of these, your results will look similar to below. If the results are not in the desired order, you can move the data to Excel or use the last parameter of the extended stored procedure.

With this, a process can easily be developed to capture the failed logins for a date range and place the data in a table for later analysis.

One of the things I look for are failed logins using the SA account. Over the years there have been several times where the increased number of failed logins using the SA account has helped us identify a bigger issue.

The code below can be used to capture the failed logins for the SA account in the past 24 hours.

DECLARE @start DATETIME = DATEADD(HOUR, -24 ,GETDATE())
DECLARE @end DATETIME = GETDATE()

EXEC master.dbo.xp_readerrorlog 0, 1, N’login failed for’, “‘SA'”, @start, @end

Hopefully this will help you find failed logins! Better yet, hopefully you don’t have very many!

Thanks for visiting my blog!!

How to connect a Database User to a Login

Over the years I have had to provide information about logins and database users, most of the time per a request of an auditor. Many times, this is very easy to accomplish because the login name matches the name of the database user account. If you look at the “New User” screen you can see that I am able to enter a different User Name.

Because of this, I can have a User Name that doesn’t match the Login Name. From an audit perspective this can create some confusion. More importantly, it can make it difficult to provide accurate information to the auditors when asked.

In order to connect a database user to a login we will need to use the SID, Security Identifier. When you create a login on a SQL Server, SQL Server creates a SID, sometimes. So what do I mean sometimes? Well, if the login is a SQL login, SQL Server will create the SID, however if the login is an Active Directory user or group, the SID will the be same as the SID in Active Directory.

In order to find the SID we will need to use Syslogins. There is quite a bit of information in this view, however for this purpose we really only need two columns, SID and Name.

SELECT sid
, name
FROM syslogins

If you run the above query your results will look similar to what is below. In this case we are going to use an account names “BlogAcct”.

In order to get the SID for a user account we would run the following query in the database with the user account. The results are just below it.

SELECT sid
, name
FROM sys.sysusers

If you thought the SID looks familiar you would be correct. The SID is the same as the login mentioned above. However, if you notice, the names are different. This login name is BlogAcct, while the User Account is Fred. Yet both have the same SID. So what does this mean? It means that the User Account Fred is mapped to the Login BlogAcct.

If you run this query in the database you are looking for the mapped logins, you will get a result set similar to the image below the query.

SELECT u.sid AS ‘DatabaseUserSID’
, u.name AS ‘DatabaseUserName’
, l.name AS ‘LoginName’
, l.sid AS ‘LoginSID’
FROM sys.sysusers u
INNER JOIN master..syslogins l
ON u.sid = l.sid

Notice that the SIDs match, but the names do not. If you were to run this code against a production database, you will probably see a much large data set. This would also return accounts that have matching names as well. If you want to only find the user accounts that have different names from the login, you will need to add a WHERE clause. Similar to below.

When you do run this, you will see something that might be unexpected. The SA login comes back as having the same SID as the DBO. This is showing that the SA login is mapped to the dbo user account for all databases.

Hopefully this might make it easier when auditors come knocking at your door looking for accounts with database access. This query will also work for AD groups as well.

Thanks for visiting my blog!

Why do I include Plan Guides in my Server Assessment

When I complete a server assessment there are a number of areas I look at. These areas include security, performance and configuration. Most of the specific items fall into one of these categories. In my opinion, a complete server assessment should not only return the state of the server but should also return any configuration settings that may have an impact on performance. Plan Guides fall into this area. They can have a negative impact on performance just a quickly as they can have a possible impact on performance.

I my over 20 years of working with SQL Server I have never seen a plan guide actually used in production. This doesn’t mean they are not being used, just that i haven’t seen it.

Before getting into why I look for Plan Guides, let’s take a few moments to go over exactly what a plan guide is.

Below is the definition from Microsoft:

“Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server 2019 (15.x). Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them.”

Basically Plan Guides allow you to utilize optimizer hints without modifying the source code. For example, Plan Guides allow for the use of a particular physical join type. The optimizer utilizes a Hash Match join and you feel that the query might perform in a more optimal manner with a Nested Loop, the Plan Guide will allow this to happen without changing any code.

While this really isn’t a post about how to create and use Plan Guides, I think it is important to have a basic understanding of them. This is a link to Microsoft that will provide more detail.

When using the Object Explorer, Plan Guides can be found under Programmability in each database.

When I execute the query below in the AdventureWorks2014 database the actual execution plan will utilize a Merge Join operator. This is without a plan guide to tell the optimizer to do anything different. Of course, as you can see, an optimizer hint is also not used in the query.

SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.Customer AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.TerritoryID = 5

When a Plan Guide is created, you will be asked to provide the desired hint. In the case below, the hint is to use a Nest Loop whenever the above query is sent to the server.

When the plan exists, the execution plan will include the use of the Nest Loop join. This is because the query that matched the one in the defined in the Plan Guide.

Something to keep in mind, Plan Guides are both case sensitive and space sensitive. If the plan guide includes the query below:

SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.Customer AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.TerritoryID = 5

And the following query is executed:

select *
FROM Sales.Customer AS c
INNER JOIN Sales.Customer AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.TerritoryID = 5

Notice that the SELECT keyword is not capitalized. This will force the plan guide to be ignored. The plan guide will also be ignored if there is a trailing space at the end of one of the lines.

Also, plan guides can also be enabled\disabled. This can be done by simply right clicking on the plan guide in Management Studio and going to “Enable” or “Disable”.

When to use Plan Guides

While I haven’t used Plan Guides I can see where they have the potential to improve performance of a particular query in certain situations. Many vendors don’t allow you to change the code of the stored procedures to add a query hint, not to mention this also may not be the best idea in the first place.

So why do a look for Plan Guides during the server assessment?

During the server assessment, I look for these more for awareness than anything else. If a particular query performs poorly, it could be because of the plan guide. If they do exist then it is important to determine if is helping or contributing to the poor performance. As mentioned earlier, if a Plan Guide does exist, you will also need to determine of it is enabled or disabled.

The code below will check every database and return a row for each plan guide found. In addition, it will return the query and whether or not it is enabled.

        CREATE TABLE #PGInfo
          ( DBName VARCHAR(128)
          , GuideName    VARCHAR(500)
          , CreateDate    DATETIME
          , ModifiedDate DATETIME
          , IsEnabled    BIT
          , QueryStatement    VARCHAR(300)
          , Type NVARCHAR(Max))

       EXECUTE master.sys.sp_MSforeachdb ‘USE [?] INSERT INTO #PGInfo SELECT
         DB_Name()
          , Name
          , create_date
          , modify_date
          , is_disabled
          , query_text
          , scope_type
        FROM sys.plan_guides ‘

SELECT *
FROM #PGInfo

        IF @@ROWCOUNT = 0
        BEGIN
         SELECT ‘No Plan Guides were found in any databases.’
        END

DROP TABLE #PGInfo

As stated earlier, looking for Plan Guides is more about awareness than anything else. If there is a performance issue with a particular query, knowing that these exist important.

Thanks for visiting my blog!

ADS: Simple Data Scripter

Moving data in a table from a production server to a lower environment such as a development server can be done a number of different ways. These include backup\restore, using SSIS to move the data, import\export wizard and scripting the data in SQL Server Management Studio. Azure Data Studio by itself doesn’t have a way to script the data in a table and it does not have the import\export wizard. This is where the Table Data Scripter extension comes in handy.

Like most extensions for Azure Data Studio, this extension is also in a preview state. As of the date of this post, it is currently at version 0.1.1 and is published by Sean Price. The documentation for this extension can be found here, Simple Data Scripter. In the documentation there are a few limitations identified

- - Not all data types are supported, specifically the binary datatypes like text, binary and varbinary
  - Has only been tested with SQL Server
  - Does not work on a view

Some extensions will add options under Preferences. This extension does not add any options. Given that, there is limited ability to configure this, which is OK, not really much of a need to do any configuration.

Just as a review, let’s take a look at how we can script the data in SQL Server Management Studio. After right clicking on the database and going to Tasks…Generate Scripts… When you do, a wizard will pop up and then after a few clicks on next, you will arrive at the screen below. The box highlights the “Types of data to script” option. With this there are three options, Data Only, Schema and Data and finally Schema Only.

When right clicking on a database in Azure Data Studio you will see limited options none of which are related to scripting data. This is also true when right clicking on a table in the database. Here are the options we see by right clicking on a table, as before, none of which are related to scripting data.

In order to script the data in Azure Data Studio we will need to install the Simple Data Scripter extension. This is a pretty straight forward and simple install. You will need to download the .vsix file. You can get to the GitHub to download the file from the documentation for the extension in ADS. You can install it under the File menu. While you will not be prompted to restart ADS when you install it, I usually like to restart ADS anyway. In this case, the extension doesn’t move to the installed extensions until after a restart.

Now that the extension is installed, you will see a new option when you right click on a table. This is another potential limitation of the extension, there isn’t an option to script all the data in the database. Well, at least I haven’t found it yet. But then again, I am not sure having a simple place to script all the data in one place if the best way to go.

When clicked the following screen will appear at the top Azure Data Studio. Initially when we look at it our first thoughts might be, what a bad query. But it does make sense, we are creating a script of all the data in the table.

If you click anywhere, ADS will cancel the query and you will see this pop up in the lower right corner and the above screen will disappear.

If you press enter Azure Data Studio will execute the query and generate the script. You will not see a result set but you will see the script. It will look similar to below. Notice the Create Table statement at the top, it is commented out. The table that is used for the example is named RentalCar. The script is working with a table named #tempRentalCar.

When the script is finished, the Task window will open up at the bottom of the screen. The top two are successful executions while the third failed, reason for the failure will be covered shortly.

You may be wondering if the query can be modified. The answer is yes, however not every type of statement will work. If you want to add a WHERE clause that works just fine. However, as the query become more complex it failed more frequently. I attempted to run a query with a join in it and it failed. That is the failure in the image above. The error stated “Query has no results to return”. However, the query works just fine when running in ADS or SQL Server Management Studio.

Here are some of the statements I have tried.

- - Query with a join – failed
  - Query with a sub-query – failed
  - Query with a WHERE clause and just one table – succeeded

When compared to the expectations, this extension works very well and I can definitely see a use for this.

Thanks for visiting my blog!!

Auto Delete Jobs????

Have you ever looked at something in SQL Server and wonder why it is there? That is what I think when I see this option in the SQL Server Agent job properties. I can not come up with any good reason of why you would want a job to delete itself upon completion. I even did a Google search and really didn’t find a good reason. However, if you know of a great reason of why you would want to enable this, I would love to hear about it.

If you are not familiar with this option, you can find it under the Notifications tab of the job properties.

Just like all the notifications, you have several options.

1. 1. When the job secceeds
  2. When the job fails
  3. When the job compeletes

When the job deletes, it will also take a job history with it. Basically there will be no evidence that the job ever existed. This is pretty straight forward, so you might be wondering why bring it up. Well, I see this setting as a potential danger, especially if you have a disgruntled DBA. While being a DBA is a great job, there are, from time to time things that can have a negative impact on how we feel about our job. Luckily the SQL Server community is filled with really great people.

We have a job that checks all of our jobs looking for any jobs that has this set. You can use the query below to get the necessary information. There is a column, delete_level in sysjobs that will allow us to get the information needed.

SELECT    name
  , delete_level
  , CASE delete_level
    WHEN 0 THEN ‘Not Set’
WHEN 1 THEN ‘Delete on Success’
WHEN 2 THEN ‘Delete on Failure’
WHEN 3 THEN ‘Delete on Completion’
    END AS ‘Delete Level Setting’
FROM sysjobs

So why do I think this is potentially dangerous? Image the disruptive code that could be written and then imagine that same code in a job that is scheduled to execute 6 months later. Now go one step further, all evidence of that job running is now gone. I really don’t want to give too much information, but I can think of several things that could be done that could cost the company a ton of money. This is why we check for this.

I also include this check on my server assessment scripts.

As I said earlier, if anyone can give me a good reason to have this set, I am willing to listen and will update this post accordingly. Giving proper credit of course.

My suggestion is to look for this!

Thanks for visiting my blog!!!!

How to View Agent Jobs in Azure Data Studio

As a DBA creating and managing SQL Server Agent jobs is an essential part of our day to day activity. We have always done this via SQL Server Management Studio or Transact SQL. Both of these are still great options for managing jobs, but what about Azure Data Studio. Unfortunately, the management of agent jobs is not part of the native functionality of Azure Data Studio. If you look at the screenshot below you will see that the SQL Server agent is not available. So does this mean that Azure Data Studio isn’t the proper tool for the management of agent job? The answer to this question is a classic DBA answer, it depends. If you do not install an extension you will not be able to manage jobs.

Before going any further let’s take a few minutes to go over what an extension is. As the name might imply, it extends something. In this case Azure Data Studio has it’s functionality extended to do something that is normal cannot do. An example of this would be the ability to manage SQL Server Agent jobs. Since Azure Data Studio requires an extension to manage jobs, what extension would need to be added? The answer is the Admin Pack for SQL Server extension. There is a SQL Server Agent extension as well, this is part of the Admin Pack for SQL Server. While this extension does more than just job management, we will focus mostly on how manage Agent jobs using Azure Data Studio.

Here is a list of some of the functionality of the Admin Pack for SQL Server extension.

- Agent Job management
- Import file wizard
- Deploy and extract dacpacs
- Deploy and extract bacpacs
- Browse Extended Events.
- Other functionality as well

As stated earlier, we will need to install the Admin Pack for SQL Server extension. So where do we get it? Since it was created by Microsoft, you will be able to get it right from inside Azure Data Studio.

Before installing the extension, we should probably check to see if it is installed already or if it has been disabled. If you click the icon in the green box below, the extension Marketplace will open up. If you see the Admin Pack for SQL Server under the Recommended block, it is not installed. If you see it under Disabled, the extension has been disabled.

List of disabled extensions can be found in the following location. Notice that the extension has been greyed out.

Another way to determine if the extension has been installed is to see if the SQL Agent appears as an option when you are managing the server level. As in the image below the Agent will be present when managing the server. If you do not see SQL Agent, it means either it is not installed or has been disabled. If it is not installed you can go to the MarketPlace and install it from there.

Now that we have confirmed the Agent extension is not only installed, it is also enabled, we are ready to manage the jobs on the server. Let’s do a quick inventory of what a typical DBA would do with jobs. All of these can be completed with Azure Data Studio, however there just a few minor limitations that will be discussed later in this post.

- - Manage jobs history
  - Manage operators
  - Manage alerts
  - Manage proxies

To get to the Agent job information in Azure Data Studio, you will need to right click on the server and go to “Manage”. As seen below.

Once you do, you will not only see the SQL Agent but also other Extensions you may have installed in Azure Data Studio.

In the above image, you will see that not only do I have the Admin Pack for SQL Server extension installed, but I also have the extension for sp_whoisactive and a few others. All of which I would recommend that you install.

When you are finally in the proper place to manage the jobs, you will see the same items you will see when using SQL Server Management Studio. You will of course see the job name, last run, enabled status, category and a few others. These are the same items we have been working with for years in SSMS. In the green box in the image below you will be able to click each item and you will be taken to the management area for jobs, alert, operators and proxies.

As you can see above, you can sort by any column you would like and it also displays information about the failure if the last execution was a failure. In addition to sorting, when you click the down arrow in the column header, you will also have an option to filter the column, similar to Excel. In the red box above is something that is pretty cool. Each bar represents one of the last five job executions. If you click on the of bars, you will be taken to the details of that execution. The image below is an example of what you will see.

When looking at the jobs you will see the button we can use to create a new job. When creating a new job, all the same information is requested so it is pretty easy to figure out.

There are a few differences though when creating a new job in Azure Data Studio. Starting with job step type. When working in Azure Data Studio you don’t have the three BI stack related types. The image below shows the job step types for both SQL Server Management Studio on the left and Azure Data Studio on the right.

Another difference is the creation of new job schedules. This can be done using SSMS, but can not be done with Azure Data Studio. You can get use existing shared schedules, just can’t create new ones.

Moving away from jobs, alerts can also be created and modified utilizing Azure Data Studio. All the same information can be entered, however there is one difference. When creating an alert you are asked to chose an alert type. When using SSMS you will have three options:

- - SQL Server Event alert
  - SQL Server Performance condition alert
  - WMI event alert

When using Azure Data Studio, you only have one option, SQL Server Event alert.

While there are a few limitations, Azure Data Studio is still a very good tool. The few limitations in Azure Data Studio for Agent job management can easily be over come simply by using SQL Server Management Studio.

Thanks for visiting my blog!!