October 26, 2009 at 1:58 pm
hello all,
not sure which forum it should be under but will try this one.
I need some advise on the next setup.
we have in place two servers, 1 in a datacenter hosting our website and 1 in our office. We have an dedicated VPN connection from and to each other. Both servers are running SQL 2008 standard. The website is being re-designed to match the database layout we have in our office.
we have a couple of users that connect through an application and have 2 users that queries the database all day long.
what would be the best possible setup for this to avoid heavy queries interfering with normal operations. and how do i maintain the same data online and offline.
I've read about several solutions such as db mirroring and replication but i am puzzled about the best solution for us.
resume, should i let the users connect to the online db for regular operations and how do i sychnronize the data between the two servers optimally.
October 26, 2009 at 2:11 pm
There are a lot of factors that go into that kind of decision.
If the datacenter server is designed for any sort of load at all, two people running a few queries against it shouldn't be a problem in most cases. However, if the queries they run are some huge data mining project that would bring the server to its knees for any length of time, then yeah, you'll need them to query a separate server.
It sounds like the two servers are geographically separated. If that's the case, replication/mirroring might be limited by latency and bandwidth issues. You'd need to test that to determine if it's an issue or not. Long-distance synchronization is often done through log shipping, but that might not work well if you need a copy that can be queried frequently.
The first question, though, is how much of a load do those two users put on the system?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2009 at 2:23 pm
indeed they are geological apart. the online db (website) is not up and running.
the two users do a lot of heavy queries that sometimes cause the regular users to experience time-outs. Thats why i thought about the online solution for the regular users.
the data on both sides need to be updated and inserts will also take place on both ends.
the network connection is however a 4mb dedicatied dsl line, but tests using openquery will show the latency.
October 26, 2009 at 2:31 pm
Do the two people doing those queries need up-to-the-second data for what they're doing? Would hour-old data or last night's data work for their needs?
Alternately, have you checked to make sure the queries they are running are well written and properly supported by the right indexes? Are they something that would benefit from a data warehouse? Would a few indexed views improve them?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2009 at 2:37 pm
hourly data would be just fine as long as their updates proceed to the websites db then.
we are in the process of improving a lot of queries they use, including indexed vieuws etc. Data warehouse is not something we would use. We collect personal information through an website and analyze en export this data to third parties. So all the do is select data for export and occasional analysis.
October 29, 2009 at 7:14 am
One thing you could do in that case would be to do a version of log shipping. Do an hourly backup, full or diff or log (mainly this would depend on the size of the backup), send it to the second machine, restore it, and let them run queryies off of that. That would be easy to set up, easy to maintain, and would have minimal if any impact on the main server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 29, 2009 at 7:30 am
that seems like an good idea to me.
would the option database mirroring work, with an snapshot for the queries you think ?
October 29, 2009 at 1:21 pm
to clarify, is it possible at all to have two 2008 standard servers replicate where server A can be updated and inserted en server B also ?
there are just 4 tables that need to be on the website side.
What i see is that this feature is called peer-two-peer and is only available in enterprise.
October 29, 2009 at 1:28 pm
You can have both replication partners update data in merge or in bidrectional transactional replication. You need to really think through what to do if there are issues with conflicts (two people updating the same row).
Mirroring with snapshots work, though I'll need to drop and recreate the snapshot periodically. If you log ship, you'll kick users out when you restore.
October 29, 2009 at 1:37 pm
Steve Jones - Editor (10/29/2009)
You can have both replication partners update data in merge or in bidrectional transactional replication. You need to really think through what to do if there are issues with conflicts (two people updating the same row).
can bidirectional transactional replication be done in sql 2008 standard ?
November 2, 2009 at 1:20 am
yes i believe. check out BOL - Features Supported by the Editions of SQL Server 2008 .
"Keep Trying"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply