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.

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.

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.

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

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): “”

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.

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.

DISCLAIMER

The informations on my blog are provided as is and I cannot guarantee that editing the registry or executing the provided code or provided scripts or doing other actions described in my articles will not cause serious damage. You use this information at your own risk! - I'm not responsible content on external sites.