August 8, 2013 at 4:48 am
I have 2 SQL servers on same domain.
ServerOLD: running workgroup edition 2008 R2 with AccOldDB (old accounts DB now read only)
ServerNEW: running standard edition 2008 R2 with AccNewDB (new 3rd party accounts system)
To maintain legacy reporting I kept the AccOldDB on ServerOLD and added a Linked Server to ServerNEW.
In this way I made views that make the new accounts system report data like the old system did.
The benefit has been no calling code/reports have had to change, in fact many people do not realise we have changed systems and that the 2 systems have very different schemas.
An Example of the views I've changed:
create view AccOldDB..vCustomers
AS
SELECT cust.name, tAdd.Add1, tAdd.Add2, tAdd.PostCode, ....
from ServerNEW.AccNewDB..tblCustomer as tCust
INNER JOIN ServerNEW.AccNewDB..tblAddress as tAdd
ON tCust.CID = tAdd.CID
WHERE tAdd.AddressType = 'Main'
I'm considering moving the AccOldDB from ServerOLD --> SERVERNEW thinking it would be quicker.
I realised that I don't understand what happens with queries across Linked servers. I presume the work is done by ServerNEW and just the results are passed to ServerOLD.
Would it benefit from moving AccOldDB to the new Server? So both dbs are on same server
I suppose an even better improvement would be to create the views directly on ServerNEW..AccNewDB, but the 3rd party vendor frowns on this.
Of course moving the DB Location would involve some changes to hard coded connection strings, however much of the connection strings out there use global File DSNs.
I'd appreciate advice from people more experienced in Linked servers than I..
Many thanks
August 8, 2013 at 6:23 am
Yes you would greatly benefit by moving the 'old' database over to your new server and modifying your views accordingly.
The probability of survival is inversely proportional to the angle of arrival.
August 8, 2013 at 7:21 am
I'll give it a go.
What are the reasons for the speed improvement..
August 8, 2013 at 9:01 am
Minimizing the network throughput for one... the whole query can be run in the engine and potentially be better optimized. Faster query results & better scaling.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply