What is the best way to tranfer data from server to server and truncate source DB

[nguyenhoangvk]

Hello,
We are running some services that cause a lot of records to be write to our database, this lead to bad performance because everyone querying it for reports all day while it's still serves requests of other services ran on it.

Currently, I set up a report server, this server is a mirror of main server and all report system will use this server to generate reports. This was a little solution and improve the main server performance but now I face a new problem, the main server performance is decreased day by day as database weight is increasing and all services ran on it becomes slow.

I'm finding a solution to solve this. I plan to remove the replication and using the second server as a archive db server, I mean all records in main server will be transfer to archive server and will be deleted when the transfer complete.

Pros:
- The main server will store only today records ( Today report will run on this ), performance of services ran on this server will increase for sure
- The archive server will serves yesterday, part report


I need your advice for this flow because I'm not a DB Admin and lack of store procedure skills, please help.

[rmiao]

-You can transfer data via linked server or with ssis package.
-May partition table by day, easier to drop old rows.
-Run sql on dedicated server.