SQL Server 2008 R2 provides an easy way to author, deploy, and manage data-tier objects as a single entity through data-tier applications. A data-tier application (DAC) is an entity that contains all of the database and instance objects used by an application.

You can create DACs using two methods. First, you can use the Extract Data-tier Application wizard to extract database and instance objects from a database in SQL Server Management Studio (SSMS). This wizard will take you through a couple simple steps and create the DAC for you.

After you have extracted the DAC, you can author it in Visual Studio 2010 or you can deploy it to an instance of SQL Server using the Deploy Data-tier Application wizard. The Deploy Data-tier Application wizard will guide you through several steps to deploy the DAC to the instance you select.

You can create a DAC in Visual Studio 2010 or author a DAC you extracted by starting a Data-Tier Application project. After a DAC has been imported, you can add several elements to the DAC project: DAC properties, definitions of all the database objects used by the application, definitions of the instance-level objects, a server selection policy that defines the pre-requisite conditions an instance of the Database Engine should have to host the DAC, and files and scripts that can be embedded in the DAC.

There are two ways to view DACs: through Object Explorer and through Utility Explorer. Object Explorer will let you see a list of DACs on the instance. Utility Explorer will give you a more detailed view of the DAC, and allows you to view utilization information just like managed instances.

Have you ever wanted to be able to see a centralized view of Microsoft SQL Server instances and database applications? SQL Server 2008 R2 introduces a new way to view information about your SQL Server instances.  At a glance, you can see a snapshot of your instances including utilization information using the SQL Server Utility. This information is available in a new view called the Utility Explorer that you access through SQL Server Management Studio.

When you open the Utility Explorer, you are presented with the screen shown below. In this screen, you are given the steps you need to do in order to use SQL Server Utility. Each of these topics links to steps to complete the activity.

In order to use SQL Server Utility, you must create a Utility Control Point. The other steps are optional. A Utility Control Point, or UCP, is configured on a SQL Server instance. After configuring a UCP, you can enroll other SQL Server instances on the same UCP. The Utility Explorer displays information about each instance such as CPU utilization, file space utilization and volume space utilization. The display will look similar to the image below. The columns displayed are configurable by right-clicking the title bar and selecting the information you wish to view.

Notice the red arrow under volume space. This is indicating the instance is overutilized. If the instance were underutilized, there would be a green down arrow. The green checkmarks indicate the instance is well utilized. You can configure the values that will alert you when the instance is overutilized or underutilized. This allows you to customize these values best for your organization.

In addition to view information about SQL Server instances, you can view information about data-tier applications. My next post will discuss data-tier applications.

In my last post I looked at some basic variable types. Now I will examine arrays.
An array is a data structure for storing a collection of data elements of the same type. Basically this means that you can have a single variable that stores multiple values.
Assign multiple values to a variable to create and initialize an array. The values stored in the array are delimited with a comma and separated from the variable name by the assignment operator (=).
For example, to create an array named $a that contains the seven numeric (int) values of 22, 5, 10, 8, 12, 9, and 80, type:
$a = 20,4,11,3,1,9,60

When no data type is specified, Windows PowerShell creates each array as an object array (type: object[]). You can create a strongly typed array, that is, an array that can contain only values of a particular type, the same way you create a strongly typed variable. You specify a type, such as string[], long[], or int32[]. Precede the variable name with an array type enclosed in brackets to cast an array.
[int32[]]$ia = 1500,2230,3350,4000

Simply type the array name to display all the elements in the array. For example:
$ia

You can refer to the elements in an array by using an index, beginning at position 0. Enclose the index number in brackets. For example, to display the third element in the $a array, type:
$ia[2]

You can create arrays that are cast to any supported type in the Microsoft .NET Framework. For example, the objects that Get-Process retrieves to represent processes are of the System.Diagnostics.Process type. Enter the following command to create a strongly typed array of process objects.
[Diagnostics.Process[]]$gp = Get-Process

Then, display the contents of $gp to see a list of running processes.
$gp

In my next post we will review operators and expressions.

In my last post I began looking at variables. Now I will go over types and how they function.
Windows PowerShell supports a number of different types such as strings, integers, arrays, etc. You can explicitly specify a type or allow Windows PowerShell to do it dynamically, which is sometimes referred to as automatic type conversion. Type conversions are used when an object of one type is assigned a value that requires another type (such as adding a string to a number). This conversion happens automatically as long as the type was not specified manually, and it is not destructive to the original object.
For example we can assign a new value to the $Loc variable which currently has the System.Management.Automation.PathInfo type, and then use the GetType method to show its new type.
$Loc = "Test"
$Loc.GetType().FullName

Then, assign it a numeric value and check the type again.
$Loc = 3
$Loc.GetType().FullName

$Loc was made an Int32 because the value wasn’t enclosed in quotes and because the value was composed solely of digits. Had it been in quotes, it would have been interpreted as a System.String.
In both cases, Windows PowerShell determined the data type that was the most appropriate for the value of the variable. This should work for most variables but there may be situations where you want the variable type to remain as it is. Suppose you are reading values out of a file and you always want the values to be treated as strings. Some of the values, however, might contain only digits, raising the possibility that Windows PowerShell would treat them as Int32 or another numeric type, which may create problems for your script. If Windows PowerShell does not recognize the value as a string, then all the methods of the System.String class are not available (and your script might rely on one of these unavailable methods).
Manually assigning a type is simple and is done when you create it. Assigning a string to a variable essentially forces the variable to be of the System.String class. Assigning a number to a variable, on the other hand, usually results in the variable becoming an Integer (or, more specifically, an Int32, which can store a specific range of values). For example, we can create a new variable and define it as a string.
[String]$var = 5
$Var.GetType().FullName

Normally, $Var would have been an Int32, but because we defined it as a String, it makes the numeric value we assigned to it a string.
Forcibly declaring variables does have repercussions, though they are not necessarily bad. In the next example, a new variable is created, defined as an integer, and assigned a numeric value. Then, it is assigned a string value.
[Int]$Num = 4
$Num = "test"

As shown above, when we tried to assign a string value to it, an error message was displayed. Because $Num was defined as a Int32, Windows PowerShell expected to convert the string “test” into an integer value. It was unable to do this, nor was it able to change the type of $Num to String.
In my next post we continue to look at types.

In my last post I reviewed the Out-Gridview cmdlet. Now I will look at variables.
Windows PowerShell lets you create variables – essentially named objects – to preserve output for later use. The Windows PowerShell variables are actually mapped to underlying classes in the Microsoft® .NET Framework. In the Framework, variables are objects, meaning they can store data and also manipulate it in many ways.
Variables are always specified with the initial character $ and can include a mix of letters, numbers, symbols, or even spaces. However, if spaces are used, the variable needs to be enclosed in braces, such as: ${My Variable} = “Hello”). Variables are created by typing a valid variable name or assigning a value to it. A variable name should help you remember what it contains, using a simple, straight-forward name.
$Loc = Get-Location

Output is not displayed when this command is entered, because the output is sent to $Loc. In Windows PowerShell, displayed output is a side effect of the fact that data which is not otherwise directed always gets sent to the screen. Typing $Loc shows its value, which in this case is the current location.
$Loc

Get-Member is used to display information about the contents of variables. Get-Member enumerates the properties and methods of that object type. Piping $Loc to Get-Member shows that it is a PathInfo object, just like the output from Get-Location.
$Loc | Get-Member

There is also a default variable that can be used for certain situations. The $_ acts a placeholder for the current object. This can be used to access the properties or methods of the current object in a command. For example we can use $_ to help filter the output of the Get-Service cmdlet, displaying only services that have a status of Running.
Get-Service | where {$_.status -eq "Running"}

In my next post we continue to look at variables and types.