Linked Servers security context and transaction logging

Question

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:

1. create linked server
2. create new database on the linked server with mirror logging tables
3. 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.

The 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:

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

2. 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 A and A1, to query database B1 and C1?

Answer 1

Don't use a linked server for this. It will likely be slow. Linked servers are suitable for small queries over remote databases, not for bulk loading data.

I would use SSIS or whichever ETL system you feel more comfortable with.

Answer 2

Ad1. For me question is bit unclear. Transaction log is not "audit log" for db, for short - it keeps history of transactions. Inserts will be on one server, deletes on another.

Ad2. Sure you can restricts rights. Server connects to linked server with security context (user/pass etc) which is given in link properites. Set needed permissions for remote server user. Nice tutorial is on databasejournal