Allen White
SQL Down Under Show 33 - Guest: Allen White - Published: 29 Feb 2008
In this show SQL Server MVP Allen White discusses automation of SQL Server using SMO and Powershell.
Details About Our Guest
Allen White is a database administrator for Advanstar Communications. A publisher of B2B trade magazines and promoter of trade shows. He has worked as a database administrator, architect, and developer for over 15 years, supporting both the Sybase and MicrosofTSQL Server platforms over that period. He worked with Microsoft as a subject matter expert on the certification exams and coursework for SQL Server 2005, all certifications: MCTS for SQL Server 2005, MCITP for database developer, and MCITP for database administrator, and is also a Microsoft-certified trainer. Allen was awarded Microsoft’s most valuable professional MVP award for SQL Server in July 2007. His blog site is
Show Notes And Links
Show Transcript
Greg Low: Introducing Show 33 with guest Allen White.
Our guest today is Allen White. Allen is a database administrator for Advanstar Communications. A publisher of B2B trade magazines and promoter of trade shows. He has worked as a database administrator, architect, and developer for over 15 years, supporting both the Sybase and MicrosofTSQL Server platforms over that period. He worked with Microsoft as a subject matter expert on the certification exams and coursework for SQL Server 2005, all certifications: MCTS for SQL Server 2005, MCITP for database developer, and MCITP for database administrator, and is also a Microsoft-certified trainer. Allen was awarded Microsoft’s most valuable professional MVP award for SQL Server in July 2007. His blog site is Welcome Allen!
Allen White: Thank you!
Greg Low: First thing, describe how you came to be involved with SQL Server. Before the show, I was describing the format to him, and he said he knows the format well.
Allen White: I’m a runner, and I use your shows as a companion during a long run.
Greg Low: Great to hear. That’s good. How did you come to be involved with SQL Server?
Allen White: One of my specialties was retail point of sale, and a company that was putting together a point of sale audit product wanted me to get involved in it and they were going to do this with a relational database. Looked at Oracle but they were too expensive. Informix was the big player in the retail market at the time, but Informix didn’t have the time to call them back. Sybase was really hungry for business so they got in bed with Sybase. I started building the application using Sybase. After a few years, MicrosofTSQL Server 6 came out, and that really felt like the database platform that businesses could rely on. So they said, “Could you rewrite all the code to work with MicrosofTSQL Server?” It was the same code base as Sybase so it was a piece of cake.
Greg Low: That’s good. I suppose that lends itself well into the topics we’re going to talk about today. One of the things that I used to find with SQL Server versions prior to 7 was that I thought they required way too much manual administration. I think the improvement was in reducing the hands-on work required from 7 on. People supporting more and more systems, so the topic we’re covering is how we’re automating or managing those things. We’re talking about SMO and PowerShell. Maybe if we start with SMO for those who have not worked with it, just a description of what we can do.
Allen White: Sure, and the whole thing that came about as I found myself having to do more, dealing with more servers, etc. I’m a lazy guy, so I want to find the fastest, easiest way to get stuff done. At one of the past conferences the professional association for SQL Server, Gert Drapers was talking about DMO, Data Management Objects. This was a revelation for me. I could start scripting some of the things that I do manually all the time.
Greg Low: Should mention that’s a forerunner of SMO and other MO products.
Allen White: All working on SQL Server 2000 at the time, so DMO was a way to script things there. In late 2004, I started looking at SMO as a new space, they were bringing ouTSQL Server 2005 and they were replacing DMO with SMO. I thought I’d check them out. Struggled for about a year, that last year before they brought ouTSQL Server 2005, just figuring out how the objects worked. Got to know the Visual Basic object browser well, they weren’t documented at the time very well.
Greg Low: People that have not used that, the thing to mention about the object browser is that you can point it at a library, and it pulls out all the properties and methods and everything. If you don’t have documentation, you can find your way around inside it.
Allen White: So we get into SMO itself, which is just a library of objects that reflects the objects in SQL Server, starting at the server level. If you can connect to the server, you’ve now got a server object. Within that you have a collection of databases, that’s called the database collection, and within that collection is an object for each database. There is an information collection that I use a lot. For example, things like, where your backup directory is. Your default data directory for your data files and log directory for your log files. Those are all in the information object collection in the server object. Browsing these pieces of information, you can discover where files are and where to put files when you need to write files.
Greg Low: Important to mention that some of those things are available from server property functions in TSQL. Some of those things are not available easily. SQL Server does it internally, often does registry recalls and so on.
Allen White: Exactly, the backup directory you cannot go through the Management Studio; you have to go through the registry to set the backup directory. Something that can be readily pulled up with SMO.
Greg Low: The other thing worth mentioning is that it gives you a level of version independence.
Allen White: With SMO, you can support servers that are running SQL 2000, 2005, and 2008. The features that each of those have are different. Times when you have to check to make sure what version of the server you’re on to do a particular function. For example, if I’m connecting to SQL 2000 Server, and I want to change my data types for Varchar (1000) and greater to Varchar(max). Won’t work on a SQL 2000 box, but will work fine on SQL 2005 and 2008.
Greg Low: So even though the functions of the error or methods of error in SMO, only apply to the versions that support those underlying functions. Same question comes up with SQL Server 2005 Management Studio to connect to box. Limited to what a SQL Server 2000 box can do.
Allen White: What I remember, most of SQL Server 2000 is written using SMO. My goal was to use the same tool as Microsoft to talk to the database.
Greg Low: One of the things I talked about with Management Studio was the fact that it used the standard interfaces to talk to the database. Anything it did, you could do easily anyway. It’s a very rich set of libraries. SMO is also a very clean structure. You mentioned servers having databases, databases having columns, and so on.
Allen White: Tables per column.
Greg Low: Completely in contrast to other Microsoft object models, like the Office ones. Word, for example. How could anyone code that without a manual?
Allen White: I have not looked at those, so…
Greg Low: They’re amazingly complicated. Without an Office programming book, there is no way to work out what to do without Googling endlessly. I think that’s what I’m getting at. With SMO, if you sit and imagine what an object model would look like, it’s pretty much what you would guess.
Allen White: True. There are some oddities when it comes to getting into finding the TCP port that your server is using. Can be weird, but for the most part, it’s just a top down structure that makes perfect sense.
Greg Low: With the version independence thing, people ask, can I just do this stuff in TSQL anyways?
Allen White: Yes! Actually, one of the SQL Server MVPs, Lindsay Shay is always responding to my PowerShell posts in my blog, saying, “Well I can do this in Visual Studio or in TSQL, why do we not do it there?” Different people, different needs. Been doing presentations for couple years on SMO patch. I’ll be doing my presentation at SQL Connections in April in Orlando. Always get resistance from crowd when talking about Visual Basic which is what I do my SMO examples in, because a lot of administrators are not allowed to have a Visual Studio environment on their desktop. When I talk about PowerShell, they get interested; they’re allowed to play in shell scripting environment. Two different ways of getting the same information.
Greg Low: One of the things we need to define is what SMO actually is. What is it?
Allen White: It’s an object library that allows a developer, a scripter, or whatever, to write code to manage SQL Server, a management tool.
Greg Low: It’s also a managed library.
Allen White: Yes. Absolutely. Actually, I get some other resistance from, with DMO, VB script and DMO kind of went together. You could use all of DMO using VB script. Then SMO came along and you could kind of do it, but could not specify a server. So you could do everything in SMO using VB script as long as you were connecting to your local default instance. Any other server and it would all fall apart. The framework SMO fully supports, so you have all objects available to you in the framework. You might understand that better than I do. My development days are way behind me.
Greg Low: One of the issues that came up with DMO was deployment. With SMO, it seems to be a much sweeter story.
Allen White: When you install SQL Server, it installs the framework 2.0. That’s what SMO requires. Everything is happy because the pieces that SMO requires are already there by virtue of already having installed SQL Server.
Greg Low: If I build a program that talks SMO and talks to SMO to do its database like DDL work, what do I need to deploy with the program? Just assemblies or a separate installer?
Allen White: To be honest, the code that I write I run myself. When I deploy an executable, I’m putting an executable module on each of my servers where I’ve already got SQL Server installed and the installation of SQL Server puts all the necessary libraries there.
Allen White: When you send out the code to somebody else, I don’t have experience in that, I’m an internal DBA at this point.
Greg Low: My guess on that is when you put the SQL native access client in place, you may or may not have to include SMO assemblies in the project when you deploy it. Either way, it’s a simple thing to do by comparison with DMO. If you had a DMO based application, deploying was really quite messy.
Allen White: Just caught on to the tail end of DMO. I discovered it 2003 at the PASS Conference. I thought it was cool, and wondered why I didn’t know before.
Greg Low: Another thing that we should mentions while in definitions, is the rest of the alphabet soup, like RMO , NMO, AMO, things like that.
Allen White: Things like Notification Management Objects, which I think are going away. There’s the replication, and again, the position I’m in, I haven’t done any replication, so I haven’t used RMO. AMO is for analysis services.
Greg Low: Whenever someone sees something-MO, as a library, it’s one of the pieces of what used to be DMO, but it didn’t used to cover so many things.
Allen White: It’s growing by leaps and bounds. They keep adding these MO’s to allow you to do the things you need to do.
Greg Low: As you say, NMO, they all disappear with notification services. I have done some coding with RMO, just when it first came out. I was trying to do some heterogeneous publishing work and it was really interesting to see all the objects. Clearly been set up for all sorts of different publishers. Support Oracle Publishing, but I was trying to backdoor a way of doing DB2 publishing at the time, kind of interesting where everything was there, but wouldn’t quite work. Ended up getting a product group guy, that doesn’t quite work, he said. But I got the feeling that the model they’ve picked actually allows for all sorts of things that haven’t been implemented.
Allen White: Just today I was talking with our data warehouse guys and they were really interested in the fact that in 2008 it looks like SSIS is going to support SBase, because we’ve got a huge SBase installation in our shop, SBase into SQL Server has been painful for them.
Greg Low: How did they do that before? Is there a provider they were using?
Allen White: Yes, they were.
Greg Low: I think there was always a big challenge is usually the difference between data types. One thing I love about the new data, is the range apart from the name, which I hate.
Allen White: You’re not alone there.
Greg Low: Really like the expanded range of years, had things like data coming from Oracle, with the year 2000, that was an error, meant to be 2000, causes you pain and grief when bringing proper dates in. As soon as you start going across different systems and different data types on different systems, always in for entertainment.
Allen White: Yeah, ETL is kind of a world of its own. Kind of played in that space for a while, pre-relational databases, enjoyed it. Amazing seeing the tools that are available now.
Greg Low: Excellent stuff. Had a show a while ago with Matthew Roach on that.
Allen White: Did a ten mile run listening to that show.
Greg Low: Tell me the sort of things that you use SMO for, at present?
Allen White: Usually it’s the maintenance plans, the minor backups. I wrote some articles on the Simple Talk website last year, a series of articles on my maintenance plans, because I really didn’t like the SQL 2005 maintenance plans when they came out, and I knew I could do simpler. And so I wrote the code that evaluates the databases on the server and builds jobs to back those databases up every night. And because I’m in a volatile shop, sometimes databases appear out of nowhere in the middle of the day, that night the job gets recreated with that database being part of the backup schedule. I also build steps in there to notify me by DB mail in the case of SQL 2005 if the job fails. It also builds jobs to do transactions on backups every whatever period of time I specify, whatever interval of time. Most of my servers I do transactions on backups once an hour, a couple of them are a little more volatile and I back up transactions every 15 minutes. I’ve got a parameter for when I run this program to generate the intervals for the schedule for the job based on the interval that I want.
Greg Low: I can imagine some people sitting listening, and thinking, can’t I just do that in SSIS? Build a whole program in a workflow to do the same thing?
Allen White: Yes, you can, but maybe it was just because I was impatient or didn’t take time to learn the SSIS, but what I had difficulty in doing was getting those maintenance plans – I had like 25 servers, and I didn’t want to go and build the package for each individual server and I didn’t see a quick and easy way to move the package from one server to another. Now I know that there probably is, but I didn’t see it. So I wrote this code, and I plugged these programs on every new server. Run them once and the jobs are all created.
Greg Low: Yeah actually, configuration files are one of the solutions in SSIS for doing that sort of thing. But to me I think one of the beauties of it is that SMO allows you to write something that works across versions. That’s the other thing I was sort of getting at – that on a server it’s much easier to write a statement that says “For each database, do this,” rather than thinking “Ok, now in SQL Server 2000, how do I get a list of databases, and in 2005 if it’s one of those servers, how do I get a list of databases, and then building a queue – so, it’s actually much more complicated than something that simply allows you to say, “For each database on the server, do this.”
Allen White: Exactly. And if you go to the Simple Talk website you can actually look through my code and see how I built that. That’s all Visual Basic code. But it was pretty straightforward, in fact, a buddy of mine left Advanstar and went to work on a big project at the Stanford Medical Center and somehow he fell into being the DBA role and so he sends me a message saying “Hey, how do I get regular backups going?” So I just pointed him to the links in the articles, and he’s like, “That’s exactly what I needed!” Started him off, and now my code’s running at Stanford!
Greg Low: Excellent. One of the questions I have: You mentioned in passing you didn’t overly like the standard maintenance plan? Have you got any feedback as to what sort of things you didn’t like?
Allen White: It just seemed really tedious to set them up. I didn’t really pursue it much further. And this was SQL 2005 RTM.
Greg Low: Yes, not SP2.
Allen White: Yes, that was really ugly. They fixed it. Was it SP1 that they fixed it? I stopped paying attention when I decided not to use them.
Greg Low: At the service packs they hadn’t finished the maintenance plans.
Allen White: Yeah, and like I said, that’s been off my radar because I don’t even use them. All my servers, regardless of what version of SQL server they’re running, use these programs that I wrote using SMO.
Greg Low: That’s good. Anything else, do you actually use it for things like creation of users, or anything like that?
Allen White: I don’t use it for creation of users, because we don’t really have a lot of that. The biggest place in our shop where we’re adding and removing users is in our CRM application, and that’s handled within the application itself, it’s not at the SQL Server level. So I don’t have a huge problem with user management. One of the things that I like to do, though, is build a database properly, if you will. By that I mean, when you say create a database from a Management Studio dialogue, it’s going to create an MDB, which is your primary file group, with all of your system tables, and then a log file. I’ve learned that that isn’t always the best structure.
Greg Low: Indeed.
Allen White: So I have a script, and actually it’s a PowerShell script but it’s using SMO that I can run when I need to create a new database. I just create a 5MB primary partition, and I call it the system data file group, and then whatever size I pass in the parameter to this thing for what I’ll call the application data partition, it creates an MDB, and that will become the default file group for any objects that get added to that database.
Greg Low: Funny story, as well. When we’re talking about MDB, and of course the NDF and LDF, one of the questions I always have come up in classes, people say “What’s NDF stand for?” I was reliably told by one of the internals that it was Nuther Data File.
Allen White: That certainly wouldn’t surprise me at all. I think I’ve heard that same thing. But it’s actually easier using SMO to do this than it is to go through the dialogue, through Management Studio to do it. The other thing is that you could create a TSQL script to do this fairly easily, but I’ve gone into the server object and pulled out of the information collection the master database path and the master log path. So I don’t have to change it for each server.
Greg Low: That’s right, the real power starts to come in where you don’t have to modify your TSQL script to have it dynamically adjust itself, or you don’t have to write some amazingly convoluted dynamic SQL statements where you’re generating the DDL before you execute it. You can do it much cleaner. Until people get used to the whole idea of scripting, it’s surprising how resistant people often are to it. I found the same thing when I used to do scripting with Windows server administrators and so on. I’ve seen people who will sit there and migrate everybody in one group across to another group or things like that, and they will do a thousand users, and they will just sit there doing them one after the other, and if you show them you could write a script to do that in a few minutes. I don’t know, there are some people who will just sit there, and think, “Well, I don’t need to know any of that stuff because I can do it manually in the tools,” but it can make a big difference.
Allen White: You just don’t have the time in the day to do that.
Greg Low: No doubt there is a bit of effort involved, but the payback is enormous. I have a friend, Peter Dean, I used to work with years ago, and I always remember him telling this story. It’s a bit like a guy standing there having a battle with a sword and there’s a tank salesman at the door but he doesn’t have time to talk to him. He’s too busy fighting the war with his sword. I think it can be exactly that. I think scripting is a super important thing to get your head around and to be able to become a very much more efficient administrator on any system.
Allen White: Right.
Greg Low: Is there a life outside SQL server?
Allen White: Yes, there is, actually. I have a wife. We’ve been married for a little over 27 years now, and I have a daughter who will be 24 in May. She just got married last month. Her husband is in the army, stationed over in Germany right now. She’ll be leaving in the next few weeks to join him over there.
Greg Low: Big trip.
Allen White: Yeah. Let’s see, I’m finishing up my bachelor’s degree. I never quite got there, so as I told the people in class last semester, “I’m here on the 36-year plan.”
Greg Low: I’m really impressed with that. Tell me why you decided to go back into it, because I hear so many people in the industry would not able to see any relevance in doing degrees at all. I’m just interested in your perspective.
Allen White: In 2002, I lost a job that I had. It was a that wasn’t doing very well, and it took ten months for me to get a new job, and there were a number of companies that expressed interest, but because I did not have a bachelor’s degree they couldn’t talk to me. Their own rules. I got the job with Advanstar, and I’ll have been with Advanstar five years this May and it’s been a great place to work. My boss has been absolutely marvelous. A couple of years ago we split into two companies. We decided that we were too diverse, if you will, in our focus, so we split into two companies, and the company that split away – when they started up, they did not have a SQL Server DVA. And I looked at that and I saw that if we split again, why would they need me? I could be out of a job through no fault of my own. So I need to patch this hole. So I’ll have been going three years, but I’ll be graduating on May 11. So I’m really excited about this.
Greg Low: So have you enjoyed doing it?
Allen White: I have.
Greg Low: So it’s not just like a chore.
Allen White: No, it’s not like a chore. It’s been really fascinating sitting in class with some people who have been out in the workforce for a while, but a lot of kids who are just out of high school, and seeing the difference in how I look at things versus how they look at things. They’re my daughter’s age, so that’s kind of interesting.
Greg Low: One of the things I used to find when I was at the university was the evening classes tended to be very different to the daytime classes, and I found exactly that. The daytime classes were mostly full of people just starting a career, and often the evening classes had a high percentage of people that were going back to do study. I found the dynamics of the two completely different. The people in the evening had a complete understanding of why they were there and they were task-oriented. I found I never really enjoyed doing first-year classes. I think they give that to new lecturers to sort you out.
Allen White: That’s probably true.
Greg Low: Particularly, I remember one of the classes, which was 400 first-year engineers. That was an absolute sink-or-swim thing. But it certainly helped you learn to control a room, put it that way.
Allen White: Presiding at a conference is nothing compared to that.
Greg Low: Yeah, that’s right. The thing I found with it was that a lot of them found it hard to make the migration from a school to a university and they almost have an attitude when they hand in an assignment or something like they think they’re somehow doing you a favor. It’s really hard for them to come to grips with the concept that if you’re working at a university, whether they pass, whether they fail, I’ll still be there, you won’t. I’ll help you by all means, but in the end, whether you do your assignment or not, that’s your issue. You’re not actually doing me a favor.
Allen White: And the other thing is running. It’s probably my biggest hobby. I’m actually working on a goal of running a marathon in all 50 states. I’ve got 22 done so far. It’s kind of fun because it gets me to places I never would have done. Last year I ran one in a little town called Olathe, Kansas, a little bit west of Kansas City. The night before the race I’m sitting in the hotel room, and it’s thunder storming, and I’m looking at the weather reports and they’re saying there’s a chance of tornado, and it’s like, “Oh yes, I’m in Kansas.”
Greg Low: That’s right, yes, Toto. Yes indeed.
Allen White: But I got through the marathon, got that pin on the map.
Greg Low: Well, we need to talk about PowerShell!
Allen White: Yes, we do.
Greg Low: For those, particularly the DVA’s listening, some will, some won’t have seen, so might you give a basic explanation of what PowerShell is?
Allen White: Okay just a little bit of background: I did spend time working with Sybase on UNIX platforms, and UNIX shells are very powerful. A UNIX administrator knows how to use the shell language and write shell scripts to their advantage. One of the things we did was we had shell scripts that would go through the Sybase log files looking for errors and report to us by email if there were any errors in the log files. We didn’t have to go looking through the log files, the shell script would do that for us. PowerShell is probably the closest equivalent on the Windows platforms that Microsoft has come out with to match the power that exists in the UNIX shell scripting environment. There’s a key difference that I really like. In a UNIX shell script, you can pipe the output of one command into another command and put together a string of piped commands that allows you to do the things like I just mentioned as far as searching through you log files and give you back meaningful results. UNIX shells scripts do that with text. They’re sending text from one program to another. PowerShell has that same type of capability from one object to another. But PowerShell returns objects.
Greg Low: Yeah, I think that’s the key differentiator. When I look at UNIX-type shells, as you mentioned, the typical approach is that they write a handful of little tools, each of which do very little. And they read from standard input right to standard output, and you can then hook them all up together to achieve something, but what they’re passing around is a list or text. Basically text. The big difference with PowerShell is that we’re saying I can pass you a list of customs or a list of databases and the thing that I get at the other end is a database.
Allen White: Right, right. And I love the idea of not having to use a crazy tool like Auk to parse through a glob of text to get the information I want because I know I have the object and I can just ask for the column that I want.
Greg Low: Exactly right, the power to literally pipe objects between things is the key benefit I see with PowerShell.
Allen White: Now Microsoft has made this part of what they call their common engineering criteria for the future. So, for example, Exchange 2007. Wonderful tool, very powerful. There are things that you need to do as an administrator that you cannot use the Exchange 2007 GUI tool to do. You have to use PowerShell to manage these pieces of Exchange. They’re only available through the PowerShell interface.
Greg Low: There’s a great interview with Kirk Munroe, I don’t know if you’ve heard it on rock over the past few weeks, where he’s talking about PowerShell, and he’s mentioning exactly that with Exchange. He was talking about the size of the library they’ve provided for it, but also describing the number of things that you can do with the shell scripting that there is no GUI equivalent to it at all. And in many cases it’s because creating a GUI equivalent would just be too clumsy.
Allen White: Well, just looking at the dialogues on the new SQL Server 2008 dialogues – some of these dialogues, there’s so much information on them you have a hard time fitting it on the screen. Well if you go at it through SMO and PowerShell, you don’t have to worry about that. There’s no screen limitation.
Greg Low: And you’re only worried about the properties of the object that you’re worried about.
Allen White: Correct.
Greg Low: Whereas on a GUI thing you’re endlessly hiding or exposing an enormous number of properties and making the screen look confusing in case somebody wants to set that one option, or whatever the thing is. I think that’s a significant difference. And there was an interesting comment in that same show where they said they’re interested in the development of UI technology and the next big thing in UI was actually the command prompt.
Allen White: Isn’t that funny! I first learned program basic language back in 1968 and we had a teletype machine. And when we were finished with the program it would respond “Ready for work.”
Greg Low: A bit friendlier than some of the old mainframe systems. I remember X8, the OS that Fujitsu had. I always used it as an example of things that were unfriendly. It used to come up with something like XCN10067J, no interpret table. I’d think, “Right.” XCN10067P, application unknown to VTemp. Okay. That’s good. Things have come a long way since that. It is interesting that there are different providers and people think, “OK, apart from straight scripting.” There was a session the other night that was showing some of the SQL 2008 provider stuff for PowerShell and it looked pretty smooth. For example, I could literally wander through the structure of SQL Server like it was a disk drive.
Allen White: Exactly, I was going to get to that. The first thing I was going to talk about, though, were the PowerShell cmdlets, because this is how PowerShell works. It uses these command line utilities called cmdlets that have a verb-noun naming convention. If you wanted to see a list of the processes on your server, you would say, get process. If you wanted to stop a service, you would say stop service. There is a cmdlet called get PSDrive for getting PowerShell drive. If you type that command in it will give you a list of the drives that are available and you’ll see things like C:, D:, whatever physical hard drives you have on your system, but you’ll also see something called HKCU for H Key Current User, HKLM for H Key Local Machine. You can navigate into the registry as though it were a drive. And when you install SQL Server 2008, and go in through the PowerShell interface that you can launch through the new Management Studio, it actually creates three of those for you: one for policies, one for SQL Server itself and I forget what the third one is. I don’t have 2008 up and running right now so I can’t tell you off the top of my head, but you get three of those PowerShell drives that you can navigate like any other file structure. One note: it is case-sensitive. I filed a bug on that, so I hope they fix that so you don’t have to worry about the case of the object. But it’s a case where if you understand the SMO object structure, you’re much farther ahead in being able to navigate that drive because it follows the SMO structure.
Greg Low: So what they’ve done is expose the SMO structure, as a drive.
Allen White: Yeah, it’s really kind of neat. I’m trying to figure out a really good application for it so I can blog about it. I haven’t found it yet. Let me know if you find it!
Greg Low: In fact, I’ve seen people showing it, and they go, “Isn’t this neat!” and everybody’s eyes light up, and they know it’s neat, but they’re not sure why.
Allen White: Exactly, and I’m going to get there. I’m bound and determined by the time SQL Server 2008 goes RTM, that I will have a good use for that. In the meantime, I’m going to keep playing with it.
Greg Low: One of the things that you said is that PowerShell is something that DVA’s are likely to be able to have available on the system where Visual Studio isn’t.
Allen White: Yes, it’s one of those things when I’m doing presentations and I do examples in Visual Basic. A lot of the DVA’s are like, “Well, I’m not allowed to have that on my workstation. I’m an administrator, not a developer.” Well, PowerShell is an administrative tool. I think every IT management structure would understand that it’s an administrative tool. There won’t be any resistance to getting PowerShell on your workstation to be able to do your work. I’m actually installing it on my SQL Servers now because I’m doing more and more power scripts on my servers because it’s so convenient. I write the script and then I run it from an agent job.
Greg Low: Yeah. So, if you go to execute one of these PowerShell scripts from an agent job, what are you doing to do that?
Allen White: You actually use a command line job step type. So you actually go into the Windows command line and say, PowerShell@ and then the name of your script. And then PowerShell fires up and executes your script, and then goes away.
Greg Low: Now, the other sort of things I’m imagining people would be wondering about: Have you got any feeling for how resource-intensive it is or how much memory this chews up, or any of those things?
Allen White: I have not looked at that. The only time I’ve run into a memory situation was - I’m using PowerShell for more than administrative functionality. We’ve got a large database of email addresses for our subscribers, because we’re a publishing company. And the subscribers are separated into different lists based on which magazine they subscribe to. And he needed a list of every subscriber and their email address, but he needed it in a separate file for each publication. That was fairly easy to do from PowerShell, just exporting these things from a text file, and every time the publication name changed, start a new file with that publication name in it. When I first wrote the code, I used an ADL object called a data table, and loaded the results set to the data table, and then parsed through the data table and ran it that way. Well, of course, the data table pulled the entire results set in memory, and when you’ve got millions and millions of rows, that becomes a problem.
Greg Low: Yeah, in fact that’s exactly what I was coming to, is that the fact that you are parsing around objects, the thing that you have to keep in mind is that they are objects. If you get something like a data table, which is just an in-memory representation of a whole bunch of objects, you still have the same problem. If you push millions and millions of objects into that, they’re going to take up a lot of space.
Allen White: Right. By changing the code so that I use an execute reader so it’s a forward-only operation, there’s no memory space chewed up by it. So it was fairly clean, then.
Greg Low: Yeah. Do you know when you pipe between things in PowerShell, do the commands that are involved in the piping execute concurrently. Do orders have to wait for the previous one to finish?
Allen White: To be honest with you, I have not looked at the execution of it underneath the hood.
Greg Low: What happened with UNIX scripting, there was always a big difference between commands that you could start at the beginning of the pipe and they would start sending information as they had it available, and then the next thing in the pipe could already be consuming it because they ran at the same time. For example, you had a command that did a sort, and it had to have the whole lot because it could then pass it on, so you do have a difference between the different types of things that you might have in the path as so whether or not they caused a big memory consumption or not.
Allen White: Yeah, on that particular example I haven’t done any memory or size-intensive work. I like I said, most of the stuff I’m doing is administrative-type things. So, for example, under SMO there’s a WMI-managed computer object. I connect to that and then I iterate through the server instances finding out the IP address and the port number of those. Well, there’s not a lot of those on a particular server, so it goes pretty quickly.
Greg Low: So I supposed that’s the key tip, is that you have to be really careful, still, if you are dealing with things that are large numbers of object, you can have issues with the amount of memory you’re dealing with.
Allen White: And that’s pretty much true with everything. How many development projects have you seen where everything works fine when they’re working with a set of 20 but when you throw 100 million rows at it, it completely falls apart.
Greg Low: Oh, indeed. The sessions you’re doing at TechEd this year – some of this was PowerShell-related? Actually, two of the sessions are specifically PowerShell. One is just PowerShell and SQL Server. Some of the examples I just talked about I’ll be going through the code and that sort of thing. The other one is doing administrative tasks with PowerShell. Things like doing backups and restores, setting up DV mail, creating an agent job, configure servers, WMI. PowerShell has a really great WMI provider built into it.
Greg Low: I should get you to spell out
Allen White: Oh yeah, the Windows Management Interface?
Greg Low: Or instrumentation, yeah.
Allen White: So, just about every piece of information you want to know about your server, you can get through WMI. It’s extremely powerful. SMO has a subset of that where you can use the SMO WMI-managed computer object and only look at the WMI objects that are specific to SQL Server, so you can look at the processes that are SQL Server processes – analyses and reporting services, and of course the SQL Server service. Those will be the only ones that come back if you’re doing it through SMO, whereas if you do a get WMI process from PowerShell directly you’ll see everything.
Greg Low: I remember there used to be a tool I was running that would show me all the contents of the WMI providers, I think it was called Scriptomatic or something like that. Just a freeware thing that allowed you to drill through the machine and have a look at all the WMI providers and what is in there. It really was quite an eye-opener to see how much was exposed for a WMI.
Allen White: I actually used the WMI code to pull back the amount of disk space I have available on my drives and I stored that in a database table so I can track disk usage over time. If a particular database is going crazy, I can say, “Ok, it’s chewing up 8 million rows every month. So I keep adding eight million rows and I can see at some point, I’m going to run out of disk space and I can predict that based on that tracked growth.”
Greg Low: And that’s good. So, a couple of sessions, I take it?
Allen White: Yeah, the SQL server and PowerShell is one. The other one is the administrative tasks with PowerShell. Then I’ve got a breakout session that Peter Ward and I are going to co-host. Just management tasks using SMO to manage SQL Server. We’re going to cover going SQL 2005, and SQL 2008.
Greg Low: In fact, I’ve had Peter do a number of sessions at a local code camp and things. Peter is based in Brisbane, where I used to be when I used to run a user group up there, but now he’s running it, and doing a great job.
Allen White: Yeah, I’ll be seeing him at a baseball game in Seattle in April.
Greg Low: I’ve got a heavy travel schedule coming up. Going to be in Seattle in mid-March for jumpstart stuff. Heading down to Houston are for a while make contact with folks down there, in case we do session, then Washington, then London, doing a session for Tony Robertson’s group on the eight of April. Heading from there to Copenhagen, down to Madrid for a few days, across to Vienna for a while, then back to London, back to Tokyo, and back home. If you’re in any of those places, pop me an email. Otherwise when I get back, I have a week or two, then I’ll be going back to Orlando, are you going there?
Allen White: Actually I will in Orlando twice, going the week after MVP Summit, the people connections conference, the week of April 20-23. My three sessions are the second week, the ITPro week, hoping to get down for other week, but not sure.
Greg Low: Has been hard for SQL Server people to decide which of the weeks to go to. Lots of good sessions both weeks. But anyway, we’ll look forward to seeing you in June. Thank you for your time today!
Allen White: Great, thank you.
Phone: 1300 SQL SQL (1300 775 775) l International +61 1300 775 775 l Fax: +61 3 8676-4913
Copyright 2017 by SQL Down Under | Terms Of Use | Privacy Statement