Michiel Wories' WebLog

An insightful article on a recent Windows Azure service disruption

2012-03-23T00:17:29Z

This was such a great read that I wanted to share it out...

http://blogs.msdn.com/b/windowsazure/archive/2012/03/10/summary-of-windows-azure-service-disruption-on-feb-29th-2012.aspx

A factual, actionable, and humble article... Makes me feel proud to work at Microsoft.

-Michiel

The use of Write-Host and SQL Server Agent PowerShell job steps

2009-10-01T01:42:00Z

SQL Server Agent implements a job subsystem that allows users to directly run PowerShell scripts in SQL Server Agent. Internally this is implemented by reusing the SQLPS.EXE shell stub (which is another shape of POWERSHELL.EXE, but preconfigured for SQL Server).

When SQL Server Agent starts SQLPS.EXE, it does not do this through the console (there is no console attached).

The $Host variable will indicate what host is attached to your runtime. When running SQLPS.EXE on a console this will be your host:

PS C:\Users\mwories> $Host

Name             : ConsoleHost
Version          : 2.0
InstanceId       : 40b6135d-0f89-4b12-b44e-d3d70a729343
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : en-US
CurrentUICulture : en-US
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

However print the host variable in an Agent Job and you will see this:

Name             : Default Host
Version          : 2.0
InstanceId       : 9cf1e259-93b1-476f-8c3f-6fe9cf935aab
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : en-US
CurrentUICulture : en-US
PrivateData      :
IsRunspacePushed :
Runspace         :

The article How PowerShell Works explains well how the runspace interacts with the host.

The Host implement PSHostUserInterface and PSHostRawUserInterface that form the display interface. These may not implement certain methods for the Default Host. And indeed, for the Default Host (the host an Agent Job receives, as there is no console), the method that implements Write-Host is not available, and it will fail with the following message:

Executed as user: REDMOND\mwories. A job step received an error at line 3 in a PowerShell script. The corresponding line is 'write-host 'foobar''. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot invoke this function because the current host does not implement it. '. Process Exit Code -1. The step failed.

Now what to do when you want to create your scripts to run in a console but also hosts that may not have a console, and may not implement any host output interface?

This will work.

if ($host.Name -eq "ConsoleHost")
{
write-host 'foobar'
}

Of course you can find ways to simplify your scripts by wrapping this in a function that only writes output when a console is attached.

If there is any output that you like to be logged, it will not appear in the SQL Agent output. It is therefore better to use Write-Output if you want to provide some kind of output that shows up in SQL Agent output or history.

Hope this helps to understand the PowerShell subsystem in SQL Server Agent a bit better.

PowerShell Tips & Tricks: Getting more detailed error information from PowerShell

2009-06-08T18:41:00Z

Allen White wrote this handy blogpost on how to handle error message and get more information out of an error record:

http://sqlblog.com/blogs/allen_white/archive/2009/06/08/handling-errors-in-powershell.aspx

There is an addtional method to get more information available. I found myself sending this little code snippet in email to many, and almost always I get the response that it saved that person with a ton of work, as new users to PowerShell are often left bewildered by the sparse error information; I had colleagues grabbing for the debugger to find the more detailed error messages...

An example; let's generate a simple error (recreate master will not work on most systems):

$db = new Microsoft.SqlServer.Management.Smo.Database
$db.Name = "master"
$db.Parent = get-item .
$db.Create()
Exception calling "Create" with "0" argument(s): "Create failed for Database 'master'. "
At line:1 char:11
+ $db.Create( <<<< )

Not very helpful is it?

Here is where this little gem comes in:

$error[0]|format-list -force

Now let's take a look at the details this emits:

Exception      : System.Management.Automation.MethodInvocationException:
                        Exception calling "Create" with "0" argument(s): "Crea
                        te failed for Database 'master'. " ---> Microsoft.SqlSe
                        rver.Management.Smo.FailedOperationException: Create fa
                        iled for Database 'master'. ---> Microsoft.SqlServer.M
                        anagement.Common.ExecutionFailureException: An exceptio
                        n occurred while executing a Transact-SQL statement or
                        batch. ---> System.Data.SqlClient.SqlException: Databas
                        e 'master' already exists. Choose a different database
                        name.
                           at System.Data.SqlClient.SqlConnection.OnError(SqlEx
<cutting middle part out>
TargetObject          :
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : DotNetMethodTargetInvocation
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo

Well, now THAT's more like it!

As you can see the -force flag does a couple of nice things.

It concatenates the error messages into a somewhat understandable result.
It dumps stack information + all other error information avalable.

Generally it's a time saver as you have this command readily available for you at the command line.

Do you have any tips on error handling? I'd love to hear about it.

-Michiel

Talking about SQLPS (SQL Server Powershell extensions) on Runas Radio

2008-09-24T20:42:00Z

I was recently interviewed on RunAs Radio on the new Powershell extensions that we developed for SQL Server 2008.

If you want to learn more about SQL Server Powershell download the podcast here : http://www.runasradio.com/default.aspx?showNum=75

If you listened to it then please post your feedback here.

Happy listening!

What? No cmdlets? -- SQL Server Powershell

2008-06-25T10:25:00Z

When you start using the SQL Server Powershell extensions, you will find there are not a whole lot of cmdlets. The expectation is indeed that Powershell support means: cmdlets for every administrative operation you can perform. Did we miss something here? Nope, this is by design. We do intend to ship more cmdlets for common admin tasks, but our first goal was to unlock as many Object Models as we could in the first release.

We did provide some basic cmdlets to start you off with. We know that DBA's willl have tons of T-SQL scripts that they will not abandon immediately in favor of Powershell. These scripts can be integrated with Invoke-Sqlcmd. We also provided a cmdlet to evaluate Policies, something that will come in handy if you have a lot of servers to manage (see one of my previous posts that contains a reference to an excellent blog entry from Lara on this subject).

Right now you will find that you can navigate to four different object models:

SQL Server Relational Managenent Objects (SMO)
Policy Based Management Objects (DMF)
Data Collection (DC)
Server Registration

An interesting fact is that the SQL Provider that provides access to these models is almost totally agnostic about these models. It does navigation, and serves these objects to the Powershell host, but without any code that is aware of each of these models. It's made pretty generic and the plan is to start covering all other services (Analysis Server's AMO, RS, etc.) in the same fashion.

Now, back to 'where are the cmdlet's'.

We do plan to provider various common cmdlets for common tasks, such as backup, restore, transfer, adding logins and users (pending customer research). But with the provider serving up objects you have almost instant access to any adminstrative commands you need. Let me illustrate this with an example:

PS C:\> cd SQLSERVER:\SQL\W2K3-TEST3\KATMAI\Logins
PS SQLSERVER:\SQL\W2K3-TEST3\KATMAI\Logins> $t=get-item TEST
PS SQLSERVER:\SQL\W2K3-TEST3\KATMAI\Logins> $t.ChangePassword("asdasd182eqwke111AA")

With tab-completion you will be able to browse the available methods quickly, and changing the password is then a piece of cake.

Another thing to point out is that if you take a look at SMO, DMF, DC, RS, AMO, and the other object models you will find there are hundreds of different of objects and literally thousands of operations you can perform. Strictly speaking, if you were to provider full coverage you would end up with hundreds (maybe over a thousand) cmdlets. This makes discoverability quite cumbersome (let alone it is a huge investment to unlock something that is inherently available). And the complexity of some of the cmdlets would make it not practical. Take for example the Transact-SQL statement to create a table. This is a very hard thing to encapsulate in a cmdlet (or new-item). Go try to think up the cmdlet to support all of CREATE TABLE. This is where the provider will be your friend. Issue a Get-Item to grab the parent. Instantiate a new SMO object and modify it to your liking. Then parent it with the reference you received from the Provider and create it. This is how this looks like:

cd SQLSERVER:\SQL\W2K3-MVP\DEFAULT\Databases\tempdb
$t = new-object Microsoft.SqlServer.Management.Smo.Table
$t.Parent = (get-item .)
$t.Name ="test"
$c = new-object Microsoft.SqlServer.Management.Smo.Column
$c.Parent = $t
$c.Name = "c1"
$c.DataType = ([Microsoft.SqlServer.Management.Smo.DataType]::Int)
$t.Columns.Add($c)
$t.Create()

This is a simple example. Creating a basic database is even simpler. But if you need more options, it's all there, under your fingertips. And you don't need to wait until we provide a cmdlet for it. But we will provide cmdlets for everyday tasks. If you have any opinion which cmdlets are important to you, it would be good to hear from you.

Some background on the use of minishells, such as SQLPS

2008-06-23T19:58:00Z

Jeffrey Snover posted a very clear article about minishells and SQL Server's use of it:

http://blogs.msdn.com/powershell/archive/2008/06/23/sql-minishells.aspx

Jeffrey is Powershell's Architect, well respected in the Powershell community, and it's good to read about his perspective on this.

Evaluating SQL Server Policies through Powershell

2008-06-20T20:27:00Z

I found this ping-back on my previous blog post which gives a good overview of one very useful cmdlet that we ship with the Powershell extensions that SQL Server 2008 will deliver. A very helpful article one of the SQL Server MVPs, Lara Rubbelke, on the use of Invoke-PolicyEvaluation:

http://sqlblog.com/blogs/lara_rubbelke/archive/2008/06/19/evaluating-policies-on-demand-through-powershell.aspx

SQL Server Powershell is here!

2008-06-15T01:35:00Z

I am very excited that SQL Server will ship with a pretty solid first release of Powershell extensions with SQL Server 2008. The SQL Server Powershell extensions deliver on a vision that we’ll expand on in the next releases to come. So what’s in this release of SQL Server Powershell?

- SQLPS – a minishell that gives you a complete pre-configured Powershell with all of SQL Server’s extensions preloaded.

- SQL Server Agent integration – A new job subsystem for Powershell

- SQL Server Management Studio Integration – context menus on every applicable node in Object Explorer (with connection context reuse. Including SQL security!)

- Four new Providers! – new providers for SQL Server relational engine, Registered Servers, Data Collection, and SQL Server Policy Management

- SQLCMD integration – SQLCMD compatible script execution within Powershell (reuses the SQL Server connection context, and even database context of the provider!)

- SQL Server Policy Management integration – Allows evaluation of any Policy

- Various other cmdlets – support the provider, such as conversion of a SMO Urn to a Powershell path, encoding and decoding of SQL identifiers.

- SQL Server Powershell redist – allows you to install SQL Server Powershell with your application or on any machine you need to have it on (this still being built so with the caveat it may be shipped later, or being cut altogether – don’t flame me yet).

As always with every release, a lot of things were left on the cutting floor, such as Powershell editor integration & execution within SSMS (with grid output), Powershell script generation from any SSMS menu, SMO objects generating Powershell script, more cmdlets that address common user scenarios, Analysis Server support. And this doesn’t include a long list of ideas that for the sake of brevity will not discuss here.

The excitement about this release is palpable, and there is no shortness of ideas for the next releases. I think you’ll be pleased with this first release.

---

From recent posts and email I know there is some explanation needed why we ship ‘pre-packaged’ SQLPS functionality. Let me address that here as well.

SQLPS is slated to be replacing SQLCMD and other tools that people now use to do ad-hoc management and management task automation. It’s more than that, we want uniformity of management across ALL of SQL Server services, whether it is Analysis Server, SQL Server, Integration Services, Reporting Services etc.

SQLPS.exe is a Minishell (also called “custom shell”). It is a form of pre-packaging of Powershell functionality, and it is available to anyone who wants to do this (make-shell). It is regular Powershell, albeit with limitations that the Powershell team decided to impose on it – it is a ‘closed’ shell, which doesn’t allow adding other snapins.

We are shipping SQLPS to make life of our DBA’s a whole lot easier. If they need to have quick access to the SQL providers, assemblies, cmdlets, default security settings, everything is there. We could have possibly done this through a startup script but not everything can be accomplished this way. We are changing the default security settings, without affecting the settings for overall Powershell. Minishells have their own settings.

We also use SQLPS to execute SQL Agent jobs. We pipe the data into SQLPS, as we cannot pass it on the cmdline. From that perspective it serves our purpose very well. This is somewhat harder (if not impossible) to accomplish using the startup script approach.

That said, in the next release we will look into it to ship a SQPS that is more flexible. Listening to the feedback, we should have a form of packaging that allows users to add cmdlets/providers.

We are however not stopping anyone to create their own startup scripts that include the cmdlets and/or providers for SQL Server, Exchange, IIS etc. etc. To get you started on this, here is a script that exactly does that:

#
# Initialize-SqlpsEnvironment.ps1
#
# Loads the SQL Server provider extensions
#
# Usage: Powershell -NoExit -Command "& '.\Initialize-SqlPsEnvironment.ps1'"
#
# Change log:
# June 14, 2008: Michiel Wories
#   Initial Version
# June 17, 2008: Michiel Wories
#   Fixed issue with path that did not allow for snapin\provider:: prefix of path
#   Fixed issue with provider variables. Provider does not handle case yet
#   that these variables do not exist (bug has been filed)

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Powershell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}

#
# Preload the assemblies. Note that most assemblies will be loaded when the provider
# is used. if you work only within the provider this may not be needed. It will reduce
# the shell's footprint if you leave these out.
#
$assemblylist =
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum"

foreach ($asm in $assemblylist)
{
$asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}

#
# Set variables that the provider expects (mandatory for the SQL provider)
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location

Write-Host -ForegroundColor Yellow 'SQL Server Powershell extensions are loaded.'
Write-Host
Write-Host -ForegroundColor Yellow 'Type "cd SQLSERVER:\" to step into the provider.'
Write-Host
Write-Host -ForegroundColor Yellow 'For more information, type "help SQLServer".'

Now, go and use SQL Server Powershell :-). Give us your feedback, and I promise you we’ll listen to it, and we’ll keep on improving on it. This is written for you, the DBA, the IT-PRO, the casual user who needs to do some ad hoc administration or anyone else who uses Powershell for their administrative tasks.

Michiel

How to change the SQL Server password programmatically

2006-11-04T02:32:00Z

Every now and the question comes up how to programmatically modify the service account or password of SQL Server. There basically two ways to accomplish this, using SMO, or WMI. This article shows you how to use WMI and VBScript to accomplish this task.

There are a couple of things you need to know.

SQL Server does not need a reboot when the password is modified. We (SQL Server Team) spent quite a bit of time ensuring you do not suffer any unnecessary downtime when performing an operation that has to happen regularly, when password are expiring. Everyone likes a ‘no reboot’ clause, right?
When changing the account and password, the service does need a reboot, but the WMI Provider does this for you. In fact, if the service is not started it will be brought up for a short moment. If it already running it will be restarted. The reasons are as follows:

The service master key needs to be re-encrypted
Various other security settings are done

Because the WMI Provider performs various operations besides changing the service account, other account change methods like ‘sc’ or the standard service control panel are not supported. You may see that the service will not start in some cases.
Changing the password only tells the service that the password has changed. If you want to change the account’s password, use a command like NET USER. You need to run that first.

The following scripts allow you to change the service account + password, or only the password:

setaccount.vbs:

‘ Set the account and password

set svr = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement:SqlService.ServiceName='MSSQL$YUKON',SQLServiceType=1")

svr.SetServiceAccount ".\TestUser", "NewPassword!!"

setpwd.vbs:

‘ Set the password

set svr = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement:SqlService.ServiceName='MSSQL$YUKON',SQLServiceType=1")

svr.SetServiceAccountPassword "", "NewPassword2!!"

You need to change 'MSSQL$YUKON' to match your instance name (replace ‘YUKON’ with the instance name, or the entire string with MSSQLSERVER for the default instance). Of course the account and password need to be changed as well.

Try this on a test server first before running this on a production server.

I hope this is helpful,

Michiel

Add oil to fire: SMO and Monad -- a flammable combination

2006-01-03T22:35:00Z

Euan pointed me to an interesting article about the use of Monad together with SMO. Those who know me well, know I am somewhat of a scripting biggot and still install Cygwin on all of my boxes as I cannot live without bash, awk, perk, sed, expr and the various other UNIX tools that I have used over the years. I have been somewhat of a Monad sceptic, but once I saw what the vision behind it was I was quickly turned around. Now history will tell whether MSH is going to be the big hit I expect it to be. But there are several teams here already deeply investing in it and Exchange is a team that already showcases a deep use of Monad and event built their management UI on top of it. SQL Server DBA's have potentially a lot to gain from a good Monad implementation with a set of well implemented cmdlets*.

I'd love to know what type of cmdlets you'd like to see for SQL Server/SMO, bye the way... This is something we're looking into for the next release.

*If want to know what a cmdlet is, take a look at this deck or this MSDN article.

SQL Server 2005 -- it's a wrap; but wait -- it's just the beginning!

2005-10-28T18:54:00Z

I'm sure there are lots of coworkers blogging about this. We're done with SQL Server 2005 and it's a very interesting and good feeling. Having worked 5 year on the project, many long days, many weekends, having to fight many battles, and at the same time giving an incredible amount of attention to the security of our products with various massive security initiatives, shipping SQL Server 2000 SP3 (which was a big SP) and many other parallel work items made this a very high intensity effort of the entire team. One of my co-workers, Kirk, called it closure. I am not calling it that. It's more of a sense of letting a bright and smart prodigy go into the world, ready to do many great works. At the same time keeping an eye out and providing guidance where needed. It's a good feeling, not of closure, but of endless possibilities. The prodigy will be doing more than its masters can imagine it's capable of. The future of SQL Server 2005 is very bright...!

SMO Samples Galore

2005-10-25T19:47:00Z

With much of the SQL Server 2005 devcelopment behind us, I am starting to have some more time to post some SMO samples. Most of these are inspired on questions in the beta newsgroups or the SMO/DMO forum.

Additionally, SQL Server 2005 ships with a pretty wide variety of samples. In fact, there are more SMO samples than there are for any other product area, and we have been adding samples up till a a few weeks ago (from here it is pretty much locked down).

Let me know if you have any sample requests!

TechED: SMO session done! Now what?

2005-07-06T20:19:00Z

The SMO session was on today! Wow the Euro-crowd is tough! (which I heard uttered by various speakers after being shell shocked by unexpected scores) Jet lag and lack of sleep last few days made this session a hard nut to crack. (the proverb "I can do this in my sleep" is something that really needs to be taken of my list of valid proverbs by the way :-) Nevertheless lots of positive responses and I think the SMO message was well understood and also we're slowly but surely getting Project Mercury off the ground!

Once back in Redmond, I'm going to see that features are closed down and make sure we have a fully functional version ready to put out to you.

An additional benefit of being at TechED, is that you meet so many people (duh!). For example Betsy Aoki and I will have a chat to see how we can get source management implemented.

Thank you all for attending the session and supporting this effort, and it's good to see so many people are starting to sign themselves in as a member of http://microsoftsqltools.com (especially considering the site is yet somewhat underdeveloped).

Michiel

Project code named 'Mercury' (not "SQL Server Web Data Administrator")

2005-07-05T14:51:00Z

Granted, I was asking for this. When we were starting the development of the follow-up version of (here comes the culprit) the new SQL Server Web Data Administrator, Euan was still my boss and asked me to change the name. Long story short, Shipping SQL Server has priority, and I couldn't imagine myself sitting in a room full with lawyers in that stage pondering over names.

We thought up a code name at the time (I think Euan came up with this) which is ‘Mercury’.

So, if you see ‘SQL Server Web Data Administrator’ just read it as ‘Mercury’ (or “Project code named ‘Mercury’ ”) if the site has not been updated yet.

I was going to ask you for suggestions for a new name (this being a shared source project, so why not have a name figured out by the community as well)… I will figure that one out once I get back from TechED Europe.

So there, Euan! [:D] Project Mercury is here. And being demoed Wednesday at TechED.

Upcoming at TechED: Web Based SQL Server Management Tool (Shared Source!)

2005-07-04T20:00:00Z

Visual Studio .NET 2003 shipped a web based sample application for management of SQL Server, based on ASP.NET and SQL-DMO. We're currently working on an updated version of that tool. This is a quick list of the things that we change:

Revamped UI
Improved navigation
Improved architecture to make adding new components easier
Fully based on ASP.NET 2.0 and SMO
Works now with SQL Server 2000 and SQL Server 2005

We focussed on extensibility, so adding new features is a breeze. In fact, I created Assembly management from scratch within 2 hours on the plane to TechED, which I will demo coming Wednesday.

This tool is going to be made available as a Shared Source project. This means you can contribute to this project if you sign up to be a team member; or download the code and modify it for your own purposes (and push your changes back to the development team). We're not ready yet to allow you to sign up for team member, as we're still working on the '1.0 release', but if you're curious how it looks like, take a look at http://microsoftsqltools.com/. You can already sign up as member, and place feature requests as well. If you feel strong about certain features, I advice you to place feedback already. The earlier you are, the more chance it will make it as a feature!

If you're at TechED Europe, you can see a demo of this new tool:
"DAT310 Programming SQL Server Management Objects (SMO)" (Wed Jul 6 12:00 - 13:15)

SQL Server 2005 Easter Egg

2005-06-25T02:06:00Z

In an interview with Gert Drapers, SQLCMD was mentioned as SQL Server 2005's Easter Egg (Gert: "one of the biggest productivity gains for DBA’s in terms of automation"). I hope that I can pull this feature somewhat out of obscurity :-)

Watch the SQL Server 2005 Scripting Webcast which covers most of SQLCMD's features. And when you're done, check out some webcast resources I posted here.

You can find Gert at www.sqldev.net (and soon he will be roaming the VS buildings).

SQL Server: Table Partitioning in SQL Server 2005

2005-06-24T22:07:00Z

Table and Index partitioning is one if the new SQL Server 2005 features that willl improve life for the DBA and application developer quite a bit. It allows Indexes and Tables to be partitioned across multiple file groups. Partitioned tables and indexes, are fully manageable with SMO. Here is a quick sample to get you started, with not too much explanation as speaks for itself, mostly. Let me know if you have questions!

Server svr = new Server();

Database db = new Database(svr, "MyDatabase");
db.FileGroups.Add(new FileGroup(db, "PRIMARY"));
db.FileGroups.Add(new FileGroup(db, "SECONDARY"));
db.FileGroups[0].Files.Add(new DataFile(db.FileGroups[0], "datafile1", @"c:\db1.mdf"));
db.FileGroups[1].Files.Add(new DataFile(db.FileGroups[1], "datafile2", @"c:\db2.mdf"));
db.Create();

PartitionFunction pf = new PartitionFunction(db, "MyPartitionFunction");
pf.PartitionFunctionParameters.Add(new PartitionFunctionParameter(pf, DataType.Int));
pf.RangeType = RangeType.Left;
pf.RangeValues = new object[] { 5000 };
pf.Create();

PartitionScheme ps = new PartitionScheme(db, "MyPartitionScheme");
ps.PartitionFunction = "MyPartitionFunction";
ps.FileGroups.Add("PRIMARY");
ps.FileGroups.Add("SECONDARY");
ps.Create();

Table t = new Table(db, "MyTable");
t.Columns.Add(new Column(t, "c1", DataType.Int));
t.Columns.Add(new Column(t, "c2", DataType.VarChar(100)));
t.PartitionScheme = "MyPartitionScheme";
t.PartitionSchemeParameters.Add(new PartitionSchemeParameter(t, "c1"));
t.Create();

// Script out the objects (for educational purposes; optional)
SqlSmoObject[] objs = new SqlSmoObject[4];
objs[0] = db;
objs[1] = pf;
objs[2] = ps;
objs[3] = t;

for (int i = 0; i < 4; i++)
{
    Console.WriteLine("-- " + objs[i].ToString());
    foreach (string s in ((IScriptable)(objs[i])).Script())
    {
        Console.WriteLine(s);
    }
    Console.WriteLine("GO");
}

Scripting in Microsoft SQL Server 2005 -- Web Cast has finished

2005-06-21T22:03:00Z

I just finished the Scripting in Microsoft SQL Server 2005 webcast.

Thanks all for being online (and also thank you MVP's for answering questions!) I was a little nervous doing this (it's a big deck with lots of demos) but I think it went quite OK.

Let me know if you have any requests/comments!

Q&A, Samples, Demo Script has been posted (click here).

Thanks,

Michiel

Forums: http://forums.microsoft.com/msdn (for SQLCMD go to SQL Server Tools General)

Blogs of MVP's who helped answering questions:
http://spaces.msn.com/members/drsql (Louis Davidson)
http://users.drew.edu/skass (Steve Kass)
http://sqljunkies.com/WebLog/ktegels (Ken Tegels)
http://www.sqlskills.com/blogs/kimberly (Kimberly Tripp)

SQL Server: Capture Object changes with SMO Capture Mode

2005-06-01T00:51:00Z

In a previous article I have showed how a SMO object can be serialized into a Transact-SQL script, which allows you to recreate the object. What if you want to have the script that is emitted when you changed one or more properties of an object? In that case Capture Mode comes in handy. This switches the ServerConnection object in a state where it starts capturing all statements that SMO emits instead of sending these to the server. The following sample illustrates how it is used.

Server svr = new Server();

Database db = svr.Databases["MySmoTestDatabase"];

// First create new test database, if it does not exist
if (db == null)
{
    db = new Database(svr, "MySmoTestDatabase");
    db.Create();
}

// Set an option (we flip it to on or off depending orginal state)
db.DatabaseOptions.AutoClose = !db.DatabaseOptions.AutoClose;

// Switch SMO in capture mode
svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

// Now perform the operation
db.Alter();

// Don't forget to switch back
svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;

// Put database back in original state
db.Refresh();

// Print script
foreach (string s in svr.ConnectionContext.CapturedSql.Text)

{
    Console.WriteLine(s);
}

Note the Refresh() near the end. This is needed to bring back the Database object in sync with the server again. It’s not enough to execute the script (if you would do that) to bring it in sync with the server.

SQL Server: SMO Scripting Basics

2005-05-07T21:51:00Z

Let's first clarify what I mean by "scripting". It often happens during talks or when I explain SMO fundamentals that eyes gloss over, shortly after which the "what * do you mean by scripting" question pops up (replace the * with your favorite combination of adjective and noun to indicate bewilderment). For people who, unlike me, do not live their lives in SQL land, "scripting" means typically some kind of language script, like perl, VBScript, or others, that allows you, without the use of a development environment to author and execute code". Not so in this case. If I were to be precise, it means "serialize a SMO or DMO object into Transact-SQL". So now we have this out of the way, let's take a look at SMO Scripting.

SMO objects, when either just instantiated and not yet persisted, or just retrieved from the server, carry state. For example a StoredProcedure object has a name, a schema, a text body, and maybe some other optional properties. This state can be serialized into a Transact-SQL script that you may store, modify, or execute. As SQL Server’s primary language is Transact-SQL, it’s obvious that one of the tasks that the SMO object model has to perform very well is scripting. SMO objects can be scripted in 5 different ways:

By calling the Script() method on the object.
By instantiating a Scripter object, and pass in a reference of the object(s) to be scripted.
Advanced scripting: generating a script in 3 distinct phases (discovery, list, script)
By using the Transfer object.
Indirectly, by capturing the SQL output of the objects

I will discuss 1 and 2 and will dedicate some more posts on the other more advanced topics.

Basic Scripting

Let’s start with scripting an existing object, with no options specified (installing AdventureWorks will help running these samples):

Server svr = new Server();

foreach (string s in svr.Databases["AdventureWorks"].StoredProcedures["uspGetEmployeeManagers", "dbo"].Script())
{
      Console.WriteLine(s);
}

If you run this code, the script should be emitted to the console. So far pretty straightforward eh?

Scripting Options

If you want to do more than just scripting the object, there are scripting options that you can pass into the Script() method. There 2 ways to pass in Scripting options. The following 2 samples illustrate the different methods of passing in scripting options.

Server svr = new Server();

StoredProcedure sp = svr.Databases["AdventureWorks"].StoredProcedures["uspGetEmployeeManagers", "dbo"];

ScriptingOptions so = new ScriptingOptions();

so.IncludeHeaders = true;

so.SchemaQualify = true;

foreach (string s in sp.Script(so))

      Console.WriteLine(s);

In the next sample, you use the ScriptOption class. This class has various static members that each return an instance of a ScriptionOption class.

Server svr = new Server();

StoredProcedure sp = svr.Databases["AdventureWorks"].StoredProcedures["uspGetEmployeeManagers", "dbo"];

foreach (string s in sp.Script(ScriptOption.IncludeHeaders + ScriptOption.SchemaQualify))

      Console.WriteLine(s);

You can take advantage of the fact that the ScriptOption static members returns a ScriptingOption class (through implict conversion), as you can use it as an alternative to construct a Scriptingoption class:

ScriptingOptions so = ScriptOption.IncludeHeaders;

so.SchemaQualify = true;

Script modified objects

You can modify and script an object, without persisting its state. This sample shows how this can be accomplished:

Server svr = new Server();

StoredProcedure sp = svr.Databases["AdventureWorks"].StoredProcedures["uspGetEmployeeManagers", "dbo"];

sp.TextHeader = "-- Scripted at " + DateTime.Now.ToString() + "\n\n" + sp.TextHeader;

foreach (string s in sp.Script())
{
      Console.WriteLine(s);
}

Ensure that you either call the Refresh() method on the object once you are done, or persist the object by calling Alter(), as this temporary state may cause problems when this object is referenced again for other purposes.

Script non-existing objects

Alternative, you can instantiate a new object, and emit the script for it, without requiring it to exist on the server. In fact the below script will not even connect to the server; this happens all on your client.

Server svr = new Server();

Database db = new Database(svr, "MyDatabase");

db.DatabaseOptions.AutoClose = true;

foreach (string s in db.Script())
{

Console.WriteLine(s);
}

Using the Scripter object

If you call the Script() method on an object, what actually happens, is that in the background a Scripter() object is instantiated, which performs the various scripting operations. Whereas each object knows how to emit script, the Scripter object pulls it all together, and does special processing before or while generating the output. Let’s take a look how the Scripter object can be used to accomplish the same task as above (scripting a database).

Server svr = new Server();

Database db = new Database(svr, "MyDatabase");

db.DatabaseOptions.AutoClose = true;

Scripter scripter = new Scripter();

scripter.Server = svr;

scripter.Options.IncludeHeaders = true;

scripter.Options.SchemaQualify = true;

SqlSmoObject[] objs = new SqlSmoObject[1];

objs[0] = db;

scripter.Script(objs);

foreach (string s in scripter.Script(objs))

      Console.WriteLine(s);

The above samples I have touched on the basis of the scripting operations. In the next posts I will elaborate and show you more complex and elaborate ways to generate script. We made scripting extremely flexible, and hope that this first post helps you to get started with scripting.

Enjoy!

Sql Server: Everything you always wanted to know about SMO Connections

2005-05-03T00:40:00Z

There are several ways of controlling connections in SMO -- you can control pretty much any aspect of a SMO connection.

Pooled connections

Let's take a look at a typical way how you would obtain a connection using a simple C# sample:

Server svr = new Server();
Console.WriteLine(svr.Information.Product + " " + svr.Information.VersionString + " " + svr.Information.ProductLevel);

When you run this sample it will print the version, and edition, but you will also notice that the Connect() call is missing. Is there a need to call Connect()? The answer is both "yes", and "no". When you do not call Connect(), SMO will automatically obtain a connection from the connection pool when it needs one (in this case when accessing the svr.Information.VersionString/Edition properties), and after the operation has finished it will return that connection to the pool. This is a nice model as it stops you from needing to worry about when to call Disconnect(). SMO will do that bookkeeping for you and if your application has to exit because of a failure you do not need to release the connection -- SMO already has done that for you.

Explicitly connecting

You can override that behavior by explicitly connecting:

Server svr = new Server();
svr.ConnectionContext.Connect();
Console.WriteLine(svr.Information.Product + " " + svr.Information.VersionString + " " + svr.Information.ProductLevel);
svr.ConnectionContext.Disconnect();

What happens here is that SMO will obtain a connection from the connection pool on your request, and will not automatically release it to the pool, until you call Disconnect().

This is very much the same pattern that you would use in SQL-DMO. Also, if you are not able to connect to the server, you will get an exception during the Connect() call. However, there is one slight difference. The connection that is obtained comes from a connection pool. If you call Disconnect() and Connect() in succession then you will likely get the connection from the pool, so this allows you to efficiently manage your connections. Connection pooling means the connection is not immediately closed. It’s up to the connection pool to close it when the lifetime expires.

Non pooled connections

In some cases you want to not draw connections from a pool, but make sure that you get a non-pooled connection that you can hold on as long as your application is running. This can be helpful when running in single-user mode, or when you are paranoid about SMO to do the right thing with your connections and want to have complete control over the connections that are managed. This can be simply accomplished by setting the NonPooledConnection property to true.

Server svr = new Server();
svr.ConnectionContext.NonPooledConnection = true;
Console.WriteLine(svr.Information.Product + " " + svr.Information.VersionString + " " + svr.Information.ProductLevel);

The connection is then a dedicated connection that will not be returned to the pool. SMO will again, like with pooled connections, connect on demand, but in this case it will hold on to the connection until you specifically call Disconnect(). Connecting is in this case much more expensive, as there will not be a connection in the pool. This type of connection however guarantees that you have that connection for the lifetime of your application. For example, SQL Server Management Studio uses such a connection.

Sharing connections between processes

You can share connections between SMO Server instances, and pass and Create a new ServerConnection object based on an existing object, which is helpful if you need multiple connections with the same setting:

Server svr = new Server();

svr.ConnectionContext.NonPooledConnection = true; // comment this out to see what happens to the SPID's
Console.WriteLine(svr.ConnectionContext.ProcessID);
// do work...

// Pass the connection on to new Server instance -- the connection is reused

Server svr2 = new Server(svr.ConnectionContext);

Console.WriteLine(svr2.ConnectionContext.ProcessID);

// do work...

// The Copy() method will create a new object, and will use a new connection to the server

Server svr3 = new Server(svr2.ConnectionContext.Copy());

Console.WriteLine(svr3.ConnectionContext.ProcessID);
// do work...

If you run this example, you will see that the ProcessID (SPID) only differs for svr3, as it will not use the existing connection.

Comment out the indicated line, and see if you understand what's happening! (please post a response, don't be shy! ;-).

Other settings

There are various other settings of the ServerConnection object that can be manipulated to finetune SMO's behavior. Examples of this are MaxPoolSize, PooledConnectionLifeTime. Take a look at the object and you will find that all possible connection settings are available.

Some interesting facts

The default instance is assumed when you do not specify a server name
SMO uses Integrated security by default (the most secure option)
Once you have connected, you cannot change the properties of the ServerConnection object. You need to get a new ServerConnection object or use Copy() if you want a new connection with the same properties.

Important note

SMO allows you to pass on ServerConnections to different Server instances, but be very careful when doing so. SMO is making assumptions about various settings, so if you change settings, for example you grab the ServerConnection for other purposes than using it for SMO, and issue a SET NOEXEC ON, then SMO will fail to execute the statements. This will cause SMO to respond unpredictable, with application exceptions likely to occur. It is strongly discouraged to reuse connections for other uses than in SMO. Use pooling and obtain a dedicated connection for purposes other than using it with SMO.

SQL Server: Tuning your SMO Application for great performance -- PART 2

2005-05-02T11:21:00Z

In the previous post I provided you with a fundamental tool to minimize the amount of SQL statements emitted and therefore limit expensive network round-trips (and SQL statements) using the Server.SetDefaultInitFields() method. In this post I will provide you with some more detail about this method, alongside with a recommendation about its use.

Let's take a quick look at the optimized sample that I provided last article:

Server svr = new Server();

Database db = svr.Databases["AdventureWorks"];
svr.SetDefaultInitFields(typeof(Table), "CreateDate");
foreach (Table t in db.Tables)
{
Console.WriteLine(t.Schema + "." + t.Name + " " + t.CreateDate);
}

This optimization is particular to this piece of code, but the setting is global to the Server instance so every time you will retrieve a Table object later in your code, it will load the same fields as just specified. It's recommended to save the state of the previous SetDefaultInitFields setting so you can set it to the same value after you have retrieved your objects. The following sample shows you have to save the state, and then set if back to that state:

Server svr = new Server();

Database db = svr.Databases["AdventureWorks"];

StringCollection sc = svr.GetDefaultInitFields(typeof(Table));

svr.SetDefaultInitFields(typeof(Table), "CreateDate");
foreach (Table t in db.Tables)
{
Console.WriteLine(t.Schema + "." + t.Name + " " + t.CreateDate);
}
svr.SetDefaultInitFields(typeof(Table), sc);

The reason you want to be careful with this is that the SetDefaultInitFields setting is global to the Server object reference, and you are likely to hold on to the Server object instance reference (svr in the above example) to perform additional work with it. For example, if you were to call Refresh() on the Tables collection, any additional operation that would cause a table object to be instantiated in the collection will use the latest SetDefaultInitFields setting.

SetDefaultInitFields has a few more helpful overloads that I will describe here:

SetDefaultInitFields(System.Type typeObject, System.Boolean allFields)	Specifies all properties are fetched when the specified object is instantiated. When set to false, the fields will be reset to default.
SetDefaultInitFields(System.Boolean allFields)	Specifies all properties are fetched when any object is instantiated. When set to false, the fields will be reset to default..

Recommendation: make sure your application is aware of this global Server state, and either specifically set the fields or reset to the previous value. If you do not take care about this, you may see sudden application degradation (or your customers may start to notice), and as it will be very hard to test all code paths you cannot know beforehand how your application is going to behave in the future if· this is not carefully managed. Also, if you pass the Server reference on to another library that component may change the field settings and cause 'mysterious' performance degradation that may be hard to debug.

Starting with SQL Server 2005 WMI with help of Scriptomatic

2005-04-28T05:40:00Z

SQL Server 2005 introduces a new WMI Provider that allows configuration of Services, Client Network Protocols, and Server Protocols. This is a great improvement for DBA that are managing many SQL Servers, as WMI allows you to query and modify SQL Server configuration settings remotely.

Now, everyone who has worked with WMI knows it's not easy to write a WMI program. You need to know the namespace, class names, etc. Everything that helps developers so well in VS (IntelliSense, object browsers) is not available to the scripters that are working on a bare system. I typically have a couple of WMI templates on my pen drive and start from there.

I recently ran into Scriptomatic. It's a great tool to give you good starting points for creating WMI scripts. It provides you with a dropdown of namespaces available on the system, and classes within the selected namespace. It even allows creation of Perl scripts...!

Download it here and then select the root\Microsoft\SqlServer\ComputerManagement namespace; select the SqlService class and voila, a ready to run script is displayed in the window. Quite nice! Here is an VbScript example:

On Error Resume Next

Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20

arrComputers = Array("C3PO")
For Each strComputer In arrComputers
   WScript.Echo
   WScript.Echo "=========================================="
   WScript.Echo "Computer: " & strComputer
   WScript.Echo "=========================================="

   Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")
   Set colItems = objWMIService.ExecQuery("SELECT * FROM SqlService", "WQL", _
                                          wbemFlagReturnImmediately + wbemFlagForwardOnly)

   For Each objItem In colItems
      WScript.Echo "AcceptPause: " & objItem.AcceptPause
      WScript.Echo "AcceptStop: " & objItem.AcceptStop
      strAdvancedProperties = Join(objItem.AdvancedProperties, ",")
         WScript.Echo "AdvancedProperties: " & strAdvancedProperties
      WScript.Echo "BinaryPath: " & objItem.BinaryPath
      WScript.Echo "Clustered: " & objItem.Clustered
      strDependencies = Join(objItem.Dependencies, ",")
         WScript.Echo "Dependencies: " & strDependencies
      WScript.Echo "Description: " & objItem.Description
      WScript.Echo "DisplayName: " & objItem.DisplayName
      WScript.Echo "ErrorControl: " & objItem.ErrorControl
      WScript.Echo "ExitCode: " & objItem.ExitCode
      WScript.Echo "HostName: " & objItem.HostName
      WScript.Echo "ProcessId: " & objItem.ProcessId
      WScript.Echo "ServiceName: " & objItem.ServiceName
      WScript.Echo "SQLServiceType: " & objItem.SQLServiceType
      WScript.Echo "StartMode: " & objItem.StartMode
      WScript.Echo "StartName: " & objItem.StartName
      WScript.Echo "State: " & objItem.State
      WScript.Echo
   Next
Next

SQL Server: Tuning your SMO Application for great performance - PART 1 (featured at TechED)

2005-04-22T23:16:00Z

I'm ramping up for TechED 2005 (Both USA and Europe) and will be giving a couple of talks on SMO. This is a repost of an article from SqlJunkies, as I am still moving these blog entries to MSDN. Ping me if you are visiting TechED and are interested in certain SMO topics.

Unlike SQL-DMO, SQL Server Management Objects (SMO) gives you a fairly fine level of control how data is retrieved from the server.

When using default behavior, an SMO object transitions into the following states:

1. Partially instantiated -- some properties are available (such as Name, Schema)
2. Fully instantiated -- all low cost properties are retrieved in bulk when you retrieve any of these
3. Expensive properties -- are fetched when needed, one at the time (such as database space)

The rationale behind this is to allow the object model to scale when a high number of objects need to be retrieved, for example when populating a collection of 10,000 tables. In contrast, SQL-DMO always fetches all properties when populating a collection. This can be compared with doing a 'select * from xxx', when you only want to know something about name and the creation date of the object. SMO fetches only the minimal set of properties that are required to populate the collection (i.e. for Table, Name and Schema are needed to uniquely identify the object).

Now here comes the catch: if your application does fetch extra properties, besides the minimally required ones, it will submit a query to retrieve these extra properties. This can cause your application to become extremely 'chatty', and will likely cause it to perform not as good (or just bluntly bad). This is especially noticed when doing 'foreach' enumerations, such as shown in the example below:

Server svr = new Server();

Database db = svr.Databases["AdventureWorks"];
foreach (Table t in db.Tables)
{
Console.WriteLine(t.Schema + "." + t.Name + " " + t.CreateDate);
}

You can inspect the result by switching SQL Profiler on and looking at the number of batches that are emitted to SQL Server.

When running above sample you will find about 155 rows in the SQL Profiler. The application is certainly chattier than it should be. This app's performance is especially down the drain on networks with a higher latency than a typical LAN, but even on a LAN it does not perform very well.

Enter Server.SetDefaultInitFields(). This call allows you to set the fields that are retrieved when the object gets initialized. This would add one extra line to the above sample:

Server svr = new Server();

Database db = svr.Databases["AdventureWorks"];
svr.SetDefaultInitFields(typeof(Table), "CreateDate");
foreach (Table t in db.Tables)
{
Console.WriteLine(t.Schema + "." + t.Name + " " + t.CreateDate);
}

Note that you do not have to indicate any of fields that are retrieved by default (Name, Schema).

After running the above sample, you will find only 11 rows in SQL Profiler, and you will also notice that the first sample you could see it fill the screen line by line (I'm running this on a 1.8Ghz P4 Toshiba notebook with 1Gb of memory) and the optimized sample flashes by and disappears in a split second (could have added some timing to the sample, but for clarity I keeping it as short as possible.

The bottom line is that you need to understand your application behavior and tuning will make a big difference. With more options to tune SMO, the responsibility to tune your application has shifted to you, as SMO cannot guess what your application is going to request. "With great power comes great responsibility" :-)

Next post will be on some of the more advanced tuning options SMO has to offer. If you have a special request about what you like me post on in the SMO space (or SQL-DMO, SQLCMD, OSQL, SQL WMI Provider, SQL Computer Manager, or XP's for that matter), then let me know.

Michiel Wories (a PM on the SQL Server Team)
---
See also: http://staff.develop.com/bobb/weblog/PermaLink.aspx?guid=0c070aff-a30f-4669-b0ae-ef7a2cde81c6
---
This posting is provided "AS IS" with no warranties, and confers no rights.

Moving over to a new blog... And something about me

2004-12-22T22:51:00Z

Moving over my blog from sqljunkies. At sqljunkies I had made a start with some background articles about SQL Server Management Objects and I have a whole bunch of articles lined up for this blog.

A quick introduction --

I moved from The Netherlands to the U.S. and started working for Microsoft about 3 1/2 years ago. Before this I had worked as Principal Architect for a large ERP company, responsible for the architecture of Development Tools and Runtime. In '99 I attended an (mostly) academic Lab organized by the .NET Framework/CLR team on a very early versions of the C#, the CLR and the .NET Framework in order to see whether we could move our language and application over to the CLR. We had developed our own Language/Runtime in the late 80's which looked very similar to .NET Runtime, it being slightly different as it runs always interpreted code -- JIT compilation was out of the question if you have to support more than 10000 applications on 30+ platforms -- you will be surprised how unreliable certain compilers are if throw enough code at them and this would blow the test matrix out of the water, not thinking about the possible support nightmare this would be as well.

At the Lab I was intrigued by the vision that Microsoft had by developing a language and platform independent runtime (which we had reaped the benefits of for already 10 years) and in 2000 I decided it was time to move to a new challenge and Microsoft had moved to the top of my list before ABN-AMRO which offered be a job as Architect, and another offer to become CTO for an Internet startup (bricks and clicks type). The choice for Microsoft was easily made, and after being grilled by the interviewers, I received an offer to go work with the SQL Server team (to be frank I thought I flunked the interview which I've heard is not an uncommon reaction after being hammered to bits for a full day). It's been a dream come true and a fun ride. SQL Server is an amazing product built by an amazing set of people and I'm pretty happy that I can be a part of such a major release as Yukon.... My team is SQL Server Tools -- see also Euan Garden's blog.

Well, that all for now. Wishing you all happy holidays and a good 2005 (which is going to be an exciting year for the SQL Server team as well :-)

If anyone wants to say hi, please do.