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

1. 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.

   Reply ↓

   • 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

     Reply ↓

     • 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

       Reply ↓

       • 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

         Reply ↓

         • 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])rootMicrosoftSqlServerComputerManagement", $cnnOpt)
} catch {
$s = New-Object System.Management.ManagementScope("\$($appSettings.Distribution.Split('\')[0])rootMicrosoftSqlServerComputerManagement10", $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

2. 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.

   Reply ↓

   • 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

     Reply ↓

     • 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.

       Reply ↓

       • 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

         Reply ↓

3. Pingback: A Follow-Up Note on SQL Aliases

This site uses Akismet to reduce spam. Learn how your comment data is processed.