SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Copy for reporting purposes


Data Copy for reporting purposes

Author
Message
bubby
bubby
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5174 Visits: 424
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230385 Visits: 25390
bubby - Monday, February 12, 2018 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

Not really enough detail here. What advice, specifically, are you hoping for?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
bubby
bubby
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5174 Visits: 424
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230385 Visits: 25390
bubby - Monday, February 12, 2018 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

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?)



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
bubby
bubby
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5174 Visits: 424
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,
Alejandro Santana
Alejandro Santana
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1179 Visits: 1189
bubby - Monday, February 12, 2018 11:53 AM

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,

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.

bubby
bubby
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5174 Visits: 424
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
bubby
bubby
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5174 Visits: 424
What is better option Log Shipping or Replication

R
bubby
bubby
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5174 Visits: 424
Can I do log shipping and replication from source as 2008 and destination as 2016
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3848 Visits: 531
bubby - Monday, February 12, 2018 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

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....

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search