Managing SQL Server Client Aliases with WMI Provider

原创 2012年03月22日 09:51:16

 SQL Server client aliases make user connections easier, faster, and more convenient. Each alias saves all the information you need to connect to a SQL Server, such as the server name and the client protocol used to connect to a server. By using an alias, you do not need to enter the information each time you connect. You can also use an easy-to-remember nickname for your application that is different from the actual server name.

 

To view a list of aliases defined on the local computer:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlServerAlias

 

The SqlServerAlias class does not define any methods. It inherits the methods from its parent class,ManagementObject. The Delete method of the ManagementObject class can be used to delete an existing alias:

$strComputer = "."
# Name of the alias
$strAliasName = "CH0DE1"
$oldalias=Get-WmiObject –computerName $strComputer -namespace root\Microsoft\SqlServer\ComputerManagement10 `
-class SqlServerAlias -filter "AliasName=’$strAliasName’"
$oldalias.Delete()

 

To get a list of loaded assemblies and use the Split-Path cmdlet to get only the filename:

[System.AppDomain]::CurrentDomain.GetAssemblies() | ForEach-Object { split-path $_.Location -leaf } | Sort-Object

 

To load the Microsoft.SqlServer.SqlWmiManagement.dll assembly for SQL Server 2008, use the LoadWithPartialName method of the System.Reflection.Assembly class. The Out-Null cmdlet is used to suppress the informational message:
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null

For SQL Server 2005, the command should be as follows:
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

 

After the assembly is loaded, you need to first create an SMO object that represents the WMI installation for SQL Server on the local computer. This object will be the parent of the alias you create. The following commands instantiate an object for the SQL Server WMI installation on the local computer:
$strComputer='.'     # '.' or ‘localhost’ for local computer.
$objComputer=New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
$strComputer


Now create an alias object from the Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias class and set the parent of this alias to the ManagedComptuer object you created:
$newalias=New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias")
$newalias.Parent=$objComputer


Each alias has a few properties, such as the name of the server to connect to and the client protocol, that need to be populated before it can be created. In this example, you recreate the CH0DE1 alias you deleted earlier. This alias connects to the named instance DEMOPC\CH0DE1 at port 7001 using the TCP/IP protocol:
$newalias.Name=’CH0DE1’ # name of the new alias
# DEMOPC\CH0DE1 is the SQL Server instance the alias points to
$newalias.ServerName=’DEMOPC\CH0DE1’
# 7001 is the port the SQL Server instance DEMOPC\CH0DE1 is listening on
$newalias.ConnectionString=7001
$newalias.ProtocolName=’tcp’
$newalias.Create()

 

The complete script:

# NAME: CreateServerAlias2008.ps1

# COMMENT: This script creates a new SQL Server alias on a computer that has the SQL Server 2008 client tools.
# =====================================================================================================

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null

$strComputer='.' # '.' or 'localhost' for local computer.
$objComputer=New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $strComputer

$newalias=New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias")
$newalias.Parent=$objComputer
$newalias.Name='CH0DE1' # name of the new alias
# DEMOPC\CH0DE1 is the SQL Server instance the alias points to
$newalias.ServerName='DEMOPC\CH0DE1'
# 7001 is the port the SQL Server instance DEMOPC\CH0DE1 is listening on
$newalias.ConnectionString=7001
$newalias.ProtocolName='tcp'
$newalias.Create()

 

相关文章推荐

sql server2008 无法连接到WMI提供程序

网上有很多人也遇到了这个问题,我站在巨人的肩膀上总结我试过的几个网上解决过这个方法给大家看看。 方法一: 管理员(administrator)没有 network service的权限或者us...

SQL Server配置管理器无法连接到 WMI 提供程序。您没有权限或者该服务器无法访问

SQL Server配置管理器无法连接到 WMI 提供程序。您没有权限或者该服务器无法访问 。通过一下几种方法应该能够解决,至少数据库能够正常运行。...

Asp中的MSSQL数据库访问:driver={SQL Server};与Provider = Sqloledb;的区别

Asp中的MSSQL数据库访问:driver={SQL Server};与Provider = Sqloledb;的区别打工作开始(2004年下半年),Asp能见到的两个数据库连接就是driver={...
  • form88
  • form88
  • 2017年03月26日 15:50
  • 299

启动SQL 2005的SQL Server Configuration Manager时显示无法连接到WMI提供程序

我想用sql server做一个数据库的作业,主要是想用一下asp做个网页。首先需要安装sql server,刚开始以为是件很简单的事,在我的visual studio 2008的安装包里就有sql ...

解决SQL Server 2005 登录问题(provider: 共享内存提供程序, error: 0 - 管道的另一端上无任何进程。)

已成功与服务器建立连接,但是在登录过程中发生错误。 (provider: 共享内存提供程序, error: 0 - 管道的另一端上无任何进程。)用户 'sa' 登录失败。该用户与可信 SQL Serv...

(provider: 命名管道提供程序, error: 40 - 无法打开到 SQL Server 的连接) 的解决方案

在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且 SQL Server 已配置为允许远程连接。 (provider: 命名管...

How to setup linked servers for SQL Server and Oracle 64 bit client

How to setup linked servers for SQL Server and Oracle 64 bit client ProblemWe have two SQL 2005 64-b...

SQL Server Native Client下载

SQL Server Native Client下载与安装 在安装 SQL Server 2008 或 SQL Server 工具时,将同时安装 Microsoft SQL Server Nativ...

Microsoft SQL Server 2012 Native Client

  • 2016年10月10日 14:36
  • 5.58MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Managing SQL Server Client Aliases with WMI Provider
举报原因:
原因补充:

(最多只允许输入30个字)