Voice of the DBA

Tag: SQLNewBlogger

Connecting to a Specific Port–#SQLNewBlogger

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

I recently blogged about finding the port your SQL Server uses. At the end of that post I showed a connection to a server using a port. This is something I’ve used rarely, but it’s been very helpful.

The specification for specifying a port can vary. For example, my build server at home runs TeamCity on port 8077. In my case, the URL to connect is:

http://192.168.1.201:8077/overview.html

If I had an FQDN, I might have this instead:

http://Atlas.dkranch.net:8077/overview.html

When I connect to SQL Server, I get this dialog:

Some of you might note there is a “Connection Properties” tab, but there’s no port setting there, even if you choose TCP/IP.

When I connect, I give a server name. In the example above, I used “.\SQL2016”. The default is that my client will try to connect on 1433 or use the SQLBrowser to get the port. However, I could specify the instance name like this:

.\SQL2016, 1433

For this instance, that wouldn’t work as the port is 6077. I’d have to write this:

.\SQL2016, 60087

But since I’m choosing a port, I don’t need that. I can use

.,60087

and I’ll connect.

Learn to use the port in your connection string. At some point, this will help you to troubleshoot connection issues.

#SQLNewBlogger

This is a quick, easy post to write. It’s five minutes work for most of you. Maybe 10 if you have to Google/Bing.

December 15, 2016
What Port Should I Use?–#SQLNewBlogger

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

This is a quick post on checking the port for your SQL Server instance. It’s in line with Tim Ford’s request for simple, beginning blog posts this year.

How do you know what port your instance is listening on? Many of you might not know, depending on the SQLBrowser to be running. That’s not necessarily a good idea, as some security protocols insist this be disabled.

There are ways to check. First, you could use a the Configuration Manager. On my desktop, this is in the Manage tool for the computer. Once inside, I can select the Server Network Utility.

Notice that I have multiple instances here, so I need to choose one. Once I do, I see the protocols on the right. In this case, I want to look at the properties of TCP/IP, which is where I’ll get the port.

If I look at properties, I’ll start with the Protocol tab, but I want to switch to the IP Addresses tab. In here, you can see I’ll see an entry for each of the IPs my instance is listening on. I can see which ones are Active as well as the port. In my case, I have these set to dynamic ports.

This doesn’t help. If I had specific ports, I’d see them listed for each IP.

The way I tend to check the specific port is that I will go to the SQL Server Error log.

When I open this up, I see my error log entries. I want to look at the startup of my current session and scroll up slightly. At some point you will see an entry like “Server is listening on…”

In my case, you can see above that I am listening for this instance on 60087. I can verify this with a connection in SSMS.

Note that this is a named instance, and I normally connect to “.\SQL2016”. The SQLBrowser determines the port, since I send the name in, and then get a port back to where I will then connect. However, here the name doesn’t matter. I just send in the port and I can connect.

#SQLNewBlogger

Everyone working with SQL Server should know this. It’s a simple item, like checking the printer is plugged in. Know how to check this.

And blog about it.

December 8, 2016
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