Basic question.... on Index....

Why the name clustered is used in the clustered index? What is exactly
clustered here?
Thanks,
Jessy
Hi,
Physical ordering of data inside the data page will be same as the Index key
order.
The pages in the data chain and the records on the pages are ordered on the
value of the clustered index key. All inserts are made at the point the key
value in the inserted row fits in the ordering sequence.
See more details of cluetsred index in books online.
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>
|||Clustered means the index keys and the table are clustered in the same page?
Jessy
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Physical ordering of data inside the data page will be same as the Index
key
> order.
> The pages in the data chain and the records on the pages are ordered on
the
> value of the clustered index key. All inserts are made at the point the
key
> value in the inserted row fits in the ordering sequence.
> See more details of cluetsred index in books online.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
>
|||Hi,
No. There are seperate Index and Data pages. read the below article.
http://msdn.microsoft.com/library/de...es_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> key
> the
> key
>
|||"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>
As an example...
A clustered index is sort of like a dictionary. The keys (dictionary words)
are stored in sorted order. If you need to add a new row called Alligator,
then it would be placed in the correct location in the dictionary.
A non-clustered index is more like the index you find at the back of a book.
The index at the back of the book is stored in sorted order, however, it has
page numbers which tell you to go to page xx to find your word. When you
insert a row here, the data will be appended to the end of the table (unless
you have a clustered index on it), and the index at the back of the book
will be updated appropriately.
One thing to note is that when you have a nonclustered index on a table that
has a clustered index, the "page numbers" don't actually point at the pages,
but rather point at the clustered index key (dictionary word from above).
When you do an insert, the nonclustered index has it's row modified and it
then points to the clustered index key and the row is added in the
dictionary order.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Excellent explanation. This is the sort of explanation I was looking
Thank you very much,
Jessy
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYPXjysZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> As an example...
> A clustered index is sort of like a dictionary. The keys (dictionary
words)
> are stored in sorted order. If you need to add a new row called
Alligator,
> then it would be placed in the correct location in the dictionary.
> A non-clustered index is more like the index you find at the back of a
book.
> The index at the back of the book is stored in sorted order, however, it
has
> page numbers which tell you to go to page xx to find your word. When you
> insert a row here, the data will be appended to the end of the table
(unless
> you have a clustered index on it), and the index at the back of the book
> will be updated appropriately.
> One thing to note is that when you have a nonclustered index on a table
that
> has a clustered index, the "page numbers" don't actually point at the
pages,
> but rather point at the clustered index key (dictionary word from above).
> When you do an insert, the nonclustered index has it's row modified and it
> then points to the clustered index key and the row is added in the
> dictionary order.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||One of the main benefits of clustered indexes is that you can get better
response for range queries or queries with duplicate keys, because the data
is stored on the same or side by side pages. An IO of 1 page will return
multiple rows that satisfy such a query, reducing IO time in a big way.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>
|||Hari:
I'm not sure what you mean by this statement. Your statement is true for
non-clustered indexes, but for the clustered index, the index key and the
data are on the same pages and ARE the data pages. The clustered index does
not use Index pages; they are the data pages, at the leaf level.
Sincerely,
Anthony Thomas

"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Tcu7vsZFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
No. There are seperate Index and Data pages. read the below article.
http://msdn.microsoft.com/library/de...es_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> key
> the
> key
>
sql

Basic question.... on Index....

Why the name clustered is used in the clustered index? What is exactly
clustered here?
Thanks,
JessyHi,
Physical ordering of data inside the data page will be same as the Index key
order.
The pages in the data chain and the records on the pages are ordered on the
value of the clustered index key. All inserts are made at the point the key
value in the inserted row fits in the ordering sequence.
See more details of cluetsred index in books online.
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>|||Clustered means the index keys and the table are clustered in the same page?
Jessy
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Physical ordering of data inside the data page will be same as the Index
key
> order.
> The pages in the data chain and the records on the pages are ordered on
the
> value of the clustered index key. All inserts are made at the point the
key
> value in the inserted row fits in the ordering sequence.
> See more details of cluetsred index in books online.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> > Why the name clustered is used in the clustered index? What is exactly
> > clustered here?
> >
> > Thanks,
> >
> > Jessy
> >
> >
>|||Hi,
No. There are seperate Index and Data pages. read the below article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Physical ordering of data inside the data page will be same as the Index
> key
>> order.
>> The pages in the data chain and the records on the pages are ordered on
> the
>> value of the clustered index key. All inserts are made at the point the
> key
>> value in the inserted row fits in the ordering sequence.
>> See more details of cluetsred index in books online.
>>
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
>> news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
>> > Why the name clustered is used in the clustered index? What is exactly
>> > clustered here?
>> >
>> > Thanks,
>> >
>> > Jessy
>> >
>> >
>>
>|||"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>
As an example...
A clustered index is sort of like a dictionary. The keys (dictionary words)
are stored in sorted order. If you need to add a new row called Alligator,
then it would be placed in the correct location in the dictionary.
A non-clustered index is more like the index you find at the back of a book.
The index at the back of the book is stored in sorted order, however, it has
page numbers which tell you to go to page xx to find your word. When you
insert a row here, the data will be appended to the end of the table (unless
you have a clustered index on it), and the index at the back of the book
will be updated appropriately.
One thing to note is that when you have a nonclustered index on a table that
has a clustered index, the "page numbers" don't actually point at the pages,
but rather point at the clustered index key (dictionary word from above).
When you do an insert, the nonclustered index has it's row modified and it
then points to the clustered index key and the row is added in the
dictionary order.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent explanation. This is the sort of explanation I was looking
Thank you very much,
Jessy
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYPXjysZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> > Why the name clustered is used in the clustered index? What is exactly
> > clustered here?
> >
> > Thanks,
> >
> > Jessy
> >
> >
> As an example...
> A clustered index is sort of like a dictionary. The keys (dictionary
words)
> are stored in sorted order. If you need to add a new row called
Alligator,
> then it would be placed in the correct location in the dictionary.
> A non-clustered index is more like the index you find at the back of a
book.
> The index at the back of the book is stored in sorted order, however, it
has
> page numbers which tell you to go to page xx to find your word. When you
> insert a row here, the data will be appended to the end of the table
(unless
> you have a clustered index on it), and the index at the back of the book
> will be updated appropriately.
> One thing to note is that when you have a nonclustered index on a table
that
> has a clustered index, the "page numbers" don't actually point at the
pages,
> but rather point at the clustered index key (dictionary word from above).
> When you do an insert, the nonclustered index has it's row modified and it
> then points to the clustered index key and the row is added in the
> dictionary order.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||One of the main benefits of clustered indexes is that you can get better
response for range queries or queries with duplicate keys, because the data
is stored on the same or side by side pages. An IO of 1 page will return
multiple rows that satisfy such a query, reducing IO time in a big way.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>|||Hari:
I'm not sure what you mean by this statement. Your statement is true for
non-clustered indexes, but for the clustered index, the index key and the
data are on the same pages and ARE the data pages. The clustered index does
not use Index pages; they are the data pages, at the leaf level.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Tcu7vsZFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
No. There are seperate Index and Data pages. read the below article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Physical ordering of data inside the data page will be same as the Index
> key
>> order.
>> The pages in the data chain and the records on the pages are ordered on
> the
>> value of the clustered index key. All inserts are made at the point the
> key
>> value in the inserted row fits in the ordering sequence.
>> See more details of cluetsred index in books online.
>>
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
>> news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
>> > Why the name clustered is used in the clustered index? What is exactly
>> > clustered here?
>> >
>> > Thanks,
>> >
>> > Jessy
>> >
>> >
>>
>

Basic question.... on Index....

Why the name clustered is used in the clustered index? What is exactly
clustered here?
Thanks,
JessyHi,
Physical ordering of data inside the data page will be same as the Index key
order.
The pages in the data chain and the records on the pages are ordered on the
value of the clustered index key. All inserts are made at the point the key
value in the inserted row fits in the ordering sequence.
See more details of cluetsred index in books online.
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>|||Clustered means the index keys and the table are clustered in the same page?
Jessy
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Physical ordering of data inside the data page will be same as the Index
key
> order.
> The pages in the data chain and the records on the pages are ordered on
the
> value of the clustered index key. All inserts are made at the point the
key
> value in the inserted row fits in the ordering sequence.
> See more details of cluetsred index in books online.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
>|||Hi,
No. There are seperate Index and Data pages. read the below article.
_05_5h6b.asp" target="_blank">http://msdn.microsoft.com/library/d...>
_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> key
> the
> key
>|||"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>
As an example...
A clustered index is sort of like a dictionary. The keys (dictionary words)
are stored in sorted order. If you need to add a new row called Alligator,
then it would be placed in the correct location in the dictionary.
A non-clustered index is more like the index you find at the back of a book.
The index at the back of the book is stored in sorted order, however, it has
page numbers which tell you to go to page xx to find your word. When you
insert a row here, the data will be appended to the end of the table (unless
you have a clustered index on it), and the index at the back of the book
will be updated appropriately.
One thing to note is that when you have a nonclustered index on a table that
has a clustered index, the "page numbers" don't actually point at the pages,
but rather point at the clustered index key (dictionary word from above).
When you do an insert, the nonclustered index has it's row modified and it
then points to the clustered index key and the row is added in the
dictionary order.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent explanation. This is the sort of explanation I was looking
Thank you very much,
Jessy
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYPXjysZFHA.1456@.TK2MSFTNGP15.phx.gbl...
> "Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
> news:#Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> As an example...
> A clustered index is sort of like a dictionary. The keys (dictionary
words)
> are stored in sorted order. If you need to add a new row called
Alligator,
> then it would be placed in the correct location in the dictionary.
> A non-clustered index is more like the index you find at the back of a
book.
> The index at the back of the book is stored in sorted order, however, it
has
> page numbers which tell you to go to page xx to find your word. When you
> insert a row here, the data will be appended to the end of the table
(unless
> you have a clustered index on it), and the index at the back of the book
> will be updated appropriately.
> One thing to note is that when you have a nonclustered index on a table
that
> has a clustered index, the "page numbers" don't actually point at the
pages,
> but rather point at the clustered index key (dictionary word from above).
> When you do an insert, the nonclustered index has it's row modified and it
> then points to the clustered index key and the row is added in the
> dictionary order.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||One of the main benefits of clustered indexes is that you can get better
response for range queries or queries with duplicate keys, because the data
is stored on the same or side by side pages. An IO of 1 page will return
multiple rows that satisfy such a query, reducing IO time in a big way.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23Sy2IfsZFHA.3852@.TK2MSFTNGP10.phx.gbl...
> Why the name clustered is used in the clustered index? What is exactly
> clustered here?
> Thanks,
> Jessy
>|||Hari:
I'm not sure what you mean by this statement. Your statement is true for
non-clustered indexes, but for the clustered index, the index key and the
data are on the same pages and ARE the data pages. The clustered index does
not use Index pages; they are the data pages, at the leaf level.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Tcu7vsZFHA.3120@.TK2MSFTNGP12.phx.gbl...
Hi,
No. There are seperate Index and Data pages. read the below article.
_05_5h6b.asp" target="_blank">http://msdn.microsoft.com/library/d...>
_05_5h6b.asp
Thanks
Hari
SQL Server MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:%23rDFQpsZFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Clustered means the index keys and the table are clustered in the same
> page?
> Jessy
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23xMrpksZFHA.2128@.TK2MSFTNGP14.phx.gbl...
> key
> the
> key
>

Bad startup parameter on clustered instance

Hello,
I inadvertantly mis-edited the SQL startup parameters on a clustered
instance and now the instance won't start. I'm wondering if there's a quick
fix to correct the problem. (short of uninstall/reinstall <g>)
I've researched this and am aware of the problems changing the local
registry not being persistent due to the cluster-shared reg overwriting the
local version. What I guess I'd like to do is force an overwrite of the
cluster version of the reg setting from the local node. Is there a way to do
this?
Thanks,
Randy Rabin
Well, you shouldn't directly edit the registry entries of a cluster-aware
app like a clustered SQL Server in the first place. That's totally
unsupported. From what you said, you are probably stuck in a vicious cycle.
No matter how hard you try to edit the startup parameter back to its correct
value, your entered value will be overwritted by the bad value, causing the
SQL Server resource to keep failing the startup.
This is because the cluster service checkpoints its changes and basically
replicates the registry changes, and your entered value is overwritten by
the checkpoint saved by the cluster service.
But there is a way to get out of the vicious cycle.
1. Remote console to the node that currently owns the offline SQL Server
group.
2. Open a command prompt on the node, and start up the SQL Server from the
command line with the correct parameters, something like the following:
cmd>sqlservr -sMSSQLSERVER -c -dE:\MSSQL\Data\master.mdl -eE:\MSSQL\Log\errorlog
-le:\MSSQL\Data\master.ldf
** (If this doesn't work, see Note below)
3. Once SQL Server is running, start up Enterprise Manager, and change your
startup parameter(s) there to their correct value. This is critical because
the change made through Enterprise Manager will be saved by the cluster
service in a checkpoint, and will be replicated to the other nodes.
4 . Go to Cluster Administrator, bring up SQL Server resource online.
5. Check the registry entries for the startup parameters to make sure tht
they are still correct, and they should be correct at this point. This step
is not necessarily. But it's just a sanity check.
6. Move the SQL Server resource group to a different node. It should come up
online on that node.
7. Check the registry entries for the startup parameters on the node to make
sure they are correct, and they should be because the startup parameter
value should have been replicated over by the cluster service from the first
node you worked on.
8. Repeat Step 6 and 7 on all the remaining nodes.
**Note that I'm typing this from my recollection. For Step 2, it may also
work if you first edit the local registry to correct the startup parameters,
and then start the SQL Server service on the command line using net start.
If Step 2 as described above doesn't work, give this a try. I know for sure
that this alternative would work for SQL Server 2005 when it's stuck in the
same situation.
***DON'T EVER DIRECTLY EDIT A CLUSTERED SQL SERVER REGISTRY ENTRIES
AGAIN****
Linchi
"Randy Rabin" <randyr@.channeladvisor.com> wrote in message
news:%23mlrK1N0FHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I inadvertantly mis-edited the SQL startup parameters on a clustered
> instance and now the instance won't start. I'm wondering if there's a
> quick
> fix to correct the problem. (short of uninstall/reinstall <g>)
> I've researched this and am aware of the problems changing the local
> registry not being persistent due to the cluster-shared reg overwriting
> the
> local version. What I guess I'd like to do is force an overwrite of the
> cluster version of the reg setting from the local node. Is there a way to
> do
> this?
> Thanks,
> Randy Rabin
>
|||Hi Linchi,
Thank you for the response, but I don't appreciate the soapbox. In fact I
*had* used Enterprise Manager to edit the startup parameter in the first
place. EntMgr wrote the incorrect parameter to the cluster-shared copy of
the registry, which then of course got replicated down to the nodes. The
"fix" I had tried was to re-edit the local node's registry (and yes, I never
do this and agree that normally nobody else should either <g>) and obviously
found out quickly that it wasn't going to work.
I did get the SQL instance running again after a 2-hour call to PSS and a
utility that allowed me to reset the value in the cluster-shared copy of the
registry. Thanks for your solution below, perhaps it will help others.
Regards,
Randy
"Linchi Shea" <linchi_shea@.NOSPAMml.om> wrote in message
news:ubi5OCU0FHA.3780@.TK2MSFTNGP12.phx.gbl...
> Well, you shouldn't directly edit the registry entries of a cluster-aware
> app like a clustered SQL Server in the first place. That's totally
> unsupported. From what you said, you are probably stuck in a vicious
cycle.
> No matter how hard you try to edit the startup parameter back to its
correct
> value, your entered value will be overwritted by the bad value, causing
the
> SQL Server resource to keep failing the startup.
> This is because the cluster service checkpoints its changes and basically
> replicates the registry changes, and your entered value is overwritten by
> the checkpoint saved by the cluster service.
> But there is a way to get out of the vicious cycle.
> 1. Remote console to the node that currently owns the offline SQL Server
> group.
> 2. Open a command prompt on the node, and start up the SQL Server from the
> command line with the correct parameters, something like the following:
>
cmd>sqlservr -sMSSQLSERVER -c -dE:\MSSQL\Data\master.mdl -eE:\MSSQL\Log\erro
rlog
> -le:\MSSQL\Data\master.ldf
> ** (If this doesn't work, see Note below)
> 3. Once SQL Server is running, start up Enterprise Manager, and change
your
> startup parameter(s) there to their correct value. This is critical
because
> the change made through Enterprise Manager will be saved by the cluster
> service in a checkpoint, and will be replicated to the other nodes.
> 4 . Go to Cluster Administrator, bring up SQL Server resource online.
> 5. Check the registry entries for the startup parameters to make sure tht
> they are still correct, and they should be correct at this point. This
step
> is not necessarily. But it's just a sanity check.
> 6. Move the SQL Server resource group to a different node. It should come
up
> online on that node.
> 7. Check the registry entries for the startup parameters on the node to
make
> sure they are correct, and they should be because the startup parameter
> value should have been replicated over by the cluster service from the
first
> node you worked on.
> 8. Repeat Step 6 and 7 on all the remaining nodes.
> **Note that I'm typing this from my recollection. For Step 2, it may also
> work if you first edit the local registry to correct the startup
parameters,
> and then start the SQL Server service on the command line using net start.
> If Step 2 as described above doesn't work, give this a try. I know for
sure
> that this alternative would work for SQL Server 2005 when it's stuck in
the[vbcol=seagreen]
> same situation.
> ***DON'T EVER DIRECTLY EDIT A CLUSTERED SQL SERVER REGISTRY ENTRIES
> AGAIN****
> Linchi
>
> "Randy Rabin" <randyr@.channeladvisor.com> wrote in message
> news:%23mlrK1N0FHA.2212@.TK2MSFTNGP15.phx.gbl...
to
>

Backups on clustered

what kind of backups would you use for a SQL Server which is clustered ?
then again it would depend on the availability ?You can use a regular backups or a maitanance plan backups...
We are working with a cluster environments and there are no problems with it...

Leonid Niraev|||i mean what kind of backups would u run ? i don't want to failover if only one database is suspect ?