Setting SQL Alias with PowerShell

PROBLEM: The SQL Servers instances I manage have long cryptic names that are almost impossible to memorize and too many keystrokes to type; besides, I rather use my memory for other things. How can I set up aliases for all the SQL instances I manage in a quick an easy manner?

SOLUTION: The configuration of SQL aliases is saved in the registry under two locations. This first one is for 32bit clients HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\ConnectTo
And this second one is for 64bit clients HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

PowerShell to the rescue! I wrote the Set-SQLAlias function that will take the SQL instance name, port number and alias I want to create. It will use this information to add an entry on the registry or modify an existing alias. I have added this function to my profile so it is always available. THIS FUNCTION MUST BE EXECUTED IN A POWERSHELL SESSION RUNNING UNDER ADMINSTRATOR PRIVILEGES”

Function Set-SqlAlias {
	[Cmdletbinding()] 
	param ( [parameter(Mandatory=$true)] [string] $SqlInstanceName,
            [parameter(Mandatory=$true)] [string] $Alias,
			[parameter(Mandatory=$true)] [string] $Port,
			[parameter(Mandatory=$false)] $OverWrite = 0
          ) 

	BEGIN {
		$x64 = "HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"

		if (!(Test-Path "$x64")) {
			New-Item -Path "$x64" -Name "ConnectTo" 
		}
	}

	PROCESS {
		if ($OverWrite -ne 0) {
			Get-Item -path "$x64"|Remove-ItemProperty -Name "$Alias"|Out-Null 
		} 
		
		if (!(Get-Item -Path "$x64").GetValue("$Alias")) {
			New-ItemProperty -Path "$x64" -Name "$Alias" `
-PropertyType String `
-Value $("DBMSSOCN,{0},{1}" -f "$SqlInstanceName", $Port) `
|Out-Null
		} 
	} 
}

Thanks to my friend Rodney Starcher for helping me out with the PowerShell script.

Cómo Cambiar el Propietario de Una Base de Datos

PROBLEMA: Tengo base de datos donde el propietario no es “sa”, cómo puedo cambiar el propietario de varias bases de datos a “sa” de una forma dinámica.

SOLUCION: Yo utilizo el cmdlet Get-SqlDatabase del módulo SqlServer para capturar las bases de datos de una instancia de SQL Server. Entonces hago una búsqueda en el objeto generado por el cmdlet Get-SqlDatabase para encontrar las bases de datos donde la propiedad “.owner” no es “sa”. De ahi, ejecuto el método “.SetOwner” y el “.Alter” por cada una de las bases de datos encontradas para asignar el nuevo propietario “sa”.

Get-SqlDatabase -ServerInstance 'TUIRA' `
| Where {$_.Owner -ne "sa"} `
| foreach {$_.SetOwner("sa"); $_.Alter()}

En Inglés

Change Database Ownership

PROBLEM: Database ownership is set to the user who created or restored the database and I want to change it to “SA”.

SOLUTION: I use the Get-SqlDatabase cmdlet from SqlServer module to capture the list of databases from the SQL instance. I then pipe and search for databases where the “.owner” property is not “sa”. Then pipe the database object to change the “.owner” property to “sa” by invoking the methods “.SetOwner” and “.Alter”.

Get-SqlDatabase -ServerInstance "TUIRA" `
| WHERE {$_.owner -ne 'sa'} `
| foreach {$_.SetOwner('sa'); $_.Alter()}

In Spanish

How to Find a Database User in Your SQL Environment

PROBLEM: Sal Young is a member of your DBA team and today is his last day at work. You need to find out if his login is mapped to any database throughout your MS SQL environment.

SOLUTION: I used Central Management Server, PowerShell, and SQLPSX. I queried my Central Management Server to get the name of all SQL servers in my environment to then proceed and loop through my list of servers and databases to look for the pattern of the user name or login, in this case “syoung”.

Notice on line 2 how the value assigned to the variable is surrounded by asterisks. This will support the wildcard search on line 18 for any database account with extra characters before or after the string value.

On line 5 and 6 is the query executed against my Central Management Server to get the list of registered SQL Servers in my environment.

CLEAR
[string] $sDBUser = "*young*"

$sSQL = @"
SELECT DISTINCT [server_name]
  FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
 WHERE [server_name] NOT LIKE '%.%'
"@

$aSQLServers = Invoke-Sqlcmd -ServerInstance "TUIRA" `
               -Database "msdb" `
               -Query $sSQL

foreach ($sSQLServer in $aSQLServers) {
    $sSQLServer = $sSQLServer.server_name
	  Get-SqlDatabase -sqlserver "$sSQLServer"| `
        % {Get-SqlUser $_}| `
        WHERE {$_.Name -like "$sDBUser"}| `
        SELECT Server, dbname, name
}

How To Change the SQL Server Agent Job Owner

PROBLEM: I need to change the owner of several SQL Server Agent Jobs. Is there an easy way to do it?

SOLUTION: Use PowerShell and SQLPSX.

I use the Get-AgentJob function from SQL Server PowerShell Extension (SQLPSX) to find the jobs I need to modify. I need to change ownership of all SQL Server Agent jobs from the login “THERIVERsyoung” to “sa” on the server “TUIRA”. This first script will display all jobs where the owner matches the login “THERIVERsyoung”.

CLEAR 

$oSQLAgentJobs = Get-AgentJob -jobserver "TUIRA"

foreach ($job in $oSQLAgentJobs) {
    if ($job.OwnerLoginName -eq "THERIVERsyoung") {
        $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
    }
}

I will proceed to add the code to change the ownership for all the jobs that meet the criteria. I want to recognize Sean McCown (MidnightDBA) for providing a clear explanation on how to change the sql server job owner. I strongly recommend you watch the entire presentation on this link for a good understanding of the Alter and Refresh methods of the JobServer object. On line number 8 I change the job ownership to “sa”. On number 9 I call the Alter method to update the property I changed on line 8 and on line 10 the Refresh method is called to complete the change.

CLEAR 

$oSQLAgentJobs = Get-AgentJob -jobserver "TUIRA"

foreach ($job in $oSQLAgentJobs) {
    if ($job.OwnerLoginName -eq "THERIVERsyoung") {
        $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
        $job.set_OwnerLoginName("sa")
        $job.Alter()
        $job.Refresh()
        $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
    }
}

I decided to add an extra few lines of code to search my entire SQL Server environment for jobs owned by “THERIVERsyoung”. I leveraged the use of Central Management Server to retrieve the name of all SQL Servers by running the query on lines 4 & 5. Notice that on line 8 I use the Invoke-Sqlcmd SQL comandlet to execute the query stored in the variable $sSQL and that I run it against DBACONSOLE which is my Central Management Server.

CLEAR

$sSQL = @"
SELECT [server_name]
  FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
"@

$aSQLServers = Invoke-Sqlcmd -ServerInstance "DBACONSOLE" `
               -Database "msdb" `
               -Query $sSQL

foreach ($sSQLServer in $aSQLServers) {
    $oSQLAgentJobs = $null

    $sSQLServerName = $sSQLServer.server_name
    $oSQLAgentJobs = Get-AgentJob -jobserver "$sSQLServerName"    

    foreach ($job in $oSQLAgentJobs) {
        if ($job.OwnerLoginName -eq "THERIVERsyoung") {
            $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
            $job.set_OwnerLoginName("sa")
            $job.Alter()
            $job.Refresh()
            $job.Parent.Name +" - "+ $job.Name +" - "+ $job.OwnerLoginName
        }
    }
}

You could easily turn this solution into a function with parameters for the current login and the new login. The preceding examples show the approach I took when my boss asked me to look for and change all SQL Server Agent jobs I was set as the owner.

How to Copy All Tables Definition with PowerShell

PROBLEM: You need to copy the table definition for all tables in a database between two SQL servers.

SOLUTION: One of the requirements is to use PowerShell and I will use it in combination with SQLPSX. Yes, I will use SQLPSX again and you probably have noticed I use it in all my PowerShell scripts that access MS SQL Server –I’m a fan, what else can I say.

There are a few highlighted lines of code I want to mention because I had a bit of trouble while scripting tables with dependencies on user defined data types and partitioned tables. Line 5 and 6 are string variables assigments for the target server and database. On line 13, I added the scripting option “NoFileGroup” because the source database was set up using multiple filegroups and the script failed when these file groups were not available on the target server; the target server in this example will have a single file group. On line 15, I added the “WithDependencies” which helped with the creation of partitioned table functions and schemas. And on line 16, I used the “IncludeIfNotExists” in order to avoid errors if the database objects already exists.

CLEAR 

$sSRCServer = "TUIRADEVELOPMENT" 
$sSRCDatabase = "AdventureWorks" 
$sTRGServer = "TUIRA" 
$sTRGDatabase = "AdventureWorks" 

$oSO = New-SqlScriptingOptions 
$oSO.Permissions = $false 
$oSO.ClusteredIndexes = $true 
$oSO.Indexes = $true 
$oSO.Triggers = $true 
$oSO.NoFileGroup = $true
$oSO.NonClusteredIndexes = $true 
$oSO.WithDependencies = $true
$oSO.IncludeIfNotExists = $true

$oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable 

foreach ($oTable in $oTables){  
    $oT = $oTable.Name  
    $oS = $oTable.Schema  
    $DDL = Get-SqlScripter $oTable -scriptingOptions $oSO 
    invoke-sqlcmd -ServerInstance "$sTRGServer" -Database "$sTRGDatabase" `
          -Query "$DDL" 
}

This solution can easily be converted to a function with optional parameters that map to scripting options. The target variables for server and/or database could hold arrays for copying the tables definition to multiple servers. A form could also be developed using WPF to make it user friendly. What do you think?

Cómo Copiar el Esquema de Tablas Utilizando PowerShell

PROBLEMA: Necesitas copiar el esquema de tablas de un servidor Microsoft SQL a otro servidor SQL.

SOLUCION: Para resolver este problema utilizo PowerShell y el SQLPSX. Si sigues este blog, podrás notar que utilizo la extensión SQLPSX de SQL para PowerShell en todos mis PowerShell scripts de base de datos.

Hay algunas líneas en el código adjunto las cuales hago resaltar porque al escribir el script tuve ciertas dificultades ya que existen dependencias entre las tablas y tipos definidos por el usuario además de la existencia de tablas con particiones. La línea 5 y 6 son para asignar el nombre del servidor y base de datos de destino a variables. En la línea 13 agrego la opción de scripting “NoFileGroup” ya que la base de datos de origen está configurada con múltiples grupos de archivos (filegroups) y mi script termino en error cuando estos filegroups no se encontraron en la base de datos de destino. En este ejemplo, la base de datos de destino cuenta con un solo filegroup. En la línea 15 añado la opción de scripting “WithDependencies” para ayudar con la creación de funciones y esquemas de particiones de tablas. Finalmente en la línea 16, incluyo la opción de scripting “IncludeIfNotExists” para verificar que los objetos de base de datos a crear no existan en la base de datos de destino y así evitar los errores de creación.

CLEAR

$sSRCServer = "TUIRADEVELOPMENT"
$sSRCDatabase = "AdventureWorks"
$sTRGServer = "TUIRA"
$sTRGDatabase = "AdventureWorks"

$oSO = New-SqlScriptingOptions
$oSO.Permissions = $false
$oSO.ClusteredIndexes = $true
$oSO.Indexes = $true
$oSO.Triggers = $true
$oSO.NoFileGroup = $true
$oSO.NonClusteredIndexes = $true
$oSO.WithDependencies = $true
$oSO.IncludeIfNotExists = $true

$oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Name
    $oS = $oTable.Schema
    $DDL = Get-SqlScripter $oTable -scriptingOptions $oSO
    invoke-sqlcmd -ServerInstance "$sTRGServer" -Database "$sTRGDatabase" `
         -Query "$DDL"
}

Esta solución puede ser convertida a una función de PowerShell y utilizar parámetros para vincular las opciones de scripting. Las variables de servidor y base de datos de destino pueden contener una matriz (array) con nombres de servidores y base de datos para así copiar el esquema de tablas a múltiples servidores. Un formulario web puede ser creado utilizando WPF para facilitar el uso de esta solución. ¿Qué te parece esta solución?

PowerShell – The Automator (T-SQL Tuesday #15)

I read about T-SQL Tuesday #15 this evening while riding the train on my way home; I need to get in the scoop of these events when they first come out.

How to Review SQL Logs with PoweShell describes one of the many scripts I created in PoweShell to automate my daily tasks. I have also created the CodePlex project iSQLPS which is a collection of scripts for a SQL Server inventory system -it is a work in progress. PowerShell is by far, the greatest tool created for administrators of Microsoft systems and I’m surprise on how many administrators still resist the force or are unwilling to see the light!

Cómo Cambiar la Extensión de Todos los Archivos en un Directorio

PROBLEMA: Tienes un directorio con muchos archivos y deseas cambiarles la extensión sin tener que hacerlo uno a uno.

SOLUCION: Puedes lograr tu objetivo utilizando PowerShell y hacerlo con 3 líneas de código.

CLEAR CD [PATH TO FILE LOCATION] Get-ChildItem *.[FILE EXTENSION]|Rename-Item –NewName {$_.Name –replace "[FROM VALUE]","[TO VALUE]"}

Siempre me gusta empezar mis scripts con la consola de PowerShell limpia y por lo tanto empiezo con el comando CLEAR. Continúo con el comando CD para cambiar al directorio que contiene los archivos con los que voy a trabajar. De allí, ejecuto mi línea de comdlets para hacer el cambio de extensión de todos los archivos.

Aquí está un ejemplo de cómo cambiar la extensión de todos los archivos con extensión .TAB a extensión .SQL

CLEAR
CD C:DatabaseScripts
Get-ChildItem *.TAB|Rename-Item -NewName { `
    $_.Name -replace "TAB","SQL"
}

En Inglés

How to Review SQL Logs with PowerShell

PROBLEM: You need an easy way to review the MS SQL Server logs of all the servers you manage.

SOLUTION: Use the Get-SqlErrorLog function from the SQL Server PowerShell Extensions (SQLPSX).

I will show you some of the things you can achieve with the use of the Get-SqlErrorLog function and a bit of creativity. We will start by retrieving the content of the current error log of our server “TUIRA” by executing:

Get-SQLErrorLog –SQLServer "TUIRA"

In the next example we want to retrieve the content of log file number 4 by specifying the optional parameter lognumber of the Get-SqlErrorLog function.

Get-SQLErrorLog –SQLServer "TUIRA" –lognumber 3

Now, getting the entire error log of a SQL server is not very useful, specially from a busy server. When I’m looking into the error logs I do so with a purpose, most of the time is because I’m looking for errors or something specific. In this following example, we’ll apply a filter and display entries where the word “error” exist in the message.

[string] $sSQLServerName = "TUIRA"
Get-SQLErrorLog –SQLServer $sSQLServerName|WHERE {$_.Text `
    –match "error"}|Format-Table -AutoSize

If you get in the habit of checking these logs on a daily basis, you can filter your result a bit further by just retrieving the logs starting from the previous day. Here is an example of how to accomplish that.

[string] $sSQLServerName = "TUIRA"
$a = Get-Date
$d = $a.day – 1
$m = $a.month
$y = $a.year
$dDate = "$m/$d/$y"

Get-SQLErrorLog –SQLServer $sSQLServerName|WHERE {$_.Text `
    –match "error" -and $_.LogDate –ge $dDate}|Format-Table -AutoSize

And our last example includes an array with a list of SQL server names. We’ll loop through this array and retrieve error entries from their log files.

$a = Get-Date
$d = $a.day – 1
$m = $a.month
$y = $a.year
$dDate = "$m/$d/$y"

$aSQLServers = "SQLServer1","SQLServer2","SQLServer3","SQLServer4"

foreach ($sSQLServer in $aSQLServers) {
    $sSQLServer
    Get-SQLErrorLog –SQLServer $sSQLServer|WHERE {$_.Text `
        -match "error" -and $_.LogDate –ge $dDate}|Format-Table `
        –AutoSize}

As a DBA, part of my morning routine is to review error logs from the SQL servers I manage. In my production environment, I have a job that retrieves the list of production servers from an inventory database (iSQLPS) and generate an HTML report for each server. So by the time I get to my desk with my first cup of java for the day, I have the reports waiting.
Do not forget to download the SQL Server PowerShell Extensions from Codeplex.com

Category: PowerShell