Adding a new node to an existing cluster

I currently have two clustered servers. OS - Windows 2003 (SP1), SQL 2000.
These servers are getting old so what I wanted to do is add two new servers
to the cluster and take the original two offline. I have loaded the new
servers, connected them to our sans, and added them to the cluster. I am at
the point of loading SQL. I have read the books on line and according to BOL,
I should be able to "On the Cluster Management screen, select the node and
click Add'. However, I am not given the option, ALL four servers show up in
the "configured nodes" list. What my question is, if I continue, will ALL
servers be loaded/reloaded with SQL or just the top server (the one I am
trying to load).
You will add SQL to the new servers only, just watch the install screen
carefully, cause you can also uninstall the original two nodes
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Marilyn" <Marilyn@.discussions.microsoft.com> wrote in message
news:96494E06-031A-4370-B2AA-C8615627E5FD@.microsoft.com...
>I currently have two clustered servers. OS - Windows 2003 (SP1), SQL 2000.
> These servers are getting old so what I wanted to do is add two new
> servers
> to the cluster and take the original two offline. I have loaded the new
> servers, connected them to our sans, and added them to the cluster. I am
> at
> the point of loading SQL. I have read the books on line and according to
> BOL,
> I should be able to "On the Cluster Management screen, select the node and
> click Add'. However, I am not given the option, ALL four servers show up
> in
> the "configured nodes" list. What my question is, if I continue, will ALL
> servers be loaded/reloaded with SQL or just the top server (the one I am
> trying to load).
|||That is what I am worried about...
I do NOT want to uninstall SQL from the existing nodes. What specifically
should I keep my eye out for?
"Rodney R. Fournier [MVP]" wrote:

> You will add SQL to the new servers only, just watch the install screen
> carefully, cause you can also uninstall the original two nodes
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
> "Marilyn" <Marilyn@.discussions.microsoft.com> wrote in message
> news:96494E06-031A-4370-B2AA-C8615627E5FD@.microsoft.com...
>
>
|||Make sure you install from one of the new machines.
Make sure you have someone standing over your shoulder watching the screens
and double checking your work.
Make sure you select ADD and see the server move to the right side (which
means they will be installed).
Make sure think about any and all defaults (this is how I uninstalled my one
node SQL Cluster a few months back, instead of adding a node).
It's very easy to either do it correctly or incorrectly. Like I said, have
someone double check the screen BEFORE you hit next and all should go well.
Lastly make sure you have a good restore on hand. Yes, I said restore, cause
a backup is worthless, unless you can and have actually tried restoring it.
I hate that people brag about backups, brag about restoring Or don't brag
about either, cause lack of either is a firing offense in my book. Ok, I am
off my soap box!
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Marilyn" <Marilyn@.discussions.microsoft.com> wrote in message
news:A8424504-3A78-4E91-B18B-42B76B18C8F8@.microsoft.com...[vbcol=seagreen]
> That is what I am worried about...
> I do NOT want to uninstall SQL from the existing nodes. What specifically
> should I keep my eye out for?
> "Rodney R. Fournier [MVP]" wrote:
|||Thank you so much. I understand, however where you say "Make sure you select
ADD and see the server move to the right side (which means they will be
installed)." That was my problem, all four servers are ALREADY on the right
side.
P.S. I am in full agreement on the backup part... ;-)
"Rodney R. Fournier [MVP]" wrote:

> Make sure you install from one of the new machines.
> Make sure you have someone standing over your shoulder watching the screens
> and double checking your work.
> Make sure you select ADD and see the server move to the right side (which
> means they will be installed).
> Make sure think about any and all defaults (this is how I uninstalled my one
> node SQL Cluster a few months back, instead of adding a node).
> It's very easy to either do it correctly or incorrectly. Like I said, have
> someone double check the screen BEFORE you hit next and all should go well.
> Lastly make sure you have a good restore on hand. Yes, I said restore, cause
> a backup is worthless, unless you can and have actually tried restoring it.
> I hate that people brag about backups, brag about restoring Or don't brag
> about either, cause lack of either is a firing offense in my book. Ok, I am
> off my soap box!
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
> "Marilyn" <Marilyn@.discussions.microsoft.com> wrote in message
> news:A8424504-3A78-4E91-B18B-42B76B18C8F8@.microsoft.com...
>
>
|||For more information about adding a node to a virtual server, refer to the
"How to add nodes to an existing virtual server (Setup)" topic in SQL Server
2000 Books Online.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Marilyn" <Marilyn@.discussions.microsoft.com> wrote in message
news:392A3D50-7EFA-4C36-82AB-0D74BE1ABB79@.microsoft.com...[vbcol=seagreen]
> Thank you so much. I understand, however where you say "Make sure you
> select
> ADD and see the server move to the right side (which means they will be
> installed)." That was my problem, all four servers are ALREADY on the
> right
> side.
> P.S. I am in full agreement on the backup part... ;-)
>
> "Rodney R. Fournier [MVP]" wrote:
|||Marilyn
Make sure your specifying the correct SQL Virtual Server name. It sounds
like you might be accidently trying to install a new instance of SQL and all
nodes are showing up.
Are you selecting the "Advanced Options" and getting the option to
"Maintain a Virtual Server for Failover Clustering" the Virtual Server? If
not then your more than likely attempting to install a new instance of SQL.
Regards
cT
"Marilyn" wrote:

> I currently have two clustered servers. OS - Windows 2003 (SP1), SQL 2000.
> These servers are getting old so what I wanted to do is add two new servers
> to the cluster and take the original two offline. I have loaded the new
> servers, connected them to our sans, and added them to the cluster. I am at
> the point of loading SQL. I have read the books on line and according to BOL,
> I should be able to "On the Cluster Management screen, select the node and
> click Add'. However, I am not given the option, ALL four servers show up in
> the "configured nodes" list. What my question is, if I continue, will ALL
> servers be loaded/reloaded with SQL or just the top server (the one I am
> trying to load).
|||I got it! I just went for it. It took about 10 minutes to load with no
problems at all. Thanks for the help and advice. I'm really glad Microsoft
has these newsgroups. I have used them serveral times to find additional
suggestions and ideas. Thanks again. M ;-)
"Charles Tolento" wrote:
[vbcol=seagreen]
> Marilyn
> Make sure your specifying the correct SQL Virtual Server name. It sounds
> like you might be accidently trying to install a new instance of SQL and all
> nodes are showing up.
> Are you selecting the "Advanced Options" and getting the option to
> "Maintain a Virtual Server for Failover Clustering" the Virtual Server? If
> not then your more than likely attempting to install a new instance of SQL.
> Regards
> cT
> "Marilyn" wrote:

Adding a column to VLDB 200GB table

The column I'm adding needs to be part of the clustered PK (it will be the
last of three columns) so I need to recreate all the indexes.
My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried
two methods so far.
Method 1:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_copyoftablewithnewfield
(
) ON PRIMARY
IF EXISTS(SELECT * FROM dbo.originaltable)
EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.originaltable
GO
EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
'OBJECT'
GO
<recreate PK constraint>
<rebuild indexes>
COMMIT
Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
Con's: Tons of wasted space and time. It took about 15 hours.
Method 2:
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
<drop PK constraint>
<drop indexes>
ALTER TABLE [dbo].[originaltable] ADD
[newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield] DEFAULT
((1))
<recreate PK constraint>
<rebuild indexes>
COMMIT TRANSACTION
Pro's: No making a copy of the entire table taking up 200GB more space in
the db data file
Con's: My tempdb grew to accomodate the row versioning info for every row in
the 200GB table. It took over 30 hours.
A lot of time and disk space is wasted with both.
Since the db is going to be unavailable to users I have some flexibility
here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then trying
method 2 again which should stop the versioning in tempdb and then turning it
back on.
I was also curious if setting the database recovery mode to SIMPLE would cut
down on db log usage and then I could set it back to FULL when done.
Do these really need to be in a transaction? If there's some hardware
failure or something unexpected I can just restore from backup and do the
conversion again. If the presence of the transaction itself is causing more
disk usage for logging or any other slowdown, I think I'd rather do without.
Given the amount of time this conversion takes, I wanted to get some
feedback other than "just try it" before doing any new tests.
Thanks.Demi
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
It is always worth taking backup of the database or snapshot as you are in
SQL Server 2005.
I'd use ALTER TABLE... (set it up with SIMPLE and ALLOW_SNAPSHOT_ISOLATION
OFF ) approach and there is no need BEGIN TRAN ....
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:D0A356E6-BFB7-498F-B5FA-871B6D3AFD75@.microsoft.com...
> The column I'm adding needs to be part of the clustered PK (it will be the
> last of three columns) so I need to recreate all the indexes.
> My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've
> tried
> two methods so far.
> Method 1:
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_copyoftablewithnewfield
> (
> ) ON PRIMARY
> IF EXISTS(SELECT * FROM dbo.originaltable)
> EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
> SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK
> TABLOCKX)')
> GO
> DROP TABLE dbo.originaltable
> GO
> EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
> 'OBJECT'
> GO
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT
> Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
> Con's: Tons of wasted space and time. It took about 15 hours.
> Method 2:
> SET XACT_ABORT ON
> GO
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> <drop PK constraint>
> <drop indexes>
> ALTER TABLE [dbo].[originaltable] ADD
> [newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield]
> DEFAULT
> ((1))
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT TRANSACTION
> Pro's: No making a copy of the entire table taking up 200GB more space in
> the db data file
> Con's: My tempdb grew to accomodate the row versioning info for every row
> in
> the 200GB table. It took over 30 hours.
> A lot of time and disk space is wasted with both.
> Since the db is going to be unavailable to users I have some flexibility
> here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then
> trying
> method 2 again which should stop the versioning in tempdb and then turning
> it
> back on.
> I was also curious if setting the database recovery mode to SIMPLE would
> cut
> down on db log usage and then I could set it back to FULL when done.
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
> Given the amount of time this conversion takes, I wanted to get some
> feedback other than "just try it" before doing any new tests.
> Thanks.sql

Adding a column to VLDB 200GB table

The column I'm adding needs to be part of the clustered PK (it will be the
last of three columns) so I need to recreate all the indexes.
My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried
two methods so far.
Method 1:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_copyoftablewithnewfield
(
) ON PRIMARY
IF EXISTS(SELECT * FROM dbo.originaltable)
EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.originaltable
GO
EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
'OBJECT'
GO
<recreate PK constraint>
<rebuild indexes>
COMMIT
Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
Con's: Tons of wasted space and time. It took about 15 hours.
Method 2:
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
<drop PK constraint>
<drop indexes>
ALTER TABLE [dbo].[originaltable] ADD
[newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield] DEFAULT
((1))
<recreate PK constraint>
<rebuild indexes>
COMMIT TRANSACTION
Pro's: No making a copy of the entire table taking up 200GB more space in
the db data file
Con's: My tempdb grew to accomodate the row versioning info for every row in
the 200GB table. It took over 30 hours.
A lot of time and disk space is wasted with both.
Since the db is going to be unavailable to users I have some flexibility
here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then trying
method 2 again which should stop the versioning in tempdb and then turning it
back on.
I was also curious if setting the database recovery mode to SIMPLE would cut
down on db log usage and then I could set it back to FULL when done.
Do these really need to be in a transaction? If there's some hardware
failure or something unexpected I can just restore from backup and do the
conversion again. If the presence of the transaction itself is causing more
disk usage for logging or any other slowdown, I think I'd rather do without.
Given the amount of time this conversion takes, I wanted to get some
feedback other than "just try it" before doing any new tests.
Thanks.
Demi
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
It is always worth taking backup of the database or snapshot as you are in
SQL Server 2005.
I'd use ALTER TABLE... (set it up with SIMPLE and ALLOW_SNAPSHOT_ISOLATION
OFF ) approach and there is no need BEGIN TRAN ....
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:D0A356E6-BFB7-498F-B5FA-871B6D3AFD75@.microsoft.com...
> The column I'm adding needs to be part of the clustered PK (it will be the
> last of three columns) so I need to recreate all the indexes.
> My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've
> tried
> two methods so far.
> Method 1:
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_copyoftablewithnewfield
> (
> ) ON PRIMARY
> IF EXISTS(SELECT * FROM dbo.originaltable)
> EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
> SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK
> TABLOCKX)')
> GO
> DROP TABLE dbo.originaltable
> GO
> EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
> 'OBJECT'
> GO
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT
> Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
> Con's: Tons of wasted space and time. It took about 15 hours.
> Method 2:
> SET XACT_ABORT ON
> GO
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> <drop PK constraint>
> <drop indexes>
> ALTER TABLE [dbo].[originaltable] ADD
> [newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield]
> DEFAULT
> ((1))
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT TRANSACTION
> Pro's: No making a copy of the entire table taking up 200GB more space in
> the db data file
> Con's: My tempdb grew to accomodate the row versioning info for every row
> in
> the 200GB table. It took over 30 hours.
> A lot of time and disk space is wasted with both.
> Since the db is going to be unavailable to users I have some flexibility
> here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then
> trying
> method 2 again which should stop the versioning in tempdb and then turning
> it
> back on.
> I was also curious if setting the database recovery mode to SIMPLE would
> cut
> down on db log usage and then I could set it back to FULL when done.
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
> Given the amount of time this conversion takes, I wanted to get some
> feedback other than "just try it" before doing any new tests.
> Thanks.

Adding a column to VLDB 200GB table

The column I'm adding needs to be part of the clustered PK (it will be the
last of three columns) so I need to recreate all the indexes.
My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried
two methods so far.
Method 1:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_copyoftablewithnewfield
(
) ON PRIMARY
IF EXISTS(SELECT * FROM dbo.originaltable)
EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields> )
SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.originaltable
GO
EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
'OBJECT'
GO
<recreate PK constraint>
<rebuild indexes>
COMMIT
Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
Con's: Tons of wasted space and time. It took about 15 hours.
Method 2:
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
<drop PK constraint>
<drop indexes>
ALTER TABLE [dbo].[originaltable] ADD
[newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfi
eld] DEFAULT
((1))
<recreate PK constraint>
<rebuild indexes>
COMMIT TRANSACTION
Pro's: No making a copy of the entire table taking up 200GB more space in
the db data file
Con's: My tempdb grew to accomodate the row versioning info for every row in
the 200GB table. It took over 30 hours.
A lot of time and disk space is wasted with both.
Since the db is going to be unavailable to users I have some flexibility
here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then tryin
g
method 2 again which should stop the versioning in tempdb and then turning i
t
back on.
I was also curious if setting the database recovery mode to SIMPLE would cut
down on db log usage and then I could set it back to FULL when done.
Do these really need to be in a transaction? If there's some hardware
failure or something unexpected I can just restore from backup and do the
conversion again. If the presence of the transaction itself is causing more
disk usage for logging or any other slowdown, I think I'd rather do without.
Given the amount of time this conversion takes, I wanted to get some
feedback other than "just try it" before doing any new tests.
Thanks.Demi
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
It is always worth taking backup of the database or snapshot as you are in
SQL Server 2005.
I'd use ALTER TABLE... (set it up with SIMPLE and ALLOW_SNAPSHOT_ISOLATION
OFF ) approach and there is no need BEGIN TRAN ....
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:D0A356E6-BFB7-498F-B5FA-871B6D3AFD75@.microsoft.com...
> The column I'm adding needs to be part of the clustered PK (it will be the
> last of three columns) so I need to recreate all the indexes.
> My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've
> tried
> two methods so far.
> Method 1:
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_copyoftablewithnewfield
> (
> ) ON PRIMARY
> IF EXISTS(SELECT * FROM dbo.originaltable)
> EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields> )
> SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK
> TABLOCKX)')
> GO
> DROP TABLE dbo.originaltable
> GO
> EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
> 'OBJECT'
> GO
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT
> Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
> Con's: Tons of wasted space and time. It took about 15 hours.
> Method 2:
> SET XACT_ABORT ON
> GO
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> <drop PK constraint>
> <drop indexes>
> ALTER TABLE [dbo].[originaltable] ADD
> [newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_new
field]
> DEFAULT
> ((1))
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT TRANSACTION
> Pro's: No making a copy of the entire table taking up 200GB more space in
> the db data file
> Con's: My tempdb grew to accomodate the row versioning info for every row
> in
> the 200GB table. It took over 30 hours.
> A lot of time and disk space is wasted with both.
> Since the db is going to be unavailable to users I have some flexibility
> here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then
> trying
> method 2 again which should stop the versioning in tempdb and then turning
> it
> back on.
> I was also curious if setting the database recovery mode to SIMPLE would
> cut
> down on db log usage and then I could set it back to FULL when done.
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
> Given the amount of time this conversion takes, I wanted to get some
> feedback other than "just try it" before doing any new tests.
> Thanks.

add second node to sql2005 clustered

Hi, where I can find a guide step-by-step to add a second node to
existing sql2005 cluster?
I have installed an istance only on first node and now I want add the
second one.
I run the setup but i don't see the option to add the new node.
The resources are off-line in the node that i'm going to install.
Thank and sorry for my bad english
..yo.mo.
Cluster modifications are no longer run from the setup CD. Use "Add/Remove
Programs" in control panel to make cluster modifications to SQL 2005
systems.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<dpasella@.gmail.com> wrote in message
news:1135164200.016047.77780@.g14g2000cwa.googlegro ups.com...
> Hi, where I can find a guide step-by-step to add a second node to
> existing sql2005 cluster?
> I have installed an istance only on first node and now I want add the
> second one.
> I run the setup but i don't see the option to add the new node.
> The resources are off-line in the node that i'm going to install.
>
> Thank and sorry for my bad english
> .yo.mo.
>
|||> <dpasella@.gmail.com> wrote in message
> news:1135164200.016047.77780@.g14g2000cwa.googlegro ups.com...
>Geoff N. Hiten wrote:
> Cluster modifications are no longer run from the setup CD. Use "Add/Remove
> Programs" in control panel to make cluster modifications to SQL 2005
> systems.
>
Thanks for your reply.
I have used "add/remove programs" but i can't modify the instance.
The log report that:
Complete: SetPackageInstallStateAction at: 2005/11/27 18:19:12,
returned true
Running: DeterminePackageTransformsAction at: 2005/11/27 18:19:12
Complete: DeterminePackageTransformsAction at: 2005/11/27 18:19:14,
returned true
Running: ValidateSetupPropertiesAction at: 2005/11/27 18:19:14
Complete: ValidateSetupPropertiesAction at: 2005/11/27 18:19:14,
returned true
Running: OpenPipeAction at: 2005/11/27 18:19:14
Complete: OpenPipeAction at: 2005/11/27 18:19:14, returned false
Error: Action "OpenPipeAction" failed during execution.
Running: CreatePipeAction at: 2005/11/27 18:19:14
Complete: CreatePipeAction at: 2005/11/27 18:19:14, returned true
Running: RunRemoteSetupAction at: 2005/11/27 18:19:14
<Func Name='CProcessCtrl::GetInstallPath'>
<EndFunc Name='CProcessCtrl::GetInstallPath' Return='0'
GetLastError='0'>
Error: 0x80070005 TaskScheduler::SetTargetComputer for \\CASTORE
Complete: RunRemoteSetupAction at: 2005/11/27 18:19:16, returned false
Error: Action "RunRemoteSetupAction" failed during execution. Error
information reported during run:
Attempting to determine log files for remote install.
Connection to remote computer's scheduler service.
Error: 80070005 Access denied
Running: PopulateMutatorDbAction at: 2005/11/27 18:19:16
Complete: PopulateMutatorDbAction at: 2005/11/27 18:19:16, returned
true
Running: GenerateRequestsAction at: 2005/11/27 18:19:16
Thanks for your help
..yo.mo.
|||Make sure the Windows Scheduler is running on the remote node.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<dpasella@.gmail.com> wrote in message
news:1135704589.682373.8140@.g49g2000cwa.googlegrou ps.com...
> Thanks for your reply.
> I have used "add/remove programs" but i can't modify the instance.
> The log report that:
>
> Complete: SetPackageInstallStateAction at: 2005/11/27 18:19:12,
> returned true
> Running: DeterminePackageTransformsAction at: 2005/11/27 18:19:12
> Complete: DeterminePackageTransformsAction at: 2005/11/27 18:19:14,
> returned true
> Running: ValidateSetupPropertiesAction at: 2005/11/27 18:19:14
> Complete: ValidateSetupPropertiesAction at: 2005/11/27 18:19:14,
> returned true
> Running: OpenPipeAction at: 2005/11/27 18:19:14
> Complete: OpenPipeAction at: 2005/11/27 18:19:14, returned false
> Error: Action "OpenPipeAction" failed during execution.
> Running: CreatePipeAction at: 2005/11/27 18:19:14
> Complete: CreatePipeAction at: 2005/11/27 18:19:14, returned true
> Running: RunRemoteSetupAction at: 2005/11/27 18:19:14
> <Func Name='CProcessCtrl::GetInstallPath'>
> <EndFunc Name='CProcessCtrl::GetInstallPath' Return='0'
> GetLastError='0'>
> Error: 0x80070005 TaskScheduler::SetTargetComputer for \\CASTORE
> Complete: RunRemoteSetupAction at: 2005/11/27 18:19:16, returned false
> Error: Action "RunRemoteSetupAction" failed during execution. Error
> information reported during run:
> Attempting to determine log files for remote install.
> Connection to remote computer's scheduler service.
> Error: 80070005 Access denied
> Running: PopulateMutatorDbAction at: 2005/11/27 18:19:16
> Complete: PopulateMutatorDbAction at: 2005/11/27 18:19:16, returned
> true
> Running: GenerateRequestsAction at: 2005/11/27 18:19:16
>
> Thanks for your help
> .yo.mo.
>
|||Yes, the service is running (local service).
It is the first control that I have made
Thanks
..yo.mo.
Geoff N. Hiten wrote:[vbcol=seagreen]
> Make sure the Windows Scheduler is running on the remote node.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <dpasella@.gmail.com> wrote in message
> news:1135704589.682373.8140@.g49g2000cwa.googlegrou ps.com...

Add a clustered index in a published table

Hi,
I am using SQL 2K standard and have created a transactional replication
between two sql servers using push subscritpion. It is working fine. Now I
want to add a clustered index in one of the published tables and I get an
error message saying that "Cannot alter the table because it is being
published for replication". Based on this message, I assume I will have to
disable all publishing and distribution, add the clustered index and then
redo the publishing and subscriptions for all the tables from scratch again.
Am I right? Is there another way to do this?
Any help on this will be very much appreciated.
Wingman
Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
are you doing anything else in the same batch eg changing the PK?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the quick response.
Here are the steps I did.
1. Open the existing publishing database in the SQL enterprise manager.
2. Select 'Design' of the table which is a published table in the
publication.
3. Select the primiary key
4. Check the box of the clustered index.
5. Close the design window and click 'save' to save the changes.
6. Then the error occurs.
There are no other batches involved and I was just doing the above. I hope
this clarifies any questions for you.
Wing
"Paul Ibison" wrote:

> Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
> are you doing anything else in the same batch eg changing the PK?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I can't test this right now, but I'd try to run this command:
CREATE CLUSTERED INDEX index_name ON table ( column_name )
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yep, I did and it ran succussfully without any problem. The clustered index
is created. Thanks for this suggestion.
Can you explain why I can't do this in EM? Is it just the limitation of
using this type of interface?
Also, do I need to do the 'create clustered index...' in the subscription
server or the push replication will take care of it? In the future, if I
need to add non-clustered index, will this be the way to do it?
Wingman
"Paul Ibison" wrote:

> I can't test this right now, but I'd try to run this command:
> CREATE CLUSTERED INDEX index_name ON table ( column_name )
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||If you run profiler when applying changes to a table using the Enterprise
Manager interface you can usually determine the cause of tis type of problem.
In this case the table was probably being dropped in the background. The same
issue applies in SQL Server 2005 where if you select to replicate DDL
changes, the Management Studio will fail whereas ALTER TABLE will succeed.
If you want this applied to the subscribers you have to apply initiate this
yourself. You could use sp_addscriptexec to use the replication setup or just
apply it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry, don't mean to keep the discussion going that long but I have a few
follow-up questions regarding sp_addscriptexec.
1. Are there any benefits and differences using sp_addscriptexec compared
to manually executing the script in the subscriber computer?
2. If I use the sp_addscriptexec command to move the scritpt to the
subscriber(s), when or under what situation will the script be excecuted and
how often?
3. If I decide that I no longer need those scripts after using
sp_addscriptexec, how do I remove it from the subscriber computer?
Wingman
"Paul Ibison" wrote:

> If you run profiler when applying changes to a table using the Enterprise
> Manager interface you can usually determine the cause of tis type of problem.
> In this case the table was probably being dropped in the background. The same
> issue applies in SQL Server 2005 where if you select to replicate DDL
> changes, the Management Studio will fail whereas ALTER TABLE will succeed.
> If you want this applied to the subscribers you have to apply initiate this
> yourself. You could use sp_addscriptexec to use the replication setup or just
> apply it manually.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||There are no extra benefits of sp_addscriptexec in terms of running the
script. However, it may be the case that you have dozens of subscribers, or
perhaps some are offline. In this was it gives you convenience and assures
you of getting the script to the subscribers. They'll receive the script when
they synchronize, and the script won't be run more than once and won't be
retained on the subscriber's computer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, thanks.
When I go to the subscription computer and check all replicated tables, I
notice that none of the primary key is defined but the index key for the
primary key is there. Why is it?
Also, when I checked the snapshot properties of the pubished article
defaults in EM, the non-clustered index check box is grayed out. Is there a
way I can activate it so I can uncheck it because I don't want the
non-clustered index to be replicated?
Wingman
"Paul Ibison" wrote:

> There are no extra benefits of sp_addscriptexec in terms of running the
> script. However, it may be the case that you have dozens of subscribers, or
> perhaps some are offline. In this was it gives you convenience and assures
> you of getting the script to the subscribers. They'll receive the script when
> they synchronize, and the script won't be run more than once and won't be
> retained on the subscriber's computer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||PKs are replicated as unique indexes by default in transactional replication.
If you enable the DRI option checkbox on the snapshot tab of the article
properties, the PK will be recreated on teh subscriber. For the chackboxes on
this tab, some are related to others, so try checking/unchecking others to
see if you can enable the nonclustered option. However not all combinations
are supported using this method and in some cases you have to resort to
scripting the replication instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)