Linked Servers security context and transaction logging 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 logging tables
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:


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