Search results matching tag 'SMO'

New Article on the SQL Server 2012 Backup and Restore PowerShell Cmdlets

AllenMWhite — Mon, 13 May 2013 13:08:00 GMT

While I was on vacation last week in Scotland Simple Talk published a new article I wrote called Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets.

Hope you have as much fun with it as I did writing it.

Allen

Excited to be speaking at #SQLSATATL next weekend

dbaduck — Sun, 12 May 2013 05:06:00 GMT

I will be on for speaking next week at SQL Saturday #220 #SQLSATATL.

My sessions will be on SQL Server TDE and SMO Internals for High Performance PowerShell. Both are great fun to present and good information to have.

Hope to see you there in Alpharetta, GA.

T-SQL Tuesday #39: Managing your SQL Server Services with PowerShell

AllenMWhite — Tue, 12 Feb 2013 18:09:00 GMT

This T-SQL Tuesday is about using PowerShell to do something with SQL Server. Now, if you've read any of my blog posts you probably know I've been using PowerShell to do things with SQL Server for a while now, but I'm glad Wayne decided on this topic for his T-SQL Tuesday topic, because everyone has different ways to use PowerShell, and you can learn from all of them, as I do.

(When I started to write this post I'd intended to share how I convert a PerfMon binary log file into SQL Server data for baseline analysis, but found I'd already done that here. Then, I thought I'd share how I save SQL Agent jobs and move them to another server, but did that one, too!)

One of the interesting aspects of SMO (Server Management Objects) is the Managed Computer object. It doesn't get a lot of attention because, well, that goes to the SQL Server instance and the various database objects. Administrators, though, need to pay attention to managing the instance itself. SQL Server 2008 introduced the Configuration Manager, a GUI application that allows administrators to view the SQL Server services installed, including their current state, the service account they use, etc. It also allows them to manage the external access to the instances via the network protocols supported, the TCP/IP ports, etc.

Here's a diagram of the Managed Computer object:

Now, if you're familiar with the Configuration Manager you should see some parallels there, and that makes sense, because these objects are the ones Configuration Manager is working with.

Let's say it's time for you to change the service account and password for your SQL Server instance. Using this model, we have our guide. We need to create a new ManagedComputer object, connect to the server, connect to the service, use the SetServiceAccount() method to set the new values, then restart the service. In this example I'll also restart the Agent service since I'm resetting the SQL Server instance service account.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer localhost
$sqlinst = $mc.Services['MSSQLSERVER']
$sqlagnt = $mc.Services['SQLSERVERAGENT']
$sqlinst.SetServiceAccount('TESTDOMAIN\AlternateAcct','L44HhRMeF25UDvQeJTj5UqyE')
$sqlinst.Alter()
$sqlinst.Stop()
start-sleep -s 10
$sqlinst.Start()
$sqlagnt.Start()

Let's say I just want to see the services on my local instance, like I do in Configuration Manager. Again, that's pretty easy.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer localhost
$mc.Services | select Name, ServiceState, DisplayName, ServiceAccount | format-table

There are times you'll need to work with the network protocols, and I've actually used the following code to change the IP port for an instance after an install, because it HAD to match a particular value.

$mc = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') localhost
$i=$mc.ServerInstances['MSSQLSERVER']
$p=$i.ServerProtocols['Tcp']
$ip=$p.IPAddresses['IPAll']
$ip.IPAddressProperties['TcpDynamicPorts'].Value = '1099'
$p.Alter()

Now, why is this important, if I can do all this in Configuration Manager? Well, SQL Server 2012 supports installation on Windows Server Core, and there's no GUI on a Core server, so this becomes the best way to access the objects you normally manage with Configuration Manager. More importantly, it allows you to build a set of scripts to "just handle" any number of problems, without trying to remember what submenu gives you access to what property to change to solve your problem.

Good luck, and thanks, Wayne, for hosting this month's T-SQL Tuesday!

Allen

Speaking - SQL Saturday 173, Washington DC

AllenMWhite — Wed, 28 Nov 2012 19:14:00 GMT

After a great time at the PASS Summit in Seattle I'll be once again presenting on PowerShell for SQL Server at SQL Saturday #173 in Chevy Chase, Maryland. On Friday, December 7 I'll be presenting my full day session Automate and Manage SQL Server with PowerShell. Here's the abstract:

This soup-to-nuts all day session will first introduce you to PowerShell, after which you'll learn the basic SMO object model, how to manipulate data with PowerShell and how to use SMO to manage objects. We'll then move on to creating Policy-Based Management policies, work with the Central Management Server, manage your system inventory and gather performance data with PowerShell. We'll wrap up with a look at the new PowerShell cmdlets introduced for SQL Server 2012 and how you can use PowerShell to manage SQL Server 2012 in server environments including the new Always On technology and Windows Server Core. After this one day you'll be ready to go to work and able to use PowerShell to make you truly effective.

On Saturday, for those who can't make Friday's session I'll present PowerShell 101 for the SQL Server DBA, the introductory module from the all day session.

If you've taken a look at Windows Server 2012 - and if you haven't yet, you should - you know that PowerShell is the tool Microsoft has chosen to allow you to manage hundreds, even thousands, of servers. Using the graphical tools available will only slow you down, making you more easily replaceable. Learning how to automate and manage lots of servers improves your efficiency and your value to your company. There's a real advantage to understanding how to use PowerShell to get things done quickly.

So I look forward to seeing you on Friday, December 7 and Saturday December 8 in Chevy Chase, for SQL Saturday #173!

Allen

PASS Summit 2012 PreCon - DBA-298-P Automate and Manage SQL Server with PowerShell

AllenMWhite — Thu, 08 Nov 2012 18:56:00 GMT

On Tuesday I presented an all-day pre-conference session on using PowerShell to automate and manage SQL Server. It was a very full day and we had a lot of great questions. One discussion in Module 6 was around scripting all the objects in a database, and I'd mentioned the script I wrote for the book The Red Gate Guide to SQL Server Team-based Development. When putting together the demos for the attendees to download I realized I'd placed that script in the Module 6 folder, so you don't need to go anywhere special to get it, it's there.

I've attached the demo material to this blog post, and those of you who stayed with me until the end of the day know the password for the file, and to the rest of you, please respect that these people paid to attend the session and the material is exclusively for them.

Thanks to everyone who attended!

Allen

[Speaking] PowerShell at the PASS Summit

AllenMWhite — Mon, 29 Oct 2012 14:49:00 GMT

Next week is the annual PASS Summit, the event of the year for those of us in the SQL Server community. We get to see our old friends, make new friends, and learn an amazing amount about SQL Server, and it'll be in Seattle, so it's close to the mother ship. I love having Microsoft close, because it's easier to get to know the people who actually make this amazing product we spend our lives working with.

This year I'm fortunate to have been selected to present three sessions. One is a regular session called Manage SQL Server 2012 on Windows Server Core with PowerShell, where I'll be showing you how to set up and install SQL Server 2012 on Windows Server 2008 R2 and Windows Server 2012 Server Core, with some cool things that make that setup easy to manage. I'm also doing a Spotlight session called Maintain SQL Server System and Performance Data with PowerShell, where I'll show you how to create a database to contain the inventory of the servers you manage, and how to gather performance metrics, all with PowerShell.

On Tuesday, I'll also be doing a pre-conference session all day called Automate and Manage SQL Server with PowerShell. I truly enjoy spending the day with this session. We start out with an introduction to PowerShell, because you really need some basics before the more advanced topics make sense. This is not a comprehensive PowerShell course, because that would take days, but the introduction will give you enough to truly get started.

Once we get the basics down, we'll dive into Server Management Objects (SMO), because the SQL Server team built this interface specifically for managing SQL Server. You may love SMO or you may hate it, but understanding its structure will help you automate your tasks as you manage your environment. We'll get into the various activities a DBA is expected to manage and walk through scripts that solve those problems. As the day goes on we'll get into managing Central Management Server and Policy-Based Management, and we'll cover the topics in the other sessions I'll be doing on Wednesday. We'll also see how much more important PowerShell is in Windows Server 2012 than ever before.

If you haven't sensed the excitement about the upcoming PASS Summit, check it out, and I hope to see you there!

Allen

Scanning the Error Log with PowerShell

AllenMWhite — Tue, 25 Sep 2012 18:33:00 GMT

One of the most important things you can do as a DBA is to keep tabs on the errors reported in the error log, but there's a lot of information there and sometimes it's hard to find the 'good stuff'. You can open the errorlog file directly in a text editor and search for errors but that gets tedious, and string searches generally return just the lines with the error message numbers, and in the error log the real information you want is in the line after that.

PowerShell 2.0 introduced a new cmdlet called Select-String which searches through a text file and returns the lines matching the target string. What it adds is an command-line parameter [-context] which allows you to specify the number of lines before and after the match is found. Bingo! By specifying the parameter -context 0,1 with the Select-String cmdlet it returns the line with the error, and the line afterwards.

To see what this does, first navigate to the errorlog directory, then issue the following commands:

$errlog = Get-Content '.\ERRORLOG'
$errlog | Select-String  -pattern 'Error:' -context 0,1

Now that's really useful, but it does mean that I have to log on to each server to get the errors, or I have to somehow know where every server's errorlog directory is and connect through the network to get to the files. Not so nice if you want to work from a client and 'just get the errors'.

Using SMO, there's a method under the Server object called ReadErrorLog() which reads the error log and (if assigned to a variable) creates a DataRow object containing the LogDate, ProcessInfo and Text properties from the errorlog. The ProcessInfo property contains the spid or other identifying value to indicate the source of the problem. By connecting to an instance using the Server object and loading the error log into the DataRow object, we can then get similar results, but we have to add an additional parameter, -inputobject, which lets us specify the property to search for our string.

$svr = new-object ('Microsoft.SqlServer.Management.SMO.Server') MyServer
$err = $svr.ReadErrorLog()
$err | Select-String -inputobject {$_.Text} -pattern 'Error:' -context 0,1

The advantage of this solution is that I don't log into the target server and I don't have to know where the log file is. The downside is that we lose the LogDate and ProcessInfo properties altogether, and there's no way to piece the parts back together. (Now, as sure as I say this Chad Miller will pipe up with a cleaner way to do this. This is good, and it's how I learn.)

What I found that works, though it's a tad clunky for my taste, is to concatenate the properties back together before sending it to the Select-String cmdlet, like this:

$errlog = @()
$err | foreach { $errlog += [string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text }

The problem with this approach, of course, is you have to pass through the errorlog twice. To minimize this, I added an option to only report the errors starting with a particular date.

$errlog = @()
$err | where {$_.LogDate -ge $startdt} | foreach {
	$errlog += [string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text
	}

This minimizes the issue, and if you're properly cycling the error log (normally once a week) the amount of log data it has to cycle through twice isn't too bad. With the properties concatenated we then return to the original search command to get our results.

$errlog | Select-String  -pattern 'Error:' -context 0,1

But wait, there's more! After playing with it for a bit I realized I'd like an option to just return DBCC results. Unlike errors, DBCC results are reported on the same line as the DBCC reports it was run, so I don't need the line afterward. In my script I added a variable called $srch to the command line arguments, and if it's equal to 'DBCC' it just returns the DBCC lines.

# Search the errorlog and return any error and the subsequent detailed message
if ($srch -eq 'DBCC') {
	$errlog | select-string -pattern 'DBCC' -context 0,0
	}
else {
	$errlog | select-string -pattern 'Error:' -context 0,1
	}

And that's it! I named the script scan-errorlog.ps1, and it takes the instance name, start date and (optionally) 'DBCC' as parameters, and returns either errors for that server after the start date, or DBCC results since the start date.

./scan-errorlog.ps1 MyServer 9/25/2012

That command line will return any errors reported in the errorlog since midnight last night.

It's really fun to find gems like this that make our lives as a DBA much easier.

Allen

24 Hours of PASS - PowerShell 101 for the SQL Server DBA

AllenMWhite — Fri, 21 Sep 2012 02:32:00 GMT

Thanks to everyone who came out for the session today. It was a lot of fun and I hope you had a great experience as well. I've attached the demo scripts and slide deck to this post, and I look forward to seeing you in November at the PASS Summit!

Allen

Speaking - 24 Hours of PASS, Summit Preview Edition

AllenMWhite — Wed, 19 Sep 2012 01:26:00 GMT

There's so much to learn to be effective with SQL Server, and you have an opportunity to immerse yourselves in 24 hours of free technical training this week from PASS, via the 24 Hours of PASS event.

I'll be presenting an introductory session on PowerShell called PowerShell 101 for the SQL Server DBA. Here's the abstract:

The more you have to manage, the more likely you'll want to automate your processes. PowerShell is the scripting language that will make you truly effective at managing lots of servers. But it's more than just a scripting language - it's an interactive shell that stores data for you and allows you to implement ad-hoc solutions quickly and easily. Within the PowerShell environment you can easily manage both SQL Server instances and the Windows servers themselves, giving you a 'best of both worlds' environment that puts you in control. This session will introduce you to PowerShell and show you how to use it to manage SQL Server across many instances.

I look forward to seeing you there.

Allen

SQL Rally Presentations

AllenMWhite — Fri, 11 May 2012 13:48:00 GMT

As I drove to Dallas for this year's SQL Rally conference (yes, I like to drive) I got a call asking if I could step in for another presenter who had to cancel at the last minute. Life happens, and it's best to be flexible, and I said sure, I can do that. Which presentation would you like me to do? (I'd submitted a few presentations, so it wasn't a problem.)

So yesterday I presented "Gathering Performance Metrics With PowerShell" at 8:45AM, and my newest presentation, "Manage SQL Server 2012 on Windows Server Core with PowerShell" at 1PM at the conference here in Dallas. The room was packed for both sessions and I truly appreciate every one of you who attended and there were great questions in both sessions.

I've attached a zip file containing the slide decks from both presentations along with the demo scripts. I hope you get as much out of using PowerShell with SQL Server as I have, and Thanks!

Allen