No downtime

My goal is to have no downtime while rebuilding indexes. Since SQL
2005 doesn't support online indexing for tables with large object types
(ie NTEXT), this is not an option for me. So I think the only other
option is to use P2P replication..
Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
and the hot-standby server without service interruption? This way, if
the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Also, is it allowed to have a single CPU license for two SQL 2005
server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
Thank you!
MichaelFirst, I'll tackle the larger issues.
A second SQL Server is licensed to be a 'hot standby' server only under the
SQL Server Enterprise Edition license.
Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'kick
in'. The hot standby server must be manually brought on-line after the last
log file is transferred and added.
Even in a Clustered, Fail-Over scenario, FailOver occurs because the server
is down, NOT because a table is inconveniently 'locked'.
I hope this helps you come up with a different strategy.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
> Thank you!
> Michael
>|||I am at loss for another strategy. Is there another common strategy to
workaround timeouts which occur from table locks while reindexing?
I found
http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
which mentions:
"When doing failover support, a server is designated as the passive
server. The purpose of the passive server is to absorb the data and
information held in another server that fails. A passive server does
not need a license, provided that the number of processors in the
passive server is equal or less than those of the active server. The
passive server can take the duties of the active server for 30 days.
Afterward, it must be licensed accordingly."
Are you sure about it can't be a hot-standby server with Standard
edition?
Thank You,
Michael
Arnie Rowland wrote:
> First, I'll tackle the larger issues.
> A second SQL Server is licensed to be a 'hot standby' server only under the
> SQL Server Enterprise Edition license.
> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'kick
> in'. The hot standby server must be manually brought on-line after the last
> log file is transferred and added.
> Even in a Clustered, Fail-Over scenario, FailOver occurs because the server
> is down, NOT because a table is inconveniently 'locked'.
> I hope this helps you come up with a different strategy.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> > My goal is to have no downtime while rebuilding indexes. Since SQL
> > 2005 doesn't support online indexing for tables with large object types
> > (ie NTEXT), this is not an option for me. So I think the only other
> > option is to use P2P replication..
> >
> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> > and the hot-standby server without service interruption? This way, if
> > the live table is locked
> > during this operation, the hot-standby server would take over the CRUD
> > statements.
> >
> > Also, is it allowed to have a single CPU license for two SQL 2005
> > server if
> > one is a hot-standby-only server and the standby-only server is only
> > used for failover purposes. Note that when tables are locked during
> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> > possible?
> >
> >
> > Thank you!
> > Michael
> >|||I never assert that I know from beans where licensing is concerned. It's
complex and often changing.
I do believe you are correct about the passive server license issue -I
misunderstood how you proposed to configure the servers. However, I think
that the licensing document goes on to indicate that any client queries are
executing on the passive server while at the same time the active server is
still online and 'working' requires a separate license for the passive
server.
From that same document: "Figure 3. Passive Servers. The passive server does
not require a license given that no queries are being executed against it."
When 'FailOver' occurs, the once active server's license is passed over to
the previous passive server. It's not unlike buying a new car and knowing
that your insurance covers you as you drive home and then you have a 'grace'
period to insure the new car.
But as I read your original post, it seems that your purpose is to have
activity on both servers at the same time -indexing on one server, and query
execution on the other server. I'm reasonably sure that is not a allowed
activity for an Active-Passive single license scenario. Bottom line is, in
my opinion, that you can't use one license for two operating servers. BUT,
the definitive answer can be obtained from the VL folks.
And a locked table STILL does not create a 'FailOver' situation.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...
>I am at loss for another strategy. Is there another common strategy to
> workaround timeouts which occur from table locks while reindexing?
> I found
> http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
> which mentions:
> "When doing failover support, a server is designated as the passive
> server. The purpose of the passive server is to absorb the data and
> information held in another server that fails. A passive server does
> not need a license, provided that the number of processors in the
> passive server is equal or less than those of the active server. The
> passive server can take the duties of the active server for 30 days.
> Afterward, it must be licensed accordingly."
> Are you sure about it can't be a hot-standby server with Standard
> edition?
> Thank You,
> Michael
>
> Arnie Rowland wrote:
>> First, I'll tackle the larger issues.
>> A second SQL Server is licensed to be a 'hot standby' server only under
>> the
>> SQL Server Enterprise Edition license.
>> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT
>> 'kick
>> in'. The hot standby server must be manually brought on-line after the
>> last
>> log file is transferred and added.
>> Even in a Clustered, Fail-Over scenario, FailOver occurs because the
>> server
>> is down, NOT because a table is inconveniently 'locked'.
>> I hope this helps you come up with a different strategy.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> <mpaine@.htxml.com> wrote in message
>> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
>> > My goal is to have no downtime while rebuilding indexes. Since SQL
>> > 2005 doesn't support online indexing for tables with large object types
>> > (ie NTEXT), this is not an option for me. So I think the only other
>> > option is to use P2P replication..
>> >
>> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
>> > and the hot-standby server without service interruption? This way, if
>> > the live table is locked
>> > during this operation, the hot-standby server would take over the CRUD
>> > statements.
>> >
>> > Also, is it allowed to have a single CPU license for two SQL 2005
>> > server if
>> > one is a hot-standby-only server and the standby-only server is only
>> > used for failover purposes. Note that when tables are locked during
>> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
>> > possible?
>> >
>> >
>> > Thank you!
>> > Michael
>> >
>|||Thank you for the licensing clarification.
I guess the root of my question has nothing to do with licensing and
all to do with high-availablility. EE doesn't not support online
indexing for LOB type tables so I am still stuck without a solution.
My only idea is to: (a) switch traffic to database mirrored server (b)
reindex live server, (c) switch traffic back. Is that even possible?
http://www.sql-server-performance.com/sql_server_high_availability2.asp
gives a good overview of the different options and I am more confused
after reading it.
Arnie Rowland wrote:
> I never assert that I know from beans where licensing is concerned. It's
> complex and often changing.
> I do believe you are correct about the passive server license issue -I
> misunderstood how you proposed to configure the servers. However, I think
> that the licensing document goes on to indicate that any client queries are
> executing on the passive server while at the same time the active server is
> still online and 'working' requires a separate license for the passive
> server.
> From that same document: "Figure 3. Passive Servers. The passive server does
> not require a license given that no queries are being executed against it."
> When 'FailOver' occurs, the once active server's license is passed over to
> the previous passive server. It's not unlike buying a new car and knowing
> that your insurance covers you as you drive home and then you have a 'grace'
> period to insure the new car.
> But as I read your original post, it seems that your purpose is to have
> activity on both servers at the same time -indexing on one server, and query
> execution on the other server. I'm reasonably sure that is not a allowed
> activity for an Active-Passive single license scenario. Bottom line is, in
> my opinion, that you can't use one license for two operating servers. BUT,
> the definitive answer can be obtained from the VL folks.
> And a locked table STILL does not create a 'FailOver' situation.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...
> >I am at loss for another strategy. Is there another common strategy to
> > workaround timeouts which occur from table locks while reindexing?
> >
> > I found
> > http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
> > which mentions:
> >
> > "When doing failover support, a server is designated as the passive
> > server. The purpose of the passive server is to absorb the data and
> > information held in another server that fails. A passive server does
> > not need a license, provided that the number of processors in the
> > passive server is equal or less than those of the active server. The
> > passive server can take the duties of the active server for 30 days.
> > Afterward, it must be licensed accordingly."
> >
> > Are you sure about it can't be a hot-standby server with Standard
> > edition?
> >
> > Thank You,
> > Michael
> >
> >
> >
> > Arnie Rowland wrote:
> >> First, I'll tackle the larger issues.
> >>
> >> A second SQL Server is licensed to be a 'hot standby' server only under
> >> the
> >> SQL Server Enterprise Edition license.
> >>
> >> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT
> >> 'kick
> >> in'. The hot standby server must be manually brought on-line after the
> >> last
> >> log file is transferred and added.
> >>
> >> Even in a Clustered, Fail-Over scenario, FailOver occurs because the
> >> server
> >> is down, NOT because a table is inconveniently 'locked'.
> >>
> >> I hope this helps you come up with a different strategy.
> >>
> >> --
> >> Arnie Rowland, Ph.D.
> >> Westwood Consulting, Inc
> >>
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> <mpaine@.htxml.com> wrote in message
> >> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> >> > My goal is to have no downtime while rebuilding indexes. Since SQL
> >> > 2005 doesn't support online indexing for tables with large object types
> >> > (ie NTEXT), this is not an option for me. So I think the only other
> >> > option is to use P2P replication..
> >> >
> >> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> >> > and the hot-standby server without service interruption? This way, if
> >> > the live table is locked
> >> > during this operation, the hot-standby server would take over the CRUD
> >> > statements.
> >> >
> >> > Also, is it allowed to have a single CPU license for two SQL 2005
> >> > server if
> >> > one is a hot-standby-only server and the standby-only server is only
> >> > used for failover purposes. Note that when tables are locked during
> >> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> >> > possible?
> >> >
> >> >
> >> > Thank you!
> >> > Michael
> >> >
> >|||On 4 Aug 2006 12:04:32 -0700, mpaine@.htxml.com wrote:
>My goal is to have no downtime while rebuilding indexes. Since SQL
>2005 doesn't support online indexing for tables with large object types
>(ie NTEXT), this is not an option for me. So I think the only other
>option is to use P2P replication..
How about move the ntext into a 1:1 table with just the NTEXT and PK,
and all other indexes in the other table.
It's not free, but it's cheap.
Josh|||<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
I think your worries about the loss in availablility due to reindexing are
grossly exaggerated. The restriction for online indexes and large object
types applies only for clustered indexes on tables with large types. So if
you really must have nothing but online indexes, just make the tables with
large object types heaps, with a non-clustered primary key index. EG
drop table itest
go
create table itest
(
id int not null,
data varchar(max),
data1 text,
constraint pk_itest
primary key nonclustered(id)
)
create unique nonclustered index pk_itest
on itest(id)
with (drop_existing=on, online=on)
Alternatively you can use partitioning to minimize the impace of index
rebuilding as a partitioned index can be rebuilt one partition at a time.
As for P2P replication, it would work fine for this, and would allow you to
take a node down to patch the OS, change hardware or whatever. The
"failover" wouldn't happen automatically since P2P has no built-in notion of
failover, and because nothing failed. You would have to manage the traffic
yourself, through the application, DNS or something like an F5 BigIP switch.
For licensing, my understanding is that an unlicensed passive failover node
can only be used with log shipping, mirroring and failover clustering. So
you would need to license the peer node.
David|||This was more than helpful! Thank you
David Browne wrote:
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> > My goal is to have no downtime while rebuilding indexes. Since SQL
> > 2005 doesn't support online indexing for tables with large object types
> > (ie NTEXT), this is not an option for me. So I think the only other
> > option is to use P2P replication..
> >
> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> > and the hot-standby server without service interruption? This way, if
> > the live table is locked
> > during this operation, the hot-standby server would take over the CRUD
> > statements.
> >
> > Also, is it allowed to have a single CPU license for two SQL 2005
> > server if
> > one is a hot-standby-only server and the standby-only server is only
> > used for failover purposes. Note that when tables are locked during
> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> > possible?
> >
> I think your worries about the loss in availablility due to reindexing are
> grossly exaggerated. The restriction for online indexes and large object
> types applies only for clustered indexes on tables with large types. So if
> you really must have nothing but online indexes, just make the tables with
> large object types heaps, with a non-clustered primary key index. EG
> drop table itest
> go
> create table itest
> (
> id int not null,
> data varchar(max),
> data1 text,
> constraint pk_itest
> primary key nonclustered(id)
> )
> create unique nonclustered index pk_itest
> on itest(id)
> with (drop_existing=on, online=on)
>
> Alternatively you can use partitioning to minimize the impace of index
> rebuilding as a partitioned index can be rebuilt one partition at a time.
> As for P2P replication, it would work fine for this, and would allow you to
> take a node down to patch the OS, change hardware or whatever. The
> "failover" wouldn't happen automatically since P2P has no built-in notion of
> failover, and because nothing failed. You would have to manage the traffic
> yourself, through the application, DNS or something like an F5 BigIP switch.
> For licensing, my understanding is that an unlicensed passive failover node
> can only be used with log shipping, mirroring and failover clustering. So
> you would need to license the peer node.
> David