Voice of the DBA

Tag: administration

No Defaults – T-SQL Tuesday #68

It’s the second Tuesday of the month, and time for T-SQL Tuesday. This month’s invitation is from Andy Yun, where he asks you to Just Say No to Defaults.

This is the monthly blog party started by Adam Machanic, and it’s the chance for you to write on a particular topic every month. The hosts rotate, so you have to watch the #tsql2sday hashtag for the topic. Posts for the month need to go live during the day according to UTC time.

I also keep a list of topics on the blog here, and you should feel free to write about any past topics and post something on your blog. It’s great practice, and a good way to get started as a #SQLNewBlogger.

Default Changes

There are all sorts of defaults in SQL Server. The setup program presents you with a number of choices, but in most cases a default exists for setting because SQL Server needs something.

I used to have a setup script that I would run for every new install. In a few jobs, back when we used to have physical servers, a hardware person or sysadmin would install Windows and SQL Server onto a new computer and then send me the name for customization. My script, which was really a series of SQLCMD calls in a batch file that in turn called various other scripts, was designed to add an administrative database, create some jobs to track the system, setup backups, and more.

The process really did part of what Policy Based Management can do, but was simpler and tailored to ensure that all of our SQL Servers worked in a similar manner. We could override settings, but this quick script gave us a starting point that all DBAs understood. We even ran this on development machines for instances we didn’t manage as it allowed us to troubleshoot other issues, it only took a few minutes, and it removed some of the management headaches from the developers’ minds.

However, there is one thing I’ve almost always changed on my instances. I try to do it during setup, but at times I need to do it later. That setting is the default location for files. I do this as I want to usually have data files, log files, and backup files separate from each other.

Even if I don’t have different drives, but setting up separate locations here now, I can easily move the files later and make one change here for the defaults and I know I’ll have things separate.

I’m not running a new install this week, but I’ll show you how to change it on an instance that’s installed. First, right click the instance in Object Explorer and click Properties.

Next, go to the Database Settings section.

At the bottom here you see locations for data, log, and backup. In this case, on my laptop, I only have two drives, so I can’t achieve great separation.

However in any production system, I’d have the data and logs separated to different physical drives, or at least different LUNs. Backups might go with logs, but they’d ideally be separated to another location.

July 14, 2015
Does It Count?

This editorial was originally published on Jan 28, 2011. It is being re-run as Steve is out of town.

I wrote a blog recently about downtime and SLAs, and the need to have not only downtime SLAs, but also a data loss SLA. Thanks to Paul Randal (blog | @PaulRandal) for clueing me in to the need for having both. In the comments someone mentioned that scheduled downtime ought not be counted against your uptime measurements. That’s a debate I’ve had in the past with employers, and I thought it would be a good Friday poll:

Should scheduled maintenance be counted against your downtime SLA?

There’s an argument that says since the outage is scheduled, and people are informed, that this shouldn’t really count against your work in keeping servers running. The flip side is that when the database instance is down, it’s not useable, and of limited use to the business.

I know that companies that calculate the usage of their vehicles, and maintenance counts against usage time. While it’s necessary, the idea is that mechanics should be looking to minimize it, and perhaps ensure that extra checks are done when the vehicle is being worked on to help prevent future issues. A similar argument could be made for database servers.

Do you feel that scheduled maintenance is downtime? Is it calculated that way at your employer? Let us know this Friday.

Steve Jones

June 19, 2015
Using the DAC with SSMS
I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

While troubleshooting another issue, I needed to connect to SQL Server with the DAC. I couldn’t remember the syntax, so I looked it up quickly and ran into this link: Diagnostic Connection for Database Administrators. I added the ADMIN: to my connection in SSMS for a query window and clicked Connect.

This took entirely too long and then I got this:

That error resulted in a rabbit trail for me to debug this, but it worked out and I’ve learned a few things. The main one is to be sure that I’ve read the documentation and errors correctly.

As I tried a few things, including SQLCMD, I realized I had issues. I searched and found that the DAC gets set to a specific port. I checked the error log to get the number.

With that, I added it to my connection dialog, sure that this would work, but of course, it didn’t.

Eventually I stumbled on the post about errors and realized I should have checked the SQL Browser earlier. I thought about it, but discarded that thought because I could connect in other ways. Mistake. Check networking first, and networking is where the browser comes in. Sure enough, it was stopped.

The properties were set to DISABLED, so I had to change that before I could start it. I assume you can do that, if not, poke around the properties until you find that setting. With that changed, I started the service.

Then I could connect. Here’s the SQLCMD version.

With this working, the first command dialog at the top of this piece worked from an SSMS query window.

SQLNewBlogger

This was longer. What I thought was a quick lookup turned into a troubleshooting exercise that lasted about 20 minutes as I searched, read, experimented, etc. At least I made a bunch of screenshots as I was experimenting, so I had lots of data and didn’t duplicate anything.

The actual writing was only about 10 minutes, most of that looking through screenshots and trying to organize my thoughts.

References
- Enabling the DAC – http://www.mssqltips.com/sqlservertip/1801/enable-sql-server-2008-dedicated-administrator-connection/
- The DAC: Why, When, How – http://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/
- Errors when Trying to Use DAC Connection in SQL Server – http://www.sqldbpros.com/2013/10/error-when-trying-to-use-dac-connection-in-sql-server/
June 11, 2015
T-SQL Tuesday #67 – Extended Events for DBCC

It’s the second Tuesday of the month and time for another T-SQL Tuesday. This time it’s #67 from Jes Borland on Extended Events. You can read the invite, and whether you can participate today or not, write your blog and leave a comment for Jes on her blog.

If you want to see all the topics from the past, I have them on a post here.

If you want to host, contact the founder of T-SQL Tuesday, Adam Machanic. He chooses the hosts and is always looking for people that want to participate. You just need a blog and a good idea.

Who Ran DBCC?

I ran across a question recently from someone that said DBCC messages had appeared in the error log, but no one had run DBCC. That’s not possible as DBCC isn’t run without someone, whether that’s a human or a service, executing the DBCC command.

In the past, I might recommend a trace to track this, but not I’d choose an Extended Events (XE) session. I’ll show how to set up a simple session, though be aware I’m not an XE guru. I’m sure there are better ways to structure this, but it worked well for me.

You can start quickly building a specific XE session by right clicking the Sessions folder under Extended Events in SSMS under Management. I tend to pick the wizard for getting started, though be sure to save the script later.

From here we get the opening screen for XE, which I won’t show. The next screen asks for a name for the session. It doesn’t matter, but once you start using XE, you’ll start to get quite a few session, so it’s handy to pick something that’s simple and easy to understand later.

I can choose to start the session here when I’m done, but don’t worry if you’re not sure. You’ll get a chance again later.

The next step is to decide whether you use a template or build a session from scratch. I’m not sure if any of these templates will work well as I haven’t dug in. In my case, I decided to just choose a generic session.

Now I need to pick the events. There are a lot, but I went down to get the sql_statement_completed as my event. This will let me see the text of a call, which is what I need.

A couple notes here. One, you might want to grow this screen so that you can read what’s in the lower windows. I know there’s a lot of data here, but this screen doesn’t seem well defined.

The second note is that don’t forget to add the event with the arrow button. Until you have an event (or multiple events) in the right window, you can’t click "Next".

The next step is to get the data fields you need. Again, there are a lot, and I limited this to what I needed. I grabbed the client host and app, as well as the database. Then I grabbed the service_principal_name and sql_text as well. This should let me determine who’s running the command.

I’ve got lots of data here, but I only care about the execution of dbcc checkdb. This means I need to filter things, which is my choice with the next screen.

Here I need to click in the top line to get a place to add a filter. Then I select sql_text from the drop down and choose like for the operator and "dbcc checkdb" for the value. This should grab events that execute with a dbcc call.

I have to choose where to store this data. Since this is mostly a check for me, I can just use the ring buffer and keep data in memory. I lowered this to 10 events, but you can certainly pick what works for you. If you care about auditing, drop this in a file. For a small session like this, there’s not much data that’s going to be captured.

I get a summary screen, which lets me review settings. I haven’t shown it here, but once I pass that, I get the "Success" screen. This is where I can start the session, and actually watch live data if I want.

For this, I’ll watch the live data.

I decided to test this first and see if it captures anything else. I ran a few queries, and then this:

Still a blank, live data screen. Then I ran checkdb. I saw the results, and then ran it a few more times.

As you can see, I now can see that I ran checkdb against the EncryptionPrimer database.

This lets me do some quick auditing of what’s happening with my DBCCs. A smart DBA might even use this to ensure that DBCC is being run against some databases, perhaps on a spare machine.

June 9, 2015