Voice of the DBA

Tag: administration

  • Setting Permissions for a SQL Server backup folder–#SQLNewBlogger

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

    While testing a script recently, I needed to set a few backup folders for my instance. This was a striped backup, and using one folder wouldn’t make sense. In modern OSes, we can’t just create a folder and expect that our processes can read it. We need to explicitly set permissions.

    It’s fairly easy for SQL Server, but since it wasn’t obvious, I decided to take a minute and document this.

    If I create a folder, say c:\sqlbackup, I see this kind of thing in properties on my Window 10 machine.

    2019-10-08 14_53_19-SQLBackup Properties

    If I click “Edit”, I get a similar view.

    2019-10-08 14_53_27-Permissions for SQLBackup

    Now, my SQL Server process can’t access this. If I try to restore a backup from here, I’ll get a permissions error.

    That’s fine. In the Permissions, I can click Add and I’ll get this dialog.

    2019-10-08 14_53_35-Select Users or Groups

    From here, I can enter “NT Service\MSSQLServer” or “NT ServiceMSSQL$SQL2017” for a named instance. My named instance is SQL2017.  Note the space in “NT Service”

    2019-10-08 15_00_49-Select Users or Groups

    If I click the “Check Names”, this will resolve for the built in service account.

    2019-10-08 15_00_53-Select Users or Groups

    Then I can click OK and set the appropriate permissions.

    If your service account is something not built in, it’s usually easy to find and add, but for build in accounts, you need the “NT Service”.

    SQLNewBlogger

    You can take a simple thing here that you needed to solve and write about it. This took my about 5 minutes to solve, playing with different names, and then about 5 minutes to write.

    Showcase your knowledge today.

  • Finding SQL Configuration Manager in Windows 10–#SQLNewBlogger

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

    I went to check a network protocol setting for SQL Server the other day on my (newish laptop) and was disappointed.

    2019-08-15 08_26_25-Settings

    This is Windows 10 and on this machine, I’d installed SQL Server 2014, 2016, and 2017. I thought that at least SQL Server 2014 had the SQL Server Configuration Manager installed, but it appears not. I know that this has been a tool that sometimes gets hidden in recent versions, but I was sure I’d seen it here.

    Either I’m wrong or Windows 10 has changed.

    In any case, the Computer Management MMC plugin has it. You can run this in a couple ways. First, hit the Start menu and type “Computer Man”. You’ll get something like this and can run this:

    2019-08-15 08_29_15-Finding SQL Configuration Manager in Windows 10 - Open Live Writer

    The other choice is to his Win+R (run) and type “compmgmt.msc”. Both will get you here:

    2019-08-15 08_30_06-Computer Management

    If you now expand the Services and Applications, you’ll see the SQL Server Configuration Manager and the various items underneath it. For me, the top one (most recent?) was the SQL Server 2017 version. The others were below as other snap-ins.

    2019-08-15 08_31_03-Computer Management

    The good thing about this is I can also manage local users and see the local logs, things I sometimes need when configurating SQL Server.

    SQLNewBlogger

    An easy post that solves a common problem, and shows I know some tips and tricks. How would you rewrite this post? You could show this knowledge with a quick 10 minutes of your time.

  • The Aware DBA

    A long time ago, at least, a long time in the eyes of my kids, I worked for a large organization as a production DBA. We had a generic monitoring tool, but it wasn’t SQL Server specific. I ended up writing my own enhancements for the tool that used its data, as well as gathering other SQL Server specific items into a local database on each instance. From there, I had this data copied to a central location each night and a report generated. This helped us with ISO certification (originally) and later, Sarbanes-Oxley compliance.

    I was reminded of that when I read Pamela Mooney’s post on Things You Should Know About Your Server. She follows the simple method of ensuring each server monitors itself and rolls up that data. This works well, even if I’d never want to do this again. The time I spent building a monitoring system wasn’t really time well spent. These days I’d buy some monitoring software and then query that data, bother the vendor for enhancements and have either a notebook or a smaller set of processes that gathered specific data I might need. Likely I’d use XE and some stored query data, but if I monitor infrastructure, I don’t want to have to worry about building and maintaining monitoring software.

    The main point from Pamela’s post, however, is that she seeks awareness of how the systems work. That’s the key for a successful sysadmin of any system. You should not, and do not want to, check each system every day, but you do want to know how to find out what is “normal” quickly and be aware of changes. We want to quickly narrow down potential and possible problems because of our experience and history with some server/instance/application/etc.

    Most of us get calls on a few systems regularly. We may know those well, but often we’re fire fighting and normal can be an elusive definition. There are often many systems that we rarely get called to examine, but most good DBAs I know have some idea of how almost every system performs. Having a little data available quickly allows us to diagnose whether a problem is real or imaginary, chronic or transient, as well as how to proceed to resolve any issue.

    Awareness is an important skill when dealing with anything on a regular basis in your life. Certainly in parts of life outside of work, but even at work, paying attention and having some idea how how your environment should work will help you catch, diagnose, and solve issues. The best DBAs might do this before the client even calls.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • What’s the Mashup Engine?

    I was testing something the other day and ran sp_who2 on a test instance. I saw this in the program listing:

    2019-04-22 10_52_34-SQLQuery13.sql - Plato_SQL2017.sandbox (PLATO_Steve (57))_ - Microsoft SQL Serve

    I had never seen the Mashup Engine listed in a program list, and I certainly don’t have any program installed by that name.

    Or do I?

    I actually do. It’s embedded in Excel 2016, as part of Power Query. Fellow MVP, Reza Rad, has a good introduction to what this actually is. Apparently it’s the engine that makes it easy for data movement and transformation. Kind of an SSIS light, that’s a part of Power BI as well.

    If you see this on your instance, you’ll know that someone is connecting with another tool. As to which one it is, that might be harder to determine.