Episode 69: SQL Server Install Checklist

We have all been there–next, next, next–let’s just get this install done.  While there are many ways to get SQL Server installed on a server, the real impact is in the way we administer the database and when we want to get good performance.  Listener James Youkhanis asked us about our install checklist and Steve and I realized we hadn’t made one public before.  In this episode of the SQL Data Partners podcast, we talk about some of the settings we are looking for when we set up a server.  We don’t have time to reference every single item, but hit some highlights.  Your list might differ slightly, but our list has been built from the experiences we have seen over time from various installations.  We love to other opinions, so give us your thoughts!

This page and the checklist available below will continue to evolve over time.  This is the version we have now.

Episode Quote

One of those [settings] that can be a life saver, but you have to have it enabled before you actually need life saving.” Thoughts on enabling the DAC in SQL Server.

Listen to Learn

  • The four areas of SQL Server installation – Preparation, Installation, Configuration, and Post Install
  • The trace flags now considered defaults on most systems
  • The cost threshold for parallelism setting we recommend
  • Azure specific settings to consider

Resources

Enable IFI
Setting Trace Flags
BgInfo
Enable
Setting Database Ownership
DBA Tools – Moving Logins

 

Episode 69: SQL Server Install Checklist

Carlos L Chacon:               Companeros, welcome to the SQL data partners pod cast. The pod cast dedicated to SQL server related topics which is designed to help you become familiar with what’s out there. How you might use those features, or ideas on how you might apply them in your environments. This is Episode 69, I am Carlos L Chacon-

Steve Stedman:                I am Steve Stedman and this week’s topic is on SQL server setup. This is something that was requested on Twitter by James for us to talk about things that we do and maybe compared to things he does on how to set up and configure your SQL server servers.

Carlos L Chacon:               Yes, exactly, I think this is something that we all have to do as administrators and we all kind-of have our own unique blend to it, and so as he proposed his idea, he actually sent us his checklist or kind-of a comparison of sorts, so we thought it would be interesting to kind-of put together our thoughts and experiences on it and then talk about some of the different sections.

Steve Stedman:                Yes, exactly and I think what’s really interesting about this is every time I see one of these from somebody else, I usually see that there are many things in common with what I normally do, but then I see that there are also one or two things that I hadn’t thought about that before or maybe I do it but it’s not actually on the checklist, so I think it’s good to look at the different lists.

Carlos L Chacon:               Sure, exactly, just how different people approach kind-of skinning that cat if you will.

Steve Stedman:                Yes. Then the phases we’re going to go through with this really the prep, the install, the config and then sort-of the post install or the things you’re going to do a little bit later.

Carlos L Chacon:               That’s right. We will actually have our checklist available. We haven’t really made it available to the public or on our blogs previous, and we will do that in this episode and that will be at sqldatapartners.com/serversetup. There we’ll have those downloads, you can get that access and kind-of, again, kind-of share and compare notes as to how you go about doing different things.

Steve Stedman:                Yes.

Carlos L Chacon:               I think we’ll go ahead and get into that conversation, Companeros, welcome to the show. Okay, so the first step as we get into how we want to set up our SQL servers, maybe the most obvious but there’s a couple of things I think we want to check off the box before we actually do the install of SQL server.

Steve Stedman:                Yes, and I think part of that is basically getting the server ready to go so that you’ll be able to get SQL server installed and its going to be happy to run after that. One of the very first things I always do is install the BG info application. If you haven’t used that, what it is, is it’s great when you’re dealing with virtual machines or when your often remote desk topping to different machines is that when you log in, it sets a bunch of information on your Windows desktop so that you can look and see immediately what server your on and what drive letters are there and how much memory is there and things like that. BG info is one of the very first things I’ll put on there just so I know I’m on the right server.

Carlos L Chacon:               Exactly, and I can tell you a little bit more information about that.

Steve Stedman:                Yes.

Carlos L Chacon:               One of the interesting things that we think about the configuration, and again we’ve had some conversations with Argenis and he’s going to be back on the show to talk about dis-configuration, but I think again, each individual environment is going to differ as to what kinds of disks they have and what’s made available to them. Whether we’re talking about local on board disks of a physical server, right? SAN, and then how that SAN is all made up.

Ultimately I think we generally want to try and separate some of the data, and we generally think about that in data and then log files and then Temp DB.

Steve Stedman:                Yes. Really the place to start with that is your C Drive first. No matter how you’re going to configure things with your different drive letters, what you want to know is that you should put nothing, really nothing but the operating system on your C Drive.

Carlos L Chacon:               Yes. Do we include, for example I know a lot of times the default SQL server stuff will get sent to, like even server logs and things, are we still sending that to a different drive as well?

Steve Stedman:                I know when you do the default install it usually installs to some stuff in program files and different sub directories under there. My preference is, I like to install that at a higher level outside of the program files directory, often times on a different drive letter so that you can call it out as here’s the install of SQL server 2016 for instance. Then that way, you’ve got it there, it’s not on your C Drive, but it’s on…that could, I guess the install could be on your C Drive, but I like to put it on a different drive letter than the C Drive itself.

Carlos L Chacon:               No it does make sense, I think particularly when start taking a peek at, if for whatever reason you wanted to keep around some system health extended event sessions or other logs you wanted to keep around for a longer time, again it just reduced the chance that you have coming in danger with filling up that C Drive for whatever reason.

Steve Stedman:                Right, yes. Then after you’ve got your C Drive and you know you’re going to put your data and logs and everything somewhere else, that’s where you really need to figure out how you’re going to split that up. Usually, we’ll have a drive that’s where SQL Server installs to, and that’s typically where I leave the system databases.

Lets say that could be your drive letter E, and then you want to set your user databases, the data files for those generally on a different drive, we can just call that F for now. Then you want to have your log files on a different drive, or yet another different drive from those E and F drives we’ve just set up. The reason for that is the logs have very different IO behaviors than what you need for your data files, and generally you can get better performance by having those on different types of storage.

Carlos L Chacon:               That’s right. Again, I think Flash is changing this dynamic slightly and so again ultimately its going to depend on your set of circumstances, these recommendations I think are towards the spinning rust if you will, so spinning disks and kind-of diversifying that, those IO differences.

Steve Stedman:                I think the other thing its not just if you’re using the spinning disks or spinning rust as Art Genis liked to call it, but if you have tiered storage, where you have some spinning disks and some SSDs, you can then, if they’re on different drive letters, you’ve got better opportunity to move those around or better promote them to some faster storage.

Carlos L Chacon:               That’s right, and that’s kind-of where, or this idea, or the suggestion of using mount points instead of drive letters comes into play, is that in the event that you need to move that data around, it can be done in the background and SQL server is none the wiser.

Steve Stedman:                Right, exactly.

Carlos L Chacon:               You don’t have to start moving around your data files like detach and re-attach because you need to go from a slower disk to a higher tier.

Steve Stedman:                Then going through this we’ve got our database files, the two sets of them, the system and the user, and we’ve got our database log files, then the next thing that we want to put on fast storage as well will be our Temp DB data and log files. Generally I like to start out with Temp DB data and log files on the same disk, but depending on how things perform you might need to split those off on a different disks or different mount points. Finally you need to have a place for where your backups are going to go.

Carlos L Chacon:               Yes, exactly.

Steve Stedman:                All right, so other things we want to do with that is we usually start out and format the disks to 50…usually we start out and format the disks to 64K allocation blocks for the databases and the log files. I know there’s a lot of different takes on SAN storage with that, but generally it’s a good practice to start with that. Then benchmark that storage before you install SQL server. There’s different benchmarking tools you can get out there to see what kind of performance you’re getting and what kinds of sequential reads and rights are you able to get on that storage.

I know working with Azure one of the things we discovered is that some of the Azure storage, the difference between the standard and the premium was basically when the SQL server goes from a usable disk speed to not usable or vice versus depending on which package you’re looking at there.

Carlos L Chacon:               Exactly, I think the other reason why you would want to consider taking these matrix before you go ahead and with SQL server is that at least you have a baseline. Should things change over time and you see that performance, for whatever reason, usually I guess it would go down, I guess if it goes faster nobody’s going to complain, but if it changes for whatever reason, at least you have those numbers to say well when I installed it, I was here and now I’m at this other number, can you help me understand what’s going on.

It could just be that there’s increase traffic on that SAN kind-of behinds the scenes there, and you can provide that information to those administrators to help you get back to where you were.

Steve Stedman:                Yes, that’s a really good point. Then the other thing you’re going to want to do to prepare the server before installing SQL server is to set up the service accounts and grant the right permissions there for the accounts that SQL server and the SQL server agent are going to run as.

Carlos L Chacon:               Yes, that’s right.

Steve Stedman:                You’re going to need those as you go through the install process.

Carlos L Chacon:               Yes, exactly. That kind-of rounds out, we may also want to think about configuring the port number that we’re going to be using. Of course the SQL server uses 1433 as a default, you’re environment may or may not require you to change that. If you do have to change that, you may also then need to think about opening up ports or whatever other security mechanisms you need to do in order for SQL server to be able to communicate with your applications.

Steve Stedman:                Yes, good point. All right, so jumping into the install then, there’s really a couple of different ways to look at the install. One is you’ve got a freshly provision machine and you’re popping in the install media or an ISO and you’re going to run through all the set up processes, or the steps and you’re going to choose what you want to install.

The other side of it is, you might be working in like an Azure VM environment where you just ask for VM to be permissioned with SQL server already installed on it. Some of the steps are going to be a little bit different depending on which one of those environments you’re in, but either way, at the end of the process, you’re going to end up with SQL server installed and hopefully you’ve got all the right components that you need installed at that point and not too many of them.

Carlos L Chacon:               Right. That’s probably the biggest point right, is that if you have somebody else install that, they’re just going to go through and check everything. Then that becomes a little bit complicated because if you don’t need SSAS or SSIS for that matter, don’t put those on there unless you’re going to be using them.

Steve Stedman:                Some people argue and say well, “I just might use them one day so why not install them.” My response to that usually is well, it’s just extra overhead. Then you might say, well but if I’m not using it, it’s not extra overhead, but where you get that extra overhead is when you get into doing service packs and updates in that if you’ve got SSRS or SSAS installed, and you’re installing that service packer update, it’s going to update it even if you’re not using it. That’s going to add a little bit of time when you do those server packs and updates. I like to not install the components that you’re not going to use.

Carlos L Chacon:               Exactly.

Steve Stedman:                Okay, so I think then once you’ve gone through and you’ve got it installed, now if you’re doing the install yourself, you will have picked all the disks locations that where data files are going to go, default locations for logs, things like that. If you’re using a pre-provision machine through Azure VM for instance, you might have to go through and manually set some of those when you’re done to set where the default location is if you just create database without specifying a path, where those databases are going to go. If you don’t do that, they’re going to end up in your default install location.

Carlos L Chacon:               I think this is probably one of the benefits of having a configuration or an install checklist, is keeping the environments consistent because one of the biggest pains is, we kind-of get into the post installation steps, is that when they’re not the same, it just makes administration that much more difficult because paths change and this server I have to do this, that server I have to do that. You have to start thinking about those differences and it could be difficult.

I think that’s why one of the reasons that the power shell installs have become much more popular. It just enables you to have that script. You maybe make a change for the network account or the log in information, and other than that everything kind-of stays the same. You know you’re going to be getting the same install no matter what.

Steve Stedman:                Yes, and that can save a lot of time if you know that things are in the same location and the same configuration on all of the SQL servers that you’re working with there.

Okay, so you finished the install. You’ve now go in sort-of the configuration mode and this is where you want to go through and start setting up the SQL server to, I don’t know, behave in the way you want it to behave.

Carlos L Chacon:               Exactly.

Steve Stedman:                Usually the first thing I’ll start with there once we know we’ve got all the right directories and things like that set up for the system is to start with the SQL server service and the SQL server agent accounts and making sure that they’re going to run as the desired either domain user or active directory user that we want to have them run as.

Often times you’re going through that, it will take a couple of re-starts and maybe a re-boot of the machine to make sure that they actually all come up and start correctly and they all have the right permissions to be able to run as a service there.

Carlos L Chacon:               Right, and I know that, again our security minded folks are going to have different recommendations there, however I must admit that the majority of the installs that I’ve done, the service account for both the…i’m sorry, what am I trying to say there? The active directory account that I’m using for the service account and the agent account are the same.

Now a lot of people are going to slap my hands for that because you don’t want your service account for the engine to have the same access that your agent might.

Steve Stedman:                Right. My experience on that usually has come down to your active directory administrator. How savvy are they on these specific permissions that you need for different services. Depending on what you can get from that active directory administrator, that may really control whether you have different user account there or not. I would prefer to have a different user account just from the security perspective.

Carlos L Chacon:               Sure. I think some of that is culture right? As you start requesting, so however you go about requesting those servers, it might be at that time that you start thinking about what those accounts are, if you have a culture requesting both accounts, obviously it makes it easier to do that.

Then you start kind-of getting up into your, you know, I need accounts and yadda, yadda, yadda, you can make that more difficult.

Steve Stedman:                Then once you’ve got the server running that way and with those users, you’re going to want to make sure that people can actually connect to it and use it remotely rather than just the local desktop. That usually involves coming back and checking the configuration on the firewall allowing ports 1433 and 1434, 1434 is for the dedicated admin connection, but allowing those ports through the firewall and allowing TCP connections.

There’s scenarios where you might not be using those, but generally that’s where you’re going to start. If you’re not using those, you probably know what ports you need to allow through.

Carlos L Chacon:               Yes, that’s another great point that I think we missed on the install step, the consideration is not installing SQL Server Management Studio on the server itself. This will help force you or limit the number of times you actually have to log into the server. As you’re doing that, you’re testing that remotely, you know that you have to administer it from your own PC like another thing.

Steve Stedman:                That’s one thing that comes up a lot. Why do we want to avoid like remote desktop into the SQL database and running the SQL Server Management Studio from there?

Carlos L Chacon:               No, exactly. It ultimately comes down to that memory reservation, because you are then part of the operating system, SQL server, or the server rather, is going to seed memory to you potentially if it needs to, for you to be able to get on there. That’s just resources that SQL server is not going to have access to.

Steve Stedman:                That reminds me of a database that I worked on a few months back where it had eight GBs of RAM so it wasn’t a gigantic amount of memory, but it should’ve been more than enough, but what happened was you had all kinds of people who were remote desktopping in. This remote desktop connection took up a GB and another one took up a GB and a half, and another one took up two GBs because they were running development environments and things like that. Next thing you know all these remote desktop connections were taking up all the memory and SQL server was squeezed down to just a few GBs of memory out of what it should’ve had up to about eight.

Anytime you can sort-of force that connection off the server but over to someone’s desktop, you’re just going to allow more memory for SQL server to run in rather than somebody’s desktop.

Carlos L Chacon:               That’s right.

Steve Stedman:                Once you’ve got to that point, we kind-of go through the rest of the configuration there and we’ll go through and usually I’ll enable instant file initialization, which is one of those that we talked about on a tuning review on a few pod casts back. Then we go through and if it’s a SQL server only dedicated box, we’re going to do things like enable lock pages and memory.

Carlos L Chacon:               That’s kind-of an important point, and people your mileage may vary, but if I know that if that SQL server is dedicated, or that server is dedicated only to SQL server, then I feel much more comfortable with that setting if it’s going to be used for different things, or you know you just have a cultural or people are going to be logging in to the server all the time, I might be a little bit more hesitant to pull that trigger.

Steve Stedman:                Then I’m going to go through and do a few more settings at this point where I’m going to do things like set up default fill factor if it hasn’t been specified. Set up the cost threshold for parallelism. Usually I’ll start that out at 50 on a new database and then kind-of tune it from there to see what it needs to be. Set up the max degree of parallelism. Turn on optimize for adhock workloads. Setup the backup compression default, this is one of those that will make it easier when you set up backups later, you don’t actually have to remember to turn on a compression, it will just use the server default. Then all your backups will be compressed.

Then of course, you turn on the dedicated remote admin connection, which allows you to get into server when you’re in specific scenarios that normal connections would not be allowed.

Carlos L Chacon:               Exactly, that’s one of those that can be a life saver, but you have to have it enabled before you actually need life saving.

Steve Stedman:                Then after that, we’ll go through and also set up multiple Temp DB files and there’s a lot of different takes on how much, or how big they should be and all that. I would usually start out with eight Temp DB files for the database and then modify from there as needed. I would also usually expand them to be large enough so that they don’t have to have growth very often.

Carlos L Chacon:               That’s another one of these areas where again, lots of different opinions there, but what I have seen is that if I’m setting up my SQL server, it has it’s own Temp DB drive, let’s just say, or mount point whatever, then they’re going to allocate, I’ve chosen an allocation size based on how big my data files I think they’re going to be. There’s usually some formula that I’m using to estimate that.

They’ve dedicated that space to me if again, whatever that number is, if you decide…like I want to take up all of that space, if I have more cores there and I decided that I want to use Temp ten DB files or for whatever reason, go ahead. I haven’t seen too many people arguing that if you have too many you kind-of get into trouble. I think in high end systems, you have a million cores kind-of a thing, yes you might come into some problems, but I think again, let me use that space it’s dedicated to me, I want to just nip that in the bud to begin with.

Steve Stedman:                That’s a really good point, I mean if you’ve got a dedicated drive that’s a fixed size that’s going to be used for Temp DB you may as well fill it up with, or almost fill it up, and I caution you, don’t fill it all the way to one hundred percent, leave a little bit of space at the end there, but almost fill it up with Temp DB because it will prevent the need to have auto growth on that or for the files to grow over time.

I think that one of the things you might experience when you do that is say, oh well I’ll fill it up to ninety-five percent, one of the things that happens is some of your SANS administrators panic a little bit saying, “Ah, the disk is full.” Well it’s as full as it’s ever going to be because we’ve just used up what we’re going to use for Temp DB at that point.

Carlos L Chacon:               Exactly. No, that’s right so I guess I would caveat that and say I would probably go up to normally eighty percent is the threshold number, so seventy-nine percent. That way you do know once you cross that eighty percent threshold that you are seeing some growth and maybe again, if your other monitoring situations haven’t alerted you that Temp DB needs some attention, that one would get my attention.

Steve Stedman:                Part of the configuration I’m going to do at this point is turning on some trace flags. The trace flags that I’m going to turn on will vary a little bit depending on whether is SQL server 2016 or 2012, 2014, 2008 or R2, but generally with everything up to 2016 or prior to 2016, I’ll turn on trace flag 1117 and 1118. I think those are now the default on 2016 so you don’t need to turn them on.

Eleven-seventeen, what that does is allows you to grow all the files in a file group equally. If you do have something like Temp DB, if you haven’t filled up that entire disk, it will make it so that when you grow that Temp DB, it will grow all the files at the same time.

Carlos L Chacon:               Right, so if you had four files and one needs to grow, all four will grow at the same time.

Steve Stedman:                Yes, exactly. That’s important because if they grow at different rates and different sizes, the bigger ones will end up taking more of the Temp DB load and therefore needing to grow more and more so you end up with some really oddball sized data files without this trace flag.

The other one was 1118 which is for full extents only. This can help with a little bit of the overall performance there when a Temp DB is growing and being allocated, but it can also impact other databases.

Then a couple others that I usually turn on are 3026 which is to enable check sums on backups. What this does is it makes it so when your backup files are written in check sum gets written to each of those chunks that gets written out, and when you’re doing the restore it will tell you if anything has changed or anything has gone bad in that file from what it had intended to be written to disk initially.

Then I’ll also turn on trace flag 3226. What 3226 does is it silences the backup messages going to the error log, SQL sever error log. It’s one of those things that I find it frustrating when you go in and look at your error log and all you see there is a bunch of backup succeeded messages. I don’t need to know that, I only need to know when something failed. Thirty-two twenty-six turns off all those backup succeeded messages.

Then depending on the specific SQL server or SQL server version or the environment, there may be a couple of other trace flags that would be considered at this point as well.

Carlos L Chacon:               Exactly right. Again, your mileage may vary there with some of those other trace flags.

Steve Stedman:                Whatever you’re doing, I would make sure that you test those trace flags before using them in a production environment.

Carlos L Chacon:               Yes. There’s one that we haven’t talked about and that is optimize for adhock workloads. I admit that’s the one I’m flipping kind-of automatically now, is the default standard as well.

Steve Stedman:                That’s one that I will, on a new server configuration I will almost always turn that on and I have never seen it cause any type of negative performance issues. It does help improve performance from what I’ve seen, but I’ve never seen it hurt any of the environment.

Then at this point, once we’ve gone through a few of those settings, this is the point that I’ll usually install database health monitor and point it at this newly built database and then use the quick scan report and database health monitor to go through and catch any other settings that I think may need to be set.

I think there’s a hand full of them that we’ve already covered here, but database health monitor and quick scan report will catch a few more things that we’ve might of overlooked somewhere along the way.

Carlos L Chacon:               What’s interesting is James kind-of approached us with this topic, I thought, ‘hmm, why haven’t we put out some of that information?’ I think some of it is because its kind-of baked in to the database health monitor tool now and so while it’s not necessarily scripted out, because the script says point the tool at your server and then go fix anything that needs to be fixed there.

Steve Stedman:                Precisely. Once we’ve done that, one of the things that it will catch right off is that DBCC check DB has never been run on your databases. I have a job that I installed that’s generally a customer job that goes through and does DBCC check DB and the one that I generally use just checks for a certain amount of time and then if that time passes, and I usually set it to like five or ten minutes, and if that amount of time passes, when it finishes the next check, it doesn’t go on to check anymore databases.

Then I usually adjust that so that it doesn’t run check DB for twenty-four hours a day, it will only run it for a certain amount of time, and the next day it will catch the oldest databases that haven’t been checked recently. The other option there is the halogran scripts for doing the checks as well.

Carlos L Chacon:               Now we’re really kind-of in the post-installation process. You’ve changed all of the SQL server’s specific type settings, now we’re going to start installing things that are, kind-of in the sense unique to each environment and the way they like to administrate as those SQL servers.

We’ve kind-of talked about the health monitor, we’ve mentioned scripts, there are a hand full of other community scripts that are out there.

Steve Stedman:                Yes, SP_ who is active-

Carlos L Chacon:               Exactly, they’re kind-of like bunched in as…I like to have it on all my servers just because if that’s the way that I then begin working or troubleshooting when I need to come back to that server.

Steve Stedman:                I think that part of the, like when we talked about those when we mentioned database health monitor, part of it is you don’t have to actually install the database health monitor application on the server itself, you can put that on your desktop and connect it to the server then have it create the monitoring database on that server or on another database if you want to.

Carlos L Chacon:               Exactly. The other things that again, I think will be environment specific might be some certain auditing that you want to enable or that you need to enable. Ultimately extended events also come into play here. Do you have some default extended event templates that you want to have installed?

Steve Stedman:                Definitely good points. Then the other thing, I think maybe this is one that we missed on configuring the database, but creating a database owner user that you can use to switch the owner of most of your databases so that the owner of the database is not SA. That’s one of those things that you can get into some security risks if your database is owned by SA and there’s a couple of examples out there of how people can get escalated security by running scripts as the database owner. It’s good practice to set the default database owner to be a user that does not have connect-ability to the database. They’re denied connect to SQL.

Carlos L Chacon:               Sure, and I guess depending on your security method, I think we’re assuming that most people still run the, what’s the name of it, mixed mode? Yes, mixed mode.

Steve Stedman:                Yes.

Carlos L Chacon:               You can still create a local log in should you need to do that.

Steve Stedman:                Right. In this case you could, if you were running in the active directory mode only, you could create a domain user called DB Owner that’s been denied the ability to connect and set the database owner to be that user as well. It doesn’t have to be an actual SQL user.

Okay, I think that kind-of hits on most of the normal set up scripts that I go through. Oh, enabling the dedicated admin connection, did we mention that?

Carlos L Chacon:               We did talk about that.

Steve Stedman:                That’s an important one. Okay, so yes we’ve got that. I think that’s it for my usual to do list on setting up a server.

Carlos L Chacon:               Another consideration that you might have is you might potentially be migrating from one server to the next, this could be in the case of a version upgrade. You’ve gone and you’ve installed all these other things and now you need to bring over the user information from the previous environment.

I admit that I had been using SP was it helprev log in for the longest time, you go to the old one, you have to install two procedures as a hashing algorithm, then you run that and it will script out with a password the information to put onto the new one.

Found a post by Chrissy LeMear, power show, who would’ve thought, MVP, and her argument is it’s 2016 why are you still using that stuff that was created for SQL 2000? I thought, ‘Hmm. Chrissy, you’ve got a great point there.’ Through the databasetools.io program, they have a way, and she actually points out some limitations which I hadn’t considered, but there is a tool set there now that will allow you to actually create, again using power show, you can connect to the old instance, connect to the new instance, specify some parameters of how you want that to be set, filter, all those kinds of things, then it will go ahead and take care of migrating those options over.

One of the bigger areas I found are compelling reasons to do that, is it will take care of the individual groups or roles. If you’ve assigned a role to a user, the SP helprev login won’t grab that information for you, it will just create the account. This will allow you to bring over those roles as well.

Steve Stedman:                That looks like a great option because I know that’s one of those things that can be very painful if you don’t have a script or a tool in place to help move all those users and permissions over.

Carlos L Chacon:               Exactly. I guess that’s kind-of our checklist. The way we kind-of go about doing things. Thanks to James we will kind-of put this organized out there on the web and you’re welcome to download it. If you do these things differently, we’d love to get your feed back there as well. We know that everybody has a unique environment, unique considerations that maybe we haven’t addressed.

I know we brought up a couple of things Steve, but I wonder are there any other changes that we need to point out for the Azure installs?

Steve Stedman:                Oh, yes good point. On the Azure installs there’s a couple of components that need to be installed so that the Azure administrative interface can, or the Azure management end of things can connect and know what’s going on with the health of the database. There’s a couple of processes that usually get installed there.

Then, just…if it’s running as an Azure virtual machine, the rest of the settings are very similar to what you’re going to be running with normal SQL server. I guess one difference there is with backups. You’ve got the ability to do your backups to Azure blob storage rather than just normal disks.

Carlos L Chacon:               Right.

Steve Stedman:                That’s something that you want to consider and take a look at too. There’s also some automated backups with Azure where just sort-of out of the box it does your SQL backups for you and that’s one of them that I have never been entirely comfortable with that, so I usually turn that off and then make sure that the backups are managed by whatever process I would normally use.

Carlos L Chacon:               Yes, exactly and I think that those automated processes sometimes it can get tricky with point in time restorers and things like that. I can do that for you, but there are some limitations as well.

Steve Stedman:                Just think of your Azure VMs in this case as just another VM running SQL server with just a few additional settings you have to add to it.

Carlos L Chacon:               One more point that we should make there in with the Azure VMs and that is the D Drive.

Steve Stedman:                Oh, good point.

Carlos L Chacon:               It can be very tempting, and we’ve done different things in the past, so the D Drive, and have a little note there that says, ‘this is a flash storage ray’, or ‘this is flash storage’. Don’t put anything here because when you bounce your box, those files are going to go away. Then you think to yourself, hmm, that sounds like a pretty interesting place to put Temp DB.

Steve Stedman:                Yes, and there’s a lot of posts out there that say give it a shot.

Carlos L Chacon:               Sure.

Steve Stedman:                That’s one of those that I’ve give it a shot, I’ve tried it, what I found is that the D Drive that you get on your Azure VM is flash storage, however it doesn’t seem to perform as well as the flash storage through the premium storage that you get through Azure. There’s a few steps that you have to go through because with Temp DB you can put your files there, and you just have to make sure that it’s set up so that when your system re-starts, and if that a brand new formatted disk that the permissions get set right so that Temp DB can recreate your files.

Carlos L Chacon:               Exactly. That could be kind-of a stain your pants kind-of movement when, particularly when it re-boots and you’re not aware for whatever reason, that D Drive, there is a possibility that you could get a new D Drive basically. As Steve mentioned, the security won’t be set there and your SQL server is going to stop.

Steve Stedman:                Yes, because what its going to attempt to do is it’s going to create those Temp DB files, when that fails, SQL server says, I can’t go any further. We can’t start the system at that point.

Carlos L Chacon:               That will be just one of those that you’ll have to make sure that those who have access to the SQL server, your admins and whatnot, that if for whatever reason, they decided they want to bounce the box, patching, depending on how you’re patching goes, that they understand that’s something that they’ll have to check afterwards. It’s not every time that will happen, but it’s just one of those that you need to be aware of it.

Steve Stedman:                You may test this, you may re-boot your SQL server twenty-five times and never have a problem, then Azure decides that machines going to be moved somewhere different and re-provision somewhere and it comes up and that D Drive is empty and doesn’t’ have the right permissions. I’ve learned that one from the school of hard knocks. It seemed like a good idea at the time, but I know better now.

Carlos L Chacon:               I guess I will say, again you use with caution, but there was a situation where the drive…we had a classic model VM and we had allocated all of the disks that were allowed, the eight disks, stripped them and we were still…we needed premium disks and we couldn’t get them because we were in classic model.

Steve Stedman:                Oh sure.

Carlos L Chacon:               By moving Temp DB over there, at least we got some breathing room because we had additional disk in play until we were able to get premium disks in there.

Steve Stedman:                I guess the way I would say it is in a perfect world, like in typical physics examples, in a perfect world I would not recommend using the D Drive for Temp DB if you have other options. However, if you’re in a pinch, read about it, understand what happens and make sure you get the permissions set right and you can use it.

Carlos L Chacon:               That’s right. Okay again, James thank you for sharing your notes with us. Hopefully this is of some benefit to you. We will be posting this out on the show notes for today’s episode at sqldatapartners.com/serversetup and we’d love to get your feedback. Did we miss something, do you not like it, do you disagree with us, in some shape or fashion, you can let us know on Twitter at #sqlpodcast or reach out to us on Twitter. I’m at carloslchacon-

Steve Stedman:                I’m at sqlemt.

Carlos L Chacon:               We’d love to get that feedback and so I guess anything else before we end Steve?

Steve Stedman:                I think that wraps it up for this week.

Carlos L Chacon:               Awesome. Okay Companeros, we’ll see you on the SQL trail.

Episode 32: Level Up Your Game With PowerShell

1400PowerShell, isn’t that a Windows Administration tool?  This is certainly how it is positioned by Microsoft, but why should the DBA community give it more than a passing glance?  My guest is Mike Fal and we chat about ways data pros can use PowerShell.

Mike says there are two reasons why PowerShell is valuable for SQL Server folks.  I hope you will join us for the conversation.  We promise not to bore you with syntax.

Are you using PowerShell?  I would love to hear your stories in the comments at the bottom of the page.

Transcription: PowerShell

Transcription: PowerShell

Carlos L. Chacon: This is the “SQL Data Partners Podcast,” Episode 32. I am Carlos L Chacon, your host. Welcome, compañeros, to the show. Thanks for joining us on the SQL trail.

This podcast is dedicated to SQL server-related topics, designed to help you become more familiar with what’s out there and how you might use certain features or ideas and apply them in your environment. The SQL community is a great big family. We like to connect with others, as well.

Today, our topic is PowerShell. I have to admit, I’m a little nervous about this subject. I was in Seattle at the Brent Ozar FreeCon last November, November of 2015.

There were about 30 of us at this event. I was trying to reach out to some folks about podcasting topics and wanted to see if they’d come onto the show with me.

In that invitation, I said, “Some topics may not work well in a podcast. PowerShell is one of them.” Sorry, Mike.

Of course, Mike Fal who’s our guest today. He’s a big PowerShell guy, and he thought that was full of baloney. He said, “No, this is a great topic. You need to talk about it. You need to have me on your show.” I thought, “OK.”

He’s here to prove us wrong. Mike is a data base consultant with UpSearch. We’re not going to be talking about syntax or anything like that. Although, we might hit one or two pieces that might come up.

However, we are going to discuss why you my Compañeros need to think about PowerShell. How it can make your lives a little bit easier. Maybe how you would go about getting started, or increasing your knowledge in PowerShell.

Today’s episode is brought to you by SQLCRUISE — Learn. Network. Relax. Grow. Just add water. Check out sqlcruise.com for more information. Let’s get to it. Welcome to the program. Mike, thanks for coming on the “SQL Data Partners Podcast.” It’s good to have you.

Mike Fal: It’s good to be here, Carlos. Thanks for letting me tag along here.

Carlos: When I think about community members, you are one of the folks that has a good niche or a good brand in the PowerShell space. You talk about it. You blog about it. You give lots of presentations about it.I know I wanted to have you on, and talk a little bit about PowerShell. Ultimately, this is a SQL Server target audience, so why as SQL Server folks do we need to be concerned about PowerShell?

Mike: I know that it’s an interesting question and one that I often think about. A little bit of additional background with myself is this. I am a SQL Server DBA. I’ve been one for 15 years. I get it where people come from the SQL Server background and say, “What’s this new PowerShell thing? Why should I care about it?Why does it matter to me? It’s funny too, because PowerShell isn’t that new. It’s been around actually for almost 10 years now.

Carlos: I know. Isn’t that crazy?

Mike: Yeah. But it’s been seen as a system administrator tool, and I get it. That’s partly on how Microsoft pushes it. That’s partly on how the SQL Server team supports it. It’s just in general how it gets exposed.To answer your question why, it’s interesting…I have a slide that I put into my presentations to answer this exact question, because it’ll always come up. There’s two reasons primarily in my mind, why PowerShell is valuable for SQL Server folks.

The first is that it works outside of the stack. What I mean by that is, your SQL Server is very good at managing data. But it’s not so good at other Windows tasks, like if you need to get some information from the Active Directory. You can do it. It works, but it’s a pain in the butt.

Carlos: Yeah. It is a pain.

Mike: Yeah. Linked servers and ADSI objects, it’s a nightmare. It’s the same with the file system. If we go to make a backup in the file path, to where we’re backing up doesn’t exist, SQL Server throws out a big, fat error, and says, what am I supposed to do?PowerShell is baked into the Windows operating system, and in my mind lives outside of the stack, or alongside with it. It can interact with the SQL Server. It can interact with the file system. It can interact with Active Directory. Exchange any number of things, and in a better manner.

There’s always the conversation, for example, the SQL Server, we can do a lot of stuff through SQL Command Shell. But there’s security concerns around that, and how that interacts. I know there’s things you can do around that, and there’s that ongoing debate.

But PowerShell gives you a more robust, a more flexible way to work with the file system and the operating system. Then, alongside it, interact with SQL Server. That’s the primary, the number one reason, why I would say to SQL Server folks why you would want to consider PowerShell for your tool belt.

Then the second is, when Jeffrey Snover, who is a Technical Fellow at Microsoft, and the guy who basically came up with PowerShell, when he came up with it, and his team put it together, they realized that, when they were building this sort of tool, administrators aren’t dealing with one server, or two servers, or three servers.

They’re dealing with 20 servers, 50 servers, a 100 servers. So PowerShell has multi-server execution kind of baked right into it. From the ground up it’s designed to say, I’m not going to just run a task in one place. I’m going to run it in a number of places.” We run into this in SQL Server all the time, like, “OK, I’m going to go and execute a script on a SQL Server.

Then I’m going to run that same script on another server, and then another server.” We have other tools available to use to help with that. I know Red Gate has their Multi Script tool. You can within central management server do multi-server execution. They’re a little clunky. I don’t want to say Red Gate’s tool is clunky, but it’s also not supported [inaudible 6:33] as I understand it.

We have this situation where we need to do the same thing in multiple places. PowerShell is built to do that from the ground up. There’s the third reason I also throw out. It’s kind of a “duh” reason. I think it makes a lot of sense to everybody. It actually applies to any script. It’s not just PowerShell, it’s T-SQL, it’s VBScript, it’s what-have-you.

If I write a script…if I put a task into a script and automate it, I can hand that script to somebody else, and they don’t even necessarily have to have knowledge of what’s in the script. They can just run it, right?

Carlos: Right.

Mike: The nice thing about PowerShell is most system administrators are familiar with PowerShell, so let’s say for example I have a script — and I actually have several demos on this — of using PowerShell to automate my SQL install from start to finish. Not just to install SQL Server, but to configure it, get all the file paths set up, set service counts, what-have-you.If I wrap that all up in a script and I hand it to a system administrator who may not be familiar with SQL, but he’s like, “I know PowerShell. I can run a script, and I have some general understanding of that,” he can take that and do that task for me. Because the other thing is, I’m a lazy DBA. I don’t want to be clicking buttons and running SQL installs.

I want to be designing architectures and building high level concept stuff, so I’ll hand the menial tasks off to somebody else, particularly if I can script it, or, honestly, if I can wrap it up in a PowerShell script and automate it completely so it’s push-button. That’s the long and sordid story of why I would say SQL Server should consider PowerShell for their toolbelt.

Carlos: Yeah, there you go. I think with that script, as you mentioned, as you hand that off to the admin, it’s so something they feel comfortable with, but it’s also something that they wouldn’t necessarily feel uncomfortable with. You hand then a T-SQL script, now they have to get into Management Studio, or something else.Now they feel like they’re touching the database. They may like, “Well, hey, that’s not me. That should be you.” You’re like, “Hey, here’s a script.” I think that could make that transition a little bit easier.

Mike: That’s absolutely right. I think that’s also one of the things that saves you some time with using PowerShell is if you’ve scripted everything out, you don’t have to add the additional layer of, “Well, I have to log into Management Studio and run this.” Because there are utilities baked into PowerShell to execute directly against SQL Server. So it can save you a step or two.

Carlos: Now Ron Deskins, who is part of the Richmond User Group community, he’s one of the leaders. He works for Markel, a large organization. His opinion — now he said this a year ago — but he said that if you were an administrator now, and he’s including database administrators in that, and you didn’t know PowerShell, that in five years, you wouldn’t be able to find a job.

Mike: That’s a fairly extreme statement.Obviously people like to make those extreme statements because it starts a buzz. But I will say that PowerShell is becoming more and more important in the Microsoft stack. Certainly, it’s getting pushed as a necessary tool for system administrators. Aspects like Exchange and Active Directory are, at this point, starting to be administered almost completely through PowerShell.

In fact, you have GUIs, Graphic User Interfaces — to manage things like Active Directory and IIS and Exchange. They’re all executing PowerShell commands underneath. So it really is, for system administrators, yes, I would say that’s definitely the case. SQL Server administrators, it’s a little fuzzier, simply because one, yes, most of what we do is T-SQL.

As long as you have a solid understanding of T-SQL, you’re going to be employable, you’re going to be fine. However, you’re going to be at a disadvantage to people who know PowerShell, to people who can interact with that language to manage tasks in larger scales and in more efficient manners.

I get where the sentiment is coming from. I sort of agree with that sentiment. At the same time, people should not be panicking and going, “Oh my God, what am I going to do?”

Carlos: [laughs] Let’s get into a little bit of that, some of the differences between T-SQL and PowerShell, or that balance.

Mike: In one aspect, they’re not all that different, because for the most part, PowerShell and T-SQL are top-down, interpreted at runtime script languages. You write a T-SQL script, and it goes from the top to the bottom and executes stuff. PowerShell is a very similar way. In PowerShell, you can create functions that are certain programming constructs that you can apply within PowerShell.I like to think of PowerShell more as an object-aware language, and not like a C#, which is object-oriented and you create classes and you instantiate objects in all of this. PowerShell, because it’s a top-down scripting language, it still has that particular feel to it, but it’s built on .Net. That was another conscious decision with the PowerShell team when they built it. They built it on top of the .Net framework.

Everything within PowerShell is an object, and you get this robustness of manipulation and data access. Strings are not just strings. They’re string objects. Integers are integer objects. You get date time objects. I think that’s actually a good parallel between SQL Server and the PowerShell language is, PowerShell supports this idea of strong data types. Databases are built on data types, right?

Carlos: Right.

Mike: They’re very important to us. Selecting the right data type is a key component to database design. Just as well with PowerShell. To extend it out a little more of this comparison, PowerShell also has a lot in common with the Unix environment in Korn shell Bash shell. For people out there who are familiar or have done some time in Linux, they’re probably, “Oh, OK. I’ve done some of that scripting.”Some of the syntax and the format of PowerShell has a lot in common with Korn shell and Bash shell. At the same time, it takes it a step further by adding the strong data types. There are some similar concepts. At the same time, PowerShell is different in the way that you…it’s more like a C# in the way you writes PowerShell.

You’re going to have curly braces, and you’re going to have loops, and you’re going to be creating variables. Certainly, variable declaration is completely different in PowerShell than it would be in T-SQL.

Carlos: It’s interesting that you’ve just mentioned a couple of things, like all of a sudden you’re talking about PowerShell, and you’re in Korn shell, and all these things, and I’m like, “Oh, my gosh.” Somebody, for example, like myself, who was never a programmer. I took a class in college, but that was not my thing. I come from the networking side.I think, “Oh, man. T-SQL was, in a sense, one hurdle,” and now you’re saying, “OK, gosh, now I got to go learn all the shell and everything here.” [laughs]

Mike: No, not at all. Again, to speak to my background a little bit, I came up from system administration myself. I don’t really have a programming background. My “Secret shame” that I always like to joke to people about is I actually have a music performance degree out of college.I went to CU Boulder and studied to be a musician. Because that didn’t pay so well, I found myself going into IT. I’ve never really done any sort of programming outside of the scripting stuff. I did dabble a little in Java and C# at one point, just to see what it was like and if it would interest me. You don’t want to see my C# code, man. You do not want to see it.

I get it, where it feels a little overwhelming. That’s the thing is it’s, anytime people look at PowerShell, they’re like, “Oh, my God. It’s this whole new thing that I have to learn.” I would say it’s not as new or there’s not as much to take on as people might think. First off, yes, PowerShell is very deep. Then again, so is SQL Server.

If you really want to be a true expert at PowerShell, you’re going to spend a lot of time with it. [laughs] But to get started, most of us have used CMD.exe. We’ve gone in, we’ve run directory workups. We’ve done IPConfig, ping, all of that stuff. You can do this all in PowerShell. Then you start to take little steps beyond that.

You say, “OK, now I’m going to do a directory lookup, but now I’m going to stuff that into a variable and maybe process through those files. Maybe I’m going to add some code that will check and see if — we go back to the backup path example — check and see if the backup path exists before it tries to execute a SQL backup.” If it doesn’t, create it, then execute the SQL backup.

Those are some very simple patterns that people can ease into. That being said, yes, PowerShell is a deep language and takes a long time to…I’d say people can get their feet under them in probably about three months. Just messing around with it and playing with it. I’ve been working with it for two and a half years now. I’ve got a pretty good grasp, but there’s plenty of stuff that I’m still like, “Oh, that’s beyond me.”

Carlos: [laughs] Interesting. You mentioned the backup example. If we were to apply the same idea to T-SQL learning, a lot of us, there are a lot of scripts out there that we will take from the Internet, and we will try to apply it in our environments. Is that something we could start with PowerShell?

Mike: Yeah. Actually, it totally is. There’s plenty of examples, and folks, I’m not the only SQL person talking about PowerShell. I talk about it a lot. I did that because I didn’t feel like there were enough voices, but there are some pretty good voices out there. For example, Allen White works at SQL Century, been doing this stuff for a long while.He’s got tons of great blog posts up on sqlblog.com, of things that he’s done with PowerShell, little learning cases, whatnot. Ben Miller, aka @DBAduck — he’s out of Utah. Another really strong PowerShell guy — got a lot of good scripts out there. Laerte Junior — he’s a PowerShell MVP, but SQL Server DBA out of Brazil. He writes a lot of stuff up on Simple Talk. There’s resources out there.

Again, people, if they want to go into my blog at mikefal.net — Mike F-A-L — because sometimes people want to add the extra L. I don’t blame them. It happens all the time. I’ve got a bunch of little scripts and snippets and things that I’ve messed around with. There are resources out there.

Carlos: We will post these that you’ve mentioned. If we connect and you have a few others you want to toss my way, we will put these up on the show notes for today’s episode, so it’s sqldatapartners.com/podcast. You can go there, search for today’s episode on PowerShell, and find this information.

Mike: Yeah. I actually will provide free, because I have several slides that I put at the end of my presentations that I go through different resources. Books that people can use, blogs that are useful, online training. Just to mention it, Microsoft Virtual Academy has like hours of free PowerShell training. If people want to do webinars, go up to.Now, it’s just generic PowerShell training. A lot of those are targeted to people who are doing more system administration stuff, but once you get the language fundamentals under you, then it’s up to your imagination. It’s up to, “Hey, what kind of problem do I need to solve?”

Carlos: The sky is the limit.

Mike: Yeah.

Carlos: Now, so you recommend someone starting, and let’s say they’re either…you brought the example CMD. I can use CMD, kind of feel comfortable with that. I want to start using PowerShell. Should I take a couple of classes to Microsoft Virtual Academy to understand some syntax there before I jump into, let’s say, using a PowerShell script to back up my databases, or should we just jump right into one of your scripts that we could use to do that?

Mike: Everybody’s learning method’s a little different. Some people like the in person classes, some people, they want to read, and study everything, some people, they like, “I’m going to dive in, and I’m going to put my hands into it up to my elbows, and just get messy. Just…”

Carlos: Yeah, “Let’s do it.”

Mike: “…full throttle over myself, and see what comes out.” Personally, I’ll be honest. I’m the latter of like I just dive in, and I start trying to figure stuff out. A good starting point no matter what your learning path is, any of these paths that we’ve talked about, I always recommend the book “PowerShell in a Month of Lunches.” It’s a book by Don Jones and Jeff Hicks.

Carlos: I have the book.

Mike: A lot of people do, and what it does is it breaks up the PowerShell fundamentals into, I want to say, 26, 27 different lessons that are each about an hour long. The idea is, is on your lunch hour, you just sit down, you want to run through the exercise. It’s pretty low impact, but you reinforce these fundamentals over the course of the month.Lots of people use that, they love it. I’ve got nothing but good things about this book, so I always recommend to people that. Now, for me what was useful, once I had the language fundamentals, a couple of things that are good patterns for people to get into to learn PowerShell, one, stop using CMD.exe. Use PowerShell.exe.

The reason is, is because pretty much everything you can do in CMD.exe, you can do in PowerShell.exe. If you needed to run a ping, or you do an IP config, again any of these commands, you can do that through PowerShell.exe and then you’re in the language. You have the access to all the other PowerShell functionality.

It’s just that’s a good app to get into. Then I will typically recommend to people, once they’re in there, just walk through the file system. If you got to go to a directory, or you need to maybe delete some files. One of the big light bulb moments to me for PowerShell, and why it was such a useful tool is DBAs.

Most of us have experienced the scenario of that 3:00 AM phone call, where backup was failing because the drive filled up, because it’s just full of old backups. We’ve all been there. We go in and plug in the computer, we go into each of the directories and we delete what we needed to. What I found is — if I need to delete all my transactional backups older than three days, I wanted to do that, I can do that across an entire drive with one line of PowerShell.

That saves me 10s, 20s, 30s of minutes, if not an hour. That was to me the big light bulb moment of like, “Oh my God. This is amazing.” That’s what I recommend to people for learning. It’s to say, “Get into PowerShell, get on that command line, manipulate the file system, explore things that way.” It starts to reinforce the patterns of how the language works.

Carlos: That’s a good point. As administrators, we have this mantra of, “Do it once, and then script it so you don’t have to do it again,” but auto this task and you make a good example of… If you’re doing that more than once then you’re not doing yourself any favors. You get a ticket, maybe other people see it like, “Oh, I have to engage this person.” You find some value, but make that easier on yourself. Instead of having to take that time, script it out.

Mike: Exactly. It comes back to again…I’m a lazy DBA, so I try to automate absolutely as much as I possibly can. I’ve had those kind of conversations with people. A lot of people come to me and say, “We can’t automate everything.” And I’m going, “Well, I think you can and at least you should make the effort.” We can have the conversation.There’s certainly a lot of good philosophical points about should be automated and what shouldn’t be automated. To use an example, I can sit down and automate out backup or restore testing. I actually have scripts for that, where I automatically restore databases to another server, I do DBCC checks and I email if there’s any errors or any problems with that process. A lot of people are like, “Oh, that’s great, that’s saves me a lot of time.”

I would also say that there’s a lot of value in practicing the method itself. Even though I have a script and a Powershell script that does that automated database restore, I will still sit down and manually restore the database, point in time, because I need to make sure I’m still good. When a fire breaks out…

Carlos: It hits the fan.

Mike: …and everything goes to heck, you’ve got to know what you’re doing. It’s that fine line between automation and practice.

Carlos: There you go. Great advice from Mike Fal. Thanks for that information. We’ve started the conversation, of course we’ll have some additional information at sqldatapartners.com/podcast. If you want to check out Mike’s stuff and some of the scripts that he’ll make available to us. Mike, it is time for what I call the SQL Family portion of the program.

Mike: OK, then.

Carlos: Here, we like to talk more about you, and some of your life experiences. Now, one of the things we’d like to talk about is tools. How people are working, what they’re using. Now, we’ve been talking about PowerShell, and if that’s your favorite tool, that’s cool, but we want to know what your favorite tool is. Free tool, paid tool, why do you use it, and why do you like it?

Mike: Oh, gosh. I think probably my favorite free tool is sp_WhoIsActive.

Carlos: Ta da.

Mike: Adam Mechanic script.

Carlos: Yes, Adam Mechanic

Mike: I will be perfectly honest that I think I would find it hard to troubleshoot problems if I didn’t have it because it’s just so useful for wrapping up information about a problem. When somebody calls me, and says, “Hey, there’s a slow query,” or, “We’ve got a problem,” almost my first command that I execute is sp_WhoIsActive to see blocks, what are the processes waiting on, and it’s all right there.Sp_WhoIsActive is probably my favorite free community tool that I would use.

Carlos: Very cool. Before we go on, I do want to mention our sponsor sqlcruise.com. Now, Mike, are you a cruiser?

Mike: Actually, so I’m glad you brought that up. I went on the last Alaskan Cruise a couple years back.

Carlos: There you go.

Mike: I can’t say enough good things about SQL Cruise, and the kind of amazing opportunity it is. It was career changing for me. What I always like to tell people is one of the instructors was Buck Woody for that particular cruise.Because of the intimacy of the cruise, it’s 20 people. Tim keeps everything small, and you got a lot of good face time with the instructors. I had the opportunity the opportunity to walk around Ketchikan Alaska for three and a half hours with just me, and Buck Woody just talking.

Carlos: Very cool.

Mike: He really opened my eyes to some things with where the industry is going, patterns and practices, things that I should consider out with my career. It did really change my career. I can’t recommend strongly enough for people to consider SQL Cruise as a training opportunity.

Carlos: I agree. Tim and Amy Ford have put together a great training environment, and they’ve offered listeners of the show $100 off the price of admission. Of course, we’ve missed out on the 2016 cruise already, but you can check out sqldatapartners.com/sqlcruise for more details on how to get on board, and how to save that $100, so check it out.Mike, you inherit a million dollars. What are you going to do with it? [laughs]

Mike: Whoa. What am I going to do with a million dollars? Gosh, you know, I’ll be honest. It’s not really a question I’ve given a lot of thought to, because in general, I tend to be, look, if it’s not a possibility, I’m not going to spend a lot of mental cycles on figuring it out.I probably would sit down, and probably try, and start my own SQL server consulting business. I have a lot of admiration for the Paul Randalls and the Brangosars and even yourself, Randy Knight — these guys have started their own boutique SQL consulting firms. That’s one of the exciting things of going to up search — I’m getting to participate in one of these unique little groups.

One, I think these outfits get it. They understand the balance of community involvement and helping people out with highly targeted, highly focused, highly specialized SQL Server consulting skills. I want to give more community members the opportunity to participate in that, because too many of us, we get stuck in the day to day grind. We’re at the corporate job, which we all get tired of.

A million dollars…I’m the kind of person that…I like challenges, I like things put in front of me, so I wouldn’t get the million dollars and say, “Well, I’m going to go…I might spend some time on a beach.” I will confess, I probably would spend a little time just relax…

Carlos: At least the weekend, right?

Mike: Yeah, at least the weekend, but I would get bored so I would want to invest that money in myself to continue to challenge myself and push myself forward. To be perfectly honest, owning and running a business — that’s a pretty steep challenge for me, but one that would be pretty cool, if I could pull it off.

Carlos: There you go. If the numbers come your way, make sure you let the compañeros here at SQL Data Partners know, and we’ll make sure that you get lots of good talent there with you. Our last question, Mike — if you could have one superhero power, what would it be and why do you want it?

Mike: It would be the ability to read other people’s minds. This is because…We’ve all been there, right? We sit down, we sit with — whether they’re our customers, whether they’re our clients, whether they’re our co-workers. One of the things that I…

Carlos: The spouse.

Mike: Spouse. What have you? One of the things I always struggle with is communication. Not personally, as you can tell, I talk a lot, I’m pretty open with that. Somebody will come to you and say, “Hey, I’ve got this problem or this thing’s going on.” We have to sit and tease them and work with our interns.It’s very much an art form to figuring out without being able to read somebody’s mind — what they’re thinking and what they want. If I could read their mind, I’d skip all those steps like, “Got it. All right. I know what you got. I know what you need, I’m going to take care of that for you.” Or, “I know what you’re trying to get at, no confusion, we’ll get it sorted.”

Carlos: Much responsibility, much privilege. Or much privilege, much responsibility there, right? Start reading my mind. I don’t want to hear about my dirty laundry online.

Mike: I will keep that to myself, sir. I promise.

Carlos: Very good. Mike, thanks for being here. I so appreciate your time.

Mike: Thank you, Carlos. This is a great opportunity and I look forward to having more conversations with you over the air, and hopefully at Summit.

Carlos: There you go. Compañeros, if you liked today’s episode I invite you to leave a comment or review on iTunes or Stitcher. Of course, you can go to our website, at sqldatapartners.com/podcast and leave your comments there. This will allow others to find it more easily, so they can enjoy the program as well. Thanks for tuning in, and I hope to see you on the SQL trail.

T-SQL Tuesday #70 – Managing an Enterprise

Tsql TuesdayThis month’s T-SQL Tuesday is hosted by Jen McCown (b|t), and is on strategies for managing an enterprise.  I had the chance to meet Jen and her husband on SQLCruise and they really have a down to earth style about the idea of managing an enterprise and they helped me think quite a bit differently about how to tackle the enterprise monster.  This post has made me realize I have a few more blog posts to write about this topic.

Where to begin?

There are number of concepts that could be discussed and I think the links to Jen’s site will run the gambit on ideas.  My concept is a simple one, but one I think worth pursing as I continue to see a variety of environments–inconsistent SQL Server setups.  There are two facets of consistent setups.  The first being the settings and options you choose when you install SQL Server.  This could be the way tempdbs are setup, your service accounts, naming conventions, and what features you choose for an installation.  Some of this can be changed after you install SQL Server–like default parameters, but some of it can’t be changed–or at least not changed very easily.  Each of these are specific to SQL Server and important; however, I wish to address a second facet of SQL Server setups–the pieces that are specific to your organization and the tools/scripts you use to manage your environment.

Consistent Setup

After SQL Server is installed, this is a great time to run through a few other setup items before letting in the environment loose.  Backup jobs and other maintenance tasks are a given, but here are few others to consider.

  • Database Mail setup
  • Extended Events traces
  • A job to recycle the SQL Logs each day.
  • The number of SQL Server logs to keep
  • Creating operators for notifications
  • Adding Alerts Error Number 823, 824, and 825

Screen Shot 09-08-15 at 11.08 PMNeed help starting your script collection?  Don’t forget to script out your options as you create them through the GUI.  This is a great way to start building your configuration nest egg.

Junk Drawer

While there may still be some debate about whether you should store items in the master database, you will need to have a place to stick your stuff.  Remember, we are talking about creating some consistency here and you will need to have a bit of order to make that work.  Growing up, my mom reserved a little pull out drawer in the kitchen for my dad.  Only his stuff was in it and if something was left on the counter, it would wind up in the ‘junk drawer’, but then my dad would always know where to go look for something when he couldn’t find it.  I suggest you do the same with your database environments–create a database dedicated to ‘admin’ stuff.  This stuff isn’t for use in other database; however, I have caught myself using my admin database for a staging table, but that should not be the norm.

This database will be for your scripts you run in your enterprise.

What’s in there?

Examples of scripts I put in my admin database include:

  •  backup scripts
  • certain common Dynamic Management View I use for performance monitoring
  • sp_whoisactive
  • System Log files–If I am auditing something or getting backup history, those tables go in the admin database.

Having all the admin utilities in a single database just makes it easier for when other folks come into the environment and you need to collaborate.  Good luck to you and I hope to see you on the SQL trail.

Podcast Episode 05: Principle of Least Privilege

Robert Verell and I chat about good security practices and the principle of least privilege.  We discuss Robert’s rule to never give anyone db_owner, other groups and Robert’s home grown group to give instead of db_owner.

Show Notes

Robert Verell on Twitter
sqlcowbell.com
What to use instead of db_owner

About Robert Verrel

Robert is a SQL Server Professional working in the Nashville, TN area where he is active in the PASS community.  He is originally from Mississippi and is a big Mississippi State fan.

Transcription: Principle of Least Privilege

Carlos L Chacon: Welcome to the SQL Data Partners Podcast. My name is Carlos L Chacon, your host. This is episode five.

Today, we’re talking about roles and responsibilities in the “Principle of Least Privilege,” with my guest Robert Verell. Super excited to have Robert on today. He’s been able to do some pretty interesting things in his environment. I’m glad that we’re going to be able to share that with you today.

As always, you can check out our show notes at sqldatapartners.com/podcast for today’s information, and for previous podcasts episodes. We are on iTunes, and if you like today’s episode, I invite you to give us some feedback. Rate our program, and let us know how we’re doing. If there’s something you’d like to hear about, we’d like to get that information as well.

If you feel so inclined, we invite you to subscribe to our channel so that you’re always getting the latest information, the latest podcast that we’re putting out. With that, let’s get in to it and welcome to the show.

SQL Data Partners

[music]

Carlos: Compañeros! Thanks for tuning in today.I would like to introduce our guest, Robert Verell. Robert’s a DBA for Cigna-HealthSpring, where he has worked as a senior database administrator for seven or eight years now. He’s a Microsoft certified IT professional. He hails from Nashville, Tennessee. Robert, welcome to the program.

Robert Verell: Hi, Carlos. Thank you for having me.

Carlos: So Robert, tell us what you’re doing these days and where people can connect with you.

Robert: Well, I’m speaking at SQL Saturdays. That’s always a great time. Also I’m pretty active on Twitter. This year I’ve launched my blog and you can go to that on sqlcowbell.com. That’s also my handle, if you will, on Twitter. It’s @sqlcowbell. It’s S-Q-L cowbell. So I’m real easy to find.Usually if you send me some kind of message or anything like that, I’m fairly quick to respond.

What I’m currently working on is I’m working for Cigna-HealthSpring. On the Cigna side, we’re developing an internal cloud. They’re doing database as a service on that end.

There’s a lot of challenges to that. There’s no elevated rights for anyone, so we had to figure out how to develop certain ways of doing a request. Since you don’t have elevated rights at the server level, you’re not able to create logins. So how do you do that?

You have an interface, obviously, that someone can go to and say, “I would like to have this user set up as a login in my database and give them these rights.” Basically, they pass in some sort of [inaudible 03:13] directory name, and that gets sent over to a procedure that I’ve written, and it’s set all that up for them.

Carlos: That’s one of the reasons that we wanted to have you on the show today, Robert, is that principle of least privilege. How simple server roles can help us attain that.I know in the past, you’ve given a presentation about these roles and I know that in your current position you’re working a lot with this. I thought you’d be perfect to come in and chat with us about some of the nuances or difficulties that we might have. Also, because you’ve been able to be successful, how folks can actually get over the hump and implement this.

When we talk about the principle of least privilege, I guess we’re talking about giving users only the rights that we need.

Robert: That’s correct, and Brian Kelly, if you’re familiar with him, he had a blog post back, several months ago that I read. It really hit home with me. As far as giving privileges to people on a database, if you follow the principle of least privilege, you should go all the way down to the column level. If someone does not need to be able to see certain columns in a table, then they should not have rights to it.That means going all the way down and getting granular and going that deep with it if you want to strictly follow the principle of least privilege. Now, I’m not telling anybody to go out and follow that to the letter, because I can’t even imagine trying to administrate that. [laughs]

Carlos: Yeah, that’s a…Lots of overhead there.

Robert: There is a good bit of overhead there, yes. At the same time, you want to make sure that you have a secure environment. You want to make sure that people have rights to things that they need, but not things that they don’t need. In case of any kind of malicious breach or something along those lines.If someone has access to sensitive data that they really shouldn’t or don’t really need to have access to, and their credentials were compromised. Now you’re talking about your organization having to send out apologies to millions of people, and losing money and a stock drop, and things like that. You just don’t want that on your conscience or on your permanent record, as it were.

Carlos: Sure. And I think, while there may be folks kind of gunning for that, the column-level security, ultimately what we’re talking about today is much, much higher than that. Providing at least some initial environment where you can begin to — I don’t want to say, “lock things down.” Only give users what they need to make, your environment a little bit more secure.As we’ll talk about a little bit later, potentially avoid an opportunity where you may be unable to provide services that you thought you were going to be able to provide.

Robert: That’s correct. Absolutely. If…

Carlos: So…I apologize. Let’s go ahead and jump in to some of the justification that users might request or things that our database administrators are getting requested with. Why they might default to those elevated privileges. Because that being the administrator role or in the case of the database and SQL Server database, the db_owner role. Right?One of the ones that kind of jumps out to me is creating databases. I think we’ve all been in that environment where we have a third party application that wants to install something, and it needs to create a database.

Robert: Sure. There’s a common misconception that in order to be able to create databases, that you need sysadmin rights. That’s a fallacy.If you give someone the db_creator role at the server level, then they will be able to create databases as they need to.

Carlos: Right. That’s a very good example of saying, “Well, here’s what they need to do. There is a level of functionality a little more granular that will give them that opportunity to do that.

Robert: That’s correct. Yes.

Carlos: I think another one that you point out is create logins.

Robert: Sure. In order to create logins, you don’t have to have the sysadmin rights in order to create a login on a server. If you give someone the security admin server role, then they’ll be able to do that as well.With the security admin role at the server level, there is a little bit of a loophole here and Microsoft admits this as well. It’s that if you have someone who is in the security admin role, they’re able to give someone sysadmin rights. So you could potentially set someone up with security admin and then they could turn around and just give themselves sysadmin rights.

[laughter]

Robert: From that perspective, I’m not going to say give that widespread, because you really shouldn’t. But if you do have to give someone that, make sure you audit. There’s something along those lines. Make sure you’re looking over that or have some sort of policy based management or alert set up that makes sure that that’s monitored. To where you can know if someone is making server side changes on roles or database roles.

Carlos: Sure. That kind of goes into then keeping up with your environment, so that you’re aware of what’s happened and changes that are going on in the system.

Robert: Sure.

Carlos: One of your ideas was that we should not be giving anybody db_owner ever.

Robert: Ever.

Carlos: [laughs]

Robert: Ever, ever. I will take that to the bank. I will take anybody. Any challengers. Any and all on that. [laughs] The reason why is…The biggest thing with db_owner is you have explicit and implicit permissions.When you give someone a database role, such as db_datareader, that implies that they will be able to read any table or view. Basically run a select statement against any object in the database.

With db_owner, the implied permissions are astronomical. They’re beyond anything that anybody really has to have if they’re doing development work. The biggest implicit permission that comes with db_owner is the alter database permission.

This brings in a whole list of things that you can do that from an administrator perspective, you don’t want anybody else working on. It gives someone with the db_owner permission the ability to add data files.

Let’s say you have all of your data files are nice and neat. You have a dedicated drive for them. But the developer comes in and they run out of space for whatever reason while they’re running something at nine o’clock at night. And they decide, “Well, we need more space.”

“I know that there’s space on the C drive, so I’ll just put a new data file out on the C drive and we’ll just let it grow forever. We’ll let it grow as long as it will go.” Eventually, that will of course fill up and then you’ll have a C drive that’s full. You may have a server that goes down. [laughs]From production to dev, that’s never a good thing, because that still halts development work even if it is a development server. Another thing that the alter database permission gives is the ability to change things like the recovery model.

Let’s say in a non-prod, but higher environment, such as a UAT. Or maybe you have an integrated testing environment where you’re wanting to test things very much like production. You have transaction log backups. You want that kind of similarity to production.

If they’re able to change that recovery model or maybe you have it in simple. OK?

Carlos: Right.

Robert: And you’re not taking transaction log backups. They could to in and change that recovery model from simple to full. Now all of a sudden, two hours later, now you’ve got a full transaction log on one of your testing databases because you’re not taking transaction log backups. [laughs]

Carlos: That’s right, and the application grinds to a halt.

Robert: Sure. On the flip side of that, if they switch from full to simple, now you’re trying to take transaction log backups. Those jobs are going to fail. You’re going to lose any kind of restore chain that you potentially have, because you got to change from simple back into full. And then take a full backup before you can start taking transaction log backups.

Carlos: Yeah. This was the big one for me. I thought that idea of breaking the backup chain…That’s that example of, so it’s database administrators, right? We always think of job number one as being able to make the data available. The source of that or the core is in those backups.If a change is made there or even if a full backup were to be taken and it breaks up our backup chain. We then put ourselves in jeopardy of being unable to provide that service to our users that they’re expecting. Things can get complicated very, fairly quickly.

Robert: Absolutely. The last one, and this was always the biggest kicker for me. I know you said “the restore chain” is big for you, but the alter database permission allows a user to take a database and take it offline.The biggest thing isn’t just that. It’s that you can take it offline and then you’re not able to bring it back online, because you have to be in the sysadmin role to be able to do that.

Carlos: There you go.[laughter]

Carlos: So they’re clicking around in the GUI and oops!

Robert: Yeah.

Carlos: And there you go. Just between you and me, Robert, have you ever had anyone do that?

Robert: Yes, I have.[laughter]

Carlos: Ooh! Well, there you go. I guess I consider myself fortunate now that that has not happened to me.We’ve just discussed some of the reasons why we don’t want to be giving the db_owner role to our users. We have this concept of role or kind of dividing and conquering some of the permissions that are available. We’ve already talked about the db_creator role and what that means.

We’ve also discussed the security admin as well.

Those are probably the two big ones or at least that I’ve more used. Any of the other roles that you want to talk about? The default SQL Server roles that you use on a regular basis?

Robert: At the server level, there’s really only one. I’m of the opinion that a lot of the server roles are…I don’t want to call them “useless.” I can see where there would be certain times when you’d want to give that role versus giving someone sysadmin rights.But I believe they’re really specific to maybe some special environment of some sort. Like disk_admin, which allows you to manage disk space and manage data files and log files. That’s something that you would assume a DBA would do, not someone who’s going to…You would assume that your DBAs are going to have sysadmin rights.

Carlos: Sure. Yeah, potentially if maybe your [inaudible 15:31] person, maybe they didn’t have a DBA and I don’t know. The disk people wanted to get in there.

Robert: Sure. And they wanted to manage their own disk space for whatever purposes. That would be a good example, sure.

Carlos: OK. Ultimately, we talk a little bit about rolling our own and other applications that use these. Even in some of the more familiar ones, at least in my mind, within SQL Server include the role in MSDB. Right? Like database mail user role? If you create a mail profile and you want that user to be able to send mail, you grant them rights to the database mail user role. Then they can send email all day long.

Robert: Sure. Absolutely. There’s a lot of roles in MSDB. I’ll be perfectly honest. I don’t know what all of them do! [laughs]From an implicit standpoint…I don’t know what every single one of them does, but I can say that I know that one that I use pretty regularly is the operator role, the SQL agent operator role as well as the SQL agent reader role.

The reader role is a real big one, because it allows users to be able to view job history. A lot of times, developers will have automated processes. They want to see run times and things like that. And compare. That’s good. We want our developers doing that. As an administrator, you want them looking at those kind of numbers.

If you give them “view job history,” they’ll be able to view all that stuff.

The operator role is good if you have jobs that you’re wanting people to be able to execute as much as you want them to be able to. [laughs] That was kind of long winded on how I said that. Basically, if you have a team, and all you want them to do is to be able to execute jobs, then you can give them that role. And they’ll be able to execute any job on the entire server.

The reader role will allow you to do that as well, but you have to specifically own the job in order to do that.

The operator role also allows you to be able to create jobs. But if you do not have them set up with any other kind of permissions in any databases or anything like that, then they won’t be able to run any kind of TSQL. If you don’t have any proxies set up for them, then they won’t be able to run SSIS packages or anything like that.

Carlos: Compañeros, one of the things we’re trying to do is to get some information out there. I think what Robert just said was huge. Giving users the reader role to your jobs is one that I see in a lot of different places, particularly your SSIS packages and other things that might be there. There is a way to give them that privilege without giving them the keys to the kingdom.I think that that’s something will be of value to a lot of folks.

Robert: Sure. That’s good.

Carlos: It’s a right there kind of within our grasp. We just need to execute and grant that role. Another one I was going to talk about was the RS.exe role, which is both in MSDB and the Master DB. That’s for, obviously, reporting services. So for those who’re going to be creating subscriptions or publishing reports, we make sure that that role is available to them so that they can have that privilege.Most of the time I think that comes through the application when you’re adding them into recording services. But I have seen from time to time, mostly with my migrations, it doesn’t quite get set. It’s just another example of specifying roles that a user would get.

Robert: Yes, I agree, that’s actually a great example, yes.

Carlos: We’ve painted the picture, we shouldn’t be giving folks DB owner role. We’ve talked about why. Some of the other things, but now they’re saying, “Wait a second, I don’t want to be bothered every time a user needs to create a database, but maybe I don’t want to give them that DB creator role or there’s other things like they want to do traces.”Another one that haven’t talked about is viewing dynamic management views. So that if they want to be doing a little tuning or performance and you don’t want that person to be coming back to you all the time requesting these little one-off permissions.

Robert: Sure, understandable. I can certainly respect the need to want to see that kind of data. Because, as stated earlier, we want our developers to be able to go in and view those kinds of things. So that they can tune their queries and we have a good, stable, fast running, environment.When developers and DBAs get along then your end users are very happy as well and your organization is happy as a whole. As far as the DMBs…

Carlos: I think we can also do the view database state or view server state, if they wanted to give them the whole server levels.

Robert: That’s right, yes.

Carlos: Would be another way to do that.

Robert: Yes, there’s many ways to do that, that’s correct. [laughs]

Carlos: Another part that I wanted to get to was your development and putting together of these roles or some of these permissions into a script or into a role that you’ve called DB developer.

Robert: That’s correct. I’m really big on this role, it’s something that I use in my organization and our shared development environments that we have. It gives developers the ability to develop in a shared environment without having the DB owner role.There’s no risk from my perspective about having any of the things that we talked about earlier. We’re saying, “Well we don’t want to mess with any kind of restore chains” or, “We don’t want someone accidentally dropping a database” or, “We don’t want someone changing a symmetric key that we don’t want changed.” [laughs] Just as some examples.

It gives you the ability to do all those things that you want your developers to work on, which includes stuff like being able to script out objects so you can see the code behind things. There’s not a role that gives you that except for the DB owner role.

My DB developer has that permission in it, where you can still do those things.

Carlos: Robert’s been very generous with us compañeros, and he’s going to make that script available for us on his site. We’ll have the link in the podcast notes, we hope that you’ll check that out and start playing with that.Another way that we can help manage some of these permissions, and this is more from an administration perspective and I think we’ve been headed in this direction for a long, long time. That is in the using groups for permissions.

Robert: Yes. I strongly recommend using groups. It’s recommended by Microsoft to do so. Basically the way that my environment is set up is, we have groups set up for anything that we think needs to have read only access. If someone needs that access we simply take their user and we put them into that directory group.Instead of going into the server and creating a login and creating a database user and then putting them into a database role. The reason why is whenever they change roles or if they leave the organization, it’s very easy to remove them out of those permissions.

If we’re wanting to remove them by using AD groups, all we do is take them out of the active directory group, that’s very easy to do. Whereas if we create a SQL login for them, then we have to login into the server, we have to delete the user and hope they don’t have any objects in the schema.

Then we have to delete the login. [laughs] There’s a decent amount more work that goes behind that.

Carlos: That’s a great point there. We can be assured that they’re not going to own any of those objects and it’s much easier to put them in or take them out. We even use that for our DBAs, creating that group and then adding them.That way, we know who has elevated privileges on a server, based on those groups.

Robert: That’s correct. That’s also another place where principle of least privilege can really come in. Let’s just take a really easy example. Let’s say that you have a server that was manually restarted. You know for a fact that on the Windows side that in order to be able to restart a server, you have to have administrator rights.You go into the server and you say, “OK, who has admin rights?” That basically narrows your list down of, “OK, well one of these people had to have been the person who did it.”

You can apply the same thing at the SQL server level. If a table is dropped, you can say, “Well I know that these are the people in this database that have the permission to drop these tables. It has to be somebody in this list.”

Carlos: Right. Making auditing a little bit easier there.

Robert: Yes.

Carlos: I think that is going to wrap up our discussion on privilege of least permission. Is there anything else that you think we should hit on before we change gears?

Robert: No I think that’s good.

Carlos: OK. Robert thanks for this discussion about roles and permissions, I think it’s been valuable. I definitely think everyone should be checking out your DB developer role. I think it will be very, very valuable. As I’m always trying to create value for folks listening, I’d like to share another way that they can learn about SQL server.[music]

Carlos: Hello there compañeros, I want to tell you about a unique training opportunity that is unlike anything you’ve encountered. As a listener of this podcast you’re entitled to a special offer. SQL Cruise is a premier training experience set in the most unlikely of places, a cruise ship.Tim and Amy Ford have created a wonderful culture on SQL Cruise. With five or six technical leads from various industry sectors, you and roughly 20 other students will talk shop in classes while at sea. While you’re in port, you can enjoy the wonderful world we live in either by yourself or with the group.

The beauty of SQL Cruise is the unique relationships you will form with other SQL cruisers. Worried about leaving your spouse? Bring them along. I did and she had a great time with the group. In fact, I have been on SQL Cruise twice and I’m looking to go a third time.

You may be wondering if this is serious training and I can assure you it is as good as any conference you will attend on land. It never crossed my mind that I could be talking about SQL server with several Microsoft MVPs on the beaches of St Johns.

I know you must have other questions so I suggest you head over to SQLCruise.com and check out the site. Once you are satisfied and you want to sign up, when you get to the question, would you be so kind to share the name of who referred you and what source material led you to us?

You enter SQL Data Partners and Tim will ensure you get $100 off the training registration. This is a great offer and I hope this $100 savings will entice you to check out the site. I did and went on the Caribbean cruise and had a great experience and I know you will too.

Maybe I’ll see you on board. So head on over to SQLCruise.com and use the code SQL Data Partners to save $100.

As we’re wrapping up here Robert, we have a couple last questions we’d like to have you share a little bit more about yourself. What’s your favorite SQL tool? It can be a paid tool, free tool, but what tool do you like and why do you use it?

Robert: That’s a really tough question. There’s a lot of tools that I use that I like. SSMS is my favorite tool.[laughter]

Robert: I’m kidding, I’ve got a better tool than that. If I had to give a really good tool to use, I would say probably Spotlight by Dell. That is probably the one that I really enjoy. It give a lot of really good data and the visualization of it, to me, is what really, really does it for me.I love other products by other organizations out there and I actually use them. I’m fortunate in my environment to have a lot of toys, as it were. In my opinion, Spotlight is something that I’m into every single day. I’m looking at stuff every day in there and evaluating things in there.

Carlos: OK. The Dell tool, that’s paid. It is interesting actually, I went to a session by Adam Machanic once and he talked about monitoring. To me, Adam Machanic is one of the people writing that knows so much about SQL server.

Robert: He’s forgotten more in the last day then I’ve learned in the last year.[laughter]

Carlos: That’s right. One of those people. He made the comment, he’s like, “Oh well, I would put a monitoring solution in all of my environments.” Interesting that, while yes, you could homegrown some of your own, that ability to have a product that is stable, is consistent along all of your environments and then be able to give you some of that history. I thought that was an interesting comment.We’d like to hear about an instance or experience that you’ve had that helps you remember why it is that you enjoy being a database administrator.

Robert: That’s really tough, do I have to limit it to one?[laughter]

Carlos: We are looking for just that one.

Robert: OK. If I had to give stories, I could mention SQL Saturdays, those are always great. Another one that I’ve done is, I’ve done a couple data center migrations and those are fantastic. You learn a lot during those, but I would say, probably one of the best DBA stories I had is something that I actually made a blogpost about.It was where I basically implemented compression earlier this year. Simply by compressing a partition in a single table, a nightly load increased by probably 20%. I was simply amazed by that. That was incredible for me, that’s the one thing that sticks out to me.

The reason why it was so big is because it was something that I had wanted to do for a long time. I had partially implemented it back six months ago, or something like that. Then two or three months ago when I got around to finishing it up and getting it completely done, the day that I got it completed, they came through. It ended up being one partition in one table that I compressed that you really did a lot of good work from it.

It was just a really good feeling to be able to implement something from an administrative side. Developers all the way down to end users saw a great benefit from it. They felt that it was a really good thing and they wanted to know where my magic wand was.

[laughter]

Robert: When you do things like that, it really does make you feel good and it makes you feel very proud. That’s the best I got.

Carlos: Sure, very good, very good. Any time we can add value there to the business, polish our credentials a little bit if you will. It makes us feel good. Very good. Before we get to the last question, want to remind folks if they want to connect with you via Twitter, they can do so at SQLCowbell. You have your new blog at sqlcowbell.com.For our last question Robert, if you could have one superhero power, what would it be, and why would you want it?

Robert: I’m going to go with…That’s a really tough question.[laughter]

Robert: I’m going to go with the ability to see the future.

Carlos: OK. There you go, divination, whatever that…

Robert: Yes, that’s a great word for it, yes.

Carlos: Very good, very good. Well Robert, thanks again for being on the show. I had a great time. I think the folks who are listening will be able to learn a lot.

Robert: That’s great, and Carlos, thank you for having me. It’s been a pleasure. As always, I enjoy talking with you. I’d love to be back on if you ever wanted to have me. I’m an open book. If you reach out to me on Twitter, I am more than happy to answer any questions. I’ve even answered questions to people who have written in Japanese kanji before.[laughter]

Carlos: Nice, man of many talents.

Robert: Yes.

Carlos: Thanks again Robert. We’ll see if we can have you on again. Compañeros, thanks for tuning in. It’s been another great show. We’ll see you on the SQL trip.[background music]SQL Data Partners.

Granting SSIS Package Administration Access to Developers

In many instances there is a development team or even a warehouse team that needs to run SSIS packages and you want to store those packages in SQL Server and run them through the SQL Agent Service.  You may not; however, want to grant those users/group sysadmin rights to the machine.  The following describes my experience in setting this up.

SSIS_DB1First, you must add the user/group to the DCOM Configuration for “Microsoft SQL Server Integration Services 11.0” (SQL 2012), which can be found in the Component Services MMC.  Right click and choose properties.

 

 

SSIS_DB2You can now add the groups to two security sections–“Launch and Activation Permissions” and “Access Permissions”.  It was my experience I had to have them in both groups.  I granted full rights in each group.

The final component is to add each user/group to the local group “Distributed COM Users”.  It seems like it won’t even authenticate a user for the groups you set earlier unless they are in the local group.  This will allow users to connect the Integration Services Instance.SSIS_DB3

At this point you should restart the Integration Service services.

If you haven’t already, you can go ahead and create your Catalog Database.  We won’t go over those steps here–perhaps in another post.

The last step is to allow them to deploy and manage the packages on the servers.  I add the group to the SSISDB and grant the role of ssis_admin.  Now your teams should be able to function completely within the SSIS parameters and not have permissions to drop database objects or modify security settings.

SSIS_DB4

Sizing the SQL Server Error Log

Sometimes SQL Server generates so many messages you think it actually enjoys it.  While there may be some other factors in creating a new log by default, it appears to me the most common scenario is when SQL Server restarts.  If your systems are anything like mine, you want SQL Server to stay up and this can cause the logs to get very large and this makes finding specific message more painful than it needs to be.

To me it makes sense to separate the logs by day. EXEC sp_cycle_errorlog will create a new SQL Server error log.  I normally create a job that runs a midnight and creates a new error file.  By default, SQL Server only keeps 6 error log files and I normally increase that number to 30.

Files To Keep
If the server has already been set up, I normally use the GUI by right clicking on SQL Server Logs and choose configure. If I am setting up the server, I have this piece of code in my scripts.Screen Shot 08-01-14 at 01.59 PM

[sourcecode language=”sql”]
USE [master]
GO
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SoftwareMicrosoftMSSQLServerMSSQLServer’, N’NumErrorLogs’, REG_DWORD, 30
GO
[/sourcecode]

Cycling the Logs
This code will create a new job to run at midnight and cycle the logs.

[sourcecode language=”sql”]

USE [msdb]
GO

/****** Object: Job [Cycle Error Log] Script Date: 08/01/2014 13:31:39 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/01/2014 13:31:39 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Cycle Error Log’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Cycle Error Log] Script Date: 08/01/2014 13:31:40 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N’Cycle Error Log’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’EXEC sp_cycle_errorlog’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email protected], @name=N’Midnight Ride’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20140801,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
— @schedule_uid=N’bca9d15e-e47f-40a4-a236-e14020f77b76′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

[/sourcecode]