Enjoy the Podcast?
This session is aimed at DBAs with or without a development background who keep hearing people screaming about PowerShell, but aren’t sure what all the fuss is about. The ideal audience are people who have some (but none is okay as well) experience with PowerShell, and everyone who wants to see what’s possible when you combine SQL Server and PowerShell together to not only make their administration life easier, but infinitely more powerful. I don’t tend to focus on syntax but instead on method and examples, allowing people to come up with their own ideas and get excited to learn the language on their own.
Whether you’ve dabbled in PowerShell or wondered what all the fuss is about, make no mistake: PowerShell isn’t going away and it is definitely something worth learning to make your life as a SQL Server professional easier.
In this session, you’ll see practical, real-world examples of how you can blend SQL Server and PowerShell together. You’ll also get a look at the SQL Server PowerShell provider and how you can incorporate it into your automation scripts. Instead of focusing on syntax, the session will plunge straight into examples and highlight various snippets of code and how they work. You can expect to see code examples on how PowerShell can do anything from comparing tables across servers, automating and centralizing your backups, and even doing in-depth security audits.
Why I Want to Present This Session:
In my current role as a senior DBA, I wear a lot of different hats, just like everyone else does. PowerShell is a great enabler for me because it allows me to automate a lot of different tasks and lets me focus on more important issues. As a result, I have come to love the language and I love the direction Microsoft has put the technology in. I get excited about sharing this passion with others, because I truly believe a more well-rounded individual who can “play in the mud” at the OS level with a tool like PowerShell is something more employers (either current or future ones) will appreciate.
(Or, How Brent NAILS the Pronunciation of Drew’s Last Name)
Brent Ozar: In this session at GroupBy, Drew is going to be talking—now that I know how to pronounce his last name I should just do it all the time: Drew Furgiuele (fur-ja-well). Did I get it right that time? Probably not.
Drew Furgiuele: You did, nailed it.
Brent Ozar: Will be talking about PowerShell. All right, Drew, you can take it away.
Drew Furgiuele: Thanks, Brent. Everybody, welcome. Brent, thanks for putting this conference on and having me. Today, I’m going to be talking about SQL Server and PowerShell. This is a little bit of a different talk about PowerShell. I find that a lot of times if you want to learn PowerShell I think the natural place you want to start is, “How do I actually write PowerShell?” Not going to focus so much on that today. Instead, it’s going to be more, “Here are some practical examples of things you can start thinking about doing with PowerShell.”
I’m going to have plenty of code examples and I’ll have plenty of links to the source material, to my code if you want to grab it, all that stuff, as we move through. This is more of what you can do and less of maybe how you can do it. Again, there will be code and things but I want to show you more about how you can start thinking about using PowerShell.
How Drew and PowerShell Became Intimate Friends
I want to talk a little bit about me first. I’m a senior DBA at a company called IGS Energy in Dublin, Ohio. If you are a customer, great. I’d love to hear from you and if we’re awesome, if we’re not awesome. I’m always looking for customer feedback. I’ve been using SQL Server since SQL Server 2000. Fell in love with it back then. I was one of those what you call accidental DBAs. I kind of got sucked into it because I was a dev at the time and we needed a database. People said, well, we have this SQL Server thing. So I said, okay, we’ll use that to store data and then it just kind of mushroomed from there.
Since my time at IGS, and I know we all wear a lot of different hats, I’m definitely no exception to that. I don’t think anybody else is, is that here at IGS my full time job and my title is a senior DBA but I also do a lot around release management and DevOps. I help manage things like code releases to production and helping developers tune queries and managing code bases and checking in change sets and troubleshooting release items and dealing with replication when it breaks production. All that fun stuff. So I have a lot of different things that I do.
Like I was telling Brent earlier, when I came to IGS I was kind of shocked but not shocked about how much we use PowerShell here. We use PowerShell for so many different parts of our business today in our IT shop. It really comes down to doing things like deployments, all published via PowerShell. A lot of our administration, our Exchange and Active Directory side, our admins are mostly PowerShell. They’re not relying on the GUI to make a lot of changes to things in those environments. SharePoint, they’re doing all the administration over there as well. We have developers that work with PowerShell to get their code deployments right and set up Visual Studio the right way. So, it’s a very, very powerful tool and we’ll talk about why I think it’s important for us to learn it here in a couple minutes.
If you want to reach out to me, my contact info is on the slide here. Again, my email address has my last name in it and there’s a lot of vowels and it’s a burden. If you want to email me, that’s great, fine. Probably Twitter is a great place to reach out to me. The two websites you see here—port1433.com is my website. When I blog that’s typically where I do most of my work. If you do go to this website there’s a couple links up here on the left-hand side. Clicking on this GitHub link, which I can’t get to over my wireless right now, that’s cool. It will take you to my GitHub where all the code examples today you’re going to see live. So if you’re interested in looking and using and playing with my code, you can get it directly from there or you could search for my name at GitHub. Again, it’s a burden so it’s probably just easier to click this link.
The other website I want to show you just kind of as a moment of Zen, I like to show this off at the beginning of every presentation. It’s a website called genesface.com. It’s a website we started here at work to show off how beautiful my boss is. We just basically take pictures of him when he doesn’t let us. He’s a fantastic sport about it. You can go check out this site and look at pictures of Gene. He really likes bananas. Enough about me and my goofy websites. Definitely, check all that stuff out and then reach out to me on Twitter or email if you have any questions or anything like that.
Over the next hour-ish, we’re going to talk about why I think it’s important that if you are a SQL Server professional in any kind of walk of life, whether that be a DBA, a developer, BI person, I think it’s important that you learn PowerShell. I’m going to give you my elevator pitch about it. You may or may not like it. You may or may not choose to run with it but I’m going to tell you why I think it’s important. Then I’m going to talk a little bit about the SQL Server PowerShell provider. What bridges the gap between actually writing PowerShell on your console and actually getting it to SQL Server and how the PowerShell provider helps with that. Then the rest of the time I’m just going to show you code. I’m just going to show you examples.
Why Using PowerShell Will Make Your Life More Awesome
Here’s my spiel about why I think it’s important that people get on the PowerShell train. PowerShell is in version five now. It’s been around for a while. It’s been something that Microsoft has been pushing. It’s just part of the Microsoft ecosystem now. Like I was saying at the beginning, when I came to IGS and I came into this role, I was at a little bit of a detriment because everything we were doing in terms of automation was happening with PowerShell. Having a little bit of dev background helped but not knowing the syntax of PowerShell put me at a detriment for a little while, but I work with some phenomenally smart people that were able to sit with me and show me things, point me in general directions to show me here’s how we’re doing this, here’s how we’re doing these deployments, here’s how PowerShell is interacting.
It’s definitely helped my career because I can go write PowerShell and not have it do anything with SQL Server but I can use it to interact at the OS level or at the domain level or the network level to do a lot of different various things. It just kind of makes you a more well-rounded IT professional to know a scripting language like PowerShell. You know, whether or not you’re going to use it with SQL Server is up to you. I’m hoping you do and that’s the purpose of this talk but understanding PowerShell and even basic cmdlets really helps.
It’s one of those things too that if you work in kind of a siloed environment where you’re in charge of database stuff and you’ve got administrators that are responsible for Active Directory or if you’ve got storage admins that are responsible for storage. Or you’ve got VM admins that are responsible for your virtual servers or your physical servers if you still have them. You know, it’s one thing to go to them and say, “Hey guys, I really need this done.” Or, if you write a script for them that you’re able to test maybe locally on your VMs, maybe in Hyper-V or VMware Workstation. You can go to them and say, “Hey guys, I wrote this script to do this. Can we look at this and maybe do something like this is production?”
Having that common language, if you will, to be able to talk to your system admin team or your storage admins or your VM admins, whatever, being able to kind of have that common language to say, “Hey, I’m going to write you a script to get this done” versus them having to take the time to open your ticket and figure out how to do it and click a whole bunch of buttons. I find that that kind of helps facilitate, get things done a little bit faster.
Then, of course, when you start to talk about things like automating processes, like automating your backups or automating permission audits or looking at replication latency, or anything like that, that can all be done with PowerShell too. You kind of mention automation and PowerShell in the same breath a lot of times when you’re talking about these things. You know, if you really want to get serious about automation and deployments and things like that, you’re probably going to want to look at PowerShell.
Of course, as we move into more cloud-based computing with Azure or anything like that, a lot of that is going to be PowerShell based. You can click around all day in the Azure web console if you want but you can get a heck of a lot more done faster if you really take the time to learn some of the cmdlets. Things like Nano Servers or IOT devices—the market for PowerShell and integration is really growing. I think it’s important that you take the time to learn how to use it.
The SQL Server PowerShell Provider
That leads me into talking about the SQL Server PowerShell provider. This is where a lot of people may nod their heads or shake their heads, depending on your experience with it. I find that a lot of people who aren’t using SQL Server and PowerShell, this is probably the reason why. There’s a little bit of history here where, when Microsoft first started putting together PowerShell support for SQL Server, they had what was called the SQLPS kind of mini shell. It was a module that wasn’t really a module that was a module. If that makes any sense, and it doesn’t, by design I think.
What that was what it was a way for you to use PowerShell in SQL Server and to get some basic cmdlets support. But it wasn’t that great, to be fair. I think it has turned a lot of people off to using SQL Server and PowerShell. That’s where the community has stepped and done cmdlets and done changes and done things to help improve upon that. To their credit, the SQL Server team listens. We have the Slack channel; we have the Trello board. We have all these different things that I’m going to show you later that we can interact with the Microsoft people to show and get these things done to get these fixed, to get things in.
With the SQL Server Management Studio 2016 release, there was a lot of really good improvements in there. The community was so thankful to have Microsoft’s ear to get these things fixed and get these improvements in there. It’s been a really good time and it’s getting even better. They’re continuing to listen. We’re providing a lot of feedback. Aaron and Chrissy work extremely hard to work with people on the Microsoft side of the house to get things fixed. It’s just constantly getting better.
The PowerShell module is how we’re going to be using all our examples today. The reason I advocate for the module is it just makes getting to SQL Server stuff in PowerShell a lot easier. There are some caveats, of course. I would say that if you haven’t used PowerShell and SQL Server today you’re best served for installing SQL Server Management Studio 2016 because that will automatically install all the modules and the providers and the stuff like that for you. If you’re using older versions of Management Studio, there can be conflicts in different libraries that the provider tries to load. It can get kind of hairy and you may have to go in and modify things, like your environment variable paths for a lot of different things. There’s ways to fix it but it’s so frustrating. Part of that frustration is what turned a lot of people off. I’m here to tell you that it’s a heck of a lot better. If you just put a little bit of time in it, it pays tremendous dividends. Today, like I said, we’re going to be talking about the provider. The link I have at the bottom of this sheet here will give you more information about that stuff. Again, these slides will all be available at the end.
Hands-On Walkthrough and Code Intro
Let’s stop for a minute and actually look at the provider. What do I mean when I’m talking about the SQL Server PowerShell provider? What I’m going to do is I’m going to open a new PowerShell window here. Typical blue on white, just like we normally see if we open a PowerShell window. In order to work with the module, what you will do is you will import the module. If you’re not familiar with PowerShell, the command to do that is import module. If you have SQL Server Management Studio 2016 installed, any version—well, not any version I should say, but one of the more recent versions—you can type SQL Server here. import-module sqlserver. Boom. Hit enter and that takes care of loading all of the different modules that you’re going to need and all the different cmdlet support and all the different things we’re going to use today. That loads it all for you. You don’t need to manually load libraries. You don’t need to know .NET to do this. This takes care of pretty much everything for you.
SQL Server Exposed … As A Drive in the PowerShell Window
The biggest thing that this does is that exposes SQL Server as a drive. What do I mean by that? What we can do then, like we would normally if you’ve ever been a DOS guy or DOS gal, you’re used to browsing your directories, like “Hey, I want to go to my scripts directory on my C drive.” Well, SQL Server is now a drive. If I change my directory to SQL Server and I see what is in there, I have a lot of different things. Again, this is because I’ve loaded the module. It exposes what’s called a PowerShell drive dedicated to things inside of SQL Server. So I’m looking in here and there’s a lot of different components to the module. I can look at xevents. I can look at analysis services. I can look at all these different parts of a SQL Server.
The first thing that you need to know is is that this isn’t necessarily the SQL Server that’s running on my machine. This can be any SQL Server. Well, for this demo I’m going to go into the SQL directory. When I do, I can see that it knows that I have an instance of SQL Server running here on my local machine which is named KnifeCastle. So if I browse to KnifeCastle and look in here I can see that it knows that I have a default instance installed. If I had a named instance it would have the named instance in there, but for now we’re going to go ahead into the default and we’re going to look. Lo and behold, I’ve got a lot of things in here. I’ve got things like databases, credentials, rolls, mail, logins, linked servers. Oh, cool, all right.
Let’s go ahead and take a look at databases. What do I have in there? What databases are you running, Drew? I’ve got a lot of databases in here. I’ve got admin, I’ve got a couple copies of the Adventureworks, something named Somedatabase. Let’s keep going. I mean, basically, this exposes each one of these as a path. When I go into something like Adventureworks 2014 and I get a directory, I see even more.
Now, those of you following along at home, this maybe should start to look familiar to you. You’ve probably seen these things before, these words, these verbs, these nouns. That’s because a lot of these things here are the same things you tend to see on the left-hand side here. Tables, stored procedures, functions, views, all of these things in some shape or another kind of fall into what you see in Management Studio. That’s kind of the first thing you need to understand about the PowerShell module is that the underlying thing that makes this work is called SMO. It’s the same libraries, it’s the same DLLs, it’s the same thing that Management Studio uses to do everything there, which means that since it’s using the same libraries, if you’ve ever wanted to not click something in Management Studio, you can automate it with PowerShell in the module because you expose all these different things in here.
Two Key Takeaways – Working with Get-ChildItem and Get-Member
For those of you that aren’t PowerShell users, this is where things get a little interesting. Currently, I’m in the Adventureworks database in my databases folder on my local instance. Let’s go ahead and take a gander into the tables directory, which exposes all of my tables. Now, if you don’t take anything away, if you’re new to PowerShell and you’ve never used this before, there’s two handy things I want to show you today that will make learning this a heck of a lot easier.
The first is Get-ChildItem. If you want to work with something inside of PowerShell like this, you would do Get-ChildItem. When I do that, I get exactly what I just did. It’s basically DIR in PowerShell is an alias for Get-ChildItem but where the real power of this comes in, just really puts the power in PowerShell, is I can declare a variable called tables and I can say Get-ChildItem. That then puts into the tables variable that starts with a dollar sign, all the things that are in here.
Then if I want to say tables.size because all of these things have properties—that doesn’t show me anything. That’s great. Let’s try another one. There we go. Table.name will return all the different names of the tables. Tables then becomes an object that I can work with inside of PowerShell.
The other thing that’s important to know is that if you’re working with really anything in PowerShell, not just SQL Server, it’s Get-Member. What Get-Member does is it will take whatever variable you pass it via the pipeline and show you all the different things you can either do to it or learn from it. For example, if I wanted to drop all of these tables, which would be really bad for all of my demos, I could say .drop as a method on this thing and it would drop all my tables, or I could do columns or I do events, size, indexes.
You know, the other thing I will say is with Get-ChildItem we can even filter this down even more. We can say where-object, and then we kind of do this kind of curly brace, and then a $_ to be the pipe variable. We say name equals person. So I want to get the table named person. I won’t put it into a variable name tables just to make sure we’re consistent. I’ll just put it in a variable called table and I’ll call it person. Beautiful. Now if I take a look at table, it’s just got person.
Then I can do things like size—no I can’t. But I can do things like indexes. I can see all the indexes on these tables. I can basically expose all these different properties that you can expose in Management Studio, you can expose here. That is an important piece of everything we’re going to see today. What we’re doing is we’re using the provider and we’re using Get-ChildItem to get all these things together in one place. Then we’re going to use methods and properties on these things to interact with it.
Eventually, what you’re going to think is, “Drew, that’s great, but I could do all that in T-SQL or I can do all that in Management Studio.” You’re absolutely right. You can. I’m not going to sit here and say that everything you’re doing T-SQL today you should do in PowerShell because that’s just not what PowerShell is designed to do. It’s not meant to replace Management Studio. It’s not meant to replace T-SQL. What it is designed to do though is to make multi-server administration easier.
Let me give you a scenario. Let’s say you were asked to say, hey, we need to know what all the different table sizes are on all our different database servers. Maybe they’re on different domains, maybe they’re in different locations. You could use PowerShell to basically iterate through a list of servers, pull down all the databases, get all their sizes, export it to a CSV and boom, you’re done. The name of the game here, guys, is automation and being able to administer multiple servers at the same time. PowerShell is probably—at least in my opinion—probably one of the best ways to do that. Sure, there’s utilities and third-party stuff and you can do multiple server queries inside of your server registrations in Management Studio.
Not saying it’s meant to replace any of that. It’s another way of doing it. I like the automation aspect of it. The fact that you can write these scripts and you can have them execute at your leisure via an agent job or some other windows scheduled task or your enterprise scheduler du jour, makes this really powerful. The provider is what enables us to do all this stuff.
When I’m talking about the provider and browsing through these directories, that’s what we’re really doing with all of my scripts is we’re just basically going to go get an object, whether it be a table or a database or an index or I don’t know, an XML collection. It could be anything really. We can do different things with it.
Brent Ozar: People are getting excited about the SSIS stuff too. They’re like, “Wait a minute, I can script SSIS stuff?”
Drew Furgiuele: Oh my god, yes you can. I might have time to do a demo on that. I do have some scripts around that. One thing about SSIS stuff you can do, guys, the SSIS path is a little trickier but not too much. If you’ve ever wanted to automatically deploy packages to sys or change environment variables or copy environment variables without having to write a lot of really heinous T-SQL, PowerShell is the way to go. Just want to throw that out there. So, yes, sys is a major component of this as well.
What I do want to talk about though is style points. This is where I put my own personal slant on things, I’m probably going to get a lot of moans and groans from people, that’s okay. I want to just kind of illustrate the difference between some different types of code that I’ve seen people write or I’ve seen on the web and examples as I’m doing research. Why do I push the provider so hard? Like I said at the beginning, the provider had a bad name. People had to find ways to work around the provider to do what they wanted to do.
What it usually entailed is this first code block I have here that I have labeled “gross”—because it makes me want to barf—is we’re doing kind of old school load with partial name to load the SMO library on your machine. Why do I say this is gross? Basically what these four lines do is I’m going to load a library. I’m going to tell SQL Server to use my local or .instance. Then I’m going to create a new object of SMO.server and pass at that server name. Then I’m going to get a list of databases from the server object.
That in itself is not bad code. I’m not going to sit here and say that anybody that writes this way is dumb or wrong in any way, but you may want to consider using the module instead. This one is particularly damning to me because load with partial name is deprecated by Microsoft. Sooner or later, I mean, sure, there’s tons of things that are deprecated that never go away, but this is something that Microsoft is trying to get away from and they’re pushing you more towards the less gross example here which is somehow more gross and less gross at the same time. In order to load the SMO assembly, you need to use add type. Well this has a big disadvantage and you can probably see it because it spans two lines. You need to basically know what version of the library you want to load, you need to know the public key token, and if you’re ever sharing code with somebody that isn’t running your version of SQL Server, this probably won’t work.
Okay, you can code around that. It’s pretty bad and it’s hard—well, not hard, but it’s definitely something you have to contend with. I will say that this is kind of the way that if you need to ever load any assemblies in PowerShell, this is the way I would probably suggest that you do it. But then again, we’re doing the same thing, we’re getting a list of database.
What I never want to see and I hope that nobody ever does, is use invoke SQL cmd to get a list of databases. See, if I have one pet peeve in PowerShell it’s if I have to write T-SQL in PowerShell then I’m wondering if I did it right because I don’t want to just basically have PowerShell be a series of invoke SQL cmds to throw things at my database servers because why didn’t I do it in Management Studio to begin with, right? Sure, I can use invoke SQL cmd and iterate through server names and pass them through invoke SQL cmd, but it just feels dirty.
When I’m looking at this example here, this one is bad for two reasons. One, because I’m hardcoding SQL in my scripts and that’s not something that I particularly care to do, but at the same time this just returns a list of database names. It doesn’t necessarily return an object. I can’t do those methods and I can’t do those things that we saw in the earlier example where I was pulling down properties. It’s just basically returning a name from a query. It’s not the same as these first two examples, good or bad as they may be. It’s not returning a true object back to me. It’s just basically giving me the names of all the databases on the server. I can’t do anything with them other than just enumerate through them, if that makes sense.
Down here at the final part is what I did. I’m basically saying import the SQL PowerShell module. If you’re using SQL Server 2012 or 2014, you would need to do import module SQLPS. Again, believe me, I know there’s differences in the module. It’s all part of the journey here, folks.
When you import the module, you can Get-ChildItem on the list of those databases from the SQL Server path that you say and it just loads them all in. I didn’t need to manually load the assemblies myself if that makes sense. That’s why when I’m telling people if you’re just getting started with this, leverage the module. Let it see how far it can take you. Don’t waste time with this antiquated, or not antiquated, .NET code to manually load libraries or think that you need to understand how references work in this thing. Just trust the module to load what it needs.
Again, just like everything else in SQL Server and PowerShell, it’s not completely perfect. There are some times where you’ll need to load modules. The biggest one for me is replication because I love replication. A lot of the replication modules don’t get loaded automatically for you. So you need to load those yourself. Again, I’m using add type to do it as opposed to load with partial name because if they ever do get deprecated then I should be okay, hopefully.
Code, Code, and More Code!
I’ve talked a little bit about the module, I’ve talked about why I think PowerShell is important, so I’m going to spend the next half hour going through some demos and showing you some problems that I’ve solved with PowerShell. Along the way, we’ll see some code and I’ll talk through it. The number one thing I want to say, guys, is that if you are new to PowerShell, don’t let this intimidate you. All this code you can get and you can test on your local. I encourage you to do that. I issue the standard boiler plate. I’m not saying I have this code out there, go run it in production. No, don’t do that, please. Don’t just take code you find on the internet and run it in production. Take the time to download it and look at it. Ask questions.
I’m certainly a resource to be able to help you understand this and there’s lots of other people that I’m going to show you at the end here as references that would be able to help you as well. Super great community out there for PowerShell. Believe me, there’s tons of people that know a heck of a lot more about this than I do because there’s always somebody better. Don’t be intimidated by this code because as we step through it I think you’re going to see that it’s actually very easy to understand once you understand how these cmdlets and the provider works.
Real-World Example: Export Database Objects (Not Just Tables) for DR Purposes
I mentioned before replication. A real-world example that I want to talk about is exporting database objects. Here’s a nightmare scenario for you: you’re working in an environment that relies on a lot of replication. Guilty. You have situations where you need to do a replication snapshot, well that in itself is not always a good thing. Maybe your subscribers get very behind or there’s changes that you’ve made to objects or you’ve added new tables or new views or new stored procedures to your replicas and you need to get them out there. So you do a snapshot. For whatever reason, you’re doing a snapshot.
The way the default behavior replication works is that when you do a snapshot, any tables, any views, anything you’re replicating down get dropped and recreated. Well that can be a problem because if you have custom indexes or permissions or things setup on these things, that could be a really bad day if your reporting replicas start going through the roof with CXPACKET waits because—and then CPU usage and people are complaining that things aren’t finishing.
The point is that I wanted a way to backup all of my schema without having to rely on, I don’t know, backing up the entire database, if that makes sense. The way I approached it at the time was that this is a replica and we only have tables replicated from production, so the data should be the same. If for whatever reason I lost my replica, I could get my data, but I couldn’t necessarily get my indexes. So what I wanted was a way to basically export indexes and permissions and user-created objects out of the database without having to do the full backup.
What we’re going to do is we’re going to look at a script called get database objects. Let me go ahead and change directories here to my scripts directory where I have all of my examples. You’ll see here in my directory I have a bunch of ps1 files. Now, ps1 files by themselves can be executed with this ./ notation. If you ever write your own module or your own cmdlets, or own functions, this is basically what you’ll be doing.
What I’m going to do is I’m going to run a script real quick. Before I run it, let’s just step through this. I’m going to run a script called get database objects ps1. I’m going to pass it a server name. Now this dash server name is a parameter that I’ve defined in my script and that the IntelliSense of the console knows about. I’m passing it another parameter called database names. So I’m saying go to my local host, go to Adventureworks. We’re going to save to this thing called a file. The file name is going to put it out here in my temp folder, and I’m using the -verbose switch.
The other thing I want to say is that verbose is awesome as you write scripts because it’s kind of an optional output parameter. If we run this without verbose, we won’t see all this yellow text you’re about to see. So if you don’t necessarily care about output, it’s kind of up to the user to say I want to see this output or not. That’s kind of like a best practice if you will.
Let’s go ahead and run this. What this is going to do is this is going to connect to my SQL Server. It’s basically iterating over different object types in my database and scripting them out. In other words, right click, script as, create to. But it’s iterating over everything in the database for me. It’s also iterating it over in such a way that when I run the script it should be able to recreate everything for me in the correct order.
Let’s say you try to create a table and you have a user-defined type defined in the table and then you try to create the table without the type being there. It’s going to blow up. This thing is designed to script things out in such a way that it will basically script them all out in a repeatable way that you can rerun in a new database. It’s going to script out 72 tables from Adventureworks. It’s writing this all to a file in real time. So if I go to my C temp drive I should see Adventureworks objects2. That’s a file size that’s rapidly growing now, that must mean it’s getting close to being done.
Brent Ozar: There’s a question too. The SQL module, does it come with the current SSMS or do I have to have the 2016 engine?
Drew Furgiuele: It comes as part of SSMS. You don’t need to have the engine installed.
Brent Ozar: Cool.
Drew Furgiuele: Now that this is done, let’s go ahead and open this file. It’s gone ahead and it has created all of this stuff for me out of Adventureworks. So if I were to create a new copy of my database, we’ll just call it “database 2.” If I go and create new database and I come over here to database 2 and I run this guy, it should recreate all of my objects as if it were Adventureworks. It did. It put everything in there for me—keys, foreign keys, indexes, all that stuff came with it.
The question is, how do we automate something like that? Like I said at the beginning, this was kind of built to serve a purpose to help me with situations where replication was being a pain in the butt. If we look at the script—here’s get database objects. This is a kind of standard PowerShell script. Let me just step through some sections here and talk about why they are the way that they are.
I mentioned at the beginning we have those parameters defined. You can define parameters at the start of a script and tell it, “I’m expecting string. I’m expecting an integer.” I’m expecting something. Then if you want to limit what the user can input, I have this validate set. So I can save to file or a database. Then I get down here in the main block of my function with a begin, basically write verbose, which is what I talked about, that verbose switch. If I exclude that, it won’t write out to the console starting script.
So I have a function that defines scripting out the objects. Yada, yada. Process where it does most of my things. There’s a couple lines I want to call your attention to here. It’s these guys. “Drew, you just got done talking about using the provider to get the server and do all these different things.” Well, that’s great if you’re working with one server at a time but this example I’m actually using two different servers at a time. The reason for that is is that the server I run the script on doesn’t need to be the server that I connect to to script the objects out. Again, that’s kind of the beauty of PowerShell is having that kind of separation.
The main object that I’m using to get this done is this scripter object. The scripter object mimics, or doesn’t mimic, or uses, or whatever the right terminology is basically the right click functionality of this script table as create to drop to. Basically, all of these different options are part of that scripter object. You can set different options. How big do you want the batch sizes to be when you script out an object? Do you want a batch terminator? Do you want to script primary keys? Do you want to write this out to file? All these different things you can control with this object.
Again, the reason that I know about this isn’t because I’ve spent time on msdn or any TechNet articles about it. It’s just I created this scripting object and then I did a Get-Member on it. It basically exposes all these things for you. A lot of them make sense, some of them you may have to look up like I did because I’m not really that bright. But you can look at this and understand that this can control how I export things.
So then what the script does is it creates this scripter object and then it finds all the different types of objects that I want to script out. So for each database in my server, find schemas, find users, find user-defined table types. Just like we saw at the beginning but instead of a table or whatever, I’m using a variable here called D which is basically every database that I passed the cmdlet to iterate through and say give me the schemas, give me the user-defined functions, give me the views, give me the foreign keys, give me the indexes. Script all these out. It just basically iterates through and it does them and it writes them to a file.
The other functionality here, and this is what I ended up doing. Once you write to file, the next thing becomes, can I write to a database? Because if I’m able to backup all of my commands or all of my schemas to a database, then I could just backup that database and if someone forgets to put an index back, boom, I’m covered. I don’t necessarily need to back up the whole database to then restore it somewhere else and go get an index that I lost.
So that was kind of the impetus behind creating something like this was an easy way for me to point this at a server or a series of databases, have it run at a scheduled time. I can run this once a week, I can run it every other day. Set it up to pull down that information, save it to a file, save it to a database, then, hey, you know, I have a report that’s taking a long time. Oh, you know what, we snapshotted that table as part of the last release. We forgot to put an index back. Well, there I have it. I can go to this file. I can go to that database. I can pull this stuff out, rerun it and everybody is happy. That was something I used PowerShell to solve.
Couple other things to call out in this script is that I wrote my own function inside of a function. If you’ll notice here, I’m calling this thing called script objects. Well that’s not native to the language, that’s something that I wrote. If you scroll back up to the top here, you’ll notice that I wrote a function called script objects. So you can write functions like you do in a lot of other different languages to basically say I want to call script objects and I want to pull this stuff down. Except when you call functions in PowerShell, you don’t use parentheses, you just separate them by a space and you pass those things. That’s what this is doing.
I’m a big fan of using bulk copy to get data table objects that you define inside of PowerShell to write to a table in SQL Server. We’ll see an example of that in a minute. So that is a script that kind of saved my bacon a few times and missing indexes and things coming back down. So very similar to what you would save from like doing like say a DACPAC but this is just something that writes raw SQL as opposed to use a DACPAC. So just an alternative. Again, I like it because I can schedule it to run. I can point it at different servers. I can run it at different times. I can run it at a specific database. All at a command line. That was kind of why we did that.
Real-World Example: Dynamically Copy Database Tables from One Database to Another
Let’s take that a step further though. What about dynamically copying database tables from one database to another? This is a little trickier. In the previous example, we looked at how we can get the schema out but now let’s say we want to copy from a table from one database to another. Here at IGS, we have a master data management process where we have a database that has kind of a set of controls, we have identifiers that identify different things like people or utilities or what have you.
A lot of different databases consume that data, so there has to be a way for us to get that data to different servers at different times if changes are made. A lot of different products out there do that but to get us off the ground as we continue our journey through our MDM process here, I wrote a process that can dynamically copy tables from one database to another. That in itself isn’t that hard. Where it can get tricky is when you run into things like foreign keys or indexes on tables. Same thing we saw before. If I copy a table somewhere, I want to make sure that if I do need to copy the data or there is a schema change, I need to drop it, recreate it, put back everything that was there to begin with. That can be a little trickier than it sounds but we’ve already seen code that shows us that we can pull that stuff out and use it.
Let’s take a look at an example here. Using the same example we were seeing before, this time I’m going to run copy database tables. Pull this out of my notes here…
Brent Ozar: I was like you can’t run it there.
Drew Furgiuele: You know, challenge accepted, Brent. I’ll work on that. This time, I’m going to copy some database tables from Adventureworks 2014 to this Somedatabase. Let’s just do that. Okay, so Somedatabase has things in it. No, you know what, we’ll mix it up. Let’s go off script. Let’s copy to this new database that I just created that’s completely empty, aside from the objects we created.
We’re going to go ahead and copy this stuff. Let’s look at this script real quick. I basically have a source server name, a source database name, and a source schema name. We’re basically only going to copy a schema. I wanted that to be an option. I don’t necessarily want to copy every table but I don’t want to call the script a number of times or I don’t want to pass it a list of tables, maybe I just want to pass it a schema and I want you to copy everything out of that schema from one server to another. Perfect. Or, one database to another.
I’m going to copy the sales schema out of that and I’m going to copy it to local host, to a database called database two—let’s call it database2. Let’s see what happens. Database2—never type in demos. Database2, destination schema. I’m providing a working directory here and I’ll explain why I’m doing that in a minute. I’m going to say no check constraints because I want that to be an option in my script. If I’m copying data in the tables and I setup foreign keys that are designed to point at other tables that don’t have any data, this is going to blow up. So I had to include an option to say no check constraints. I’ll show you how you can check for that as well. Then I’m saying verbose and then I’m saying warning action silently continue. Explain that in a minute as well.
Let’s go ahead and run this and see what happens. It’s going to connect. It’s going to script out all those tables. Hey, I found a schema-bound view. That might be important to put back when I’m done if I can’t drop a table that’s being referenced by one. Oh, hey, I found a bunch of foreign keys. Let’s go ahead and script those out too. Then I’m going to drop them so I can put a new copy of the table there. I found a whole bunch for this table as well. That’s why I love Adventureworks. It already has a bunch of foreign keys that make it really easy to test stuff like this.
Cool. Now I’m putting the table back and I’m copying all the data over. Pretty neat. Sales order detail is kind of a big table, this will take a few seconds. When I’m done, all this stuff is coming over to database2, and again, the only thing we had in here beforehand were just a bunch of empty objects. But, if I start to look at sales order detail I can see that I have data in here now and it pretty much matches production with the same sales order IDs and sales order detail IDs and all the things that came over from my other server. When it’s done, it puts back all the foreign keys. So if I look at sales order detail I should see all my foreign keys. Perfect. It did exactly what I wanted it to do.
Well, again, why is this important? Typically, if you wanted to copy tables between database servers or maybe this is something, I like to call it the manual replication or poor man’s replication, where you need to do a snapshot of certain tables or certain schemas and get them from one server to another. Again, using my MDM example as a good example of that. It’s pretty easy to set that up on an automated basis with existing tools. Maybe I can write a sys package that takes care of dropping a table, issues a create command for a table and writes all that stuff and imports all the data with a data pump. Hey, that’s great.
What can happen is is that if these tables grow where there’s a requirement to add new tables or there’s a requirement to change tables, you have to go in and you have to modify this sys package or this process every time you do something like that. With a solution like this, you could basically setup your own custom script like I did to automatically say, “Just give me everything in that schema.” If we have to remove tables, so be it. The script is going to take care of it. I don’t really need to modify anything because my script is assigned to automatically pick that stuff up and move them from place to place. That’s a big win.
That way, you don’t have to worry about okay, well, are they releasing a change to this database or this schema? Okay, well let’s get it on our developer roadmap. We need to modify this sys package. Let’s get it checked in. Let’s do this. Let’s do that. Whereas we can just issue a PowerShell command to say, “Copy these tables from server A to server B” database B, whatever. Tonight everything is going to work the same because I’ve deployed my change to the production server that has it. I issue a PowerShell command to basically automatically grab everything I need to move, copy it over, and you’re good to go.
Something like that is a huge timesaver in that it requires less coding on your part. It basically takes the same, I guess principle, that we say in the previous example. Now, this one is a little more well written out. The other one didn’t have this, but this section at the top here kind of talks about a little help file. This is exposable via PowerShell. So if you did get help on my script or my cmdlet, this kind of stuff would be exposed. PowerShell knows how to read this format if you will with this .parameter.description.synopsis. So you can get help on your cmdlets.
So just like in the previous example, we’re declaring some parameters, no big deal. Here we go again, I’m using the module to go out and get a series of tables from my source and my destination. Pretty cool, right? Just like we showed at the beginning. Then we declare this working directory. So you probably were wondering why did I have that parameter set working directory. The thing about PowerShell is that if you really want to fail at scale, it’s a great tool for that because if you start automating things and you start deploying things, if you have a bug and you have a series of automation processes set up to point these things from point A to point B and something goes wrong, something can go wrong really bad in a whole lot of places, sometimes at once.
Basically what I do with something like this is before I make any changes to a destination database, I like to have my script dump out the changes it’s going to try to make. That way if the connection dies halfway through or we encounter an error we weren’t anticipating, we have a way to get those things back that we dropped attempting to copy the tables. It’s just a little bit of a ripcord that I can pull to say here’s what it attempted to do, here’s where it failed. Oh crap, I better put these back before I try to run this again.
Then if you just run it again without thinking about what you did, the script is going to run. It’s going to say, “I don’t have any indexes there. I don’t need to script anything out.” Then your kind of lost and you have some explaining to do. I like to do things like that, that’s just a little helpful tip that I will say that as you start to get into automation, start to build yourself some ripcords because PowerShell is awesome but it can also be very unforgiving if you’re not sure what’s going on. That’s why I have that write to that directory so it can run these files through to show me what changes were made. Then I have a process that goes in and cleans these up after the fact.
Then we kind of go through each of our different things we need to script out, just like we saw in the previous example. I have a scripting object and I say .script and I pass it that variable. I look for things like permissions and foreign keys, etc. Then it saves to my file.
Now, I mentioned in the previous example that if you want to copy data between two servers what I like to do is I like to use bulk copy. There’s a bulk copy object that you can use. You can just like you would with any SQL bulk copy operation, you can pass it things like, hey, let’s keep an identity as we do an insert. So I’m declaring a new bulk copy option to my target. I’m basically passing it a schema and table name based on the current table name that I’m processing. Then I’m building a data table and passing it over.
So basically it iterates through the columns, it builds my bulk copy object. It knows what to copy. Then it issues a bulk copy command, built dynamically to say copy these columns with these rows from source A to source B. It just does that one table at a time, kind of like a really [inaudible – cursor] I guess you could say. It does all that. Then when it’s done, it puts back the foreign keys. It’s about 300 lines of code. It probably could be condensed even more because I wrote this at a time when I’m constantly improving. There’s ways to improve this code. Take it, run with it, use it, improve it. Have had it. I’m going to have that on my link here in a minute for you to look at.
Again, this is a good way to say I want to dynamically copy tables. I say dynamically because again, we passed it a schema name. It will automatically take care of the rest. I don’t need to give it a list of tables, it’s just going to grab them from the schema, copy from point A to point B. Again, solved a problem for me, it could probably solve a problem for you, or something similar.
Real-World Example: Managing Backups
Let’s talk about a really good one. We’ve see some maybe isolated examples, like, okay, Drew, that’s cool. That worked for you. But whatever, it’s not really blowing my hair back. Let’s talk about managing backups because I’m managing all of my backups today with PowerShell. The reason for that is that is that it gives me an easy way to centralize my backup process with one script that can run pretty much from any location and do a lot of things that I’m doing in T-SQL without having to rely on a SQL agent job necessarily to get it done.
Today, if you’re using SQL agent backups or you’re using a third-party product or a data domain or what have you to do your backups, all that stuff is great. I think everybody should do backups. I don’t think anybody on this call or anybody on this conference is going to say backups are dumb. I would hope not. I hope not.
What’s interesting about PowerShell is is that it’s pretty neat to backup SQL databases. Aaron wrote a really good blogpost recently on SQL Server Central about using backup SQL command to backup your databases. It’s a very elegant cmdlet that’s included with the provider. It works great. If you want to extend upon it, there’s ways to do that. I’m going to show you a couple examples.
The real beauty of using PowerShell isn’t necessarily in the backup, it’s in the restore. Because if you need to get back to a point in time really quickly, unless you have that stuff rehearsed and scripted out ahead of time, it can be a headache with probably VPs, directors, and in some cases CEOs standing over your shoulder wondering why aren’t our backups done yet, because I’m trying to find log files and put them in the right order and do all those different things. PowerShell can alleviate a lot of that stress as well.
The whole time I’ve been talking here, I’ve had another PowerShell window open. I have a little script run that has been backing up my Adventureworks database behind the scenes. At certain times it does a full backup, sometimes it does a log backup. Sometimes it does a differential. Let’s go ahead and kill that guy real quick.
Over on my computer I have a directory here, temp, and I’ve got backups. So the script I’m using basically can automatically take care of a uniform naming convention. It can take care of putting things out in directories so it’s easier to find. All that stuff. I’ll show you that in a minute. But what it’s done is I have a couple full backups in here with different times, looks like every 20 minutes it’s doing a full backup, now I remember. I’ve got differentials in here taking every five minutes and I’ve got logs every 30 seconds-ish, maybe every minute. I have a lot of backup sets to work with.
Now, what we can do then is let’s say we had a boo-boo or we had an issue and we want to get back to an exact point in time. Let’s go ahead and see if we can pick a good in time to go to here. I had a full backup taken at 9:55. I had a differential taken at 10:00. I’ve got logs up until 10:12. Let’s say that. So, what I can do is I can use a PowerShell script to take my database back to a point in time. Let’s go ahead and copy this guy over here because you’re not supposed to type in demos, I keep saying that. What I’m going to do is I’m going to make sure I don’t have any connections to the database open because that’s always something I run into when I do this.
Okay, so if everything works the way it should, we’re going to start a SQL restore. We’re going to say I want to restore Adventureworks 2014. I’m telling it that all of my full backups are located here, my differential backups are located here, my log backups are located here and I want to go back to a point in time. I want to go back to—what is today? The 13th, 2017. I want to go back to 10:15:17 a.m. So what is this going to do? I’m going to tack on this error action stop. You may have seen me use warning action and error action. These are really good emergency brakes for your scripts. One thing PowerShell is really good at is even though it has an error, it may still exit successfully if you have code that runs after your error.
What error action stop does say is, hey, if I hit an error, quit right there. Return error code 1 or return error code 3, or whatever the error code is going to be. Because if you omit that, it’s going to error and then it’s going to finish running and say, “I’m done!” It’s going to say, “I was successful even though I really wasn’t.” So error action stop, and that is a command that works pretty much everywhere by the way, whether it’s your script or someone else’s or they’re building a cmdlet, that will work if you have cmdlet binding setup. So it will automatically throw that emergency brake to kill whatever is running and return an error code.
Let’s go ahead and see what happens if I run this. Cool. Wow. I got a lot of verbose output. So while it’s running and doing the restore, which it’s probably going to fail because I have something to open to it right now. Always happens, “Database is use.” At any rate, it failed because it was in use. I’ll rerun it again here in a minute.
What it’s basically doing is going to those directories, it’s finding those files, and then it’s building your restore chain. So it’s looking at the fulls, it’s looking at the differentials, it’s finding ones that—it basically starts and it looks for files between that timeframe. It says, “I’m going to grab this full. I’m going to grab this log file.” Then when it finds all the log files it validates your log chain. Do my LSNs match in sequential order? Am I going to be able to restore in the order that I want? Pretty good so far, looks good. It would have worked too if it wasn’t for those meddling kids. There we go. Now it came back online. There, after killing that session because the database was in use, it works.
Basically, it’s saying, “I’m doing ten total restore operations.” I’m stopping my log restores at this point in time so then when it gets to that log file that meets the criteria, the LSN being within that point of time, it basically does that restore and then it brings the databases back online. I can tell you, having never been in that situation except for one time and I never want to be in that situation again, when the system is down because of database corruption or some issue where you need to restore your databases, obviously time is of the essence. We talk about things like RPO and RTO. This is a big step in that direction for making sure that your restore scripts are on point and have everything you need.
So being able to issue a PowerShell command to go get this information, bring it back, and build your restore job for you—to me, is a huge timesaver. This relies on not only PowerShell but it’s relying on things like the file system to look at file times and to dig into files and look at different things to pull back that require the file. For me, this is hopefully the lightbulb is starting to go and say, oh wow, this is actually way more powerful than I thought. If that one doesn’t do it, the next one really will. Let’s look at this code real quick.
Brent Ozar: Let me jump in and ask, Anthony asks, “Does this look at the backup header of the files or does it use info from msdb?”
Drew Furgiuele: It looks at backup headers from the file, because if msdb isn’t available, what are you going to do? So it’s going to look—it actually reads backup headers. Here’s the code. Let’s scroll down to the line in question. I wish I knew the line number off the top of my head, Anthony, I apologize. Here it is, “Verify header. Read backup header.” What it does is it reads the backup headers of the files and pulls back the information to know what the LSN information is. So it’s actually reading the backup headers.
Brent Ozar: He says, “Awesome sauce.”
Drew Furgiuele: Yeah. What this does is this is actually a lot of code. It could probably be condensed, so challenge to anybody. There’s functionality out there I’ll talk about later where people have done this better than me, but this was my attempt at it. What this does is this basically goes out, retrieves those series of files from your file system, tries to build a log chain, and then verifies that log chain. Then issues the restore command to the database. Now, I’m cheating a little bit here because I said I hate writing SQL in my PowerShell scripts, but this is how I got around a particularly thorny problem.
When you’re restoring log files, you want to make sure you leave your database and recovery versus no recovery. You want to make sure you’re able to restore additional log files. Well, the way I got around a particular thorny problem about the database not being online when I was done was to say if I do a log file restore, leave it with no recovery until I am done. Then issue this command to say with recovery to bring the database online. So it’s a bit of cheat. Again, there’s probably better ways to do this. That is what this does.
This is a timesaver, “oh crap” something is broke, I-need-to-fix-it solution to a problem. This was something I was using to restore databases to point in times before we got our flash array which let’s me do basically restores a hell of a lot faster. But this is something that’s in my back pocket to do a restore if I need it. It saves me a lot of time.
Again, the backup portion of this, right, which I’ll show you briefly here because I’m going to hit up on my time limit here pretty quick is it basically takes care of this path convention. So the backup script is nice because if I’m using the same script on all of my servers then I have consistency as to where it’s being saved. It will automatically create folders for the name of the server you’re backing up. It will automatically create folders for each of the databases. It will create paths for differential, fulls, and logs. That kind of stuff can be baked into the code as well to test for those paths, create those paths, write them, write to them. That way, I have a directory that is structured the same for all of my backups wherever they may be. That’s why I like using PowerShell to do my SQL Server backups as well.
Real-World Example: Auditing SQL Server Group Permissions
One more example before I’m done. If you’ve been kind of bored so far, I would say that if you’re new to PowerShell or you hate my code, here’s a good example of a really really good example, use case, for PowerShell and SQL Server. I don’t know if there’s any security auditors on the conference right now. I want to preface this by saying that I don’t hate you as a person but I do have a habit of getting phone calls from people at 4:00 on a Friday saying, “Hey, we need to know who has write access to our databases.”
What you could do is you could fire up a T-SQL query and you could say, hey, I ran this query against SQL Server. Here’s all the user principals or groups that have write access to the DBO schema of a particular database. Maybe that’s good enough. Maybe you can give those groups back to the auditors and say, “Here you go. Here’s a burden. Go investigate these groups and see who’s who.” Well wouldn’t it be nice if you could run a script that took the user credentials that are currently identified in your database, take them against Active Directory, go through all the groups and actually enumerate the users that have write access to your database.
This is an example of where we kind of step outside of just using SQL Server and PowerShell to bridging that gap to say, “I want to use SQL Server and PowerShell but I want to integrate it with Active Directory to solve a problem.” I talked about multi-server administration but now we’re talking about multi-domain administration. We’re using Active Directory and we’re using SQL Server to provide value. Again, that is a huge, huge benefit of PowerShell is that you can have cmdlets to interface with Exchange or Active Directory or SharePoint or all these different things and SQL Server.
So if you have these things that kind of cross paths together, you can use automation to find this information out. For this example, we’re going to pop over to a VM that’s running a domain. Over here, on this SQL Server, hopefully you guys can see that okay, I have a bunch of groups to find. I have DBAs, I have developers, I have a dev ops group, I have an HR, IT operations, QA testers. Probably not very different to what’s in most production systems today. So then I have a copy of Adventureworks and I have a copy of Adventureworks called dev.
Let’s say I want to do an audit. Let’s start with a cold read here. Let’s say I want to run an audit and figure out who’s who in my database. What we’re going to do is we are going to import the SQL Server module on this server. That’s going to run for a second. Then—hold your breath—there we go. Let’s go ahead and here. Okay, so I’ve got this thing called start SQL permission audits. We’re going to go ahead and say start SQL permission audit. I want to point it at local host because I’m lazy. If I wanted it to get to a particular instance name or database name, I have parameters set up for that. Let’s do verbose.
This time, we’re going to use a pipe. We’re going to pass the results of my script to another cmdlet called export CSV. We’re going to say see C:\temp\ audit.csv. No type information. Cool. Let’s try that. This is going to run and it’s going to look at all the databases on SQL Server A and it’s going to go through and it’s going to connect to my Active Directory domain which is another computer running in my VM farm that’s going to go and talk to that Active Directory server to figure out who’s who in these groups.
Brent Ozar: We had a question. Michael asks, “How do you like to schedule stuff like this to run reoccurring? Is there any tool that you like to use for that?”
Drew Furgiuele: What I do is I use a combination. I have certain things that run in SQL agent jobs, which it can be a little tricky to get PowerShell scripts to run correctly in SQL agent jobs. I would say that if you want to learn more about that, I’d be happy to share a beer with you and tell you why that has caused me a lot of sleepless nights. But there are ways to make it work. It turns out you have to do things like command.exe as opposed to PowerShell jobs because that makes sense.
I also, at IGS, we have an enterprise scheduler application that we use to basically run things, but really windows schedule task or SQL agent jobs, it really could be anything. The key is monitoring. You need to know when they fail. Whatever solution you go with, you just want to make sure that you know that things fail.
So that spit out a report called audit CSV. I’m going to cheat here just to save a little bit of time. I have on my desktop a permission audit. What this did—is this basically went through my database and it found the different users that are members of these groups and what kind of permissions they have to my databases. I didn’t just say, “Oh, gee, SQL developers have select.” I’m saying that Chad and Julie and John and Paul and Rick all have this level of access, and oh yeah, Kate Rhoad, she works in HR. She’s got insert, update, she’s got access to the HR schema, that makes sense. Then I can look at this report, either as a DBA or a security professional. I can say, “Wait a minute, Blake is a QA tester. What is he doing in operations?” This should cue you to say, “Oh my god, why do a QA tester have full blown production and access?”
So this kind of just puts faces to groups to say, hey, these are the people that have access to your databases. I went to AD. I iterated through these things and I pulled this information out and found information out about them. Do these people actually need it? Because, oh by the way, Blake shows back up here as a QA tester having select permissions to something else whereas John Krebs does as well. Then I can see here that I have members that are part of destructive groups in the dev group. But it’s dev and we don’t care about dev except when it breaks and they need it refreshed. So I have all these different groups that are members of the SQL developers and QA testers that have more destructive permissions.
This to me should be a eureka moment if you’ve ever wondered about why it’s important to learn PowerShell is that this is now we’re crossing not just in SQL Server, I’m using SQL Server because I’m pulling security stuff out of SQL Server, but now I’m actually taking that over to Active Directory to figure out who’s who.
Brent Ozar: Several people are jumping in and asking do they need domain admin permissions to do this kind of thing.
Drew Furgiuele: Well, no, but it depends on how your Active Directory domain is setup. I think one of the default settings means that anybody can query Active Directory but if you have Active Directory locked down to run something like that, you may need to work with your AD people to say, yes, I need the ability to run this query. Maybe they can set you an admin account to run it or something like that. But I think the default behavior in Active Directory is that it doesn’t but don’t totally quote me on that.
So how are we doing this? This is like I said, we’re mixing two PowerShell kind of modules together here. We’re using the SQL Server PowerShell module but we’re also using the Active Directory PowerShell module to get this done. So if you’ve been following along here, I’m not doing anything different than I’ve normally done. I’m getting a list of databases and I’m getting logins, all with the provider using Get-ChildItem on my SQL Server path. I’m using variable substitution here based on what I pass it in its parameters. Then for each database, I basically go in and I’m looking at every object.
There’s a method on the database objects that can say enumerate object permissions which returns you every single object permission in the database. Then, I’m basically creating a new custom object to hold these values and what type of login they are. I’m also looking for users that may have a database login that don’t necessarily have a server principal login as well, so I can look for orphaned users that may be getting permissions from somewhere else.
Then, I’m getting to the Active Directory commands down here. I’m doing things like get AD group member. So I’m basically passing it an AD group to say give me everybody in this group. Get AD group member has this awesome switch called recursive, which means if there’s groups within groups within groups, it will go to the lowest level and pull out every user for your report. Now in this example, I won’t show you but just take my word for it, is that if I went over to my domain controller in my VM farm and I pulled up this information, I would see that I had a QA testers group and part of the operations group.
Obviously, probably what happened there is a DBA like myself got lazy, they were trying to fix a bug in production and we said, “Okay, QA testers. Go test this functionality. We gave them access and then we never took it away.” So that’s the kind of stuff that an audit solution like this can get you. There’s a lot of good examples of code like this out on the web.
This is a what I consider to be the cold stone dead example of why I learning PowerShell is awesome for SQL Server. Because now you’re crossing domains. You can say, “Hey, I want to know who’s got write access to my databases and I don’t just want to know at the user principal level. I want to know it all, kind of at the Active Directory level.” Then if it’s a Windows user, it’s just basically get AD user instead. Then it basically creates a custom object to output all of this stuff.
So why did I do export CSV? Because if I had run this command, and again, this is more a style points thing as you’re writing. You never really want to control user output. Had I not done export CSV, all this would have done is basically spit out the same thing but an object level, because you never really want to pigeonhole the user in the one type of export. Maybe they don’t want export CSV. Maybe they just want to see a table or maybe they want to format it on their screens. You want to leave it up to the user how they want to get their output, same thing—for kind of the same reason I use verbose output as opposed to write host. See this is just writing out to my console the actual object but it’s returning the same info. You’re piping it over to export CSV to get it to that CSV file.
So those were real, four practical examples of things you can do in SQL Server and PowerShell. I know that I probably glanced over a lot of code and maybe people wanted to see additional things. I can tell you that again, all this code is out there. I’m happy to answer questions. There are people way smarter than me at this that can answer questions as well and probably tell you why I did it that way was wrong.
Just to wrap up real quick, look, I think as IT professionals, I’m not just speaking at the DBA-level domain here. I think it’s important that people take the time to learn PowerShell, even if it’s just a simple command like Get-ADUser or Get-ChildItem to find old files somewhere, old backup files that you know you want to get rid of, an easy way to filter on file data. I mean, that’s the kind of stuff you can do with PowerShell.
You can get to the more advanced stuff like using SQL Server and solving problems but you’d be surprised how fast you can get there. It’s very intuitive once you start using it. Honestly, if I was interviewing for any position I would make sure PowerShell was pretty much called out on my resume. It’s just a good solid skill to have in any Microsoft shop. Now with SQL Server going to Linux, we’re going to see more people elevating Bash and Python and all those other scripting languages that we know about, but PowerShell is actually really, really powerful, guys.
If nothing else today, I hope I’ve illustrated to you that this is something that’s worth taking the time to learn. There’s a lot of good resources to do it. So if you do want to learn more, there’s a lot of msdn resources out there for getting started. There’s references for the PowerShell cmdlets and there’s references for the SMO objects. I’ve got a lot of good people here that would be happy to help you if you don’t necessarily want to reach out to me. Hey, Scripting Guy! is kind of a blog where people can write questions or ask questions to the Microsoft scripting guys and basically publish articles and you see different examples of how to do things.
Then you got people like Aaron Nelson and Chrissy LeMaire, Mike Fal, all these guys and girls that are fantastic people to work with to get questions. You’ll probably run into one of them at a SQL Saturday either doing a precon or doing a presentation. If you want to learn more, carve out the time to see them. Or, if they’re doing a webinar, carve out the time to see them. They really know their stuff. They can help you learn this better than I probably can.
Aaron had mentioned at the beginning of the webcast a project called dbatools. This is a community collaborated project where we have people in the community who are passionate about PowerShell and SQL Server writing their own cmdlets and sharing them with people. We’re working towards a 1.0 release that you can go and get this module off the PowerShell module marketplace and use them and have it be tested and supported code. This is all really exciting stuff. I really urge you to check it out.
The other thing I want to point you to is the PASS PowerShell Virtual Chapter. Aaron runs this and just like all the other virtual chapters, this is something that meets once a month or sometimes twice a month. They do broadcasts, they save all their stuff to their YouTube channel. You can find out more at powershell.sqlpass.org. Just shameless plug—I’m giving a talk there next week on multi-threading in PowerShell. If you really want to melt your brain, come on over there. I can show you how to multi-thread some stuff and why that’s pretty cool.
I’m going to go ahead and stop it there. I know I have like maybe 10 or 15 minutes left and I want to open it up to questions and see what people think. I’ll leave my contact info up while I do it here if you guys want to reach out.
Q & A
Brent Ozar: Yeah, and folks, the link at the bottom there, groupby.org.go/session2, is where you can go and get to some additional resources. You can post questions as well and you can rate this session. If you want to drop Drew a line and rate a session there.
Before we go into the questions, I just want to thank Drew for presenting today. I know this is a hugely, highly voted up session in terms of both the PowerShell sessions were really popular. So thanks for taking time out of your day there.
James asks, “Is there a Slack channel for people working with PowerShell?”
Drew Furgiuele: There is. I think that there may be a Slack channel for PowerShell specifically but there is the SQL Server Slack channel, or the SQL Server Slack kind of main group. I don’t have it running on this laptop to show you so I apologize but it can be easy to find. I’m sure somebody could probably put it in chat and share it real quick. I don’t have it handy with me right now, I apologize.
We have dedicated people on the Slack channel. They are there to answer questions about PowerShell and SQL Server or PowerShell help in general. So definitely hop into the Slack channel and ask questions on the SQL Server Slack channel. I’m hoping somebody can maybe tweet it at me so I can share it real quick.
Brent Ozar: Yeah, if you go to dbatools.io, at dbatools.io scroll all the way down to the bottom and there’s a Slack icon, the pound sign. You can click there. You can get an invite from there so that you can join in immediately.
Drew Furgiuele: Yeah, there it is. Thanks, Brent.
Brent Ozar: Let’s see here. Next up, SQL Bob says, “Thanks to Drew for the PowerShell scripts, it’s going to save me so much time for our replication process.”
James asks, “Does Drew have any video training or any video classes?”
Drew Furgiuele: I don’t. This is only the second virtual presentation I’ve ever given. So I apologize for the technical difficulties with the lights, but no one wants to look at my anyway, so don’t worry about that. But I will go yell at the facilities guys when I’m done here. No, I really don’t, but I know this session is getting recorded next Wednesday, has that available. And I think Brent, you’ll make this one available as well but if people would love to see videos, tweet at me, I’ll see what I can do.
Brent Ozar: Tony also suggests powershell.slack.com for people who aren’t in the SQL Server community.
Drew Furgiuele: There you go.
Brent Ozar: Steven says, “If PowerShell is something that really interests you and you want to chat more and see more of what Drew is doing, go to the PowerShell classes on the Microsoft Virtual Academy. We’ll get you started as a primer.”
Drew Furgiuele: I would say that the Virtual Academy classes are great. There is also the YouTube series about learn PowerShell over a series of lunches. Those are great YouTube videos to get started as well. I should have probably linked that in the resources.
Brent Ozar: Ann has a great question. Say that you’re doing all the scripting in and out of objects and data, could you leverage data masking and object level security with stuff like this? Like could you mung the data as you pull it in and out?
Drew Furgiuele: That’s a tricky one. There are probably libraries that you would basically use in-between moving it from place to place to do that. But if you have things like SQL Server 2016 and you have dynamic data masking or something on the destination server, that probably will be handled automatically for you would be my answer. But if you’re using older versions of SQL Server 2012 or 2014, I’m sure that there are a lot of good .NET libraries or projects out there. I’m going to write that one down actually because that’s a really good question. That can take your data kind of inflate before it gets written to the destination and change it up.
Brent Ozar: J.D. asks if you posted the slides anywhere.
Drew Furgiuele: I can make the slides available on my website. Brent, I know you’re going to make the slides available as well.
Brent Ozar: Sure, I can. We can just upload them to SlideShare.
Drew Furgiuele: Okay, I will put them there.
Brent Ozar: Steven says—that question is kind of long so I’m going to skip that one. Camille says she’s working on a PowerShell script to automate report deployment. She’s stuck on mapping multiple data sources for the report. “Have you done any work with SSRS PowerShell or is there anyone who you know who has?”
Drew Furgiuele: I have, creating data connection objects can be a little tricky in SSRS and SSIS. I would say that if you have questions either shoot me an email and tell me what you’re doing, because we’re doing all of our deployments to production using PowerShell and SSRS. I would say if you have questions, I would happy to share the code that we’re using and maybe we can see where I can help, or if I can help. Maybe I’m not the—I’d be happy to look at your code. Either tweet at me or send me an email and I’d be happy to take a look.
Brent Ozar: Aaron, I bet you’ve got something to say there.
Aaron Nelson: Yeah, just recently back in November, the SSRS team actually launched their GitHub module for SSRS. I’m going to get you the URL to that. If you go to I believe sqlps.io/ssrs, that will take you right to it. If you go the SSRS team blog, they’ve got it there as well. Their stuff is a work in progress and probably needs a few more things, but it can deploy an entire folder of reports with a single command, so that’s a really helpful thing that they’ve got built into there.
Brent Ozar: sqlps.io/ssrs.
Aaron Nelson: Precisely.
Brent Ozar: Rushab says, “Do you have any general PowerShell scripts which run a query against multiple servers and then dump out the results in html?”
Drew Furgiuele: Okay, so you’re going to be dynamically building html. I guess it would depend on what you’re trying to do. Top of my head, no. I don’t have any scripts to do that, but it wouldn’t be that hard to make. You could use the provider to basically connect to a series of servers or a series of databases, pull back the properties you want in a variable, and then you could kludge together an html body and basically write stuff to the html, constantly appending to that string and then writing out the file when you were done. I know that’s not saying a lot to do a lot, but it’s really, that’s what it would be. It would be using the provider to go to SQL Server and get the stuff you want and then basically building your own html document or using a document template to write to. There’s a lot of resources on that online I’m sure.
Brent Ozar: Last thing we’ll say is Katie says that if you’re in the UK Rob Sewell—I’m not sure if I’m pronouncing his last name right—is known as the “DBA with a Beard.” He does a lot of PowerShell talks over in the UK and is a very friendly guy as well. Thanks again, Drew. I appreciate it.
Drew Furgiuele: See you everybody. Thanks.