Voice of the DBA

Tag: administration

Enable a Named Instance to Connect on 1433

My default setup for a few years is to only use named instances, usually with a standard SQL2014 or SQL 2016 as the name of the instance. Recently, I set up a new instance and needed to connect from another machine. I’ve done this lots of times, but in this case, I wanted to both enable TCP/IP as well as move the port.

This post explains how to get your named instance to listen on 1433.

First, run the Configuration Manager and check your protocols for the server. You likely see something like this, where TCP/IP is disabled.

Right click TCP/IP and enabled it from the menu.

You’ll have to restart the instance, but wait. Cancel the message that appears. Now open the properties for the TCP/IP service.

At the bottom, there’s an IPAll item. This is likely set for Dynamic ports for your named instance. Remove the 0 and enter 1433 for the TCP Port line. You should get something like this:

When you click OK or Apply, you’ll see this message.

Now restart your database engine service and you should be able to connect over 1433.

April 22, 2019
Changing the Database Collation–#SQLNewBlogger

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

I was testing some changes recently and needed to verify how things worked in a different collation. Here’s the scenario: I had this situation and ran a query.

As you can see, the query rates these two Unicode strings as equal, which makes some sense as this is a Case Insensitive, Accent Sensitive, default US English database.

I then went to the database properties to change this. When I did so, I picked a case sensitive collation.

I clicked OK, and a few minutes, later I get this:

Hmmm, why is this locked on my workstation? I have a query window open. Changing a collation is potentially a disruptive operation and requires an exclusive lock on the database. This means no other users can be inside the database with a connection.

The solution? Change the context of my query window. I can change this to master (or any other database) and make the change again.

While collation isn’t something you often change, if you do find a database that needs this alteration, you will need to drain off any connections before you can make this change.

SQLNewblogger

I ran across this while doing testing and realized this was a nice, short piece of information that I should remember. Not because I do this often, but because as I automate changes and use pipelines, I’d need a way to remove all connections before this would deploy.

An example of a nice short piece of information that I can document about my knowledge, and that might get asked about in an interview.

February 27, 2019
Reserved Space

Many of us that work with SQL Server are familiar with the idea of reserving space that our system might need later. This is normal operation in SQL Server, where we pre-allocate space to data and log files, knowing that SQL Server will manage and use this space over time. This can often be a strange concept for newcomers to the platform, who expect our database behaves like a text file, only using the space it needs for data. Choosing the amount of space to reserve is an art that many of us both struggle to articulate and explain to newcomers. It’s also a problem that we often solve by guessing high and implementing monitoring.

Lots of people worry about storage, even in this era of TB sized drives. I ran across an article noting that Windows is reserving 7GB of space for updates, which makes sense to me. I’ve had a few updates for smaller devices, like mobiles, fail because of space. I think the idea of having some space reserved for temp storage and then using that for updates is a good one. I also recognize that I’m in a different place than others. Most of my devices have plenty of free space. Well, not my phone, but that’s a separate issue.

From the comments of the article, it seems that many people feel as I do. Reserving the space is a good idea. There are plenty of complaints about older, small storage devices, and I feel bad for those people. I’ve own a 32GB Windows device and over time it’s a pain. Those devices likely need to have some sort of network/cloud storage to be usable, so There are also no shortage of the “Windows is already too large” complaints, but to me, those are separate discussions.

Ensuring enough space is around for normal operation is one thing. Keeping extra around for emergencies and critical functions is another. I’ve been a big fan of placeholders on drives, especially database servers. I keep a few on all my drives, both on desktops and laptops. If I run low on space and really need some, I can make the decision to remove these without thought. Something I can’t always do with other files.

Like the DBCC SHRINKFILE, which lets us get out of situations where we’ve made a mistake in allocation, I’d like all OSes to include some options that remove older files, such as updates, temp files, etc. that we might not need, but could make a difference in our space usage. I know modern Android helps me when I’m low on space, and would like to see that in Windos. My other hope is that we get quantum, PB sized storage devices that can fit in our phone. I can’t ever remember anyone on Star Trek running out of storage space on their drives. I look forward to the day in my life when that won’t happen to me.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 6.3MB) podcast or subscribe to the feed at iTunes and Libsyn.

January 30, 2019
Mitigate Issues Early

This is a great postmortem from Basecamp with a detailed explanation of a problem and how they are hoping to avoid issues with their service. Basecamp is a Software as a Service tool from 37 Signals that had an outage recently. Actually, the service was up and could be read, but new items couldn’t be added and existing items couldn’t be changed. That somewhat defeats the purpose of the tool. 37 Signals recognizes this and notes that they’re calling this downtime and not trying to get pedantic about the state of the service.

I have sympathy here because I’ve gotten the same error in SQL Sever. I’ve reached the point where I had 2,147,483,647 items in a table. Any additional items caused an error, which some of you might guess. This is the largest positive INT value and if you’ve started at 0 and incremented all new rows, when you reach this mark, you can’t add more data. There are a few fixes for this, including moving to negative numbers or changing to a bigint type.

The staff at 37 Signals knew this was an issue and had actually set a new default for Basecamp that used a bigint in MySQL and PostgreSQL. What they didn’t do is actually make the changes in their hosted Basecamp database because they thought they had more time. I’ve seen that before, often when an application runs at a higher workload than expected for a few years and DBAs aren’t paying attention. When staff least expects it, the INT will reach the max value an clients start complaining. I’ve had to respond to an urgent phone call about this in a few companies.

If you use INT values that are being incremented in some standard way, I’d likely set some alert process that triggers when the value gets to 2 billion or so, depending on how fast you use the numbers. SQL Monitor can do this with a custom metric, and I’m sure other software can as well, including SQL Agent. Having some monitoring in place will help you plan and be prepared to address issues before they cause problems.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

November 28, 2018