Search results matching tags 'SQL Server 2008' and 'SMO'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+2008,SMO&orTags=0Search results matching tags 'SQL Server 2008' and 'SMO'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Change SQL Servers Authentication Mode with PowerShellhttp://sqlblog.com/blogs/allen_white/archive/2011/05/19/change-sql-servers-authentication-mode-with-powershell.aspxThu, 19 May 2011 20:22:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:35748AllenMWhite<p>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.</p>
<p>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 <a href="http://sqlblog.com/blogs/allen_white/archive/2008/01/09/create-agent-jobs-to-run-powershell-scripts.aspx">here</a>.)</p>
<pre># Connect to the instance using SMO<br>$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'<br>[string]$nm = $s.Name<br>[string]$mode = $s.Settings.LoginMode<br><br>write-output "Instance Name: $nm"<br>write-output "Login Mode: $mode"<br></pre>
<p>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:</p>
<ul>
<li>Integrated - Windows Authentication</li>
<li>Mixed - Mixed Mode</li>
<li>Normal - SQL Server Only Authentication</li>
<li>Unknown - Undefined (and no, I haven't tried it.)</li>
</ul>
<p>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:</p>
<pre>#Change to Mixed Mode<br>$s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed<br><br># Make the changes<br>$srv.Alter()<br></pre>
<p>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.</p>
<p>&nbsp;</p>
<p>Allen</p>Discover Facets and Properties in PowerShellhttp://sqlblog.com/blogs/allen_white/archive/2010/09/27/discover-facets-and-properties-in-powershell.aspxMon, 27 Sep 2010 14:56:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:29002AllenMWhite<p>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.</p>
<p>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:</p>
<pre>[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Dmf')
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.Management.Sdk.Sfc')
</pre>
<p>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.</p>
<pre>$conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=SQLTBWS\INST01;Trusted_Connection=true");
$PolicyStore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($conn);
</pre>
<p>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:</p>
<pre>$facets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets
$facets | select Name, Description
</pre>
<p>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:</p>
<pre>$f = $facets['IDatabaseMaintenanceFacet']
$fp = $f.FacetProperties
$fp | select Name, PropertyType
</pre>
<p>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.</p>
<p>Allen</p>PASS PowerShell/SQL Server PreConhttp://sqlblog.com/blogs/allen_white/archive/2010/08/19/pass-powershell-sql-server-precon.aspxThu, 19 Aug 2010 19:17:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:28067AllenMWhite<p>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:</p>
<p><a>Use PowerShell to Get the Most out of SQL Server</a></p>
<ul>
<li>Module 1: Introduction to PowerShell</li>
<ul>
<li>Cmdlets</li>
<li>Aliases</li>
<li>The Pipeline</li>
<li>Variables</li>
<li>Objects</li>
<li>Control Flow</li>
<li>Functions</li>
<li>Modules</li>
<li>Error Handling</li>
<li>Command-line Arguments</li>
</ul>
<li>Module 2: Introduction to SMO</li>
<ul>
<li>Introduction to the SMO Library</li>
<li>SQLPS.exe - The SQL Server Mini-Shell</li>
<li>The SMO Object Model</li>
</ul>
<li>Module 3: Backup and Recovery</li>
<ul>
<li>Backup</li>
<li>Restore</li>
</ul>
<li>Module 4: Automating &amp; Maintaining Databases</li>
<ul>
<li>Automating Index Maintenance</li>
<li>Automating DBCC</li>
</ul>
<li>Module 5: Importing, Exporting and Transforming Data</li>
<ul>
<li>ADO.NET</li>
<li>Invoke-SQLCMD</li>
<li>Importing and Exporting using CSV Files</li>
<li>Importing and Exporting using XML Files</li>
</ul>
<li>Module 6: Manipulating Database Objects</li>
<ul>
<li>Creating and Deleting Schemas</li>
<li>Creating, Modifying and Deleting Tables</li>
<li>Creating, Modifying and Deleting Triggers</li>
<li>Creating, Modifying and Deleting Indexes</li>
<li>Creating, Modifying and Deleting Views</li>
</ul>
<li>Module 7: Managing the Enterprise</li>
<ul>
<li>Registering Servers</li>
<li>Creating a Central Management Server</li>
<li>Connecting to a Central Management Server</li>
<li>Running Scripts Against Multiple Servers</li>
<li>Defining Policies for Policy-Based Management</li>
<li>Evaluating Policies for Policy-Based Management</li>
</ul>
<li>Module 8: Profiling and Monitoring SQL Server</li>
<ul>
<li>Gathering WMI Information</li>
<li>Gathering Performance Counters</li>
<li>Setting up a Server Side Trace</li>
<li>Monitor Log Growth</li>
</ul>
<li>Module 9: Configuring and Tuning SQL Server</li>
<ul>
<li>Setting Configuration Options</li>
<li>Setting Windows Mode or Mixed Mode</li>
</ul>
</ul>
<p>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.</p>
<p>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 <a href="http://sqlpass.eventpoint.com/topic/details/DBA232P" title="SQLPass.org" target="_blank">Use PowerShell to Get the Most out of SQL Server</a>.</p>
<p>See you there!</p>
<p>Allen</p>SMO Changes from SQL 2005 to SQL 2008http://sqlblog.com/blogs/allen_white/archive/2009/02/20/smo-changes-from-sql-2005-to-sql-2008.aspxFri, 20 Feb 2009 14:17:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:12030AllenMWhite<p>A year ago in anticipation of publishing a book on SMO (which never came to be) I wrote a number of chapters demonstrating how to manage specific sets of objects, and I provided examples in VB.Net, C# and PowerShell. As should be expected, I thoroughly tested all of the code samples to make sure they worked.</p>
<p>This year I've been part a couple of book projects, one being the MVP Charity book which will come out this spring, and I just completed the chapter called "Scripting with PowerShell" for Paul Nielsen's <a href="http://www.sqlserverbible.com/">SQL Server 2008 Bible</a>. Without thinking much about it I included a couple of scripts that I'd written for the SMO book as examples. Unfortunately, I didn't test them again in my SQL Server 2008 environment.</p>
<p>I've already blogged <a href="http://sqlblog.com/blogs/allen_white/archive/2007/10/19/sql-2008-smo-doing-a-little-rearranging.aspx">here</a> about the relocation of objects from the SMO.dll to the SMOExtended.dll.</p>
<p>What burned me most recently was a change in how you load a table object. Under SQL 2005 the following code worked fine:</p>
<pre>[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") "MyServer\MyInstance"
#Reference the AdventureWorks database.
$db = $s.Databases["AdventureWorks"]
#Connect to the HumanResources.Employee table
$tbhremp = $db.Tables["Employee", "HumanResources"]
</pre>
<p>The script just wouldn't work using the SQL Server 2008 SMO DLLs. Here's what I ended up doing, and this worked.</p>
<pre>#Connect to the HumanResources.Employee table
$tbhremp = $db.Tables | where-object {$_.Name -eq 'Employee' -and $_.Schema -eq 'HumanResources'}
</pre>
<p>I'm not sure why the first construct wouldn't work, but I couldn't even load the ErrorLog table using the first method. (I used that table for testing because it's in the dbo schema, which my login had as its default, so I didn't need to specify the schema name.)</p>
<p>Needless to say, any scripts you use today which rely on the behavior of the SQL 2005 DLLs need to be thoroughly tested with the SQL 2008 DLLs after they've been installed.</p>
<p>Allen</p>Loading SMO Assemblies into PowerShellhttp://sqlblog.com/blogs/allen_white/archive/2008/12/07/loading-smo-assemblies-into-powershell.aspxSun, 07 Dec 2008 20:59:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10341AllenMWhite<p>Microsoft included PowerShell with SQL Server 2008, and the new sqlps.exe program which includes the SQL PowerShell drivers including the PS Drives and new cmdlets for SQL Server. I prefer to work from vanilla PowerShell and load the assemblies I need, so I have code at the start of my script to load the appropriate assemblies.</p>
<p>When I started testing my SQL 2005 PowerShell scripts against a system with SQL 2008 loaded I ran into problems because many of the functions formerly included in the SMO DLL were moved to the SMOExtended and SQLWMIManagement DLLs. I was teaching a class this past week where a student asked if the code could be written so that the proper DLLs were loaded regardless of which version of SQL Server was installed.</p>
<p>I started playing with it and found some anomalies (or what seemed to me to be anomalies) in doing this. First, I expected the version information passed back when you load an assembly to be different between the two systems, but when I loaded the SMO assembly both reported back v2.0.50727. This is obviously of no help.</p>
<p>After playing with a few properties I found the release information I wanted buried in the property called FullName. I executed the following statement:</p>
<pre>$v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
</pre>
<p>The $v variable contains a value of type System.Reflection.Assembly and the FullName property of this variable contains</p>
<pre>2005: Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
2008: Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
</pre>
<p>So, by splitting out the string returned by the FullName property I can get the version. First I split by a comma (,), then by the equal sign (=), then finally by a period (.), and when I'm done I have the main version number of the DLL loaded. Here's the code:</p>
<pre># Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$p = $v.FullName.Split(',')
$p1 = $p[1].Split('=')
$p2 = $p1[1].Split('.')
if ($p2[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
}
</pre>
<p>With this at the head of my PowerShell scripts I can be certain that whatever version of SQL Server I'm using the same scripts will load and run properly.</p>
<p>Allen</p>PASS Demonstration Errorshttp://sqlblog.com/blogs/allen_white/archive/2008/11/24/pass-demonstration-errors.aspxMon, 24 Nov 2008 16:37:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10073AllenMWhite<p>Errors occur. (I'm sure that's someone's corollary to Stuff Happens.) Errors occur, and that's why we write error handling code in our applications.</p>
<p>My least favorite time for the occurrance of errors is when I'm doing demos, and that's what happened in a presentation I did at PASS last week. I try to test everything before giving the demos, but every so often something slips through the cracks, and that happened. :-(</p>
<p>Two things that I'd blogged about here (<a href="http://sqlblog.com/blogs/allen_white/archive/2007/10/19/sql-2008-smo-doing-a-little-rearranging.aspx">SQL 2008 SMO - Doing a Little Rearranging</a>) came back and bit me, because I'd forgotten about them. In a nutshell, the Backup/Restore objects have been moved to Microsoft.SqlServer.SmoExtended.dll and the WMI objects have been moved to Microsoft.SqlServer.SqlWmiManagement.dll. The appropriate DLL has to be loaded in the PowerShell script before the objects contained in those DLLs can be referenced.</p>
<p>If you download my demo scripts from the PASS website, know that they're configured to be run from a workstation where SQL Server 2008 tools have been installed. They'll work against SQL Server 2000, 2005 and 2008. If you want to use them from a workstation with SQL Server 2005 tools, then delete the lines reference the aforementioned DLLs and they'll run fine.</p>
<p>One other problem occurred with my createdb.ps1 script, which is a condition that's occurred since I started working with SMO from PowerShell. In the Server object there's a collection called Settings. In that collection are properties for DefaultData and DefaultLog. These properties are very convenient for finding where your data and log files are supposed to go when you're adding files to databases.</p>
<p>For some reason, these properties are set to null, or an empty string, when the server is installed. The only way I've found to set them to their proper values is to use the Server Properties dialog in Management Studio, go to the Database Settings page and change them to something very different than what's there, then go back and set them back again. Once that's done the properties return the correct values in PowerShell and the createdb.ps1 script I use in my demos works just fine.</p>
<p>I've updated the slides and demos available for download from the PASS site, so grab those updated files and good luck with your testing.</p>
<p>Allen</p>SQL Server 2008 SMO Library Changeshttp://sqlblog.com/blogs/allen_white/archive/2008/10/18/sql-server-2008-smo-library-changes.aspxSat, 18 Oct 2008 16:35:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:9560AllenMWhite<p>While preparing for a presentation at PASS next month I tested my PowerShell script to back up user databases on a server. This script has been running flawlessly against SQL Server 2005 for a couple of years, so I was really surprised when PowerShell returned a screen full of errors and no backups.</p>
<p>What slipped my mind was something I'd reported a year ago <a href="http://sqlblog.com/blogs/allen_white/archive/2007/10/19/sql-2008-smo-doing-a-little-rearranging.aspx">here</a>, that a number of objects have been moved from the SMO dll to the SMOExtended dll.</p>
<p>The problem was easily resolved by adding the following line to the top of my PowerShell script:</p>
<pre>[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | out-null
</pre>
<p>Fortunately it's a minor <i>gotcha</i>, but one that can cause some problems after an upgrade if you're not prepared.</p>
<p>Allen</p>SQL Saturday 6 - Cleveland August 9http://sqlblog.com/blogs/allen_white/archive/2008/06/27/sql-saturday-6-cleveland-august-9.aspxFri, 27 Jun 2008 11:42:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:7542AllenMWhite<p>Wow, it's been a crazy month! Two weeks of Tech Ed in Orlando, where I spent time helping people with questions on SQL Server in the Database Platform area and presented two sessions on SMO and PowerShell (one with my friend Peter Ward from Australia). Then, my last week as a DBA at Advanstar, followed by my first week as a Trainer for <a href="http://www.scalabilityexperts.com/">Scalability Experts</a>! Wow.</p>
<p>So I wanted to get the word out about our upcoming SQL Saturday event in Berea, Ohio, on the campus of Baldwin-Wallace College. If you're going to be in the area, or if this will provide motivation to come to Cleveland, it should be a great event. I'm hoping to have about 30 sessions on SQL Server, both how to do things now, and how you'll be able to do things much easier with SQL Server 2008.</p>
<p>The Call for Abstracts is open, so if you're interested in presenting, please submit your session idea <a href="http://www.sqlsaturday.com/callforspeakers.aspx">here</a>.</p>
<p>If speaking isn't your strong suit but you'd like to attend we'll be happy to have you. The link to register is <a href="http://www.sqlsaturday.com/register.aspx">here</a>.</p>
<p>The morning sessions will each be an hour long, in the Math and Computer Science building, and the afternoon sessions will be 75 minutes each, and be held in larger rooms in Strosacker Union. We'll have a box lunch on the picnic tables behind the Union (weather permitting) and a nice wrapup after the sessions with some great giveaways, including a full MSDN Team Suite subscription!</p>
<p>I look forward to seeing you at SQL Saturday, and perhaps meeting some of you in my teaching assignments with Scalability Experts!</p>
<p>Allen</p>Start PowerShell in Management Studiohttp://sqlblog.com/blogs/allen_white/archive/2008/02/20/start-powershell-in-management-studio.aspxWed, 20 Feb 2008 12:59:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:5158AllenMWhite<p>In the February CTP of SQL Server 2008 a new feature has appeared in SQL Server Management Studio. You now have the ability to right-click on an object in the Object Explorer window and open up a PowerShell window. In this window you can navigate the database structures much like you can a disk file subsystem, or like you can navigate the registry in PowerShell.</p>
<p>So, let's say you right-click on your server name and open up a PowerShell window. You can then issue the command:</p>
<pre>cd Databases/AdventureWorks<br></pre>
<p>Now you're pointing to the AdventureWorks database. Using PowerShell in this way allows you to browse the Server Management Objects (SMO) tree structures. (By the way, you'll have to remember to use proper case when browsing the SMO objects. They are case-sensitive in this environment.) Now, you can type this:</p>
<pre>dir Tables<br></pre>
<p>What returns is a list of the tables in AdventureWorks. It's kind of neat idea, and definitely fun to play with. I haven't found a productive use for it yet, but it's too new for me to make a judgement yet. I like it, but I don't yet know why.</p>
<p>Let me know what you think.</p>
<p>Allen</p>SQL 2008 SMO - Doing a Little Rearranginghttp://sqlblog.com/blogs/allen_white/archive/2007/10/19/sql-2008-smo-doing-a-little-rearranging.aspxFri, 19 Oct 2007 18:36:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:3050AllenMWhite<p>In browsing through the SQL Server 2008 SMO Object Library I noticed there are a couple of additional DLL's. For example, the SQL Server WMI Management objects, formerly in Microsoft.SqlServer.Smo.dll, are now in Microsoft.SqlServer.SqlWmiManagement.dll. There's also a new one called Microsoft.SqlServer.SmoExtended.dll. I took a look in there and found this list of objects, which have been removed from Microsoft.SqlServer.Smo.dll:</p>
<p>
SmoExtended Objects
</p><li>AsyncStatus
</li><li>Backup
</li><li>BackupActionType
</li><li>BackupDevice
</li><li>BackupDeviceCollection
</li><li>BackupDeviceItem
</li><li>BackupDeviceList
</li><li>BackupRestoreBase
</li><li>BackupRestoreBase.AsyncOperation
</li><li>BackupTruncateLogType
</li><li>DeviceType
</li><li>PercentCompleteEventArgs
</li><li>PercentCompleteEventHandler
</li><li>RelocateFile
</li><li>Restore
</li><li>RestoreActionType
</li><li>SqlVerifyAction
</li><li>Transfer
</li><li>VerifyCompleteEventArgs
</li><li>VerifyCompleteEventHandler
<p>This means that if you are doing backup or data transfer operations through SMO you'll have to be sure to add references in your projects to the SmoExtended dll in SQL Server 2008.</p>
<p>I expect there will be more changes as updated CTP's are released.</p>
<p>Allen</p></li>