Archiving database for reporting

HI,
I have a database on sql server 2005 ent edition with about 300 000
new records every day. I must keep data in production database about
two months, but there are a lot of reporting activities which are
interrupting normal functioning of production server and there is also
requirement to query historical data(about 1 year) with reports. I
must add new data to the archive database every day. Additionally two
months old data in the production database can change (delete, update)
and that also must be reflected in archive database. So basically I
must insert new data, update changed date and delete deleted data from
production database into archive database but also keep about 1 year
of data in archive database. Then I can separately optimize archive
database for reporting. What is the best way to satisfy all that
requirements. I am thinking about SSIS.SSIS will work. You could also consider a small trigger on the main table
that will put the key values into a holding table when
inserts/updates/deletes occur so you can very quickly make the necessary
changes to the historical records when <2mth old data is modified.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.|||Hi
SSIS can be a good choice.
Also
>also keep about 1 year
> of data in archive database.
DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
> I must keep data in production database about
> two months, b
Create a job and schedule it on day period
I must keep data in production database about
INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
dt<=DATEADD(month,-2,GETDATE())
>Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database.
Here, I'd suggest you to create a trigger or (take a look at OUTPUT clause)
and flag or whatever modified data
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.|||On Feb 7, 9:03=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> SSIS can be a good choice.
> Also
> >also keep about 1 year
> > of data in archive database.
> DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
> > I must keep data in production database about
> > two months, b
> =A0 =A0 Create a job and schedule it on day period
> =A0I must keep data in production database about
> INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >=3D'19000101' AN=D
> dt<=3DDATEADD(month,-2,GETDATE())
> >Additionally two
> > months old data in the production database can change (delete, update)
> > and that also must be reflected in archive database.
> Here, I'd suggest =A0you to create a trigger or (take a look at OUTPUT cla=use)
> and flag or whatever modified data
> "OgnjenT" <Ognj...@.gmail.com> wrote in message
> news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
>
> > HI,
> > I have a database on sql server 2005 ent edition with about 300 000
> > new records every day. I must keep data in production database about
> > two months, but there are a lot of reporting activities which are
> > interrupting normal functioning of production server and there is also
> > requirement to query historical data(about 1 year) with reports. I
> > must add new data to the archive database every day. Additionally two
> > months old data in the production database can change (delete, update)
> > and that also must be reflected in archive database. So basically I
> > must insert new data, update changed date and delete deleted data from
> > production database into archive database but also keep about 1 year
> > of data in archive database. Then I can separately optimize archive
> > database for reporting. What is the best way to satisfy all that
> > requirements. I am thinking about SSIS.- Hide quoted text -
> - Show quoted text -
Problem with triger is when I clean data older then two month it will
delete data in archive database too. Also I must then put the triger
on the other tables because of the referential integrity in the
archive database.
>INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >=3D'19000101' AND=
>dt<=3DDATEADD(month,-2,GETDATE())
I can't do that because I must have to synchronize production and
archive database every day because every night I mast have a dozen of
reports.
I decided to have archive database because of different way of queries
for normal processing and for reporting and I don't wont reporting
have so mutch influence on normaln work. So it is not only archiving
data but creating separate database for reporting so I can put some
more indexes, indexed views and in the same time my insert and updates
will still be fast in the production database. Also production server
doesn't have to hold data older than two months so it is better to
clean it so my queries woold be faster.
Is it maybe ok to delete data older than two months from archive
database every night and insert that data from production database. It
seems to me that it will be faster than check every row in production
database and then update archive database if row is updated, delete if
deleted. The esiest of course is to insert new rows.
Maybe before that bulk delete and insert it wood be smart to drop all
indexes, indexed views and constraint and recreate it after. But I
must do all that for about 15 minutes.

Apps cannot connect to SQL after server upgrade to 2003 R2

I had a working SQL Server 2005 install based on an evaluation of Windows
Server 2003 SP2 Ent. I have several other servers running MOSS 07 and WSUS.
I upgraded the SQL server machine to 2003 R2 SP2 Ent. as the evaluation had
9 days before expiry.
However after the upgrade other machines cannot connect to the databases in
SQL, yet i can connect locally using SQL server management studio, the
databases are all intact and permissions have not been changed.
Any ideas would be gratefully received as I have my A Level coursework
stored in a a sharepoint site in one of these Databases!!!!!
AdamDid you check your Windows Firewall settings in your upgraded machine?
If it's not the problem, what's the error message exactly?
--
Ekrem Ã?nsoy
"Adam Gent" <AdamGent@.discussions.microsoft.com> wrote in message
news:2997350C-2891-46FB-AC33-010DBA43C807@.microsoft.com...
>I had a working SQL Server 2005 install based on an evaluation of Windows
> Server 2003 SP2 Ent. I have several other servers running MOSS 07 and
> WSUS.
> I upgraded the SQL server machine to 2003 R2 SP2 Ent. as the evaluation
> had
> 9 days before expiry.
> However after the upgrade other machines cannot connect to the databases
> in
> SQL, yet i can connect locally using SQL server management studio, the
> databases are all intact and permissions have not been changed.
> Any ideas would be gratefully received as I have my A Level coursework
> stored in a a sharepoint site in one of these Databases!!!!!
> Adam|||windows firewall is off - i get error message when i try to access sharepoint
pages - "cannot connect to configuration database" and WSUS gives similar
error.
I can see no errrors in the application logs on the SQL server.|||Then go to SQL Server Configuration Manager and check out your protocols.
Ensure they are enabled and configured correctly.
Also, You did not mention which SQL Server Edition you have, however SQL
Server 2005 Express and Developer Editions are configured Remote Connections
disabled by default. You could check it from SQL Server Surface Area
Configuration tool.
--
Ekrem Ã?nsoy
"Adam Gent" <AdamGent@.discussions.microsoft.com> wrote in message
news:654B7ED7-35C6-4854-8DBA-128757AAC45F@.microsoft.com...
> windows firewall is off - i get error message when i try to access
> sharepoint
> pages - "cannot connect to configuration database" and WSUS gives similar
> error.
> I can see no errrors in the application logs on the SQL server.|||I have SQL Server 2005 SP2 Enterprise.
All remote protocols are enabled apart from VIA as the service wont start
with it enabled.
"Ekrem Ã?nsoy" wrote:
> Then go to SQL Server Configuration Manager and check out your protocols.
> Ensure they are enabled and configured correctly.
> Also, You did not mention which SQL Server Edition you have, however SQL
> Server 2005 Express and Developer Editions are configured Remote Connections
> disabled by default. You could check it from SQL Server Surface Area
> Configuration tool.
> --
> Ekrem Ã?nsoy
>
> "Adam Gent" <AdamGent@.discussions.microsoft.com> wrote in message
> news:654B7ED7-35C6-4854-8DBA-128757AAC45F@.microsoft.com...
> > windows firewall is off - i get error message when i try to access
> > sharepoint
> > pages - "cannot connect to configuration database" and WSUS gives similar
> > error.
> >
> > I can see no errrors in the application logs on the SQL server.
>