February 1, 2007 at 8:53 am
Where is the alias information stored? Is it stored in .ini files or windows registry?
We have few developers workstations on which I need to make new aliases and I would like to make these changes seamless.
Thank you all.
February 1, 2007 at 12:21 pm
Ram,
see if this article from Database Journal will help you. It contains a VB script that adds values to the registry for SQL Server 7, you can adjust script lines for your version.
http://www.databasejournal.com/scripts/article.php/3520216
VB code to add SQL Server advanced entries/server aliases
Disclaimer: This is informational, I did not use this code.
Regards,Yelena Varsha
February 2, 2007 at 6:07 am
just add a registry key to the given path
key type: String Value
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 5, 2007 at 10:31 am
I created a VBScript file to set up standard server definitions in Enterprise Manager. We have two production servers that we access over a VPN that require alias definitions and a SQL login, the other connections all use Windows login. If you just need to define aliases, you only need the CreateAlias part of the script.
Option Explicit
' Declare variables
Dim dmoApp, dmoServerGroup
On Error Resume Next
' Create a ref to the SQL Server Object
Set dmoApp = WScript.CreateObject("SQLDMO.Application")
' Create Production group
CreateGroup dmoApp, "Production", dmoServerGroup
' Check the aliases for PROD1 and PROD2
CreateAlias "PROD1", "DBMSSOCN,192.168.1.1"
CreateAlias "PROD2", "DBMSSOCN,192.168.1.2"
' Register the production servers
RegisterServer dmoServerGroup, "PROD1", "Rocky", "Bullwinkle"
RegisterServer dmoServerGroup, "PROD2", "Rocky", "Bullwinkle"
' Create Servers group
CreateGroup dmoApp, "Servers", dmoServerGroup
RegisterServer dmoServerGroup, "Groucho", "", ""
RegisterServer dmoServerGroup, "Harpo", "", ""
RegisterServer dmoServerGroup, "Chico", "", ""
' Create Workstations group
CreateGroup dmoApp, "Workstations", dmoServerGroup
RegisterServer dmoServerGroup, "(LOCAL)", "", ""
RegisterServer dmoServerGroup, "Abbott", "", ""
RegisterServer dmoServerGroup, "Costello", "", ""
Set dmoServerGroup = Nothing
Set dmoApp = Nothing
Private Sub CreateGroup(ByRef dmoApp, ByRef sGroup, ByRef dmoServerGroup)
On Error Resume Next
Set dmoServerGroup = dmoApp.ServerGroups(sGroup)
If Err.Number Then
Set dmoServerGroup = WScript.CreateObject("SQLDMO.ServerGroup")
dmoServerGroup.Name = sGroup
dmoApp.ServerGroups.Add dmoServerGroup
End If
End Sub
Private Sub CreateAlias(ByRef sAlias, ByRef sDefinition)
Dim sh
Set Sh = Wscript.CreateObject("WScript.Shell")
sh.RegWrite "HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo\" & sAlias, sDefinition, "REG_SZ"
Set Sh = Nothing
End Sub
Private Sub RegisterServer(ByRef dmoServerGroup, ByRef sServer, ByVal sLogin, ByVal sPassword)
Dim dmoRegServer
On Error Resume Next
Set dmoRegServer = dmoServerGroup.RegisteredServers(sServer)
If Err.Number Then
Set dmoRegServer = WScript.CreateObject("SQLDMO.RegisteredServer")
With dmoRegServer
.Name = sServer
If sLogin = "" Then
.UseTrustedConnection = 1
Else
.UseTrustedConnection = 0
.Login = sLogin
.Password = sPassword
End If
End With
' dmoApp.ServerGroups(sGroup).RegisteredServers.Add dmoRegServer
dmoServerGroup.RegisteredServers.Add dmoRegServer
End If
Set dmoRegServer = Nothing
End Sub
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply