Hi all,
Would appreciate some thoughts on the best way to do this scenario.
This is not a large database in the number of tables, but has about 100,000
new rows per day. Data is inserted only, and no queries / SPs are being run
against the data.
Database 1 (transactional database)
Holds all current data, and all data up to 100 days old.
Database 2 (historic database)
Holds all data and is max 15 mins older than Database 1.
So, what would be the best way to keep the historic database current?
Replication, triggers, agent running SPs, keeping it within 15mins sync to
the transactional database, and deleting records older than 100 days from th
e
transactional database...
Would appreciate anyones thoughts on this...
-MarkFor 100000 rows, in general, one could write a simple job & schedule it to
run every day. The job can have a script that copies the historical data to
the destination & purges it from the source.
Anith|||My choices...
Part 1: Removing from Active db.
Use a nightly job to delete all records over 100 days (You do have a Date
Added Column? Is it indexed?)
Part 2: Moving new data to 'Historic' db
Trigger that copies the INSERTED table to the 'Historic' db.
For this application, a trigger would be have the same or less
operational impact, and the maintenance would be significantly
less. This assumes, of course, that security and connection
issues allow the use of a Trigger.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"MarkieMark" <u20627@.uwe> wrote in message news:5e6ed66fd1456@.uwe...
> Hi all,
> Would appreciate some thoughts on the best way to do this scenario.
> This is not a large database in the number of tables, but has about
> 100,000
> new rows per day. Data is inserted only, and no queries / SPs are being
> run
> against the data.
> Database 1 (transactional database)
> Holds all current data, and all data up to 100 days old.
> Database 2 (historic database)
> Holds all data and is max 15 mins older than Database 1.
> So, what would be the best way to keep the historic database current?
> Replication, triggers, agent running SPs, keeping it within 15mins sync to
> the transactional database, and deleting records older than 100 days from
> the
> transactional database...
> Would appreciate anyones thoughts on this...
> -Mark|||Arnie,
My thoughts were with triggers to. The only issue I had was if the historic
database went off-line for some reason etc., that the databases would get ou
t
of sync. Haven't been able to get my head round that little problem yet.
-Marksql
Tuesday, March 27, 2012
archiving data
Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon
"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>
|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon
"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>
|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Archiving data
You could possibly use transactional replication for this.
All the tables would be in the same publication and
relationships may also be registered there. Foreign keys
would be marked as Yes (Not for Replication). You'd have
to ensure that deletes are not replicated (using None for
the replication command).
Initially I mentioned 'possibly' - this is because there
may be some deletes that you really want to be replicated -
it all depends on how your system is set up. EG on one
database I worked on, there were no deletes allowed -
updates of a bit flag would effectively remove the record
from the user's vision. If your system is like this then
transactional would be ok. If not, then DTS, or snapshot
replication to append records could be used before the
deletes take place.
HTH,
Paul Ibison
I agree with Paul. I would incorporate the business logic for deletes in
custom stored procedures.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:17eb01c46f48$46da5a20$a301280a@.phx.gbl...
> You could possibly use transactional replication for this.
> All the tables would be in the same publication and
> relationships may also be registered there. Foreign keys
> would be marked as Yes (Not for Replication). You'd have
> to ensure that deletes are not replicated (using None for
> the replication command).
> Initially I mentioned 'possibly' - this is because there
> may be some deletes that you really want to be replicated -
> it all depends on how your system is set up. EG on one
> database I worked on, there were no deletes allowed -
> updates of a bit flag would effectively remove the record
> from the user's vision. If your system is like this then
> transactional would be ok. If not, then DTS, or snapshot
> replication to append records could be used before the
> deletes take place.
> HTH,
> Paul Ibison
All the tables would be in the same publication and
relationships may also be registered there. Foreign keys
would be marked as Yes (Not for Replication). You'd have
to ensure that deletes are not replicated (using None for
the replication command).
Initially I mentioned 'possibly' - this is because there
may be some deletes that you really want to be replicated -
it all depends on how your system is set up. EG on one
database I worked on, there were no deletes allowed -
updates of a bit flag would effectively remove the record
from the user's vision. If your system is like this then
transactional would be ok. If not, then DTS, or snapshot
replication to append records could be used before the
deletes take place.
HTH,
Paul Ibison
I agree with Paul. I would incorporate the business logic for deletes in
custom stored procedures.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:17eb01c46f48$46da5a20$a301280a@.phx.gbl...
> You could possibly use transactional replication for this.
> All the tables would be in the same publication and
> relationships may also be registered there. Foreign keys
> would be marked as Yes (Not for Replication). You'd have
> to ensure that deletes are not replicated (using None for
> the replication command).
> Initially I mentioned 'possibly' - this is because there
> may be some deletes that you really want to be replicated -
> it all depends on how your system is set up. EG on one
> database I worked on, there were no deletes allowed -
> updates of a bit flag would effectively remove the record
> from the user's vision. If your system is like this then
> transactional would be ok. If not, then DTS, or snapshot
> replication to append records could be used before the
> deletes take place.
> HTH,
> Paul Ibison
archiving data
Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be remove
d
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the da
ta
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be remove
d
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the da
ta
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Archiving Data
I was exploring ways of archiving old data in our production environment
whilst maintaining it's availability... ...the current data takes 90% of the
queries, but grows very large and puts pressure on the disks... ...I wanted
to explore using a "sliding window" on a partitioned view across a federated
database and have two initial questions I can't find an answer to...
1. Does the data move between partitions when partitions are located on
different federated servers?
2. Assuming a good horizontal partition is chosen, is an automated sliding
window practical in a production environment?
Thanks in advance BenUKPersonally, I'd revise your strategy to use Partioned Tables in SQL2K5.
Awesome feature.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:843C5511-A7D1-4323-83A9-7D7B17BA7639@.microsoft.com...
> I was exploring ways of archiving old data in our production environment
> whilst maintaining it's availability... ...the current data takes 90% of
the
> queries, but grows very large and puts pressure on the disks... ...I
wanted
> to explore using a "sliding window" on a partitioned view across a
federated
> database and have two initial questions I can't find an answer to...
> 1. Does the data move between partitions when partitions are located on
> different federated servers?
> 2. Assuming a good horizontal partition is chosen, is an automated sliding
> window practical in a production environment?
> Thanks in advance BenUK
whilst maintaining it's availability... ...the current data takes 90% of the
queries, but grows very large and puts pressure on the disks... ...I wanted
to explore using a "sliding window" on a partitioned view across a federated
database and have two initial questions I can't find an answer to...
1. Does the data move between partitions when partitions are located on
different federated servers?
2. Assuming a good horizontal partition is chosen, is an automated sliding
window practical in a production environment?
Thanks in advance BenUKPersonally, I'd revise your strategy to use Partioned Tables in SQL2K5.
Awesome feature.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:843C5511-A7D1-4323-83A9-7D7B17BA7639@.microsoft.com...
> I was exploring ways of archiving old data in our production environment
> whilst maintaining it's availability... ...the current data takes 90% of
the
> queries, but grows very large and puts pressure on the disks... ...I
wanted
> to explore using a "sliding window" on a partitioned view across a
federated
> database and have two initial questions I can't find an answer to...
> 1. Does the data move between partitions when partitions are located on
> different federated servers?
> 2. Assuming a good horizontal partition is chosen, is an automated sliding
> window practical in a production environment?
> Thanks in advance BenUK
archiving data
Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> >
> > Hi all hope this is in the right forum.
> >
> > Ive got a SQL database which accumulates about 4 million records each
> > year.
> > This amount causes my PC to slow down when searching, and my hard drive is
> > getting fuller.
> >
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> > To overcome this problem I have to delete half the records each year (2
> > million) which takes quite some time.
> >
> Yeah, deletions can take time.
>
> > My question is - is it possible to archive some of the SQL database each
> > year onto another hard drive or DVD. The archived data should them be
> > removed
> > from the original SQL data base.
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
> >
> > I Would also need to be able to view, sort and search this archived data
> > on
> > another machine in excell or something.
> >
> > Hope someone can help.
> >
> > Thanks
> >
> > Jon
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.comsql
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> >
> > Hi all hope this is in the right forum.
> >
> > Ive got a SQL database which accumulates about 4 million records each
> > year.
> > This amount causes my PC to slow down when searching, and my hard drive is
> > getting fuller.
> >
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> > To overcome this problem I have to delete half the records each year (2
> > million) which takes quite some time.
> >
> Yeah, deletions can take time.
>
> > My question is - is it possible to archive some of the SQL database each
> > year onto another hard drive or DVD. The archived data should them be
> > removed
> > from the original SQL data base.
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
> >
> > I Would also need to be able to view, sort and search this archived data
> > on
> > another machine in excell or something.
> >
> > Hope someone can help.
> >
> > Thanks
> >
> > Jon
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.comsql
Archiving AS 2000 Database to a network backup server
WE are currently using the following to archive our AS 2000 OLAP cubes to the local directory on the server:
"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\DataMart" "DataMart" "c:\BackupFiles\DataMart.cab" It runs inside an agent job. It works fine.
I wanted to change it to archive to our standard backup server location. So I changed it to the following:
"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\DataMart" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\ServerName\DataMart.cab". The job now fails & outputs nothing to the log file. The only thing it says when I view history on the job is : Executed as user: SqlAdmin. The step did not generate any output. Process Exit Code 1. The step failed.
At first I thought maybe AS 2000 couldn't archive over the network so I open up AS Manager & manually did an archive to the same network location. It was successfull. So that blew my theory.
I don't know why the job won't do the archive to the network location. Any ideas?
Thanks,
John
If you can manually run the command
"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\DataMart" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\ServerName\DataMart.cab"
then it's possible that the SqlAdmin user doesn't have write permissions to the share.
Adrian
|||Adrian,
I forgot to mention that SQLAdmin does have privileges to the network backup location. We use this same login for all our SQL backups as well.
I wasn't ever able to get the command to run manually. I was able to manually run the archive process through Analysis manager. Don't know if that makes a difference or not.
Any other ideas?
Thanks,
JOhn
|||Unless you have changed the default data directory, I think you have an incorrect "datamart" sub folder in your data directory parameter.
"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\DataMart" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\ServerName\DataMart.cab"
try removing it and see if the command works then.
"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\ServerName\DataMart.cab"
Also, where are you running Analysis Manager from? Judging by the (x86) in the path I would say that your AS2k server is 64bit and all the management tools (including msmdarch.exe) are 32 bit. The last recommendation I saw said to try to run these tools from a 32bit machine and not on the 64bit server. I don't know if that means it would not work, but it might be another option to explore.
|||Darren,
I removed the extra "datamart" from the command but same result.
Sorry I didn't mention this earlier. I am running W2K3 64 bit with AS 2000 32 bit. I was running Analysis Manager on the 64 bit server when I was able to do the archive manually. WIth this command, I am running it under SQL 2005 EE 64 bit agent job.
I tried running the following command from our 32 bit test server but it had the same result:
"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch" /a 64BitServer"C:\Program Files (x86)\Microsoft Analysis Services\Data\" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\64BitServer\DataMart.cab"
Any other ideas?
Thanks,
John
|||Hmm, running out of ideas.
Have you tried mapping a drive so that you are backing up to z:\... or something like that?
Have you tried specifying the other 2 optional parameter (the logfile and tempfolder)? The log file might give you some ideas, although it might be blank, I seem to remember having trouble like this once, but I can't remember how I resolved it.
Finally, if all else fails there was some documentation, I think in the operations guide whitepaper, on how to "manually" back up an AS2k server by taking a file back up of the data directory and the repository database.
|||Darren,
A mapped drive seems to work. Although, I don't like using mapped drives on our servers but I may have to make an exception in this instance. Maybe I will have it archive locally & then execute a batch job that would xcopy the .cab file to the backup server.
I also tried adding the log & temp folder parameters but that did not make any difference. I still got the useless blank log file. 
Thanks for the help,
JOhn
|||Hello,
Enjoyed reading the thread, I had a thought that might be helpful:
You might try enabling UNC support for your command prompt. By default, Microsoft ships this flag turned off, but I've yet to have any trouble with it turned on. YMMV.
Microsoft KB156276 instructions for doing this:
Under the registry path:
HKEY_CURRENT_USER
\Software
\Microsoft
\Command Processor
add the value DisableUNCCheck REG_DWORD and set the value to 0 x 1 (Hex).
http://support.microsoft.com/kb/156276
I hope this helps you. Good luck...!
-ed2
|||Ed,
Thanks for the thought. I added this to the registry & then ran my job but same result. It may need to have a reboot to get the change into effect. However, the KB article lists this as a bug for Win NT 4 & states that it would be fixed in a SP. So I would hope that this has been fixed in Windows 2003 server. I will leave it until after a reboot to see if it works or not.
THe batch job is working great btw.
John
|||I'm sorry to hear this wasn't a fix... WIth only 8 months of mainstream support remaining on SQL Server 2000, it's hard to imagine this will get much attention, but you might try opening a call if you can justify the expense...
Read the KB article I sent you over again, and you'll find out that the fix is in Win2K, XP, and Win2K3. The KB article said that NT4 originally had no check for UNC pathing in the command prompt, and that caused problems for users who would launch Windows apps from the command prompt session, then close the command prompt and the pipeline to the exe file would become broken. To remedy this Microsoft changed CMD.EXE such that whenever you use a UNC path at the command line it produces an error message UNLESS you put in the registry entry from the KB article. So basically the fix was to decide that it was inappropriate to use UNC pathing at the command prompt. However, I think that once you understand the limitations (and know what not to do), the benefits gained by using the UNC pathing outweigh the risks.
In one of the earlier posts it sounded as though you might be running the job from SQL Server Agent. I've seen some odd behavior from that in some ways -- for example, I wasn't able to call an osql command from SQL Server Agent using xp_cmdshell (pointless as that might seem). So, if you haven't already done so, try the Windows Job Scheduler instead.
Best wishes,
Ed
Subscribe to:
Posts (Atom)