asked
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: 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][1] 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 and `userA` must be able to query only database `A` and `A1`, how can I restrict his access to the databases `B1` and `C1`? [1]:
https://msdn.microsoft.com/en-us/library/ms188279.aspx
When moving the data, the delete is performed against the table(s) in database A while the insert is in database B. Therefore, you are correct that those operations will be logged accordingly with the delete logged in database A and the insert logged in database B. Can you please reword your last question? I'm not sure I understand it and don't want to accidentally give the wrong advice. If you have Enterprise edition (which you need to use compression anyway), I recommend you look into table partitioning. A sliding window partition strategy could make your data move operations much faster because the delete in the source database would only take a second or so if using partition switching.
The partitioning would make the deleting much simpler and quicker. Once the data is moved, you could just "switch" out that partition instead of running a delete statement on that range of data.
15 People are following this question.
