Hi,
I will be working on a project soon that will require one SQL Server 2016 instance to query another SQL Server instance (2008). What is the best practice to query one SQL Server to another? Is Linked Server the best route or should I replicate? Is there another method that I should consider? I am looking for the best method and most efficient/fastest. Some queries will be retrieving 100,000+ records.
Both SQL Server instances are on separate VM servers.
Any help is appreciated.
Thanks,
Tim
October 28, 2019 at 4:24 pm
Linked servers are going to give you a lot of headaches in terms of performance.
how many tables do you need to query on the 2008 instance? - it might be worth looking at transactional replication (if it's only a few tables) - but you would have maybe 5-10 seconds latency
MVDBA
October 28, 2019 at 4:29 pm
Just a quick estimate, I am estimating 20-30 tables that we will need to query.
October 28, 2019 at 5:04 pm
To ask the direct question that MVDBA implied, how much data latency delay is allowed?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2019 at 5:08 pm
in that case the only other pertinent question is "how often and how many rows"..
if you are joining local to remote data in realtime then i'd use replication - if it's a one off overnight bulk copy - then go for linked servers
MVDBA
October 28, 2019 at 5:26 pm
I'm not too worried about having some data latency because this will be a bulk overnight job that only runs once at night. I just need to to be completed in time in the morning before folks arrive at work.
There will be about 20-30 tables that I will be querying. Some of the queries will return 100,000+ rows. So in total, I would estimate 500K to 1M records in one night.
transactional or snapshot replication..100% ... You want the data to be on your local drives. .. With replication it just keeps dribbling data.. The only bad bit is reinitialising. With linked servers, you have no idea how much msdtc will punish you
MVDBA
October 28, 2019 at 7:23 pm
+1 for replication then. Linked servers usually create headaches.
October 28, 2019 at 11:25 pm
I'm not too worried about having some data latency because this will be a bulk overnight job that only runs once at night. I just need to to be completed in time in the morning before folks arrive at work.
There will be about 20-30 tables that I will be querying. Some of the queries will return 100,000+ rows. So in total, I would estimate 500K to 1M records in one night.
I wouldn't fetter a machine with replication for something that runs just once per night. Contrary to what most seem to say, I'd make a linked server, drag what I needed from the other server over using some minimal logging tricks, and then do the queries locally.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2019 at 9:41 am
tim.stutzman wrote:I'm not too worried about having some data latency because this will be a bulk overnight job that only runs once at night. I just need to to be completed in time in the morning before folks arrive at work.
There will be about 20-30 tables that I will be querying. Some of the queries will return 100,000+ rows. So in total, I would estimate 500K to 1M records in one night.
I wouldn't fetter a machine with replication for something that runs just once per night. Contrary to what most seem to say, I'd make a linked server, drag what I needed from the other server over using some minimal logging tricks, and then do the queries locally.
Actually sounds like an interesting test, pull vs push 🙂
MVDBA
October 29, 2019 at 10:08 am
I wouldn't fetter a machine with replication for something that runs just once per night. Contrary to what most seem to say, I'd make a linked server, drag what I needed from the other server over using some minimal logging tricks, and then do the queries locally.
+1
I do this already 🙂
Far away is close at hand in the images of elsewhere.
Anon.
October 29, 2019 at 3:07 pm
Replication has far more headaches than a simple linked server. I'm not against replication when it's really needed, of course, but I don't see that need here. I, too, would just bring over the data needed.
However, I would try to make sure I could efficiently more over only the data I needed to. Add column(s) to the main table so you could tell which rows have been inserted or updated since the last pull. Or use change tracking. 100K rows isn't that big a deal anyway, but if you can reduce it to 20K rows, that is obviously better.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 29, 2019 at 3:48 pm
Replication uses has far more headaches than a simple linked server. I'm not against replication when it's really needed, of course, but I don't see that need here. I, too, would just bring over the data needed.
I'd bring a new element into the dicussion - if it's snapshot replication then you pretty much have to write zero code. and you can pick and choose articles, indexes, columns etc with relative ease.... I've trained a few "tame in house monkeys" how to re-initialise replication and check replication monitor.
the other one is - with transactional replication, you won't get one big hit - it will dribble data throughout the day... no more walking in at 7am and it's 100% broken and it takes 4 hours to fix--- ok that can still happen
I'm not against linked servers, But my god MSDTC can play havoc with you when you do remote updates.
MVDBA
October 29, 2019 at 4:39 pm
But my god MSDTC can play havoc with you when you do remote updates.
Yes, quite true. Thus, when you can, with relatively low amounts of data, don't do remote updates. Instead, send the data needed to do the update to a table in tempdb (tempdb.dbo.table_name) on the remote server, then the do the update(s) completely from that server, i.e., locally.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 29, 2019 at 4:43 pm
I'm now curious as to the original poster's opinion - thankfully no-one has posted "mirror with snapshot" or "log shipping"
MVDBA
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply