Enumerate, add, update and remove SQL Server aliases by using PowerShell


I’ve created a PowerShell script for adding, updating, enumerating und removing SQL Server aliases. – You can use the script to create/remove/enumerate both 32bit und 64bit aliases in one step or only 32bit or only 64bit aliases.

You can change this script into a PowerShell module. Just see the comment below inside the script.

I’ve tested the script on Windows Server 2008 R2 with SQL Server 2008 R2 on it. – Please post your comments how it works on other systems.

Here is the script:

#region Author: Ingo Karstein / Blog: ikarstein.wordpress.com
<#
Script written by Ingo Karstein.

**Do not remove this comment**

Please see my blog:
  https://ikarstein.wordpress.com

Licence: GPLv2
    Ingo Karstein's SharePoint 2010 Custom Ribbon Demo
    Copyright (C) 2011  Ingo Karstein, ikarstein@hotmail.com

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License along
    with this program; if not, write to the Free Software Foundation, Inc.,
    51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

#>
#endregion

#region Functions

function connectint($cnnOpt) {
    $s = $null
    try { 
        $s = New-Object System.Management.ManagementScope("\\.\root\Microsoft\SqlServer\ComputerManagement10", $cnnOpt)
    } catch {
        $s = New-Object System.Management.ManagementScope("\\.\root\Microsoft\SqlServer\ComputerManagement", $cnnOpt)
    }
    $s.connect()

    $mp = New-Object system.Management.ManagementPath("SqlServerAlias")
    $ca = New-Object system.Management.ManagementClass ($s, $mp, $null )

    $ca.get()
    
    return $ca
}

function remove-sqlaliasint($name, $cnnOpt)
{
    $ca = connectint -cnnopt $cnnOpt
    foreach($mo in $ca.getInstances()){
      $mo.get()
      if( $mo.GetPropertyValue("AliasName") -eq $name ) {
          $mo.Delete()
      }
    }
}
function remove-sqlalias32($name) {
    $cnnOpt = new-object System.Management.ConnectionOptions
    $cnnOpt.Context.add("__ProviderArchitecture",32)
    
    remove-sqlaliasint $name $cnnOpt
}

function remove-sqlalias64($name) {
    $cnnOpt = new-object System.Management.ConnectionOptions
    $cnnOpt.Context.add("__ProviderArchitecture", 64)
    
    remove-sqlaliasint $name $cnnOpt 
}

function remove-sqlalias($name) {
    remove-sqlalias32 -name $name
    remove-sqlalias64 -name $name
}

function add-sqlaliasint($name, $server, $port, $protocol, $cnnOpt)
{
    $ca = connectint -cnnopt $cnnOpt

    foreach($mo in $ca.getInstances()){
          $mo.get()
          if( $mo.GetPropertyValue("AliasName") -eq $name ) {
            $mo.SetPropertyValue("ServerName", $server)
            $mo.SetPropertyValue("ProtocolName", $protocol)
            $mo.SetPropertyValue("ConnectionString", (&{ if( $port -eq $null ) { [System.String]::Empty } else {$port -as [System.String]}}))
            $result = $mo.Put()
            return
        }
    }
    
    $mo = $ca.CreateInstance()
    $mo.SetPropertyValue("AliasName", $name)
    $mo.SetPropertyValue("ServerName", $server)
    $mo.SetPropertyValue("ProtocolName", $protocol)
    $mo.SetPropertyValue("ConnectionString", (&{ if( $port -eq $null ) { [System.String]::Empty } else {$port -as [System.String]}}))
    $result = $mo.Put()
}

function add-sqlalias32($name, $server, $port=1433, $protocol="tcp")  {
    $cnnOpt = new-object System.Management.ConnectionOptions
    $cnnOpt.Context.add("__ProviderArchitecture", 32)
    
    add-sqlaliasint $name $server $port $protocol $cnnOpt
}

function add-sqlalias64($name, $server, $port=1433, $protocol="tcp")  {
    $cnnOpt = new-object System.Management.ConnectionOptions
    $cnnOpt.Context.add("__ProviderArchitecture", 64)
    
    add-sqlaliasint $name $server $port $protocol $cnnOpt 
}

function add-sqlalias($name, $server, $port=1433, $protocol="tcp") {
    add-sqlalias32 -name $name -server $server -port $port -protocol $protocol
    add-sqlalias64 -name $name -server $server -port $port -protocol $protocol
}

function get-sqlaliasint($cnnOpt, $a)
{
    $ca = connectint -cnnopt $cnnOpt
    
    foreach($mo in $ca.getInstances()){
          $mo.get()
        @{
        Architecture=$a;
        AliasName=$mo.GetPropertyValue("AliasName");
        Server = $mo.GetPropertyValue("ServerName");
        Protocol=$mo.GetPropertyValue("ProtocolName");
        Port=$mo.GetPropertyValue("ConnectionString");
        } | Select-Object @{Name="Architecture"; Expression={$_.Architecture}}, 
                          @{Name="AliasName"; Expression={$_.AliasName}},
                          @{Name="Server"; Expression={$_.Server}},
                          @{Name="Protocol"; Expression={$_.Protocol}},
                          @{Name="Port"; Expression={$_.Port}};                  
    }
}

function get-sqlalias32  {
    $cnnOpt = new-object System.Management.ConnectionOptions
    $cnnOpt.Context.add("__ProviderArchitecture", 32)
    
    get-sqlaliasint $cnnOpt 32
}

function get-sqlalias64  {
    $cnnOpt = new-object System.Management.ConnectionOptions
    $cnnOpt.Context.add("__ProviderArchitecture", 64)
    
    get-sqlaliasint $cnnOpt 64
}

function get-sqlalias {
    get-sqlalias32 
    get-sqlalias64 
}

#endregion

#uncomment this line and remove the samples. Then rename the file with extension ".psm1". Then you 
#  can use the file as PowerShell module with cmdlet "import-module"

#Export-ModuleMember -Function get-sqlalias, get-sqlalias64, get-sqlalias32, add-sqlalias64, add-sqlalias32, add-sqlalias, remove-sqlalias, remove-sqlalias64, remove-sqlalias32

#region Samples
get-sqlalias | gm

remove-sqlalias "slqserver"

add-sqlalias "sqlserver" "sps2010"  
#endregion
Advertisements

10 thoughts on “Enumerate, add, update and remove SQL Server aliases by using PowerShell

  1. How can I change it to launch the script on a remote computer and update the SQL Alias on a server? I need to update the SQL alias on the SQL server with the ip address of the remote computer.

    1. Hi!

      I don’t understand your scenario. – Why do you want to create a SQL Alias on the server with the IP address of the remote computer? – In all scenarios I work with the SQL alias will be created on the client to address the server. In this situation you don’t need to change the SQL server itself… – Please discribe it again.

      Ingo

      1. The remote computers connect to the SQL Server thru VPN. Once the remote computers connect to SQL Server the server checks to see if there is new data and does a push to the remote computer. If the remote computer is not on the network then SQL Server has no way to connect back to it. I did a test and if I hard code the VPN IP address in a SQL Alias entry for the name of the remote computer it can replicate and do its push. There is no way to change the replication to a pull. Hopes this make sence.

        1. Hi!

          Ok. I understand. You have a database replication running from the “SQL Server” to the “remote computer”?! – There are two ideas:
          1) If you have development skills you could create a web service on “SQL Server” that the remove computer calls at the moment it connects thru VPN. The remote computer tells the web service it’s alias and it’s IP and the web service creates a SQL alias on the “SQL Server”…
          2) With PowerShell you can do “remoting”: start a powershell script on a remote computer. See PowerShell help for details. Typ “get-help about_remoting”. Or “get-help enter-pssession”. – You could also use PowerShell jobs that you start on another machine (“remote job”). See PowerShell help: “get-help about_remote_jobs”.

          I would prefer the first solution. Remoting with PowerShell can be difficult. I had some problems in diffrent situations. A web service would be easier to realize I think. You could call the web service thru PowerShell on the “remote computer”.

          Ingo

  2. I’ve been trying for the best part of a week to get this method to work when running from a Windows 7 box & connectging to a 2008 server running SQL 2008 R2 and I just keep getting:
    Exception calling “Put” with “0” argument(s): “”
    or
    Exception calling “Delete” with “0” argument(s): “”

    The Get seems to work OK though.

    1. Hi Paul,

      are you connecting to SQL Server as SQL Server administrator?

      May be you have to add authentication the the scripts.

      Look at the lines containing “new-object System.Management.ConnectionOptions”. This is a call to the constructor of the .NET class. There are other constructors for this class. Using them you can authenticate / impersonate the connection.

      Please look at this MSDN site: http://msdn.microsoft.com/en-us/library/0xabb593.aspx

      Please send another comment if you have tested this. – Please also post if you need more help on this. I could extend the script.

      Regards
      Ingo

      1. Hi Ingo,
        I got this to work on the test bed I had set up, but am now having the same issue on the Live boxes. The scenario is this Server A is the Principal Server, Server B is the Mirror Server, Server C is the Distribution Server and Server D is the Replication Target.

        Server A mirrors databases to Server B, and also replicates some of those mirrored databases to Server D (using Server C as a central distribution server). The mirrors are all configured with -FailOverPartner settings on the replication agents in order to make replication mirror aware, and in the event of a simple failover the replication continues with noi problems.

        However in the event of a catastrophic failure of Server A, then there is a Powershell script which is run on Server B that breaks the mirrors and brings the databases back on line. As a part of this script it also creates a SQL Alias on the distribution server named for Server A but pointing at Server B to fool replication into continuing.

        It is here that the Put() is failing. I think you are probably right in that it it a permissions issue, but it does seem to be rather hit and miss as to whether it works. I realise it’s a fairly complicated scenario (the one given here is actually simpler than the one I am having to build) and there may be easier ways of doing this, but the brief I was given is that the application should be runnable by a janitor in the event of a disaster and the systems could be brought back operational at the push of a single button.

        I’d like to ay thanks for the script though, it was an inspiration, and a great pointer in the right direction, as well as an excellent piece of Powershell.

        Cheers,

        Paul

        1. Hi Paul!

          Thank you very much for your answer! – I’ve tested the script in a Windows Server 2008 R2 domain environment. There it works perfectly. – Too bad it doesn’t for you.

          Kind regards
          Ingo

          1. I got it to work against the remote server by a very strange solution. I simply reversed the try/catch statement in the ManagementScope:

            function connectint($cnnOpt) {
            $s = $null
            try {
            $s = New-Object System.Management.ManagementScope("\\$($appSettings.Distribution.Split('\\')[0])\root\Microsoft\SqlServer\ComputerManagement", $cnnOpt)
            } catch {
            $s = New-Object System.Management.ManagementScope("\\$($appSettings.Distribution.Split('\\')[0])\root\Microsoft\SqlServer\ComputerManagement10", $cnnOpt)
            }
            $s.connect()

            $mp = New-Object system.Management.ManagementPath("SqlServerAlias")
            $ca = New-Object system.Management.ManagementClass ($s, $mp, $null )

            $ca.get()

            return $ca
            }

            And for some reason this seems to work fine, where having it the other way round results in a Exception calling "Connect" with "0" argument(s): "Invalid namespace" error.

            Cheers,

            Paul

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s