Rebuild SQL 2000 indexes on-line

I’ve asked several “experts” and none of them have a way to do this. Do you
know of any way to do this while the users are on line (without using DBCC
Indexdefrag)? As I understand the Indexdefrag, eventually you’ll have to
rebuild the indexes anyway.
I have 3 databases and each of them has several very large tables (one of
them exceeds 182 million rows). In order to truly be 7x24, I need to find a
way to rebuild the indexes while the users are in the database.
Thanks in advance for any help you can provide,
Regards,
George
You can't with SQL 2000. The best you can do without disrupting users
is to use DBCC INDEXDEFRAG, which will reorder pages in the index but
not shuffle extents so that they're contiguous. It will improve the
logical fragmentation as reported by DBCC SHOWCONTIG but not the extent
fragmentation.
I guess your options are 1) to upgrade to SQL 2005, which allows online
index rebuild operations (although there are a few caveats around those,
for example they're only available with the Enterprise Edition, can't be
done on clustered indexes when the underlying table contains LOB data,
can't be done on nonclustered indexes that contain LOB columns...), or
2) have an outage while you rebuild your clustered index on your large
tables (nonclustered indexes are normally not as big a problem).
Personally, I'm looking for a good excuse to rebuild one of our big
clustered indexes online in SQL 2005 just for the fun of it...and
because we can. <g>
*mike hodgson*
http://sqlnerd.blogspot.com
George wrote:

>I’ve asked several “experts” and none of them have a way to do this. Do you
>know of any way to do this while the users are on line (without using DBCC
>Indexdefrag)? As I understand the Indexdefrag, eventually you’ll have to
>rebuild the indexes anyway.
>I have 3 databases and each of them has several very large tables (one of
>them exceeds 182 million rows). In order to truly be 7x24, I need to find a
>way to rebuild the indexes while the users are in the database.
>Thanks in advance for any help you can provide,
>Regards,
>George
>

Rebuild SQL 2000 indexes on-line

I’ve asked several “experts” and none of them have a way to do this. D
o you
know of any way to do this while the users are on line (without using DBCC
Indexdefrag)? As I understand the Indexdefrag, eventually you’ll have to
rebuild the indexes anyway.
I have 3 databases and each of them has several very large tables (one of
them exceeds 182 million rows). In order to truly be 7x24, I need to find a
way to rebuild the indexes while the users are in the database.
Thanks in advance for any help you can provide,
Regards,
GeorgeYou can't with SQL 2000. The best you can do without disrupting users
is to use DBCC INDEXDEFRAG, which will reorder pages in the index but
not shuffle extents so that they're contiguous. It will improve the
logical fragmentation as reported by DBCC SHOWCONTIG but not the extent
fragmentation.
I guess your options are 1) to upgrade to SQL 2005, which allows online
index rebuild operations (although there are a few caveats around those,
for example they're only available with the Enterprise Edition, can't be
done on clustered indexes when the underlying table contains LOB data,
can't be done on nonclustered indexes that contain LOB columns...), or
2) have an outage while you rebuild your clustered index on your large
tables (nonclustered indexes are normally not as big a problem).
Personally, I'm looking for a good excuse to rebuild one of our big
clustered indexes online in SQL 2005 just for the fun of it...and
because we can. <g>
*mike hodgson*
http://sqlnerd.blogspot.com
George wrote:

>I’ve asked several “experts” and none of them have a way to do this.
Do you
>know of any way to do this while the users are on line (without using DBCC
>Indexdefrag)? As I understand the Indexdefrag, eventually you’ll have to
>rebuild the indexes anyway.
>I have 3 databases and each of them has several very large tables (one of
>them exceeds 182 million rows). In order to truly be 7x24, I need to find a
>way to rebuild the indexes while the users are in the database.
>Thanks in advance for any help you can provide,
>Regards,
>George
>

real time progress bar

The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

This is definitely NOT the way to go. Launching a GUI from a Script Task would be very cumbersome and error prone and would require that SSIS is installed on the end user machines.

A much better solution is to execute the package from your own program. Books On-line has documentation on how to do this, look up "Managing Packages Programmatically".

<shameless_plug>You can also check out Professional SQL Server 2005 Integration Services. It has a chapter dedicated to manipulating SSIS from external applications.</shameless_plug>

[Edit]
After reading your post again, you definitely can't do it from a Script Task. Your say your requirements are for the end-user to not have the designer. However, if you launch any GUI elements from a script, they will load on the machine the script is run on. So if you're running on a server, it will get the screens. You'll also probably be running it under a system account so no one will see the GUI at all. I alluded to this in the first paragraph but I wanted to make it clear as to why.
|||

Hi Jason,

Thank you for you input. These SSIS packages are hosted on the same machine as the application that the users are running. BIDS is not deployed on those boxes and we have already been successfully opening winforms from SSIS on these boxes in order to retrieve some parameters for the ETL from the users at runtime. So far no problems. It is convenient for our project to provide one box to the remote sites which houses all required software to run the application.

re:<<GUI from a Script Task would be very cumbersome and error prone >>

This seemed easy to me when I programmed it. All I did was create a pretty winform in VB.net express edition using the wsywig designer, and copy and paste all the generated code directly into a script component in SSIS. Then I only had to point the GUI component's source properties at my package variables.

Since I already have this GUI running successfully, I am thinking the easiest way to provide run time feedback to the user is to have that winform stay open and have it reference additional package variables that are updated via SSIS events.

I suppose another way to do this would be to have the SSIS package persist progress information to an external data store, and run a seperate winform process to query that data and display to the user. This approach has the advantage as you point out of being hosted on a different box, independant of the SSIS box, but would be a more complex solution than just having a winform access variables directly from within the package. Then again it has another advantage that all the progress info for an ETL run would be persisted for auditing and archival reference. Theoretically if that data is all persisted along with the time that each event was logged, you would then have the ability to also "replay" the runtime display at any time. That sounds interesting...

real time progress bar

The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

This is definitely NOT the way to go. Launching a GUI from a Script Task would be very cumbersome and error prone and would require that SSIS is installed on the end user machines.

A much better solution is to execute the package from your own program. Books On-line has documentation on how to do this, look up "Managing Packages Programmatically".

<shameless_plug>You can also check out Professional SQL Server 2005 Integration Services. It has a chapter dedicated to manipulating SSIS from external applications.</shameless_plug>

[Edit]
After reading your post again, you definitely can't do it from a Script Task. Your say your requirements are for the end-user to not have the designer. However, if you launch any GUI elements from a script, they will load on the machine the script is run on. So if you're running on a server, it will get the screens. You'll also probably be running it under a system account so no one will see the GUI at all. I alluded to this in the first paragraph but I wanted to make it clear as to why.|||

Hi Jason,

Thank you for you input. These SSIS packages are hosted on the same machine as the application that the users are running. BIDS is not deployed on those boxes and we have already been successfully opening winforms from SSIS on these boxes in order to retrieve some parameters for the ETL from the users at runtime. So far no problems. It is convenient for our project to provide one box to the remote sites which houses all required software to run the application.

re:<<GUI from a Script Task would be very cumbersome and error prone >>

This seemed easy to me when I programmed it. All I did was create a pretty winform in VB.net express edition using the wsywig designer, and copy and paste all the generated code directly into a script component in SSIS. Then I only had to point the GUI component's source properties at my package variables.

Since I already have this GUI running successfully, I am thinking the easiest way to provide run time feedback to the user is to have that winform stay open and have it reference additional package variables that are updated via SSIS events.

I suppose another way to do this would be to have the SSIS package persist progress information to an external data store, and run a seperate winform process to query that data and display to the user. This approach has the advantage as you point out of being hosted on a different box, independant of the SSIS box, but would be a more complex solution than just having a winform access variables directly from within the package. Then again it has another advantage that all the progress info for an ETL run would be persisted for auditing and archival reference. Theoretically if that data is all persisted along with the time that each event was logged, you would then have the ability to also "replay" the runtime display at any time. That sounds interesting...

real time progress bar

The ETL processing for my project will be deployed as part of a larger application which is operated by non-technical users. Therefore I want to provide real-time feedback to the user on progress, errors, etc., using windows standard GUI interface objects and style- for example a progress bar. The "designer" of course does some of this but there won't be any designer in our deployed application (and SSIS designer is neither intended to be used by, nor appropriate for, non-technical users anyway).

Now that I have the ETL logic working, it's time to tackle this UI requirement. I am thinking one way to do this is to start a script task to run in parallel with the main ETL processing, open a winform in that task that has various GUI objects whose "state" is based on package scoped variables, update the package level variables via the "on progress" event (or other events as needed) of various SSIS tasks and components, and refresh the winform's display regularly via a timer event.

Does this approach seem like it would be effective? Has anyone tried maintaining an open winform via a script task and updating it's objects via package variables in parrallel with the package is running other tasks?

Thanks,

Ken

This is definitely NOT the way to go. Launching a GUI from a Script Task would be very cumbersome and error prone and would require that SSIS is installed on the end user machines.

A much better solution is to execute the package from your own program. Books On-line has documentation on how to do this, look up "Managing Packages Programmatically".

<shameless_plug>You can also check out Professional SQL Server 2005 Integration Services. It has a chapter dedicated to manipulating SSIS from external applications.</shameless_plug>

[Edit]
After reading your post again, you definitely can't do it from a Script Task. Your say your requirements are for the end-user to not have the designer. However, if you launch any GUI elements from a script, they will load on the machine the script is run on. So if you're running on a server, it will get the screens. You'll also probably be running it under a system account so no one will see the GUI at all. I alluded to this in the first paragraph but I wanted to make it clear as to why.
|||

Hi Jason,

Thank you for you input. These SSIS packages are hosted on the same machine as the application that the users are running. BIDS is not deployed on those boxes and we have already been successfully opening winforms from SSIS on these boxes in order to retrieve some parameters for the ETL from the users at runtime. So far no problems. It is convenient for our project to provide one box to the remote sites which houses all required software to run the application.

re:<<GUI from a Script Task would be very cumbersome and error prone >>

This seemed easy to me when I programmed it. All I did was create a pretty winform in VB.net express edition using the wsywig designer, and copy and paste all the generated code directly into a script component in SSIS. Then I only had to point the GUI component's source properties at my package variables.

Since I already have this GUI running successfully, I am thinking the easiest way to provide run time feedback to the user is to have that winform stay open and have it reference additional package variables that are updated via SSIS events.

I suppose another way to do this would be to have the SSIS package persist progress information to an external data store, and run a seperate winform process to query that data and display to the user. This approach has the advantage as you point out of being hosted on a different box, independant of the SSIS box, but would be a more complex solution than just having a winform access variables directly from within the package. Then again it has another advantage that all the progress info for an ETL run would be persisted for auditing and archival reference. Theoretically if that data is all persisted along with the time that each event was logged, you would then have the ability to also "replay" the runtime display at any time. That sounds interesting...

Real Time DSS

Right now we tend to provide extracts to our data warehousing system once a
day. Users seem to be presseing for near real time warehousing. It seems to
be a lot of processing to happen on the production side to get this data
during regular hours and hence the nightly extracts..
I am critical to know how we can provide such a real time solution and not
affect the OLTP performance..
Thanks
There are ways they are usually products by third parties like Informatica
or Ascential. However, this sounds far more like an ODS or reporting
database than a warehouse. The solutions range in complexity and cost from
simple replication of require tables for reporting to providing a service
oriented architecture where transactions can be posted in multiple
directions.
Post more details like number of transactions, size of warehouse, DW
architecture, ETL tools in use and perhaps the group can provider further
advice.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
> Right now we tend to provide extracts to our data warehousing system once
> a
> day. Users seem to be presseing for near real time warehousing. It seems
> to
> be a lot of processing to happen on the production side to get this data
> during regular hours and hence the nightly extracts..
> I am critical to know how we can provide such a real time solution and not
> affect the OLTP performance..
> Thanks
>
|||What is SOA ? I keep hearing about it. Can you send me a good reading link
to that
"Ray" <someone@.nowhere.com> wrote in message
news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.c om...
> There are ways they are usually products by third parties like Informatica
> or Ascential. However, this sounds far more like an ODS or reporting
> database than a warehouse. The solutions range in complexity and cost
from[vbcol=seagreen]
> simple replication of require tables for reporting to providing a service
> oriented architecture where transactions can be posted in multiple
> directions.
> Post more details like number of transactions, size of warehouse, DW
> architecture, ETL tools in use and perhaps the group can provider further
> advice.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
once[vbcol=seagreen]
not
>
|||why don't you try having 2 AS servers pointing to the same Warehouse
(cleaned) databases for their source dimensions and facts. Then have the
application switch between them (via control table indicating which AS is
currently processing data and which one is not so the app would call a stored
procedure to look in the control table to tell it which AS it should connect
to get data). This way you can process the cubes at appropriate time
(whether at a regular time interval or when data volume coming into warehouse
tables met certain row count threshold) then switch over to the other server
to process it also when you are done with the first one.
The user experience is that they will see data is always available as close
to real time as possible...depending on your processing time. By the way,
AS2005 can 'sniff' your warehouse source tables for changes in data to update
the cube automatically (you can do the same now in AS2000 but via ROLAP
storage model which looks at your fact tables so indexing is critical here).
"Hassan" wrote:

> What is SOA ? I keep hearing about it. Can you send me a good reading link
> to that
> "Ray" <someone@.nowhere.com> wrote in message
> news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.c om...
> from
> once
> not
>
>

Real Time DSS

Right now we tend to provide extracts to our data warehousing system once a
day. Users seem to be presseing for near real time warehousing. It seems to
be a lot of processing to happen on the production side to get this data
during regular hours and hence the nightly extracts..
I am critical to know how we can provide such a real time solution and not
affect the OLTP performance..
ThanksThere are ways they are usually products by third parties like Informatica
or Ascential. However, this sounds far more like an ODS or reporting
database than a warehouse. The solutions range in complexity and cost from
simple replication of require tables for reporting to providing a service
oriented architecture where transactions can be posted in multiple
directions.
Post more details like number of transactions, size of warehouse, DW
architecture, ETL tools in use and perhaps the group can provider further
advice.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
> Right now we tend to provide extracts to our data warehousing system once
> a
> day. Users seem to be presseing for near real time warehousing. It seems
> to
> be a lot of processing to happen on the production side to get this data
> during regular hours and hence the nightly extracts..
> I am critical to know how we can provide such a real time solution and not
> affect the OLTP performance..
> Thanks
>|||What is SOA ? I keep hearing about it. Can you send me a good reading link
to that
"Ray" <someone@.nowhere.com> wrote in message
news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.com...
> There are ways they are usually products by third parties like Informatica
> or Ascential. However, this sounds far more like an ODS or reporting
> database than a warehouse. The solutions range in complexity and cost
from
> simple replication of require tables for reporting to providing a service
> oriented architecture where transactions can be posted in multiple
> directions.
> Post more details like number of transactions, size of warehouse, DW
> architecture, ETL tools in use and perhaps the group can provider further
> advice.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
once[vbcol=seagreen]
not[vbcol=seagreen]
>|||why don't you try having 2 AS servers pointing to the same Warehouse
(cleaned) databases for their source dimensions and facts. Then have the
application switch between them (via control table indicating which AS is
currently processing data and which one is not so the app would call a store
d
procedure to look in the control table to tell it which AS it should connect
to get data). This way you can process the cubes at appropriate time
(whether at a regular time interval or when data volume coming into warehous
e
tables met certain row count threshold) then switch over to the other server
to process it also when you are done with the first one.
The user experience is that they will see data is always available as close
to real time as possible...depending on your processing time. By the way,
AS2005 can 'sniff' your warehouse source tables for changes in data to updat
e
the cube automatically (you can do the same now in AS2000 but via ROLAP
storage model which looks at your fact tables so indexing is critical here).
"Hassan" wrote:

> What is SOA ? I keep hearing about it. Can you send me a good reading link
> to that
> "Ray" <someone@.nowhere.com> wrote in message
> news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.com...
> from
> once
> not
>
>