I have many large
logging tables that are populating with data constantly. The information is rarely or never read and I want to move them out of the database. So, the idea is to:
- create linked server
- create new database on the linked server with mirror
- each night/several days/week a job will delete the records from the first database and insert them into the second one
The general goal is to reduce the first database size and put it on SSD drives and put the historical not queried data on slow storage.
mirror tables will have less indexes and can be additionally compressed via row,page,column store or clr compressions. It will be working under simple recovery model.
I have never used linked servers and have the following questions:
- If large amount of data is deleted from the first database and inserted into the second one, only the delete operation is logged in the transaction log file of the first database, right?
If I have two linked servers with many databases like this:
Server A Server A1 Database A Database A1 Database B Database B1 Database C Database C1
How can a restrict a user who has can query database
A1, to query database