Search results matching tags 'powershell' and 'SQL Server 2008'

Change SQL Servers Authentication Mode with PowerShell

AllenMWhite — Thu, 19 May 2011 20:22:00 GMT

Lately I've been working on scripts to check and set security and configuration settings for SQL Server using PowerShell. One of the settings that I normally set and forget at install time is the Authentication Mode setting. Best practices suggest that you set this to Windows Authentication, but my experience has been that it's always better to set it to Mixed mode, and set a very strong password for the sa account.

So, what if we want to change it after the fact? Well, it's a registry setting. Management Studio allows you to make that change via the Security page in Server Properties, but I prefer scripting when setting configuration settings. The setting is exposed in SMO (Server Management Objects), however, and we can check the setting using PowerShell. (I'm going to assume you're either running SQLPS.exe or you've already loaded the SMO libraries. If you don't know what I'm talking about, check here.)

# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'
[string]$nm = $s.Name
[string]$mode = $s.Settings.LoginMode

write-output "Instance Name: $nm"
write-output "Login Mode: $mode"

What the script returns is the instance name, and the authentication mode it's using. The property in SMO is called LoginMode, and can have one of four values:

Integrated - Windows Authentication
Mixed - Mixed Mode
Normal - SQL Server Only Authentication
Unknown - Undefined (and no, I haven't tried it.)

I sometimes encounter a system that was set to Integrated, and I want to change it to Mixed mode, because I like to have that safety net of sa if something goes wrong. Here's how I do that:

#Change to Mixed Mode
$s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed

# Make the changes
$srv.Alter()

Once that's done the server does need to be restarted, and I need to go in and set the sa password to something VERY strong right away. But now the server is set to the authentication mode I prefer, and it's a lot easier (in my mind) than going into the registry to do it.

Allen

Speaking at SQL Saturday 61 in Washington DC

AllenMWhite — Thu, 02 Dec 2010 14:34:00 GMT

The organizers of SQL Saturday #61 in DC (actually Reston, VA) created an Advanced DBA/Dev track for their event, which I think is cool. Both of the presentations I'll be doing there on Saturday are in that track. (In fact, they're the first two sessions of the day.)

The first, Automate Policy-Based Management using PowerShell will walk through the basics of Policy-Based Management, and then show you how to build PowerShell scripts to create and evaluate your policies.

The second, Gather SQL Server Performance Data with PowerShell will go explain the basics of performance metrics, define a set of counters which make up a baseline, walk you through the PowerShell script which gathers those counters, and then I'll show you how to create a report to report on the performance, and show you how to view that report from within Management Studio.

I look forward to seeing you there.

Allen

Working with the SQL Server Powershell Provider and Central Management Servers from Powershell

jmkehayias — Sat, 09 Oct 2010 03:45:40 GMT

I am a big fan of the Central Management Server setup that exists in SQL Server 2008, as well as how you can leverage this for the Enterprise Policy Management Framework that Laura Rubbelke (Blog|Twitter) created on Codeplex. Today one of my DBA’s asked a question about how to leverage the list of servers in the CMS through PowerShell and I had to double back on my own code because in PowerGUI the SQL Server Powershell provider is not natively registered so I had to write some additional code to check whether or not the PowerShell Provider had actually been loaded or not before running code to return a list of the registered servers from the CMS. The following code will validate whether or not the PowerShell provider for SQL has been loaded as a PS-Drive or not and load it if it is not loaded, then return a list of the registered servers from the CMS.

# Load the Snapins for SqlServer if they aren't currently loaded
if((Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null)
{    Add-PSSnapin SqlServerCmdletSnapin100    }

if((Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null)
{      Add-PSSnapin SqlServerProviderSnapin100    }

$CMSPath = 'SQLSERVER:\SQLRegistration\Central Management Server Group'
foreach ($RegisteredServer in (dir $CMSPath -recurse | where-object { $_.Mode -eq "-" }))
{
    $RegisteredServer.Name
}

Discover Facets and Properties in PowerShell

AllenMWhite — Mon, 27 Sep 2010 14:56:00 GMT

When you're trying to set up scripts to deploy policies to your servers (because you ALWAYS automate with PowerShell, right?), it can be sometimes difficult to determine exactly what facets are there, and when you do know the facet name, what properties are available to you.

First, in a native PowerShell window you'll need to load the assemblies with the Policy-Based Management objects. Because PBM was originally called Dynamic Management Framework, the DLL carries the original name, so the following two lines will load the correct assemblies:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Dmf')
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.Management.Sdk.Sfc')

The next thing to do is to connect to our SQL Server instance, and load the instance's PolicyStore object, which is where the existing policies are kept.

$conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=SQLTBWS\INST01;Trusted_Connection=true");
$PolicyStore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($conn);

Now we can start exploring. We can pull the list of facets by referencing them within the PolicyStore object, and then store them in a variable called $facets. We can then pipe that variable into a select-object cmdlet and get the name and description of the available facets:

$facets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets
$facets | select Name, Description

Now we know what facets are available. Let's say we want to set up some database maintenance policies. We can figure out what properties are available to us by creating a facet object variable for the 'IDatabaseMaintenanceFacet' facet. Then we grab the properties list from it by setting a variable to the facet's FacetProperties object. Finally we can pipe the property list through the select-object cmdlet to see the name and property type of each property for the facet:

$f = $facets['IDatabaseMaintenanceFacet']
$fp = $f.FacetProperties
$fp | select Name, PropertyType

Once we have this information we can use it to build conditions, and from those conditions, the policies we want to establish on our servers.

Allen

SQL Server 2008 R2 Best Practices Analyzer

AaronBertrand — Wed, 25 Aug 2010 21:00:00 GMT

Maybe I'm a little late to the party, but I thought I would share with you the most recent release of Microsoft's Best Practices Analyzer for SQL Server 2008 R2 (you can also read the original blog announcement from CSS). Let me be clear: this tool is not just for people who are already on SQL Server 2008 R2. It will also happily analyze your SQL Server 2008 instances, so don't be fooled by the label.

That said, getting the thing installed was an exercise in patience. Like most people, I clicked the download link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591

And I was off to the races. Or so I thought. I still haven't developed a healthy habit of READING before CLICKING. The first time I ran the installer, I got this complaint about not having the Baseline Configuration Analyzer installed (I'm typing out the error message so that users experiencing the same thing can find this post, and I'm showing a screen shot to show how asinine it is to put a URL in a dialog but not have the ability to click or copy it):

Microsoft Baseline Configuration Analyzer 2.0 is not installed. Please install it from http://www.microsoft.com/downloads/details.aspx?FamilyID=1b6e9026-f505-403e-84c3-a5dea704ec67

To help out a bit, here is the link to download the Configuration Analyzer (which, I will admit, was much easier to obtain from a Google search than by transcribing the link from the dialog) :

http://www.microsoft.com/downloads/details.aspx?FamilyID=1B6E9026-F505-403E-84C3-A5DEA704EC67

If you're not on Windows 7 or Windows Server 2008 R2, you have the potential to receive error messages about missing PowerShell 2.0, as follows:

Microsoft Baseline Configuration Analyzer 2.0 requires PowerShell 2.0 to be pre-installed on the system.

No worries, you can download PowerShell 2.0 as part of Windows Update:

Or if you don't want to go through the hassle of Windows Update, you can also get it by installing the Windows Management Framework Core Package individually:

http://support.microsoft.com/?kbid=968930

(Now why, since PowerShell 2.0 is only a 6 MB download, it couldn't be published as part of the Configuration Analyzer setup, or at least to go fetch it dynamically during installation, I have no idea.)

I got a little bit further into the installation once I had the Configuration Analyzer installed. But then, I discovered in a very round-about way, that there is an issue with the installer when you try to put the BPA on a machine that is not connected to a domain (e.g. just about every virtual machine I own). When it tried to run the PowerShell commands during setup, I saw a PS window briefly, along with some scary-looking red text, and then it disappeared and setup returned this error message (again typed out for SEO goodness):

There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor.

Which is great to know that it wasn't BPA's fault, but something else's. But would it have been that hard to consume the error message that obviously came back from PowerShell? Or better yet, return an error message like this:

Google "BPA 2.0 error powershell"

Because as it turns out, there is a very easy fix to this, and it was the first hit for the search above. If you want to install BPA onto a machine that is not domain-savvy, instead of double-clicking the MSI file, run this command line (replacing <path> with the folder where the MSI file is located, and changing <platform> either to 32 or 64):

msiexec /i <path>\SQL2008R2BPA_Setup<platform>.msi SKIPCA=1

(Thanks to Adam Saxton for pointing this out on the CSS SQL blog.)

Once I got the BPA running, it did quickly point out several issues I was aware of (including bad partition alignment on a non-essential drive, some databases that were intentionally offline, and the fact that the error logs have a lot of history), as well as a couple of new issues:

The one I've expanded is one that Bob Ward talked about in a blog post yesterday as one of the key reasons to use the new BPA:

http://blogs.msdn.com/b/psssql/archive/2010/08/24/why-use-sql-server-2008-r2-bpa-case-1-missing-updates.aspx

Obviously it finds a bunch of things that you may already have alerting for, and probably some things that you don't. In any case, this can be a very quick way to assess a SQL Server instance without having to rummage around for all of this information manually, and even with the stumbling you may have getting it configured, it should pay off rather quickly. There are plenty of things it doesn't look for, and you can get that information from other places as well - for example, check out the BLITZ! takeover script from Brent Ozar (blog | twitter):

http://www.brentozar.com/sql/blitz-minute-sql-server-takeovers/

Anyway, I haven't really offered any input that you can't see elsewhere, but I hope it is useful nonetheless.

EDIT: If you are running on Windows Server 2003

PASS PowerShell/SQL Server PreCon

AllenMWhite — Thu, 19 Aug 2010 19:17:00 GMT

PowerShell is an amazing tool to help you automate your administrative processes. There are a lot of books and online sources to help you learn PowerShell, but how do you learn how to use it with SQL Server? Simple, just sign up for my PASS PreCon session. Here's the outline:

Use PowerShell to Get the Most out of SQL Server

Module 1: Introduction to PowerShell

Cmdlets
Aliases
The Pipeline
Variables
Objects
Control Flow
Functions
Modules
Error Handling
Command-line Arguments

Module 2: Introduction to SMO

Introduction to the SMO Library
SQLPS.exe - The SQL Server Mini-Shell
The SMO Object Model

Module 3: Backup and Recovery

Backup
Restore

Module 4: Automating & Maintaining Databases

Automating Index Maintenance
Automating DBCC

Module 5: Importing, Exporting and Transforming Data

ADO.NET
Invoke-SQLCMD
Importing and Exporting using CSV Files
Importing and Exporting using XML Files

Module 6: Manipulating Database Objects

Creating and Deleting Schemas
Creating, Modifying and Deleting Tables
Creating, Modifying and Deleting Triggers
Creating, Modifying and Deleting Indexes
Creating, Modifying and Deleting Views

Module 7: Managing the Enterprise

Registering Servers
Creating a Central Management Server
Connecting to a Central Management Server
Running Scripts Against Multiple Servers
Defining Policies for Policy-Based Management
Evaluating Policies for Policy-Based Management

Module 8: Profiling and Monitoring SQL Server

Gathering WMI Information
Gathering Performance Counters
Setting up a Server Side Trace
Monitor Log Growth

Module 9: Configuring and Tuning SQL Server

Setting Configuration Options
Setting Windows Mode or Mixed Mode

As you can see, we're going to spend the first part of the day learning the basics of PowerShell, but the focus is on how you can manage your own environment using this powerful language, so you can spend your time solving problems instead of the constant point-and-click drudgery of typical DBA tasks.

The all-day session is reasonably priced, and you'll come away with scripts to help you put this tool into use right away. Check out the session details at Use PowerShell to Get the Most out of SQL Server.

See you there!

Allen

Speaking at SQL Saturday Nashville

AllenMWhite — Wed, 18 Aug 2010 13:05:00 GMT

SQL Saturday - Nashville, TN, August 21, 2010

I'll be presenting two sessions this Saturday (August 21, 2010) in Nashville, TN - XQuery Basics and Gather SQL Server Performance Data with PowerShell. The event takes place at Nashville State Community College (120 White Bridge Road, Nashville, Tennessee, 37209). In the XQuery session I'll give an introduction to XQuery and show how easy it is to examine XML data in SQL Server, and will show how to extract data from devices like GPS receivers. In the Performance session I'll show how to build a PowerShell script to collect performance data for your SQL Server systems and build reports to monitor those servers. For more information please visit SQLSaturday.com or email us at sqlsaturday#51@sqlsaturday.com.

Allen

TechEd 2010 Followup

AllenMWhite — Sun, 13 Jun 2010 18:25:00 GMT

Last week I presented a couple of sessions at Tech Ed NA in New Orleans. It was a great experience, even though my demos didn't always work out as planned. Here are the sessions I presented:

DAT01-INT Administrative Demo-Fest for SQL Server 2008

SQL Server 2008 provides a wealth of features aimed at the DBA. In this demofest of features we'll see ways to make administering SQL Server easier and faster such as Centralized Data Management, Performance Data Warehouse, Resource Governor, Backup Compression and more.

DAT306 Gather SQL Server Performance Data with PowerShell

We all know how important it is to keep a baseline of performance metrics that allow us to know when something is wrong and help us to track it down and fix the problem. We don't always know how to do this easily and consistently. This session will walk you through a series of PowerShell scripts you can schedule which will capture the most important data and a set of reports to show you how to use that data to keep your server running smoothly.

I've attached the scripts I used for the demos. Please feel free to use these, and enhance them with your own needs, but please attribute this work to me if you publish your enhancements.

Thanks.

Allen

SQL Server PowerShell Provider And PowerShell Version 2 Get-Command Issue

BuckWoody — Mon, 10 May 2010 13:13:30 GMT

The other day I blogged that the version of the SQL Server PowerShell provider (sqlps) follows the version of PowerShell. That’s all goodness, but it has appeared to cause an issue for PowerShell 2.0. the Get-Command PowerShell command-let returns an error (Object reference not set to an instance of an object) if you are using PowerShell 2.0 and sqlps – it’s a known bug, and I’m happy to report that it is fixed in SP2 for SQL Server 2008 – something that will released soon.

You can read more about this issue here: http://connect.microsoft.com/SQLServer/feedback/details/484732/sqlps-and-powershell-v2-issues

Speaking at SQLSaturday #33 in Charlotte

AllenMWhite — Thu, 04 Mar 2010 15:33:00 GMT

I'll be presenting two sessions this Saturday (March 6, 2010) in Charlotte - Automate SQL Server Administration with PowerShell and Gather SQL Server Performance Data with PowerShell. The event takes place at the Microsoft Charlotte Offices on 8050 Microsoft Way, Charlotte, NC 28273. For more information please visit SQLSaturday.com or email us at sqlsaturday#33@sqlsaturday.com.

I hope to see you there.

Allen