Voice of the DBA

Tag: SQLNewBlogger

Hey Posh, Are My Services Running?–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In a previous post, I looked at escaping strings. The reason I needed to do this was that I was trying to do some automatic work with building and starting SQL Server instances. Part of laying the base for this was checking if services are running, and then perhaps taking action, like starting or stopping.

I knew there was a Get-Service command, and ran that. The output from this is much more than I’d like to consume.

I’d like to limit this to SQL Server services. I know there is usually an MSSQLServer service, but since I tend to use named instances, this doesn’t work. Plus, I don’t want to search for just a particular service. I want all services for SQL Server.

There is a Where-Object command, that allows me to search. There is also a –Like option for comparisons. I’ll structure a command like this:
```
Get-Service | Where-Object ($_.Name –Like “SQL*”)
```
That is less than successful.

Why not? Well, PoSh has some syntax requirements and one of them here is that I don’t want parenthesis, I want curly braces. If I change this, things work better.

If you’re older like me and don’t necessarily read small print easily, this might be one that catches you for a bit. However, notice that I only have my SQLAgent and system services, not the core database engine. My wildcard needs work.

Now I see all my services and I can easily decide if I want to stop, start, restart, etc.

#SQLNewBlogger

This was a quick post. It took me 10 minutes to relearn a few PoSh things and practice and then about 5 minutes to write this.

I’ll remember it, and it shows how I’m building my administrative skills. You should do that as well.
December 1, 2016
Escaping a Dollar Sign in PoSh–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ve been working more with PowerShell lately and ran into a problem I thought would be easy, but it wasn’t. So I decided to blog what I learned.

Assigning a String

I had a string that I wanted to use in a command. In this case, the Start-Service command. Here’s what I wrote:

Start-Service -Name “SQLAgent$$SQL2016_QA”

This didn’t work, mainly because of this error.

PoSh thinks my string is “SQLAgent\”, not “SQLAgent$SQL2016_QA”. That’s strange, at least to me. Let’s try a variable.

OK, I know I need to escape the dollar sign. I ran a quick Google search, because that’s way quicker than looking through documentation and found a piece on escaping strings. It mentions the backtick (`) as the character to use. Let’s try that.

It works. Now to try to start the service.

No error, but it is it running? Yes.

This is a quick look at string work in PoSh. The more I try to automate work and get things to run themselves, the handier I find PoSh. Since I tend to work with named instances, this was valuable.

#SQLNewBlogger

This was one of those items that I spent about 5-10 minutes figuring out and then another 10 minutes shooting screens and duplicating my work. The writeup was easy, and it will help me remember how to do that in the future.

November 23, 2016
#SQLNewBlogger – T-SQL ESCAPE for Wildcards

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran into a really interesting issue recently. I was working with a table and wanted to determine if the first character of a string was a left bracket. However, I discovered searching for a bracket isn’t as simple as I expected.

Setup

Here’s a mock table and some data.

CREATE TABLE MyData
( myid INT IDENTITY(1,1),
mychar VARCHAR(50)
);
GO

INSERT dbo.MyData
(mychar)
VALUES
(‘This is a string’),
(‘”A Quoted String”‘),
(”’Single quoted string”’),
(”’more single quotes”’),
(‘[My bracketed string]’),
(‘[I like brackets]’),
(‘Can I find [this] string?’)
;
GO

I wanted to return only rows 5 and 6 (based on identity) and not the others. My first thought was that I could just make a simple query.

SELECT myid, mychar FROM mydata WHERE mychar LIKE ‘[%’

The results:

That didn’t work. As soon as I got zero rows, I remembered that brackets allow me to wildcard part of a query. I need to escape the bracket, so I decided to try and do that with a repeating character, as we do with quotes.

Still not working. I searched, and there is an escape character of a backslash as well, but that didn’t help.

Now I was really curious. I checked the page for LIKE, and saw there was an ESCAPE option. I never knew this existed. I read the entry, but then when I looked at the samples, I was slightly confused. Why were they using an exclamation point?

I read further and realized I hadn’t paid close attention. The escape character is the one I want to match up before the character I’m escaping. So I need to escape the trigger I’m going to use in front of the bracket.

This is easier to show than explain. Here’s what I first did.

It looks like I’m escaping the bracket, but then why do I need two of them? If I remove one bracket, this doesn’t work (shown here).

What happens is the first left bracket is a trigger for the compiler to evaluate the next bracket as a literal, not a wildcard. If I replace the first bracket and the parameter with the exclamation point, this makes sense.

It’s not often I’ve had to search for brackets, but checking for percent signs has been common, and this is handy. I can’t believe I’ve never had to do this.

#SQLNewBlogger

Once I played with this in my code, I realized this was a neat function. I mocked up the table and it took longer to just type the words around the code than actually figure things out.

This would be a nice short type of post for those of you that want to show you’ve learned a small thing.

References

LIKE – https://msdn.microsoft.com/en-us/library/ms179859.aspx

November 17, 2016
Creating a Logon Trigger–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Suppose you want to audit logins for your SQL Server instance. There are multiple ways to do this, but Logon Triggers have a few advantages. First, they get data into a table that most of us are familiar with, SQL Server. Second, they guarantee that the event is captured on the instance if the trigger is enabled.

There are plenty of other uses for these triggers, but beware that you can cause problems if your code doesn’t execute flawlessly.

I’ll show you how to create a basic logon trigger here. Note, these are server level items, and you’ll need to be able to create the trigger in the master database.

The structure of this code is similar to other triggers. We’ll use the CREATE TRIGGER DDL. Where this differs from DML triggers is that we use the ON ALL SERVER command. For auditing, I tend to set this as an EXECUTE AS ‘sa’, but you may choose a different type of account.

Here’s my basic code:
```
CREATE TRIGGER LogonTrigger
--ALTER TRIGGER LogonTrigger
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    INSERT DBAAdmin.dbo.LoginAuditing
    SELECT SYSDATETIMEOFFSET(),
        ORIGINAL_LOGIN(),
        HOST_NAME(),
        APP_NAME();
END;
GO
Note in this code  I’m specifying a specific table to store data in. This has to exist.
```
I also use the FOR LOGON event. You can scope triggers for other events, at the server or database levels, and read more about DDL triggers in BOL.

Once a user attempts to logon, the trigger fires and a simple insert takes place. If there are errors in inserting this data, the user may not be able to logon.

There is a lot to write about logon triggers, but for this short piece, I’m just showing hot to get started. Please, please, please, be careful with these and read the documentation carefully. Be sure you understand how they work and how to disable them. If you implement one, test it extensively.

SQLNewBlogger

I had a small issue building a logon trigger, and thought I’d get a few posts written on the topic. This was 5 minutes work since I had the code and just wanted to describe things, but I’ve got a few more posts sketched on this topics as I’ve learned more.

Learning something, solving a problem, writing about it. This is a good way to show someone you are learning about a topic and developing some skill.
October 20, 2016