February 12, 2018 at 10:43 am
Hi!
@ present we have a reporting server which is for internal and external users..they run regular reports.
- we keep the data in this server for 1 year
- want to avoid the load onto this server,
- server is in 2008 r2 environment
Needs:
- I want o keep the data longer than 1 year
- i can configure another SQL server with 2016
- I can dedicate this for reporting purposes
_ Please advise How this is possible, Guidance is highly appreciated,
Regards,
Bubby
February 12, 2018 at 10:57 am
bubby - Monday, February 12, 2018 10:43 AMHi!
@ present we have a reporting server which is for internal and external users..they run regular reports.
- we keep the data in this server for 1 year
- want to avoid the load onto this server,
- server is in 2008 r2 environmentNeeds:
- I want o keep the data longer than 1 year
- i can configure another SQL server with 2016
- I can dedicate this for reporting purposes_ Please advise How this is possible, Guidance is highly appreciated,
Regards,
Bubby
Not really enough detail here. What advice, specifically, are you hoping for?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 12, 2018 at 11:02 am
Thanks for the reply, I am thinking to provision on SQL server and do the log shipping for those 5 databases needed for reporting purposes of my existing server. Then my only issue is the data is only kept for 1 year on my existing server.
- I need to know How to proceed,
- is there any way I can do the archive,
Thanks
February 12, 2018 at 11:15 am
bubby - Monday, February 12, 2018 11:02 AMThanks for the reply, I am thinking to provision on SQL server and do the log shipping for those 5 databases needed for reporting purposes of my existing server. Then my only issue is the data is only kept for 1 year on my existing server.- I need to know How to proceed,
- is there any way I can do the archive,Thanks
Ah – are you asking for ways in which to remove data which is > 12 months old?
There's nothing built in ... you'll have to write your own script to do this (and that will depend on all of your tables having CreatedDate and ModifiedDate columns). Do you really mean archive, or just delete? (Because what's the point of archiving source data which already exists in your main database?)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 12, 2018 at 11:53 am
Here is what we have at present,
SQL Server (ABC) where reports are run,
- I keep 1 year data on this one,
- do not want to keep data more than 1 year due to performance issue
- all reports are run of here at present
Requirements:
- need to have data available more than 1 year
- want to create new server,
- want to export the data copies to this new server using ....please suggest best practice
Help is highly appreciated,
Thanks,
February 12, 2018 at 12:33 pm
bubby - Monday, February 12, 2018 11:53 AMRequirements:
- need to have data available more than 1 year
- want to create new server,
- want to export the data copies to this new server using ....please suggest best practice
Help is highly appreciated,Thanks,
You want to create a new server and want to have more data available.
Because of your requirements i can see you need:
-A new server (obviously)
-More space for storing the data (obviously)
For your third and last requirement i suggest you use if you dont want to use built in features of SQL Server like replication or log shipping.
1)SQL server integration services that updates all your tables or x tables x times per day, this is good because you can transform however you want your data to your report server.
2)I don't know if you backup your production database every night and if you do just restore every morning your night backup on your report server, if you dont want to do this see 1st choice.
February 12, 2018 at 12:43 pm
Thanks,
-A new server (obviously)-------I will do that no issue
-More space for storing the data (obviously)------No issue as we are in virtual environment (resources no issue)
For your third and last requirement i suggest you use if you dont want to use built in features of SQL Server like replication or log shipping.
1)SQL server integration services that updates all your tables or x tables x times per day, this is good because you can transform however you want your data to your report server.-------I am not certain about this
2)I don't know if you backup your production database every night and if you do just restore every morning your night backup on your report server, if you dont want to do this see 1st choice.----------I am not sure if this helps as the database I backup is going to 1 year of the date.
Please see my comments
Thank,s
February 13, 2018 at 8:52 am
What is better option Log Shipping or Replication
R
February 13, 2018 at 9:47 am
Can I do log shipping and replication from source as 2008 and destination as 2016
February 14, 2018 at 7:46 am
bubby - Monday, February 12, 2018 10:43 AMHi!
@ present we have a reporting server which is for internal and external users..they run regular reports.
- we keep the data in this server for 1 year
- want to avoid the load onto this server,
- server is in 2008 r2 environmentNeeds:
- I want o keep the data longer than 1 year
- i can configure another SQL server with 2016
- I can dedicate this for reporting purposes_ Please advise How this is possible, Guidance is highly appreciated,
Regards,
Bubby
Do we really face performance issues when we keep data longer than one year ? Was this your understanding OR Was this exactly a requirement ? because there have been many servers built outside with a more than a year / archived old records running without much performance issues....
February 14, 2018 at 7:53 am
Thanks,
- what are the options I have say if I have this scenario,
In Production server I have a database (XYZ) with 1 year data
I can do the logshipping to create a copy onto the reporting server,
Is there any option so I can archive the data of production to reporting server and keep the data for longer duration,
Help is appreciated,
Regards,
February 14, 2018 at 7:57 am
bubby - Wednesday, February 14, 2018 7:53 AMThanks,
- what are the options I have say if I have this scenario,
In Production server I have a database (XYZ) with 1 year data
I can do the logshipping to create a copy onto the reporting server,
Is there any option so I can archive the data of production to reporting server and keep the data for longer duration,Help is appreciated,
Regards,
As was already mentioned, SSIS is an option.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 14, 2018 at 11:34 pm
Phil Parkin - Wednesday, February 14, 2018 7:57 AMbubby - Wednesday, February 14, 2018 7:53 AMThanks,
- what are the options I have say if I have this scenario,
In Production server I have a database (XYZ) with 1 year data
I can do the logshipping to create a copy onto the reporting server,
Is there any option so I can archive the data of production to reporting server and keep the data for longer duration,Help is appreciated,
Regards,
As was already mentioned, SSIS is an option.
Yep, We could built a history db on the same server with more than 1 year old data captured. We can use SSIS to achieve this. Only one problem
would be foreign key relationships between OLTP & OLAP tables in future.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply