Posted - 09/25/2012 : 03:17:36
I have 6-7 DBs locally organized till now on 1 SQL2008 std edition instance. Some of the DBs are real time production, others are mostly batch processing(reporting, warehouse) and the last are documents folder "oriented". I would like to offload both the warehouse building process which is both time and resource consuming and the network overload of documents attaching and load to different physical machine in the same LAN so that I will get free CPU/Mem/Network/Disk for the real time processing and reporting. Furthermore the warehouse which is queried often would not break the real time processes. I was thinking to move the databases on the linked server. I was reading quite a while for linked servers and did not find any obstacle not to do so but since I have no prev. experience on this I would like to add few questions here:
0. Is this the correct way to go or I have missed the complete point? If yes than the following questions actually make sense
1. do I need to take special consideration on licensing or all I need is 2 std edition servers?
2. will I really get performance on document fetching trough the network all the complete traffic will go trough the "central" server which will add on additional performance constraint?
3. I do want to "redirect" triggers from the real time DB to a DB on the warehouse server. Will it improve performance?
4. are there any special things that I'll have to implement in code except changing the schema naming conventions?
5. Any difficulties that I may not be aware of right now and can come as a question in future?
Thank you all.