No Indexes - Linking to MS Access

Hello,

I am using MS Access 2000 as a front end to a SQL Server 2000 db. When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link. The pk's and indexes do exist in SQL Server. I haven't had to link to these tables in over a year, but they used to link just fine.

Do you have a suggestion as to where to look at this problem?

Thanks you - Darryle

Please define the problem. Are you trying to “design” SQL Server tables form Access? If there are indices in SQL Server, it will take advantage of them when you send queries. You can link to the sysindexes table in your database, and you can query what indices you have in SQL Server for that database.

Zlatko

|||

It has been a while since I have done this type of linking. But it seemed that when I used to link to SQL Server from Access, the linking process would automatically figure out what the primary key was and also see the other indexes on the SQL table. I have an old Acccess front end, when I look at the link tables in design mode (I know I can't change it, just looking) I see the pk's and the indexes.

It seems that now when I link a table from Access, it asks me to pick the pk column, which I do. Once I have linked and I look at the linked table in design mode in Access, I see the pk but none of the indexes show. These are the same tables that had linked to before. Is this a problem? Will the indexes set up in SQL Server be used in Access even though I don't see them?

Thanks for you help and I'll be glad to give you any further information - Darryle

|||

Your question is entirely related to Access and not to SQL Server. Unfortunately I don’t see a thread dedicated to Access programming. You may want to try http://asp.net.

Consider “pass through” queries. They send a native SQL statement to the linked store, and receive the final result – all the processing is done at the server. In that case you don’t care about indices being replicated. Normal Access queries perform the processing in Access, and they need indices but the approach of using SQL Server only for storage and not for processing is near useless.

sql

No Indexes - Linking to MS Access

Hello,

I am using MS Access 2000 as a front end to a SQL Server 2000 db. When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link. The pk's and indexes do exist in SQL Server. I haven't had to link to these tables in over a year, but they used to link just fine.

Do you have a suggestion as to where to look at this problem?

Thanks you - Darryle

Please define the problem. Are you trying to “design” SQL Server tables form Access? If there are indices in SQL Server, it will take advantage of them when you send queries. You can link to the sysindexes table in your database, and you can query what indices you have in SQL Server for that database.

Zlatko

|||

It has been a while since I have done this type of linking. But it seemed that when I used to link to SQL Server from Access, the linking process would automatically figure out what the primary key was and also see the other indexes on the SQL table. I have an old Acccess front end, when I look at the link tables in design mode (I know I can't change it, just looking) I see the pk's and the indexes.

It seems that now when I link a table from Access, it asks me to pick the pk column, which I do. Once I have linked and I look at the linked table in design mode in Access, I see the pk but none of the indexes show. These are the same tables that had linked to before. Is this a problem? Will the indexes set up in SQL Server be used in Access even though I don't see them?

Thanks for you help and I'll be glad to give you any further information - Darryle

|||

Your question is entirely related to Access and not to SQL Server. Unfortunately I don’t see a thread dedicated to Access programming. You may want to try http://asp.net.

Consider “pass through” queries. They send a native SQL statement to the linked store, and receive the final result – all the processing is done at the server. In that case you don’t care about indices being replicated. Normal Access queries perform the processing in Access, and they need indices but the approach of using SQL Server only for storage and not for processing is near useless.

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/downl...censingv1.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:[vbcol=seagreen]
> First, I'll tackle the larger issues.
> A second SQL Server is licensed to be a 'hot standby' server only under th
e
> SQL Server Enterprise Edition license.
> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'ki
ck
> in'. The hot standby server must be manually brought on-line after the las
t
> log file is transferred and added.
> Even in a Clustered, Fail-Over scenario, FailOver occurs because the serve
r
> 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...|||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/downl...censingv1.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:
>|||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.c...ailability2.asp
gives a good overview of the different options and I am more confused
after reading it.
Arnie Rowland wrote:[vbcol=seagreen]
> 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 ar
e
> executing on the passive server while at the same time the active server i
s
> still online and 'working' requires a separate license for the passive
> server.
> From that same document: "Figure 3. Passive Servers. The passive server do
es
> 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 'grac
e'
> 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 que
ry
> 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...|||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...
> 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 i
f
> 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 t
o
> 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 traffi
c
> yourself, through the application, DNS or something like an F5 BigIP switc
h.
> For licensing, my understanding is that an unlicensed passive failover nod
e
> can only be used with log shipping, mirroring and failover clustering. S
o
> you would need to license the peer node.
> Davidsql

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

Nice feature for next SQL server version

Wouldn't it be nice if SQL build statistics which automatically would help
the system create indexes at runtime?
It defenitely would help lazy programmers :)I would add some statistics to help us know whether indexes are used or not (so we can drop the
indexes not being used). Sounds like a case for sqlwish@.microsoft.com. :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> Wouldn't it be nice if SQL build statistics which automatically would help
> the system create indexes at runtime?
> It defenitely would help lazy programmers :)
>|||I take it you're aware of the Index Tuning Wizard which semi-automates the
process of identifying useful indexes and creating them.
--
David Portas
--
Please reply only to the newsgroup
--
"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> Wouldn't it be nice if SQL build statistics which automatically would help
> the system create indexes at runtime?
> It defenitely would help lazy programmers :)
>|||How often have you actually gotten something useful out of the Index
Tuning Wizard? I try it from time to time when I'm stumped and 99% of the
time it ends up either not being able to do anything with the query or
recommends a gigantic composite index that slows performance... Very
annoying.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:88WdnexFd47lWFmiRVn-sQ@.giganews.com...
> I take it you're aware of the Index Tuning Wizard which semi-automates the
> process of identifying useful indexes and creating them.
> --
> David Portas
> --
> Please reply only to the newsgroup|||> How often have you actually gotten something useful out of the Index
> Tuning Wizard?
Errr.. never. I've hardly ever used it. I expect it can give useful results
if supplied with a suitable Profiler trace but I expect most full-time
administrators and designers prefer to do this stuff for themselves based on
business requirements and their own research.
I don't think generating indexes at "runtime" could offer anything better.
Creating indexes without the opportunity for review by the
administrator/designer sounds pretty dangerous to me - if that's what the OP
was suggesting.
--
David Portas
--
Please reply only to the newsgroup
--|||Yeah! Something like a usage counter for each index would be great.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23gTl40CtDHA.684@.TK2MSFTNGP09.phx.gbl...
> I would add some statistics to help us know whether indexes are used or
not (so we can drop the
> indexes not being used). Sounds like a case for sqlwish@.microsoft.com. :-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > Wouldn't it be nice if SQL build statistics which automatically would
help
> > the system create indexes at runtime?
> > It defenitely would help lazy programmers :)
> >
>|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> Wouldn't it be nice if SQL build statistics which automatically would help
> the system create indexes at runtime?
I'd absolutely hate it. Well building a table of suggested indexes might be
nice, but making the change automagically would introduce more problems than
it was worth IMO.
> It defenitely would help lazy programmers :)
Niall Litchfield
Oracle DBA
Audit Commission UK|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:88WdnexFd47lWFmiRVn-sQ@.giganews.com...
> I take it you're aware of the Index Tuning Wizard which semi-automates the
> process of identifying useful indexes and creating them.
I do not agree quite.
I'd use it personally on projects, but the problem is I DO NOT manage the
SQL server, so I should instruct the DBADMIN to optimize or start an
optimization session. Of course, I include indexes at design but I might
forget some of them and more, I have some sort of SQL stored procedures,
which can sort and filter on ANY column. So if the -client- uses a lot of
queries of a specific type, SQL Server should decide at a certain defined
point, to add the index.
It would be unwise of me to add indexes for all possible combinations on
deployment.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > Wouldn't it be nice if SQL build statistics which automatically would
help
> > the system create indexes at runtime?
> > It defenitely would help lazy programmers :)
> >
>|||"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fc5ee5a$0$9383$ed9e5944@.reading.news.pipex.net...
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > Wouldn't it be nice if SQL build statistics which automatically would
help
> > the system create indexes at runtime?
> I'd absolutely hate it. Well building a table of suggested indexes might
be
> nice, but making the change automagically would introduce more problems
than
> it was worth IMO.
Allrighty,
in case we can define withhold parameters for auto-indexing and such it
would be no problem. The designer still has to have control over it but the
designer should not be too dependend on the dbadmin for helping or
co-operating on optimizing (and vice-versa)|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:tPGdne5sW-tVTVmiRVn-tA@.giganews.com...
> > How often have you actually gotten something useful out of the Index
> > Tuning Wizard?
> Errr.. never. I've hardly ever used it. I expect it can give useful
results
> if supplied with a suitable Profiler trace but I expect most full-time
> administrators and designers prefer to do this stuff for themselves based
on
> business requirements and their own research.
> I don't think generating indexes at "runtime" could offer anything better.
> Creating indexes without the opportunity for review by the
> administrator/designer sounds pretty dangerous to me - if that's what the
OP
> was suggesting.
Again, I do not suppose that SQL blindly will do that, I suggest a feature
that can be enabled per table, so if a table consists of 20 columns,
potentially, it could create 20 or more (if more columns are combined)
indexes SQL should decide at some points whether or not to create one.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:eOIchwxvDHA.2180@.TK2MSFTNGP09.phx.gbl...
> "Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
> news:3fc5ee5a$0$9383$ed9e5944@.reading.news.pipex.net...
> > "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in
message
> > news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > > Wouldn't it be nice if SQL build statistics which automatically would
> help
> > > the system create indexes at runtime?
> >
> > I'd absolutely hate it. Well building a table of suggested indexes might
> be
> > nice, but making the change automagically would introduce more problems
> than
> > it was worth IMO.
> Allrighty,
> in case we can define withhold parameters for auto-indexing and such it
> would be no problem. The designer still has to have control over it but
the
> designer should not be too dependend on the dbadmin for helping or
> co-operating on optimizing (and vice-versa)
You seem to be suggesting that the fewer times the dba and the system
designer have to speak the better. I'd have though the polar opposite were
true. design and admin are tasks that are inextricably linked.
Niall Litchfield
Oracle DBA
Audit Commission UK|||"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fd87eb4$0$9385$ed9e5944@.reading.news.pipex.net...
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:eOIchwxvDHA.2180@.TK2MSFTNGP09.phx.gbl...
> > in case we can define withhold parameters for auto-indexing and such it
> > would be no problem. The designer still has to have control over it but
> the
> > designer should not be too dependend on the dbadmin for helping or
> > co-operating on optimizing (and vice-versa)
> You seem to be suggesting that the fewer times the dba and the system
> designer have to speak the better. I'd have though the polar opposite were
> true. design and admin are tasks that are inextricably linked.
I absolutely do not say that there is a **one fits all* way of working. In
some cases you are right. In other cases, you don't want to have long talks
with the dbadmin that you even don't know, you give him a list of options
(db size, backup needs, etc) but you don't want to beg to get him or her
optimize *your app* if he has no budget for it and you have no priviliges to
start a tracelog.
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>|||"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fd87eb4$0$9385$ed9e5944@.reading.news.pipex.net...
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:eOIchwxvDHA.2180@.TK2MSFTNGP09.phx.gbl...
> You seem to be suggesting that the fewer times the dba and the system
> designer have to speak the better. I'd have though the polar opposite were
> true. design and admin are tasks that are inextricably linked.
ps; I'd like to stress again my original point. I have an app (CRM like)
where you *cannot* optimize everything on forehand! The app can only be
optimized in combination of user statistics, so if the user likes to search
on postalcode only or on his own favorite fields, SQL server should decide
for that environment, to add a new index. My app is that flexible that the
user can sort and search on any column. You don't want to add indexes for
any column do you? :)