SQL Server 2000 Failover Clustering SP3A enterprise ed.
Action: Change some registry keys after bring SQL resources offline.
I am trying to change the following registry keys to point to new location
on registry at
Master node, member node and virtual server. However, Sytem didn't allow me
to change. The Keys changed themself back after I brought SQL resources back
online or reboot nodes:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer
FullTextDefaultPath
E:\DATA\MSSQL\FTDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Replication
WorkingDirectory
E:\DATA\MSSQL\REPLDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
SQLDataRoot
E:\DATA\MSSQL
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ SQLServerAgent
WorkingDirectory
E:\DATA\MSSQL\JOBS
There must be somewhere hidden in the registry to override those keys I did
change. Can anyone help me figure out how to change those keys in failover
clustering? Is there anyone with such expience? how do you fix that?
Thank you.
Maggie
Hello Maggie,
This is the expected behavior for a clustered SQL Server 2000 instance. The behavior that you saw is due to the fact the registry entries are stored in the Quorum and are over written when you get the SQL Server
resource online. Changing the registry entries is NOT Supported (although there are ways to do it) and I do not recommend it.
Are you trying the registry changes on a production server or a test server?
What exactly are you trying to achieve?
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||For SQL Server default installation, we have default the locations for the
following subdirectories:
BACKUP - default backup location
FTDATA - default FT Catalog location and FTS system config files
JOBS - SQL Agent working area
REPLDATA - Replication working area
However, we need to move them from default installation location to other
cluster disck drives in order apply our capacity planning, such as:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\
Backup from E:\data\mssql\backup to F:\backup
FullTextDefaultPath from E:\DATA\MSSQL\FTDATA to E:\FTDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Replication\WorkingDirectory
E:\DATA\MSSQL\REPLDATA to R:\REPLDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
SQLDataRoot from E:\DATA\MSSQL to E:\DATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ SQLServerAgent
WorkingDirectory from E:\DATA\MSSQL\JOBS to J:\JOBS
Plus, we even could not change any
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0 for applications, gathering
manager and indexer to redirect our full text indexes.
We are in middle of building SQL Server Failover Clustering . We are able
to change registry keys on other non-clustering regulger SQL Server to apply
our disk/file allocation design. But we can't change on the same registry
keys on Clustering as we always did on non-cluster. I hope that Microsoft
can work with us to solve this issue, and make failover clustering for SQL
Server configuable same as on reguler Window 2003 server enterprise ed.
Thank you.
Maggie
|||Maggie,
Even though you can change the registry entries for a regular SQL Server instance, it is not the recommended/supported method (infact it is the worst case scenario and done only with the assistance of a SQL Server
support engineer). SQL Server clustered instances are slightly different and the registry entries are replicated to all cluster nodes and are stored in the quorum. It is not supported to change the registry. That does not
mean one cannot change the registry keys for clustered SQL instances but then what is the guarantee that something may not break. So why go the unsupported path. Not worth it for a production server.
But this doesn't mean that one cannot change the location of the data files, log files, backups etc to other shared disk on the cluster. Supported steps that could be used on regular instances can still be used on
clustered SQL Server. For example, to move the log files on a seperate drive, one could used attach/detach method or Restore with Move.
KB "http://support.microsoft.com/?kbid=314546" has some ideas and links to related articles that you may find helpful.
You can also open up a case with Microsoft Customer Support Services for additional support. Call Support Customer Care: 1-800-936-3500 (U.S. and Canada Only)
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Actually, I already know these articles about moving databases around. And
also I always applied these procedures when each time I built new SQL Server.
However, There are not appliable to move default BACKUP, FTDATA ,JOBS and
REPLDATA folders to other disk drives instead of default E:\data\mssql\ on
failover clustering, such as,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\
Backup from E:\data\mssql\backup to F:\backup
FullTextDefaultPath from E:\DATA\MSSQL\FTDATA to E:\FTDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Replication\WorkingDirectory E:\DATA\MSSQL\REPLDATA to R:\REPLDATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
SQLDataRoot from E:\DATA\MSSQL to E:\DATA
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ SQLServerAgent
WorkingDirectory from E:\DATA\MSSQL\JOBS to J:\JOBS
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0 for applications, gathering
manger, indexer
We do not want to point Backup, jobs, replication and FTDATA to same disk
drives. We need spread out files across the physical drives in order to
allocate resources efficiently. That's best way to implement enterprise
level database servers.
Oracle provide OFA(Optimal Flexible Architecture) to allow us to implement
very large database as part of software without additonal support call.
Microsoft should do the same feature if you have a big vision to support
enterprise databases.
Thanks.
"Uttam Parui[MS]" wrote:
> Maggie,
> Even though you can change the registry entries for a regular SQL Server instance, it is not the recommended/supported method (infact it is the worst case scenario and done only with the assistance of a SQL Server
> support engineer). SQL Server clustered instances are slightly different and the registry entries are replicated to all cluster nodes and are stored in the quorum. It is not supported to change the registry. That does not
> mean one cannot change the registry keys for clustered SQL instances but then what is the guarantee that something may not break. So why go the unsupported path. Not worth it for a production server.
> But this doesn't mean that one cannot change the location of the data files, log files, backups etc to other shared disk on the cluster. Supported steps that could be used on regular instances can still be used on
> clustered SQL Server. For example, to move the log files on a seperate drive, one could used attach/detach method or Restore with Move.
> KB "http://support.microsoft.com/?kbid=314546" has some ideas and links to related articles that you may find helpful.
> You can also open up a case with Microsoft Customer Support Services for additional support. Call Support Customer Care: 1-800-936-3500 (U.S. and Canada Only)
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>
|||You are correct that one should spread out files across the physical drives in order to allocate resources efficiently. And this is how most of our customers do for their GB and TB dbs.
But can't we do the same thing without touching the registry. For example, if I want to use drive V: for Backup, W: for jobs, X: for Replication then we can make these drives dependencies for SQL Server and then use
them. For example, for the backup one will just have to point the new drive (in this case V
. Similary for the jobs, the job definition will point to the appropriate drive. Same will be true for Replication (using TSQL). I amnot an expert on Fulltext and cannot speak for that though. Somebody in the Fulltext newsgroup may be able to answer how to change the default location using TSQL/SEM.
Please note that changing registry (for any SQL instance whether default or virtual) is not supported and may give you undesired results. It is always best to use TSQL or SEM to make the changes. There may be
certain things that cannot be done (as of now) -- for example changing the virtual server name (other than uninstalling/reinstalling) and we have filed a Design Change Request to the SQL development team.
If you feel a particular feature or change should be made, please feel free to write to sqlwish@.microsoft.com decribing the feature you want and its business impact. You may not get a reply back but the SQL
Developers are monitoring it and they will consider it for next versions/service pack changes.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Thank you for your advice. I still have some questions:
1. Can you tell me how to change default location for V: for Backup, W: for
jobs, X: for Replication with TSQL/SEM ?
2. Can you ask your Fulltext team on how to change the default location
using TSQL/SEM on failover clusterng environment?
3. You may request Microsoft SQL Team to offer OFA(Optimal Flexible
Architecture) to the customers on Failover Clustering environment with
formal documents on implementation.
Best Regards,
Maggie
"Uttam Parui[MS]" wrote:
> You are correct that one should spread out files across the physical drives in order to allocate resources efficiently. And this is how most of our customers do for their GB and TB dbs.
> But can't we do the same thing without touching the registry. For example, if I want to use drive V: for Backup, W: for jobs, X: for Replication then we can make these drives dependencies for SQL Server and then use
> them. For example, for the backup one will just have to point the new drive (in this case V
. Similary for the jobs, the job definition will point to the appropriate drive. Same will be true for Replication (using TSQL). I am> not an expert on Fulltext and cannot speak for that though. Somebody in the Fulltext newsgroup may be able to answer how to change the default location using TSQL/SEM.
> Please note that changing registry (for any SQL instance whether default or virtual) is not supported and may give you undesired results. It is always best to use TSQL or SEM to make the changes. There may be
> certain things that cannot be done (as of now) -- for example changing the virtual server name (other than uninstalling/reinstalling) and we have filed a Design Change Request to the SQL development team.
> If you feel a particular feature or change should be made, please feel free to write to sqlwish@.microsoft.com decribing the feature you want and its business impact. You may not get a reply back but the SQL
> Developers are monitoring it and they will consider it for next versions/service pack changes.
> Best Regards,
> Uttam Parui
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
>
|||See inline.
HTH,
Wish you a very happy and prosperous New Year.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
From: =?Utf-8?B?TWFnZ2ll?= <Maggie@.discussions.microsoft.com>
Subject: RE: Can't Change Registry keys in Failover Clustering
Date: Thu, 30 Dec 2004 09:13:06 -0800
Newsgroups: microsoft.public.sqlserver.clustering
Thank you for your advice. I still have some questions:
1. Can you tell me how to change default location for V: for Backup, W: for
jobs, X: for Replication with TSQL/SEM ?
<uttam>
- Assuming SQL Server resource is dependent on V:, W: and X:
- For Backup, if it is a TSQL backup job then I will basically change the TSQL statement to point to V:
For ex, If the backup job is as
BACKUP DATABASE TestDB
TO DISK = 'c:\Testdb.bak'
I will change it to
BACKUP DATABASE TestDB
TO DISK = 'V:\Testdb.bak'
Or if you are using database maintenance plan wizard to create the backup job, you will be able to use V: as the backup location.
- Same idea for jobs.
- For Replication, one method would be to use the TSQL command sp_changedistpublisher. See SQL Server Books Online for syntax.
</uttam>
2. Can you ask your Fulltext team on how to change the default location
using TSQL/SEM on failover clusterng environment?
<uttam> As experts in SQL Clustering are monitoring this newsgroup, experts in SQL fulltext are monitoring the fulltext newsgroup.
Please post your qs in the sql fulltext newsgroup.
http://www.microsoft.com/sql/communi...ver.fulltex t
For all SQL Server newsgroups, see
http://www.microsoft.com/sql/communi...s/default.mspx
</uttam>
3. You may request Microsoft SQL Team to offer OFA(Optimal Flexible
Architecture) to the customers on Failover Clustering environment with
formal documents on implementation.
<uttam> I can do that. But I would suggest that you directly email sqlwish@.microsoft.com. Its better for the development team to hear directly from our customers. It does makes a difference. Do write the business
impact (i.e. how this will benefit your business and/how you are hurting without it, etc) . All emails are monitored and are considered for next versions/service packs.
</uttam>
Best Regards,
Maggie
No comments:
Post a Comment