Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get And Set SQL Server 2005 Instance Registry Values


Get And Set SQL Server 2005 Instance Registry Values

Author
Message
vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 543
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/vIacoboni/3092.asp



Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623

Was there anything in the PERL script that couldn't be accomplished with xp_instance_regread and xp_instance_regwrite?





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7231 Visits: 2679

Interesting stuff. Couple questions though. Can you post some examples of why you've needed registry access from within SQL? I'm not a PERL user, so is there any advantage to using PERL over the already loaded .Net framework?



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 543
Andy,

Are you asking why registry access in general is needed within SQL, or the SQL Server 2005 specific registry keys discussed in the article?

In some ways, the answer is the same. The registry controls how certain programs interact with their environment. Using SQL Server as an example, it specifies certain paths and options, such as the backup path and port options discussed in the article. If you want these options tweaked, you must either use sanctioned front-ends (assuming they are exposed there) or some kind of utility to do so. In my environment, getting those paths and options to be consistent across multiple instances was important enough that I wrote this utility.

For example, the backup path specifies where files will go if they're not fully qualified. This can make it much easier to backup using a DISK= statement.

Does that answer your question?



Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7231 Visits: 2679

Sorta, maybe wasnt the best question either. Are there things you're able to change this way that you can't using SMO or DMO?



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 543
Andy,

I'm not very familiar with SMO or DMO, but you can only work with what portions of the object model are exposed. BackupDirectory is an exposed property, with warnings not to use it. I didn't see any properties for ports. Of course, SMO and DMO have the advantage that they are documented and supported better than registry keys.

For me, I'm comfortable with Perl and the registry so this was my way to solve the issue. I can see that others might prefer a SMO approach.



Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623

Your list doesn't include the DefaultData and DefaultLog entries in it.

These are the only values I read or set on a regular basis:

Exec xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'

Exec xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog'

Exec xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData'





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
vince.iacoboni@db.com
vince.iacoboni@db.com
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 543
Thanks, Robert, I'll add them in.



George Orellana
George Orellana
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 54
If Microsoft.VisualBasic.Right(hpServerName.ToUpper, 1) = "C" Then

Dim rk As RegistryKey = RegistryKey.OpenRemoteBaseKey(RegistryHive.LocalMachine, _

hpServerName).OpenSubKey("Software\Microsoft\Microsoft SQL Server\MSSQL." & strX & "\Cluster")

Dim Instances As String

Instances = rk.GetValue("ClusterName")



To all those who want to do the Perl script in net code behind.
rstone
rstone
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 824
Vince, SQLArg0 will have the first startup option, no matter which one it is. You need check each possible one until the desired startup option is found or the key does not exist. It's annoying. There can several options (e.g., trace flags). SQLArg0,..., SQLArg10,....

Andy, if you have more than a few dozen SQL Servers, it helps to use these tools to standardize the installation and setup via scripts. The GUI leaves too much room for mistakes by large teams. It's also easier to just run the script. There is a SMO Setting class of some sort, but I think it has a warning that it's going away in a future version. I'm stickin' with the xp version until I'm forced to do otherwise. It's nice when something does not have to be fixed with each version - getting too old for much more of that.

Randy
Helpdesk: "Perhaps I'm not the only one that does not know what you are doing." ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search